一条MySQL慢SQL优化（Join优化）

查询指定区划下的所有有效供应商

1 慢SQL及背景

ag_protocol_qualification 850万数据，ag_protocol35万数据

explain select protocol_id as id , supplier_code from ag_protocol_qualification t1 where exists (select t2.id from ag_protocol t2 where t2.instance_code = 'HNDZMC' and t2.is_deleted = 0 and t2.bid_state = 4 and t2.state = 1 and t1.protocol_id = t2.id ) and t1.is_deleted = 0 and t1.state = 2 and t1.deliver_id = '439900';

2 问题

先看执行计划

从执行计划可得大概的执行过程：

• 步骤一，ag_protocol_qualification表，从索引idx_qualification_deliver_id_state_source上查询满足条件的id（匹配deliver_id、state），然后回表查询（主键索引），判断is_deleted是否满足，然后将满足的数据的protocol_id及supplier_code放入join_buffer
• 步骤二，接下来是一个依赖子查询，从join_buffer中取出一条数据的protocol_id，走ag_protocol的主键索引，找到数据并判断是否匹配
• 步骤三，上述两个步骤依次执行，一次一条数据，ag_protocol_qualification表索引查询20580次、回表查询20580次；ag_protocol主键索引查询20580次（实际可能会少一点，步骤一回ag_protocol_qualification表时可能会遇到is_deleted不满足的数据）

3推荐方案

查询语句改写

explain select t2.id, t2.supplier_code from db_agreement.ag_protocol t2 left join db_agreement.ag_protocol_qualification t1 on t2.id = t1.protocol_id where t2.instance_code = 'ZJWC' and t2.is_deleted = 0 and t2.bid_state = 4 and t2.state = 1 and t1.instance_code = 'ZJWC' and t1.deliver_id = '339900' and t1.state = 2 and t1.is_deleted = 0;

针对步骤一，建立instance_code+deliver_id+state+is_deleted+protocol_id的联合索引，利用覆盖索引可以避免回表，减少2万多次的查询（因为ag_protocol_qualification表中也有instance_code字段，所以新的索引中添加该字段也可以适当的减少子查询中ag_protocol表的查询量），这是修改后的执行计划，可以看出Extra列中有Using index，表明覆盖索引起作用了

总结

• 对于数据量小的查询，新增这条索引意义不大，尤其是protocol_id这个字段其实区分度很高，已有单列索引，这里完全是用空间换查询时间。

期待更好的解决方案。。。