MySQL索引管理

一、概述

1. 简介

索引类似于目录,能有效提高查询效率;

2. 索引分类

  • B-TREE索引
  • B+TREE索引
  • B*TREE索引
  • HASH索引
  • R-TREE索引
  • Fulltext索引
  • 地理位置索引

3. B树索引功能细分

MySQL是以IOT(索引组织表)方式存储数据
  • 辅助索引(Secondary Index)
    辅助索引:用于辅助聚簇索引,优化查询效率
  • 聚簇索引(Cluster Index)
    聚簇索引:以主键为聚集索引列,由系统自动生成聚簇索引;聚簇索引的叶子节点中保存的数据是表中的记录;

4.索引树高度增加因素

索引树越高,查询效率越低;
  • 数据量大
    解决方法:分区表、分布式架构(分库分表)
  • 索引列值过长
    解决办法: 构建前缀索引
  • 主键值过长
    解决办法:优化主键列值长度
  • 数据类型不合理
    解决办法:选择合适的数据类型

二、 索引管理命令

1. 创建索引

在查询业务相关列上建立索引;不建议在数据频繁更新的列上建立索引

ALTER TABLE 表名 ADD INDEX idx_索引名(索引列); #建立指定列的单列索引
ALTER TABLE 表名 ADD INDEX idx_索引名(索引列(n)); #建立指定列的前缀索引(n为前缀长度)
ALTER TABLE 表名 ADD INDEX idx_索引名(索引列1,索引列2,...); #建立多列的联合索引
可以在同一列上建立多个索引,但是不能存在重名索引;
前缀索引只能用于字符串列,不能用于数字列
#原始数据
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#创建单列索引
mysql> alter table city add index idx_name(name);
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#创建前缀索引
mysql> alter table city add index idx_District(District(4));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   | MUL |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#创建联合索引
mysql> alter table city add index idx_name_coutrycode_population(name,countrycode,population);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY                        |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode                    |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name                       |            1 | Name        | A         |        3998 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_District                   |            1 | District    | A         |        1125 |        4 | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            1 | Name        | A         |        3998 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            2 | CountryCode | A         |        4056 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            3 | Population  | A         |        4079 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

2. 删除索引

ALTER TABLE 表名 DROP INDEX idx_索引名; #删除指定索引
mysql> alter table city drop index idx_District;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table city drop index idx_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 查询索引

DESC 表名; #查看表结构(关注Key列)
DESC指令不能查看联合索引
key列
PRI 主键索引
MUL 辅助索引
UNI 唯一索引
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
SHOW INDEX FROM 表名; #查看指定表相关索引
mysql> show index from city;
+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY                        |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode                    |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            1 | Name        | A         |        3998 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            2 | CountryCode | A         |        4056 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name_coutrycode_population |            3 | Population  | A         |        4079 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

三、索引

1. 索引效率

mysqlslap --defaults-file=配置文件 \
--concurrency=并发次数 --iterations=1 --create-schema='数据库名' \
--query="测试语句" engine=搜索引擎 \
--number-of-queries=执行次数 -u用户名 -p密码 -verbose
concurrency=100 #100个用户并发执行
number-of-queries=2000 #一共查询2000次
query="语句" #执行的操作
示例
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780P'" engine=innodb \
--number-of-queries=2000 -uroot -p123456 -verbose
#无索引测试
[root@db01 /tmp]# mysqlslap --defaults-file=/data/mysql/3306/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 638.550 seconds
    Minimum number of seconds to run all queries: 638.550 seconds
    Maximum number of seconds to run all queries: 638.550 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

