场景中使用的数据库是mysql5.6，下面简单介绍一下场景。创建表Course(c_idintPRIMARYKEY,namevarchar(10))包含100个数据项。学生表createtableStudent(idintPRIMARYKEY,namevarchar(10))有70000条数据。学生成绩表CREATE表SC(sc_idintPRIMARYKEY,s_idint,c_idint,scoreint)数据70w。查询目的：查找汉语考试成绩为100分的考生。查询语句：selects.*fromStudentwheres.s_idin(selects_idfromSCscwheresc.c_id=0andsc.score=100)执行时间：30248.271s晕，怎么这么慢，我们查一下查询先计划：EXPLAINselects.*fromStudentswheres.s_idin(selects_idfromSCscwheresc.c_id=0andsc.score=100)发现没有使用索引，类型全是ALL，所以首先想到的就是创建Index，索引的字段当然是where条件中的字段。首先对sc表的c_id和score建立索引。在SC(c_id)上创建索引sc_c_id_index；在SC(score)上创建索引sc_score_index；再次执行上面的查询语句，时间是：比3w倍快1.054s，大大缩短了查询时间，貌似可以大大提高索引为了查询效率，建索引是很有必要的，很多时候忘记了建造它。有索引，数据量小的时候，完全感觉不到。这个优化感觉很爽。但是1s还是太长了，能优化一下吗？仔细看执行计划：查看优化后的sql:SELECT`YSB`.`s`.`s_id`AS`s_id`,`YSB`.`s`.`name`AS`name`FROM`YSB`.`Student``s`WHERE(`YSB`.`s`.`s_id`,(SELECT1FROM`YSB`.`SC``sc`WHERE((`YSB`.`sc`.`c_id`=0)AND(`YSB`.`sc`.`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这里为了重新分析连接查询，先临时删除索引sc_c_id_index和sc_score_index。执行时间为：0.057s。效率得到了提高。看执行计划：这里有表连接。我猜想有必要为sc表的s_id创建一个索引。在SC(s_id)上创建索引sc_s_id_index；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过滤。但是我们这里的情况，如果joinfirst，会有70万条数据发送给join进行操作，所以先执行where。过滤是一个明智的解决方案。现在为了排除mysql查询优化，自己写了一个优化过的sql。SELECTs.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentssONt.s_id=s.s_id即先进行sc表过滤，再进行表join，而执行时间为：0.054s。和之前没有建s_id索引的时间差不多。查看执行计划：先提取sc，再连接表，这样效率会高很多。现在的问题是，提取sc的时候，有一个scan表，所以现在很清楚，需要建立相关的索引。在SC(c_id)上创建索引sc_c_id_index；在SC(score)上创建索引sc_score_index；再次执行查询：SELECTs.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentsONt.s_id=s.s_id的执行时间为：0.001s，这是相当可靠的，而且速度快了50倍。执行计划：我们会看到使用索引先提取sc，再提取表。然后再次执行sql。SELECTs.*fromStudentsINNERJOINSCsconsc.s_id=s.s_idwheresc.c_id=0andsc.score=100执行时间0.001s执行计划：这里是mysql查询语句优化，这里先进行过滤，再执行连接操作，并且都使用索引。添加于2015-04-30：最近，重新导入了一些生产数据。经过测试，发现前几天优化过的SQL执行效率又变低了。调整内容是SC表的数据增长到300W，学生的分数更加离散。初评：showindexfromSCexecutesqlSELECTs.*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，这个速度还是可以接受的。执行计划：这条语句的优化暂时告一段落。综上所述，mysql嵌套子查询的效率确实比较低。它可以优化为连接查询。连接表时，可以先用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);分别对sex、type、age字段进行索引。数据量300w，查询时间0.415s。多列索引我们可以在这三列上建立多列索引，并复制表进行测试。在user_test(sex,type,age)上创建索引user_test_index_sex_type_age；查询语句：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=10Executiontime:0.003s比fetchallfields快很多。排序select*fromuser_testwheresex=2andtype=2ORDERBYuser_name时间：0.139s在排序字段上建立索引会提高排序效率。createindexuser_name_indexonuser_test(user_name)最后附上一些sql调优总结，以后有空再深入研究。列类型尽量定义为数值类型，长度尽量短，如主键和外键、类型字段等。创建单列索引。根据需要创建多列联合索引。当单列过滤后还有很多数据时，索引的效率会比较低，即该列的区分度较低。那么如果在多列上建立索引，那么多列的区分度就会高很多，效率也会有明显的提升。根据业务场景构建覆盖索引。只查询业务需要的字段。如果这些字段被索引覆盖，查询效率会大大提高。需要在多表连接的字段上建立索引，可以大大提高表连接的效率。需要在where条件字段上建立索引。排序字段需要索引。需要在分组字段上建立索引。不要在Where条件中使用算术函数，以避免索引失败。
