本文将结合实例demo阐述优化SQL的30条建议。大部分都是在实际开发中总结出来的。希望对大家有所帮助。图片来自Pexels1。查询SQL尽量不要用select*，而是选择特定的字段。反例：select*fromemployee;正例：selectid，namefromemployee；原因如下：只取必填字段，节省资源，减少网络开销。选择*查询时，很可能不会使用覆盖索引，导致查询回表。2、如果知道只有一条查询结果或者只有一条最大/最小记录，建议使用limit1。假设现在有一张employee表，要找一个叫jay的人：CREATETABLE`employee`(`id`int(11)NOTNULL，`name`varchar(255)DEFAULTNULL，`age`int(11)DEFAULTNULL，`date`datetimeDEFAULTNULL，`sex`int(1)DEFAULTNULL，PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8；反例：selectid,namefromemployeewherename='jay'正例：selectid,namefromemployeewherename='jay'limit1;原因如下：加上limit1后，只要找到对应的一条记录，就不会再继续向下扫描，效率会大大提高。当然，如果name是唯一索引，就没有必要加limit1，因为limit的存在主要是为了防止全表扫描，提高性能。差别不大。3.尽量避免在where子句中使用or来连接条件。创建一个新的用户表，其中有一个公共索引userId。表结构如下：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`int(11)NOTNULL,`age`int(11)NOTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userId`(`userId`))ENGINE=InnoDBDEFAULTCHARSET=utf8;假设现在需要查询userid年龄为1或18的用户，很容易有如下SQL。反例：select*fromuserwhereuserid=1orage=18正例：//useunionallselect*fromuserwhereuserid=1unionallselect*fromuserwhereage=18//或者单独写两条SQL：select*fromuserwhereuserid=1select*fromuserwhereage=18原因：使用or可能做索引无效，于是全表扫描。对于没有索引的or+age的情况，假设它使用的是userId索引，但是当它到达age查询条件时，仍然需要扫描全表，即需要一个三步过程：全表扫描+indexscan+merge，if是从全表扫描开始，一次扫描就完成了。MySQL有一个优化器。出于效率和成本的考虑，当遇到or条件时，索引可能会失败，这似乎是合理的。4.优化limit分页我们在做日常分页需求的时候，一般都是使用limit来实现，但是当offset特别大的时候，查询效率就变低了。反例：selectid,name,agefromemployeelimit10000,10正例：//方案一：返回上次查询的最大记录（偏移量）selectid,namefromemployeewhereid>10000limit10.//方案二：orderby+indexselectid,namefromemployeeorderbyidlimit10000,10//方案三：在业务允许的情况下限制页数：原因如下：当offset最大时，查询效率会比较低，因为MySQL不会跳过offset直接取后面的数据，而是先把offset+要取的item个数，然后丢弃之前offset的数据返回。如果采用优化方案1，则返回最后一条最大查询记录（偏移量），这样就可以跳过偏移量，大大提高了效率。方案二采用orderby+index，同样可以提高查询效率。对于方案三，建议最后跟业务商量是否需要检查分页。因为绝大多数用户是不会翻太多页的。5.优化你的like语句在日常开发中，如果使用模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。反例：selectuserId,namefromuserwhereuserIdlike'%123';正例：selectuserId,namefromuserwhereuserIdlike'123%';原因：把%放在前面，不会使用索引，如下图：把%放在关键字后面，仍然会使用索引，如下图：6.使用where条件限制数据被查询以避免返回冗余行。假设业务场景是这样的：查询用户是否是会员。我见过这样的旧实现代码。反例：ListuserIds=sqlMap.queryList("selectuserIdfromuserwhereisVip=1");booleanisVip=userIds.contains(userId);正例：LonguserId=sqlMap.queryObject("selectuserIdfromuserwhereuserId='userId'andisVip='1'")booleanisVip=userId!=null;原因：需要什么数据就查什么，避免返回不必要的数据，节省成本。7、尽量避免在索引列上使用MySQL的内置函数。业务需求：查询最近7天登录过的用户（假设loginTime有索引）。反例：selectuserId,loginTimefromloginuserwhereDate_ADD(loginTime,Interval7DAY)>=now();正例：explainselectuserId,loginTimefromloginuserwhereloginTime>=Date_ADD(NOW(),INTERVAL-7DAY);原因：在索引列上使用了MySQL的内置函数，索引失败：如果索引列没有添加内置函数，索引仍然会走：8.尽量避免对其中的字段进行表达式操作where子句，会导致系统放弃使用索引，进行全表扫描。示例：select*fromuserwhereage-1=10;正例：select*fromuserwhereage=11;原因：age虽然有索引，但是因为对它进行操作，索引直接丢失了。9.Innerjoin，leftjoin，rightjoin，先用Innerjoin。如果是leftjoin，左表的结果要尽可能的小。Innerjoin内连接，当连接和查询两个表时，只保留两个表中完全匹配的结果集。当左连接对两个表进行连接查询时，即使右表没有匹配的记录，也会返回左表中的所有行。rightjoin当对两个表进行连接查询时，即使左表中没有匹配的记录，也会返回右表中的所有行。在都满足SQL要求的前提下，推荐优先使用Innerjoin（内连接）。如果要使用leftjoin，左表的数据结果要尽量小，有条件的话尽量放在左边处理。反例：select*fromtab1t1leftjointab2t2ont1.size=t2.sizewhereet1.id>2;正例：select*from(select*fromtab1whereid>2)t1leftjointab2t2ont1.size=t2.size;原因如下：如果innerjoin是等价连接，可能返回的行数比较少，所以性能会相对好一些。同样，如果使用leftjoin，左表的数据结果要尽可能小，条件尽量放在左边，也就是说返回的行数可能会比较少。10、尽量避免在where子句中使用!=或<>运算符，否则引擎会放弃使用索引，进行全表扫描反例：selectage,namefromuserwhereage<>18;正例：//可以考虑分开两个sql写selectage,namefromuserwhereage<18;selectage,namefromuserwhereage>18;原因：使用!=和<>很可能使索引失效：11、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则表结构：（有一个联合索引idxuseridage,userIdfirst,agelater)CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`int(11)NOTNULL,`age`int(11)DEFAULTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userid_age`(`userId`,`age`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;反例：select*fromuserwhereage=10;正例：//满足最左匹配原则select*fromuserwhereuserid=10andage=10;//符合最左匹配原则select*fromuserwhereuserid=10;原因如下：当我们创建联合索引时，比如(k1,k2,k3)，相当于创建了(k1),(k1,k2),(k1,k2,k3)三个索引，这是最左匹配原则。联合索引不满足最左原则，索引一般会失效，但这也和MySQL优化器有关。12、优化查询，考虑对where和orderby涉及的列建立索引，尽量避免全表扫描。反例：select*fromuserwhereaddress='Shenzhen'orderbyage;正例：添加索引altertableuseraddindexidx_address_age(address,age)13。如果插入过多数据，考虑批量插入反例：for(Useru:list){INSERTintouser(name,age)values(#name#,#age#)}正例：//一次批量插入500条，insertintouser(name,age)values(#{item.name},#{item.age})原因：批处理插入性能好，节省时间。打个比方：如果你需要把10000块砖搬到楼顶，你有电梯，电梯一次可以放适量的砖（最多500块）。您可以选择一次运输一块砖，也可以一次运输500块砖。你认为哪个最耗时？14.适当时，使用覆盖索引。覆盖索引可以让你的SQL语句不需要返回表，只需要访问索引就可以得到所有需要的数据，大大提高了查询效率。反例：//像模糊查询，不使用索引select*fromuserwhereuseridlike'%123%'正例：//id为主键，则为普通索引，即覆盖索引。selectid,namefromuserwhereuseridlike'%123%';15.慎用distinct关键字distinct关键字一般用于过滤重复记录，返回不重复的记录。在查询一个字段或几个字段的情况下使用，给查询带来优化效果。但是，当字段较多时，会大大降低查询效率。反面例子：SELECTDISTINCT*fromuser;正例：selectDISTINCTnamefromuser；原因：带distinct的语句的CPU时间和占用时间比不带distinct的要高。因为在查询很多字段的时候，如果使用distinct，数据库引擎会比较数据，过滤掉重复的数据。但是，这个比较和过滤过程会占用系统资源和CPU时间。16.删除冗余重复索引反例：KEY`idx_userId`(`userId`)KEY`idx_userId_age`(`userId`,`age`)正例：//删除userId索引，因为组合索引(A,B)等价于Created(A)and(A,B)indexKEY`idx_userId_age`(`userId`,`age`)原因：需要维护重复的索引，优化器在优化查询时也需要一一考虑.会影响性能。17、如果数据量很大，优化你的修改/删除语句，避免同时修改或删除过多的数据，因为这会导致CPU占用率过高，影响其他人访问数据库。反例：//一次删除10万还是100万+？deletefromuserwhereid<100000;//或者使用单循环操作，效率低，时间长：如果一次删除的数据过多，可能会出现lockwaittimeoutexceed的错误，建议分批操作。18.在where子句中，考虑使用默认值而不是null反例：select*fromuserwhereageisnotnull;正例：//设置0为默认值select*fromuserwhereage>0;原因：不代表usingisnull或isnotnull就不会取索引，这跟MySQL版本和查询成本有关。如果MySQL优化器发现使用索引的成本比不使用索引的成本高，肯定会放弃索引。这些条件！=、>isnull和isnotnull通常被认为会使索引无效。其实是因为查询的成本普遍偏高。优化器自动丢弃索引。如果将空值替换为默认值，往往可以走遍索引，同时表达的意思也会比较明确。19.表连接数不要超过5个。连接的表越多，编译时间和开销就越大。将连接表分解为更小的执行更具可读性。如果您必须连接许多表才能获取数据，则意味着糟糕的设计。20、exist&in的合理使用假设A表代表某公司的员工表，B表代表部门表。查询所有部门的所有员工，很容易有如下SQL：select*fromAwheredeptIdin(selectdeptIdfromB);这相当于：先查询部门表BselectdeptIdfromB然后使用部门deptId查询A的员工select*fromAwhereA.deptId=B.deptId可以抽象成这样一个循环：List<>resultSet;for(inti=0;iresultSet;for(inti=0;i=Date_sub(now(),Interval1Y)正例：//分页查询select*fromLivingInfowherewatchId=useIdandwatchTime>=Date_sub(now(),Interval1Y)limitoffset,pageSize//如果是前端分页，可以先查询前两百条记录，因为一般用户不应该向下滚动太多页，select*fromLivingInfowherewatchId=useIdandwatchTime>=Date_sub(now(),Interval1Y)limit200;26、当在一条SQL语句中连接多个表时，请使用表的别名，并在每一列前加上别名，这样语义更清晰。反例：select*fromAinnerjoinBonA.deptId=B.deptId;正例：selectmemeber.name,deptment。deptNamefromAmemberinnerjoinBdeptmentonmember.deptId=deptment.deptId;27、尽量使用varchar/nvarchar而不是char/nchar反例：`deptName`char(100)DEFAULTNULLCOMMENT'部门名'正例：`deptName`varchar(100)DEFAULTNULLCOMMENT'部门名'reason如下：因为变长字段存储空间小，可以节省存储空间。其次，对于查询来说，在一个比较小的领域内搜索效率更高。28.为了提高groupby语句的效率，可以在语句执行前过滤掉不需要的记录反例：selectjob,avg(salary)fromemployeegroupbyjobhavingjob='president'orjob='managent'正例：selectjob,avg(salary)fromemployeewherejob='president'orjob='managent'groupbyjob;29.如果字段类型是字符串，where必须用引号括起来，否则索引失败反例：select*fromuserwhereuserid=123;正例：select*fromuserwhereuserid='123';原因：为什么第一条语句没有单引号就没有索引？这是因为没有单引号，是字符串和数字的比较，它们的类型不匹配。MySQL将进行隐式类型转换，将它们转换为浮点数并进行比较。30.使用explain来分析你的SQL计划。在日常开发和编写SQL的时候，尽量养成一个习惯。使用explain来分析你写的SQL，特别是有没有使用索引。explainselect*fromuserwhereuserid=10086orage=18;
