后端程序员必备：编写高质量 SQL 的 30 个技巧

2024-01-16 04:02:56 阅读 0

前言

本文将基于示例demo讲解30条优化SQL的建议。大多数都是在实际开发中总结出来的。希望对大家有所帮助。

1、查询SQL时尽量不要使用*，而是特定字段。

反例：

select * from employee;

正面例子：

select id，name from employee;

原因：

2、如果知道查询结果只有一条或者只有一条最大/最小记录，建议使用limit 1。

假设有一个员工表，你想找到一个名叫 Jay 的人。

CREATE TABLE `employee` ( 
`id` int(11) NOT NULL, 
`name` varchar(255) DEFAULT NULL,  
`age` int(11) DEFAULT NULL,  
`date` datetime DEFAULT NULL, 
`sex` int(1) DEFAULT NULL, 
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

反例：

select id，name from employee where name='jay'

正面例子

select id，name from employee where name='jay' limit 1;

原因：

3.尽量避免在where子句中使用or来连接条件。

新建用户表，使用公共索引，表结构如下：

CREATE TABLE `user` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`userId` int(11) NOT NULL,  
`age` int(11) NOT NULL, 
`name` varchar(255) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设现在需要查询年龄为1岁或18岁的用户，很容易有下面的SQL

反例：

select * from user where userid=1 or age =18

正面例子：

//使用union all
select * from user where userid=1 
union all
select * from user where age = 18

//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18

原因：

对于or+age没有索引的情况，假设已经到了索引，但是到达age查询条件的时候，还是要全表扫描，也就是说需要三步的过程：全表扫描+ 索引扫描 + 合并

如果一开始就进行全表扫描，那么一次扫描就完成了。 MySQL 有一个优化器。考虑到效率和成本，索引遇到OR条件时可能会失败，这看起来很合理。

4.优化限制分页

我们在做日常的分页需求时，通常会使用limit来实现。但当偏移量特别大时，查询效率就会变低。

反例：

select id，name，age from employee limit 10000，10

正面例子：

//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.

//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

//方案三：在业务允许的情况下限制页数：

原因：

5.优化你的点赞语句

在日常开发中，如果使用模糊关键字查询，很容易想到like，但是like很可能会让你的索引失效。

反例：

select userId，name from user where userId like '3';

正面例子：

select userId，name from user where userId like '123%';

原因：

6、使用where条件限制查询的数据，避免返回冗余行

假设业务场景是这样的：查询用户是否是会员。我见过旧的实现代码是这样的。。。

反例：

List userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);

正面例子：

Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId！=null;

原因：

7、尽量避免在索引列上使用MySQL的内置函数。

业务需求：查询最近7天内登录的用户（假设添加了索引）

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正面例子：

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

原因：

8、尽量避免对where子句中的字段进行表达式操作。这将导致系统放弃使用索引并扫描整个表。

反例：

select * from user where age-1 =10；

正面例子：

select * from user where age =11；

原因：

9. 内连接、左连接、右连接，优先选择内连接。如果是左连接，左表的结果应该尽可能小。

在满足所有SQL要求的前提下，建议优先使用Inner join。如果要使用左连接，左表的数据结果应该尽可能小。如果可以的话，尽量放在左侧进行处理。

反例：

select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2;

正面例子：

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

原因：

10.尽量避免在where子句中使用!=或运算符，否则引擎将放弃使用索引并执行全表扫描。

反例：

select age,name  from user where age <>18;

正面例子：

//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

原因：

11、使用联合索引时，要注意索引列的顺序，一般遵循最左匹配原则。

表结构：（有联合索引，第一个，年龄最后）

CREATE TABLE `user` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`userId` int(11) NOT NULL, 
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL, 
PRIMARY KEY (`id`),  
KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

反例：

select * from user where age = 10;

php按条件查询sql语句_sql语句或者条件查询_查询语句中条件子句是()

正面例子：

//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;

查询语句中条件子句是()_php按条件查询sql语句_sql语句或者条件查询

sql语句或者条件查询_查询语句中条件子句是()_php按条件查询sql语句

原因：

12、优化查询，可以考虑对where和order by涉及到的列建立索引，尽量避免全表扫描。

反例：

select * from user where address ='深圳' order by age ;

sql语句或者条件查询_php按条件查询sql语句_查询语句中条件子句是()

正面例子：

添加索引alter table user add index idx_address_age (address,age)

sql语句或者条件查询_php按条件查询sql语句_查询语句中条件子句是()

13、如果插入的数据过多，可以考虑批量插入。

反例：

