Mysql не работает индекс

Индексы в MySQL

Индексы в MySQL (Mysql indexes) — отличный инструмент для оптимизации SQL запросов. Чтобы понять, как они работают, посмотрим на работу с данными без них.

1. Чтение данных с диска

На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.

При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, так как понадобится прыгать по разным участкам диска.

Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.

2. Поиск данных в MySQL

Таблицы MySQL – это обычные файлы. Выполним запрос такого вида:

MySQL при этом открывает файл, где хранятся данные из таблицы users. А дальше — начинает перебирать весь файл, чтобы найти нужные записи.

Кроме этого, MySQL будет сравнивать данные в каждой строке таблицы со значением в запросе. Допустим работа ведется с таблицей, в которой есть 10 записей. Тогда MySQL прочитает все 10 записей, сравнит колонку age каждой из них со значением 29 и отберет только подходящие данные:

Итак, есть две проблемы при чтении данных:

• Низкая скорость чтения файлов из-за расположения блоков в разных частях диска (фрагментация).
• Большое количество операций сравнения для поиска нужных данных.

3. Сортировка данных

Представим, что мы отсортировали наши 10 записей по убыванию. Тогда используя алгоритм бинарного поиска, мы могли бы максимум за 4 операции отобрать нужные нам значения:

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

Индекс – это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.

4. Выбор индексов в MySQL

В самом простом случае, индекс необходимо создавать для тех колонок, которые присутствуют в условии WHERE.

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

Нам необходимо создать индекс на колонку age:

После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов. Индекс будет использоваться и для выборок по диапазонам значений этой колонки:

Сортировка

Для запросов такого вида:

действует такое же правило – создаем индекс на колонку, по которой происходит сортировка:

Внутренности хранения индексов

Представим, что наша таблица выглядит так:

После создания индекса на колонку age, MySQL сохранит все ее значения в отсортированном виде:

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

Уникальные индексы

MySQL поддерживает уникальные индексы. Это удобно для колонок, значения в которых должны быть уникальными по всей таблице. Такие индексы улучшают эффективность выборки для уникальных значений. Например:

На колонку email необходимо создать уникальный индекс:

Тогда при поиске данных, MySQL остановится после обнаружения первого соответствия. В случае обычного индекса будет обязательно проведена еще одна проверка (следующего значения в индексе).

5. Составные индексы

MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

Рассмотрим такой запрос:

Нам следует создать составной индекс на обе колонки:

Устройство составного индекса

Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значения всех входящих колонок сразу. Для таблицы с такими данными:

значения составного индекса будут такими:

Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

Поиск по диапазону

Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:

Сначала будут отфильтрованы все данные, которые подходят под условие age . Затем, поиск по значению “male” будет произведен без использования индекса.

Сортировка

Составные индексы также можно использовать, если выполняется сортировка:

В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):

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

Колонок в индексе может быть больше, если требуется:

В этом случае следует создать такой индекс:

6. Использование EXPLAIN для анализа индексов

Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:

Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).

Как видим, в примере не используется ни один индекс. После создания индекса:

Прочитана всего одна запись, так как был использован индекс.

Проверка длины составных индексов

Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):

Значение key_len показывает используемую длину индекса. В нашем случае 24 байта – длина всего индекса (5 байт age + 19 байт gender).

Если мы изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:

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

В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.

7. Селективность индексов

Вернемся к запросу:

Для такого запроса необходимо создать составной индекс. Но как правильно выбрать последовательность колонок в индексе? Варианта два:

Подойдут оба. Но работать они будут с разной эффективностью.

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

68 rows in set (0.00 sec)

Эта информация говорит нам вот о чем:

1. Любое значение колонки age обычно содержит около 200 записей.
2. Любое значение колонки gender – около 6000 записей.

Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.

Это значит, что индекс age_gender будет работать лучше, чем gender_age.

Селективность колонки определяется количеством записей в таблице с одинаковыми значениями. Когда записей с одинаковым значением мало – селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.

8. Первичные ключи

Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:

При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.

Кластерные индексы

Обычные индексы являются некластерными. Это означает, что сам индекс хранит только ссылки на записи таблицы. Когда происходит работа с индексом, определяется только список записей (точнее список их первичных ключей), подходящих под запрос. После этого происходит еще один запрос — для получения данных каждой записи из этого списка.

Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.

Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.

Overhead

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

Создавайте только необходимые индексы, чтобы не расходовать зря ресурсы сервера. Контролируйте размеры индексов для Ваших таблиц:

Когда создавать индексы?

