场景，我使用的数据库是mysql5.6。下面简单介绍一下场景课程：createtableCourse(c_idintPRIMARYKEY,namevarchar(10))with100studentrecords:createtableStudent(idintPRIMARYKEY,namevarchar(10))with70,000studentsResulttableSCCREATEtableSC(sc_idintPRIMARYKEY,s_idint,c_idint,scoreint)700,000itemsofdata查询目的：搜索中文考100分的考生晕，为什么这么慢，先查看查询计划：EXPLAINselects.*fromStudentswheres.s_idin(selects_idfromSCscwheresc.c_id=0andsc.score=100)发现没有使用索引，类型全是ALL，所以首先想到的就是建索引，索引的字段当然就是where条件中的字段。首先对sc表的c_id和score建立索引CREATEindexsc_c_id_indexonSC(c_id);CREATEindexsc_score_indexonSC(score);再次执行上面的查询语句，时间是：1.054s比3w倍快，大大缩短了查询时间，看来索引可以大大提高查询效率，建索引很有必要。很多时候，我忘记建立索引。数据量小的时候，完全感觉不到。这个优化感觉很爽。但是1s还是太长了，能优化一下吗？仔细看执行计划：SELECT`YSB`.`s`.`s_id`AS`s_id`,`YSB`.`s`.`name`AS`name`FROM`YSB`.`Student``s`WHERE(`YSB`.`s`.`s_id`,(SELECTFROM`YSB`.`SC``sc`WHERE((`YSB`.`sc`.`c_id`=0)AND(`YSB`.`s`.`score`=100)AND((`YSB`.`s`.`s_id`)=`YSB`.`sc`.`s_id`))))补充：这里有朋友问如何查看优化语句，方法如下：在命令窗口执行withtype=all按照我之前的想法，SQL的执行顺序应该是先执行子查询selects_idfromSCscwheresc.c_id=0andsc.score=100耗时：0.001s得到如下结果：然后执行selects.*fromStudentswheres.s_idin(7,29,5000)耗时：0.001s，相当快，而不是执行内部查询首先，Mysql将sql优化成exists子句，出现EPENDENTSUBQUERY。Mysql先执行外层查询，再执行内层查询，所以需要循环70007*8次。那么改用连接查询呢？SELECTs.*fromStudentsINNERJOINSCsconsc.s_id=s.s_idwheresc.c_id=0andsc.score=100这里为了重新分析join查询，先暂时删除索引sc_c_id_index，sc_score_index的执行时间为：0.057s。改进，看执行计划：这里有一个表连接，我猜想需要为sc表的s_id建立索引CREATEindexsc_s_id_indexonSC(s_id);showindexfromSC执行连接查询时间：1.076s，甚至变长了，为什么？查看执行计划：优化后的查询语句为：SELECT`YSB`.`s`.`s_id`AS`s_id`,`YSB`.`s`.`name`AS`name`FROM`YSB`.`Student``s`JOIN`YSB`.`SC``sc`WHERE((`YSB`.`sc`.`s_id`=`YSB`.`s`.`s_id`)AND(`YSB`.`sc`.`score`=100)AND(`YSB`.`sc`.`c_id`=0))好像是先连接查询，然后where条件过滤返回到之前的执行计划：这里的where先做条件过滤，再做表连接。执行计划是不固定的，我们先看标准的SQL执行顺序：正常情况下是先join，再进行where过滤，但是在我们的例子中，如果先join，就会有70万条数据发送给join对于操作，所以先进行where过滤是一个明智的解决方案。现在，为了排除mysql查询优化，我写了一个优化过的sqlSELECTs.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentsONt.s_id=s.s_id的意思是先过滤sc表，然后加入表。执行时间为：0.054s，和之前没有建s_id索引的时间差不多。查看执行计划：先提取sc，再join表，这样效率会高很多。现在的问题是，提取sc的时候出现了扫描表，所以现在很清楚需要建立相关的索引CREATEindexsc_c_id_indexonSC(c_id);CREATEindexsc_score_indexonSC(score);然后执行查询：SELECts.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentsONt.s_id=s.s_id执行时间：0.001s，这个时间还是挺靠谱的，快了50倍执行计划：我们看看，先提取sc，然后连表，都用索引，然后执行下sqlSELECTs.*fromStudentsINNERJOINSCsconsc.s_id=s.s_idwheresc.c_id=0andsc.score=100执行时间0.001s执行计划：这里是mysql查询语句优化，先其中进行filter，然后进行join操作，使用索引。最近，重新导入了一些生产数据。经过测试，发现前几天优化的SQL执行效率又变低了。初审：showindexfromSCexecutesqlSELECTs.*fromStudentsINNERJOINSCsconsc.s_id=s.s_idwheresc.c_id=81andsc.score=84SELECTs.*fromStudentsINNERJOINSCsconsc.s_id=s.s_idwheresc.c_id=81andsc。score=84执行时间：0.061s，这个时间有点慢。执行计划：这里使用了intersectunion操作，即合并两个索引同时检索的结果，然后得到字段score和c_id的区分。单从一个领域来看，歧视的程度并不是很大。从SC表中查找，c_id=81检索的结果为70001，score=84的结果为39425，c_id=81和score=84的结果为897，即组合的判别度这两个字段比较高，所以建立联合索引的查询效率会更高。从另外一个角度来看，这张表的数据是300w，以后还会更多。就索引存储而言，这不是一个小数目。磁盘读取，所以索引数量越多，读取磁盘的开销就越大。所以需要根据具体的业务情况建立多列联合索引，我们来试试看。altertableSCdropindexsc_c_id_index;altertableSCdropindexsc_score_index;createindexsc_c_id_score_indexonSC(c_id,score);执行上面的查询语句，消耗时间为：0.007s，这个速度还是可以接受的执行计划：这条语句的优化已经结束可以作为连接查询进行优化。连接表时，可以先用where条件过滤表，然后做表连接（虽然mysql会优化连接表语句）建立合适的索引，必要时建立多列联合索引以学习分析sql执行计划，mysql会优化sql，所以分析执行计划很重要索引优化上面提到的子查询优化以及如何建立索引，当多个字段建立索引时，为每个字段建立一个单独的索引场，后来发现。建立联合索引会更高效，尤其是数据量大，单列区分度不高的时候。单列索引查询语句如下：select*fromuser_test_copywheresex=2andtype=2andage=10Index:CREATEindexuser_test_index_sexonuser_test_copy(sex);CREATEindexuser_test_index_typeonuser_test_copy(type);CREATEindexuser_test_index_ageonuser_test_copy(age);CREATEindexuser_test_index_ageonuser_test_copy(age);分别索引sex,type,age字段00w，查询量为3时间：0.415s执行计划：发现type=index_merge这是mysql对多个单列索引的优化，对结果集使用交并运算为操作多列索引我们可以在这3列上创建多列索引，并一起复制表用于测试select*fromuser_testwheresex=2andtype=2andage=10查询语句：select*fromuser_testwheresex=2andtype=2andage=10执行时间：0.032s，快了10多倍，多列索引区分度越高，速度越快。执行计划：最左前缀多列索引也具有最左前缀的特点。执行语句：select*fromuser_testwheresex=2select*fromuser_testwheresex=2andtype=2select*fromuser_testwheresex=2andage=10将被用于索引，即索引的第一个字段sex应该出现在where条件中。索引覆盖是指查询的所有列都已经建立了索引，这样在获取结果集的时候就不需要再去磁盘中获取其他列的数据，直接返回索引数据。没错，比如：selectsex,type,agefromuser_testwheresex=2andtype=2andage=10执行时间：0.003s，比fetchallfields快。select*fromuser_testwheresex=2andtype=2ORDERBYuser_name时间：0.139s在排序字段上建立索引会提高排序效率createindexuser_name_indexonuser_test(user_name)***附上一些sql调优总结，有空再研究：1.列类型尽量定义为数值类型，长度尽量短，比如主键和外键、类型字段等2.根据需要创建单列索引创建多列联合索引。当单列过滤后还有很多数据时，索引的效率会比较低，即列的区分度会低。3、如果在多列上建立索引，多列的判别会减少，如果多的话，效率会有明显的提升。4、根据业务场景，构建覆盖索引，只查询业务需要的字段。如果这些字段被索引覆盖，查询效率会大大提高。5.需要在多个表连接的字段上建立索引，可以大大提高表连接的效率6.需要在where条件字段上建立索引7.需要在排序字段上建立索引8.索引需要建立在分组字段上9.不要在where条件上使用算术函数，以免索引失效
