我使用的数据库是mysql5.6。下面是场景课程的简单介绍：createtableCourse(c_idintPRIMARYKEY,namevarchar(10))100个学生表：createtableStudent(idintPRIMARYKEY,namevarchar(10))data70000个学生成绩表SC：CREATEtableSC(sc_idintPRIMARYKEY,s_idint,c_idint,scoreint)data70w查询目的：查找语文考试100分的考生查询语句：selects.*fromStudentswheres.s_idin(selects_idfromSCscwheresc.c_id=0andsc.score=100)执行时间：30248.271s晕，怎么这么慢，先查查询计划：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);再次执行上面的查询语句，时间是：比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`,(SELECTFROM`YSB`.`SC``sc`WHERE((`YSB`.`sc`.`c_id`=0)AND(`YSB`.`sc`.`score`=100)AND((`YSB`.`s`.`s_id`)=`YSB`.`sc`.`s_id`))))如何查看优化语句？方法如下（在命令窗口执行）：type=all按照我之前的思路，sql的执行顺序应该是执行子查询selects_idfromSCscwheresc.c_id=0andsc.score=100耗时：0.001s得到如下结果：然后Executingselects.*fromStudentswheres.s_idin(7,29,5000)耗时：0.001s，速度还是挺快的。Mysql没有先执行内层查询，而是将sql优化成一个exists子句，出现WithEPENDENTSUBQUERY，mysql先执行外层查询，再执行内层查询，所以需要循环70007*8次。改用连接查询怎么样？SELECTs.*fromStudentsINNERJOINSCsconssc.s_id=s.s_idwheresc.c_id=0andsc.score=100这里为了重新分析连接查询，先暂时删除索引sc_c_id_index，sc_score_index的执行时间为：0.057s。效率提高了，再看执行计划：这里有个连表的情况。我估计是需要为sc表的s_id建立索引CREATEindexsc_s_id_indexonSC(s_id);showindexfromSC然后执行连接查询time: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的查询优化，自己写了一个优化过的sql：SELECTs.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentsONt.s_id=s.s_id表示先过滤sc表然后加入表格。执行时间为：0.054s，和之前没有建s_id索引的时间差不多。查看执行计划：先提取sc，再连接表，这样效率会高很多。现在的问题是，提取sc的时候，有一个scan表，所以现在很清楚，需要建立相关的索引。CREATEindexsc_c_id_indexonSC(c_id);CREATEindexsc_score_indexonSC(score);再次执行查询：SELECTs.*FROM(SELECT*FROMSCscWHEREsc.c_id=0ANDsc.score=100)tINNERJOINStudentsONt.s_id=s.s_idExecutiontime:0.001s这个时间还是挺靠谱的，执行计划快了50倍：Wewillsee该索引用于先提取sc，然后再提取表。然后执行下一条sql：SELECTs.*fromStudentsINNERJOINSCsconssc.s_id=s.s_idwheresc.c_id=0andsc.score=100执行时间0.001s执行计划：这里是mysql查询语句优化，这里先进行过滤，再进行连接操作performed，并且都使用索引。调整内容是SC表的数据增长到300W，学生的分数更加离散。初评：showindexfromSC执行sqlSELECTs.*fromStudentsINNERJOINSCsconssc.s_id=s.s_idwheresc.c_id=81andsc.score=84执行时间：0.061s这个时间有点慢。执行计划：这里使用intersectunion操作，即合并两个索引同时检索的结果，然后看字段score和c_id的判别度。单从一个领域来看，歧视的程度并不是很大。从SC表Retrieve，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嵌套子查询确实效率比较低。它可以优化为连接查询。索引，必要时建立多列联合索引学会分析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字段0和0进行索引：0.415s执行计划：发现type=index_merge这是mysql对多个单列索引的优化，对结果集使用intersectunion操作来操作multi-列索引我们可以在这3列上创建多列索引并复制表以进行测试createindexuser_test_index_sex_type_ageonuser_test(sex,type,age);查询语句：select*fromuser_testwheresex=2andtype=2andage=10执行时间：0.032s快10倍以上，多列索引的判别度越高，速度提升越快多执行计划：最左prefix多列索引和最左前缀的特性：会使用索引，即索引的第一个字段sex要出现在where条件中，执行语句：select*fromuser_testwheresex=2select*fromuser_testwheresex=2andtype=2select*fromuser_testwheresex=2andage=10索引覆盖是指查询的所有列都被索引，这样在获取结果集的时候，不需要去磁盘获取其他列的数据，索引数据可以直接返回，如：selectsex,type,agefromuser_testwheresex=2andtype=2andage=10执行时间：0.003s比取所有字段快，多排序select*fromuser_testwheresex=2andtype=2ORDERBYuser_name时间：0.139s在排序字段上创建索引会提高排序的效率createindexuser_name_indexonuser_test(user_name)***attachedPrevioussql调优的一些总结，以后有时间再深入研究，列类型尽量定义成数值类型，长度尽量短，比如primarykey和foreignkey,typefield等创建单列索引和创建多列联合索引作为单列如果过滤后数据还是很多，那么索引的效率会比较低，即该列的辨别度较低。如果在多列上建立索引，那么多列的区分度就会很大，就会有明显的差异。效率提升根据业务场景构建覆盖索引，只查询业务需要的字段。如果这些字段被索引覆盖，查询效率会大大提高。需要在字段上创建索引。需要在排序字段上创建索引。需要在分组字段上创建索引。不要在Where条件上使用计算函数，以避免索引失败。
