MySQL Index

Материал из poiuty wiki
Перейти к: навигация, поиск

Исходная структура таблицы. В ней много данных. Попробуем выставить индексы и сравнить скорость запросов.

mysql> describe cpu_use;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| billing_id | int(5)      | NO   |     | NULL    |       |
| cpu        | float       | NO   |     | NULL    |       |
| time       | int(20)     | NO   |     | NULL    |       |
| type       | varchar(12) | NO   |     | php     |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Проверяем запросы без индекса. Читает все строчки.

mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'mysql';
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
|  1 | SIMPLE      | cpu_use | ref  | main          | main | 4       | const | 125844 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row in set (0.00 sec)


describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'php';
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
|  1 | SIMPLE      | cpu_use | ref  | main          | main | 4       | const | 125844 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

Сделаем один общий индекс на колонки -> billing_id и type. Использует индекс, читает меньше строчек.

mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'php';
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref         | rows   | Extra                 |
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
|  1 | SIMPLE      | cpu_use | ref  | main          | main | 42      | const,const | 120210 | Using index condition |
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
1 row in set (0.00 sec)


mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'mysql';
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref         | rows | Extra                 |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | cpu_use | ref  | main          | main | 42      | const,const | 5733 | Using index condition |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

Удалим один общий индекс. Сделаем два индекса. Первый на таблицу billing_id, второй - type.
Первый запрос -> не использовал индекс и прочитал все строчки. Второй использовал, но прочитал больше строчек, чем в предыдущем варианте.

mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'php';
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
|  1 | SIMPLE      | cpu_use | ref  | main,2        | main | 4       | const | 125844 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
 
mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'mysql';
+----+-------------+---------+-------------+---------------+--------+---------+------+-------+--------------------------------------+
| id | select_type | table   | type        | possible_keys | key    | key_len | ref  | rows  | Extra                                |
+----+-------------+---------+-------------+---------------+--------+---------+------+-------+--------------------------------------+
|  1 | SIMPLE      | cpu_use | index_merge | main,2        | 2,main | 38,4    | NULL | 12451 | Using intersect(2,main); Using where |
+----+-------------+---------+-------------+---------------+--------+---------+------+-------+--------------------------------------+
1 row in set (0.00 sec)

Если три индекса, два - на две колонки, один на две колонки. Выберет самый быстрый вариант.

mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'php';
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref         | rows   | Extra                 |
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
|  1 | SIMPLE      | cpu_use | ref  | main,2,3      | 3    | 42      | const,const | 120210 | Using index condition |
+----+-------------+---------+------+---------------+------+---------+-------------+--------+-----------------------+
1 row in set (0.00 sec)

mysql> describe select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'mysql';
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref         | rows | Extra                 |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | cpu_use | ref  | main,2,3      | 3    | 42      | const,const | 5733 | Using index condition |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

На сколько выросла скорость?

select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'mysql';
5734 rows in set (0.57 sec) -> без индекса
5734 rows in set (0.01 sec) -> с индексом

select SQL_NO_CACHE * from cpu_use where billing_id= '4' and type = 'php';
57154 rows in set (0.61 sec) -> без индекса
57154 rows in set (0.11 sec) -> с индексом

Лучше расставлять индексы под конкретные запросы. И не лепить их на все колонки.