今天给大家分享几个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； mysqlshowwarnings； 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 1PRIMARYoindexPRIMARY824Usingwhere；Usingtemporary 2DEPENDENTSUBQUERYImpossibleWHEREnoticedafterreadingconsttables 3DERIVEDorefidx2，idx5idx58const1Usingwhere；Usingfilesort 重写为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，idx5idx58const1Usingwhere；Usingfilesort 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 2DEPENDENTSUBQUERYmrefidxmessageinfo122const1Usingindexcondition；Usingwhere 去掉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 1SIMPLEoperationrefidx4idx4514const1Usingwhere；Usingindex 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 1SIMPLEoALLNULLNULLNULLNULL909119Usingwhere；Usingtemporary；Usingfilesort 1SIMPLEueqrefPRIMARYPRIMARY4o。uid1NULL 1SIMPLEpALLPRIMARYNULLNULLNULL6Usingwhere；Usingjoinbuffer（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 1PRIMARYALLNULLNULLNULLNULL15Usingtemporary；Usingfilesort 1PRIMARYueqrefPRIMARYPRIMARY4o。uid1NULL 1PRIMARYpALLPRIMARYNULLNULLNULL6Usingwhere；Usingjoinbuffer（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语句也能减小数据库的负担。
大爆冷！性感美女32掀翻国乒夺冠热门，陈幸同横扫华裔老将WTT澳门乒乓球冠军赛国乒的东京奥运会双料冠军陈梦意外23（1189118111171113）不敌罗马尼亚的性感美女斯佐科斯，罕见无缘女单的8强。另外一场18决赛中，世乒赛冠军成员陶汉林哑火开局山东队受限，王晗反复试阵，终于找到破解密钥在一场限制与反限制的比赛中，王晗技高一筹，率队击败来势汹汹的新疆队。比赛一波三折，两队教练之间的斗智，球员之间斗狠，给球迷上演了一场跌宕起伏的大戏。陶汉林哑火开局山东高速受限王晗反西汉姆联VS伯恩茅斯新援仍需磨合，西汉姆联主场盼望三分西汉姆联VS伯恩茅斯新援仍需磨合，西汉姆联主场盼望三分阵容实力有差距，弱旅伯恩茅斯反击犀利，西汉姆联主场不能大意北京时间10月25日0300，西汉姆联将会坐镇主场迎来与伯恩茅斯的英国王杯首轮对阵出炉皇萨瓦伦西亚及贝蒂斯不用出战前两轮直播吧10月24日讯西班牙国王杯首轮比赛对阵情况已经出炉，皇马巴萨贝蒂斯和瓦伦西亚因参加西班牙超级杯的比赛不用参加前两轮。以下是具体对阵情况CDLAlcoraVS埃尔切Velard苏醒相信这次世界杯，会给梅西和所有爱阿根廷的人很圆满的结局直播吧10月24日讯在接受采访时，歌手苏醒谈到了自己的偶像梅西，他表示，相信卡塔尔世界杯会给梅西和所有支持阿根廷的人非常圆满的结局。苏醒说道我是非常狂热的梅西球迷，这是他的最后一届英超最新榜单看完了本轮精彩的英超，让我们来看看最新的英超积分榜射手榜助攻榜！积分榜本轮英超踢完之后，英超传统big6中，除了曼联与切尔西内战，只有曼城赢球，阿森纳利物浦热刺集体拉胯无法赢球，特2022年10月23日A股周末重要消息和明天操作要点上周五，美股三大指数集体收涨，道指涨2。47，纳指涨2。31，标普涨2。37。一消息面1沪深交易所近期有关于交易所对券商基金等市场机构进行指导的报道不属实。上交所始终遵循建制度不干2022世界杯十大球星巡礼之德布劳内，逆境中崛起的英雄足球世界杯德布劳内足球助力团如果有人问你，当今足坛的第一中场是谁？我想很多人会不假思索的就说出德布劳内的名字。确实，德布劳内太全面了，无论是进攻还是防守，无论是长传还是定位球，出色由于对加密货币的敌意，日本正在失去其作为世界游戏之都的地位从监管到税收，日本一直对加密货币游戏怀有敌意，这种立场正在威胁该国作为全球游戏领导者的地位。对加密货币等新兴Web3技术的明显敌意冒着使日本失去作为世界游戏之都的地位的风险。我们正全职妈妈也要有点追求每个人在自己还是个孩子的时候，都有一个大胆而美好的梦想，几乎所有的小学生也都写过类似的作文我的梦想，我长大了要做什么可是随着年龄的增大，长大的我们渐渐现实起来，再也不提那曾经信誓旦古代没有验孕棒，女性是如何验孕的？女生怀孕生子是一种生理现象也是必不可少的，很多女生表示现在已经比古代幸运多了，因为那个时候一个家庭中的孩子是数不胜数的，很多女人，一辈子只是为了生孩子而活着是到了现代大多数女生都有