MySQL - оптимизация индекса

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

Структура пользовательской таблицы, используемая в этой статье для тестирования:

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

кардинальность

Количество уникальных ключей (distict_keys) для одного столбца называется кардинальностью.

SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;

Общее количество строк в пользовательской таблице равно 5, а мощность столбца «пол» равна 2, что указывает на наличие большого количества повторяющихся значений в столбце «пол», а мощность столбца «имя» равна общее количество строк, указывающее, что в столбце имени нет повторяющихся значений, что эквивалентно первичному ключу.

Вернуть масштаб данных:

В пользовательской таблице есть 5 частей данных:

SELECT * FROM user;

Запросите количество записей, пол которых равен 0 (мужской):

Тогда пропорциональное количество возвращенных записей:

Аналогичным образом запросите количество записей с именем 'swj':

Пропорциональное количество возвращаемых записей:

Теперь приходит проблема, предполагая, что имена и гендерные столбцы имеют индексы, затемSELECT * FROM user WHERE gender = 0; SELECT * FROM user WHERE name = 'swj';Можно индекс?

Детали индекса пользовательской таблицы:

SELECT * FROM user WHERE gender = 0;Индекса совпадений нет, обратите внимание, что значение filtered — это доля возвращенных записей, которую мы рассчитали выше.

SELECT * FROM user WHERE name = 'swj';Индекс index_name срабатывает, потому что запрашиваемую запись можно найти напрямую, пройдясь по индексу, поэтому значение filtered равно 100.

в заключении:

30 % данных в возвращаемой таблице будут проиндексированы, а если будет возвращено более 30 % данных, будет использовано полное сканирование таблицы. Конечно, этот вывод слишком абсолютен, и это не абсолютные 30%, а примерный диапазон.

форма возврата

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

Команда EXPLAIN приводит кUsing IndexЭто означает, что таблица не будет возвращена, а основные данные можно будет получить через индекс.Using WhereЭто означает, что данные нужно вернуть в таблицу.

Оптимизация индекса на практике

Иногда в базе данных есть индекс, но он не выбирается для использования оптимизатором.

мы можем пройтиSHOW STATUS LIKE 'Handler_read%';Проверить использование индекса:

Handler_read_key: Значение Handler_read_key будет высоким, если работает индексация.

Handler_read_rnd_next: количество запросов на чтение следующей строки в файле данных.Если выполняется большое количество просмотров таблицы, значение будет высоким, что указывает на неидеальное использование индекса.

Правила оптимизации индекса:

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

   Доля возвращаемых данных — важный показатель, и чем ниже доля, тем легче попасть в индекс. Запомните это значение диапазона — 30%, следующее содержимое основано на соотношении возвращаемых данных в пределах 30%.

2. Ведущий нечеткий запрос не может попасть в индекс

   Столбец имени создает обычный индекс:

   

   Ведущий нечеткий запрос не может попасть в индекс:

   EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';

   

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

   EXPLAIN SELECT * FROM user WHERE name LIKE 's%';

   

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

   EXPLAIN SELECT * FROM user WHERE name=1;

   

   EXPLAIN SELECT * FROM user WHERE name='1';

   

   ​

4. В случае составного индекса условие запроса не включает крайнюю левую часть столбца индекса (крайний левый принцип не выполняется), и индекс не будет достигнут.

   Создайте составной индекс для столбцов имени, возраста и статуса:

   ALTER TABLE user ADD INDEX index_name (name,age,status);

   

   Детали индекса пользовательской таблицы:

   SHOW INDEX FROM user;

   

   По крайнему левому принципу можно попасть в составной индекс index_name:

   EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;

   

   Обратите внимание, что крайний левый принцип не означает порядок условий запроса:

   EXPLAIN SELECT * FROM user WHERE status=1 AND name='swj';

   

   Но содержит ли условие запроса самое левое поле столбца индекса:

   EXPLAIN SELECT * FROM user WHERE status=2 ;

   

