记一次生产数据库sql优化案例--23秒优化到0.9秒

数据库 MySQL
新项目业务人员反馈说最近订单发放模块经常很卡,导致总是有锁的情况发生,在用慢查询和开启锁监控观察后发现实际上只是单条查询慢造成的阻塞锁,这里考虑先对单条查询做一下优化。

新项目业务人员反馈说最近订单发放模块经常很卡,导致总是有锁的情况发生,在用慢查询和开启锁监控观察后发现实际上只是单条查询慢造成的阻塞锁,这里考虑先对单条查询做一下优化。

一、优化前的表结构、数据量、SQL、执行计划、执行时间

1. 表结构

A表有90个字段,B表有140个字段。

记一次生产数据库sql优化案例--23秒优化到0.9秒

2. 数据量

  1. select count(*) from A; 
  2. --166713 
  3. select count(*) from B; 
  4. --220810 

3. sql

开启慢查询观察到慢sql如下,单条执行只取200条记录是在23秒左右。

  1. select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,  
  2. ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1  
  3.  from A as ob  
  4. where ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>''  
  5. and ob.if_cost_proof='N'  
  6. and EXISTS (select 1 from B ol where ob.id=ol.order_base) limit 200; 

记一次生产数据库sql优化案例--23秒优化到0.9秒

记一次生产数据库sql优化案例--23秒优化到0.9秒

4. 执行计划

记一次生产数据库sql优化案例--23秒优化到0.9秒

思路

这两张表都是订单表,全国各地的每天大概会产生十万行左右,这里又是全扫,等后期达到上千万的数据就GG了。目前只是看到这个sql上的问题,先考虑exists部分做一下改写。

二、exists部分改写

  1. select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,  
  2. ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1  
  3.  from fsl_order_base as ob,fsl_order_base_line ol 
  4. where ob.id=ol.order_base and ob.if_cost_proof='N' and 
  5. ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>'' limit 200; 

执行时间:耗时1.8秒

记一次生产数据库sql优化案例--23秒优化到0.9秒

对应的执行计划:

可以看到ob表走了主键索引

记一次生产数据库sql优化案例--23秒优化到0.9秒

业务确认结果符合需求,那就在这基础上建一下索引吧!

三、ol表建索引

  1. create index idx_obl_id on fsl_order_base_line(order_base); 
  2. create index idx_ob_id_cost on fsl_order_base(id,if_cost_proof); 

加上去但实际上用不到这个索引,选择去掉

记一次生产数据库sql优化案例--23秒优化到0.9秒

四、查看执行时间和执行计划

耗时1.1秒,可惜执行计划还是走了全扫,在对ob表建了索引实际上也用不到,最终只在ol表建了索引。

记一次生产数据库sql优化案例--23秒优化到0.9秒

记一次生产数据库sql优化案例--23秒优化到0.9秒

五、考虑用join改写

把ob结果集缩小,然后再做关联查,并测试是否可以用上索引。

  1. SELECT 
  2.  obc.id, 
  3.  obc.customer, 
  4.  obc.order_no1, 
  5.  obc.accountingitems_code, 
  6.  obc.insert_date, 
  7.  obc.weight, 
  8.  obc.volume, 
  9.  obc.qty, 
  10.  obc.project_code, 
  11.  obc.order_no2, 
  12.  obc.order_type1  
  13. FROM 
  14.  (select * from fsl_order_base AS ob where ob.if_cost_proof = 'N' and ifnull( ob.project_code, '' ) <> '' and ifnull( ob.accountingitems_code, '' ) <> '' ) obc 
  15.  join 
  16.  fsl_order_base_line ol  
  17.  on obc.id = ol.order_base limit 200; 

时间快了一点,但不是很明显,先凑合吧

记一次生产数据库sql优化案例--23秒优化到0.9秒

执行计划保持不变。

记一次生产数据库sql优化案例--23秒优化到0.9秒

总结

建索引前因为走了主键索引,所以时间在1.6秒这样,建索引后不走主键索引了,走ol表的索引,所以在1.5秒,然后缩小结果集去查的话就在1s这样。

更重要的是这两个表一个90个字段,一个150个字段,所以这两个表的关联查后期结果集应该还是会很大,建议是弄成分区表的形式,表能拆分的话是最好的。这些长度不要直接给那么大,这么宽对性能都是有影响的。

记一次生产数据库sql优化案例--23秒优化到0.9秒

责任编辑:赵宁宁 来源: 今日头条
相关推荐

2019-08-19 01:34:38

数据库SQL数据库优化

2021-08-26 22:26:55

性能优化技术

2019-12-16 07:18:42

数据库SQL代码

2018-12-06 16:25:39

数据库服务器线程池

2019-11-18 13:42:55

MySQL数据库迁移

2019-11-22 08:05:01

数据库mysql分区

2019-01-21 11:17:13

CPU优化定位

2019-12-12 10:38:10

mysql数据库nnodb

2019-08-15 11:30:06

SQL数据库ASH

2021-10-14 10:53:20

数据库查询超时

2019-07-25 08:30:58

数据库服务器故障

2019-09-05 09:17:37

MySQL数据库线程

2019-09-08 17:52:10

数据库log file sy等待事件

2019-12-02 08:09:57

境数据库连接超时自动回收

2021-03-01 06:14:50

环境高并发延迟

2019-04-04 15:00:40

SQL索引数据库

2023-11-29 12:12:24

Oceanbase数据库

2018-07-18 15:37:24

数据库DB2故障处理

2021-07-30 07:28:16

SQL优化日志

2020-09-25 07:57:42

生产事故系统
点赞
收藏

51CTO技术栈公众号