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

Два типа индексов

Используемый механизм хранения: MySQL5.7 InnoDB

кластеризованный индекс

* 如果表设置了主键，则主键就是聚簇索引
* 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
* 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

Листовой узел кластеризованного индекса InnoDB хранит записи строк (фактически это структура страницы, а страница содержит несколько строк данных) InnoDB должен иметь по крайней мере один кластеризованный индекс.

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

нормальный индекс

Обычные индексы также называются вторичными индексами, индексами, отличными от кластеризованных индексов, а именно некластеризованными индексами.

Листовой узел общего индекса InnoDB хранит значение первичного ключа (кластеризованный индекс), а общий индекс MyISAM хранит указатель записи.

Пример

построить таблицу

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;

Поле id представляет собой кластеризованный индекс, а поле age — общий индекс (вторичный индекс).

Ввод данных

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

mysql> select * from user;
+----+--------+------+
| id | name  | age |
+----+--------+------+
| 1 | 张三  |  30 |
| 2 | 李四  |  20 |
| 3 | 王五  |  40 |
| 4 | 刘八  |  10 |
+----+--------+------+

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

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

Кластерный индекс (ClusteredIndex)

age — это общий индекс (вторичный индекс), некластеризованный индекс, и его конечные узлы хранят значение кластеризованного индекса.

обычный индекс (secondaryIndex)

Если условием запроса является первичный ключ (кластеризованный индекс), вам нужно только один раз просмотреть дерево B+, чтобы найти данные записи строки, которые вы хотите найти с помощью кластеризованного индекса.

Например: выберите * от пользователя, где id = 1;

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

Если условием запроса является обычный индекс (некластеризованный индекс), вам необходимо дважды просмотреть дерево B+.Первое сканирование находит значение кластеризованного индекса через обычный индекс, а затем второе сканирование находит значение кластеризованного индекса. index, чтобы найти значение кластеризованного индекса. row записывает данные. Например: выберите * от пользователя, где возраст = 30;

1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据

Первый шаг обычного процесса поиска по индексу

Второй шаг обычного процесса поиска по индексу

запрос формы возврата

Сначала найдите значение кластеризованного индекса по значению общего индекса, а затем найдите данные записи строки по значению кластеризованного индекса.Ему нужно дважды просмотреть дерево индекса B+, и его производительность ниже, чем сканирование дерева индекса. однажды.

покрытие индекса

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

Например: выберите идентификатор, возраст от пользователя, где возраст = 10;

Как реализовать покрывающий индекс

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

1. Если реализовано: выберите идентификатор, возраст от пользователя, где возраст = 10;

Объяснение анализа: поскольку возраст является общим индексом, используется возрастной индекс, и соответствующие результаты могут быть запрошены путем однократного сканирования дерева B+, таким образом реализуя покрывающий индекс.

2. Реализация: выберите id, age, name у пользователя, где age = 10;

Объясните анализ: age — это общий индекс, но столбца name нет в дереве индексов, поэтому после запроса значений id и age через индекс age нужно вернуть таблицу, а затем запросить значение name. В это время значение NULL в столбце Extra указывает на то, что был выполнен запрос обратно к таблице.

Для достижения покрытия индекса необходимо построить составной индекс idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

Объясните анализ: в настоящее время поля age и name представляют собой комбинированный индекс idx_age_name, а значения запрошенных полей id, age и name только что находятся в дереве индексов.Вам нужно только просмотреть комбинированный индекс Дерево B+ один раз, что является реализацией индексного покрытия.Когда в поле Дополнительно указано Использование индекса, это означает, что индексное покрытие используется.

Какие сценарии подходят для использования индексного покрытия для оптимизации SQL

Полная оптимизация запросов подсчета таблиц

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> )engine=innodb charset=utf8mb4;

Например: выберите количество (возраст) от пользователя;

Используйте оптимизацию покрытия индекса: создайте индекс для поля возраста

create index idx_age on user(age);

Оптимизация таблицы возврата запроса столбца

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

Например: выберите идентификатор, возраст, имя пользователя, где возраст = 10;

Используйте покрытие индекса: создайте составной индекс idx_age_name(age,name)

Пейджинговый запрос

Например: выберите id,age,name из заказа пользователя по возрастному ограничению 100,2;

Поскольку поле имени не является индексом, в запросе на подкачку необходимо выполнить запрос обратно к таблице.В настоящее время Extra сортирует файл Using filesort, и производительность запроса низкая.

Используйте покрытие индекса: создайте составной индекс idx_age_name(age,name)