引言

上一篇文章梳理了一下mysql-explain的详细用法以及一些索引优化方式的简单建议。最近出了几天差，发现很多同事对于数据库索引失效、查询慢缺乏足够的应对手段，公司用的是达梦数据库，缺乏适用性，因此本文就通过mysql，来分享一些索引优化经验，以帮助大家利用索引来应对各种慢查询情况。

索引设置未生效

在mysql中，经常会遇到一种情况，明明建了索引，查询字段也是用的索引字段，利用explain却发现并未使用索引，如下图，然后就懵了。其实这个是mysql内部算法优化导致的，mysql得出全表扫描比使用索引更快。因为覆盖索引是二级索引，查询条件为select *，如果走覆盖索引还得去聚合索引里面再回表查询其他字段，效率反而更低。

解决办法如下：

• 如果只是单纯想让索引生效，很简单，在表名后面增加一个force index(idx_name)就可以了，如下图。不过一般加了这句话虽然会强制使用索引，不过效率比不使用索引更慢Σ(っ °Д °;)っ。

• 当然上面那种方法没什么灵性，如果需要查询的字段不多，可以把select * 改成具体字段，通过覆盖索引一次性能查询到所有的数据，优化掉回表耗时，也可以让索引正常生效，而且效率能提升不少，上一篇有例子，这里就不截图了
• 如果优化后仍然查询过慢，不要单纯从索引是否使用下手，看看能否加一些其他查询条件，如果业务确实需要，可以结合搜索引擎进行优化。

mysql内部其实非常多这种优化机制，比如用in 或者or，在数据量比较大的时候，mysql会使用索引，当整表数据量比较小的时候，则直接全表扫描。那么mysql中，如何选择合适的索引呢？总不能真的等发现慢sql再去处理吧？接下来就是我们trace工具了。

mysql选择索引机制分析-trace

下面看两条极其相似的sql，都是基于name的一个范围查询，上面的数据量大走了全表，下面的数据量小，走了索引。接下来通过trace工具来挖一挖mysql内部的决策机制。

首先第一步开启trace：

set session optimizer_trace="enabled=on",end_markers_in_json=on;

然后就是将需要执行的需要分析的sql，就可以从information_schema.OPTIMIZER_TRACE里面查到mysql的分析逻辑了，这里选择和索引以及全表扫描有关的内容，来看看mysql，在可以使用索引的情况下，为何不使用索引，而进行全表扫描。

在trace字段里，有很多内容，我们先看join_optimization属性里面的rows_estimation，其中的table_scan，显示的全表扫描需要扫描的行数以及成本消耗值。同时在potential_range_indexes属性里面可以查看，本次查询哪些索引可以被使用。如下图。

继续往下看日志，看看如果使用索引会消耗多少成本，如下图。很明显使用索引消耗成本值为2万多，而上面全表扫描才1.18万，本着花最小的成本做最多的事原则(●ˇ?ˇ●)，mysql当然直接使用全表扫描啦。

当然用完后记得执行下：set session optimizer_trace="enabled=off"。开启trace很耗性能，记得及时关闭。trace还可以对mysql很对行为进行分析，比如sql优化阶段的查询条件处理、执行阶段的filesort排序分析等。

注意：trace的执行语句要和查询语句一块执行，如果先执行sql语句，再查询OPTIMIZER_TRACE，则trace字段里面的step为空，看起来就和trace未生效一样，效果如下图，这个坑请注意。

关于order by和group by优化方法

在实际业务中，order by和group by都是必不可缺的，不过这两个关键字也经常扮演导致sql查询效率低下的“凶手”。甚至某些运营商的分布式数据库中，禁止使用group by，说是会导致他们的分片键失败，这不是纯纯因噎废食吗？有一些简单的统计分析还非得通过java在内存中计算，复杂的得抽到他们的mpp数据库中才能分析。那不多说，对于大多数开发，还是需要直接使用group by的，我们先看看怎么进行优化。

• 利用覆盖索引，将查询条件以及排序字段一起建立索引，这样即使用了order by，索引也仍然生效。原理可以结合mysql的索引b+树结构思考，因为索引已经将数据排序了，如果order by与where条件的组合符合索引设置，则直接利用索引的排序即可。如下图。

• 使用覆盖索引切记最左法则。当联合索引为多个字段时，查询条件以及order by使用的字段，能做到与索引字段从左开始依次匹配，中间不出现漏字段。文字可能看不懂，看如下图示例。当然记住，order by里面的字段排序要和索引顺序一致，不然还走filesort，自行理解

• 查询条件为in或者范围查询，也会导致排序出现filesort。要想处理此情况，首先order by的字段要和where字段一致，且都为索引字段。其次select后面的查询条件得利用覆盖索引进行优化（PS：如果你的所有字段都为覆盖索引，那么select * 也会使用索引，不过实际中不推荐，万一加字段了或者其他情况都会导致索引失效），如下图，这样排序就可以使用索引了。

总的来说，在使用order by时，出现的时using index效率高，出现filesort代表效率低，需要进行优化。所以尽量在索引列上进行排序，且排序与索引的“最左法则”适配。同时。group by与order by的优化基本一致，这里就不细讲了。最后在查询中，尽量使用覆盖索引，这样能有效提高执行效率。

索引优化实际应用

说完了一些常见的优化策略，接下来就是在实际项目中，如果通过设置索引、优化sql编写等方式，来应对我们项目中常见的条件检索、范围查询、自定义字段查询、分页查询等实际情况，这些情况简单建两条索引肯定是搞不定的，要结合实际情况建立联合索引，或者是通过表关联来优化mysql的分页查询等，实际情况，比较多，详细在下篇文章看吧O(∩_∩)O