跳转至

索引优化

一、避免索引失效

环境准备

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
mysql [test]>select * from tb_seller;
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| sellerid | name                                 | nickname              | password                         | status | address   | createtime          |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| alibaba  | 阿里巴巴                             | 阿里小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| baidu    | 百度科技有限公司                     | 百度小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| huawei   | 华为科技有限公司                     | 华为小店              | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| itcast   | 传智播客教育科技有限公司             | 传智播客              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| itheima  | 黑马程序员                           | 黑马程序员            | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| luoji    | 罗技科技有限公司                     | 罗技小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| oppo     | OPPO科技有限公司                     | OPPO官方旗舰店        | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| ourpalm  | 掌趣科技股份有限公司                 | 掌趣小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| qiandu   | 千度科技                             | 千度小店              | e10adc3949ba59abbe56e057f20f883e | 2      | 北京市    | 2088-01-01 12:00:00 |
| sina     | 新浪科技有限公司                     | 新浪官方旗舰店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| xiaomi   | 小米科技                             | 小米官方旗舰店        | e10adc3949ba59abbe56e057f20f883e | 1      | 西安市    | 2088-01-01 12:00:00 |
| yijia    | 宜家家居                             | 宜家家居旗舰店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
12 rows in set (0.01 sec)

1. 全值匹配

对索引中所有的类都执行具体的值，该情况下，索引生效，执行效率高。

1
2
3
4
5
6
7
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = '1' AND address = '北京市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

和字段的先后顺序没有关系。

1
2
3
4
5
6
7
8
# 调整WHERE字段顺序
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE  status = '1' AND address = '北京市' AND name = '小米科技';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2. 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

2.1 匹配最左前缀法则，走索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 注意观察key_len的长度，索引的长度发生变化
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = '1';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = '1' AND address = '北京市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.2 违法最左前缀法则 ， 索引失效

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# 注意key的值此时为NULL
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE status = '1' AND address = '北京市';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |     8.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE status = '1';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.3 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

1
2
3
4
5
6
7
8
# 只用到name字段的所有，address并没有用到。
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND address = '北京市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3. 范围查询右边的列，不能使用索引

1
2
3
4
5
6
7
8
# 只有name和address使用了索引，address的索引失效。
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status > '1' AND address = '北京市';
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | NULL |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

4. 不要在索引列上进行运算操作

在索引列上作任何运算操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 

1
2
3
4
5
6
7
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE substring(name, 3, 2) = '科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

5. 字符串不加单引号索引失效

在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = '1';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# status = 1没有加单引号，status的索引失效。
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = 1;
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.01 sec)

6. 尽量使用覆盖索引，避免select *

只使用覆盖索引（索引号包含查询列），效率高

1
2
3
4
5
6
7
mysql [test]>EXPLAIN SELECT name,status,address FROM tb_seller WHERE name = '小米科技' AND status = '1' AND address = '西安市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询列超出索引列，性能降低

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql [test]>EXPLAIN SELECT name,status,address,password FROM tb_seller WHERE name = '小米科技' AND status = '1' AND address = '西安市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND status = '1' AND address = '西安市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

using index ：使用覆盖索引的时候就会出现 using where：在查找使用索引的情况下，需要回表去查询所需的数据 using index condition：查找使用了索引，但是需要回表查询数据 using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7. 使用or连接索引会失效

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND nickname = '小米官方旗舰店';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using where |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 使用OR时:type为ALL、key为NULL，没有走索引
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' OR nickname = '小米官方旗舰店';
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_name_sta_addr | NULL | NULL    | NULL |   12 |    19.00 | Using where |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

8. 以%开头的LIKE模糊查询，索引会失效

1
2
3
4
5
6
7
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE name LIKE '%科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

解决方案 ： 通过覆盖索引来解决 

1
2
3
4
5
6
7
mysql [test]>EXPLAIN SELECT name FROM tb_seller WHERE name LIKE '%科技';
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_seller | NULL       | index | NULL          | idx_seller_name_sta_addr | 813     | NULL |   12 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

9. 如果MySQL评估使用索引比全表更慢，则不使用索引。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql [test]>create index idx_seller_address on tb_seller(address);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 查询北京市没有走索引
mysql [test]>explain select * from tb_seller where address='北京市';
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 |    91.67 | Using where |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 查询西安市走索引
mysql [test]>explain select * from tb_seller where address='西安市';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_address | idx_seller_address | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 北京市在表中的记录占的比例特别大，此时mysql直接全表扫描
mysql [test]>select address,count(*) from tb_seller group by address;
+-----------+----------+
| address   | count(*) |
+-----------+----------+
| 北京市    |       11 |
| 西安市    |        1 |
+-----------+----------+
2 rows in set (0.00 sec)

10. IS NULL、IS NOT NULL有时索引失效。

根据表中数据量决定：

如果NULL值比较多，IS NOT NULL走索引；

如果NULL值比较少，IS NULL走索引；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE address IS NULL;
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_address | idx_seller_address | 403     | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE address IS NOT NULL;
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

11. IN走索引，NOT IN 索引失效

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE sellerid IN ('oppe', 'xiaomi', 'sina');
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | PRIMARY       | PRIMARY | 402     | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
# 使用NOT IN索引失效
mysql [test]>EXPLAIN SELECT * FROM tb_seller WHERE sellerid NOT IN ('oppe', 'xiaomi', 'sina');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   12 |    91.67 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

二、单列索引和复合索引

尽量使用复合索引，二减少单列索引

1. 创建复合索引

1
2
3
4
5
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
# 就相当于创建了三个索引 ： 
#     name
#     name + status
#     name + status + address

2. 创建单列索引

1
2
3
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

三、查看索引使用情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql [test]>show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 5     |
| Handler_read_key      | 8     |
| Handler_read_last     | 0     |
| Handler_read_next     | 25    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 61    |
+-----------------------+-------+
7 rows in set (0.05 sec)

mysql [test]>show global status like 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 296     |
| Handler_read_key      | 1840    |
| Handler_read_last     | 0       |
| Handler_read_next     | 300958  |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 92      |
| Handler_read_rnd_next | 2896042 |
+-----------------------+---------+
7 rows in set (0.00 sec)

输出列的含义

Variable_name 含义
Handler_read_first 索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）
Handler_read_key 如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next 按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev 按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd 根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next 在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。