#索引测试
mysql> use test;
Database changed
mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (2.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

[root@db01 /tmp]# mysqlslap --defaults-file=/data/mysql/3306/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 0.382 seconds
    Minimum number of seconds to run all queries: 0.382 seconds
    Maximum number of seconds to run all queries: 0.382 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20
从上述例子中可以看出,就该表建立索引后查询效率提高1600倍;

2.执行计划

基本介绍

EXPLAIN语句-获取MySQL数据库的执行计划
EXPLAIN语句可以帮助我们规避一些执行效率比较低的语句;辅助我们优化索引,合理的简历索引。
EXPLAIN SQL语句 == DESC SQL语句 #获取指定语句的执行计划信息
+----+-------------+-------+------------+--------+----------------+--------+----------------------+------+--------+----------+-------------+
| id | select_type | table | partitions |  type  | possible_keys  |  key   |        key_len       | ref  | rows   | filtered |   Extra     |
+----+-------------+-------+------------+--------+----------------+--------+----------------------+------+--------+----------+-------------+
|序号|             | 表名  |             |查询类型|可能会用到的索引 |执行索引|查询时,索引的覆盖长度  |      |        |          |  额外信息   |
+----+-------------+-------+------------+--------+----------------+--------+----------------------+------+--------+----------+-------------+
key_len参数主要用于联合索引判断

重要参数

  • type
全表扫描
索引扫描:全索引遍历、索引范围扫描、辅助索引等值查询;
无查询结果

做索引优化时要避免全表扫描和全索引遍历情况的发生,索引查询级别至少要达到range及以上级别。

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
  1. ALL:全表扫描
造成全表扫描的常见原因:查询条件列无索引;获取全表数据;SQL语句不符合索引查询条件;
#获取全表数据
mysql> explain select * from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#查询条件列无索引
mysql> explain select name from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#SQL语句不符合索引查询条件
mysql> explain select * from city where countrycode like '%CN';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. index:全索引遍历
    造成全索引的常见原因:查询条件不足
mysql> explain select countrycode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. range: 索引范围扫描
    造成索引范围扫描:>,>=,<,<=,like,between and,or,in,!=(仅限主键列,非主键列不使用索引),not in(仅限主键列,非主键列不使用索引)
>, >=, < , <=, like, between and 可以受到B+TREE的索引优化
or, in无法受到B+TREE的索引优化,在实际应用情况中,orin的SQL语句一般改写为 UNION ALL的SQL语句
#>,>=,<,<=,like,between and
mysql> explain select * from world.city where id > 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2094 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from world.city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
# or,in
mysql> explain select * from city where countrycode in ('USA','CHN');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

#语句优化
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.34 sec)
#主键列 != ,not in
mysql> explain select * from city where id <> 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2103 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.ref 辅助索引等值查询

mysql> explain select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

5.eq_ref
多表连接查询,非驱动表的连接条件是主键或唯一键

一般情况下:驱动表 JOIN 非驱动表 ON 驱动表.xx=非驱动表.yy
mysql> explain select city.name,country.name,city.population,country.SurfaceArea from city join country on cityy.countrycode=country.code where city.population<100;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

6.const(system)
主键或唯一键等值查询

mysql> explain select * from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

7.NULL 无查询结果(性能最好)

mysql> explain select * from city where id=0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
  • key_len 联合索引覆盖长度
数据类型 最大长度(字节) key_len(not null) key_len 字符集
tinyint 1 1 2 utf8mb4
int 4 4 5 utf8mb4
bigint 8 8 9 utf8mb4
char(n) 4*n 4*n 4*n+1 utf8mb4
varchar(n) 4*n 4*n+2 4*n+3 utf8mb4
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| k1    | int(11)    | YES  | MUL | NULL    |       |
| k2    | int(11)    | NO   | MUL | NULL    |       |
| c1    | char(5)    | YES  | MUL | NULL    |       |
| c2    | char(5)    | NO   | MUL | NULL    |       |
| c3    | varchar(5) | YES  | MUL | NULL    |       |
| c4    | varchar(5) | NO   | MUL | NULL    |       |
+-------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> explain select * from t1 where k1=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_k1        | idx_k1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where k2=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_k2        | idx_k2 | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='aa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1        | idx_c1 | 21      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c2='aa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c2        | idx_c2 | 20      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c3='aa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c3        | idx_c3 | 23      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c4='aa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c4        | idx_c4 | 22      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
查询语句的索引覆盖长度越长,代表索引设计合理,索引应用合理,语句性能越好;
说明:

