Как предотвратить выбор MySQL одного индекса, когда доступен лучший?

У меня есть таблица с 30 000 строк (и она растет), к которой я присоединяюсь к другой таблице. На некоторых страницах мне нужно выполнить более 100 таких запросов, и все становится медленнее. Если я EXPLAIN выполняю запрос, я замечаю, что одна таблица использует первичный ключ и работает быстро, а другая таблица использует один из своих индексов, который не самый лучший. Вот обзор:

SIMPLE | acc_entries | ref | ledger,date,type,status,status_ledger_date_type | type | 1 | const | 15359 | Using where

Это пример запроса:

SELECT SUM(usd) AS total FROM acc_entries
LEFT JOIN acc_ledgers ON acc_entries.ledger = acc_ledgers.id
WHERE acc_entries.status = 1 AND 
acc_ledgers.account = 3004 AND 
date >= '2011-01-01' AND 
date <= '2011-08-30' AND 
type = 'credit'

Как видите, я использую в WHERE поля status, ledger (это поле, которое соединяется с acc_ledgers.account), date и type. Все эти поля имеют индексы. Однако существует также специальный индекс, который используется для всех из них в том же порядке. Он называется status_ledger_data_type, и, как вы видите, это один из индексов, которые MySQL рассматривает для использования. Однако в конце MySQL предпочитает использовать type в качестве индекса. Он содержит около 15 000 возможных строк (половина таблицы), в то время как другой комбинированный индекс содержит лишь часть этого числа. Итак, мои вопросы: почему MySQL выбирает этот индекс, когда доступен лучший, и как я могу предотвратить это?


mysql query-optimization indexing
person Community    schedule 22.10.2011    source источник

Ответы (2)


arrow_upward
1
arrow_downward

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

документы MySql по подсказкам индекса

Битва между индексом Force и оптимизатором запросов< /а>

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

person R. Valbuena    schedule 22.10.2011

arrow_upward
0
arrow_downward

На самом деле, вы хотите, чтобы ваш индекс был основан на меньшей степени детализации. Ledger из вашей таблицы Acc_Entries присоединится к вашей таблице ACC_Ledgers по первичному индексу ID ITS, поэтому Acc_Ledgers на самом деле не использует часть Ledger для предложения WHERE. Ваш индекс должен максимально точно соответствовать предложению WHERE ваших общих запросов. В этом случае у меня будет индекс на

(Учетная запись, Статус, Тип, Дата)

Причина, по которой учетная запись является первой, имеет меньший набор результатов. У вас может быть 5000 записей. Из них 300 записей для одной учетной записи, так что вы уже удалили огромное количество данных для обработки. Затем, Статус... из 300, у вас может быть 100 @ статус 1, 100 @ статус 2, 100 @ статус 3, так что теперь вы еще больше сократили набор и т. д. по другим критериям типа и даты.

В противном случае ваш запрос совершенно в порядке ... просто личный стиль письма, я стараюсь писать свои запросы с условиями WHERE как близко соответствующими индексу в той же последовательности, поэтому я бы просто сначала использовал предложение Account, затем Status, Type и дата... но опять же, это личный стиль написания запросов.

person DRapp    schedule 22.10.2011