对于普通程序员来说最大的工作量就在CURD上了，并发量不高、数据量不大的情况下，SQL性能差一点无所谓，但是随着技术的提升就不应该仅仅满足于能用的SQL了，而应该写出更好的SQL。
网上有很多SQL优化方案的文章，但是我觉得知识不内化，知道也用不了。而知道原理后就能举一反三，在实际工作中用上。
为了这次总结SQL优化方案，之前写了一个 B+Tree总结 ，这次我们也是根据B+Tree原理去理解思考如何优化SQL。

profiling

SQL性能好不好，最硬的指标就是执行时间，这时可以使用到profiling查看具体SQL的执行时间。
使用方法也很简单：

• SET SESSION profiling = 1;
• SQL语句
• show profiles;
  返回结果的Duration就是执行时间，时间精确到了小数点后8位，可以更明显的看出SQL执行耗时的差异。

执行计划 EXPLAIN

profiling只能看到SQL执行的耗时，但是我们需要有一个东西看看我们的SQL究竟走没走索引，走了哪个索引，这时我们就需要使用到explain命令。
对于结果我们通常关注一下几个内容：

type

访问类型，常用的类型有： ALL、index、range、 ref、eq_ref、const、system（从左到右，性能从差到好）。

• system一般在只有1条数据是出现。
• const在使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时出现。
• eq_ref在连接表时字段是第二个表的主键或唯一索引时出现。
• ref在连接表时不是主键或唯一索引时出现，ref和eq_ref的差别在于是否只匹配到唯一的1条记录。
• range索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
• index索引全表扫描，把索引从头到尾扫一遍。
• all全表扫描，没有走索引。

possible_keys

列出该SQL可以使用到的索引，但是只有其中1个会被使用到。

key

实际使用到的索引，肯定包含在possible_keys中。

key_len

索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
如果是单列索引，则为该索引的长度；如果是联合索引，为实际使用到的长度，通过这个长度我们就可以知道是否合理创建/使用到了联合索引。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

Extra

辅助信息，根据辅助信息可以知道是否回表，性能消耗是否严重等。

• Using where：使用了where条件过滤数据。
• Using index：SQL所需要返回的所有列数据均在一棵索引树上，不用回表。
• Using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by。
• Using temporary：表示使用了临时表存储中间结果。
• Using index condition：使用了索引，但是需要回表找到需要的数据。

优化方案分析

为了测试，我创建了一个user表并插入了10条数据：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

不要使用select *，应该列出具体使用的字段。

这应该是网上最常见的说法了，但是不使用select * 就一定更快吗？这个就要具体分析一下了。
根据网上的说法，select * 造成“慢”主要是有3个原因：

1. 数据库需要把 * 翻译成字段名。
2. 因为数据库是通过网络连接，所以多余字段的数据会增加网络传输的时间。
3. 一般来说不能使用到覆盖索引，需要回表。

对于原因1，我们使用profiling测试一下两条SQL：

select * from user;
select id,name,age,email from user;

得到结果：

10    0.00019702    select * from user
11    0.00020811    select id,name,age,email from user

多次测试发现*甚至比列出字段名更快。
查询了一下，有人表示MySQL对*有优化，一般来说原因1并不一定成立。

对于原因2，因为涉及到网络传输，不好测试，但是理论是成立的，数据量大传输耗时就长。
但是我们的程序和数据库都是在同一个内网中，并且一般很少遇到某个字段的数据量非常大的情况。
所以原因2一般也不是主要原因。

对于原因3，如果需要回表，就会在聚簇索引上进行额外的IO操作。

select id,name,age from user where name='ZhangSan' and age=12;

不加索引的explain结果：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ALL                    10    Using where

然后我们创建一个联合索引测试一下：

ALTER TABLE `user`
ADD INDEX `idx_name_age` (`name`, `age`) USING BTREE ;

加索引的explain结果：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    idx_name_age    idx_name_age    128    const,const    1    Using where; Using index

Extra多了一个Using index，说明在该索引上就能直接找到需要的数据，不需要回表。

如果我们再加一个不在联合索引中的值再看看：

select id,name,age,email from user where name='ZhangSan' and age=12;

explain结果：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    idx_name_age    idx_name_age    128    const,const    1    Using index condition

Using index condition，如果有不在联合索引中的字段，则需要回表。

如果要对这3个原因的影响大小进行排序的话，从1到3的影响依次从小到大。
很显然原因3才是造成select * “慢”的主要原因。
当然，如果你没有实现覆盖索引，那么可以认为select *与列出字段的方式性能差别不大。
对于如何实现覆盖索引可以看看MySQL索引原理 B+Tree总结。

最左原则

最左原则是针对联合索引的使用来说的，并不是说where后面多个条件的左边。
以index(name, age)为例，构建的B+Tree是先根据name字段创建，然后再针对相同name的值的数据，再以age字段创建一个B+Tree。
所以我们写SQL时，如果想用到age的B+Tree，那么就一定需要先使用name的B+Tree。
例子SQL就是之前的SQL：

select id,name,age from user where name='ZhangSan' and age=12;

explain：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    idx_name_age    idx_name_age    128    const,const    1    Using where; Using index

可以看到使用到了idx_name_age索引，并且key_len为128。

