mysql like 4种优化_MySQL高级（四）：索引优化

1、准备

上一篇的内容学习了如何使用explain来分析SQL的性能问题，通过这个性能分析利器我们就能够对SQL语句进行索引优化，来帮助我们写出更高性能的SQL语句。好了，接下来开始实战吧。

首先创建一张表：1

2

3

4

5

6

7create table staffs(

id int primary key auto_increment,

name varchar(24) not null default '' comment '姓名',

age int not null default 0 comment '年龄',

pos varchar(20) not null default '' comment '职位',

add_time timestamp not null default current_timestamp comment '入职时间'

) charset utf8 comment '员工记录表';

接着添加三列符合索引：1alter table staffs add index idx_nap(name, age, pos);

2、索引优化

全值匹配

type为ref，key为idx_nap，ref为const,const,const表示使用了非唯一性索引，也就是自己创建的idx_nap索引，并且使用了全部索引列。

最左前缀原则(重要)

最左前缀原则指的是复合索引从左边为起点任何连续的索引都能匹配上。从索引的最左边开始，如果中间断了，断了那个索引列的左边部分索引生效，后面的索引列就不生效了。如果不是从最左边开始的，那么索引不会生效。

key为idx_nap表示使用了索引，但是看key_len长度和全值匹配的长度要少，说明该索引只使用了部分索引列，上图语句中由于断了age，所以实际上只用到了name索引列。

type为ALL进行了全表扫描，索引失效，因为违背了最左前缀原则即没有从索引的最左边的索引列开始。

不在索引列上做操作

不要在索引列上做操作，包括计算、函数、自动或者手动类型转换，这样会导致索引失效而转向全表扫描。

例如下图在索引列上使用了函数导致了索引失效：

varchar类型的字段千万不能少了引号，会导致隐式类型转换，索引失效。

范围之后全失效

索引列使用范围查找，那么该索引列之后的索引列会失效。

例如下图age使用了范围查找，type为range，key为idx_nap，key_len比全值匹配中的长度要少，说明用到了索引，但是age后面的索引列并没有生效。

使用覆盖索引

我们在写查询语句时，尽量不要使用select *，最好用覆盖索引去查找。

下图第二条语句与第一条语句相比使用了覆盖索引，在Extra字段上显示Using index，性能比第一条要好。

第三条使用了范围查找，根据上面所学type应该是range且索引列用到了name和age，但是这里用到了覆盖索引，type为ref级别，索引列用到了name，虽然索引列只用到了一个，但是type为ref显然比range要好。

第五条语句与第四条语句相比说明select查找的列是索引的全部和部分都能使用覆盖索引。

不要使用不等于

使用!=或者<>会导致索引失效而使用全表扫描，如图所示。

like查询不要以通配符开头

使用like查询时，如果以通配符开头%abc，那么索引也会失效导致全表扫描。

但是如果业务中非得使用通配符开头的话，我们可以使用覆盖索引来解决索引失效问题。

少用or或in

使用or或者in也会导致索引失效。

索引对排序的影响

通过之前的学习，我们了解到索引具有排序功能，如果能够利用索引的排序功能对SQL进行排序，也能提高SQL的查询速度。如果不能利用索引的排序功能，MySQL就得自己实现排序功能来满足用户需求，这时通过explain执行计划会发现Extra那一栏出现Using filesort。所以我们在优化SQL时，应该尽量使用索引排序，避免Using filesort。

order by满足两种情况会使用索引进行排序，第一种order by满足索引最左前缀原则，第二种使用where子句与order by子句组合满足最左前缀原则。

索引对分组的影响

group by的实质是先排序后分组，group by使用索引也是遵循最左前缀原则，还有就是能写在where的限定条件就不要用having去限定了。

3、补充

下面注意这种写法，这种写法虽然看起来比较怪异，但是其实是满足最左前缀原则的， MySQL查询优化器会帮我们优化好顺序。

下图的语句中，like为范围查找，根据范围之后全失效，索引列应该只用到了一个，但是实际上全部都用到了，因为like后跟的是Ju%，通配符前面其实是常量了，后面的索引列可以用上。如果like后跟的是%abc%或者%abc，那么这样是范围了，满足范围以后全失效。

4、建议对于单值索引，尽量选择针对当前query过滤性更好的索引。

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引。

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。