SQL语句优化（姊妹篇2）

码刀攻城

已于 2024-06-18 10:58:21 修改

阅读量964

点赞数 21

分类专栏： Mysql 文章标签： sql 数据库

于 2024-03-22 12:10:46 首次发布

本文链接：https://blog.csdn.net/weixin_45541665/article/details/136935112

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

项目早期数据量少，开发人员开发时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。
SQL优化整体主要体现在两个方面：

减少IO的次数，就是所有查询尽量全部走索引
减少IO的数据量，比如mysql5.6后的索引下推等，尽量减少传输数据量

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

反例子：

select * from employee;

正例子：

select id，name from employee;

原由：

只取需要的字段，节省资源、减少网络开销。
select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

2、一条记录的查询用limit 1

反例：

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

正例

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

原由：

加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能。

3、or 可用 union all 代理

新建一个user表，它有一个普通索引userId，表结构如下：

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;

假设现在需要查询userid为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可能会使索引失效，从而全表扫描。

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

4、优化limit分页， where条件辅助

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

反例：

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

正例：

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

原由：

当偏移量最大的时候，查询效率就会越低，因为Mysql 并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
如果使用优化方案，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

5、优化like语句

日常开发中用到模糊查询关键字like 很可能让你的索引失效。

反例：

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

正例：

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

原由：

把%放前面，并不走索引
把% 放关键字后面，还是会走索引的

6、索引列上避免使用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的内置函数，索引失效
如果索引列不加内置函数，索引还是会走的

7、索引列上避免使用表达式操作

反例：

select * from user where id-1 =10；

正例：

select * from user where id =11；

原由：

虽然id加了索引，但是因为对它进行运算，索引失效了。

8、Inner join 、left join、right join，优先使用Inner join，如果是left 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是等值连接，返回的行数比较少时，性能相对会好一点。
同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

9、避免: 不等符号（ `!=` 或 `<>` ），索引失效

反例：

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;

原由：

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

10、联合索引：`带头大哥不能死，中间兄弟不能断`

表结构：（有一个联合索引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优化器有关的。

11、在 where 及 order by 涉及的列上建立索引

反例：

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

正例：

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

12、如果插入数据过多，考虑合适数据地批量插入。

反例：

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>

原由：

合适数量的批量插入性，可以减少IO的消耗

13、在适当的时候，使用 `覆盖索引`。

覆盖索引：就是select的数据列仅仅用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

反例：

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

正例：

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

14、字段很多慎用distinct关键字

distinct 关键字一般用来过滤重复记录，以返回不重复的记录。
在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。
但是在 字段很多 的时候使用，却会大大降低查询效率。

反例：

SELECT DISTINCT * from  user;

正例：

select DISTINCT name from user;

原由：

带distinct的语句 cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

15、删除冗余、重复索引

反例：

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

正例:

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

原由：

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

16、如果数据量较大，优化你的修改/删除语句。

避免同时修改或删除 过多数据，因为会造成 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；

原由：

一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

17、where句中用 `默认值` 代替null。

反例：

select * from user where age is not null;

正例：

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

原由：

并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。

如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件!=，>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。

如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

18、不要超过`5个`以上的表连接

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

19、exist & in的合理利用

假设表A 员工表，表B 部门表，需要查询所有部门的所有员工，很容易有以下SQL:

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

这样写等价于：

先查询部门表B
再由部门deptId，查询A的员工，再从A表做循环.

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

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

那么，这样写就等价于：

select * from A,先从A表做循环
select * from B where A.deptId = B.deptId,再从B表做循环.

本着小表驱动大表的逻辑，让小表限制性，所以如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。

20、尽量用 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，这样会提高效率。

21、索引不宜太多，一般5个以内。

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

22、尽量使用 `数字型字段`，

若只含数值信息的字段尽量不要设计为字符型

反例：

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

正例：

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

原由：

相对于数字型字段，字符型会 降低查询和 连接 的性能，并会增加存储开销。

23、索引远离大量重复数据的字段

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

24、字段类型是字符串，where时一定用 `引号`括起来，否则索引失效

反例：

select * from user where userid =123;

select * from user where userid ='123';

原由：

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

25、SQL常规优化思路

一、通过慢查日志等定位那些执行效率较低的SQL语句

二、explain 分析SQL的执行计划

需要重点关注type、key、rows、filtered、extra。

type由上至下，效率越来越高

1、ALL 全表扫描  
  
2、index 索引全扫描  
  
3、range 索引范围扫描，常用语<,<=,>=,between,in等操作  
  
4、ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中  
  
5、eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询  
  
6、const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询  
  
7、null MySQL不访问任何表或索引，直接返回结果

