4、mysql索引优化二：SQL优化实践

1、索引设计原则

以Innodb为例，在了解了索引组成之后，我们知道，一个索引mysql都会以一颗B+树来表示，索引建的越多，B+树也就越多，那么mysql维护成本就越高（比如表中数据增删改，都会涉及到每个索引树的变更，甚至是树的重平衡以及page页分裂等这样的重量级变更），占用磁盘空间也就越大，有时反而会影响SQL执行性能。所以我们在设计索引时，不能一味地增加索引，索引并不是越多越好，而是应该根据自己的项目业务情况来合理地设计索引，尽量以联合索引代替多个单值索引，这样可以减少索引的数量，降低索引的维护成本；

如果业务上某个字段具有唯一特性，那么该字段建议建成唯一索引，这样不仅会提高查询性能，还能有效地避免脏数据，即便在应用层做了完善的校验控制，只要没有唯一索引，根据墨菲定律，必然会有脏数据；

另外，我们很多人设计表的同时也会把索引也一并给创建了（除非在你非常了解业务并且十分确定该索引非常有必要），这可能会因为前期业务不确定导致一些索引设计地不合理，我个人理解还是要结合自己项目情况，不一定非要在做数据库设计时就必须把索引设计好，可以在项目开发完成之后再去根据项目整体sql使用情况去设计索引。而且很多时候索引也不是必需的，比如某个表数据量比较小，即使没有索引也能满足性能需求，那么索引也并不是非建不可；

索引是有需求时才应该考虑去创建，创建时不能一味地求多，而是尽量用联合索引代替多个单值索引。一般一张表的索引个数建议不超过三、四个，但这不是绝对地，需要结合实际的业务情况来考虑。同时我们也不能太死板，太受这些“建议”给约束，以至于我们可能存在如下这些误区：
1） 索引宁滥勿缺。 认为一个查询就需要建一个索引。
2） 吝啬索引的创建。 认为索引会消耗空间、 严重拖慢记录的更新以及行的新增速度。
3） 抵制惟一索引。 认为惟一索引一律需要在应用层通过“先查后插” 方式解决。
总之，我们需要根据自己项目的实际业务需求来综合分析，索引的设计没有一个绝对的标准法则来指导我们，但是万变不离其宗：索引的原理，只有理解了索引的原理和Explain关键字的使用，那么我们在实际工作中对索引的设计和优化就会游刃有余。

2、联合索引最佳实践

2.1、利用最左前缀原则
最左前缀是指如果一个索引是由多列组成的联合索引，那么查询的时候从该索引的最左边的列开始依次利用索引中各个列，中间不能有跳跃，这样的sql就会使用到该索引来进行检索，否则，索引将失效。例：user表建了一个联合索引：

以下两条sql都会利用最左前缀原则走了索引：

但是这种情况下就无法走索引，这是因为没有按照联合索引的顺序进行查询，出现了跳跃：

注意使用联合索引的时候，如果第一个索引字段使用范围查询，也会导致索引失效，为什么呢？我们可以假设如果mysql这种情况下走了索引，由于第一个字段使用范围查询，那么查出来的数据对于后面的identity_card字段都是无序的，所以这个索引也只能利用了age字段，然后还需要去回表过滤，如果根据age范围查找出来的数据量非常庞大，那么回表的效率会很低，这样还不如全表查询了，因此mysql在这种场景下就选择不走索引，直接全表查询。

如果涉及到模糊搜索，那么建议使用like 'xxx%'这种右模糊搜索方式，如果表数据量比较大的话，尽量禁止全模糊或者左模糊搜索，这样可能会导致无法使用索引（不符合最左前缀原则）。

2.2、索引覆盖
首先说明一下，索引覆盖并不是只有联合索引才会有，单值索引也同样可以使用这种优化策略，只不过联合索引使用索引覆盖的使用场景更多一些。那么什么叫索引覆盖呢？还是用user表为例，存在一个联合索引：i_user_age_card(age,identity_card)，可能有些业务场景只需要查询用户的age和identity_card两个字段数据，那么我们就可以使用这种sql：

可以看到Extra字段值为Using index，说明使用了索引覆盖优化，使用索引覆盖的话，就不需要进行回表了，只需查找辅助索引树就可以得到所需的数据，这样大大提高了查询效率。因此我们写sql的时候尽量不要select * 这种写法，尽量使用索引覆盖。

2.3、联合索引字段包含查询条件
如果sql里有where、order by、group by这种语句，也尽量让联合索引的字段包含在里面，这样会使用索引来提高效率，如：

