SQL 优化简明教程.docx

《SQL 优化简明教程.docx》由会员分享，可在线阅读，更多相关《SQL 优化简明教程.docx（14页珍藏版）》请在第一文库网上搜索。

1、SQL优化简明教程sql语句的执行顺序:1、LIMIT 语句分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般DBA想到的办法是在type, name, create_time字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。I|AND name = SlowLogI好吧，可能90%以上的DBA解决该问题就到此为止。但当LIMIT子句变成LIMIT1000000, 10时，程序员仍然会抱怨：我只取10条记录为什么还是慢？要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程

2、序员偷懒了。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下：IFROMoperation一.丁AXDcreate_time 2017-03-16 14:00:00ORDER BY create_time limit在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。2、隐式转换SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句:nysql explain extended SELECT * ，IIII)AND b. isverified 薪ny sq 1 show warn i ngs; Warn

3、ing 1739 | Cannot use ref access on index bpn due to ty其中字段bpn的定义为varchar(20), MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。3、关联更新、删除虽然MySQL5. 6引入了物化特性，但需要特别注意它Fl前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。比如下面UPDATE语句，MySQL实际执行的是循环/嵌套子查询(DEPENDENTSUBQUERY)

4、,其执行时间可想而知。Io. statusFROM operat；cn oAND o. status N(Dd执行计划:Impossible WHERE noticed after reading const tables重写为JOIN之后，子查询的选择模式从DEPENDENT SUBQUERY变成DERIVED,执行速度大大加快，从7秒降低到2毫秒。o. statusFROM operation oAND o. status NOT IN(, done,执行计划简化为:len refrows Extraible WHERE noticed after reading const tables

5、| 2 I DERIVED| o| ref | idx 2, idx 5| idx5 | 8| const | 1| Using where; Using filesort4、混合排序MySQL不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。I,ROM my_order 0I1mR JOI my appraise a ON a. orderid = o. idORDER BY a. i s_rep 1a. appraise_time DESC执行计划显示为全表扫描:+.id | selecttype | table | typepossibl ekes| keykey

6、_len | ref| rows| Extra1 | SIMPLE| aI ALL| idx orderid | NUL1 | SIMPLEj_oi_eq_ref1PRIMARY| PR|I MARY 122I a. orderid1 NULL由于is_reply只有0和1两种状态，我们按照下面的方法重写后，执行时间从L 58秒降低到2毫秒。FROM my_order oINNER J0而0 a. orderid = o. id，AND is_reply = 0ORDER BYFROM my_order oINNEROX a. orderid = o. id IORDERBY appraise

7、_time DESCLIMIT0, g0)5appraisetimeJMIT 20;5、EXISTS 语句MySQL对待EXISTS子句时.，仍然采用嵌套子查询的执行方式。如下面的SQL语句：ELECT *my_neighbor nLEFT JOI my_ne i ghbor_app1y sran. id = sra. neighbor idxxxAl) sra. user_idn. t()r):c slHlus -)message_info mWill-RI- n. id = m. neighbor idAM) m. inuser =xxx)Al) n. topic_type 5执行计划为:

8、r_id 123const1 Using where2 | DEPENDENT SUBQUERY | mrefidx message inf122constUsing index condition;Using where去掉exists更改为join,能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。KROM my_neighbor nINNER JOINON n. id = n neighbord，AND m. inuser = xxxLEFT JOIN my_neighbor_apply sraON n. id = sra. neighbord AD sra. user id =

9、xxxMIERE n. topic_status （ 4n. i（）p：（- i vp（.）新的执行计划:id select type table typepossible keyskekey len refrows | Extra1 | SIMPLEm| refmessage_info|122const1 Using index condition |1 | SIMPLE| n| eq_ref | PRIMARY | 122ighbor_id1 Using where6、条件下推外部查询条件不能够下推到复杂的视图或子查询的情况有：1、聚合子查询；2、含有LIMIT的子查询；3、UNION或U

10、NION ALL子查询；4、输出字段中的子查询；如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后:Count (*)GROUP BY target) t二川，target 二，rm-xxxx，id | select type | table| type possible keys keykey_len | ref I rows Extra4+|1 | PRIMARY| ref | | 514| idx_4| 519| NULL |20 | Using index卜+确定从语义上查询条件可以直接下推后，重写如下:FROM operationtarget = rm-xxxx执行计划变为:

11、+id | select_type | table | type | possible_keys | key | key_ler+1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const7、提前缩小范围*先上初始SQL语句:SELECT * |ON o. uid = u. uidLEFT JOIN my_productinJON o. pid = p. pidWHERE ( 0. display 丁丁丁AND ( o. ostaus = 1 ),).-1 iu(该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划

12、也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。id select_type table | typepossible_keys keykey_len | ref| rows | Extra1 I SIMPLEoALLNULL| NULL | NULL I NULLI 9091191 | SIMPLE| u| eq_ref | PRIMARYPRIMARY I _4I _o.uid_I1 I NULLSIMPLEpALLPRIMARYNULLNULLNULLUsing where; Using join buffer (Block Nested Loop)由于最后WHERE条件以

13、及排序均针对最左主表，因此可以先对my_ordor排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。ROM my_order o 1( 3.|AND ( o. ostaus = 1 )I), scd 1 H|ON o. uid = u. uid|ON o. pid = p. pidORDER BY )s e 111 i再检瓷执行计划：子查询物化后(soloct_typo=DERIVED)参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT子句后，实际执行时间变得很小。vhere; Using join buffer (Block Nested Loop

14、)2DERIVEDoindexNULL8、中间结果集下推再来看下面这个已经初步优化过的例子（左连接中的主表优先作用查询条件）:c. allocatedI -ANDcusmanagercode = 123456|ORDER BY saiecode limit 20) a JLEFT JOIN|SELECT resourcesid, sum(ifnull (allocation,0) * 12345) allocated|FROMmy_resources( i. r1：-：/.: ：,1: J .；.；那么该语句还存在其它问题吗？不难看出子查询C是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。其实对于子查询C,左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们