践行索引优化

Posted on Edited on In

  • Table Demo

  • DB Version

  • Case

  • 全值匹配

  • 最左前缀

  • 禁止索引列上做任何操作（计算、函数、（自动or手动）类型转换）

  • 存储引擎不能使用索引中范围条件右边的列

  • 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

  • mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

  • is null,is not null 一般情况下也无法使用索引

  • like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

  • like 的优化

  • 字符串不加单引号索引失效

  • 少用or或in

  • 范围查询优化

  • 索引总结

  • 搞定MySQL

Table Demo

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (id),
  KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

DB Version

1
2
3
4
5
6
7
8
9
 mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)

mysql>

Case

1
KEY idx_name_age_position (name,age,position) USING BTREE

联合索引

全值匹配

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

算算这个ke_len

key_len ： 显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

【字符串】

  • char(n)：n字节长度

  • varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

【数值类型】

  • tinyint：1字节

  • smallint：2字节

  • int：4字节

  • bigint：8字节

【时间类型】

  • date：3字节

  • timestamp：4字节

  • datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

name varchar(24) —> 3 * 24 + 2 = 74 , 用了联合索引中的name .

1
2
3
4
5
6
7
mysql> explain select * from employees where name = 'LiLei' and age= 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
*

key_len 变成了 78 ？

第二个是int , int 占 4个字节 ， 74 + 4 = 78 ，这个SQL用了联合索引中的 name + age

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

key_len = 74 + 4 + 72 = 140

那我们跳过age 呢 ？

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei'  and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

用了联合所以中的 name

最左前缀

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

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei' and age= 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

符合最左前缀。

*

不符合最左前缀

user where : 使用 where 语句来处理结果，并且查询的列未被索引覆盖

* 不符合最左前缀

user where : 使用 where 语句来处理结果，并且查询的列未被索引覆盖

禁止索引列上做任何操作（计算、函数、（自动or手动）类型转换）

会导致索引失效而转向全表扫描

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where left(name,2) = 'LiLei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

结合索引那个B+Tree , 特征 排好序

*

left 函数，MYSQL并没有做优化 ，left(name,2) 在那棵B+Tree上并没有，肯定不会走索引。

看看函数的操作

加个索引

1
alter table employees add index idx_hire_time(hire_time) using btree;

查看目前的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show index from employees ;
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY               |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            2 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            3 | position    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_hire_time         |            1 | hire_time   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql>

在索引列上使用函数

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where date(hire_time)='2018-09-30';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

变幻一下

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where  hire_time>='2018-09-30 00:00:00' and  hire_time<='2018-09-30 23:59:59';
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_hire_time | idx_hire_time | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

好了，实验完毕

移除索引

1
alter table employees drop  index idx_hire_time;

存储引擎不能使用索引中范围条件右边的列

比对一下

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

key_len = 140 （74 + 4 + 78） 全部走了 idx_name_age_position (name,age,position)

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name = 'LiLei' and age> 22 and position = 'manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

key_len = 78 （74 + 4 ） 走了 idx_name_age_position (name,age,position) 中的 name 和 age

为什么呢？

脑海中找到那个B+Tree

*

name 是相同的， 所以第二列 age 肯定是有序的， 而age这里取的是大于， age是大于， 第三列没办法保证有序。 如果age是等于，那可以，第三列有序。 上面这个图不是很合适，不要被误导了，放上去仅仅是为了让读者对B+树有个轮廓。

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name , age  from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

看到第二个的 Extra ： Using Index 使用了覆盖索引

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

1
2
3
4
5
6
7
8
mysql> 
mysql> explain select * from employees where name != 'LiLei' ;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

is null,is not null 一般情况下也无法使用索引

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name is null ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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 | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

null 值在树中会放到一起和其他节点搞个双向指针

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

1
2
3
4
5
6
7
mysql> explain select * from employees where name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

%在前

还是要回想那个索引B+Tree , % 在前面 意味着前面可能还有其他的字符串， 那在树中的有序性没法保证啊

1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where name like 'Lei%';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

继续回想那个索引B+Tree , % 不在前面 意味着%前面的字符串固定， 那在树中的就是有序的，当然可以走索引

*

key_len = 74 ,可以推导出 走了 联合索引中的name

like 的优化

【问题：解决like’%字符串%’索引不被使用的方法？】

A： 使用覆盖索引，查询字段必须是建立覆盖索引字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> explain select * from employees where name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> 
mysql> explain select name ,age position  from employees where name like '%Lei';
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
* 不敢说好太多， index 总比 all 好吧 。

B： 如果不能使用覆盖索引则可能需要借助搜索引擎 ，Es等

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

*

少用or或in

用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

*

范围查询优化

增加索引

1
alter table employees add index idx_age(age) using BTREE;
1
2
3
4
5
6
7
8
9
mysql> explain select * from employees where age>=1 and age<=2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_age       | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法： 可以将大的范围拆分成多个小范围

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain select * from employees where age>=1 and age<=10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from employees where age>=11 and age<=20;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
*

还原索引

1
alter table employees drop  index idx_age ;

索引总结

* **like KK%相当于=常量，%KK和%KK% 相当于范围**