предисловие
Привет, милашки, после обновления последней длинной статьи я снова здесь.
Мы вырастили переднюю часть InnoDB из семи страниц данных, каждая страница данных представляет собой двусвязный список, записывается на каждой странице данных в соответствии с первичным ключом в порядке возрастания, чтобы сформировать единый связанный список, для каждой из этих страниц данных создается запись каталоге можно использовать бинарный поиск для повышения скорости запроса.
Тогда возникает вопрос, если записи в таблице содержат несколько страниц данных, как их найти?
поиск без индекса
Чтобы облегчить понимание, давайте сначала поговорим о ситуации с оператором SQL, который является простейшим точным запросом, следующим образом:
select [列名列表] from [表名] where 列名=XXX
Найти на странице
- Поиск по первичному ключу
- Поиск по другому столбцу
Поиск на нескольких страницах
Независимо от того, используется ли первичный ключ в качестве представления поиска, каталог на странице данных не может использоваться для двоичного поиска, а может выполняться только последовательный поиск и сравнение один за другим.
结果：这样查找速度肯定是慢的，我们得想一个提升速度的方法，那么索引就出现了。
индексированный поиск
Что такое индекс?
Давайте создадим счет и добавьте три оператора для него, утверждение выглядит следующим образом:
create table score(
id varchar(10),
name varchar(10),
score int,
primary key (id)
);
insert into score values('001','张三',100);
insert into score values('002','李四',90);
insert into score values('004','王五',70);Согласно статье, о которой идет речь, которая хранится на жестком диске, структура имеет следующий вид (при условии, что каждая страница хранит только три данных, остальная ненужная информация удаляется).
Если вы добавите еще один фрагмент данных, эта страница не поместится, вам нужно добавить новую страницу для хранения данных, как показано ниже.
На приведенном выше рисунке мы обнаружили, что 003 меньше, чем 004, но отстает, что не соответствует下一个数据页中用户记录的主键值必须大于上一页中用户记录的主键值Это стандарт, поэтому нам нужно обменять эту запись 003 на эту запись 004.
Зачем соответствовать этому стандарту? Мы можем использовать каталог для выполнения бинарного поиска внутри страницы, чтобы повысить скорость запроса, так можем ли мы также использовать бинарный поиск между страницами? Ответ положительный, поэтому он должен соответствовать критериям прямо сейчас, потому что предпосылка бинарного поиска состоит в том, что данные должны быть упорядочены.
Представьте, что если страниц данных много, мы объединяем большую страницу через каждые три страницы.Большая страница имеет всего три фрагмента данных, что соответствует минимальному значению маленькой страницы ниже, но даже в этом случае количество данных по-прежнему много.Слияние , таким образом образуя форму следующего рисунка, который является деревом B+.
Фактически, статистика других (ха-ха-ха, неправильно, хороший отторговчик, см. Хвост ссылки на статью), 6000000 Данные также 3 слоя, так, как правило, B + дерево не более четырех слоев (включая слой 3 и записи страниц. Страница элемента данных).
所以，索引是对按主键排列的数据进行速度提升的一种数据结构。我自己想的，非官方概念。
Вопросы для интервью за десять тысяч лет: почему дерево с индексом B+? вместо B-деревьев?
Прежде чем разобраться с этой проблемой, давайте посмотрим, что такое B-дерево и что такое B+-дерево? (картинку украду, сама рисовать не хочу)
B-tree: то есть B-дерево, главная особенность в том, что на нелистовых узлах есть не только указатели, но и поля данных.
Дерево B+: неконечные узлы имеют только указатели, без полей данных, механизм хранения индексов по умолчанию InnoDB.
Итак, вопрос в том, почему индекс использует дерево B+?
- Все листовые узлы дерева B+ связаны через двусвязный список (не спрашивайте меня, почему там нет той картинки, которую я сделал, потому что она была украдена у других, я добавил ее красной стрелкой), если я хочу для поиска диапазона, например, значения от 60. Когда оно достигает 66, его можно получить напрямую через указатель между листовыми узлами, что быстрее. Если используется B-дерево, необходимо использовать метод обхода по порядку, а скорость запроса низкая.
- Мы представляем до того, как данные делится на страницы, и слой innoDB-накопителя хранилищ в страницах данных памяти в партиях, данные, возвращаемые памятью, завершили обработку клиенту. Если в случае чтения того же количества страниц, B + дерево может хранить данные больше, потому что это только указатель, нет данных, занимает меньше памяти. B + Дерево, так что единственная информация IO IO больше B-дерева, поэтому количество дерева IO B + меньше, чем B-дерева, конечно, скорость также быстрее.
кластеризованный индекс
Выше приведен кластеризованный индекс, включающий две характеристики:
- Постройте древовидную структуру, используя первичный ключ, определенный в таблице. Записи на странице располагаются в порядке размера первичного ключа, в виде односвязного списка, а страницы связаны друг с другом в виде двусвязного списка. Например, первичным ключом приведенной выше таблицы результатов является id, тогда его кластеризованный индекс располагается в порядке id от меньшего к большему. Если я хочу проверить записи с id=XXX, я могу напрямую запросить кластеризованный индекс, используя двоичный метод, который может значительно повысить скорость запроса.
- Листовые узлы кластеризованного индекса хранят полные пользовательские записи, то есть в таблице оценок, помимо идентификатора первичного ключа, имя и оценка хранятся в конечных узлах.
注意：这一点我们在辅助索引（二级索引）说，因为聚簇索引存储的是完整的用户记录，总有什么索引存储的不是完整的用户记录。Вторичный индекс (Вторичный индекс)
Dangdang, вторичный индекс (вторичный индекс) прибыл.
Если мы хотим запрашивать записи с именем = xxx, мы можем только пересекать их по одному через узлы листьев первичного ключа кластерного индекса, а затем сравните, какое имя = xxx, этот метод представляет собой обход одностороннего запроса списка , не излюблен, что определенно медленно. Есть ли способ лучше?
Ответ определенно да, то есть построить индекс для столбца имени и расположить его в соответствии с именем от меньшего к большему, Это может быть то же самое, что и идентификатор первичного ключа, и данные можно быстро найти в форме классовой дихотомии.
Затем мы сначала строим индекс index_name по имени, оператор выглядит следующим образом:
alter table score add index index_name(name)
Индекс установился, а как вспомогательный индекс хранит данные?Простая версия, обратите на это внимание, хахаха.
Из приведенного выше рисунка мы можем обнаружить, что листовой узел вспомогательного индекса не имеет поля score, но имеет поле id первичного ключа, то есть данные его листового узла являются неполными. Затем, если мы хотим проверить три поля идентификатора, имени и оценки Ли Си, мы можем использовать все index_name на основе имени, использовать дихотомию класса, чтобы найти идентификатор первичного ключа записи Ли Си, а затем передать идентификатор первичного ключа к первичному ключу. Кластеризованный индекс находит полную информацию об этой записи, этот процесс называется回表.
注意：为什么要采用回表的形式呢？因为如果辅助索引的叶子节点存放的也是完整的记录，列存放的数据越大，对内存的消耗就越明显，越浪费空间。采用回表的方式，可以节省下空间，多浪费了一些时间。那么问题就来了，如果我采用辅助索引得出来的数据量很大，已经接近于所有数据，然后再根据各自的主键id去查看完整的记录，这样的时间消耗可以比我直接采用主键索引一个个遍历对比的时间消耗来的大，那么MySQL还会选择辅助索引吗？这个问题就是查询优化器的事情，下篇说，先立个flag，等着以后打脸。哈哈哈。。。。
совместный указатель
Например, я ищу имя = Джо Смит, оценка = 100 в этой записи, если кластеризованный индекс по идентификатору первичного ключа и выполняется только одно сравнение, эта скорость очень низкая. Или на основе имени вторичного индекса, но нет поля оценки вторичного индекса, поэтому также через обратный путь к таблице найдите поле оценки, но если имя = Джо Смит записал 100, то мы можем найти только 100 данных, а затем обратно к таблице один за другим, кстати, найдите эту запись score = 100. Таким образом, независимо от того, основано ли оно на кластерном индексе с идентификатором первичного ключа или вторичном имени на основе индекса, это не лучшее решение.
Таким образом, вы можете создать совместный индекс. Утверждение выглядит следующим образом. На самом деле, когда имя такое же, оно сортируется по счету. Индекс включает имя, счет и идентификатор первичного ключа. Соответствующая индексная карта выглядит следующим образом, просто, давайте посмотрим. Я очень старалась рисовать (для пробы добавила еще два данных, 005 и 006).
create index name_and_score_index on score (name,score);
注意：因为这边就只有三个字段，如果字段量多的话，也是需要回表，通过主键id得到其他字段信息。
Правильный способ открытия индекса
Опираясь на вышеприведенные теоретические знания, давайте попрактикуемся (из вышеизложенного можно сделать понятным).
Чтобы представить предысторию, таблица очков имеет три поля, а именно идентификатор, имя и счет. Он также имеет два индекса, один из которых представляет собой кластеризованный индекс, а другой — совместный индекс, основанный на имени и оценке.
Сначала посмотрите на следующую инструкцию, чтобы определить, можно ли использовать индекс для запроса. Если вы можете точно сказать, используется ли следующий индекс, то не читайте его ниже, просто скажите эти вещи.
select * from score where id=XXX; 聚簇索引
select * from score where name=XXX；联合索引
select * from score where score=XXX；不使用索引
select * from score where name=XXX and score=XXX; 联合索引
select * from score where score=XXx and name=XXX; 联合索引（经他人纠正，已改）
select * from score order by name; 联合索引
select * from score order by score;不使用索引
Первые пять операторов в основном используют принцип крайнего левого префикса. Излишне говорить, что если условием запроса за where является id, то он напрямую использует кластеризованный индекс и принимает метод дихотомии классов, то есть, начиная с корневого узла дерева, соответствующие записи могут быть быстро запрошены. Условие запроса за вторым, где есть имя, затем его также можно запросить на основе объединенного индекса. (Поскольку совместный индекс сначала сортируется по имени, а затем по количеству баллов, ряд данных находится по имени). Условие запроса после третьего where is score, то совместный индекс использовать нельзя, т.к. возможно разные имена имеют одинаковый score, тогда данные разбросаны по каждой странице, поэтому для их индексации можно использовать только кластерный индекс одно за другим. Перемещайтесь и сравнивайте поля. Четвертый и пятый могут попасть в совместный индекс.Принцип крайнего левого префикса предназначен для порядка индекса и не имеет ничего общего с порядком оператора SQL.
Последние два в основном используются для сортировки.Если оператор SQL сортируется по определенному полю, попробуйте позволить ему завершить сортировку на уровне индекса. Если сортировка не завершена на уровне индекса, то время и место в памяти будут потрачены впустую на выполнение ряда алгоритмов сортировки для реализации функции сортировки, что определенно повлияет на производительность. Возвращаясь к оператору SQL только что, если вы сортируете по имени, вы можете использовать индекс, потому что индекс сначала сортируется по имени, а затем индексируется по счету. Однако, если вы сортируете по оценке, вы не можете использовать индекс, потому что оценка сортируется позже, то есть только одно и то же имя будет отсортировано по оценке, но оператор SQL должен быть отсортирован по оценке полностью.
Эпилог
Кодовое слово не простое, пожалуйста, обратите на него больше внимания.
Справочная литература
Почему MongoDB (индекс) использует B-дерево, а Mysql использует дерево B+
Как работает MySQL