SQL优化的26个小技巧

于由wagnwu发布

浏览次数: 11

前言

梳理了SQL 优化的26条小技巧，建议收藏起来慢慢看~~

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 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
"list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})

理由：

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

打个比喻: 假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放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 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 ;

理由：

  • 查询效率：当返回的数据量过大时，查询所需的时间会显著增加，导致数据库性能下降。通过限制返回的数据量，可以缩短查询时间，提高数据库响应速度。
  • 网络传输：大量数据的传输会占用网络带宽，可能导致网络拥堵和延迟。减少返回的数据量可以降低网络传输的负担，提高数据传输效率。

0
分类： mysql

0 条评论

发表回复

Avatar placeholder

您的邮箱地址不会被公开。 必填项已用 * 标注

此站点使用 Akismet 来减少垃圾评论。了解我们如何处理您的评论数据。

相关文章

mysql

一文探索应运而生的数据库们

导读 本文系统性回顾了数据库技术的发展历程与现状，从层次数据库 IMS 阅读更多…

mysql

深入探索MySQL索引策略

导读 本文旨在深入探讨MySQL数据库中索引的设计与优化方法。 引言 阅读更多…

mysql

MySQL日志15连问(–)

前言 。最近行情越来越卷了，给大家整理了15道经典MySQL日志面试题 阅读更多…

蜀ICP备16001794号
© 2014 - 2024 linpxing.cn All right reserved.