5. union, in или все могут попасть в индекс, рекомендуется использовать in.

   union:

   EXPLAIN SELECT * FROM user WHERE status = 1

   UNION ALL

   SELECT * FROM user WHERE status = 2;

   

   in:

   EXPLAIN SELECT * FROM user WHERE status IN (1,2);

   

   or:

   EXPLAIN SELECT * FROM user WHERE status=1 OR status=2;

   

   Потребление ЦП запросом: или > in > union

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

   EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

   

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

7. Отрицательные условные запросы не могут использовать индексы и могут быть оптимизированы, как и запросы.

   К отрицательным условиям относятся: !=, , не в, не существует, не нравится и т. д.

   Создайте индекс в столбце состояния:

   ALTER TABLE user ADD INDEX index_status (status);

   

   Детали индекса пользовательской таблицы:

   SHOW INDEX FROM user;

   

   Отрицательные условия не могут попасть в кеш:

   EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;

   

   Его можно оптимизировать как in-запрос, но исходить из того, что степень дискриминации высока, а доля возвращаемых данных находится в пределах 30%:

   EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);

   

8. Условный запрос диапазона может попасть в индекс

   Условия диапазона: , >=, между и т. д.

   Столбцы статуса и возраста создают индексы соответственно:

   ALTER TABLE user ADD INDEX index_status (status);

   

   ALTER TABLE user ADD INDEX index_age (age);

   

   Детали индекса пользовательской таблицы:

   SHOW INDEX FROM user;

   

   Условные запросы диапазона могут попасть в индекс:

   EXPLAIN SELECT * FROM user WHERE status>5;

   

   Столбцы диапазона могут быть проиндексированы (объединенный индекс должен быть крайним левым префиксом), но столбцы после столбца диапазона не могут быть проиндексированы. Индекс можно использовать не более чем для одного столбца диапазона. Если в условии запроса два столбца диапазона, индекс не может использоваться для всех. :

   EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;

   

   Если запрос диапазона и эквивалентный запрос существуют одновременно, индекс эквивалентного столбца запроса сопоставляется первым:

   EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

   

9. База данных выполняет расчет, не затрагивая индекс

   EXPLAIN SELECT * FROM user WHERE age > 24;

   

   EXPLAIN SELECT * FROM user WHERE age+1 > 24;

   

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

10. Используйте покрывающий индекс для запроса, чтобы избежать возврата к таблице

Для запрашиваемого столбца данные можно получить из индекса, вместо перехода к строке через row-locator, то есть «опрашиваемый столбец должен быть покрыт построенным индексом», что может ускорить запрос.

Детали индекса пользовательской таблицы:

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

Using IndexПредставляет запрос из индекса

EXPLAIN SELECT status FROM user where status=1;

При запросе других столбцов вам необходимо выполнить запрос обратно к таблице, поэтому вам следует избегатьSELECT *Одна из причин:

EXPLAIN SELECT * FROM user where status=1;

1. Столбец для индексации, null не допускается

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

   Проиндексируйте столбец примечаний:

   ALTER TABLE user ADD INDEX index_remark (remark);

   

   IS NULL может попасть в индекс:

   EXPLAIN SELECT * FROM user WHERE remark IS NULL;

   

   IS NOT NULL не может попасть в индекс:

   EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

   

   Хотя IS NULL может попасть в индекс, сам по себе NULL не является хорошей структурой базы данных, и следует использовать ограничение NOT NULL и значение по умолчанию.

2. Индексация не подходит для полей, которые обновляются очень часто.

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

3. Индексация не должна устанавливаться на полях с небольшой дискриминацией

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

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

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

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

6. Избегайте следующих заблуждений при создании индексов

   • Чем больше индексов, тем лучше, считается, что запрос должен построить индекс.

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

   • Сопротивляйтесь уникальному индексу и думайте, что уникальность бизнеса нужно решать методом «сначала проверить, а затем вставить» на прикладном уровне.

   • Оптимизация преждевременно, оптимизация без знания системы.

Суммировать

Для оператора SQL-запроса, написанного вами, попробуйте использовать команду EXPLAIN для его анализа и будьте программистом, стремящимся к производительности SQL. Для измерения надежности программиста важным показателем является умение работать с SQL. Как back-end программист, я воспринимаю это как должное.

Ссылаться на

• «Углубленный MySQL»