非常干的sql书写规范建议超实用sql优化技巧

「干货」关于SQL书写建议&索引优化的总结，你值得拥有前言平时写sql写的比较多，一直没把优化相关的知识整理记录下来，本文章记录对SQL优化的一些技巧；我将结合demo（一个百万级数据表），去实践验证这些优化技巧。测试用例接下来，我们创建一个测试表并生成100w条测试数据，有助演示或验证接下来的知识--创建一个测试表CREATETABLE`users`(`id`int(11)NOTNULLAUTO_INCREMENT,`a`varchar(255)DEFAULTNULL,`b`varchar(255)DEFAULTNULL,`c`varchar(11)DEFAULTNULL,`d`int(2)DEFAULTNULL,PRIMARYKEY(`id`),KEY`index_name`(`a`,`b`,`c`)USINGBTREE,KEY`d`(`d`),KEY`b`(`b`))ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8;--------------------------------Recordsofusers------------------------------INSERTINTO`users`VALUES('1','a','b','c','1');INSERTINTO`users`VALUES('2','asd','785qwe','2','2');INSERTINTO`users`VALUES('3','wer','123','1','3');INSERTINTO`users`VALUES('4','左先生','123','1','4');INSERTINTO`users`VALUES('5','qwe1','wq12','2','5');INSERTINTO`users`VALUES('6','qwe','123','2',null);INSERTINTO`users`VALUES('7','1','1','1',null);INSERTINTO`users`VALUES('8','w','1','1',null);INSERTINTO`users`VALUES('9','aa','1',null,null);--创建生成随机数据的存储过程DROPPROCEDUREIFEXISTS`create_test_data`;DELIMITER;;CREATEPROCEDURE`create_test_data`(nINT)COMMENT'生成若干随机数据'BEGINDECLAREiINTDEFAULT1;WHILEi<nDOINSERTINTO`test`.`users`(`a`,`b`,`c`)VALUES(get_rand_str(10),get_rand_str(10),get_rand_str(10));SETi=i+1;ENDWHILE;END;;--返回随机字符串的函数DROPFUNCTIONIFEXISTS`get_rand_str`;DELIMITER;;CREATEFUNCTION`get_rand_str`(nINT)RETURNSvarchar(100)COMMENT'返回随机数'BEGINDECLAREchar_strvarchar(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';DECLAREreturn_strvarchar(255)DEFAULT'';DECLAREiINTDEFAULT0;WHILEi<nDOSETreturn_str=concat(return_str,substring(char_str,FLOOR(1+RAND()*62),1));SETi=i+1;ENDWHILE;RETURNreturn_str;END;;--生成100w条随机数据--预计花费半小时或更久，其实也可以生成1w条。主要是数据多一点，更能反映出索引的重要性callcreate_test_data(100*10000);ExplainExplain是确定一个SQL是否走索引最简单的办法，我们用此方法可以对SQL进行调优，本文章只需关注以下项目，关于Exolain的具体说明可查阅具体说明type（从最好到最差依次是const>eq_ref>ref>range>index>all）const表示通过索引一次就找到了，const用于比较primarykey或者unique索引eq_ref多表连接中使用primarykey或者uniquekey作为关联条件ref非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体range只检索给定范围的行，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好index遍历全表，ALL区别为index类型只遍历索引树(select索引列或orderby主键两种情况，但是where没用到索引)all遍历全表以找到匹配的行一般保证查询至少达到range级别，最好能达到ref。key本次查询最终用到哪个索引key_len索引使用的前缀长度或整个长度row扫描过的记录行数--测试一下,其中b字段有索引，c字段没有索引SELECT*fromuserswhereb='随便啦，测试而已';--花费0.001sSELECT*fromuserswherec='随便啦，测试而已';--花费0.306sSQL优化建议少用select*老生常谈，大家都懂。合理使用limit1如果知道查询结果只有一条或者只要一条记录，建议用limit1，当然，如果已存在唯一索引就没必要用。合理使用joinInnerjoin内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集leftjoin在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录rightjoin在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录都满足SQL需求的前提下，推荐优先使用Innerjoin（内连接），如果要使用leftjoin，左边表数据结果尽量小，如果有条件的尽量放到左边处理。批量插入数据数量不大的情况下，一条一条插入问题不大。如果数据量两，使用批量插入语句效率更高for(){INSERTINTO`test`.`users`(`a`,`b`,`c`)VALUES('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U')}更优：INSERTINTO`test`.`users`(`a`,`b`,`c`)VALUES--此处可自行拼接语句，如使用mybatis等('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U'),('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U')尽量用unionall替换union如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用unionall代替union，这样会提高效率。--执行时间0.06sSELECT*FROMusersLIMIT0,10000UNIONALLSELECT*FROMusersLIMIT10000,20000--执行时间0.2sSELECT*FROMusersLIMIT0,10000UNIONSELECT*FROMusersLIMIT10000,20000会使索引失效的几种情况where条件中没有匹配字段类型where中使用NOT、!=、IN("IN"Mysql5.6及以上支持索引)where中使用OR连接没有索引的字段where中使用in(mysql5.6及以上支持索引)like'%关键字%'where中对字段进行运算或使用函数使用复合索引但没有使用"引导列"我们知道测试表中b字段是有索引，c没有索引，接下来逐一测试一下where条件中没有匹配字段类型--b是字符串类型,where且写了整数，虽然可以正常执行sql，但是不会走索引EXPLAINSELECT*fromuserswhereb=1;**NOT、!=**--均会使索引失效EXPLAINSELECT*fromuserswherebnotin('a');EXPLAINSELECT*fromuserswherebisnotnull;EXPLAINSELECT*fromuserswhereb!='a'OR--用or连接没有索引的字段这种情况，假设它走了b的索引，但是走到c查询条件时，它还得全表扫描--也就是需要三步过程：全表扫描+索引扫描+合并。所以OR会导致索引失效--注意，测试表中c是没索引的，如果c也有索引，用or其实是OK的EXPLAINSELECT*fromuserswhereb='a'orc='a'--优化方式1.改用inSELECT*fromuserswherebin('b','bbb')2.UNION--对于or，我们可以这样优化我们的sql,虽然第二条没有走索引,但是第一条sql就走了索引啦SELECT*fromuserswhereb='b'UNIONSELECT*fromuserswherec='c'LIKE--%关键字%会让索引失效SELECT*fromuserswherealike'%abc%'--正例,"关键字%"是可以使用索引提高查询效率,类似前缀索引SELECT*fromuserswherealike'abc%'where中对字段进行运算或使用函数--均会使索引失效EXPLAINSELECT*fromuserswhereYEAR(ctime)='2020';EXPLAINSELECT*fromuserswhered+1=2;大于号与小于号--在mysql中大于号小于号是个神奇的东西，使用它有时候会走索引有时候不走，据说是和结果的数量有关的，当数量较少（网上查到是有一个比例）时时使用索引的--建议能用BETWEEN就不要用><--可以自行按时间筛选出不同的数量测试SELECTidfromuserswherectime>'2020-03-3019:45:30'使用复合索引但没有使用"引导列"--可知表中有复合索引idx_abc(a,b,c),还有一个idx_b索引，我们先把idx_b删除--以下sql没有用到"引导列"所以不会走idx_abc索引，"引导列"只指复合索引的第一个字段EXPLAINSELECT*fromuserswherec='c'andb='b';--正例只要出现a即可EXPLAINSELECT*fromuserswherea='a'andb='b';EXPLAINSELECT*fromuserswherea='a'andc='c';limit分页优化我们日常做分页需求时，一般会用limit实现--常用做法SELECT*fromusersLIMIT1000000,10当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。优化分页是需要跟业务结合，这里提供几种解决方案，没有最好只有最合适where加上时间筛选比如只获取最近一年的数据、只获取今年的数据wherecreatetime>'2020-01-01'放弃选页，即只有上一页下一页第一页直接查获得第一页max(id)，如123，一般是最后一条数据，然后查询带上索引，这样每次只要扫描10条数据whereid>123limit10限制页数如只允许获取前100页索引优化建立索引mysql中索引一共分为主键索引、唯一索引、普通索引、全文索引。常用的都是前三种，第一种跟随主键，无需手动创建，而第四种全文索引用于全文搜索。只有InnoDB和MyISAM存储引擎支持FULLTEXT索引和仅适用于CHAR，VARCHAR和TEXT列，一般比较少用，因为像大文本的检索都会采用一些全文检索框架如elasticsearch，而不是在数据库里检索。--单列索引CREATEINDEXindex_nameONusers(`name`);--多列索引CREATEINDEXindex_nameONusers(`name`,age);--唯一索引，单列索引CREATEUNIQUEINDEXindex_nameONusers(`name`);--唯一索引，多列索引CREATEUNIQUEINDEXindex_nameONusers(`name`,age);优化索引与字段选择性如下两个字段，邮箱、用户名这种选择性较高的字符串是比较适合做索引，而性别这种比较单一的字段，建索引效率并不会提高太多，但如果存在男极多女极少的情况下，也可以考虑建索引。另外如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，这种情况也属于选择性较低的字段，不适合做索引|email|age|username||asdasda@|男|小明||123basb1@163.com|女|小红|联合索引的顺序问题建立联合索引的时候往往也需要考虑索引的顺序，以email与age为例，选择性高的字段应该排在age前面，如email。--正确CREATEINDEXindex_nameONusers(email,age);--反例CREATEINDEXindex_nameONusers(age,email);联合索引能为前缀单列，复列提供帮助--联合索引idx_1(a,b,c)--有效wherea=?wherea=?andb=?wherea=?andc=?(mysql5.6及以上才支持)wherea=?andb=?andc=?