这些SQL优化技巧握在手面试可以横着走…….docx
OISQL执行顺序选择表：FROMIefMable链接条件：ON<join_condition>链接:<join_type>JOIN<right_table>条件过滤：WHERE<where_condition>分组:GROUPBY<group_byjist>聚合函数：AGGFUNC(columnorexpression),.分组过滤:HAVING<having_condition>选择字段：SELECT去重：DISTINCTcolumn,.排序：ORDERBY<order_byjist>分页：LIMITcountOFFSETcount02基础SQL优化1、查询SQL尽量不要使用SeleCt*,而是具体字段D反例SELECT*FROMstudent2)正例SELECTiNAMEFROMstudent3）理由字段多时，大表能达到IOO多个字段甚至达200多个字段；只取需要的字段，节省资源、减少网络开销；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可能会使索引失效，从而全表扫描；对于。r没有索引的SaIary这种情况，假设它走了id的索引，但是走到Salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到Or条件，索引还是可能失效的。3、使用VarChar代替Char1)反例'deptname'char(100)DEFAULTNULLCoMMENT'部门名称2)正例'deptname'varchar(100)DEFAULTNULLCoMMENT，部门名称3)理由VarChar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；Char按声明大小存储，不足补空格；其次对于查询来说，在一个相对较小的字段内搜索，效率更高。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、查询尽量避免返回大量数据如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。通常采用分页，一页习惯10/20/50/100条。6、使用explain分析你SQL执行计划SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。EXPlain主要看SQL是否使用了索引。EXPLAINSELECT*FROMstudentWHEREid=l返回结果：JdjelcjypcableI'typepoa«ible_k«yekeyk«y_lenr«froveExtraIIlSIKPLtacudentIPRIMARYPRIKRY4con>tX7、是否使用了索引及其扫描类型type:ALL全表扫描，没有优化，最慢的方式；index索引全扫描；range索引范围扫描，常用语<,<=,>=1between等操作；ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中；eqef类似ref,区别在于使用的是唯一索引，使用主键的关联查询；const当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理；nullMySQL不访问任何表或索引，直接返回结果；System表只有一条记录（实际中基本不存在这个情况）。性能排行：System>const>eq_ref>ref>range>index>ALLpossible_keys：显示可能应用在这张表中的索引key真正使用的索引方式8、创建name字段的索引提高查询速度的最简单最佳的方式。ALTERTABLEstudentADDINDEXindex_name(NAME)9、优化Iike语句模糊查询，程序员最喜欢的就是使用Iika但是Iike很可能让你的索引失效。1)反例EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,%1,EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,%1%,2)正例EXPLAINSELECTid,NAMEFROMstudentWHERENAMELIKE,1%,3)理由未使用索引，故意使用SeX非索引字段：EXPLAINSELECTid,NAMEFROMstudentWHERENAME=IORsex=lkey(WIi)key-lenref fbs Extra <MX) (NUU)3 QtiDg where,dselect-typeII"SDCPLg主键索引生效EXPLAINSELECTid,NAMEFROMstudentWHEREid=lIdelc-typeIpoaalble-lceykeykey-lenrefrwExt"11SIKPUstudentco>tPRIMARYPRIWkRY4con>t1索引失效，type=ALL,全表扫描EXPLAINSELECTid,NAMEFROMstudentWHEREidLIKE'%jeleCfyPeubl<te),lesrefJrowEknIIl5IXFLt9twi9ntILLI(WnX)(IroU4(KOTX)OroLL)3Ulngwb«r«10、字符串怪现象1）反例#未使用索引EXPLAINSELECT*FROMstudentWHERENAME=1232)正例#使用索引EXPLAINSELECT*FROMstudentWHERENAME=,123,3）理由为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较。11、索引不宜太多，一般5个以内索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要；insert或叩date时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要。12、索引不适合建在有大量重复数据的字段上如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，MySql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。13、Where限定查询的数据数据中假定就一个男的记录。D反例SELECTid,NAMEFROMstudentWHEREsex='男'2）正例SELECTid,NAMEFROMstudentWHEREid=lANDsex='男'3）理由需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。14、避免在索引列上使用内置函数业务需求：查询最近七天内新生儿（用学生表替代下）给birthday字段创建索引：ALTERTABLEstudentADDINDEXidx_birthday(birthday)当前时间加7天：SELECTNOW()SELECTDATE.ADD(NOW(),INTERVAL7DAY)D反例EXPLAINSELECT*FROMstudentWHEREDATE_ADD(birthday,INTERVAL7DAY)>=NOW();2)正例EXPLAINSELECT*FROMstudentWHEREbirthday>=DATE_ADD(NOW(),INTERVAL7DAY);3)理由使用索引列上内置函数索引失效：索引有效：2。XHJ,2 .”:-.r9txXteM22HOMMnirtwA2撼MMn½>rcELTM,化1ReuMV2MeosesB3TbOaU，,，IMOT5XofY订0之lt/QjrrndtE"*(IidMlectyp<t«bl<typpoMrtkeykeyIenrtfrow»>rI1SXMXOtudeatfwidx>XtMay4(WCHX)XCil&gxadexCoodltioa15、避免在Where中对字段进行表达式操作1)反例EXPLAINSELECT*FROMstudentWHEREid+l-l=+l2)正例EXPLAINSELECT*FROMstudentWHEREid=÷l-l+lEXPLAINSELECT*FROMstudentWHEREid=l3）理由SQL解析时，如果字段相关的是表达式就进行全表扫描。字段干净无表达式，索引生效。IAV_19XFLAJ20stirHBttlVtodmflBM石1ResultV2MessagesiTableData*4InfoT5History3 年,。电E.4otJyrtultWTjdMlct.typetbl.cypepo”IbiJk*yajkyk*y-lnr«frowsExtrI1SIHPU9tu<Sent嬴stPRIMARYPRIKRY4CCMt1应尽量避免在where子句中使用!二或。操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。1）反例EXPLAINSELECT*FROMstudentWHEREsalary!=3000EXPLAINSELECT*FROMstudentWHEREsalary<>3OOO2)理由使用!二和<>很可能会让索引失效。idelecttypetableuyppos9ibleavteykeykey-learefrowsExtraISIXPIXawdrnt谊(WnX)IWnX)<NUU.)(NULL)3UtlDgwtwr«17、
