SQL查询优化策略：应对复杂关联与条件,-优快云博客

先看下sql

 explain select distinct zc.*,zp.id zpid,saved_path,zf.id zfid,pac.comp_name,retailing_name,scale_name,country_name,pp.person_name,
        pac.full_name as market_name,ztp.place_name,
        concat(pac.full_name,'_',zc.company_id) as market,
        concat(retailing_name,'_',ztr.retailing_code) as retailing_form1,
        concat(scale_name,'_',zcs.scale_code) as scale,
        concat(country_name,'_',zcc.country_code) as country,
        case when cust_state=1 then '营业中' else '停业中' end as cust_state_cn,
        case when cust_state=1 then concat('营业中','_',1) else concat('停业中','_',0) end as state,
        concat(ztp.place_name,'_',ztp.place_code) as place,
        concat(psa1.full_name,'_',psa1.id) as province,
        concat(psa2.full_name,'_',psa2.id) as city,
        concat(psa3.full_name,'_',psa3.id) as district,
        pp.mobile_tel
        from
        zd_customer zc
        LEFT JOIN zd_customer_picture zp on zc.id = zp.cust_id
        LEFT JOIN zd_upload_file zf on zp.picture_id = zf.id
        left join pub_all_company pac on zc.company_id = pac.id
        LEFT JOIN zd_type_retailingform ztr on ztr.retailing_code = zc.retailing_code
        left join zd_customer_scale zcs on zc.scale_code = zcs.scale_code
        left join zd_customer_country_category zcc on zc.country_code = zcc.country_code
        left join zd_rel_person_customer arpc on arpc.customer_id=zc.id
        left join pub_person pp on pp.id=arpc.person_id
        left join pub_person_company ppc on zc.company_id=ppc.company_id
        left join zd_type_place ztp on zc.place_type=ztp.place_code
        left join pub_state_area psa1 on psa1.id=zc.province_code
        left join pub_state_area psa2 on psa2.id=zc.city_code
        left join pub_state_area psa3 on psa3.id=zc.district_code
        where zc.del_flag = 0 and (artificial_person like '%${param.name}%' or cust_name like '%${param.name}%' or
        cust_code like '%${param.name}%' or pp.person_name like '%${param.name}%')
         and zc.company_id in (select company_id from pub_person_company where person_id=100001)
         and (zc.cust_state=1 or zc.cust_state=0 or zc.cust_state=2)
        order by zc.province_code,zc.city_code,zc.district_code

这个是一个树形结构的表关系，主表为zd_customer,其他表为附属表。

一般我们会使用explain来查看sql的执行计划，explain中的列属性我们要先了解一下，这里直接放下我参考的博文：慢Sql语句优化思路_慢sql优化思路_云计算&大数据的博客-优快云博客

explain format=JSON、TREE（显示格式） -分析执行计划的工具执行之后还可以使用show warnings查看优化器执行细节 json能显示出每一步的成本
table字段：每一行记录都对应着一个单表（explain结果有几行记录就有（用了）几个table）
id字段：每一个select关键字对应一个唯一的id（标记你的查询是第几号，同一个id是同一个查询）
select type字段：查询类型，是什么查询（子查询，相关子查询，联合查询）
partition：分区
type字段：访问单表的方式（获取数据的方式） system直接从系统那 const常量 ref结果等值比较 ……
possiblekey：可能使用的索引
key：实际使用的索引
key_len：检查是否充分利用了索引（值越大越好，因为全用上的话的到的结果就少）单位：字节-b+树中每个列都是字节存储的，keylen能看到我们用了索引从前到后的哪个部分（从前往后数多少字节）
ref字段：等值比较的对象的信息
rows：可能取得记录数
filtered字段：经过过滤后剩余记录的条目的百分比。（时间用上rows中记录的百分比）
Extra：额外信息，准确告诉我们如何执行sql

有了思路再回头看下我们这个sql的思路，zc.*由于业务需要肯定是需要全表查询了，关联的表非常的得多，但是大部分都是字典表，一般都会走逐渐索引，字典表记录较少的也会走全表扫描，但是里面也只有俩三条记录(mysql会自动帮我们做一些优化，对记录比较少的表加索引也只会浪费空间)，事实上看执行计划也确实是如此。关联条件一般也都是通过逐渐id关联。

那我们只能从where条件上下手了

先去zc的表上查考del_flag字段的类型，隐式转化也会降低查询效率甚至会使索引失效。发现del_flag为varchar类型，所以修改为zc.del_flag = '0'.

人员姓名这个查询条件(artificial_person like '%${param.name}%' or cust_name like '%${param.name}%' or cust_code like '%${param.name}%' or pp.person_name like '%${param.name}%') 在左侧使用了%，会使索引失效，不符合最左前缀原则。但是业务可能就是这样设计的所以这个也不能改。

接下来就是这个zc.company_id in (select company_id from pub_person_company where person_id=100001) 子查询了，这个子查询真的是查询慢的罪魁祸首，使用了临时表和文件排序，而且这个子查询(他是一个关系表)会返回5000多条数据，id in 5000多条也就是说主查询每查询出一条就要去5000多条中匹配一次，这个效率肯定就下降了。修改思路：尽量要转换成多表查询（因为子查询会产生临时表，消耗资源，临时表还无法使用索引）。我发现pub_person_company这个表在上面其实已经join了，所以说可以把子查询删掉使用where条件。

修改后and  ppc.person_id =#{personId}

and (zc.cust_state=1 or zc.cust_state=0 or zc.cust_state=2)这个条件就没有什么优化了，因为它属于一个状态字段，重复数据过多，不适合建立索引。但是联合索引的话其实也可以考虑。

order by zc.province_code,zc.city_code,zc.district_code 由于之前的表上已经有太多索引，索引也会占用存储空间，order by一般也不会作为主要优化索引字段。

这此优化是查询速度提升了一倍(查不出来->10700mx)查不出来的情况是这个person_id负责了太多的company_id，还是很有成就感的，毕竟是第一次优化，我觉得应该还有很多可以优化的地方(在索引方面),不过时间有点紧就先这样先让服务正常走起来，原来业务超时崩溃了。。。

以后有时间在进一步优化后再来更新——————————————————

2023年11月22日，回过头来发现这个sql真的有点烂(函数用的太多-主要用来拼接了`字典`数据，有没有其他更好的方法做字典相关呢 TODO 待研究)

---------------------------------------------------------------------------------------------------------

字典表解决方案，前端缓存字典相关信息，在前端处理显示数据。至于这个sql，真的是集合了各种sql的不好于一身(函数，字段数，关联表数，模糊查询，不必要的where条件)

记一次sql优化