В предыдущем посте для этого продолжающегося « EXPLAIN FORMAT = JSON это круто! В этой серии мы обсудили покрытые индексы и то, как массив used_columns может помочь выбрать их разумно. Существует еще один тип многоколоночных индексов: составные индексы . Составные индексы — это просто индексы по нескольким столбцам. Покрываемые индексы являются подгруппой из большего набора «составных индексов». В этом посте мы обсудим, как «used_key_parts» может помочь показать, какая часть ключа из нескольких столбцов используется.
Вы должны отдавать предпочтение использованию составных индексов, когда у вас есть запросы, которые выполняют поиск как по множеству столбцов, так и по одному столбцу. Например, если вы запускаете такие запросы:
SELECT first_name, last_name FROM employees WHERE first_name='Steve';
SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%';
SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01';
Было бы лучше иметь один индекс для столбцов first_name, last_name и hire_date, а не три индекса для first_name, составной для (first_name, last_name) и составной для (first_name, last_name, hire_date). Но каков наилучший метод проверки эффективности нового индекса?
Еще раз, ответ EXPLAIN FORMAT = JSON.
Чтобы проиллюстрировать эту идею, давайте добавим составной индекс (first_name, last_name, hire_date) к таблице «сотрудники» из стандартной базы данных сотрудников :
mysql> alter table employees add index comp (first_name, last_name, hire_date);
Query OK, 0 rows affected (9.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
Теперь давайте проверим, используется ли этот индекс для разрешения наших запросов:
mysql> explain SELECT first_name, last_name FROM employees WHERE first_name='Steve';
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ref | comp | comp | 16 | const | 245 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%';
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | range | comp | comp | 34 | NULL | 8 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01';
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | range | comp | comp | 37 | NULL | 8 | 33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
Он используется во всех запросах, и key_len увеличивается, что показывает, что каждый запрос использует больше частей индекса. Но какая часть индекса фактически использовалась для разрешения условия WHERE, а какая использовалась для получения строк?
EXPLAIN FORMAT = JSON сохраняет эту информацию в элементе used_key_parts.
Для первых двух запросов показан следующий результат:
mysql> explain format=json SELECT first_name, last_name FROM employees WHERE first_name='Steve'G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "51.22"
},
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"comp"
],
"key": "comp",
"used_key_parts": [
"first_name"
],
"key_length": "16",
"ref": [
"const"
],
"rows_examined_per_scan": 245,
"rows_produced_per_join": 245,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "2.22",
"eval_cost": "49.00",
"prefix_cost": "51.22",
"data_read_per_join": "11K"
},
"used_columns": [
"first_name",
"last_name"
]
}
}
}
1 row in set, 1 warning (0.00 sec)
Только поле first_name индекса использовалось для запроса с условием WHERE first_name = ‘Steve’.
mysql> explain format=json SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%'G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4.24"
},
"table": {
"table_name": "employees",
"access_type": "range",
"possible_keys": [
"comp"
],
"key": "comp",
"used_key_parts": [
"first_name",
"last_name"
],
"key_length": "34",
"rows_examined_per_scan": 8,
"rows_produced_per_join": 8,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "2.65",
"eval_cost": "1.60",
"prefix_cost": "4.25",
"data_read_per_join": "384"
},
"used_columns": [
"first_name",
"last_name"
],
"attached_condition": "((`employees`.`employees`.`first_name` = 'Steve') and (`employees`.`employees`.`last_name` like 'V%'))"
}
}
}
1 row in set, 1 warning (0.00 sec)
И два поля, first_name и last_name, были использованы для второго запроса.
"key": "comp",
"used_key_parts": [
"first_name",
"last_name"
],
Но, что удивительно, тот же самый результат происходит с последним запросом, хотя он также запрашивает столбец hire_date:
mysql> explain format=json SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01'G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4.24"
},
"table": {
"table_name": "employees",
"access_type": "range",
"possible_keys": [
"comp"
],
"key": "comp",
"used_key_parts": [
"first_name",
"last_name"
],
"key_length": "37",
"rows_examined_per_scan": 8,
"rows_produced_per_join": 2,
"filtered": "33.33",
"using_index": true,
"cost_info": {
"read_cost": "3.71",
"eval_cost": "0.53",
"prefix_cost": "4.25",
"data_read_per_join": "127"
},
"used_columns": [
"first_name",
"last_name",
"hire_date"
],
"attached_condition": "((`employees`.`employees`.`first_name` = 'Steve') and (`employees`.`employees`.`last_name` like 'V%') and (`employees`.`employees`.`hire_date` > '1990-01-01'))"
}
}
}
1 row in set, 1 warning (0.00 sec)
Скорее всего, это связано с тем, что в столбце hire_date слишком много значений, удовлетворяющих условиям, поэтому проще извлечь набор данных, используя часть индекса, а затем проверить условие для столбца hire_date.
Это означает, что, поскольку мы не извлекаем hire_date, мы можем удалить его из индекса. Мы можем быть немного осторожны с тем, какие строки таблицы будут доступны для окончательного сравнения со столбцом hire_date, но в этом случае это нормально:
mysql> flush status;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Steve | Vecchi |
| Steve | Veldwijk |
| Steve | Vickson |
+------------+-----------+
3 rows in set (0.00 sec)
mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 8 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
mysql> alter table employees drop index comp;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table employees add index comp (first_name, last_name);
Query OK, 0 rows affected (7.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> flush status;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Steve | Vecchi |
| Steve | Veldwijk |
| Steve | Vickson |
+------------+-----------+
3 rows in set (0.00 sec)
mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 8 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
Как видите, переменные Handler_ * одинаковы для обоих индексов. Причиной этого является то, что в этом случае оптимизатор может использовать оптимизацию сжатия условия индекса :
mysql> explain format=json SELECT first_name, last_name FROM employees WHERE first_name='Steve' and last_name like 'V%' and hire_date > '1990-01-01'G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.21"
},
"table": {
"table_name": "employees",
"access_type": "range",
"possible_keys": [
"comp"
],
"key": "comp",
"used_key_parts": [
"first_name",
"last_name"
],
"key_length": "34",
"rows_examined_per_scan": 8,
"rows_produced_per_join": 2,
"filtered": "33.33",
"index_condition": "((`employees`.`employees`.`first_name` = 'Steve') and (`employees`.`employees`.`last_name` like 'V%'))",
"cost_info": {
"read_cost": "11.68",
"eval_cost": "0.53",
"prefix_cost": "12.21",
"data_read_per_join": "127"
},
"used_columns": [
"first_name",
"last_name",
"hire_date"
],
"attached_condition": "(`employees`.`employees`.`hire_date` > '1990-01-01')"
}
}
}
1 row in set, 1 warning (0.00 sec)
В выводе выше у нас нет члена:
"using_index": true,
Но есть информация о состоянии индекса:
"index_condition": "((`employees`.`employees`.`first_name` = 'Steve') and (`employees`.`employees`.`last_name` like 'V%'))",
Однако query_cost в этом случае выше: 12,21 против 4,24 для составного индекса трех полей.
Вывод: поле used_key_parts вывода EXPLAIN FORMAT = JSON может помочь нам определить, насколько эффективны наши составные индексы.