mysql 复杂sql创建索引的的方法

mysql查询慢是一个常见问题，当单表数据量一大，关联表一多查询就慢，最好的办法就是优化sql和创建合适的索引，如何优化sql不在本篇细讲，本篇主要讲，在复杂查询情况下，如何创建合适的索引，mysql技术资料或者书籍上讲解的也都是几个简单法则，一旦遇到复杂的多表查询，就显得过于简单，亦或是高手优化了一下索引，却也不敢打包票是否已是最佳，显得仿若羚羊挂角撞运气。本篇讲的就是在复杂查询背景下，通过研究sql查询的路线，创建匹配查询路线的索引，从而获得极致的查询速度的一套方法。

一、创建索引疑惑

       创建索引是否必要不必说，只是对以下两点常见的疑惑进行解释

1，sql和索引谁先谁后？先有sql，然后分析sql，然后根据需要创建索引

2，要创建多少索引？一个表索引多了对性能有影响，有多少影响？一张表在系统中对应若干sql，分析每一个sql可能都有索引需求产生，这样汇总后索引多了，怎么办？

根据sql需要创建索引，需要就创建，增加索引对数据库影响可以忽略，因为一条索引，只会让数据库服务器增加一个异步线程，增加一条好的索引，能加快数据的查询，提高cpu的效能，所获得的收益，远远大于一个线程的损耗；反过来看， 增加索引带来的损耗，可以通过堆叠的方式来削减，增加CPU核数，能够提供更多的并行线程（一个CPU核=两个线程），增加磁盘，抵消索引带来的磁盘消耗(一个索引一个表的拷贝)。所以如果按查询必须越快越好的要求，那么就应该大胆创建索引。

二、案例

我们以小学业务系统为例:

需求背景：1993-2024年数据，学生数据3万条，6个年级，每个年级20个班，每年度

分为上下两个学期，每个年级课程不完全相同，有6-8门课程，每个学期的每门课期末成绩记入score成绩表，如上图分数表有一百七十多万数据，从百万级数据表里面捞数据，具体要求如下:

查2015年6年级2班全班数学成绩

select
student.name ,course.name ,term.name ,term.updown ,term.`year` ,score.score,clasz.name
from score
left join student on score.student_id = student.id
left join course on score.course_id = course.id
left join term on score.term_id = term.id
left join studentclass on student.id = studentclass.student_id and
term.`year`=studentclass.`year`
left join clasz on clasz.id = studentclass.class_id
where term.`year` =2015 and term.updown = 1 and course.name ='数学' and clasz.name
= '六年级2班

三，创建配套查询路线的索引

我们的确需求一套办法，快速、精准创建索引

step1，初诊

1，去掉sql缓存，以便掌握真实的访问速度

SET GLOBAL query_cache_size = 0;SET GLOBAL query_cache_type=OFF;

2，所有表，除自然id,无二级索引，查询3次，分别耗时4.121/4.172/4.4秒

3，explain

step2，执行计划的查看

通过执行计划，研究sql的查询路线，显然，第一行表明了哪个表是驱动表，后面是被驱

动表，这是mysql引擎自动调节的（这个自动调节具体的就是解析重构sql，所以

第一次查询耗时往往较长，这个与常说的数据缓存不是一回事，所以sql第一次查询时间一般不计入统计）

step3，分析mysql查询路线

step4，根据mysql查询路线,创建索引

step5，检查索引效果

step6，索引加上后，再分析mysql查询路径（step3、step4再次重复一遍），矫正索引

step7，矫正索引后，效果

四，总结

  分析sql的查询路线-执行计划，说白了就是分析mysql查询引擎干活的顺序，如果我们要获得极致的查询速度，即要让mysql查询引擎在查询时，每一步都查询的快(一步可以理解为explain展示的每一行)，每一步都查询的快，也就意味着每一步都有查询索引作为凭借，那么我们就需要创建匹配查询路线的索引，只有这样，mysql查询才会极快。那么“创建匹配查询路线的索引“即为创建索引的最佳办法。

   那些sql没有获得极致的查询速度，往往是查询路线中的所有步骤，只有部分或者全部没有利用到索引；反过来说，那些获得一定提速的sql，却没有获得极致的查询速度的，可能只对其中几步加了匹配的索引，而没有全部应加尽加。

   当然每次遇到查询慢，不必都从第一步干起，掌握以上精髓，只需explain一下，然后按以上方法写写排排序就好了。我们这套办法基本上抛弃了传统的详尽分析explain的方法，传统方法要分析每一行几个关键属性，然后反向推导索引，而且每次只能推导一层，如果是级联多级索引，那就非常烧脑了，很麻烦。我们这套方法，能够对查询路线中的每一步进行检查，做到应加速尽量加速，从而获得极致的查询速度。当然你也可以用这套办法，检查现有的sql，看看是不是索引没加全，或者索引建错了，甚至于看看还有没有提升的空间，都是可以的

   一般来说，简单百万级的数据查询，本地查询小于2毫秒，复杂百万级数据查询，小于10毫秒，简单千万级的数据查询，本地查询小于20毫秒，复杂千万级数据查询，50左右毫秒(有的还需优化sql,敬请期待下一篇，也是与众不同)。不管多复杂，一般来说亿级以下的数据查询超过1秒，一定有问题。

   以上只是考虑sql本身已经是最优的。简单的sql，自不必多说，往往是复杂的sql，如何调整才能做到最优是很需要经验和方法的，敬请期待下一篇。