Можете ли вы придерживаться других, что вы не можете упорствовать, вы не можете иметь других людей, которых не было.
обрати внимание на编程大道Официальный аккаунт, давайте придерживаться того, что мы думаем, и расти вместе! !
введение
предыдущий пост«MySQL индексирует эти вещи»В основном рассказывается об основном принципе индекса MySQL и сравниваются преимущества B+Tree как базовой структуры данных индекса по сравнению с другими структурами данных (двоичное дерево, красно-черное дерево, B-дерево) и, наконец, описывается хранилище. индекса в качестве иллюстрации структура. Но все они основаны на однозначных индексах.Из-за большого объема статьи совместный индекс лишь кратко упоминается в конце статьи, и подробного обсуждения нет, поэтому в этой статье речь пойдет о структуре хранения совместный индекс на дереве B+.
Основное содержание этой статьи:
Структура хранения совместного индекса на дереве B+
Как найти общий индекс
Почему существует принцип соответствия крайнего левого префикса?
Перед тем, как поделиться этой статьей, я поискал в Интернете информацию о структуре хранения объединенного индекса MySQL в дереве B+ и прочитал много блогов и технических статей, и некоторые из них утверждали, что это противоречит действительности. К счастью, я увидел, что поисковая система перечислила вопрос и ответ от сообщества Sifu, ответчик ответил на этот вопрос и разместил статью, изображение и простое описание. PS: Ссылка на размещенную статью не открылась.
Вот в таких условиях и родилась эта статья.
Структура хранения объединенного индекса
Давайте обратитесь к этому вопросу и ответу от сообщества Sifu расширить структуру хранения совместного индекса, который мы собираемся обсудить сегодня.
Совместный индекс bcd выглядит в дереве индексов, как показано на рисунке В процессе сравнения сначала оценивается b, затем оценивается c и затем d,
Поскольку ответом является только одна картинка и одно предложение, это может вас немного запутать, поэтому давайте на этом примере более подробно изучим структуру хранения объединенного индекса на дереве B+ с помощью плеч предшественников.
Во-первых, в таблице T1 есть поля a, b, c, d, e, где a — это первичный ключ, за исключением того, что e — это varchar, остальные — это типы int, и создается объединенный индекс idx_t1_bcd(b, c, d), и затем b, c. Три столбца d используются в качестве общего индекса, а структура дерева B+ показана на рисунке выше. Все индексные столбцы объединенного индекса отображаются по номеру индекса, и размеры трех столбцов сравниваются по очереди. Высота дерева на приведенном выше рисунке составляет всего два слоя, что нелегко понять.Ниже приведены данные гипотетической таблицы и мое улучшение структурной диаграммы ее совместного индекса на дереве B+.PS: на основе механизма хранения InnoDB
Табличные данные таблицы T1 следующие:
Структура и данные sql таблицы следующие:
/*
Navicat Premium Data Transfer
Source Server : mysql57
Source Server Type : MySQL
Source Server Version : 50727
Source Host : localhost:3306
Source Schema : text_explain
Target Server Type : MySQL
Target Server Version : 50727
File Encoding : 65001
Date: 24/07/2020 13:01:36
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NULL DEFAULT NULL,
`c` int(11) NULL DEFAULT NULL,
`d` int(11) NULL DEFAULT NULL,
`e` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX `index_bcd`(`b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (1, 13, 12, 4, 'dll');
INSERT INTO `t1` VALUES (2, 1, 5, 4, 'doc');
INSERT INTO `t1` VALUES (3, 13, 16, 5, 'img');
INSERT INTO `t1` VALUES (4, 12, 14, 3, 'xml');
INSERT INTO `t1` VALUES (5, 1, 1, 4, 'txt');
INSERT INTO `t1` VALUES (6, 13, 16, 1, 'exe');
INSERT INTO `t1` VALUES (7, 5, 3, 6, 'pdf');
SET FOREIGN_KEY_CHECKS = 1;
Структурная схема индекса соединения bcd на дереве B+:
Благодаря этим двум рисункам у нас есть общее представление о структуре хранения объединенного индекса в дереве B+. Позвольте мне объяснить это вам на моем бледном языке ниже 😝
Давайте сначала посмотрим на таблицу T1, его первичный ключ будет установлен на整型自增из(PS: Что касается того, почему это глава о целочисленном самоувеличении«MySQL индексирует эти вещи»Есть подробное введение, больше рассказывать не буду), InnoDB будет использовать индекс первичного ключа для поддержки индексов и файлов данных в дереве B+, а затем мы создадим联合索引（b，c，d）Также будет сгенерировано дерево индексов, которое также находится в структуре дерева B+, за исключением того, что его часть данных хранит значение первичного ключа строки, в которой расположен объединенный индекс (фиолетовая фоновая часть конечного узла на рисунке выше), почему часть данных вспомогательного индекса хранит значение первичного ключа«MySQL индексирует эти вещи»Есть также введения, если вы заинтересованы или еще не знаете, вы можете проверить их.
Ну, в общем, ситуация исчерпана. Ниже мы объединим эти два рисунка, чтобы объяснить.
Для объединенного индекса всего на несколько столбцов больше, чем для индекса с одним значением, и все эти столбцы индекса отображаются в дереве индекса. Для совместного индекса механизм хранения сначала сортирует по первому столбцу индекса.Как показано на рисунке выше, мы можем смотреть только на первый столбец индекса и смотреть по горизонтали, например,1 1 5 12 13.... он монотонно возрастает, если первый столбец равен, то сортировать по второму столбцу, и так далее, чтобы сформировать индексное дерево рисунка выше, когда столбец b на рисунке выше равен 1, затем отсортируйте по c. В это время столбец c также равен, а затем он сортируется по столбцу d, например:1 1 4,1 1 5, c=4 предшествует c=5, и13 12 4,13 16 1,13 16 5может проиллюстрировать эту ситуацию.
Как найти общий индекс
Когда наш язык SQL можно применить к индексам, таким какselect * from T1 where b = 12 and c = 14 and d = 3;То есть запись, чья колонна в таблице T1 составляет 4. Сначала поиск двигателя хранения из корневого узла (обычно резидент в памяти). Первый индексной столбец первого индекса составляет 1, 12 больше 1, а первый столбец второго индекса составляет 56, а 12 меньше, чем 56, поэтому прочитайте адрес файла диска следующего узла с середины этих двух индексов, загрузите узел с диска, обычно сопровождаемый дисковым IO, а затем посмотрите на память. При загрузке второго узла узла листьев является еще одним диском IO, сравните первый элемент, B = 12, C = 14, D = 3, полностью согласуются, поэтому найдите элемент данных в соответствии с индексом, то есть значение идентификатора, И тогда из окончательных данных находятся на главном клавише.
Принцип сопоставления крайнего левого префикса
Причина, по которой существует принцип сопоставления крайнего левого префикса, связана с методом построения индекса и структурой хранения объединенного индекса.
Сначала мы создалиindex_bcd(b,c,d)Индекс эквивалентен созданию трех индексов (b), (b, c) (b, c, d).Прочитав следующее, вы поймете, почему это эквивалентно созданию трех индексов.
Как мы видим, объединенный индекс представляет собой индексное дерево, построенное с использованием в первую очередь первого столбца многостолбцового индекса.В приведенном выше примере idx_t1_bcd(b,c,d) он строится с использованием в первую очередь столбца b. значения столбца b равны, они сортируются по столбцу c., если значения в столбце c также равны, то сортируются по столбцу d. Мы можем взглянуть на листовые узлы дерева индексов.
Можно сказать, что первый столбец индекса, то есть столбец b, монотонно возрастает слева направо, но мы видим, что столбцы c и d не обладают этой особенностью, они могут увеличиваться только в небольшом диапазоне, когда значения столбца b равны, например, первый и второй элементы первого конечного узла и последние три элемента второго конечного узла.
Поскольку совместный индекс представляет собой метод построения индекса и структуру хранения, как описано выше, совместный индекс можно искать только в первом столбце многостолбцового индекса. Таким образом, если ваше условие поиска не включает столбцы b, такие как (c, d), (c), (d), кэш не может быть применен, а индекс, такой как (b, d), не может быть полностью использован для столбцов, только использование индекса столбца b.
Это как наша телефонная книга, с именем и фамилией и телефоном, имя и фамилия - это совместный указатель. В том случае, если фамилию можно отсортировать по начальной букве фамилии, а начальная буква фамилии совпадает, то можно отсортировать по начальной букве имени.
как:
M
毛 不易 178********
马 化腾 183********
马 云 188********
Z
张 杰 189********
张 靓颖 138********
张 艺兴 176********
Мы знаем, что по имени и фамилии можно быстро найти фамилию из начального индекса фамилии, затем найти имя, а затем найти номер телефона, потому что все фамилии упорядочены сверху вниз по установленному правил (начальный порядок) , а имя сортируется по первой букве имени при условии, что первая буква фамилии определена, но в целом все имена складываются в беспорядке, поэтому, если вы знаете только имя, поиск будет медленнее, т.к. быстрый поиск невозможен с уже упорядоченной структурой.
На данный момент вы понимаете, почему существует принцип соответствия крайнего левого префикса?
упражняться
Ниже приведен список некоторых случаев использования индекса SQL.
select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
select * from T1 where b = 12 and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化
select * from T1 where b = 12 and d = 3;-- 应用到一列索引 因为不能跨列使用索引 没有c列 连不上
select * from T1 where c = 14 and d = 3;-- 无法应用索引，违背最左匹配原则
постскриптум
На этом структура хранения и метод поиска объединенного индекса индекса MySQL закончены.У меня ограниченные возможности и я также создал эту статью на плечах своих предшественников, потому что я видел результаты поиска поисковой системы в предыдущие технические статьи.Описание неясное или описание неверное, поэтому я обобщил эту статью и поделился ею с вами.Если что-то не так, пожалуйста, поправьте меня, спасибо.
Эта статья использовалась с перерывами в течение двух или трех дней, чтобы рисовать и писать в свободное время, и основное содержание выше. Бесспорно, что эта статья в какой-то степени на бумаге, потому что я новичок в использовании MySQL, и у меня нет большого опыта в настройке баз данных, поэтому мне стыдно говорить об этом здесь. Это как мои личные учебные заметки.
Кроме того, индексы и знания MySQL очень обширны, и в этой статье затронуты лишь некоторые из них. Например, в этой статье не рассматривается тема оптимизации индекса и покрывающего индекса, связанного с сортировкой (ORDER BY), в то же время, помимо индекса B-Tree, MySQL также включает в себя хэш-индексы, полнотекстовые индексы и т. д. .поддерживается разными движками.Не участвует. Если будет возможность, надеюсь дополнить части, не затронутые в этой статье.
Пожалуйста, укажите источник
Это не легко создать, если вам это поможет, пожалуйста, не скупитесь на лайки, это для меня большой стимул~😘
Пожалуйста, помогите, если это поможет вамНравится↓↓↓
Отсканируйте код, чтобы подписаться на официальный аккаунт: Programming Avenue, читайте последние статьи в первый раз, ваше внимание — самая большая поддержка для меня!~~