业务系统如何优化慢Sql?

优化慢SQL是一个复杂且需要经验的过程,因为SQL的性能受到多种因素的影响,包括数据库设计、表结构、索引、查询逻辑等。

MySql 业务系统查询过程中如何优化慢SQL?

 以下重点讨论explain 分析慢sql的过程。   

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

2、 explain 分析SQL的执行计划

执行计划需要重点关注的内容 :type、rows、filtered、extra.

2.1、 type 由上至下,效率越来越高:
ALL 全表扫描  
index 索引全扫描  
range 索引范围扫描,常用语<,<=,>=,between,in 等操作  
ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
eq_ref 类似 ref,区别在于使用的是唯一索引,使用主键的关联查询
const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询  
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

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

3、show profile 分析

了解 SQL 执行的线程的状态及消耗的时间。
默认是关闭的,开启语句“set profiling = 1;”
1
2
SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

SQL

4、trace

分析优化器如何选择执行计划,通过 trace 文件能够进一步了解为什么又会选择 A 执行计划而不选择 B 执行计划。
1
2
3
set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

SQL

5、确定问题并采用相应的措施

如下:

优化索引

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

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

数据碎片处理

6、场景分析

索引生效原则分析

6.1 最左匹配
1
2
3
4
5
6
7
-- 最左匹配
-- 索引
KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

-- sql 语句
select * from _t where orderno=''
-- 查询匹配从左往右匹配,要使用 order_no 走索引,必须查询条件携带 shop_id 或者索引(shop_id,order_no)调换前后顺序。

SQL

6.2 隐式转换
1
2
3
4
5
6
-- 索引
KEY `idx_mobile` (`mobile`)

-- sql 语句
select * from _user where mobile=12345678901
-- 隐式转换相当于在索引上做运算,会让索引失效。mobile 是字符类型,使用了数字,应该使用字符串匹配,否则 MySQL 会用到隐式替换,导致索引失效。

SQL

6.3 大分页
1
2
3
4
5
6
7
8
9
10
11
12
-- 索引
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 回表,但是要记得索引需要完全覆盖才有效果。

-- 修改如下:
select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

SQL

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 为延迟关联。
6.4 范围查询阻断,后续字段不能走索引
1
2
3
4
5
6
7
-- 索引
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”。

SQL

6.5 in + order by 查询
1
2
3
4
5
6
-- 索引
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

SQL

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 为延迟关联。

6.6 不等于、不包含不能用到索引的快速检索
1
2
3
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等。

SQL

6.7 优化器选择不使用索引的情况
1
2
-- sql 语句
select * from _order where  order_status = 1

SQL

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

6.8 复杂查询
1
2
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

如果是统计某些数据,可能改用数仓进行解决;如果是业务上就有那么复杂的查询,可能就不建议继续走 SQL 了,而是采用其他的方式进行解决,比如使用 ES 等进行解决。

6.9 asc 和 desc 混用
1
2
3
select * from _t where a=1 order by b desc, c asc
-- desc 和 asc 混用时会导致索引失效。

SQL

总之,SQL的优化方法有很多种,可以根据实际情况选择合适的优化方法进行优化、调整,以提高数据库的性能和稳定性,提高系统使用的体验。优化SQL查询是一个持续的过程,需要不断地分析、调整和监控。通过应用上述优化技巧,可以显著提高查询性能并提升数据库的整体吞吐量。然而,需要注意的是,每个数据库和应用场景都有其独特性,因此在实际优化过程中需要根据具体情况灵活应用这些技巧。

  • 26
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQLSQL优化是指当数据库查询执行时间过长,影响系统性能时,对查询语句进行分析和调整的过程,以提高查询效率。以下是一些常见的优化策略: 1. **使用EXPLAIN分析**:使用`EXPLAIN`关键字可以查看SQL执行计划,了解哪些部分导致了查询变,例如索引使用情况、是否全表扫描等。 2. **优化查询语句**: - **避免全表扫描**:尽可能利用索引来减少数据扫描,如确保索引覆盖查询字段。 - **减少子查询**:尽量将复杂的子查询转换为JOIN操作,或使用临时表。 - **避免在WHERE子句中使用函数**:函数会破坏索引,应尽量在SELECT语句中处理。 3. **创建合适的索引**: - 针对频繁查询的列创建索引,尤其是作为WHERE条件的部分。 - 考虑复合索引(多个列组合)以匹配更复杂的查询。 4. **缓存机制**:启用`innodb_buffer_pool_size`来增大缓冲区,加快查询速度。也可以考虑使用`Query Cache`或者第三方缓存工具。 5. **优化表结构**: - 适当分区表,根据业务需求划分数据,减少单次查询的数据量。 - 减少冗余数据,避免存储不必要的重复数据。 6. **定期维护**: - 定期重建或优化索引,如使用`ALTER TABLE ... ANALYZE`。 - 清理无用的数据,保持数据表的整洁。 7. **服务器配置优化**: - 调整`max_connections`以适应并发需求。 - 调整`innodb_flush_log_at_trx_commit`以平衡写入速度和数据安全性。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值