一、概述
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: 03. 查询索引
|
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)- 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)- 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)- range： 索引范围扫描
造成索引范围扫描：>,>=,<,<=,like,between and,or,in,!=(仅限主键列，非主键列不使用索引),not in(仅限主键列，非主键列不使用索引)
|
>, >=, < , <=, like, between and 可以受到B+TREE的索引优化 or, in无法受到B+TREE的索引优化，在实际应用情况中，or和 in的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)附：思维导图
