Почему MySQL не использует индекс в поле int, который используется как логическое?

select * from myTable where *****

не будет отображаться никаких возможных клавиш при объяснении запроса, даже если в поле ***** есть индекс.

Изменить: Указанный индекс не уникален.

4 ответа

Чтобы MySQL использовал индекс, вам нужно явно сравнить значение поля int со значением (например, true, 1).

select * from myTable where ***** = true


Я не эксперт базы данных, но разве он не побеждает цель иметь индекс в поле, если есть только два возможных значения поля?

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

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


Есть много факторов, которые следует учитывать.

Один фактор, который не должен в него вступать, - это обозначения, используемые в вопросе. Когда столбец является логическим, то эти условия должны обрабатываться оптимизатором как идентичные:

SELECT * FROM MyTable WHERE *****;
SELECT * FROM MyTable WHERE ***** != 0;
SELECT * FROM MyTable WHERE ***** IS TRUE;
SELECT * FROM MyTable WHERE ***** = TRUE;

Могут быть другие эквивалентные формулировки. Первый из них не является стандартным SQL (даже если тип ***** является BOOLEAN, другие являются стандартными, но оптимизатор должен просто преобразовать стенографию в соответствующую длинную форму, а затем вести себя так же, как если бы длинная форма была написана пользователь. (Если оптимизатор этого не делает, то, возможно, проблема с оптимизатором: запрос должен быть уменьшен до канонической формы, прежде чем принимать решение о том, как обрабатывать запрос. Однако часто появляются слепые пятна даже у лучших оптимизаторов Изучение того, как избежать этого, - это форма искусства и, по сути, специфичная для СУБД.)

Оптимизатор использует индекс, когда считает, что индекс повысит производительность запроса. Когда индекс не будет повышать производительность, он игнорируется (если оптимизатор является хорошим). Иногда это зависит от того, обновлена ​​ли статистика для индекса.

В системах хранения данных система может быть спроектирована и сконфигурирована так, чтобы обеспечить последовательное сканирование таблицы очень быстро; в таких системах, если избирательность индекса такова, что использование этого будет тянуть больше, чем всего лишь 25% строк, на самом деле может быть проще выполнить полное сканирование таблицы, чем использовать индекс.

Подумайте об этом. При чтении через индекс СУБД должен выполнять как минимум два чтения; он считывает информацию о строке с индексной страницы, а затем она должна читать строку со страницы данных.

Некоторые СУБД предоставляют таблицы только для индекса. Все данные находятся в индексе. Другие СУБД предоставляют такой механизм, что вы можете сказать, что "индекс уникален для столбцов A, B, C, но также включать столбцы D и E в данные". Затем, если для запроса требуются данные из A, B, C, D или E (или любой комбинации), и нет фильтрации в других столбцах, СУБД необходимо только сканировать индекс, а не страницы таблицы.

Как правило, вы получаете много строк индекса на странице. Однако для некоторых таблиц чтение индекса может потребовать чтения большего количества данных, чем чтение строк. Рассмотрим архетипическую таблицу сопоставления "многие ко многим", содержащую два (4 байта) значения целочисленного идентификатора. Для страниц данных требуется 8 байт на строку, но для индекса, вероятно, требуется 4-8 байтов служебных данных (поскольку в записи ключа индекса хранятся два идентификационных значения плюс информация, необходимая для поиска соответствующей строки на диске). Таким образом, сканирование индекса может включать в два раза больше дискового ввода-вывода в качестве сканирования данных, даже если сканирование индекса выполняется только "только индекс".

Это едва затрагивает поверхность возможных причин использования или отсутствия индекса.


Ваш вопрос SQL выглядит неправильным для меня. Вы ищете ненулевые значения столбца? Это должно использовать индекс:

select * from myTable where ***** is not null

licensed under cc by-sa 3.0 with attribution.