《这些SQL优化技巧握在手面试可以横着走…….docx》由会员分享，可在线阅读，更多相关《这些SQL优化技巧握在手面试可以横着走…….docx（18页珍藏版）》请在课桌文档上搜索。
1、OISQL执行顺序选择表：FROMIefMable链接条件：ON链接:JOIN条件过滤：WHERE分组:GROUPBY聚合函数：AGGFUNC(columnorexpression),.分组过滤:HAVING选择字段：SELECT去重：DISTINCTcolumn,.排序：ORDERBY分页：LIMITcountOFFSETcount02基础SQL优化1、查询SQL尽量不要使用SeleCt*,而是具体字段D反例SELECT*FROMstudent2)正例SELECTiNAMEFROMstudent3）理由字段多时，大表能达到IOO多个字段甚至达200多个字段；只取需要的字段，节省资源、减少网络
2、开销；SeIeCt*进行查询时，很可能不会用到索引，就会造成全表扫描。2、避免在Where子句中使用Or来连接条件查询id为1或者薪水为3000的用户：D反例SELECT*FROMstudentWHEREid=lORsalary=300002）正例使用unionall:SELECT*FROMstudentWHEREid=lUNIONALLSELECT*FROMstudentWHEREsalary=30000分开两条SQL写：SELECT*FROMstudentWHEREid=lSELECT*FROMstudentWHEREsalary=300003）理由使用。r可能会使索引失效，从而全表扫描；
3、对于。r没有索引的SaIary这种情况，假设它走了id的索引，但是走到Salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到Or条件，索引还是可能失效的。3、使用VarChar代替Char1)反例deptnamechar(100)DEFAULTNULLCoMMENT部门名称2)正例deptnamevarchar(100)DEFAULTNULLCoMMENT，部门名称3)理由VarChar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；Char按声
4、明大小存储，不足补空格；其次对于查询来说，在一个相对较小的字段内搜索，效率更高。4、尽量使用数值替代字符串类型主键(id):Primarykey优先使用数值类型int,tinyint性别(SeX)：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint支付方式(payment)：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡服务状态(state)：1-开启、2-暂停、3-停止商品状态(state):1-上架、2-下架、3-删除5、查询尽量避免返回大量数据如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千
5、条甚至更多，用户也看不过来。通常采用分页，一页习惯10/20/50/100条。6、使用explain分析你SQL执行计划SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。EXPlain主要看SQL是否使用了索引。EXPLAINSELECT*FROMstudentWHEREid=l返回结果：JdjelcjypcableItypepoaible_kyekeyky_lenrfroveExtraIIlSIKPLtacudentIPRIMARYPRIKRY4contX7、是否使用了索引及其扫描类型type:ALL全表扫描，没有
6、优化，最慢的方式；index索引全扫描；range索引范围扫描，常用语,=1between等操作；ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中；eqef类似ref,区别在于使用的是唯一索引，使用主键的关联查询；const当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理；nullMySQL不访问任何表或索引，直接返回结果；System表只有一条记录（实际中基本不存在这个情况）。性能排行：Systemconsteq_refrefrangeindexALLpossible_keys：显示可能应用在这张表中的索引key真正使用的索引方式8、创建
7、name字段的索引提高查询速度的最简单最佳的方式。ALTERTABLEstudentADDINDEXindex_name(NAME)9、优化Iike语句模糊查询，程序员最喜欢的就是使用Iika但是Iike很可能让你的索引失效。1)反例EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,%1,EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,%1%,2)正例EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,1%,3)理由未使用索引，故意使用SeX非索引字段：EXPLAINSELE
8、CTid,NAMEFROMstudentWHERENAME=IORsex=lkey(WIi)key-lenref fbs Extra tPRIMARYPRIWkRY4cont1索引失效，type=ALL,全表扫描EXPLAINSELECTid,NAMEFROMstudentWHEREidLIKE%jeleCfyPeubl=NOW();2)正例EXPLAINSELECT*FROMstudentWHEREbirthday=DATE_ADD(NOW(),INTERVAL7DAY);3)理由使用索引列上内置函数索引失效：索引有效：2。XHJ,2 .”:-.r9txXteM22HOMMnirtwA2撼M
9、MnrcELTM,化1ReuMV2MeosesB3TbOaU，,，IMOT5XofY订0之lt/QjrrndtE*(IidMlectyptblrI1SXMXOtudeatfwidxXtMay4(WCHX)XCil&gxadexCoodltioa15、避免在Where中对字段进行表达式操作1)反例EXPLAINSELECT*FROMstudentWHEREid+l-l=+l2)正例EXPLAINSELECT*FROMstudentWHEREid=l-l+lEXPLAINSELECT*FROMstudentWHEREid=l3）理由SQL解析时，如果字段相关的是表达式就进行全表扫描。字段干净无表达
10、式，索引生效。IAV_19XFLAJ20stirHBttlVtodmflBM石1ResultV2MessagesiTableData*4InfoT5History3 年,。电E.4otJyrtultWTjdMlct.typetbl.cypepo”IbiJk*yajkyk*y-lnrfrowsExtrI1SIHPU9tuSent嬴stPRIMARYPRIKRY4CCMt1应尽量避免在where子句中使用!二或。操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。1）反例EXPLAINSELECT*FROMst
11、udentWHEREsalary!=3000EXPLAINSELECT*FROMstudentWHEREsalary3OOO2)理由使用!二和很可能会让索引失效。idelecttypetableuyppos9ibleavteykeykey-learefrowsExtraISIXPIXawdrnt谊(WnX)IWnX)O3）理由并不是说使用了isnull或者isnotnull就会不走索引了，这个跟mysql版本以及查询成本都有关；如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件!=,isnull,isnotnull经常被认为让索引失效，其实是因为一般情况下，查询的成本
12、高，优化器自动放弃索引的；如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点。03高级SQL优化1、批量插入性能提升大量数据提交，上千，上万，批量性能非常快，mysql独有。1)多条提交INSERTINTOstudent(id,NAME)VALUES(4,name);INSERTINTOstudent(id,NAME)VALUES(5,name2,);2)批量提交INSERTINTOstudent(id,NAME)VALUES(4,name1,),(5,name21);3)理由默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启
13、和提交。自然速度飞升；数据量小体现不出来。2、批量删除优化避免同时修改或删除过多数据，因为会造成CPU利用率过高，会造成锁表操作，从而影响别人对数据库的访问。D反例# 一次删除10万或者100万+?deletefromstudentwhereid100000;# 采用单一循环操作，效率低，时间漫长for(Useruser:list)(deletefromstudent;)2)正例# 分批进行删除，如每次500for()deletestudentwhereid=500andid1000;3)理由一次性删除太多数据，可能造成锁表，会有IoCkWaittimeOUteXCeed的错误，所以建议分批操
14、作。3、伪删除设计1)商品状态(state)架架除下2)理由这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查；同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用；通过whereState=I或者wherestate=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用；操作速度快，特别数据量很大情况下。4、提高groupby语句的效率可以在执行到该语句前，把不需要的记录过滤掉。1)反例：先分组，再过滤selectjob,avg(salary)fromemployeegroupbyj
15、obhavingjob=,president,orjob=managent,;2)正例：先过滤，后分组selectjob,avg(salary)fromemployeewherejob=president,orjob=,managent,groupbyjob;5、复合索引最左特性创建复合索引，也就是多个字段。ALTERTABLEstudentADDINDEXidx_name_salary(NAME,salary)满足复合索引的左侧顺序，哪怕只是部分，复合索引生效。EXPLAINSELECT*FROMstudentWHERENAME=,name没有出现左边的字段，则不满足最左特性，索引失效。EX
16、PLAINSELECT*FROMstudentWHEREsalary=3OOO复合索引全使用，按左侧顺序出现name,salary,索引生效。XPLAINSELECT*FROMstudentWHERENAME=陈子枢ANDsalary=3OOO虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化。EXPLAINSELECT*FROMstudentWHEREsalary=3OOOANDNAME=,name1)理由复合索引也称为联合索引；当我们创建一个联合索引的时候，如(kl,k2,k3),相当于创建了(kl)、(kl,k2)和(kl,k2,k3)三个索引，这就是最左匹配原则；
17、联合索引不满足最左原则，索引一般会失效，但是这个还跟MySqI优化器有关的。6、排序字段创建索引什么样的字段才需要创建索引呢?原则就是Where和Orderby中常出现的字段就创建索引。#使用*,包含了未索引的字段，导致索引失效EXPLAINSELECT*FROMstudentORDERBYNAME;EXPLAINSELECT*FROMstudentORDERBYNAME,salary#name字段有索引EXPLAINSELECTid,NAMEFROMstudentORDERBYNAME#name和salary复合索引EXPLAINSELECTid,NAMEFROMstudentORDERBY
18、NAME,salaryEXPLAINSELECTid,NAMEFROMstudentORDERBYsalary,NAME# 排序字段未创建索引，性能就慢EXPLAINSELECTid,NAMEFROMstudentORDERBYsex7、删除冗余和重复的索引SHOWINDEXFROMstudent# 创建索引indexjnameALTERTABLEstudentADDINDEXindex_name(NAME)# 删除student表的index_name索引DROPINDEXindex_nameONstudent;# 修改表结果，删除student表的index_name索引ALTERTABL
19、EstudentDROPINDEXindex_name;# 主键会自动创建索引，删除主键索引ALTERTABLEstudentDROPPRIMARYKEY;.QueryfQuf6uUrr,Sm4D*QnUMABTg.12SHCVmcxnz.stdeatfty0W。Wgmy)名幻9lBsindexSlgfi.3 ColIatloe Cardinality SubAXt Fackt4 WUU XMeM-ype 2 133 MX) (WU) BTUZBTRttBm1KML1 birtMSWIX) (WU) YtS S MX) OKjU4 YU YtS2MUry$ YBS BTMX8、不要有超过5个以
20、上的表连接关联的表个数越多，编译的时间和开销也就越大；每次关联内存中都生成一个临时表；应该把连接表拆开成较小的几个执行，可读性更高；如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了；阿里规范中，建议多表联查三张表以下。9、innerjoin、Ieftjoinsrightjoin,优先使用innerjoin三种连接如果结果相同，优先使用innerjoin,如果使用IeftjOin左边表尽量小。innerjoin内连接，只保留两张表中完全匹配的结果集；IeftjOin会返回左表所有的行，即使在右表中没有匹配的记录；rightjoin会返回右表所有的行，即使在左表中没有匹配的记录。1
21、）理由如果Enerjoin是等值连接，返回的行数比较少，所以性能相对会好一点；同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。10、in子查询的优化日常开发实现业务需求可以有两种方式实现：使用数据库SQL脚本实现使用程序实现如需求：查询所有部门的所有员工：#in子查询SELECT*FROMtb_userWHEREdept_idIN(SELECTidFROMlb_dept);# 这样写等价于：# 先查询部门表SELECTidFROMtb_dept# 再由部门dept_i
22、d,查询tb_user的员工SELECT*FROMtb_useru,tb.deptdWHEREu.deptjd=d.id假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：ListresultSet;for(inti=O;iB.length;i+)(fr(intj=O;jA.length;j+)if(Ai.id=Bj.id)resultSet.add(Ai);break;)上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。1)理由数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，
23、这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿。11、尽量使用unionall替代unionD反例SELECT*FROMstudentUNIONSELECT*FROMstudent2）正例SELECT*FROMstudentUNIONALLSELECT*FROMstudent3）理由UniOn和UniOnalI的区别是，UniOn会自动去掉多个结果集合中的重复结果，而UnionaIl则将所有的结果全部显示出来，不管是不是重复；union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNloN。
