前言

之前的两篇文章：

浅谈sql索引 -- 初步介绍索引的特性
MySQL索引性能分析 -- 怎么用explain去查看sql的执行情况

对索引不熟悉的朋友可以看一下，本篇文章是在前两篇的基础上写的，所以之前提过的在这里不会细说。

正文

现在你手头有这么一张表

假如这张表最常用的查询语句是这句：

select id,author_id from article where category_id=1 and comments>1 order by views desc limit1;

让你根据这个语句，去建立一个好的索引，你会怎么做？

怎

么

做

呢

？

建索引简单啊，这么写即可。

CREATE INDEX 索引名称 ON 表名(XX列,XX列...);

可是建立一个好的索引却需要好好思考。

为什么？

你想想，咱们夸张点，第一张图这个表里，咱们随便建立了一个索引在content字段上。

可若真实情况是，基本很少有sql会根据content字段去查找，那么建立了没用不说，还占空间。

如果content字段的内容经常改变，那么改完一次表中的内容，索引也要改，这便是极大的浪费。

那么如何建立好一个索引呢？

还是要多用explain。

我们先用explain，查看一下，最多使用查询语句的执行情况。

这是咱们还没建立索引的情况，可以发现这里面的问题在于问题在于：

1. type的值为ALL -- 这次查询是全表扫描
2. Extra的值为Using filesort -- 排序没用到索引。

那咱们尝试一下用(category_id,comments,views)，建立索引。

这里是根据sql语句where后面的三个字段，按顺序，进行初步尝试。

然后explain一下，看一下执行情况：

初步建立了索引，各项情况好多了，但是Extra的值还是Using filesort，这是个很难受的情况，这就需要修改索引。

我们尝试一下把索引中的comments字段去掉。

去掉一开始建立的索引，新建索引(category_id,views)，结果是：

这样的索引，就比上一个好点，不敢说是最优，至少没有坏的地方。

这里面到底有什么规律呢？

上面的问题是解决了，但看下来总觉得是迷迷糊糊的。

让我们换个角度，来看看这里面有什么规律吧。

我们拿一个建好索引的表，explain不同的sql语句，看看里面的情况。

// 建表语句
create 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 defalut'' comment'职位',
	add_time timestamp not null dafault current_timestamp comment'入职时间'
) charset utf8 comment '员工记录表';

// 插入一些数据
insert into staffs(name,age,pos,add_time) values('z3','22','manager',now());
insert into staffs(name,age,pos,add_time) values('july','23','dev',now());
insert into staffs(name,age,pos,add_time) values('2000','23','dev',now());

// 建立一个索引
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

1.全值匹配

索引的三个值按顺序都用上是效果最好的，如图：

2.最佳左前缀法则

指的是查询从索引的最左前列开始并且不跳过索引中的列。

如果索引了多列，要遵循该法则。

比如，上面的索引idx_staffs_nameAgePos(name,age,pos)中：

假设跳过了name直接查询，那么是全表扫描，相当于完全没用到索引。

假设有name开头，跳过了age，用了pos，即跳过了中间的索引列，那么索引其实只会用到name而已，如图：

有没有用pos字段，表中的ref列都只有一个const，如果pos有用上，那么二表的ref列应该有两个const（参考上一张图，三个索引都用上了，就有三个const），说明pos没用上索引，同时key_len列也能说明这个问题。

3.不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换）

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

如图，left(name,4)='july'是mysql的函数，表示查找name字段左边起4位的值为'july'的结果。

从结果上来说，和上面的select语句查询结果是一致的，但是因为在索引列上做了操作，导致了索引失效。

4.存储引擎不能使用索引中【范围条件】右边的列。

age字段因为变成了范围查询，所以age字段不再在索引里起到检索的作用，而是起到排序的作用，并且pos字段完全失效，何以见得？

因为首先，如果只用到name字段，那么key_len的值应该为74而非78，下图是三个字段逐步使用时key_len的值：

那么现在key_len的值为78而非74，就说明age字段有在用。

索引的作用不止有查找，还有排序。

age的查找作用变弱，并且直接影响到后面的pos索引查找失效，那么age起作用的就是排序。

5.尽量使用覆盖索引

即尽量让索引列和查询列一致，select后面可以的话尽量使用索引列，少使用select *

在这个表中，最好使用select name,age,pos。但这个要根据实际业务来。

6.mysql在使用不等于（!= 或者 <>）时无法使用索引会导致全表扫描。

注意：这里的<> 就是不等于，它是一个符号，而不是小于和大于两个符号。

7.is null，is not null 也无法使用索引。

不信你试试。

8.like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作。

但是右边可以加%（如'abc%'），这样索引不会失效。

这里就会有个面试题了：

解决like '%字符串%'时索引不被使用的方法？

答案：使用覆盖索引。select 主键/索引的任意一个字段都行/最好是按索引顺序来，只是最好，在本案例中，索引为idx_staffs_nameAgePos(name,age,pos)。

可行的情况有：

select id where 哪个字段都行 like '%aa%';  //主键
select age where 哪个字段都行 like '%aa%';  //索引单独第二位第三位也行
select age,pos where 哪个字段都行 like '%aa%';  //索引第二位第三位一起也行
select name where 哪个字段都行 like '%aa%';  //索引第一位肯定行

不行的情况：

//皆为有不是索引的字段
select * where 哪个字段都行 like '%aa%';
select age,add_time where 哪个字段都行 like '%aa%';

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

这个问题的起因是这样的：

mysql的功能比较强大，在本案例的表中，name字段有一个值是2000，那么在mysql中，

select * from staffs where name='2000'

和

select * from staffs where name=2000

这两句话都能查出来数据且结果一样。

但是mysql底层执行时，name字段本身是varchar类型，后一句select中2000是int类型，mysql自己会做一个转化，用的就是函数，那么参考第3条，索引就失效了。

虽然查询结果一样，但查询效率变低了。

10.少用or，用它来连接时索引会失效。

11.小总结

假设现在有索引index(a,b,c)，那么根据where语句的不同，索引的使用情况有:

最后

如果我们熟悉了这些规律，那么在建立索引时就能把该避免的点避免掉，这样做出来的索引，就算不是最优的，也不会是最差的。

通常来说，咱们是根据使用最多的那几个查询语句去建立索引，可是索引建好之后，我们在写查询语句的时候，也应该配合着索引来，这样sql语句才是最高效的。