"Быстро найти неэффективный SQL?«Оставив хвост:
select id,name where name='shenjian'
select id,name_,sex_ where name='shenjian'
Запрашивается еще один атрибут, почему процесс поиска совершенно другой?
Что такое запрос обратной формы?
Что такое покрытие индекса?
Как добиться покрытия индекса?
В каких сценариях можно использовать индексное покрытие для оптимизации SQL?
Это то, чем я хочу поделиться сегодня.
Голос за кадром: Эксперимент в этой статье основан на MySQL5.6-InnoDB.
1. Что такое запрос формы возврата?
Это начинается с реализации индекса InnoDB.InnoDB имеет два типа индексов:
-
кластеризованный индекс
-
вторичный индекс
**В чем разница между кластерным индексом InnoDB и обычным индексом?
**
InnoDB кластеризованный индексЛистовые узлы хранят записи строк, поэтому InnoDB должен иметь и только один кластеризованный индекс:
(1) Если таблица определяет ПК, то ПК является кластеризованным индексом;
(2) Если таблица не определяет PK, первый уникальный столбец, отличный от NULL, является кластеризованным индексом;
(3) В противном случае InnoDB создаст скрытый идентификатор строки как кластеризованный индекс;
Голос за кадром: Таким образом, PK-запрос выполняется очень быстро, непосредственно определяя местонахождение записи строки.
InnoDB нормальный индексЛистовые узлы хранят значение первичного ключа.
Голос за кадром: Обратите внимание, что вместо хранения указателей на заголовки записей в конечных узлах индекса MyISAM хранятся указатели на записи.
Например, пусть у нас есть таблица:
t(id PK, name KEY, sex, flag);
Голос за кадром: id — это кластеризованный индекс, а name — обычный индекс.
В таблице четыре записи:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
Два индекса дерева B+ показаны выше:
(1) id — это PK, кластеризованный индекс, листовые узлы хранят записи строк;
(2) имя KEY, общий индекс, конечный узел хранит значение PK, а именно id;
Поскольку записи строк не могут быть найдены напрямую из обычных индексов, тоКаков процесс запроса обычного индекса?
Обычно вам нужно дважды просмотреть дерево индексов.
Например:
select * from t where name='lisi';
Как это работает?
какРозовыйPath, вам нужно дважды просканировать дерево индексов:
(1) Сначала найдите значение первичного ключа id=5 с помощью обычного индекса;
(2) Найдите запись строки с помощью кластеризованного индекса;
Это называетсязапрос формы возврата, сначала найдите значение первичного ключа, а затем найдите запись строки, его производительность ниже, чем однократное сканирование дерева индекса.
Во-вторых, что такое покрытие индекса (Индекс покрытия)****?
Что ж, арендодатель не нашел этого понятия на официальном сайте MySQL.
Голос за кадром: Вы прилежны в учебе?
Заявление позаимствовать с официального сайта SQL-Server.
На официальном веб-сайте MySQL аналогичное утверждение появляется в главе об оптимизации плана запроса объяснения, то есть, когда дополнительное поле выходного результата объяснения имеет значение Использование индекса, может быть активировано покрытие индекса.
Будь то официальный сайт SQL-Server или официальный сайт MySQL, говорится, что все данные столбца, требуемые SQL, могут быть получены только в дереве индексов, нет необходимости возвращать таблицу, а скорость выше.
3. Как добиться покрытия индексом?
Обычный метод заключается в том, чтобы встроить запрашиваемое поле в совместный индекс.
все еще "Быстро найти неэффективный SQL?" в примере:
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
Первый оператор SQL:
select id,name from user where name='shenjian';
Он может попасть в индекс имени, конечный узел индекса хранит идентификатор первичного ключа, а идентификатор и имя можно получить через дерево индекса имени, не возвращаясь к таблице, в соответствии с охватом индекса и высокой эффективностью.
Голос за кадром, экстра:Using index.
Второй оператор SQL:
select id,name_,sex_ from user where name='shenjian';
Можно попасть в индекс имени, а идентификатор первичного ключа хранится в конечном узле индекса, но поле пола можно получить только путем запроса таблицы, что не соответствует покрытию индекса.
Голос за кадром, экстра:Using index condition.
Если индекс с одним столбцом (имя) будет обновлен до общего индекса (имя, пол), он будет другим.
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
можно увидеть:
select id,name ... where name='shenjian';
_select id,name,_sex ... where name='shenjian';
Все могут достичь покрытия индекса, не возвращаясь к таблице.
Голос за кадром, экстра:Using index.
4. В каких сценариях можно использовать покрытие индекса для оптимизации SQL?
Сценарий 1. Оптимизация запросов полного подсчета таблиц
Исходная таблица такая:
пользователь (идентификатор ПК, имя, пол);
непосредственный:
select count(name) from user;
Покрытие индекса использовать нельзя.
Добавить индекс:
alter table user add key(name);
Вы можете использовать покрытие индекса для повышения эффективности.
Сценарий 2. Оптимизация таблицы возврата запроса столбца
select id,name,sex ... where name='shenjian';
Этот пример здесь повторяться не будет.Одностолбцовый индекс (имя) обновляется до объединенного индекса (имя, пол), чтобы не возвращаться к таблице.
Сценарий 3. Пейджинговый запрос
select id,name,sex ... order by name limit 500,100;
Обновление одностолбцового индекса (имя) до объединенного индекса (имя, пол) также позволяет избежать возврата к таблице.
Кластерный индекс InnoDB Обычный индекс,форма возврата,покрытие индекса, Я надеюсь, что эта 1 минута у каждого есть что-то выиграть.
Подскажите, если вам непонятно значение использования индекса в дополнительном поле результата объяснения, прочтите предисловие к статье: 《Как использовать инструменты для быстрого обнаружения неэффективного SQL?》
Путь архитектора- Поделитесь технологией, которую можно внедрить
связанное предложение:
"Буферный пул (буферный пул), на этот раз я полностью понимаю!》
"Буфер записи (буфер изменения), на этот раз я полностью понимаю!》