Индексация может эффективно повысить эффективность поиска в нашей базе данных.В различных статьях по оптимизации баз данных есть соответствующие знания, но об этом легко забыть просто по записи.
Поэтому Сонге хочет рассказать вам о правильном подходе к индексации через несколько статей и объединить несколько конкретных примеров, чтобы помочь вам понять оптимизацию индексов.Это небольшая серия статей, и их может быть несколько.Сегодня приходите к первой.
1. Независимость столбца индекса
Когда мы используем в качестве условия поиска столбец с индексом, нам нужно убедиться, что индекса нет в выражении, а индекс не содержит различных операций.
Позвольте мне привести простой пример, предположим, что у меня есть следующая таблица:
В пользовательской таблице четыре поля, и по каждому полю строятся индексы.Теперь тестовых данных три:
Давайте сравним следующие два запроса:
можно увидеть:
- Первый тип — ALL, что означает полное сканирование таблицы (индекс не используется), второй тип — ref, что означает поиск данных по индексу, Обычно при совпадении значений используется тип ref.
- Второй ключ указывает, какой индекс MySQL использует для оптимизации запроса; rows показывает количество строк, которые MySQL должен прочитать, чтобы найти желаемое значение.
- Первый экстра это
Using whereЭто означает, что этот поиск нужно судить (фильтровать) на уровне сервера, то есть уровень механизма хранения не может возвращать данные, удовлетворяющие условиям (конечно, здесь нет необходимости возвращать таблицу, потому что нет индекса вообще).
Как видно из вышеприведенного анализа, хотяage-1=98а такжеage=99Хотя логически то же самое, MySQL не может автоматически разрешать первое выражение, что делает первое неспособным использовать индекс.Поэтому не следует писать выражения в условии where, причем не только приведенные выше выражения, но и некоторые выражения, использующие свои функции, нужно стараться максимально упростить условие where.
Однако приведенный выше пример слишком надуманный, обычно люди не совершат такой ошибки, но следующий пример не обязателен, и некоторые мелкие партнеры могут попасть на него: столбец также является индексом):
На этом рисунке я дал две разные идеи запроса:
- Вычислить день рождения.Если к дате рождения добавляется один год, а полученное время больше текущего времени, это означает, что дата рождения пользователя находится в пределах последнего года.
- Вычислить текущую дату.Если время, полученное путем вычитания одного года из текущей даты, меньше дня рождения, это означает, что день рождения находится в пределах одного года.
По результатам объяснения на рисунке выше видно, что первая схема не использует индекс и выполняет полное сканирование таблицы, а вторая схема использует индекс и считывает только две строки данных. Причина в том, что MySQL не может использовать индекс, поскольку первая схема выполняет функциональные операции над столбцом индекса.
2. Используйте покрывающие индексы с умом
Вообще говоря, мы не рекомендуем использовать его непосредственно в запросе.select *,использоватьselect *Есть много проблем, одна из которых — невозможность воспользоваться сканированием покрытия индекса (covering indexes).
Тогда здесь нужно сначала понять, что такое покрывающий индекс.
существуетЧто такое «таблица возврата» MySQL?В этой статье Сонг Гэ говорил со всеми, что по способу физического хранения индексы можно разделить на кластеризованные индексы и некластеризованные индексы.
Индекс первичного ключа, который мы обычно называем, на самом деле является кластеризованным индексом; кроме индекса первичного ключа, другие индексы называются индексами непервичного ключа, а индексы непервичного ключа также называются вторичными индексами (вторичный индекс) или называются индексами. вспомогательный указатель.
Для индекса первичного ключа и индекса непервичного ключа используется структура данных B+Tree, единственное отличие состоит в том, что содержимое, хранящееся в листовом узле, отличается:
- Листовой узел индекса первичного ключа хранит полную строку данных.
- Листовые узлы индекса, не являющегося первичным ключом, хранят значение первичного ключа и значение столбца индекса.
Это самая большая разница между ними.
Таким образом, если при поиске используется индекс, не являющийся первичным ключом, всего будет выполняться поиск в двух B+деревьях.Первое B+дерево поиска получает значение первичного ключа, а затем B+дерево с индексом первичного ключа. Этот процесс представляет собой так называемую поверхность возврата. Однако если искомое поле находится на конечном узле вторичного индекса, нет ли необходимости возвращать таблицу? Давайте проверим.
Предположим, у меня есть следующая таблица:
CREATE TABLE `user2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`gender` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `username` (`username`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
id — первичный ключ, имя пользователя и адрес — составные индексы.
В этой таблице есть три записи:
Давайте проведем простой тест, сначала посмотрим на следующий SQL:
explain select username,address from user2 where username='javaboy';
В этом запросе SQL поля, которые мы запрашиваем, - это имя пользователя и адрес. Поскольку эти два поля являются составными индексами, они оба хранятся в листовых узлах B + Tree вторичного индекса. После поиска имени пользователя вы также можете получить адрес стоит того,Поэтому нет необходимости возвращать запрос таблицы. Обратите внимание, что индекс «Использование» в последнем дополнении — это то, что он означает.
Использование индекса означает использование сканирования покрытия индекса для возврата записей, прямую фильтрацию ненужных записей из индекса и возврат результатов совпадений.Это делается на уровне сервера MySQL, но нет необходимости возвращать таблицу для запроса записей.
По той же причине значение id также существует во вторичном индексе, и само собой разумеется, что нет необходимости возвращать таблицу, поэтому я немного изменил запрос SQL и добавил id.Давайте посмотрим:
explain select username,address,id from user2 where username='javaboy';
Видно, как мы думали.
Что, если я добавлю пол? Если запрашиваемое поле содержит пол, так как пол не хранится в конечном узле вторичного индекса, то вам нужно запросить таблицу обратно:
explain select gender from user2 where username='javaboy';
Видно, что Extra в это время пуст, и в то же время используется имя пользователя вторичного индекса, поэтому таблицу нужно вернуть в это время.
Это покрывающий индекс, умелое использование покрывающего индекса позволяет избежать возврата к таблице и повысить эффективность запроса. Поэтому старайтесь избегать использованияselect *(поскольку обычно невозможно построить составной индекс для всех полей).
Ладно, не знаю, поймут ли мои друзья, продолжим в следующей статье~