前言
索引优化有很作最佳实践原则,下面对常用原则进行分析。
MySQL 索引底层数据结构和算法:https://blog.csdn.net/yhl_jxy/article/details/88392411
MySQL explan 执行计划详解:https://blog.csdn.net/yhl_jxy/article/details/88570154
优化原则实例 SQL 准备。
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(24) NOT NULL DEFAULT '' COMMENT '用户姓名',
`user_age` int(11) NOT NULL DEFAULT 0 COMMENT '用户年龄',
`user_level` varchar(20) NOT NULL DEFAULT '' COMMENT '用户等级',
`register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
PRIMARY KEY (`id`),
KEY `idx_userName_userAge_userLevel` (`user_name`,`user_age`,`user_level`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
INSERT INTO user(user_name, user_age, user_level, register_time)
VALUES('ZhangSan', 30, 'A', NOW()), ('LiSi', 31, 'B', NOW()), ('WangWu', 31, 'C', NOW());
1、全值匹配
按索引字段顺序匹配使用。
mysql> explain select * from user where user_name = 'ZhangSan';
通过 explain 分析,type 为 ref,使用索引,效率高。key_len 为74,根据 key_len 计算规则,
如果字段类型为 varchar(n),并且是 utf-8 编码格式,则 key_len = 3n+2,where 后使用了
user_name,则 key_len=3*24+2=74,证明索引用到了联合索引的第一个字段 user_name,
从 ref 也可以看到一个 const。
使用联合索引两个字段时:
mysql> explain select * from user where user_name = 'ZhangSan' and user_age = 30;
使用联合索引三个字段时:
mysql> explain select * from user where user_name = 'ZhangSan'and user_age = 30 and user_level = 'A';
2、最佳左前缀法则
如果建的是联合索引,要遵循最左前缀法则。
要想使用索引,where 后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。
mysql> explain select * from user where user_name = 'ZhangSan'and user_age = 30 and user_level = 'A';
按照索引字段顺序使用,三个字段都使用了索引。
mysql> explain select * from user where user_age = 30 and user_level = 'A';
直接跳过 user_name 使用索引字段,索引无效,未使用到索引。
mysql> explain select * from user where user_age = 30 and user_level = 'A' and user_name = 'ZhangSan';
where 后面查询条件顺序是 user_age、user_level、user_name 与我们建的索引顺序
user_name、user_age、user_level 不一致,为什么还是使用了索引,这是因为 MySQL 底层优化
器给咱们做了优化。但是,咱们最好还是按顺序使用索引。
3、不要在索引列上做任何操作,比如计算、使用函数、自动或手动进行类型转换,
会导致索引失效,从而使查询转向全表扫描。
mysql> explain select * from user where user_name = 'ZhangSan';
where 条件直接使用索引字段 user_name 用到了索引。
mysql> explain select * from user where right(user_name, 3) = 'San';
where 条件使用计算后的索引字段 user_name,没有使用索引,索引失效。
4、存储引擎不能使用范围条件右边的索引列。
mysql> explain select * from user where user_name = 'ZhangSan' and user_age = 30 and user_level = 'A';
三个列都使用“=”号,顺序使用三个字段,三个字段都使用了索引。
mysql> explain select * from user where user_name = 'ZhangSan' and user_age > 30 and user_level = 'A';
将 user_age 修改为 ">" 之后,Extra 为 Using index condition,表明索引没有被完全使用,
并且 key_len 由 140 降为 78,说明最后一个字段 user_level 没有使用索引。
即范围之后索引全无效。
但是如果我们把 “>” 变为 ">=" 呢?
mysql> explain select * from user where user_name = 'ZhangSan' and user_age >= 30 and user_level = 'A';
加上等号后,key_len 变为了 140,但是 Using index condition确又表明索引没有被完全使用,
只能说明在满足 user_age 为“=”号条件时全部索引使用,否则,范围之后的索引失效。
5、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句。
mysql> explain select * from user where user_name = 'ZhangSan' and user_age = 30 and user_level = 'A';
Extra 显示 null,表示查询的列未被索引列覆盖,并且 where 筛选条件是索引的前导列,说明用到
了索引,但是部分字段未被索引列覆盖,必须通过“回表”来实现,所以不是纯粹地用到了索引,也
不是完全没用到索引。
mysql> explain select user_name, user_age from user where user_name = 'ZhangSan' and user_age = 30 and user_level = 'A';
将 * 换成索引列,查询时使用了索引,用索引列覆盖查询的 *, 叫做覆盖索引。
6、MySQL 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
mysql> explain select user_name, user_age from user where user_name = 'ZhangSan';
mysql> explain select user_name, user_age from user where user_name != 'ZhangSan';
Extra 显示 Using whre Using index,表示查询的列被索引列覆盖,但是 where 后面条件未使用索
引,说明无法直接通过索引查找查询到符合条件的数据。
7、is null,is not null 也无法使用索引。
mysql> explain select * from user where user_name = 'ZhangSan';
使用了索引。
mysql> explain select * from user where user_name is not null;
未使用索引。
8、like 以通配符开头(like '%aaa')MySQL 索引失效会变成全表扫描操作。
mysql> explain select * from user where user_name like '%San';
% 开头,未使用索引。
mysql> explain select * from user where user_name like 'Zhang%';
% 结尾,使用了索引。
mysql> explain select * from user where user_name like '%Zhang%';
%aaa% 前后都使用 % 号,也未使用索引。
like 使用总结:aaa% 可以使用索引,但是 %aaa 或 %aaa% 相当于范围查询,
无法使用索引,以及在之后的索引列也会索引失效。
总结
关于索引优化原则,可以做如下总结。
全值匹配心上人(这是基本原则),最左前缀要遵行(联合索引一般都围绕最左前缀优化);
带头大哥活才行(联合索引从最左边字段开始使用),中间兄弟规矩行(不能跳过中间的字段,跳过后索引无效);
索引列上少计算(索引列上尽量不要进行计算),范围之后全完蛋(where后面使用范围查询的之后的索引无效);
like百分最右写(%号写最右边,写左边会导致索引失效),覆盖索引别写星(尽量避免select*这样的语句,能写索引列最好);
空值不等还有or,索引失效最无情(is null,is not null,!=,<>,or会导致索引无效);
关于索引优化原则,不同的sql版本会有不同,并且需要结合explain的各项参数分析,需要不断体会,
explain博大精深。