新项目业务人员反馈说最近订单发放模块经常很卡，导致总是有锁的情况发生，在用慢查询和开启锁监控观察后发现实际上只是单条查询慢造成的阻塞锁，这里考虑先对单条查询做一下优化。
一、优化前的表结构、数据量、SQL、执行计划、执行时间
1. 表结构
A表有90个字段，B表有140个字段。
2. 数据量
- select count(*) from A;
- --166713
- select count(*) from B;
- --220810
3. sql
开启慢查询观察到慢sql如下，单条执行只取200条记录是在23秒左右。
- select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,
- ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1
- from A as ob
- where ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>''
- and ob.if_cost_proof='N'
- and EXISTS (select 1 from B ol where ob.id=ol.order_base) limit 200;
4. 执行计划
思路
这两张表都是订单表，全国各地的每天大概会产生十万行左右，这里又是全扫，等后期达到上千万的数据就GG了。目前只是看到这个sql上的问题，先考虑exists部分做一下改写。
二、exists部分改写
- select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,
- ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1
- from fsl_order_base as ob,fsl_order_base_line ol
- where ob.id=ol.order_base and ob.if_cost_proof='N' and
- ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>'' limit 200;
执行时间：耗时1.8秒
对应的执行计划：
可以看到ob表走了主键索引
业务确认结果符合需求，那就在这基础上建一下索引吧!
三、ol表建索引
- create index idx_obl_id on fsl_order_base_line(order_base);
- create index idx_ob_id_cost on fsl_order_base(id,if_cost_proof);
加上去但实际上用不到这个索引，选择去掉
四、查看执行时间和执行计划
耗时1.1秒，可惜执行计划还是走了全扫，在对ob表建了索引实际上也用不到，最终只在ol表建了索引。
五、考虑用join改写
把ob结果集缩小，然后再做关联查，并测试是否可以用上索引。
- SELECT
- obc.id,
- obc.customer,
- obc.order_no1,
- obc.accountingitems_code,
- obc.insert_date,
- obc.weight,
- obc.volume,
- obc.qty,
- obc.project_code,
- obc.order_no2,
- obc.order_type1
- FROM
- (select * from fsl_order_base AS ob where ob.if_cost_proof = 'N' and ifnull( ob.project_code, '' ) <> '' and ifnull( ob.accountingitems_code, '' ) <> '' ) obc
- join
- fsl_order_base_line ol
- on obc.id = ol.order_base limit 200;
时间快了一点，但不是很明显，先凑合吧
执行计划保持不变。
总结
建索引前因为走了主键索引，所以时间在1.6秒这样，建索引后不走主键索引了，走ol表的索引，所以在1.5秒，然后缩小结果集去查的话就在1s这样。
更重要的是这两个表一个90个字段，一个150个字段，所以这两个表的关联查后期结果集应该还是会很大，建议是弄成分区表的形式，表能拆分的话是最好的。这些长度不要直接给那么大，这么宽对性能都是有影响的。