• Индексы следует создавать по мере обнаружения медленных запросов. В этом поможет slow log в MySQL. Запросы, которые выполняются более 1 секунды, являются первыми кандидатами на оптимизацию.
• Начинайте создание индексов с самых частых запросов. Запрос, выполняющийся секунду, но 1000 раз в день наносит больше ущерба, чем 10-секундный запрос, который выполняется несколько раз в день.
• Не создавайте индексы на таблицах, число записей в которых меньше нескольких тысяч. Для таких размеров выигрыш от использования индекса будет почти незаметен.
• Не создавайте индексы заранее, например, в среде разработки. Индексы должны устанавливаться исключительно под форму и тип нагрузки работающей системы.
• Удаляйте неиспользуемые индексы.

Самое важное

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

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

Highload нужны авторы технических текстов. Вы наш человек, если разбираетесь в разработке, знаете языки программирования и умеете просто писать о сложном!
Откликнуться на вакансию можно здесь .

Как исправить ошибку доступа к базе 1045 Access denied for user

Примеры ad-hoc запросов и технологии для их исполнения

Основные понятия о шардинге и репликации

Настройка Master-Master репликации на MySQL за 6 шагов

Check-unused-keys для определения неиспользуемых индексов в базе данных

Как создать и использовать составной индекс в Mysql

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

Анализ медленных PHP скриптов с помощью XHprof

Синтаксис и оптимизация Mysql LIMIT

Правильная настройка Mysql под нагрузки и не только. Обновлено.

Типы и способы применения репликации на примере MySQL

Запрос для определения версии Mysql: SELECT version()

Как работают индексы в Clickhose и как их использовать.

Настройка Master-Slave репликации на MySQL за 6 простых шагов

3 примера установки индексов в JOIN запросах

Быстрый подсчет уникальных значений за разные периоды времени

И как правильно работать с длительными соединениями в MySQL

Просмотр профиля запросов в Mysql

Анализ медленных запросов с помощью EXPLAIN

Правила выбора типов данных для максимальной производительности в Mysql

Описание, рекомендации и значение параметра query_cache_size

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

Источник

mysql не использует индексы, когда присутствует оператор » not in

у меня странная проблема. Пожалуйста, посмотрите на следующий запрос:

если я объясню этот запрос, он пройдет через все строки:

если я удаляю оба оператора» не в», то он работает нормально:

теперь запрос explain возвращает:

Я не уверен, почему это не работает правильно. Чего мне не хватает в указателях?

6 ответов

я не уверен, почему это не работает правильно. Чего мне не хватает в указателях?

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

мы можем заставить оптимизатор использовать объединение индексов для нас, что сделает его значительно быстрее. Вы может держать not in и не изменить ни одного из or заявления. Я провел несколько базовых тестов метода, который я использовал против метода union. Предостережения применяются, потому что ваша конфигурация БД может сильно отличаться от моей. Запуск запроса 1000 раз и выполнение этого 3 раза я взял лучшее время для каждого запроса.

оптимизированный запрос, показанный ниже

переписано как набор профсоюзы!—33—>

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

следующий SQL на несколько порядков быстрее в тестах на базе данных

4 миллионов строк. Ключевым изменением является следующая строка

эта одна строка значительно уменьшает набор данных, над которым mysql должен работать, потому что мы используем in вместо not in . Это новый запрос, я тоже пытался не изменять исходный запрос много.

план объяснения для этого выглядит совсем по-другому.

оптимизатор, будучи умным, сразу может видеть, что ему не нужна большая часть данных, потому что мы сказали ему использовать IN заявление с несколькими клавишами. Большинство оптимизаторов запросов придают высокую стоимость доступу к диску, поэтому все, что уменьшает это, обычно предпочтительно оптимизатором.

не в vs в

not in и in очень разные. Разница между ними в этом случае-шаблон доступа, нужны ли мне данные временно или как часть результирующего набора. Когда вы используете not in с несколькими ключами, и индекс содержит миллионы ключей, возможно, потребуется прочитать много записей, если данные являются частью результирующего набора. Даже при использовании индексов not in можно читать миллионы записей с диска. in С несколькими ключами, и это ключи, которые вам нужны, просят найти и использовать небольшое подмножество. Два шаблона доступа очень разные. Следующий пример может помочь прояснить это.

номер 2 быстрее, потому что из шаблона доступа ie я нашел 10 мне нужно, Nunmber 1. может понадобиться прочитать миллион записей.

оптимизатор запросов MySQL видит этот ie, последние два оператора OR запрашивают большие подмножества данных из таблицы или индекса ie case 1. выше. Видя это и тот факт, что ему нужно было использовать первичный ключ, оптимизатор решил было бы быстрее использовать первичный ключ.

при удалении not in вещи меняются ie теперь планировщик запросов может использовать индексы, потому что в двух других or пункты они действуют get me the few from the many и он выполняет index_merge на двух ключах, которые разделяют to и

Источник