"Индекс базы данных, в конце тому, что делать?" представил дерево B+, которое является очень подходящиминдекс базы данныхСтруктура данных:
(1) Он очень подходит для хранения на диске и может в полной мере использовать принцип локальности и упреждающего чтения с диска;
(2) очень низкая высота дерева, способная хранить большой объем данных;
(3) Память, занимаемая самим индексом, очень мала;
(4) Он может поддерживать одноточечный запрос, запрос диапазона и заказанный запрос;
Индекс базы данных делится наиндекс первичного ключа(основной Inkex) снормальный индекс(Вторичный индекс). Как InnoDB и MyISAM используют деревья B+ для реализации этих двух типов индексов и в чем разница? Вот о чем мы сегодня поговорим.
Во-первых, индекс MyISAM
MyISAM索引与行记录是分开存储的, называетсянекластеризованный индекс(некластеризованный индекс).
Существенной разницы между его индексом первичного ключа и обычным индексом нет:
- область с непрерывной агрегацией
单独存储行记录 - Листовой узел индекса первичного ключа, в котором хранится первичный ключ, и соответствующая запись строки
指针 - Листовой узел обычного индекса, в котором хранится столбец индекса, и соответствующая запись строки
指针
*画外音：MyISAM的表可以没有主键。*
Индекс первичного ключа и обычный индекс представляют собой два независимых дерева индекса B+.При поиске в столбце индекса сначала найдите конечный узел дерева B+, а затем найдите запись строки с помощью указателя.
Например, MyISAM:
t(id PK, name KEY, sex, flag);
В таблице четыре записи:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
Его структура индекса дерева B+ показана выше:
- записи строк хранятся отдельно
- ID ID PK, есть индексное дерево идентификатора, то указывают на записи строки
- имя KEY, есть индексное дерево имен и лист, указывающий на строки
Во-вторых, индекс InnoDB
ИнноБДиндекс первичного ключа иЗаписи строк хранятся вместе, поэтому они называютсякластеризованный индекс(кластеризованный индекс):
- Нет отдельной области для хранения записей строк
- листовые узлы индекса первичного ключа,
存储主键，与对应行记录(вместо указателей)
Голос за кадром: Таким образом, поиск PK в InnoDB выполняется очень быстро.
Благодаря этой функции таблицы InnoDB必须要有聚集索引:
(1) Если таблица определяет PK, PK является индексом агрегирования;
(2) Если таблица не определяет PK, первый непустой уникальный столбец является кластеризованным индексом;
(3) В противном случае InnoDB создаст скрытый идентификатор строки как кластеризованный индекс;
кластерный индекс, а также只能够有一个, так как строка данных может иметь только одно совокупное хранилище на физическом диске.
ИнноБДнормальный индексТам может быть больше, чем один, который отличается от кластеризованного индекса:
- Обычный индексированный листовой узел,
存储主键(тоже не указатель)
Для таблиц InnoDB откровение здесь следующее:
(1) Не рекомендуется использовать в качестве первичного ключа длинный столбец, такой как char(64), потому что все обычные индексы будут хранить первичный ключ, из-за чего обычный индекс будет слишком большим;
(2) Рекомендуется использовать趋势递增Ключ является первичным ключом, потому что строка данных интегрирована с индексом, поэтому при вставке записи происходит большое количество разбиений индекса, и запись перемещается;
Это все тот же пример выше, просто замените механизм хранения InnoDB:
t(id PK, name KEY, sex, flag);
В таблице еще есть четыре записи:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
Его структура индекса дерева B+ показана выше:
- id - это PK, запись строки и дерево индексов идентификаторов хранятся вместе
- Имя KEY, есть индексное дерево имени, а листья хранят идентификатор
когда:
выберите * из t, где name='lisi';
Он будет находиться в листовом узле индекса дерева B + по имени, указав вторичный идентификатор = 5, а затем через строки, чтобы найти кластеризованный индекс.
*画外音：所以，其实扫了2遍索引树。*
3. Резюме
И MyISAM, и InnoDB используют деревья B+ для реализации индексов:
- Индекс и данные MyISAM
分开место хранения - Индексный лист для MyISAM
存储指针, индекс первичного ключа мало чем отличается от обычного индекса - ИнноБДкластеризованный индекси
数据行统一存储 - Кластерные индексы InnoDB хранят сами строки данных,нормальный индекс
存储主键 - InnoDB должен иметь один и только один кластеризованный индекс
- InnoDB рекомендует использовать
趋势递增整数作为PK,и不宜使用较长的列作为PK