Кластерные и «обычные» индексы MySQL (InnoDB)

Сам первичный (кластерный) ключ хранится вместе с данными и под абстракцию «индексной таблицы» не подходит. А вот вторичные ключи все имеют скрытую часть в виде полного набора полей ключа первичного.

Пример. Есть таблица t1 (InnoDB) с полями k1, k2, i1, i2. Есть первичный ключ по полям k1, k2 (он и будет кластерным). И есть «обычный» индекс (вторичный ключ) по полям i1, i2.

CREATE TABLE `t1` (
  `k1` int(10) unsigned NOT NULL DEFAULT '0',
  `k2` int(10) unsigned NOT NULL DEFAULT '0',
  `i1` int(10) unsigned NOT NULL DEFAULT '0',
  `i2` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`k1`,`k2`),
  KEY `secondary_index` (`i1`,`i2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Строки в индексной таблице для вторичного ключа будут представлять собой кортежи вида

(`i1`,`i2`, `k1`, `k2`)

где первая пара полей (i) используется для поиска второй (k). Когда получена вторая пара (k, образующая первичный ключ), по ней ищутся строки в таблице данных.

Заполним таблицу случайными данными:

mysql> SELECT * FROM `t1`;
+-----+-----+-----+-----+
| k1  | k2  | i1  | i2  |
+-----+-----+-----+-----+
| 251 | 762 |  60 |  13 |
| 786 | 490 |  92 | 988 |
| 885 | 385 | 272 | 202 |
| 159 | 403 | 537 | 480 |
| 624 | 341 | 830 | 130 |
| 667 | 372 | 856 | 163 |
+-----+-----+-----+-----+
6 rows in set (0.00 sec)

И посмотрим план выполнения простого запроса с фильтрацией:

mysql> EXPLAIN SELECT * FROM `t1` WHERE `i1` > 200 AND `i2` < 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: secondary_index
          key: secondary_index
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using index

Ключ наш оптимизатором запросов признан годным и в плане выполнения используется. Кроме того, Using index в графе Extra означает, что фактического чтения данных с диска не производилось. Все данные брались прямо из индекса. В графе key указан secondary_index, значит все выбранные данные содержатся в нём.

Для доказательства и интереса ради проведём простой эксперимент. Выполним

ALTER TABLE `t1` DROP PRIMARY KEY;
ALTER TABLE `t1` ADD PRIMARY KEY (`k1`);

В результате чего поле k2 выпадает из индексов. План выполнения запроса тут же меняется:

mysql> EXPLAIN SELECT * FROM `t1` WHERE `i1` > 200 AND `i2` < 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: secondary_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where

Обратите внимание на «key: NULL». Поскольку так или иначе придётся читать данные, не входящие в ключ (выбираем-то мы "*"), оптимизатор MySQL принимает решение не использовать ключи вообще, а провести фулскан (type: ALL). Естественно, в Extra больше нет «Using index».

Но стоит нам исключить из выборки поле `k2`, как мы возвращаемся к использованию кластерных ключей:

mysql> EXPLAIN SELECT `k1`, `i1`, `i2` FROM `t1` WHERE `i1` > 200 AND `i2` < 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: secondary_index
          key: secondary_index
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using index

Описание специально упрощённое. Это достаточно базовые знания о структуре таблиц и индексов, статья писалась для относительных новичков. Мне такой информации в своё время очень не хватало. Именно упрощённой и разжёванной.

Что касается «плоской» и не-совсем-такой-как-в-реальности «индексной таблицы» (каковой, строго говоря, вообще не существует) — это абстракция, упрощающая восприятие. Возможно я зря решил так упростить, но лично мне было бы проще воспринимать именно так.

Речь здесь именно о InnoDB, а там свои правила: dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html И кластерный индекс создаётся всегда.

Спасибо. Обязательно дополню.

> любой table scan превращается в случайное прыганье по диску

Это еще почему? Страницы же читаются в том порядке, в котором лежат на диске. Страницы промежуточных уровней пропускаются.

В общем — тема интересная, важная. Но, увы, статья получилась не однозначная. Мне затруднительно оценить чего тут больше — введения в заблуждение или же разложения по полочкам.

В статье автор описывает, что кластерные индексы организованы в виде небинарного дерева. То есть у нода может быть больше двух дочерних нодов. Это не так?

Можно partitions использовать. Индекс будет создаваться для каждой партиции, конечно, но при выборке будет использоваться только одна:

mysql> create table t1(f1 int, f2 int, f3 int, updated timestamp, key(f1,f2), key(f2,f3), key(f1,f3))engine=innodb
-> PARTITION BY RANGE ( UNIX_TIMESTAMP(updated)) (
-> PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
…
-> PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
-> PARTITION p9 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (1.15 sec)

mysql> insert into t1 (f1,f2,f3) values(1,2,3);
Query OK, 1 row affected (0.08 sec)

mysql> explain partitions select * from t1 where f1=1 and updated > '2010-01-01 00:00:00'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: p9
type: ref
possible_keys: f1,f1_2
key: f1
key_len: 5
ref: const
rows: 152
Extra: Using where
1 row in set (0.01 sec)

В Oracle — да (пользуясь тем что он NULL не хранит в индексе), в MySQL — нет.

Партиционирование и периодически перемещать партицию в архивную таблицу.