搜索
第一PPT模板网！执行计划中的typecolumn查看MySQL对查询语句的执行计划，在查询语句前加EXPLAIN关键字即可。这一节我们谈一谈执行计划中非常关键的列：type。Type列的枚举值有这样几个：All，index，range，ref，eq_ref,const(system),null。情况由坏到好，其中null最好，MySQL可以在optimization阶段完成查询，甚至无需读取数据。下面对几个枚举值的含义作诠释：1.All，代表MySQL不得不对数据表做全表扫描。这是最差的type，除非sql本身就是全表查询，否则一定要想办法优化，改变type为其他值。2.Index，代表MySQL需要对数据表做索引序上的全表扫描，这种扫描代价甚至可能会大于all类型，因为扫描对于硬盘是随机取，io代价高于顺序扫描的可能性很高。执行计划中的typecolumn3.Range，代表MySQL根据where条件内的某个过滤条件可以做出一个indexrange，从而缩小访问范围，前提是过滤条件相关字段上有索引。常见的出现场景如depart_dateXXXXX或者betweenand运算符。需要注意的是，IN（）在函数参数数目不太多的时候，也可以达成range条件，但是太多可能会退化为allType。4.Ref，典型的indexaccess，MySQL可以让索引和一个引用值匹配，以达到快速定位的目的，这个值可能是sql中的常量，也可能是多表查询中前一个表中的某个引用。一般地，如果我们的执行计划type字段为此值，且rows字段值不太大，那么这条执行计划的时间代价都会比较小。执行计划中的typecolumn5.eq_ref,这种类型表明MySQL知道它至多只需要返回一行数据。这种type会出现在查询中拿表的主键或者含唯一索引的列和特定引用值作对比的时候。这种type的执行很高效，因为MySQL一旦发现了一个匹配值，即可直接返回，无须再考虑其他匹配可能。6.Constant，往往出现于表的主键等于某个引用值的条件出现在where条件中，mysql可以把这种查询转换为常量，非常高效。规避all以及indextype这两种执行计划的出现，往往意味着你的查询内没有使用到合适的索引。可以按照以下步骤检查：1.表的可用索引有哪些？你的where查询条件中有用到和索引相关的字段吗？2.有没有可能是你的where子句内的查询条件相关字段不是某个联合索引的最左前缀？类似：如果我们写:……wherestock_type=2limit200;这种情况下，mysql是无法走这个联合索引的。3.若非联合索引的问题，可否在相关字段上加索引？加索引的基本原则是，字段对记录的区分度足够高（该字段的值集足够大，不同值足够多，区分效果好），且字长不太长。此原则系指导原则，无硬性标准，具体是否添加需要结合具体场景。4.如果是多个查询条件的与，且多重过滤的效果足够好，可否考虑添加联合索引?Mysql对rangecondition的优化来看一个官网给出的例子：其中key1上含索引，而nonkey上则无索引。1.首先从原始的where子句开始优化2.首先，我们移除掉其中的nonkey=4以及key1like‘%b’条件，前者因为无法走索引，后者则因为mysql不支持非最左前缀的索引匹配。为了确保结果集正确，我们为其替换上true。Mysql对rangecondition的优化3.目前的语句是这样的：可以注意到，key1like‘abcde%’orTRUE是永真的，而key1‘uux’andkey1‘z’是永假的，故这两个条件可以被替换掉。替换之后4.把上面的语句中移除掉无必要的true和false，得到：最后得到；至此range优化工作完成。当然，通过优化后的条件走indexlookup之后得到的记录还需要经过两个无法走索引的条件的筛选。选择合适的索引索引的目的，永远是让MySQL快速定位记录，缩短查询时间的，所以选择索引的宗旨，往往是这个字段上的索引所带来的区分度是否足够高。比如，stk_nm.stouck_round，系批次表，此表的round_code之前是无索引的，相关的sql查询就比较慢，那么在它之上加索引到底有无必要呢？首先，以此字段作为过滤条件的sql语句执行次数足够高，有一万多次，每次平均1.3s，很有优化的必要。其次，此字段对于记录的筛选效果足够好，关于此属性，可以根据不重复的round_code总数目在表记录中的占比大小来判定。故而尽管此字段属varchar(19),字长相对长，但是有做索引的必要，做索引之后查询效率可以得到大大提升。需要注意的是，变更非常频繁的字段上尽量避免做index，诸如update_time或者update_uid，因为InnoDB默认的索引存储方式是b树，字段值变更之后相应索引也要变更，进而引起整个b树结构的变化，是代价较高的操作。Nodependentsubquery！什么情况下会出现dependentsubquery？独立子查询在上述的sql中出现了，而它为何需要规避？MySQL会根据第一条执行计划，以stock_round表作为依赖，将独立子查询涉及的查询语句执行527442次！子查询的特点就是，子查询中的第一个SELECT语句直接依赖于外层结果集，这种强耦合的关系，造成了不必要的多次查询，降低了查询性能。在MySQL5.6及以上版本中，子查询问题得到了很大程度上优化，故此问题在这个版本以上不是那么重要，但是我们公司用的是5.5，所以这种执行计划已然必须规避。Nodependentsubquery！最推荐的规避方案，是使用联表方式替代独立子查询。如果遇到了联表无法表达独立子查询的语义的时候，至少也应该拆分查询并在java代码内拼装数据，而不能对独立子查询视而不见。执行计划中的extra列执行计划中的extra列中常见的枚举值是usingindex，usingwhere，usingtemporary以及usingfilesort。首先谈谈usingindex。这里的usingindex，并不是在说MySQL会对此执行计划走索引，而是代表着MySQL可以走coveringindex，只读b树，甚至不去访问真实记录，查询效率很高！也就是说，我们如果可能的话，应该尽量让extra内出现此hint。下面看几个例子：e.g1为何能走usingindex？因为stock_type和id上都有索引，MySQL在检索b树的时候就能直接根据索引返回结果，无需再去访问真实数据记录。执行计划中的extra列e.g2这个例子用到了一点不是很容易想得到的小技巧。考虑这样一个语句：SELECTid,title,contentFROMarticleORDERBYcreatedDESCLIMIT10000,10;我们考虑created字段上已经有索引了，但是只要limit偏移量大些，查询效率依然高不起来。那么有没有什么办法优化呢？修改为以下格式：Selectid,title,contentfromarticlewhereidin(selectidfromarticleorderbycreateddesclimit10000,10);这样修改之后，子查询可以使用coveringindex，大幅提升查询效率。e.g3SELECTCOUNT(*)FROMarticlesWHEREcategory_id=...只要在category_id上做了索引，此查询就可以走coveringindex，因为MySQL只需要在访问b树的时候对满足条件的记录进行计数，无需访问真实记录。执行计划中的extra列一般形式：SELECTaFROM...WHEREb=...如果遇到了这种形式的sql，我们至少应该考虑下是否要走coveringindex，即作出idx_b_a(b,a)这样的索引。如果a不止一个字段，那么需要考量，因为太长的联合索引维护代价很大。UsingWhereUsingwhere的出现，表示MySQL在从存储引擎读取出记录之后，需要根据过滤条件进行后过滤（post-filter),usingwhere和usingindex有点对立的意思，因为一旦MySQL试用了coveringindex完成了查询，那么它不可能再去访问存储引擎，也就不可能出现usingwhere。Usingtemporary以及Usingfilesort这两个hint的出现意味着你的sql有必要优化了，因为MySQL不得不产生临时表和做内存内或者硬盘内额外排序以满足需求。执行计划中的extra列Usingtemporary很多时候是因为不恰当的groupby计划引起的，下面谈一谈MySQL对于groupby语句的优化。比较好的groupbyexecutionplan应该走indexaccess，否则MySQL就只能创建临时表了，这正是我们不希望的。走indexaccess有两种方案：1.Looseindexscan（松散索引扫描）触发条件：I：单表查询（多表查，且groupby子句内字段非全是驱动表有索引字段，或者不按照驱动表索引有序，都会产生Usingtemporary）。II：最左前缀原则。考虑一个表有这样的索引：idx(c1,c2,c3),那么groupbyc1,groupbyc1,c2都可以触发，但是类似groupbyc2,c3则无法触发。III：集合函数仅允许包含min()以及max(),其中包含的列必须一致，且必须在联合索引内，或者含单字段索引。IV：包含varchar等变长字段类型等，字段必须含全文索引，不支持字段上的左前缀索引。执行计划中的extra列如果looseindexscan成功触发，你应该能在exrta内看到Usingindexforgroup-by。下面的语句均可以触发上述前提的LIS：在MySQL5.5中，增加了对以下函数的支持：AVG(DISTINCT),SUM(DISTINCT),andCOUNT(DISTINCT)aresupported.AVG(DISTINCT)andSUM(DISTINCT)takeasingleargument。Tightindexscan：执行计划中的extra列在looseindexscan无法被触发条件下，MySQL也能有另外一条途径避免因groupby引起临时表的创建，即tightindexscan，如果where子句内含rangecondition，MySQL可以根据这个条件锁定一个范围的index，从而避免临时表，如果没有，MySQL就做全表的indexscan，也可以避免临时表。Usingfilesort：这个hint往往是对记录排序引起的。Usingfilesort意味着mysql需要对orderby在内存甚至硬盘内做一次额外的排序，产生相应的io代价等，如果能够规避，需要规避。规避它的办法，还是依靠索引，下图所示的情况都可以让mysql索引排序，而非额外排序：执行计划中的extra列如图示，可见除开orderby中的列均能对应上相关索引以外，where字居中包含对索引列字段和常量之间的比较的时候，也是可以规避usingFilesort的。然而在以下情况下，MySQL无法使用索引来解决orderby，只能产生额外排序：执行计划中的extra列1.对不同索引列进行排序，如Select*fromt1orderbykey1,key2其中key1和key2都是索引列。2.Orderby字句跟的不是索引或者索引的最左前缀。3.既使用到了asc也使用到了desc，这时MySQL无法走索引以完成orderby。4.Where字