上面这条sql使用了sex排序，Extra里出现了Using filesort，这种是很影响性能的，但是如果使用下面的sql：

没有出现Using filesort，这是利用了索引在内存中来排序了。

2.4、索引下推优化
索引下推的介绍可以参见《4、mysql索引优化一：Explain关键字分析》，2.3例子中的sql就使用了索引下推优化，Extra字段值包含了Using index condition，索引下推优化一般出现在辅助联合索引中，并且第一个字段一般是范围查询，除了2.3的例子，like模糊查询在我们的业务中也非常常用，为了方便说明，建了个test表：

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`last_name`,`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

并且创建了一个联合索引：idx_name (last_name,first_name)

如图，这条sql也利用了索引下推优化，索引下推可以有效减少回表次数。

3、order by、group by优化
如果有这两种使用场景，建议利用索引的有序性来排序，由于索引本身是有序的，索引如果order by或者group by的字段存在索引，就会利用索引来排序，会比file_sort效率高。如果是联合索引，需要注意不能违背最左前缀法则，order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，如：where a=? and b=? order by c; 索引： a_b_c，这就会走索引，但是如果where a>10 order by b 或者 where a = 10 order by c，这种情况就无法走索引。
我们在项目中可能经常会遇到这种场景：页面展示列表数据要求按照创建时间的倒序排序，但是create_time字段没有索引，对于这种需求，我们可以给create_time字段加上索引，然后order by create_time desc，但是还有一个更方便的方法：order by id desc，这样create_update字段就无需加索引也可以满足这个需求。这是利用了innodb表自增整型主键的特性，但是如果主键不是自增的或者uuid，就无法使用这种方法了，只能老老实实给create_time加索引。

4、分页优化
mysql的分页一般使用limit关键字来实现，比如：select * from user limit 10000, 20;
这条sql的意思是查询第10001条开始的20条数据，但是mysql的limit关键字存在一个坑：越往后翻页，查询性能会越低，如果是大量数据，可能会成千上万页，那么后面的页的查询可能会非常慢。根本原因在于mysql对于limit的读取方式：mysql并不会跳过前面的10000条记录，直接从10001条开始查，而是先把10020条数据全部给查出来，然后遍历并丢弃前面10000条的数据，只取最后的20条数据，所以当数据量非常大的时候，这个性能是非常低的。

对于一般的业务场景中，传统的limit分页方式可以满足，如果是大数据量，就不能按照上面那样简单来分页查询了，就需要进行一下优化，常见的优化思路如下：

4.1、主键连续数据分页优化
如果表的主键采用了整型自增主键，并且表中数据是按照主键id连续的，那么上面那条sql就可以改成这样：
select * from user where id > 10000 limit 20;
这样就会利用主键索引，减少扫描行数，极大地提高了查询效率，但是这种方式适用的场景非常少，我们实际项目中的数据不能全部是连续的，如果中间删除了一些数据，就不能用这种方法了，这样就会导致查询出来的数据不正确。

4.2、利用子查询优化
我们实际的场景中，一般需要分页查询符合某种条件的数据，如：select * from user where age = 25 limit 10000, 20; 并且age字段有索引，这时候我们可以利用子查询的方式来优化，将sql改成：

select * from user a join(
	select id from user where age =25 limit 500000, 10) b on a.id = b.id

这样mysql就会先利用索引树来查找出符合条件的id字段，再跟进这些id来匹配，这样大大提高的效率。

注：这种优化方式其实并没有根本上解决limit 越翻页越慢的问题，只是利用了索引提高了查询的效率，当数据非常庞大的情况下，计算这种方式也会越往后翻页越慢的现象。

4.3、从业务上来优化
既然我们无法根本上杜绝limit的问题，那么就只能利用变通的方式，从业务需求角度来做一些限制：如果数据量非常大，分页数比较多，从需求上就禁止往后翻太多页，比如只允许翻到第50页就不允许翻了，这样限制也是符合绝大多数用户需求的，一般人不可能无聊到需要往后查这么多数据，大部分情况下只会关注前面几页（最新）的数据。

5、总结

SQL优化场景非常多，我就不一一列举了，这里只是举了几个工作中比较常见的例子，来演示如何根据前面介绍的索引原理以及Explain关键字来进行分析和优化，实际工作中可能会遇到各种各样的sql，但是优化的思路不会变，核心就是索引原理和Explain关键字，这样就可以应对各种sql、各种优化需求。