MySQL - Почему COUNT с "больше чем" быстро, но "меньше чем" занимает вечность?
SELECT count(*) c FROM full_view WHERE verified > ( DATE (NOW()) - INTERVAL 30 DAY)
Если я выполняю этот запрос, это занимает долю секунды, но если я переключаю оператор сравнения, это занимает эоны. Теперь первый способ count = 0 и второй способ count = 120000, но если я просто посчитаю всю таблицу, то это также займет микросекунды.
Но происходит что-то необычное, потому что, если запрос когда-нибудь закончится, он будет выполняться очень быстро после этого. MySQL кеширует запрос или что-то не так? Ну, я не хочу зависеть от кэшей, чтобы сайт не зависал.
Это кажется бессмысленным: если он может посчитать все, что больше определенной даты, то почему нужно больше времени, чтобы сосчитать обратное? В любом случае он должен просматривать всю таблицу, верно? И все, что нужно вернуть, это число, поэтому пропускная способность не должна быть проблемой.
Объясните по запросу:
1, 'SIMPLE', 'b', 'range', 'updated,verified_index', 'updated', '3', '', 28, 'Using where'`
1, 'SIMPLE', 'l', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'xyz_main.b.loc_id', 1, 'Using index'
1, 'SIMPLE', 'f', 'ALL', '', '', '', '', 2214, ''
РЕДАКТИРОВАТЬ:
Это может представлять некоторый интерес, я нашел эту информацию при запуске запроса:
Handler_read_rnd_next:
- 254436689 (при выполнении меньше, чем)
- 2 (больше чем)
Key_read_requests: 314393 против 33 (33 - самое большое число для всех характеристик при использовании больше чем)
Ключ обработчика: 104303 против 1
Обход представления и выполнение запроса непосредственно в основной таблице устраняет медлительность. Так что мне нужно сделать, чтобы ускорить его? Представление по сути так:
SELECT x, y, z, verified FROM table1 LEFT JOIN table2 on tab2_ID = table2.ID LEFT JOIN table3 on tab3_ID = table3.ID
РЕШЕНО: Фрэнки повел меня в правильном направлении. Вторая объединенная таблица (таблица компании) была объединена с помощью полнотекстового названия компаний. Я только недавно решил добавить целочисленный ключ к этой таблице. Столбец имени должен был быть проиндексирован, но я, возможно, испортил это. Во всяком случае я реорганизовал все. Я преобразовал внешний ключ в основной таблице, чтобы он соответствовал целочисленному идентификатору таблицы компаний, а не полному названию компании. Я переиндексировал эти столбцы в каждой таблице, а затем обновил представление, чтобы отразить новую точку соединения. Теперь он работает мгновенно в обоих направлениях.:) Так что я думаю, что целочисленные ключи были ключом. Проблема ушла, но все же я не чувствую, что мой первоначальный вопрос действительно был решен.
Спасибо за помощь ребята.
4 ответа
Пожалуйста, запустите приведенный ниже запрос и опубликуйте результаты.
EXPLAIN SELECT count(*) c
FROM full_view
WHERE verified > ( DATE (NOW()) - INTERVAL 30 DAY)
Давно забытый EXPLAIN почти всегда что-то приносит! ;)
Изменить 1:
Это, вероятно, наступательная линия:
1, 'SIMPLE', 'f', 'ALL', '', '', '', '', 2214, ''
ALL там говорится, что есть полное сканирование таблицы.
Вы можете копать дальше в Explain синтаксис на этой диаграмме.
Попробуй посмотреть, куда уходят различия...
Изменить 2:
Этот документ наверняка прояснит ситуацию Explain выход. Пожалуйста, проверьте это.
Изменить 3:
Пошаговый анализ команды объяснения.
1, 'SIMPLE', 'b', 'range', 'updated,verified_index', 'updated', '3', '', 28, 'Using where'`
1 - id
SIMPLE - simple select, not using sub-queries
b - table name
range - only rows that are in a given range are retrieved, using an index
updated,verified_index - are both possible keys
updated - was the key eventually used
3 - key lenght
'' - this is the ref column and would show which columns or constants are compared to the index name in the key column to select rows from the table.
28 - number of rows mysql believes it must examine to execute the query
Using where - self explanatory
Я предполагаю, что вычитание из Date(Now()) это то, что занимает много времени для обработки. Для значений verified которые уже меньше Date(Now())оценка может быть замкнута, потому что в этот момент она ДОЛЖНА быть ложной (при сравнении "больше чем").
В ситуации, когда вы сравниваете с "меньше чем", время даты должно быть вычтено в каждом случае, независимо от текущего значения, поскольку у него нет никакого способа логически заключить выражение как истинное или ложное до оценки вычитания даты и времени
Впрочем, это всего лишь предположение - возьмите его с крошкой соли.
Может случиться так, что есть статистика, сообщающая ядру базы данных, что нет записей для проверенных> 30 дней назад. В этом случае даже не нужно читать таблицу, а просто получать информацию из гистограммы статистики.
Если у вас есть индекс на verified в таблице, то более ограничительный COUNT (> один) будет быстрее. COUNT(*) без предложения WHERE может быстро вернуться, так как счетчик может быть получен из статистики таблицы / индекса.