还剩9页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
「干货」关于SQL书写建议索引优化的总结,你值得拥有前言平时写sql写的比较多,一直没把优化相关的知识整理记录下来,本文章记录对SQL优化的一些技巧;我将结合demo一个百万级数据表,去实践验证这些优化技巧测试用例接下来,我们创建一个测试表并生成100w条测试数据,有助演示或验证接下来的知识-创建一个测试表CREATETABLEusers、id、int11NOTNULLAUTOJNCREMENTvarchar255DEFAULTNULLb、varchar255DEFAULTNULLvcxvarcharllDEFAULTNULL、d、int2DEFAULTNULLPRIMARYKEY、idKEY、index_name、USINGBTREEKEY、d、、dKEY、b、、b、ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8;—RecordsofusersINSERTINTOusers、VALUESTT;INSERTINTOusers、VALUES2asd785qwe22;INSERTINTOusers、VALUES3we“123,T3;INSERTINTOusers、VALUES4‘左先生」123T4;INSERTINTOusers、VALUES5qwel「wql2;25;INSERTINTOusers、VALUES6Jqwe「123;2null;INSERTINTOusers、VALUES7TTTnull;INSERTINTOusers、VALUES8‘w’,TTnull;INSERTINTOusers、VALUES9aTnullnull;联合索引能为前缀单列,复列提供帮助-联合索引idx_labc--有效wherea=wherea=andb=wherea=andc=mysqI
5.6及以上才支持wherea二?andb=andc=wherec=andb=anda=只要三者都出现,顺序打乱都没问题,mysql会自动给你排成上一句的顺序-无效whereb=andc=whereb=根据上面的规律,其实可以发现如果where里面如果没有a那么都不会走索引这里引入一个概念叫“引导列”,在联合索引中,排在第一位的就叫引导列,只有where条件中包含引导列,该查询才会走索引为了理解,其实当我们创建一个联合索引的时候如idxlidx2idx3相当于创建了idxlidxlidx2和idxlidx2idx3三个索引当然实际过程中不应该建3个索引,减少不要要的冗余索引覆盖扫描索引覆盖扫描是指根据字段A查询字段B建立索引idxab会比单一索引idx⑸效率更高,如现实场景中,系统经常会根据用户名查询用户密码进行登录操作针对此操作我们对用户名在前密码在后建立联合索会比只建立单一索引查询效率更好-根据用户名查询用户密码SELECTpwdfromuserswhereusername=a;--更优做法,查询时不需要回表查询pwd字段,减少了10开销idx_1usernamepwd;-一般的做法idx_lusername;避菟冗余的索引重复的索引需要维护,并且优化器在优化查询的时候也需要逐个地进行考虑这会影响性能的反例:idxaidxab正例组合索引AB相当于创建了A和AB索引idxab另外索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要-创建生成随机数据的存储过程DROPPROCEDUREIFEXISTSScreate_test_data\DELIMITER;;CREATEPROCEDUREcreate_test_datanINTCOMMEN「生成若干随机数据’BEGINDECLAREiINTDEFAULT1;WHILEinDOINSERTINTO、test.、users、、a、b、,、cVALUESget_rand_str10get_rand_str10get_rand_str10;SETi=i4-1;ENDWHILE;END;;--返回随机字符串的函数DROPFUNCTIONIFEXISTSKget_rand_stf;DELIMITER;;CREATEFUNCTIONKget_rand_stfnINTRETURNSvarcharlOOCOMMENT返回随机数’BEGINDECLAREchar_strvarcharlOODEFAULTabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789;DECLAREreturn_strvarchar255DEFAULTn;DECLAREiINTDEFAULT0;WHILEinDOSETreturn_str=concatreturn_strsubstringchar_strFLOOR1+RAND*621;SETi=i+l;ENDWHILE;RETURNreturn_str;END;;-生成100w条随机数据--预计花费半小时或更久,其实也可以生成lw条主要是数据多一点,更能反映出索引的重要性callcreate_test_data100*10000;ExplainExplain是确定一个SQL是否走索引最简单的办法,我们用此方法可以对SQL进行调优,本文章只需关注以下项目,关于Exolain的具体说明可查阅具体说明type从最好到最差依次是consteq_refrefrangeindexallconst表示通过索引一次就找到了,const用于比较primarykey或者unique索引eq_ref多表连接中使用primarykey或者uniquekey作为关联条件ref非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值得行,然而它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体range只检索给定范围的行,一般就是where语句中出现了betweenin等范围的查询这种范围扫描索引扫描比全表扫描要好index遍历全表,ALL区别为index类型只遍历索引树select索引列或orderby主键两种情况,但是where没用到索引all遍历全表以找到匹配的行一般保证查询至少达到range级别,最好能达到refokey本次查询最终用到哪个索引key_len索引使用的前缀长度或整个长度row扫描过的记录行数-测试一下,其中b字段有索引,c字段没有索引SELECT*fromuserswhereb=随便啦,测试而已;一花费
0.001sSELECT*fromuserswherec=随便啦,测试而已’;—花费
0.306s查询创建工具查诲筋器EXPLAIN结刖曲状态jj走了索引一select_typetableItypeLossible-keyskeykeyJenrefSIMPLEusersIreftdx^bidx_b768const查询创建工具查疸编市器1r.XP^xIMSE_二Ci•fromuserswherec=SQL优化建议少用select*老生常谈,大家都懂合理使用limit1如果知道查询结果只有一条或者只要一条记录,建议用limit1当然,如果已存在唯一索引就没必要用合理使用joinInnerjoin内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集leftjoin在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录rightjoin在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录都满足SQL需求的前提下,推荐优先使用Innerjoin内连接,如果要使用leftjoin左边表数据结果尽量小,如果有条件的尽量放到左边处理批量插入数据数量不大的情况下,一条一条插入问题不大如果数据量两,使用批量插入语句效率更高for{INSERTINTO、test、.、users、a、b、c、VALUEShLQK51GcL6f;1DXIzvIS3t;4LsQGKva6U更优INSERTINTO、test、.、users、、a、,、b、,、c、VALUES--此处可自行拼接语句,如使用mybatis等hLQK51GcL6UDXIzvISSt4LsQGKva6U‘hLQK51GcL6;lDXIzvIS3t;4LsQGKva6U‘尽量用unionall替换union如果使用union不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序如果已知检索结果没有重复记录,使用unionall代替union这样会提高效率--执行时间
0.06sSELECT*FROMusersLIMIT010000UNIONALLSELECT*FROMusersLIMIT1000020000-执行时间
0.2sSELECT*FROMusersLIMIT010000UNIONSELECT*FROMusersLIMIT1000020000会使索引失效的几种情况where条件中没有匹配字段类型where中使用NOT、!
二、IN(〃IN〃Mysql
5.6及以上支持索引)where中使用0R连接没有索引的字段where中使用in(niysql
5.6及以上支持索引)like%关键字对where中对字段进行运算或使用函数使用复合索引但没有使用〃引导列〃我们知道测试表中b字段是有索引,C没有索引,接下来逐一测试一下where条件中没有匹配字段类型-b是字符串类型where且写了整数,虽然可以正常执行sql但是不会走索引EXPLAINSELECT*fromuserswhereb=l;**NOT!=**-均会使索引失效EXPLAINSELECT*fromuserswherebnotina;EXPLAINSELECT*fromuserswherebisnotnull;EXPLAINSELECT*fromuserswhereb!二区OR-用or连接没有索引的字段这种情况,假设它走了b的索引,但是走到c查询条件时,它还得全表扫描-也就是需要三步过程全表扫描+索引扫描+合并所以OR会导致索引失效-注意,测试表中c是没索引的,如果c也有索引,用or其实是OK的EXPLAINSELECT*fromuserswhereb=aorc=a-优化方式.改用inSELECT*fromuserswherebinbbbb.UNION-对于or我们可以这样优化我们的sql虽然第二条没有走索弓I但是第一条sql就走了索引啦SELECT*fromuserswhereb=UNIONSELECT*fromuserswherec=cLIKE-%关键字%会让索引失效SELECT*fromuserswherealike%abc%-正例J关键字%”是可以使用索引提高查询效率类似前缀索引SELECT*fromuserswherealikeabc%where中对字段进行运算或使用函数-均会使索引失效EXPLAINSELECT*fromuserswhereYEARctime=2020;EXPLAINSELECT*fromuserswhered+l=2;大于号与小于号-在mysql中大于号小于号是个神奇的东西,使用它有时候会走索引有时候不走,据说是和结果的数量有关的,当数量较少网上查到是有一个比例时时使用索引的-建议能用BETWEEN就不要用可以自行按时间筛选出不同的数量测试SELECTidfromuserswherectimef2020-03-3019:45:30*使用复合索引但没有使用〃引导列〃可知表中有复合索引idx_abcabc还有一个idx_b索引,我们先把idx_b删除以下sql没有用至『引导歹「所以不会走idx_abc索引,”引导歹『只指复合索引的第一个字段EXPLAINSELECT*fromuserswherec=candb=b;-正例只要出现a即可EXPLAINSELECT*fromuserswhereandb=b;EXPLAINSELECT*fromuserswherea=aandc=c;limit分页优化我们日常做分页需求时,一般会用limit实现-常用做法SELECT*fromusersLIMIT100000010当偏移量最大的时候,查询效率就会越低,因为Mysql并非是跳过偏移量直接去取后面的数据,而是先把偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的优化分页是需要跟业务结合,这里提供几种解决方案,没有最好只有最合适where加上时间筛选比如只获取最近一年的数据、只获取今年的数据wherecreatetimeJ2020-01-01,放弃选页,即只有上一页下一页.第一页直接查.获得第一页maxid如123一般是最后一条数据,.然后查询带上索引,这样每次只要扫描10条数据whereid123limit10限制页数如只允许获取前100页索引优化建立索引mysql中索引一共分为主键索引、唯一索引、普通索引、全文索引常用的都是前三种,第一种跟随主键,无需手动创建,而第四种全文索引用于全文搜索只有IrmoDB和MylSAM存储引擎支持FULLTEXT索引和仅适用于CHARVARCHAR和TEXT歹U一般比较少用,因为像大文本的检索都会采用一些全文检索框架如elasticsearch而不是在数据库里检索―单列索引CREATEINDEXindex_nameONusers、name、;--多列索引CREATEINDEXindex_nameONusersnameage;-唯一索引,单列索引CREATEUNIQUEINDEXindex_nameONusersname、;—唯一索引,多列索引CREATEUNIQUEINDEXindex_nameONusersnameage;优化索引与字段选择性如下两个字段,邮箱、用户名这种选择性较高的字符串是比较适合做索引,而性别这种比较单一的字段,建索引效率并不会提高太多,但如果存在男极多女极少的情况下,也可以考虑建索引另外如果有一个CHAR255的列,如果在前10个或20个字符内,多数值是唯一的,这种情况也属于选择性较低的字段不适合做索引|email|age|username||asdasda@qq.com|男|小明I123basbl@
163.com|女|小红|联合索引的顺序问题建立联合索引的时候往往也需要考虑索引的顺序,以email与age为例,选择性高的字段应该排在age前面,如emailo—正确CREATEINDEXindex_nameONusersemailage;-反例CREATEINDEXindex_nameONusersageemail;。