high performance sqls

《high performance sqls》由会员分享，可在线阅读，更多相关《high performance sqls（21页珍藏版）》请在文档大全上搜索。

1、执行计划中的type column规避all以及index类型优化range condition选择适当的索引执行计划中的extra column规避using temporary 以及using filesortNo dependent subquery！执行计划中的type column查看MySQL对查询语句的执行计划，在查询语句前加EXPLAIN关键字即可。这一节我们谈一谈执行计划中非常关键的列：type。Type列的枚举值有这样几个：All，index，range，ref，eq_ref,const(system),null。情况由坏到好，其中null最好，MySQL可以在optimi

2、zation阶段完成查询，甚至无需读取数据。下面对几个枚举值的含义作诠释：1.All，代表MySQL不得不对数据表做全表扫描。这是最差的type，除非sql本身就是全表查询，否则一定要想办法优化，改变type为其他值。2.Index，代表MySQL需要对数据表做索引序上的全表扫描，这种扫描代价甚至可能会大于all类型，因为扫描对于硬盘是随机取，io代价高于顺序扫描的可能性很高。执行计划中的type column3.Range，代表MySQL根据where条件内的某个过滤条件可以做出一个index range，从而缩小访问范围，前提是过滤条件相关字段上有索引。常见的出现场景如depart_dat

3、eXXXXX或者between and运算符。需要注意的是，IN（）在函数参数数目不太多的时候，也可以达成range条件，但是太多可能会退化为allType。4.Ref，典型的index access，MySQL可以让索引和一个引用值匹配，以达到快速定位的目的，这个值可能是sql中的常量，也可能是多表查询中前一个表中的某个引用。一般地，如果我们的执行计划type字段为此值，且rows字段值不太大，那么这条执行计划的时间代价都会比较小。执行计划中的type column5.eq_ref,这种类型表明MySQL知道它至多只需要返回一行数据。这种type会出现在查询中拿表的主键或者含唯一索引的列和特

4、定引用值作对比的时候。这种type的执行很高效，因为MySQL一旦发现了一个匹配值，即可直接返回，无须再考虑其他匹配可能。6.Constant，往往出现于表的主键等于某个引用值的条件出现在where条件中，mysql可以把这种查询转换为常量，非常高效。规避all以及index type这两种执行计划的出现，往往意味着你的查询内没有使用到合适的索引。可以按照以下步骤检查：1.表的可用索引有哪些？你的where查询条件中有用到和索引相关的字段吗？2.有没有可能是你的where子句内的查询条件相关字段不是某个联合索引的最左前缀？类似：如果我们写:where stock_type = 2 limit

5、200;这种情况下，mysql是无法走这个联合索引的。3.若非联合索引的问题，可否在相关字段上加索引？加索引的基本原则是，字段对记录的区分度足够高（该字段的值集足够大，不同值足够多，区分效果好），且字长不太长。此原则系指导原则，无硬性标准，具体是否添加 需要结合具体场景。4.如果是多个查询条件的与，且多重过滤的效果足够好，可否考虑添加联合索引?Mysql对range condition的优化来看一个官网给出的例子：其中key1上含索引，而nonkey上则无索引。1.首先从原始的where子句开始优化2.首先，我们移除掉其中的nonkey=4以及key1 like %b条件，前者因为无法走索引

6、，后者则因为mysql不支持非最左前缀的索引匹配。为了确保结果集正确 ，我们为其替换上true。Mysql对range condition的优化3. 目前的语句是这样的： 可以注意到，key1 like abcde% or TRUE 是永真的，而key1 z是永假的，故这两个条件可以被替换掉。替换之后4.把上面的语句中移除掉无必要的true和false，得到： 最后得到；至此range优化工作完成。当然，通过优化后的条件走index lookup之后得到的记录还需要经过两个无法走索引的条件的筛选。选择合适的索引索引的目的，永远是让MySQL快速定位记录，缩短查询时间的，所以选择索引的宗旨，往往

7、是这个字段上的索引所带来的区分度是否足够高。比如，stk_nm.stouck_round ，系批次表，此表的round_code之前是无索引的，相关的sql查询就比较慢，那么在它之上加索引到底有无必要呢？首先，以此字段作为过滤条件的sql语句执行次数足够高，有一万多次，每次平均1.3s，很有优化的必要。其次，此字段对于记录的筛选效果足够好，关于此属性，可以根据不重复的round_code总数目在表记录中的占比大小来判定。故而尽管此字段属varchar(19),字长相对长，但是有做索引的必要，做索引之后查询效率可以得到大大提升。需要注意的是，变更非常频繁的字段上尽量避免做index，诸如upda

8、te_time或者update_uid，因为InnoDB默认的索引存储方式是b树，字段值变更之后相应索引 也要变更，进而引起整个b树结构的变化，是代价较高的操作。No dependent subquery！什么情况下会出现dependent subquery？独立子查询在上述的sql中出现了，而它为何需要规避？MySQL会根据第一条执行计划，以stock_round表作为依赖，将独立子查询涉及的查询语句执行527442次！子查询的特点就是，子查询中的第一个SELECT语句直接依赖于外层结果集，这种强耦合的关系，造成了不必要的多次查询，降低了查询性能。在MySQL5.6及以上版本中，子查询问题得

