Блог

Главная - Вопросы по программированию - ЗАКАЗ MySQL ПО занимает очень много времени, даже если у меня есть индексы

ЗАКАЗ MySQL ПО занимает очень много времени, даже если у меня есть индексы

#mysql #sql #select #sql-order-by

#mysql #sql #выберите #sql-order-by

Вопрос:

У меня есть следующий запрос MySQL, который занимает около 40 секунд на виртуальной машине Linux:

 SELECT
* FROM `clients_event_log`
WHERE
`ex_long` = 1475461 AND 
`type` in (2, 1) AND NOT 
(
    (category=1 AND error=-2147212542) OR
    (category=7 AND error=67)
)
    ORDER BY `ev_time` DESC LIMIT 100

Таблица содержит около 7 миллионов строк, размером около 800 МБ, и в ней есть индексы для всех полей, используемых в предложениях WHERE и ORDER BY .

Теперь, если я изменю запрос таким образом, чтобы порядок выполнялся во внешнем выборе, все работает намного быстрее (около 100 мс):

 SELECT res.* FROM
(
  SELECT * FROM `clients_event_log`
  WHERE 
  `ex_long` = 1475461 AND 
  `type` in (2, 1) AND NOT 
  (
    (category=1 AND error=-2147212542) OR
    (category=7 AND error=67)
  )
) AS res
    ORDER BY res.ev_time DESC LIMIT 0, 100

У вас есть какие-либо идеи, почему первый запрос занимает так много времени? Спасибо.

Более позднее обновление:

1-й запрос ОБЪЯСНЯЕТ: введите описание изображения здесь

 id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  clients_event_log   index   category,ex_long,type,error,categ_error ev_time 4   NULL    5636    Using where

2-й запрос ОБЪЯСНЯЕТ:
введите описание изображения здесь

 id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  system  NULL    NULL    NULL    NULL    1   
2   DERIVED clients_event_log   ref category,ex_long,type,error,categ_error ex_long 5       131264  Using where

Определение таблицы:

 CREATE TABLE `clients_event_log` (
  `ev_id` int(11) NOT NULL,
  `type` int(6) NOT NULL,
  `ev_time` int(11) NOT NULL,
  `category` smallint(6) NOT NULL,
  `error` int(11) NOT NULL,
  `ev_text` varchar(1024) DEFAULT NULL,
  `userid` varchar(20) DEFAULT NULL,
  `ex_long` int(11) DEFAULT NULL,
  `client_ex_long` int(11) DEFAULT NULL,
  `ex_text` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`ev_id`),
  KEY `category` (`category`),
  KEY `ex_long` (`ex_long`),
  KEY `type` (`type`),
  KEY `ev_time` (`ev_time`),
  KEY `error` (`error`),
  KEY `categ_error` (`category`,`error`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Комментарии:

1. Конечно, этого не должно быть. Запросы семантически равны и должны приводить к одному и тому же плану выполнения. Поскольку это, очевидно, не так, я бы счел это недостатком СУБД.

2. Вероятно, у вас есть отдельные индексы для всех полей, например, один индекс включен ex_long , один индекс включен type , один индекс включен ev_time . MySQL может использовать только один индекс для каждого запроса и, вероятно, использует неправильный в вашем 1-м запросе (например, индекс включен ev_time ). Посмотрите на explain вывод, чтобы проверить это (и добавьте его в свой вопрос). Итак, чтобы решить вашу проблему, вам понадобится составной индекс, например ex_long, ev_time (в зависимости от ваших данных, возможно, и других столбцов) или принудительно ввести правильный одностолбцовый индекс (тот, который, вероятно, использовался в вашем 2-м запросе).

3. Первый план выполнения просто глуп (доступ к каждой записи через индекс только с целью их чтения в порядке окончательной сортировки.) Как я уже сказал: недостаток в оптимизаторе MySQL.

4. @ThorstenKettner Вы правы, это не должно иметь значения, но в MySQL это имеет значение. В старых версиях mysql так создавалась производная таблица, и, следовательно, MyQSL не будет использовать now outer order by -index во внутреннем запросе. 5.7 правильно выполнит его.

5. @Julian Ваш explain показал, как и ожидалось, что вы используете разные индексы в своих запросах. Вы можете либо добавить составной индекс (с таким количеством столбцов, сколько захотите, но, я думаю, 2 должно быть хорошо), либо (или, если он по-прежнему не работает по какой-то причине), вы можете принудительно ввести правильный индекс, используя from `clients_event_log` force index (ex_long) в своем первом запросе.

Ответ №1:

В итоге я использовал второй запрос (внутренний SELECT), потому что оптимизатор MySQL решил всегда использовать ev_time индекс, даже если я пробовал несколько версий составного индекса, содержащего столбцы в предложениях WHERE и ORDER BY .

Использование force index (ex_long) также сработало.

Версия MySQL была 5.5.38

Спасибо.

Комментарии:

1. Просто имейте в виду, что при обновлении до версии 5.7 этот код может больше не работать. Ваш limit допускает возможность того, что mysql выбирает индекс on ev_time , поэтому он может сделать это снова, как только производная таблица больше не будет создаваться с этим синтаксисом, поэтому force index (либо для составного, либо для отдельного индекса) будет более безопасным выбором. Или попробуйте optimize свою таблицу, ваша статистика может быть отключена.

Ответ №2:

Добавьте эти

 INDEX(ev_long, ev_time),
INDEX(ev_long, type)

и используйте первый формат запроса, и пусть оптимизатор решит, что лучше, основываясь на статистике.