虽然上至下，效率越来越高，但是根据cost模型

假设有两个索引 idx1(a, b, c), idx2(a, c)  
SQL为"select * from t where a = 1 and b in (1, 2) order by c";  
如果走idx1，那么是type为range，如果走idx2，那么type是ref；  
当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

Extra

1、Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。  
通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。  
然后关键字被排序，并按排序顺序检索行。  
  
2、Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化  
  
3、Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）  
避免访问了表的数据行，效率不错！如果同时出现 using where，  
意味着无法直接通过索引查找来查询到符合条件的数据。  
  
4、Using index condition：MySQL5.6之后新增的ICP  
using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤  
而不是在服务层过滤，利用索引现有的数据减少回表的数据。

三、show profile 分析

了解SQL执行的线程的状态及消耗的时间。

默认是关闭的，开启语句“set profiling = 1;”

SHOW PROFILES ;  
SHOW PROFILE FOR QUERY  #{id};

四、trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";  
set optimizer_trace_max_mem_size=1000000;  
select * from information_schema.optimizer_trace;

五、确定问题，通用解决方案：

①优化索引

②优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤

③改用其他实现方式：ES、数仓等

④数据拆分处理，比如拆分业务，拆分SQL，数据碎片处理

场景分析

案例1、最左匹配

索引

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL语句

select * from test where orderno=''

查询匹配从左往右匹配，要使用order_no走索引，必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后顺序。

案例2、隐式转换

索引

KEY `idx_mobile` (`mobile`)

SQL语句

select * from _user where mobile=12345678901

隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。

案例3、大分页

索引

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL语句

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式，

1、把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。

2、采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;

案例4、in + order by

索引

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL语句

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

in查询在MySQL底层是通过n*m的方式去搜索，类似union，但是效率比union高。

in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

处理方式，可以(order_status, created_at)互换前后顺序，
并且调整SQL为延迟关联来进行数据查询

案例5、范围查询阻断，后续字段不能走索引

索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL语句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索

select * from _order where shop_id=1 and order_status not in (1,2)  
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

select * from test_order where  order_status = 1

查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

案例8、复杂查询

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';  
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

如果是统计某些数据，可能改用数仓进行解决；

如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

案例9、asc和desc混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存,那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

码刀攻城

关注

21
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
SQL语句优化（姊妹篇2）

工作中你一定能用到的sql语句优化，看完背下来吧
复制链接

扫一扫

专栏目录

SQL语句优化（姊妹篇2）

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

2、一条记录的查询用limit 1

3、or 可用 union all 代理

4、优化limit分页， where条件辅助

5、优化like语句

6、索引列上 避免使用mysql的内置函数

7、索引列上 避免使用 表达式操作

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

9、避免: 不等符号（ != 或 <> ），索引失效

10、联合索引：带头大哥不能死，中间兄弟不能断

11、在 where 及 order by 涉及的 列 上建立索引

12、如果插入数据过多，考虑合适数据地批量插入。

13、在适当的时候，使用 覆盖索引。

14、字段很多慎用distinct关键字

15、删除冗余、重复索引

16、如果数据量较大，优化你的修改/删除语句。

17、where句中用 默认值 代替null。

18、不要超过5个以上的表连接

19、exist & in的合理利用

20、尽量用 union all 替换 union

21、索引不宜太多，一般5个以内。

22、尽量使用 数字型字段，

23、索引 远离 大量重复数据的字段

24、字段类型是字符串，where时一定用 引号括起来，否则索引失效

25、SQL常规优化思路

一、通过慢查日志等定位那些执行效率较低的SQL语句

二、explain 分析SQL的执行计划

三、show profile 分析

四、trace

五、确定问题，通用解决方案：

场景分析

案例1、最左匹配

案例2、隐式转换

案例3、大分页

案例4、in + order by

案例5、范围查询阻断，后续字段不能走索引

案例6、不等于、不包含不能用到索引的快速搜索

案例7、优化器选择不使用索引的情况

案例8、复杂查询

案例9、asc和desc混用

案例10、大数据

6、索引列上避免使用mysql的内置函数

7、索引列上避免使用表达式操作

9、避免: 不等符号（ `!=` 或 `<>` ），索引失效

10、联合索引：`带头大哥不能死，中间兄弟不能断`

11、在 where 及 order by 涉及的列上建立索引

13、在适当的时候，使用 `覆盖索引`。

17、where句中用 `默认值` 代替null。

18、不要超过`5个`以上的表连接

22、尽量使用 `数字型字段`，

23、索引远离大量重复数据的字段

24、字段类型是字符串，where时一定用 `引号`括起来，否则索引失效