今天给大家分享几个SQL常见的坏毛病及优化技巧。 SQL语句的执行顺序： 1、LIMIT语句 分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般DBA想到的办法是在type，name，createtime字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。 SELECT FROMoperation WHEREtypeSQLStats ANDnameSlowLog ORDERBYcreatetime LIMIT1000，10； 好吧，可能90以上的DBA解决该问题就到此为止。但当LIMIT子句变成LIMIT1000000，10时，程序员仍然会抱怨：我只取10条记录为什么还是慢？ 要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。 在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下： SELECT FROMoperation WHEREtypeSQLStats ANDnameSlowLog ANDcreatetime2017031614：00：00 ORDERBYcreatetimelimit10； 在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。 2、隐式转换 SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句： mysqlexplainextendedSELECT FROMmybalanceb WHEREb。bpn14000000123 ANDb。isverifiedISNULL； Warning1739Cannotuserefaccessonindexbpnduetotypeorcollationconversiononfieldbpn 其中字段bpn的定义为varchar（20），MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。 上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。 3、关联更新、删除 虽然MySQL5。6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。 比如下面UPDATE语句，MySQL实际执行的是循环嵌套子查询（DEPENDENTSUBQUERY），其执行时间可想而知。 UPDATEoperationo SETstatusapplying WHEREo。idIN（SELECTid FROM（SELECTo。id， o。status FROMoperationo WHEREo。group123 ANDo。statusNOTIN（done） ORDERBYo。parent， o。id LIMIT1）t）； 执行计划： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1PRIMARYoindexPRIMARY824UUsingtemporary 2DEPENDENTSUBQUERYImpossibleWHEREnoticedafterreadingconsttables 3DERIVEDorefidx2，idx5idx58const1UUsingfilesort 重写为JOIN之后，子查询的选择模式从DEPENDENTSUBQUERY变成DERIVED，执行速度大大加快，从7秒降低到2毫秒。 UPDATEoperationo JOIN（SELECTo。id， o。status FROMoperationo WHEREo。group123 ANDo。statusNOTIN（done） ORDERBYo。parent， o。id LIMIT1）t ONo。idt。id SETstatusapplying 执行计划简化为： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1PRIMARYImpossibleWHEREnoticedafterreadingconsttables 2DERIVEDorefidx2，idx5idx58const1UUsingfilesort 4、混合排序 MySQL不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。 SELECT FROMmyordero INNERJOINmyappraiseaONa。orderido。id ORDERBYa。isreplyASC， a。appraisetimeDESC LIMIT0，20 执行计划显示为全表扫描： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1SIMPLEaALLidxorderidNULLNULLNULL1967647Usingfilesort 1SIMPLEoeqrefPRIMARYPRIMARY122a。orderid1NULL 由于isreply只有0和1两种状态，我们按照下面的方法重写后，执行时间从1。58秒降低到2毫秒。SELECT FROM（（SELECT FROMmyordero INNERJOINmyappraisea ONa。orderido。id ANDisreply0 ORDERBYappraisetimeDESC LIMIT0，20） UNIONALL （SELECT FROMmyordero INNERJOINmyappraisea ONa。orderido。id ANDisreply1 ORDERBYappraisetimeDESC LIMIT0，20））t ORDERBYisreplyASC， appraisetimeDESC LIMIT20； 5、EXISTS语句 MySQL对待EXISTS子句时，仍然采用嵌套子查询的执行方式。如下面的SQL语句： SELECT FROMmyneighborn LEFTJOINmyneighborapplysra ONn。idsra。neighborid ANDsra。useridxxx WHEREn。topicstatus4 ANDEXISTS（SELECT1 FROMmessageinfom WHEREn。idm。neighborid ANDm。inuserxxx） ANDn。topictype5 执行计划为： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1PRIMARYnALLNULLNULLNULL1086041Usingwhere 1PRIMARYsrarefidxuserid123const1Usingwhere 2DEPENDENTSUBQUERYmrefidxmessageinfo122const1UUsingwhere 去掉exists更改为join，能够避免嵌套子查询，将执行时间从1。93秒降低为1毫秒。 SELECT FROMmyneighborn INNERJOINmessageinfom ONn。idm。neighborid ANDm。inuserxxx LEFTJOINmyneighborapplysra ONn。idsra。neighborid ANDsra。useridxxx WHEREn。topicstatus4 ANDn。topictype5 新的执行计划： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1SIMPLEmrefidxmessageinfo122const1Usingindexcondition 1SIMPLEneqrefPRIMARY122ighborid1Usingwhere 1SIMPLEsrarefidxuserid123const1Usingwhere 6、条件下推 外部查询条件不能够下推到复杂的视图或子查询的情况有：1、聚合子查询；2、含有LIMIT的子查询；3、UNION或UNIONALL子查询；4、输出字段中的子查询； 如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后： SELECT FROM（SELECTtarget， Count（） FROMoperation GROUPBYtarget）t WHEREtargetrmxxxx idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1PRIMARYref514const2Usingwhere 2DERIVEDoperationindexidx4idx4519NULL20Usingindex 确定从语义上查询条件可以直接下推后，重写如下： SELECTtarget， Count（） FROMoperation WHEREtargetrmxxxx GROUPBYtarget 执行计划变为： idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1SIMPLEoperationrefidx4idx4514const1UUsingindex 7、提前缩小范围 先上初始SQL语句： SELECT FROMmyordero LEFTJOINmyuserinfou ONo。uidu。uid LEFTJOINmyproductinfop ONo。pidp。pid WHERE（o。display0） AND（o。ostaus1） ORDERBYo。selltimeDESC LIMIT0，15 该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。 idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1SIMPLEoALLNULLNULLNULLNULL909119UUUsingfilesort 1SIMPLEueqrefPRIMARYPRIMARY4o。uid1NULL 1SIMPLEpALLPRIMARYNULLNULLNULL6UUsingjoinbuffer（BlockNestedLoop） 由于最后WHERE条件以及排序均针对最左主表，因此可以先对myorder排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。 SELECT FROM（ SELECT FROMmyordero WHERE（o。display0） AND（o。ostaus1） ORDERBYo。selltimeDESC LIMIT0，15 ）o LEFTJOINmyuserinfou ONo。uidu。uid LEFTJOINmyproductinfop ONo。pidp。pid ORDERBYo。selltimeDESC limit0，15 再检查执行计划：子查询物化后（selecttypeDERIVED）参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT子句后，实际执行时间变得很小。 idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra 1PRIMARYALLNULLNULLNULLNULL15UUsingfilesort 1PRIMARYueqrefPRIMARYPRIMARY4o。uid1NULL 1PRIMARYpALLPRIMARYNULLNULLNULL6UUsingjoinbuffer（BlockNestedLoop） 2DERIVEDoindexNULLidx15NULL909112Usingwhere 8、中间结果集下推 再来看下面这个已经初步优化过的例子（左连接中的主表优先作用查询条件）： SELECTa。， c。allocated FROM（ SELECTresourceid FROMmydistributed WHEREisdelete0 ANDcusmanagercode1234567 ORDERBYsalecodelimit20）a LEFTJOIN （ SELECTresourcesid，sum（ifnull（allocation，0）12345）allocated FROMmyresources GROUPBYresourcesid）c ONa。resourceidc。resourcesid 那么该语句还存在其它问题吗？不难看出子查询c是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。 其实对于子查询c，左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。 SELECTa。， c。allocated FROM（ SELECTresourceid FROMmydistributed WHEREisdelete0 ANDcusmanagercode1234567 ORDERBYsalecodelimit20）a LEFTJOIN （ SELECTresourcesid，sum（ifnull（allocation，0）12345）allocated FROMmyresourcesr， （ SELECTresourceid FROMmydistributed WHEREisdelete0 ANDcusmanagercode1234567 ORDERBYsalecodelimit20）a WHEREr。resourcesida。resourcesid GROUPBYresourcesid）c ONa。resourceidc。resourcesid 但是子查询a在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用WITH语句再次重写：WITHaAS （ SELECTresourceid FROMmydistributed WHEREisdelete0 ANDcusmanagercode1234567 ORDERBYsalecodelimit20） SELECTa。， c。allocated FROMa LEFTJOIN （ SELECTresourcesid，sum（ifnull（allocation，0）12345）allocated FROMmyresourcesr， a WHEREr。resourcesida。resourcesid GROUPBYresourcesid）c ONa。resourceidc。resourcesid 总结 数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。 上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。 程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。 编写复杂SQL语句要养成使用WITH语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担。
哥谭骑士D加密现已移除但还是有优化问题2023世界25处最佳旅行目的地榜单公布，中国龙门石窟榜上有玩转国家植物园白鸽挂满珙桐树，本周在北京就能看到植物界大熊猫统一企业上半年净利润下滑加速，食品板块亏损1。58亿元好儿媳徐梦迪大婚被闫学晶表白，与婆婆情同母女莫兰德透露将返回辽篮新赛季再争总冠军盼郭艾伦能够留下患上不死癌症的李宇春，严重时要靠轮椅度日，自嘲挣得钱够看病了世联赛最佳阵容出炉，中国女排双核入围，蔡斌安排丁霞替补真不冤美媒有趣的事实库里是75大里唯一晋级分区决赛的球星三星GalaxyZFold4将有大惊喜，GalaxyZFol7月12日国内汽柴油价格有望迎两连降英超BIG6阵容大变样！曼城一枝独秀，五强力拼欧冠入场券浪潮信息为中国航天探索事业出力高性能计算技术领衔珍惜生命的名言集锦五百年前的缘分二年级语文说课稿学校管理方面的建议清明节可以提前扫墓吗清明节祭奠先人越早越好老公爱爱持久力差怎么破钢材合同秋雨叹其二宏字起名男孩男孩带宏字起名大全如果人类没有手七言诗春月景色