В InnoDB таблица не лежит отдельно от индекса: строки хранятся в листьях B+-дерева, упорядоченные по первичному ключу. Отсюда два следствия, которые видит DBA: вторичный индекс вместо адреса строки хранит копию её PK, поэтому широкий ключ раздувает каждый индекс таблицы, а случайный ключ (UUID v4) заставляет вставки бить в произвольные страницы дерева. Устройство самой страницы и строки описано в статье про раскладку, разделение страниц B-tree в PostgreSQL — в статье про B-tree.
Лист таблицы, лист вторичного индекса и цена ключа
Опыт 1: вторичный индекс хранит PK
Поднимаем сервер. После инициализации mysqld в контейнере один раз перезапускается, поэтому дождись, пока SELECT 1 проходит дважды подряд: база, созданная до перезапуска, пропадёт.
docker run -d --name dba-innodb-clustered-index -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
docker exec -it dba-innodb-clustered-index mysql -uroot -ppg -tSELECT @@version, @@innodb_page_size, @@innodb_buffer_pool_size, @@innodb_change_buffering;вывод
+-----------+--------------------+---------------------------+---------------------------+
| @@version | @@innodb_page_size | @@innodb_buffer_pool_size | @@innodb_change_buffering |
+-----------+--------------------+---------------------------+---------------------------+
| 8.4.13 | 16384 | 134217728 | none |
+-----------+--------------------+---------------------------+---------------------------+Версия совпадает с тегом mysql-8.4.13, на который ссылаются цитаты. Две одинаковые таблицы на миллион строк отличаются только типом PK: BIGINT и CHAR(36) (строка вида UUID, в ascii, чтобы один символ был один байт). Значения PK растут в обеих, так что порядок вставки не мешает:
CREATE DATABASE lab; USE lab;
SET SESSION cte_max_recursion_depth = 1000000;
CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 1000000) SELECT n FROM s;
CREATE TABLE t_bigint (id BIGINT NOT NULL PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, KEY idx_customer (customer_id));
CREATE TABLE t_char36 (id CHAR(36) CHARACTER SET ascii NOT NULL PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, KEY idx_customer (customer_id));
INSERT INTO t_bigint SELECT n, n % 50000, n % 1000 FROM nums;
INSERT INTO t_char36 SELECT LPAD(n,36,'0'), n % 50000, n % 1000 FROM nums;
ANALYZE TABLE t_bigint, t_char36;
SELECT table_name, index_name, stat_value AS leaf_pages FROM mysql.innodb_index_stats
WHERE database_name='lab' AND table_name IN ('t_bigint','t_char36') AND stat_name='n_leaf_pages' ORDER BY 1, 2;вывод
+------------+--------------+------------+
| table_name | index_name | leaf_pages |
+------------+--------------+------------+
| t_bigint | PRIMARY | 2337 |
| t_bigint | idx_customer | 1723 |
| t_char36 | PRIMARY | 4185 |
| t_char36 | idx_customer | 4480 |
+------------+--------------+------------+В idx_customer одно и то же четырёхбайтное customer_id, но индекс вырос в 2,6 раза (1723 → 4480 листьев, 580 записей на лист против 223), тогда как сама таблица — в 1,8 раза (2337 → 4185). Если вторичный индекс хранил бы адрес строки, он бы не зависел от PK. Хранит PK, и это видно по запросам:
SELECT customer_id, id FROM t_bigint WHERE customer_id = 27777 LIMIT 3;
EXPLAIN SELECT id FROM t_char36 WHERE customer_id = 27777\Gвывод
+-------------+--------+
| customer_id | id |
+-------------+--------+
| 27777 | 27777 |
| 27777 | 77777 |
| 27777 | 127777 |
+-------------+--------+
...
key: idx_customer
key_len: 4
Extra: Using indexid в индексе по customer_id нигде не объявлен, но запрос SELECT id обошёлся одним индексом (Using index, ключ длиной 4 байта — одно customer_id), и записи с одним customer_id идут по возрастанию id. Код достраивает индекс при создании (dict_index_build_internal_non_clust, тег mysql-8.4.13, dict0dict.cc):
for (i = 0; i < clust_index->n_uniq; i++) {
field = clust_index->get_field(i);
if (!indexed[field->col->ind]) {
dict_index_add_col(new_index, table, field->col, field->prefix_len,
field->is_ascending);
} else if (dict_index_is_spatial(index)) {
/*For spatial index, we still need to add the
field to index. */
dict_index_add_col(new_index, table, field->col, field->prefix_len,
field->is_ascending);
}
}К полям вторичного индекса добавляются все столбцы PK, которых там ещё нет. Запрос, которому нужен не только id, дополнительно идёт в дерево таблицы по PK: второй EXPLAIN (SELECT amount) показал Extra: NULL, без Using index.
Опыт 2: пять миллионов строк в трёх порядках
Три таблицы с одинаковыми данными (customer_id, amount) и без вторичных индексов; различается только PK и порядок его значений. Версия UUID v7 собрана вручную: 48 бит времени в миллисекундах (десять строк на миллисекунду) и случайный хвост; v4 — 122 случайных бита. Оба хранятся как BINARY(16):
CREATE TABLE ins_auto (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL);
CREATE TABLE ins_v7 (id BINARY(16) NOT NULL PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL);
CREATE TABLE ins_v4 (id BINARY(16) NOT NULL PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL);
SET GLOBAL innodb_monitor_enable = 'index_page_splits';Каждую таблицу заполняет процедура: 50 вставок по 100 000 строк из таблицы nums100k. Время берётся из NOW(3), разделения страниц — из счётчика index_page_splits, чтения диска — из Innodb_buffer_pool_reads: все три снимаются до и после загрузки. После загрузки ANALYZE TABLE ins_auto, ins_v7, ins_v4 и запрос листьев из mysql.innodb_index_stats:
SELECT kind, seconds, page_splits, disk_reads FROM run_log;
SELECT table_name, stat_value AS leaf_pages, ROUND(5000000/stat_value) AS rows_per_leaf, ROUND(stat_value*16384/1048576) AS leaf_mib
FROM mysql.innodb_index_stats WHERE database_name='lab' AND table_name LIKE 'ins\_%' AND index_name='PRIMARY' AND stat_name='n_leaf_pages' ORDER BY table_name;вывод
+------+---------+-------------+------------+
| kind | seconds | page_splits | disk_reads |
+------+---------+-------------+------------+
| auto | 13.2 | 11695 | 139 |
| v7 | 22.3 | 18492 | 586 |
| v4 | 1306.4 | 20257 | 1204382 |
+------+---------+-------------+------------+
+------------+------------+---------------+----------+
| table_name | leaf_pages | rows_per_leaf | leaf_mib |
+------------+------------+---------------+----------+
| ins_auto | 11683 | 428 | 183 |
| ins_v4 | 20210 | 247 | 316 |
| ins_v7 | 18445 | 271 | 288 |
+------------+------------+---------------+----------+Файлы .ibd в /var/lib/mysql/lab/: 201 326 592 байта (192 МиБ) у ins_auto, 360 710 144 (344 МиБ) у ins_v7, 394 264 576 (376 МиБ) у ins_v4. Прирост листьев делится на две части. Ширина ключа: четвёртая таблица ins_bin16 с теми же 16 байтами и возрастающим значением (UNHEX(LPAD(HEX(n),32,'0'))) заняла 14 287 листьев, 350 строк на лист, это +22 % к BIGINT. Порядок: v7 добавляет к ней ещё 29 % (18 445), v4 — 41 % (20 210). Страницы делятся при вставке в заполненную. Сначала InnoDB пробует обойтись без этого (btr_cur_optimistic_insert), и только при DB_FAIL страница делится (btr0cur.cc, btr_cur_pessimistic_insert):
if (dict_index_get_page(index) ==
btr_cur_get_block(cursor)->page.id.page_no()) {
/* The page is the root page */
*rec = btr_root_raise_and_insert(flags, cursor, offsets, heap, entry, mtr);
} else {
*rec = btr_page_split_and_insert(flags, cursor, offsets, heap, entry, mtr);
}Но число разделений здесь не главное: у AUTO_INCREMENT их почти столько же, сколько листьев (11 695 при 11 683): при дописывании в хвост каждая новая страница тоже считается разделением, а 428 записей на лист показывают, что страницы заполнены. У v4 их 20 257, всего в 1,7 раза больше, а время выросло в 99 раз (1306,4 против 13,2 с). Разница — в чтениях диска: 1 204 382 против 139. Таблица v4 (около 376 МиБ) не помещается в буферный пул на 128 МиБ, и каждая вставка идёт на случайный лист, которого в памяти, скорее всего, нет. Вставка UUID v7 растёт монотонно с точностью до миллисекунды, поэтому по времени близка к AUTO_INCREMENT (22,3 против 13,2 с), а читает диск 586 раз.
Опыт 3: change buffer
Для вторичного индекса InnoDB умеет не читать страницу, а откладывать правку: change buffer копит вставки для листьев, которых нет в буферном пуле, и применяет их, когда страница всё равно читается. В контейнере innodb_change_buffering = none (вывод выше), то есть буфер выключен. Две таблицы с вторичным индексом по случайной строке и 2 млн вставок в каждую (20 раз по 100 000 строк INSERT … SELECT MD5(RAND())):
CREATE TABLE cb_none (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, token CHAR(32) CHARACTER SET ascii NOT NULL, KEY idx_token (token));
CREATE TABLE cb_all (LIKE cb_none);
SET GLOBAL innodb_monitor_enable = 'module_ibuf_system';
SET GLOBAL innodb_change_buffering = 'all';Перед каждой загрузкой переменная ставится в none или all, а ibuf_merges и ibuf_merges_insert берутся из information_schema.innodb_metrics как разность до и после.
SELECT mode, seconds, disk_reads, ibuf_inserts AS merged_inserts, ibuf_merges AS merges FROM cb_log;вывод
+------+---------+------------+----------------+--------+
| mode | seconds | disk_reads | merged_inserts | merges |
+------+---------+------------+----------------+--------+
| all | 24.4 | 1438 | 14637 | 3071 |
| none | 33.5 | 12297 | 5735 | 790 |
+------+---------+------------+----------------+--------+В режиме all загрузка быстрее на 27 % (24,4 против 33,5 с), а чтений диска в 8,6 раза меньше. Строка none показывает слияния 790 и 5735: это остатки записей, накопленных в all перед ней, применённые, когда страницы всё-таки прочитали. Обратный порядок даёт тот же результат: при первом прогоне none (39,4 с, 14 290 чтений, ноль слияний) шёл раньше all (29,5 с, 1397 чтений, 13 232 слияния вставок). Решение откладывать принимает ibuf_insert (ibuf0ibuf.cc), читая переменную один раз на вызов. Выигрыш умеренный: индекс из 2 млн ключей почти помещается в 128 МиБ; на индексе, сильно превышающем пул, его нужно мерить отдельно.
Что из этого следует
- PK — это размер каждого индекса. Замена
BIGINTнаCHAR(36)увеличила вторичный индекс в 2,6 раза при том жеcustomer_id. UUID храни какBINARY(16), а не строкой, и не делай PK составным без нужды: он копируется в каждый вторичный индекс. - Порядок PK — это порядок вставки. Монотонный ключ (
AUTO_INCREMENT, UUID v7) пишет в хвост дерева, случайный (v4) читает случайные страницы. В опыте таблица, превышающая буферный пул, вставлялась в 99 раз дольше. Пока таблица влезает в память, разницы почти нет: проверяй на данных своего размера. - Покрывающий индекс не заходит в таблицу. Любой запрос, которому достаточно столбцов индекса и PK, не заходит в таблицу (
Using index); остальным нужен второй спуск по PK. - В контейнере change buffer выключен. Если на вторичные неуникальные индексы идёт случайная запись, проверь
innodb_change_bufferingи сравни загрузку на своей нагрузке. - Если нужен случайный идентификатор снаружи, клади его отдельным столбцом с уникальным индексом, а PK оставь монотонным. В PostgreSQL таблица — куча (страница и строки), а индекс хранит адрес версии строки, поэтому ширина PK в чужие индексы не копируется.
Чек-лист
- Узнай, как устроен PK у больших таблиц:
SHOW CREATE TABLE, тип, ширина, монотонность. ДляCHAR(36)и составных ключей посчитай вклад в каждый вторичный индекс. - Сравни
n_leaf_pagesвторичных индексов вmysql.innodb_index_statsс числом строк: мало записей на лист значит широкий PK или разреженные страницы. - Следи за
index_page_splitsвinformation_schema.innodb_metrics(сначалаSET GLOBAL innodb_monitor_enable) иInnodb_buffer_pool_reads: рост чтений диска при вставке указывает на случайный ключ. - Для UUID используй
BINARY(16)и упорядоченный по времени вариант (v7); проверь вставку 1–5 млн строк на таблице, превышающей буферный пул. - Убери контейнер вместе с томом:
docker rm -f -v dba-innodb-clustered-index.