9、到了很大程度上优化，故此问题在这个版本以上不是那么重要，但是我们公司用的是5.5，所以这种执行计划已然必须规避。No dependent subquery！最推荐的规避方案，是使用联表方式替代独立子查询。如果遇到了联表无法表达独立子查询的语义的时候，至少也应该拆分查询并在java代码内拼装数据，而不能对独立子查询视而不见。执行计划中的extra列执行计划中的extra列中常见的枚举值是using index，using where，using temporary 以及using filesort。首先谈谈using index。这里的using index，并不是在说MySQL会对此执行计划走

10、索引，而是代表着MySQL可以走covering index，只读b树，甚至不去访问真实记录，查询效率很高！也就是说，我们如果可能的话，应该尽量让extra内出现此hint。下面看几个例子：e.g1为何能走using index？因为stock_type和id上都有索引，MySQL在检索b树的时候就能直接根据索引返回结果，无需再去访问真实数据记录。执行计划中的extra列e.g2这个例子用到了一点不是很容易想得到的小技巧。考虑这样一个语句：SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;我

11、们考虑created字段上已经有索引了，但是只要limit偏移量大些，查询效率依然高不起来。那么有没有什么办法优化呢？修改为以下格式：Select id,title,content from article where id in (select id from article order by created desc limit 10000,10);这样修改之后，子查询可以使用covering index，大幅提升查询效率。e.g3SELECT COUNT(*) FROM articles WHERE category_id = .只要在category_id上做了索引，此查询就可以走co

12、vering index，因为MySQL只需要在访问b树的时候对满足条件的记录进行计数，无需访问真实记录。执行计划中的extra列一般形式：SELECT a FROM . WHERE b = .如果遇到了这种形式的sql，我们至少应该考虑下是否要走covering index，即作出idx_b_a(b,a)这样的索引。如果a不止一个字段，那么需要考量，因为太长的联合索引维护代价很大。Using WhereUsing where的出现，表示MySQL在从存储引擎读取出记录之后，需要根据过滤条件进行后过滤（post-filter),using where和using index有点对立的意思，因为

13、一旦MySQL试用了covering index完成了查询 ，那么它不可能再去访问存储引擎，也就不可能出现using where。Using temporary以及Using filesort这两个hint的出现意味着你的sql有必要优化了，因为MySQL不得不产生临时表和做内存内或者硬盘内额外排序以满足需求。执行计划中的extra列Using temporary 很多时候是因为不恰当的group by计划引起的，下面谈一谈MySQL对于group by语句的优化。比较好的group by execution plan应该走index access，否则MySQL就只能创建临时表了，这正是我们

14、不希望的。走index access有两种方案：1.Loose index scan（松散索引扫描）触发条件： I：单表查询（多表查，且groupby子句内字段非全是驱动表有索引字段，或者不按照驱动表索引有序，都会产生Using temporary）。 II：最左前缀原则。考虑一个表有这样的索引：idx(c1,c2,c3),那么group by c1 , group by c1,c2 都可以触发，但是类似group by c2,c3则无法触发。III：集合函数仅允许包含min()以及max(),其中包含的列必须一致，且必须在联合索引内，或者含单字段索引。IV：包含varchar等变长字段类型等

15、，字段必须含全文索引，不支持字段上的左前缀索引。执行计划中的extra列如果loose index scan成功触发，你应该能在exrta内看到Using index for group-by。下面的语句均可以触发上述前提的LIS：在MySQL5.5中，增加了对以下函数的支持：AVG(DISTINCT), SUM(DISTINCT), and COUNT(DISTINCT) are supported. AVG(DISTINCT) and SUM(DISTINCT)take a single argument。Tight index scan：执行计划中的extra列在loose index

16、scan无法被触发条件下，MySQL也能有另外一条途径避免因group by引起临时表的创建，即tight index scan，如果where子句内含range condition，MySQL可以根据这个条件锁定一个范围的index ，从而避免临时表，如果没有，MySQL就做全表的index scan，也可以避免临时表。Using filesort：这个hint往往是对记录排序引起的。Using filesort意味着mysql需要对order by在内存甚至硬盘内做一次额外的排序，产生相应的io代价等，如果能够规避，需要规避。规避它的办法，还是依靠索引，下图所示的情况都可以让mysql索引

17、排序，而非额外排序：执行计划中的extra列如图示，可见除开order by中的列均能对应上相关索引以外，where字居中包含对索引列字段和常量之间的比较的时候，也是可以规避using Filesort的。然而在以下情况下，MySQL无法使用索引来解决order by，只能产生额外排序：执行计划中的extra列1.对不同索引列进行排序，如Select * from t1 order by key1, key2其中key1和key2都是索引列。2.Order by字句跟的不是索引或者索引的最左前缀。3.既使用到了asc也使用到了desc，这时MySQL无法走索引以完成order by。4.Whe

18、re字句内的过滤条件对应的索引和order by之后对应的索引不匹配。5.Order by字句包含了表达式，包括函数表达式等。6. 多表联查时，你想要order by的字段并非全是驱动表内字段，这种情况发生概率较高。7.Order by和group by字句不一致。 小结本分享覆盖了一部分我们在平时编码时需要注意的书写sql的规范，这些规范不仅仅停留在“不犯错误”阶段，而是为开发者指出了如何书写高性能sql的方向，望大家多多吸收，积极分享交流，共同进步。MySQL中的查询优化工作涉及方面很多，着手点也很多，包括sql本身的改造，服务器配置优化，全局变量优化，数据归档甚至结合java代码共同优化等，难以完全覆盖。此分享系抛砖引玉，望大家提出宝贵意见，共同改进sql质量，以提升自己在这方面的技能水平。T