如何优化SQL查询?优化SQL语句的几点建议

时间:2020-08-05 10:54:19 来源: 读芯术头条号


我们致力于打造能够较好运行并延续较长一段时间的query(查询)。本文将给出关于优化SQL语句的几点建议,希望能够帮到你。

1. 尝试不去用select *来查询SQL,而是选择专用字段。

反例:

select*fromemployee;

正例:

selectid,namefromemployee;

理由:

通过只用必要字段进行查询,能够节省资源并减少网络开销。

这样做可能不会使用覆盖索引,会导致一个查询返回到表中。

2. 如果已知只有一个查询结果,推荐使用limit 1

假设有一张员工表格,想在其中找到一名叫jay的员工。

CREATETABLEemployee(

idint(11)NOTNULL,

namevarchar(255)DEFAULTNULL,

ageint(11)DEFAULTNULL,

datedatetimeDEFAULTNULL,

sexint(1)DEFAULTNULL,

PRIMARYKEY(`id`));

反例:

selectid,namefromemployeewherename='jay';

正例:

selectid,namefromemployeewherename='jay'limit1;

理由:添加limit 1后,查找到相应的记录时,便不会继续查找下去,效率会大大提高。

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`))

现在假设需要查询userid为1或为18岁的用户,使用如下的SQL就会很简单。

反例:

select*fromuserwhereuserid=1orage=18;

正例:

//seunionall

select*fromuserwhereuserid=1

unionall

select*fromuserwhereage=18;//OrwritetwoseparateSQL

select*fromuserwhereuserid=1;

理由:or 的使用可能会使索引无效,因此需要进行全表扫描。

在or 无索引的情况下,假设已采用userId索引,但是当涉及到 age(年龄)查询条件时,必须执行全表扫描,其过程分为三步:全表扫描+索引扫描+合并。

图源:unsplash

4. 尽可能避免在where子句中使用!=或<>运算符,否则,引擎将放弃使用索引并执行全表扫描。

反例:

selectage,namefromuserwhereage<>18;

正例:

//Youcanconsiderseparatetwosqlwriteselectage,namefromuserwhereage<18;

selectage,namefromuserwhereage>18;

理由:使用!=和<>可能使索引无效。

5. 优化limit分页

通常用limits来实现日常分页,但当偏移量特别大时,查询效率便会降低。因为Mysql不会跳过偏移量,而是直接获取数据。

反例:

selectid,name,agefromemployeelimit10000,10;

正例:

//Solution1:Returnthelargestrecord(offset)ofthelastquery

selectid,namefromemployeewhereid>10000limit10;//Solution2:orderby+index

selectid,namefromemployeeorderbyidlimit10000,10;

理由:

如果使用了优化方案1,则会返回最末的查询记录(偏移量),因此可以跳过该偏移量,效率自然会大幅提高。

选项二:使用+索引排序,也可以提高查询效率。

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-1000a4103fe6

7. 使用where条件限制将要查询的数据来避免返回额外行

假设要查询一名用户是否为会员,老式执行代码会这样做。

反例:

ListuserIds=sqlMap.queryList("selectuserIdfromuserwhereisVip=1");booleanisVip=userIds.contains(userId);

正例:

LonguserId=sqlMap.queryObject("selectuserIdfromuserwhereuserId='userId'andisVip='1'")booleanisVip=userId!=null;

理由:能够检查需要的数据,避免返回非必要数据,并能节省费用和计算机开销。

打造出色查询:如何优化SQL查询?

图源:unsplash

8. 考虑在where子句中使用默认值而不是null

反例:

select*fromuserwhereageisnotnull;

正例:

select*fromuserwhereage>0;//Set0asdefault

理由:如果用默认值取代null值,则通常可以建立索引,与此同时,表达式将相对清晰。

9. 如果插入数据过多,可以考虑批量插入

反例:

for(User

u:list){

INSERTintouser(name,age)values(#name#,#age#)

}

正例:

//Onebatchof500inserts,carriedoutinbatchesinsertintouser(name,age)values

 

(#{item.name},#{item.age})

 

理由:批量插入性能良好且省时。

打个比方,在有电梯的情况下,你需要将1万块砖移送到建筑物的顶部。电梯一次可以放置适当数量的砖块(最多500块),你可以选择一次运送一块砖,也可以一次运送500块。哪种方案更好?

10. 谨慎使用distinct关键词

Distinct关键词通常用于过滤重复记录以返回唯一记录。当其被用于查询一个或几个字段时,Distinct关键词将为查询带来优化效果。然而,在字段过多的情况下,Distinct关键词将大大降低查询效率。

反例:

SELECTDISTINCT*fromuser;

正例:

selectDISTINCTnamefromuser;

理由:带有“distinct”语句的CPU时间和占用时间高于没有“ distinct”的语句。

如果在查询多字段时使用distinct,数据库引擎将比较数据,并滤除重复的数据。然而,该比较和滤除过程将消耗系统资源和CPU时间。

打造出色查询:如何优化SQL查询?

图源:unsplash

11. 删除多余和重复的索引

反例:

KEY`idx_userId`(`userId`)

KEY`idx_userId_age`(`userId`,`age`)

正例:

//DeletetheuserIdindex,becausethecombinedindex(A,B)isequivalenttocreatingthe(A)and(A,B)indexesKEY`idx_userId_age`(`userId`,`age`)

理由:若保留重复的索引,那么优化程序在优化查询时也需要对其进行一一考量,这会影响性能。

12. 如果数据量很大,优化 modify或delete语句

避免同时修改或删除过多数据,因其将导致CPU利用率过高,从而影响他人对数据库的访问。

反例:

//Delete100,000or1million+atatime?

deletefromuserwhereid<100000;//Orusesinglecycleoperation,lowefficiencyandlongtime

for(Useruser:list){

deletefromuser;}

正例:

//Deleteinbatches,suchas500eachtimedeleteuserwhereid<500;

deleteproductwhereid>=500andid<1000;

理由:一次删除过多数据,可能会导致lock wait timeout exceed error(锁定等待超时错误),因此建议分批操作。

13. 使用explain分析SQL方案

在日常开发中编写SQL时,尝试养成习惯:使用explain来分析自己编写的SQL,尤其是索引。

explainselect*fromuserwhereuserid=10086orage=18;

14. 尝试用union all代替union

如果搜索结果里没有重复的记录,我推荐用union all代替union。

反例:

select*fromuserwhereuserid=1

union

select*fromuserwhereage=10

正例:

select*fromuserwhereuserid=1

unionall

select*fromuserwhereage=10

理由:

如果使用union,则无论有没有重复的搜索结果,它都会尝试对其进行合并、排序,然后输出最终结果。

若已知搜索结果中没有重复记录,用union all代替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查询是门技术活,常常思考和尝试,你会打开新世界的大门。

关键词:优化SQL查询

关于我们 加入我们 广告服务 网站地图

All Rights Reserved, Copyright 2004-2020 www.ctocio.com.cn

如有意见请与我们联系 邮箱:5 53 13 8 [email protected]

豫ICP备20005723号    IT专家网 版权所有