Как работают индексы MySQL

Отсканируйте QR-код ниже или WeChat, чтобы найти официальную учетную запись.菜鸟飞呀飞, вы можете следить за публичной учетной записью WeChat, читать дальшеSpring源码分析,Java并发编程,Netty源码系列иMySQL工作原理статья.

Индекс — это структура данных, которая ускоряет выполнение запросов. В MySQL структура данных индекса — B+Tree.Что касается B+Tree и почему MySQL выбирает B+Tree в качестве индекса, вы можете проверить переднюю часть официальный аккаунт три статьи.

• B-дерево и B+дерево структуры данных индекса (часть 1)

• B-дерево и B+дерево структуры данных индекса (часть 2)

• Почему MySQL не использует в качестве индексов такие структуры данных, как массивы, хеш-таблицы и двоичные деревья?

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

Во-первых, он должен быть описан, все обсуждения в этой статье основаны на механизме хранения InnoDB.

Пример таблицы

Для иллюстрации давайте сначала создадим пример таблицы. Оператор создания таблицы выглядит следующим образом

CREATE TABLE user (
`id` BIGINT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 64 ) COMMENT '姓名',
`age` INT ( 4 ) COMMENT '年龄',
PRIMARY KEY ( `id` ),
INDEX ( NAME )
) ENGINE = INNODB COMMENT '用户表';

INSERT INTO `user` ( `name`, `age` ) VALUES ( 'AA', 30 ),( 'BB', 33 ),( 'CC', 31 ),( 'DD', 30 ),( 'EE', 29 )

В приведенном выше операторе SQL создается пользовательская таблица с тремя полями, id является первичным ключом, name и age представляют имя и возраст пользователя соответственно, а также создается общий индекс для имени поля. Чтобы облегчить описание позже, в таблицу также вставлены части данных 5. Поскольку идентификатор первичного ключа является самоувеличивающимся, значения идентификатора этих пяти строк данных разделены на 1 ~ 5.

индекс первичного ключа

Индекс первичного ключа, также известный как кластерный индекс, характеризуется тем, что данные строки, соответствующей текущему первичному ключу, хранятся в листовом узле. Что это обозначает? Возьмите приведенный выше пример, чтобы проиллюстрировать, что в таблице user id является индексом первичного ключа, поэтому будет индексное дерево id, в листовом узле индексного дерева не только значение идентификатора первичного ключа, но и имя и значение являются сохраненным значением . Например: в данных строки с id=1 значения имени и возраста равны AA и 30, тогда в дереве индексов в узле id=1 значение (1,"AA" ,30) хранятся три значения. Схематическая диаграмма дерева индексов идентификаторов выглядит следующим образом.

Давайте посмотрим на поток выполнения этого оператора SQL:

select * from user where id = 1;

Заявление добавлено ID = 1, где условия в состоянии фильтра, которые используют идентификатор дерева индекса первичного ключа.

1. Выберите использование первичного ключа id для индексации дерева;
2. Найдите узел первого уровня дерева индексов id (узел, в котором расположены ключевые слова 3 и 7).Поскольку в условии where id=1, 1 меньше 3, то идем в левое поддерево ключевого слова 3 для поиска;
3. Введите узел второго уровня дерева индексов идентификаторов.Узел второго уровня является конечным узлом.Конечный узел хранит данные таблицы, и есть ключевое слово с идентификатором=1, поэтому возвращается R1. (R1 представляет данные строки с id=1).

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

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

Давайте посмотрим на поток выполнения этого оператора SQL:

select * from user where name = 'BB';

Этот оператор добавляет условие фильтра name='BB' к условию where.Поскольку мы создали индекс для поля имени при построении таблицы, мы будем использовать дерево индекса имени. Кроме того, поскольку мы используем select *, то есть значения всех полей в таблице запроса, а в дереве индекса имени хранится только значение id первичного ключа, что не может удовлетворить потребности запроса всех полей, а данные всех полей хранятся в индексном дереве идентификатора первичного ключа, поэтому после нахождения значения идентификатора первичного ключа в индексном дереве имен необходимо также выполнить поиск значения других полей в эта строка записей в дереве индекса первичного ключа в соответствии с найденным значением идентификатора.Этот процесс Мы называем его формой возврата. (Процесс возврата из обычного индексного дерева в поиск по первичному ключевому индексному дереву называется возвратом к таблице). Таким образом, поток выполнения приведенного выше оператора SQL выглядит следующим образом:

