本文转载自公众号《核心阅读》（ID：AI_Discovery）。我们致力于使查询运行良好并持续很长时间。本文将给出一些优化SQL语句的建议，希望对你有所帮助。1、尽量不要用select*查询SQL，而是选择一个专用的字段。反面例子：select*fromemployee;正例：selectid，namefromemployee；原因：只使用必要的字段进行查询，可以节省资源，减少网络开销。这样做可能不会使用覆盖索引，并且会导致查询返回表。2、如果已知查询结果只有一个，建议使用limit1。假设有一个员工表单，你想在里面找一个叫jay的员工。CREATETABLEemployee(idint(11)NOTNULL,namevarchar(255)DEFAULTNULL,ageint(11)DEFAULTNULL,datedatetimeDEFAULTNULL,sexint(1)DEFAULTNULL,PRIMARYKEY(`id`));反例：selectid,namefromemployeewherename='jay';正例：selectid,namefromemployeewherename='jay'limit1;原因：加上limit1后，当找到对应的记录时，就不会再继续查找，效率会大大提高。3.尽量避免在where子句中使用orjoin条件创建新的用户表，该表有一个正则索引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`))现在假设查询userid是1或者18岁的用户，使用下面的SQL会很简单。反例：select*fromuserwhereuserid=1orage=18；正例：//seunionallselect*fromuserwhereuserid=1unionallselect*fromuserwhereage=18;//或者writetwoseparateSQLselect*fromuserwhereuserid=1;原因：使用or可能会使索引失效，需要进行全表扫描。在有或没有索引的情况下，假设已经采用了userId索引，但是当涉及到年龄（age）查询条件时，必须进行全表扫描。过程分为三个步骤：全表扫描+索引扫描+合并。图源：unsplash4。尽量避免在where子句中使用!=或<>运算符，否则引擎会放弃使用索引而进行全表扫描。反例：selectage,namefromuserwhereage<>18;正例：//可以考虑分开两个sql写selectage,namefromuserwhereage<18;selectage,namefromuserwhereage>18;原因：使用!=和<>可能会使索引无效。5.优化limit分页Limits通常用于实现每日分页，但是当offset特别大的时候，查询效率会降低。因为Mysql并没有跳过offset，而是直接去取数据。反例：selectid,name,agefromemployeelimit10000,10；正例：//方案一：Returnthelargestrecord(offset)ofthelastqueryselectid,namefromemployeewhereid>10000limit10;//方案二：orderby+indexselectid,namefromemployeeorderbyidlimit10000,if1会使用优化方案：10个原因；返回最后一条查询记录（偏移量），这样就可以跳过这个偏移量，效率自然会大大提高。方案二：使用+索引排序也可以提高查询效率。6.优化like语句在日常开发中，如果我们使用模糊关键字查询，很容易想到like，但是like可能会使索引失效。反例：selectuserId,namefromuserwhereuserIdlike'%123';正例：selectuserId,namefromuserwhereuserIdlike'123%';原因：https://medium.com/@pawanjain.432/hey-thanks-dovid-for-pointing-out-a-typo-in-13-1000a4103fe67。使用where条件来限制将要查询的数据，以避免返回额外的行假设你想查询一个用户是否是会员，老式的执行代码会这样做。反例：ListuserIds=sqlMap.queryList("selectuserIdfromuserwhereisVip=1");booleanisVip=userIds.contains(userId);正例：LonguserId=sqlMap.queryObject("selectuserIdfromuserwhereuserId='userId'andisVip='1'")booleanisVip=userId!=null;原因：可以检查需要的数据，避免返回不必要的数据，节省成本和计算机开销。图源：unsplash8。考虑在where子句中使用默认值而不是null反面例子：select*fromuserwhereageisnotnull;正例：select*fromuserwhereage>0;//set0asdefault原因：将null值替换为默认值，通常可以创建索引，同时表达会比较清晰。9、如果插入的数据过多，可以考虑批量插入反例：for(Useru:list){INSERTintouser(name,age)values(#name#,#age#)}正例：//Onebatchof500inserts,carriedoutinbatchesinsertintouser(name,age)values(#{item.name},#{item.age})原因：批量插入性能好，节省时间。例如，您需要使用电梯将10,000块砖移动到建筑物的顶部。电梯一次可以放置合适数量的砖块（最多500块），你可以选择一次运送一块砖，或者一次运送500块。哪种解决方案更好？10.谨慎使用distinct关键字。Distinct关键字通常用于过滤重复记录以返回唯一记录。当用于查询一个或多个字段时，Distinct关键字会给查询带来优化效果。但是在字段过多的情况下，Distinct关键字会大大降低查询效率。反面例子：SELECTDISTINCT*fromuser;正例：selectDISTINCTnamefromuser；原因：带“distinct”的语句的CPU时间和占用时间比不带“distinct”的语句高。如果在查询多个字段时使用distinct，数据库引擎会比较数据，过滤掉重复的数据。但是，这个比较和过滤过程会消耗系统资源和CPU时间。图片来源：unsplash11。删除多余重复的索引反例：KEY`idx_userId`(`userId`)KEY`idx_userId_age`(`userId`,`age`)正例：//删除userId索引，因为组合索引(A,B)等同于创建(A)and(A,B)indexesKEY`idx_userId_age`(`userId`,`age`)原因：如果保留了重复的索引，优化器在优化查询时也需要一一考虑，会影响性能。12.如果数据量大，优化修改或删除语句，避免同时修改或删除过多数据，因为会导致CPU占用率过高，影响其他人访问数据库。反例：//Delete100,000or1million+atatime?deletefromuserwhereid<100000;//使用单周期操作，效率低且时间长for(Useruser:list){deletefromuser;}正例：//批量删除，如500次deleteuserwhereid<500;数据过多可能会导致lockwaittimeoutexceederror（锁等待超时错误），建议分批操作。13、使用explain分析SQLscheme在日常开发写SQL的时候，尽量养成一个习惯：使用explain分析自己写的SQL，尤其是索引。explainselect*fromuserwhereuserid=10086orage=18;14。尝试使用unionall而不是union。如果搜索结果中没有重复的记录，我推荐使用unionall而不是union。反例：select*fromuserwhereuserid=1unionselect*fromuserwhereage=10正例：select*fromuserwhereuserid=1unionallselect*fromuserwhereage=10原因：如果使用union，无论搜索结果是否重复，都会尝试合并，排序，然后输出FinalResults。如果知道搜索结果中没有重复的记录，使用unionall代替union会提高效率。15.尽可能使用数字字段。如果该字段仅包含数字信息，请尽量不要将其设置为字符类型。反例：`king_id`varchar(20)NOTNULL;正例：`king_id`int(11)NOTNULL;原因：与数字字段相比，字符类型会降低查询和连接的性能，并会增加存储开销。16、尽可能使用varchar或nvarchar，而不是char或nchar反例：`deptName`char(100)DEFAULTNULL正例：`deptName`varchar(100)DEFAULTNULL原因：一是因为变长字段的存储空间体积小，这种方法可以节省存储空间。其次，查询在相对较小的字段中搜索效率更高。优化和加速SQL查询是一项技术活动。经常思考和尝试，你会打开通往新世界的大门。