当然，联合索引我们也可以只使用左边的索引而不使用右边的索引：

select id,name,age from user where name='ZhangSan';

explain:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    idx_name_age    idx_name_age    123    const    1    Using where; Using index

此时我们看到仍然使用了idx_name_age索引，而key_len为123，说明只用到了name，而没有用到age。
另外我们也可以推断出，如果key_len为128，那么则既使用到了name，又使用到了age。

根据最左原则，我们可以推测出，如果我们只想使用age，但不使用name，是不会走索引的。

select id,name,age from user where age=13;

explain:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    index        idx_name_age    128        10    Using where; Using index

等等，这是什么情况？possible_keys为null，但是key使用到了索引，并且key_len为128，说明name和age都使用到了。但是ref为10，因为我们的表里只有10条记录，
这种情况是因为我们需要的字段在index(name, age)上都有，MySQL引擎认为对index(name, age)进行全部数据遍历比扫描全表的开销低，所以对索引进行了全索引的扫描。
我们只要换成*或者加上email字段explain就变成了：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ALL                    10    Using where

table为all，进行了全表扫描。

所以要正确使用联合索引，必须遵循最左原则。

单列索引和联合索引的选择

index(name, age)的情况下，我们不能跳过name直接使用age，但如果必须要直接使用age，就必须对age创建单列索引。
那么我们可不可以将index(name, age)拆分成index(name)和index(age)呢？
能是能，但是我们需要注意业务情况。
拆分后对于SQL：

select id,name,age from user where name='ZhangSan' and age=12;

就只能走1个索引:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    index_name,index_age    index_name    123    const    1    Using index condition; Using where

可以看到possible_keys有2个，分别是index_name,index_age，但实际使用到的是index_name。
这是MySQL自己的选择，与数据内容有一定的关系，因为根据数据name字段上的区分度大于age，所以使用了index_name。
如果我们将数据改一下，多几个ZhangSan，让age=12只有1个：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ref    index_name,index_age    index_age    5    const    1    Using where

此时MySQL又选择了使用index_age。

单列索引有个缺点，那就是一个SQL只能使用1个B+Tree，如果我们的数据name、age每个单列的区分度，但联合起来的区分度高，比如很多人叫张三，很多人12岁，但是叫张三并且12岁的人只有1个，这种情况我们使用单列索引就不能达到很好的效果。
此时就必须根据具体情况，创建index(name, age)或者index(age, name)的联合索引。

like模糊查询

很多时候我们会对name进行模糊查询。比如查询一个名字中有'a'的人：

select * from user where name like '%a%';

explain：

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    ALL                    10    Using where

此时是不能使用索引的，原因也很简单，因为我们需要找中间任意位置有'a'的值，不能使用排序好的索引，即使使用到了索引，也会扫描全部索引的。
但是如果我们改成查询名字以'Z'开头的人：

select * from user where name like 'Z%';

explain:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    range    index_name_age    index_name_age    123        2    Using index condition

此时type为range，说明使用了范围查询，key为index_name_age说明使用到了index(name, age)这个索引，key_len为123说明只使用了name字段上的索引。
也就是说'x%'这种形式是可以走索引的，原因也很简单，在name的B+Tree上找到以x开头的索引值，然后向后遍历链表，直到name值不以x开头即可停止。

索引下推

在上面的情况中我们还会收到一种需求，查询名字以'Z'开头，并且年龄为12岁的人：

select * from user where name like 'Z%' and age=12;

在这种情况中，可能就会用到索引下推来提高查询性能。
首先需要查看你的MySQL版本select VERSION()，如果是5.6以上则支持索引下推。
其实5.6出来已经很久了，基本不是特别老的项目都是使用的5.6以上的MySQL。

在5.6之前，也就是没有索引下推时，根据'Z%'找到2条结果，于是拿着这2条结果回表查询age=12，满足条件则返回。
而使用索引下推后，在匹配到'Z%'的2条结果后，会继续在索引中判断age是否为12。

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    user    range    index_name_age    index_name_age    128        2    Using index condition

这一点我们从索引的key_len可以看出来。

不要在索引字段上做运算或函数操作。

以1年后年龄为12岁的需求为例，sql中会写age + 1 = 12，虽然我们对age做了索引，但是没有对age + 1做索引，MySQL优化器还没有那么智能，我们必须要对等式进行处理，写成age = 12 - 1，即age = 11。

如果必须要使用order by，尽量对order by的列创建索引。

因为索引已经是有序了的，并且所有叶子节点都使用双链表连接，遍历很容易。
而不使用索引，就需要数据库对查出来的数据再进行一次排序，就会消耗性能，这点从Extra字段可以看出：Using filesort。

谨慎对待网上的各种"SQL优化方案"

我在写这边博客的时候也查询了大家的一些方案，但是根据我自己实测，网上很多的“方案”其实并不正确。
这与MySQL的版本也有一定的关系，毕竟MySQL团队也在让MySQL变得更好。
另外也与网上各种博客都是抄来抄去有关。

所以我认为不要将网上别人总结的各种方案奉为瑰宝，一定要自己理解原理，并且根据explain自己去实验才能得到最准确的结论。

另外还有一些SQL优化方案，但是与B+Tree关系不大，就不再列出了。