for(User u :list){ 
INSERT into user(name,age) values(#name#,#age#)
}

正面例子：

//一次500批量插入，分批进行
insert into user(name,age) values
"list" item="item" index="index" separator=",">   
    (#{item.name},#{item.age})

原因：

打个比方：如果你需要将 10,000 块砖移到建筑物的屋顶，你就有一部电梯。电梯一次可以放入适量的砖块（最多500块）。您可以选择一次运输一块砖或一次运输 500 块砖。你认为哪一项最耗费时间？

14. 适当时，使用覆盖索引。

覆盖索引可以使你的SQL语句不需要返回表。只需访问索引就可以获取所有需要的数据，大大提高了查询效率。

反例：

// like模糊查询，不走索引了
select * from user where userid like '3%'

php按条件查询sql语句_查询语句中条件子句是()_sql语句或者条件查询

正面例子：

//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '3%';

sql语句或者条件查询_php按条件查询sql语句_查询语句中条件子句是()

15. 谨慎使用关键词

关键字通常用于过滤重复记录以返回唯一记录。当查询一个字段或者极少数字段时使用，可以给查询带来优化效果。但字段较多时使用会大大降低查询效率。

反例：

SELECT DISTINCT * from  user;

正面例子：

select DISTINCT name from user;

原因：

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

反例：

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

正面例子：

  //删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引 
  KEY `idx_userId_age` (`userId`,`age`)

原因：

17. 如果数据量很大，请优化您的修改/删除语句。

避免同时修改或删除过多的数据，这样会导致CPU利用率过高，影响其他人对数据库的访问。

反例：

//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{  
    delete from user； 
}

正面例子：

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；

原因：

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

反例：

select * from user where age is not null;

php按条件查询sql语句_sql语句或者条件查询_查询语句中条件子句是()

正面例子：

//设置0为默认值select * from user where age>0;

查询语句中条件子句是()_sql语句或者条件查询_php按条件查询sql语句

原因：

如果mysql优化器发现建索引的成本比不建索引还高的话，肯定会放弃索引，这些条件！ =、>、通常被认为使索引无效。其实这是因为一般情况下查询成本较高，优化器会自动放弃索引。

19.表连接不要超过5个 20.合理使用exist&in

假设A表代表某企业的员工表，B表代表部门表。要查询所有部门的所有员工，很容易有下面的SQL：

select * from A where deptId in (select deptId from B);

这样写就相当于：

首先查询部门表B

从B

然后通过部门查询A的员工

* 来自 A，其中 A. = B。

它可以被抽象成这样的循环：

   List<> resultSet ; 
    for(int i=0;i          for(int j=0;j          if(A[i].id==B[j].id) {        
             resultSet.add(A[i]);     
             break;       
          }   
      }  
  }

显然，除了使用in之外，我们还可以实现同样的查询功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因为查询的理解就是先执行主查询，获取数据，然后放入子查询中进行条件验证。根据验证结果（真或假），决定是否保留主查询的数据结果。

那么，这样写就相当于：

* 从A开始，首先从表A进行循环

* from B where A. = B.，然后从B表循环。

同理，可以抽象成这样的循环：

   List<> resultSet ;   
    for(int i=0;i          for(int j=0;j          if(A[i].deptId==B[j].deptId) {     
             resultSet.add(A[i]);         
             break;     
          }  
      }  
  }

数据库最难的就是与程序的链接和释放。假设链接两次，每次都进行百万数据集查询，然后就离开，所以只做了两次；反之，建立了数百万个链接，并且多次重复申请链接释放，因此系统将不再受到限制。即MySQL的优化原则是小表驱动大表，小数据集驱动大数据集，从而获得更好的性能。

因此，我们需要选择最小的最外层循环，即如果B的数据量小于A，则适合使用in，如果B的数据量大于A，则适合选择exist 。

21.尝试用union all替换union

如果搜索结果中没有重复记录，建议将union替换为union all。

反例：

select * from user where userid=1 
union  
select * from user where age = 10

正面例子：

select * from user where userid=1
union all
select * from user where age = 10

原因：

22.索引不宜过多，一般在5个以内。 23.尽量使用数字字段。如果字段只包含数字信息，尽量不要将其设计为字符字段。

反例：

`king_id` varchar（20） NOT NULL COMMENT '守护者Id'

正面例子：

 `king_id` int(11) NOT NULL COMMENT '守护者Id'

原因：

24、索引不适合建立在有大量重复数据的字段上，比如性别数据库字段。

因为SQL优化器根据表中的数据量来优化查询，如果索引列中有大量重复数据，Mysql查询优化器计算出不使用索引更便宜，很可能放弃索引。

25.尽量避免向客户端返回过多的数据。

假设业务需求是用户请求查看过去一年观看过的直播数据。

反例：

//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)

正面例子：

//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize

//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

26、在SQL语句中连接多个表时，请使用表的别名，并在每列前面加上别名，使语义更清晰。

反例：

select  * from A inner
join B on A.deptId = B.deptId;

正面例子：

select  memeber.name,deptment.deptName from A member inner
join B deptment on member.deptId = deptment.deptId;

27. 尽可能使用 / 代替 char/nchar。

反例：

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'

正面例子：

  `deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'

原因：

28、为了提高group by语句的效率，可以在执行语句之前过滤掉不需要的记录。

反例：

select job，avg（salary） from employee  group by job having job ='president' 
or job = 'managent'

正面例子：

select job，avg（salary） from employee where job ='president' 
or job = 'managent' group by job；

29、如果字段类型为字符串，where必须用引号括起来，否则索引无效。

反例：

select * from user where userid =123;

查询语句中条件子句是()_php按条件查询sql语句_sql语句或者条件查询

正面例子：

select * from user where userid ='123';

php按条件查询sql语句_查询语句中条件子句是()_sql语句或者条件查询

原因：

30. 使用分析 SQL 的计划

在日常开发和编写 SQL 时，尽量养成一种习惯。分析你写的SQL，尤其是是否使用索引。

explain select * from user where userid =10086 or age =18;

sql语句或者条件查询_php按条件查询sql语句_查询语句中条件子句是()

-EOF-

上一篇： mysql还是会使用索引？ mysql或者条件可以使用索引来避免整表

下一篇： MySQL索引优化策略（二）：多个索引应该建立为独立索引还是联合索引？

标签：索引字段假设

排行榜

259℃1hustoj比赛作业里面的时间是什么含义，OI排名跟普通排名有何区别？
214℃2Ubuntu18.04搭建LAMP环境完成帝国CMS安装
212℃3LAMP环境如何搭建多站点
210℃4 linux 下基本文件操作
197℃5hustoj脚本把OJ装在哪里了？可以卸载么？
187℃6hustoj如何限制未登录用户访问？
170℃7hustoj比赛后题目看不见了怎么弄？
160℃8Apache如何做301重定向

图文教程

技术标签