Совет по MySQL: посмотрите на used_key_parts, чтобы выяснить, какая часть ключа из нескольких столбцов используется

В предыдущем посте для этого продолжающегося « 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 может помочь нам определить, насколько эффективны наши составные индексы.

Совет по MySQL: посмотрите на used_key_parts, чтобы выяснить, какая часть ключа из нескольких столбцов используется

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC