Блог

У меня есть 2 большие таблицы: заголовок, подробности в MySQL 8.0.22 (ubuntu 20.04.1).

детали связаны с заголовком с помощью внешнего ключа (ID). я также создал индекс в поле даты в таблице сведений.

так что, если я:

 select * from details where insert_date >= date_sub(now(), interval 1 day)
 

это очень быстро, и с помощью explain я вижу, что он использует мой индекс.
Но если я это сделаю:

 select * from details d, header h where d.insert_date >= date_sub(now(), interval 1 day) and d.header_id = h.header_id
 

он использует внешний ключ вместо моего индекса даты и работает ужасно медленно.

Поэтому я попытался изменить индекс, изменив его на несколько столбцов (insert_date, header_id), но по-прежнему mysql использует только внешний ключ.

Если я принудительно введу индекс:

 select * from details d FORCE INDEX (iDet01), header h where d.insert_date >= date_sub(now(), interval 1 day) and d.header_id = h.header_id
 

это очень быстро.

Так почему mysql не использует его? Есть ли что-то, что я могу сделать, или мне всегда нужно принудительно вводить индекс?

Спасибо!

РЕДАКТИРОВАТЬ 1:

Как и было запрошено, это результат с помощью Explain analyze (я ограничился 100 строками)

 -> Limit: 100 row(s)  (cost=33239.96 rows=100) (actual time=73140.778..73148.843 rows=100 loops=1)n    
-> Nested loop inner join  (cost=33239.96 rows=107) (actual time=73140.777..73148.835 rows=100 loops=1)n        
-> Index lookup on x using iEve004 (TipoEvento='R')  (cost=1.15 rows=9) (actual time=0.016..0.017 rows=2 loops=1)n        
-> Filter: ((e.DataOraIns >= TIMESTAMP'2020-11-24 00:00:00') and (e.DataOraIns <= TIMESTAMP'2020-11-25 00:00:00'))  (cost=3323.07 rows=12) (actual time=36570.379..36574.405 rows=50 loops=2)n            
-> Index lookup on e using fkEve003_idx (IdEsito=x.ECLID)  (cost=3323.07 rows=3703) (actual time=0.171..36462.049 rows=1277108 loops=2)n
 

И это то же самое с индексом, введенным мной:

 -> Limit: 100 row(s)  (cost=79703.94 rows=100) (actual time=20.747..36.088 rows=100 loops=1)n    
-> Nested loop inner join  (cost=79703.94 rows=7215) (actual time=20.746..36.079 rows=100 loops=1)n        
-> Filter: ((e.DataOraIns >= TIMESTAMP'2020-11-24 00:00:00') and (e.DataOraIns <= TIMESTAMP'2020-11-25 00:00:00') and (e.IdEsito is not null))  (cost=29196.14 rows=144308) (actual time=0.035..17.373 rows=33185 loops=1)n            -> Index range scan on e using iEve004  (cost=29196.14 rows=144308) (actual time=0.033..12.003 rows=33185 loops=1)n        -> Filter: (x.TipoEvento = 'R')  (cost=0.25 rows=0) (actual time=0.000..0.000 rows=0 loops=33185)n            -> Single-row index lookup on x using PRIMARY (ECLID=e.IdEsito)  (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=33185)n
 

Кроме того, перед этим я провел анализ таблицы.

РЕДАКТИРОВАТЬ 2:

Я нашел кое-что, выполнив разные тесты:

таким образом, при этом используется правильный индекс:

 select * 
  from details d 
 where d.insert_date >= 20201124000000 
   and d.insert_date <= 20201125000000
 

это не:

 select * 
  from details d, 
       header h 
 where d.insert_date >= 20201124000000
   and d.insert_date <= 20201125000000
   and d.header_id = h.header_id
 

это делает:

 select * 
  from details d, 
       header h 
 where d.insert_date >= DATE_SUB(now(), INTERVAL 1 DAY)
   and d.insert_date <= now()
   and d.header_id = h.header_id
 

это не:

 select * 
  from details d, 
       header h 
 where d.insert_date >= DATE_SUB(now(), INTERVAL 1 DAY)
   and d.insert_date <= now()
   and d.header_id = h.header_id
   and h.tipo = 'U'
 

В таблице заголовка был указан индекс «tipo», и результаты были указаны выше. Однако изменение индекса в таблице заголовков, например (header_id, tipo), и все вышеперечисленное работает.

но нормально ли это?? (я нахожу это странным ..)

Однако изменение запроса таким образом (1 месяц):

 select * 
  from details d, 
       header h 
 where d.insert_date >= DATE_SUB(now(), INTERVAL 1 MONTH)
   and d.insert_date <= now()
   and d.header_id = h.header_id
   and h.tipo = 'U'
 

и он снова использует неправильный индекс.

Я не понимаю.

Для такого рода вещей первым делом нужно проверить, актуальна ли ваша статистика :

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

Если у вашего механизма запросов нет соответствующей статистики о ваших таблицах, он обязательно сделает какой-то неправильный ход. Поэтому поддержание их в актуальном состоянии должно быть главным приоритетом, если у вас есть какие-то изменения в данных.

Для меня ваш составной индекс insert_date, header_id должен работать, но если вы можете отредактировать свой вопрос с помощью некоторых EXPLAIN ANALYZE https://mysqlserverteam.com/mysql-explain-analyze / это поможет нам улучшить наш ответ.