Обзор
Предыдущая статья《Как выполняется оператор sql в mysql«Мы говорили о внутреннем выполнении SQL-запросов, в том числе о том, как механизм InnoDB поддерживает транзакции, и о том, как его можно создавать резервные копии и восстанавливать. Итак, сегодня давайте поговорим об индексе MySql. структура данных индекса в InnoDB, как работает индекс и как лучше использовать индекс для повышения эффективности.
1. Что такое индекс
Индекс базы данных — это отсортированная структура данных в системе управления базами данных, помогающая быстро запрашивать и обновлять данные в таблицах базы данных. Так же, как каталог словаря Синьхуа, который мы использовали ранее, он может помочь нам быстро найти определенное слово.
Во-вторых, классификация индекса
| Перспектива классификации | имя индекса |
|---|---|
| структура данных | Дерево B+, хэш-индекс, R-дерево и т. д. |
| уровень хранения | кластеризованный индекс, некластеризованный индекс |
| логический уровень | Индекс первичного ключа, обычный индекс, составной индекс, уникальный индекс, пространственный индекс и т. д. |
3. Анализ экземпляра индекса (в качестве примера возьмем InnoDB)
3.1 Структура индекса в InnoDB
В InnoDB таблицы хранятся в виде индексов в соответствии с порядком первичных ключей.Этот метод хранения данных также называется кластеризованным индексом.«Кластеризация» означает, что строки данных и значения смежных ключей компактно хранятся вместе.То есть, строки данных фактически хранятся на листовых страницах индекса. Мы создаем таблицу, чтобы фактически проиллюстрировать структуру индекса в InnoDB, Оператор создания таблицы выглядит следующим образом:
create table person(
id int primary key,
age int not null
index (age)engine=InnoDB;
Затем мы вставляем пять частей данных как (1, 15), (2, 17), (6, 20), (10, 18), (19, 21) Древовидная структура индекса выглядит следующим образом:
На приведенном выше рисунке показаны две части.На первом рисунке показано содержимое кластеризованного индекса (индекса первичного ключа).Как видите, данные отсортированы по размеру идентификатора, и соответствующий индекс будет содержать всю строку данные индекса.
На втором рисунке показана диаграмма структуры индекса с использованием возраста в качестве индекса, то есть некластеризованного индекса (индекса без первичного ключа).Вы можете видеть, что индекс отсортирован по возрасту, но в отличие от индекса с первичным ключом возраст index соответствует Id , поэтому мы можем знать, что содержимое записи индекса непервичного ключа является значением индекса первичного ключа.
У некоторых учащихся могут возникнуть вопросы: если я не укажу первичный ключ при построении таблицы, какова будет структура индекса? Фактически, в InnoDB, если первичный ключ не определен, вместо этого он выберет уникальный ненулевой индекс. Если такого индекса нет, то он неявно определяет первичный ключ как кластеризованный индекс. Таким образом, установите ли вы первичный ключ или нет, InnoDB все равно поможет вам проиндексировать данные в форме приведенной выше диаграммы. Далее мы анализируем процесс индексного запроса.
3.2 Анализ индексных запросов
Предположим, мы выполняем оператор запросаselect * from person where ID = 6, Поскольку запрос идентификатора первичного ключа используется напрямую, будет использоваться индекс первичного ключа.Поскольку индекс первичного ключа напрямую связан со всеми данными во всей строке, механизм может запросить результат только после его однократного выполнения.
Если выполняемый оператор sql не является индексом первичного ключа
select * from person where age = 18
Вышеприведенный оператор будет использовать обычный индекс возраста.Индекс сначала ищет индексную запись, равную 18 в соответствии с возрастом, находит запись с идентификатором = 10, а затем ищет индекс первичного ключа один раз, а затем извлекает данные для спросил.
Процесс определения индекса первичного ключа из общего индекса и последующего запроса данных называется таблицей возврата.. Поскольку таблица возврата должна выполнить еще один запрос, поэтому индекс первичного ключа быстрее обычного индекса, мы должны стараться использовать запрос первичного ключа как можно больше.
3.3 Покрывающие индексы
Обычно мы создаем индекс на основе условия запроса where, но это всего лишь наша обычная практика.Из приведенного выше анализа мы можем узнать, что если мы хотим сделать запрос эффективно, во-первых, используйте индекс первичного ключа, а во-вторых, избегайте возврата в таблицу, то есть можно максимально получить нужные данные в индексе. Если индекс содержит поля, которые необходимо запрашивать, мы называем его «покрывающим индексом».
Итак, как построить индекс покрытия? Ответ заключается в том, чтобы достичь через совместный индекс, с помощью поля совместного индекса, чтобы покрыть запрашиваемое поле, чтобы добиться эффекта покрытия индекса.
Давайте преобразуем приведенный выше оператор построения таблицы, чтобы проанализировать, как реализовать покрывающий индекс.
CREATE TABLE `person` (
`id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Выше я создал совместный указатель имени и возраста, и схема структуры индекса выглядит следующим образом:
Из рисунка видно, что совместный индекс связан с порядком создания полей индекса.В приведенном выше примере сначала выполняется сортировка по имени, затем по тому же имени, а затем сортировка по возрасту. Итак, как мы можем добиться эффекта покрытия индекса после построения таблицы? Я полагаю, что некоторые студенты уже знают, как написать sql для достижения эффекта покрытия индекса.SQL выглядит следующим образом:
select name,age from person where name = "Barry"
Поскольку имя поля и возраст, которые нам нужно запросить, можно напрямую запросить в индексе, поэтому нет необходимости искать идентификатор первичного ключа, а затем возвращаться к таблице.
Увидев это, некоторые студенты обязательно скажут, а можно ли в таком случае построить совместный индекс для всех комбинаций полей, которые нужно запрашивать? Ответ - нет. Потому что индексы тоже занимают место, а обслуживание индексов тоже требует затрат, о которых я упомяну позже в преимуществах и недостатках. Итак, есть ли другой способ добиться эффекта невозвращения таблицы в максимально возможной степени? Здесь мы представим MySqlкрайний левый префикс.
Каков принцип самого левого префикса? То есть при сопоставлении индекса это могут быть крайние левые N полей индекса или крайние левые N символов строкового индекса. Например, на приведенном выше рисунке для запроса имен, начинающихся с буквы A, используется оператор запроса
select name from person where name like 'A%'
В это время вы можете использовать индексный запрос, чтобы удовлетворить правило крайнего левого префикса.Здесь вы будете полагаться на индекс для запроса имени, первая буква которого — A, а затем переходить назад, пока условие не будет выполнено.
Итак, что означает левые n поля? Это означает индекс (имя, возраст), вы можете использовать имя непосредственно как отдельный индекс, вы можете использовать только некоторые поля индекса совместного, но оно должно быть в том же порядке, таким как индекс (A, B, C), Если вы хотите использовать левые правила префикса, вы можете использовать индексы A, AB.
Мы также можем использовать это правило, чтобы поддерживать на один или несколько индексов меньше.Например, если нам нужны запросы a, ab, abc, то для удовлетворения требований необходим только совместный индекс (a, b, c).
3.4 Проталкивание индекса
В MySql версии 5.6 была введена новая функция, называемая «проталкивание условия индекса», которая также известна каквыталкивание индекса. Так что же такое проталкивание индекса вниз? На самом деле, из названия «передача условия индекса» можно показать, что эта функция может выполнять условную оценку полей в индексе, а затем фильтровать записи, которые не соответствуют условиям, уменьшая количество возвратов к Таблица.
Например, данные на рисунке выше имеют преимущественную силу, а sql выглядит следующим образом:
select * from person where name like 'A%' and age =19;
Затем, если нет проталкивания индекса, во-первых, все записи, имена которых начинаются с A, будут запрошены в соответствии с индексом, затем будет запрошен идентификатор, а затем будет возвращена таблица для запроса соответствующей записи идентификатора. Поскольку есть 2 записи, начинающиеся с A, в этом случае таблица будет возвращена дважды.
В случае проталкивания индекса InnoDB будет напрямую определять, удовлетворяет ли age=19 условию в индексе, и отфильтровывать записи, которые не удовлетворяют условию, поэтому возвращается только одна запись, то есть таблица должна быть только вернулся один раз. Тем самым повышая производительность.
3.5 Преимущества и недостатки индексации
Сказав так много об индексах, давайте поговорим о плюсах и минусах индексов.
преимущество:
- Уменьшите объем данных, которые сервер должен сканировать
- Индексы помогают серверу избежать сортировки и временных таблиц.
- Индекс может стать порядком случайного IO IO
недостаток
- Индексы занимают дополнительное место для хранения
- Техническое обслуживание индекса требует определенной стоимости. После вставки данных необходимо убедиться, что исходный индекс в порядке, поэтому он также повлияет на определенную производительность базы данных.
V. Резюме
В этом сообщении в блоге я в основном говорил об определении индексов, классификации индексов и общих типах индексов, которые можно разделить на разные аспекты. Затем я сосредоточился на структуре данных индекса, проиндексированного в InnoDB. Разница между индексом первичного ключа и индексом непервичного ключа заключается в том, что запрос индекса первичного ключа может напрямую возвращать данные, в то время как индексу непервичного ключа необходимо сначала запросить идентификатор первичного ключа, а затем запросить данные. называется таблицей возврата. Мы можем уменьшить количество возвратов к таблице, покрыв индекс, чтобы добиться эффекта повышения производительности. После MySQL 5.6 InnoDB может поддерживать выталкивание индекса.При использовании объединенного индекса, если вы можете оценить условие в индексе, затем отфильтруйте строки, которые не соответствуют условию в индексе, тем самым уменьшив количество возвращаемых таблиц.
6. Ссылка
- «Высокопроизводительный Mysql», 3-е издание
- Столбец "Лекция MySql45"