Узнайте, использует ли MySQL индексы (1). Обязательно ли запросы диапазона используют индексы?

MySQL

Сначала подготовьте 4 версии базы данных, 5.5/5.6/5.7/8.0

Затем каждая библиотека имеет точно такую ​​же таблицу, тот же объем данных, тот же установленный индекс, механизм InnoDB.

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

  1. MySQL5.5

  2. MySQL5.6

  3. MySQL5.7

  4. MySQL8.0


    При сравнении видно, что объем статистических данных в основном одинаков.

В этой статье исследуется случай >,=,,=,

Оригинальные слова официального сайта следующие:
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

Запросите каждый индекс таблицы и используйте лучший индекс, если только оптимизатор не сочтет более эффективным использование сканирования таблицы.
Сканирование использования основано на том, охватывает ли лучший индекс более 30% таблицы, но фиксированный процент больше не определяет, следует ли использовать индекс или сканирование. Теперь оптимизатор стал более сложным, и его оценки основаны на других факторах, таких как размер таблицы, количество строк и размер блока ввода-вывода.

Примечание: в этом отрывке есть несколько моментов.

  1. Если в условии where задействовано несколько индексов, MySQL выберет оптимальный индекс. Лучший индекс — это самый избирательный индекс, поскольку он может отфильтровать множество бесполезных строк данных.
  2. значение шкалы 0,30. Возможно, он был >0,30 до того, как индекс не был взят, но минимальная версия документа — 5,6, и это невозможно проверить. Теперь это зависит не только от значения шкалы, но и от других факторов. Итак, проверьте следующее с > или >= или

Далее подготовьтесь к сушке.

  1. В MySQL 5.5 путем непрерывного тестирования был найден ключ номер 8.

    explain select user_name,user_age from user1 where user_age <= 8;
explain select user_id,user_name,user_age from user1 where user_age < 8;


    На второй картинке, хотя в Экстра явно не указано Использование индекса, но тип отображается как запрос диапазона диапазона, и количество просканированных строк 35042 строки, но на первой картинке тип: ВСЕ, строк: 210463 и полный статистика таблицы в начале статьи, первичный ключ Количество данных id одинаковое, количество данных первичного ключа id = сколько строк данных в полной таблице, видно что это полная сканирование таблицы.
    Разница только в том, включено ли значение 8, поэтому посчитайте количество данных


    Рассчитайте соотношение: user_age Соотношение user_age Из вышеизложенного видно: значение отношения = 0,1770 Оптимизатор считает, что полное сканирование таблицы выполняется быстрее, поэтому в индекс не идет.
    Тест больше ситуации (89 также постоянно проверяется)

    explain select user_name,user_age from user1 where user_age >= 89;
explain select user_id,user_name,user_age from user1 where user_age > 89;


    Вроде, посмотрите на статистику> = 89 и объем данных 89


    Значение шкалы для user_age > 89: 35894/210463 = 0,1705 (индекс ходьбы)
    Значение шкалы для user_age >= 89: (35894 + 2088)/210463 = 0,1804 (без индексации)
    Сочетая значение отношения user_age 89, можно узнать, что для версии 5.5 значение отношения столбца индекса user_age находится между 0,1705 и 0,1770.

    Частный случай 1, вы можете использоватьFORCE INDEXПринудительный индекс, независимо от значения масштаба

    explain select user_name,user_age from user1 force index(idx_user_age) where user_age < 50;
explain select user_name,user_age from user1 force index(idx_user_age) where user_age > 20;


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

    explain select user_age from user1 where user_age < 80;

  2. MySQL5.6 проверяет только случай 

    explain select user_name,user_age from user1 where user_age <= 8;
explain select user_id,user_name,user_age from user1 where user_age < 8;


    Также рассчитайте значение шкалы:
    user_age user_age Из этого можно сделать грубый вывод, что в версии 5.6 соотношение составляет от 0,1680 до 0,1786.
    Специальное примечание. На втором рисунке показано условие использования индекса, которое представляет собой оптимизацию, сделанную MySQL 5.6, ICP (перемещение условия индекса).По сравнению с версией 5.5, то, что отображается в Extra,Using where, что означает, что данные окончательно фильтруются на сервисном уровне MySQL. Версия 5.6 показывает, чтоUsing index condition, что означает, что бесполезные данные фильтруются на уровне механизма хранения.ICP может уменьшить количество раз, когда механизм хранения должен обращаться к базовой таблице, и количество раз, когда сервер MySQL должен обращаться к механизму хранения.
    Официальный адрес сайта следующий:Dev.MySQL.com/doc/Furious/…
    Аналогично, если вы используете FORCE INDEX, индекс обязательно будет взят, будет возвращен только столбец индекса, вне зависимости от значения шкалы, индекс все равно будет взят, и никакого отображения здесь не будет.

  3. MySQL5.7 проверяет только случай 

    explain select user_name,user_age from user1 where user_age <= 20;
explain select user_id,user_name,user_age from user1 where user_age < 20;


    Значение расчетного коэффициента следующее:
    user_age user_age Аналогично, если вы используете FORCE INDEX, индекс обязательно будет взят, будет возвращен только столбец индекса, вне зависимости от значения шкалы, индекс все равно будет взят, и никакого отображения здесь не будет.
    Специальное примечание: здесь появляется MRR, который здесь не обсуждается и будет подробно обсуждаться позже.

  4. MySQL8.0 проверяет только случай 

    explain select user_name,user_age from user1 where user_age <= 12;
explain select user_id,user_name,user_age from user1 where user_age < 12;


    Рассчитать значение масштаба:
    user_age user_age Отсюда можно сделать вывод, что пропорциональное значение находится между 0,2626 и 0,2725.
    Аналогично, если вы используете FORCE INDEX, индекс обязательно будет взят, будет возвращен только столбец индекса, вне зависимости от значения шкалы, индекс все равно будет взят, и никакого отображения здесь не будет.

Суммировать:

  1. Вместо индексирования столбца выполните индексирование этого столбца.запрос диапазонаПри , будет взят индекс, и он имеет пропорциональное значение. Значение шкалы зависит отВерсия, сервер, IO, объем данных, дублирование данныхи разные. То есть одна и та же версия, одна и та же библиотечная таблица, значение коэффициента может быть другим в этот раз и в следующий раз. Я столкнулся с этой проблемой в середине теста.
  2. В версии MySQL 5.6 были сделаны оптимизации, ICP и MRR, что значительно улучшило производительность. Позже будут примеры.
  3. Роль FORCE INDEX в особых случаях может возвращать только столбец индекса.
Категории