如果在查询条件中,所有索引列都为等值查询条件时,那么查询语句的执行效率与索引列的先后顺序无关
建立联合索引时将唯一值较多的列放在首位,以此类推;不等值查询条件列放在末位;
建立联合索引时将唯一值较多的列放在首位,以此类推;不等值查询条件列放在末位;
建立联合索引时,严格按照查询语句中各子句的逻辑执行顺序建立联合索引;
当查询条件中存在非等值查询时,key_len会被非等值查询语句截断;
当存在冗余索引时,MySQL优化器会自动选择使用存在时间长的索引;
当查询语句查询条件不连续,无法满足联合索引创建时列的顺序,则条件断开处之后无法走索引;
mysql> show index from t1;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | idx_union |            1 | c1          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | idx_union |            2 | c2          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | idx_union |            3 | c3          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | idx_union |            4 | c4          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
#如果在查询条件中,所有索引列都为等值查询条件时,那么查询语句的执行效率与索引列的先后顺序无关
mysql> explain select * from t1 where c1='a' and c2='b' and c3='c' and c4='d';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref                     | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_union     | idx_union | 86      | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t1 where c3='c' and c4='d' and c2='b' and c1='a';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref                     | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_union     | idx_union | 86      | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#当查询条件中存在非等值查询时,key_len会被非等值查询语句截断;
mysql> explain select * from t1 where c1='a' and c2>'b' and c3='c' and c4='d';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_union     | idx_union | 41      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#当存在冗余索引时,MySQL优化器会自动选择使用存在时间长的索引;
mysql> alter table t1 add index idx_c1_c3_c4_c2(c1,c3,c4,c2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where c1='a' and c3='c' and c4='d' and c2 > 'b';
+----+-------------+-------+------------+-------+---------------------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_union,idx_c1_c3_c4_c2 | idx_union | 41      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table t1 drop index idx_union;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where c1='a' and c3='c' and c4='d' and c2 > 'b';
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 86      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='a' and c3='c' and c4='d' and c2 > 'b';
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 86      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#当查询语句查询条件不连续,无法满足联合索引创建时列的顺序,则条件断开处之后无法走索引;
mysql> desc select * from t1 where c1='a' and c2='b';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 21      | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from t1 where c1='a' and c3='b';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 44      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • Extra
    Using temporary 使用临时表
    Using filesort 文件排序
如果Extra位置出现了Using temporary 和/或Using filesort,一定要分析GROUP BY条件、ORDER BY条件、DISTINCT条件、JOIN ON条件以及UNION条件是否和WHERE子句创建了联合索引;
mysql> desc select count(*) from t1 where c1='a' group by c2;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 21      | const |    1 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(*) from t1 where c1='a' group by c3;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 21      | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

3.索引建立原则

  • 建表必须有主键,如果没有可以作为主键的条件列,创建无关列为主键;便于系统创建聚簇索引;
  • 为经常作为where,order by,group by,join on,distinct的条件建立索引;(业务:产品功能+用户行为)
  • 尽量使用唯一值多的列创建索引,若果索引列重复值较多,可以考虑使用联合索引
  • 以列值长度较长的列创建索引,建议使用创建索引
  • 降低索引冗余度,尽量不要创建没有索引,不常用索引要进行清理(工具: percona toolkit)
  • 索引维护要避开业务繁忙期
  • 建立联合索引时,唯一值最多的放在最左侧,多子句按照子句查询顺序进行创建联合索引

4. 查询不引用索引的几种情况

  • 没有查询条件,或者查询条件没有建立索引
mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where k2='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 查询结果集是原表中大部分数据(≥25%)时,不使用辅助索引
mysql> alter table city  add index idx_population(population);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from city where population > 100000;
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | idx_population | NULL | NULL    | NULL | 4188 |    84.96 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from city where population < 100000;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | idx_population | idx_population | 4       | NULL |  517 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 索引本身失效,索引数据不真实(解决办法:重建索引或重新收敛索引统计信息optimize table city)

  • 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,?,!)

mysql> explain select id,name,countrycode from city where population-100=200;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select id,name,countrycode from city where population=400-100;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_population | idx_population | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 隐式转换导致索引失效
mysql> explain select * from t1 where c1=1;
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_c1_c3_c4_c2 | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain select * from t1 where c1='1';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1_c3_c4_c2 | idx_c1_c3_c4_c2 | 21      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| k1    | int(11)    | YES  |     | NULL    |       |
| k2    | int(11)    | NO   |     | NULL    |       |
| c1    | char(5)    | YES  | MUL | NULL    |       |
| c2    | char(5)    | NO   |     | NULL    |       |
| c3    | varchar(5) | YES  |     | NULL    |       |
| c4    | varchar(5) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
  • not in,like '%字符串', <>等不使用辅助索引
mysql> explain select * from t1 where c1 like '%aa';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from city where countrycode not in ('USA','CHN');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4188 |    82.19 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

附:思维导图

发表评论

您的电子邮箱地址不会被公开。