1. Выберите использование имени дерева индексов;
2. Найдите узел первого уровня дерева индексов.Поскольку значение «BB» в условии where меньше значения ключевого слова «CC» в узле первого уровня, индекс входит в левое поддерево ключевого слова « CC' для поиска;
3. Введите конечный узел второго слоя и найдите ключевое слово "BB". Поскольку данные идентификатора первичного ключа хранятся в листовом узле, значение идентификатора первичного ключа в "BB" возвращается как 2;
4. В соответствии с первичным ключом id=2 перейти к дереву индексов первичного ключа id, чтобы найти данные R2, соответствующие id=2;
5. Продолжайте поиск в обратном направлении в дереве указателя имен, найдите следующее ключевое слово «CC» из «BB», обнаружите, что «CC» не равно «BB» в условии «где», поэтому завершите поиск.

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

Для второго приведенного выше примера, поскольку только одна запись Name = 'bb', в таблицу возвращается только таблица, и если есть несколько записей, соответствующих условию NAME = 'BB', то выполняется несколько операций резервного копирования. Очевидно, что чем больше количество возвратов, тем медленнее выполнение SQL, как избежать возврата? Ответ переопределен index.

Индекс покрытия - что это такое? Во втором примере выше мы используем select * для запроса всех полей, что, если нам не нужны все поля, только поле id? Например, выберите идентификатор от пользователя, где имя = «BB»; поскольку имя конечного узла индексного дерева уже присутствует в значении первичного ключа идентификатора, имя индексного дерева напрямую соответствует нашим требованиям и, следовательно, не должно возвращаться. в таблицу в это время операции, и в этом случае мы называем это покрывающим индексом.

Покрывающие индексы могут значительно повысить производительность запросов, поскольку они могут значительно сократить большое количество операций возврата таблицы. Покрывающий индекс — это очень распространенный метод оптимизации SQL, к тому же он очень прост в использовании. В процессе разработки мы обычно не рекомендуем использовать select * для запроса данных, так как, с одной стороны, когда объем данных большой, select * может вернуть много бесполезных полей, тратя сетевые ресурсы, с другой стороны, это также максимально использовать покрывающий индекс.

Совместный индекс и принцип крайнего левого соответствия

Если теперь у нас есть требование запросить имя и возраст человека с именем = «BB» в пользовательской таблице, наш SQL должен быть написан следующим образом:

select name,age from user where name = 'BB';

Очевидно, что дерево индексов имен также будет использоваться в это время, а поскольку дерево индексов имен не хранит информацию о поле age, необходимо вернуться к таблице и вернуться к дереву индексов идентификатора первичного ключа. чтобы получить значение поля возраста. Так есть ли способ оптимизировать его? Пусть по этому запросу не нужно возвращаться к таблице. Должно быть! Используйте покрывающий индекс. Как это использовать?

Когда мы создаем индекс имени, мы фактически создаем индекс с одним столбцом (выбирается только столбец имени), а в MySQL мы можем выбрать несколько столбцов для создания индекса при создании индекса Это называется совместным индексом. Например: теперь мы создаем совместный индекс для поля имени и поля возраста, и оператор выполнения выглядит следующим образом:

# 为了不影响测试，我们先将之前的name字段的索引删除
alter table user drop index `name`;
# 创建name、age的联合索引
alter table user add index(`name`,`age`);

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

Затем в это время, когда мы выбираем имя, возраст от пользователя, где имя = «BB», поскольку требуемое поле имени и поле возраста уже существуют в этом объединенном индексном дереве, этому запросу не нужно возвращаться в таблицу.

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

select name,age from user where name = 'BB' and age = 33; # 在使用联合索引时，会依次匹配name列和age列。
select name,age from user where name like 'B%' and age = 33; # 在使用联合索引时，当匹配到name这一列的时候，由于name使用了like范围查找，因此后面不会再匹配age这一列了。
select name,age from user where age = 33; # 在使用联合索引时，由于联合索引的最左列为name列，而我们在where条件中匹配的是age列，因此不满足最左匹配原则，所以该条SQL会进行该联合索引的全表扫描。

Почему MySQL следует принципу самого левого сопоставления? Это связано с тем, что в B+Tree данные на всех узлах упорядочены.Когда мы создаем объединенный индекс, мы сначала обеспечиваем упорядоченность первого столбца всех данных, а затем проверяем, чтобы были упорядочены второй и третий столбцы. и следующие столбцы упорядочены. Взяв в качестве примера совместный индекс в пользовательской таблице выше, в дереве индексов столбец имени упорядочен по всем данным, но столбец возраста не упорядочен. Только когда имена совпадают, возраст упорядочен. Когда мы ищем данные, если мы сталкиваемся с поиском по диапазону, поскольку нельзя гарантировать, что следующие столбцы будут в порядке, мы не можем продолжать выполнять сопоставление равных значений, и мы можем только выполнить полное сканирование таблицы для следующих столбцов.

Суммировать

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

использованная литература

• «Высокопроизводительный MySQL»
• Geek Time Линь Сяобинь "MySQL Практические боевые 45 лекций"