Início / user-306137

andrescmasmas's questions

Martin Hope
andrescmasmas
Asked: 2024-09-19 22:23:37 +0800 CST
  • 6

Tenho duas consultas diferentes, cada uma delas é executada muito rápido (em milissegundos).

Consulta 1:

select t1.id
from table1 t1
         left join table2 t2 on t1.id = t2.t1_id
where (t1.update_date >= :timestamp1 AND t1.update_date < :timestamp2);

Consulta 2:

select t1.id
from table1 t1
         left join table2 t2 on t1.id = t2.t1_id
where (t2.update_date >= :timestamp1 AND t2.update_date < :timestamp2);

Quando tento executar uma terceira consulta com uma condição que inclui as condições das duas primeiras consultas, a consulta fica muito lenta (segundos ou minutos, dependendo do tamanho da tabela):

Consulta 3.

select t1.id
from table1 t1
         left join table2 t2 on t1.id = t2.t1_id
where (t1.update_date >= :timestamp1 AND t1.update_date < :timestamp2)
   or (t2.update_date >= :timestamp1 AND t2.update_date < :timestamp2);

Parece que parte do problema é que o BD tem que escanear todas as linhas em t1. Sei que posso melhorar essa consulta e obter o resultado que quero usando UNIONing os resultados das duas primeiras consultas. Mas gostaria de saber qual é o motivo exato pelo qual o otimizador do MySQL (ou possivelmente de qualquer outro BD relacional) não consegue otimizar essa consulta sozinho?

Existe algum impedimento teórico para otimizadores que os impeça de fazer esse tipo de otimização?

Detalhes adicionais

Versão resumida das definições de tabela e declarações explicativas:

Tabela 1:

CREATE TABLE `table1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
   -- ... lots of other field definitions...
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `update_date` (`update_date`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT

Tabela 2:

CREATE TABLE `table2` (
  `t1_id` int unsigned NOT NULL,
  `update_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`t1_id`),
  KEY `update_date` (`update_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

A consulta 1 explica:

+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key         | key_len | ref           | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+------+----------+--------------------------+
|  1 | SIMPLE      | t1     | NULL       | range  | update_date   | update_date | 4       | NULL          |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | t2     | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | t1.id         |    1 |   100.00 | Using index              |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+------+----------+--------------------------+

A consulta 2 explica:

+----+-------------+--------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys         | key           | key_len | ref                   | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | t2     | NULL       | range  | PRIMARY,update_date   | update_date   | 5       | NULL                  |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | t1     | NULL       | eq_ref | PRIMARY               | PRIMARY       | 4       | t2.t1_id              |    1 |   100.00 | Using index              |
+----+-------------+--------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+--------------------------+

A consulta 3 explica:

+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+----------+----------+-------------+
| id | select_type | table  | partitions | type   | possible_keys | key         | key_len | ref           | rows     | filtered | Extra       |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+----------+----------+-------------+
|  1 | SIMPLE      | t1     | NULL       | index  | update_date   | update_date | 4       | NULL          | 10271767 |   100.00 | Using index |
|  1 | SIMPLE      | t2     | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | t1.id         |        1 |   100.00 | Using where |
+----+-------------+--------+------------+--------+---------------+-------------+---------+---------------+----------+----------+-------------+
mysql