SQL优化的26个小技巧,收藏好!!!

数据库 其他数据库
如果userId加了索引,age没加索引,以上or的查询SQL,假设它走了userId的索引,但是走到age查询条件时,它还得全表扫描,也就是需要三步过程:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就完事。

1、查询SQL尽量不要使用select *,而是select具体字段。

反例子:

select * from employee;

正例子:

select id,name, age from employee;
  • select具体字段,节省资源、减少网络开销。
  • select * 进行查询时,很可能就不会使用到覆盖索引了,就会造成回表查询。

2、应尽量避免在where子句中使用or来连接条件

反例:

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可能会使索引失效,从而全表扫描。一位朋友踩过这个坑,差点把数据库CPU打满了。

如果userId加了索引,age没加索引,以上or的查询SQL,假设它走了userId的索引,但是走到age查询条件时,它还得全表扫描,也就是需要三步过程:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就完事。mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引可能失效,看起来也合情合理。

3. 尽量使用limit,避免不必要的返回

假设一个用户有多个订单,要查询最新订单的下单时间的话,你是这样查询:

select id, order_date from order_tab 
where user_id=666 
order by create_date desc;

获取到订单列表后,取第一个的下单时间:

List<Order> orderList = orderService.queryOrderListByUserId('666');
Date orderDate = orderList.get(0).getOrderDate();

还是用limit ,只获取最新那个订单返回:

select id, order_date from order_tab 
where user_id=666 
order by create_date desc limit 1;

显然,使用limit的更好~ 因为整体性能更好。

4. 尽量使用数值类型而不是字符串

比如我们定义性别字段的时候,更推荐0代表女生,1表示男生,而不是定义为WOMEN 或者MAN的字符串。

因为:

  • 数值类型(如 INT, FLOAT, DECIMAL 等)通常占用的存储空间比字符串类型(如 VARCHAR, CHAR)小
  • 数值类型的比较和计算速度通常比字符串快

5. 批量操作(更新、删除、查询)

反例:

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

正例:

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

理由:

  • 批量插入性能好,更加省时间

打个比喻: 假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖(最多放500),你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

6、尽量用 union all 替换 union

如果检索结果中不会有重复的记录,推荐union all 替换 union。

反例:

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

理由:

  • 如果使用union,不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序。如果已知检索结果没有重复记录,使用union all 代替union,这样会提高效率。

7. 尽可能使用not null定义字段

如果没有特殊的理由, 一般都建议将字段定义为NOT NULL。

city VARCHAR(50) NOT NULL

为什么呢?

  • NOT NULL 可以防止出现空指针问题。
  • 其次,NULL值存储也需要额外的空间的,它也会导致比较运算更为复杂,使优化器难以优化SQL。
  • NULL值有可能会导致索引失效

8、尽量避免在索引列上使用mysql的内置函数

业务需求:查询最近七天内登陆过的用户(假设loginTime加了索引)

反例:

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

理由:

  • 索引列上使用mysql的内置函数,索引失效

9、应尽量避免在 where 子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫

反例:

select * from user where age-1 =10;

正例:

select * from user where age =11;

理由:

  • 虽然age加了索引,但是因为对它进行运算,索引直接迷路了。。。

10、为了提高group by 语句的效率,可以在执行到该语句前,把不需要的记录过滤掉。

假设有一个 orders 表,存储了所有用户的订单信息,并包含 city 字段表示用户所在城市。我们想要计算来自北京的每个用户的总消费金额。

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    city VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2)
);

计算北京用户的消费总额,按用户分组,反例SQL(不使用 WHERE 条件过滤):

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = '北京';

应该先用 WHERE 条件过滤,正例如下:

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = '北京'
GROUP BY user_id;

11、优化你的like语句

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

反例:

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

正例:

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

理由:

  • 把%放前面,并不走索引.

有些时候你就是需要包含关键词,可以结合其他查询条件(加索引的其他条件)结合起来。或者可以借助 Elasticsearch 来进行模糊查询,这里知道like在前会导致索引失效这个点就好啦。

12.使用小表驱动大表的思想

小表驱动大表,这主要是为了优化性能,让查询执行得更高效。背后的核心原因是减少数据扫描量,尽量让数据库在处理时能先过滤掉大量无关数据,从而缩短查询时间。

假设我们有个客户表和一个订单表。其中订单表有10万记录,客户表只有1000行记录。

现在要查询下单过的客户信息,可以这样写:

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 会逐行扫描 customers 表(即小表),对每一行 c.id,在 orders 表(大表)中检查是否有 customer_id = c.id 的记录。

当然,也可以使用in实现:

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

in 查询会先执行内部查询部分 SELECT customer_id FROM orders,获得 orders 表(大表)中的所有 customer_id,然后在 customers 表(小表)中查找匹配的 id。

因为orders表的数据量比较大,因此这里用exists效果会相对更好一点。

13. in查询的元素不宜太多

如果使用了in,即使后面的条件加了索引,还是要注意in后面的元素不要过多哈。in元素一般建议不要超过200个,如果超过了,建议分组,每次200一组进行哈。

反例:

select user_id,name from user where user_id in (1,2,3...1000000);

如果我们对in的条件不做任何限制的话,该查询语句一次性可能会查询出非常多的数据,很容易导致接口超时。尤其有时候,我们是用的子查询,in后面的子查询,你都不知道数量有多少那种,更容易采坑.如下这种子查询:

select * from user where user_id in (select author_id from artilce where type = 1);

正例是,分批进行,比如每批200个:

select user_id,name from user where user_id in (1,2,3...200);

14. 优化limit分页

我们日常做分页需求时,一般会用 limit 实现,但是当偏移量特别大的时候,查询效率就变得低下,也就是出现深分页问题。

反例:

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。

标签记录法

就是标记一下上次查询到哪一条了,下次再来查的时候,从该条开始往下扫描。就好像看书一样,上次看到哪里了,你就折叠一下或者夹个书签,下次来看的时候,直接就翻到啦。

假设上一次记录到100000,则SQL可以修改为:

select  id,name,balance FROM account where id > 100000 limit 10;

这样的话,后面无论翻多少页,性能都会不错的,因为命中了id索引。但是这种方式有局限性:需要一种类似连续自增的字段。

延迟关联法

延迟关联法,就是把条件转移到主键索引树,然后减少回表。如下:

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

优化思路就是,先通过idx_create_time二级索引树查询到满足条件的主键ID,再与原表通过主键ID内连接,这样后面直接走了主键索引了,同时也减少了回表。

15. 尽量使用连接查询而不是子查询

因为使用子查询,可能会创建临时表。

反例如下:

SELECT *
FROM customers c
WHERE c.id IN (
    SELECT o.customer_id
    FROM orders o
);

IN 子查询会在 orders 表中查询所有 customer_id,并生成一个临时结果集。

我们可以用连接查询避免临时表:

SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id;
  • 通过 JOIN 直接将 customers 和 orders 表关联,符合条件的记录一次性筛选完成。
  • MySQL 优化器通常可以利用索引来加速 JOIN,避免了临时表的创建,查询效果就更佳

16、Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小

  • Inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
  • left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
  • right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。

都满足SQL需求的前提下,推荐优先使用Inner join(内连接),如果要使用left 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;

理由:

  • 如果inner join是等值连接,或许返回的行数比较少,所以性能相对会好一点。
  • 同理,使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少。

17、应尽量避免在 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;

理由:

  • 使用!=和<>很可能会让索引失效

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

表结构:(有一个联合索引idx_userid_age,userId在前,age在后)

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;

正例:

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

理由:

  • 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
  • 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的。

19、对查询进行优化,应考虑在 where 及 order by 涉及的列上建立索引,尽量避免全表扫描。

反例:

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

正例:

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

20、在适当的时候,使用覆盖索引。

覆盖索引能够使得你的SQL语句不需要回表,仅仅访问索引就能够得到所有需要的数据,大大提高了查询效率。

反例:

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

正例:

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

21、删除冗余和重复索引

反例:

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

正例:

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

理由:

  • 重复的索引需要维护,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能的。

22、不要有超过3个以上的表连接

  • 连表越多,编译的时间和开销也就越大。
  • 把连接表拆开成较小的几个执行,可读性更高。
  • 如果一定需要连接很多表才能得到数据,那么意味着糟糕的设计了。

23、索引不宜太多,一般5个以内。

  • 索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率。
  • insert或update时有可能会重建索引,所以建索引需要慎重考虑,视具体情况来定。
  • 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要。

24、索引不适合建在有大量重复数据的字段上,如性别这类型数据库字段。

因为SQL优化器是根据表中数据量来进行查询优化的,如果索引列有大量重复数据,Mysql查询优化器推算发现不走索引的成本更低,很可能就放弃索引了。

25、如何字段类型是字符串,where时一定用引号括起来,否则索引失效

反例:

select * from user where userid =123;

正例:

select * from user where userid ='123';

理由:

  • 为什么第一条语句未加单引号就不走索引了呢?这是因为不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为浮点数再做比较。

26、尽量避免向客户端返回过多数据量。

假设业务需求是,用户请求查看自己最近一年观看过的直播数据。

反例:

//一次性查询所有数据回来
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 ;

理由:

  • 查询效率:当返回的数据量过大时,查询所需的时间会显著增加,导致数据库性能下降。通过限制返回的数据量,可以缩短查询时间,提高数据库响应速度。
  • 网络传输:大量数据的传输会占用网络带宽,可能导致网络拥堵和延迟。减少返回的数据量可以降低网络传输的负担,提高数据传输效率。
责任编辑:武晓燕 来源: 捡田螺的小男孩
相关推荐

2023-03-10 08:45:15

SQL优化统计

2018-11-28 12:30:58

Python命令行编程语言

2021-11-10 18:52:42

SQL技巧优化

2009-06-18 11:12:42

Hibernate S优化

2023-05-04 08:02:13

2024-04-09 14:27:39

2023-09-25 13:15:50

SQL数据库

2024-06-21 08:21:44

2021-06-16 10:50:16

Python代码优化

2021-05-07 16:02:54

Python代码优化

2022-03-10 08:01:06

CSS技巧选择器

2022-11-24 10:34:05

CSS前端

2021-02-03 10:46:31

SQL数据库技巧

2011-05-10 17:06:05

SEO

2021-09-06 10:25:27

Python代码优化

2021-11-18 08:20:22

接口索引SQL

2021-05-29 07:36:08

MySQLSQL数据库

2021-07-02 09:45:13

Python优化代码

2022-05-24 14:07:53

OpenFeignSpring开源

2020-12-24 09:18:51

SQL数据库函数
点赞
收藏

51CTO技术栈公众号