记一次线上sql优化

需求

概况：

每天凌晨从大数据接口拉取数据（都是对昨天数据的统计），每天差不多拉取3000多条数据。

数据库表部分字段：只有主键索引

字段	描述
id	主键
cal_day	统计日期
cpmo_cop	公司编码
cpmo_cop_txt	公司名称
target_name	指标名称
sort	排名

要求：

返回通过公司编码和日期查询，某个公司指定日期的数据和前一天的排名。

最初sql:

        select t.target_name AS targetName,t.target_value as targetValue,t.score,t.sort,t.ysort,mt.target_type as targetType from
        (select t.target_name,t.target_value,t.score,t.sort,ifnull(y.sort,0) as ysort from
      (select target_name,target_value,score,sort from table_nanme
       where
        cal_day = '2023-04-25'
       and cpmo_cop = 123
        
        ) t,(
        select target_name,target_value,score,sort from table_nanme
        where  cal_day = (SELECT DATE_SUB( '2023-04-25',INTERVAL 1 DAY))
       and cpmo_cop = 123
      
        ) y
        where t.target_name=y.target_name ) t LEFT  join security_map_target mt on t.target_name=mt.target_name order by mt.target_type desc

sql执行结果：

ysort为前天排名
当时因为数据量小，所以sql执行返回的速度还是挺快的。
过了半年，数据量达到了50W+，然后sql执行变慢了，因为全表扫描并且两次，接近1s才能查到数据。

第一次优化：

首先想到的是减少全表扫描，把两天的sql都返回，然后到内存中去做比对

select t.target_name AS targetName,t.target_value as targetValue,t.score,t.sort,t.cal_day as calDay, mt.target_type as targetType from
        ( select target_name,target_value,score,sort,date_format(cal_day,'%Y-%m-%d') as cal_day from table_nanme
        where
        cal_day in  ('2023-04-25',( SELECT DATE_SUB( '2023-04-25', INTERVAL 1 DAY )))
       and cpmo_cop = 123   
        ) t LEFT  join security_map_target mt on t.target_name=mt.target_name order by mt.target_type desc

优化结果：

执行时间到了0.5s以内，效率提升一倍，但是我觉得还是慢，主要原因还是数据量太大，全表扫描的效率很低。
ps：这个时候我已经想加索引了（领导没同意），索引的快乐谁用谁知道。

第二次优化：

没有彻底解决的问题，随着时间的推移，会越来越痛苦。所以痛苦的时候来了。
到现在数据量达到了197W，那个sql执行的效率已经无法直视。
于是必须添加索引了，适合添加索引的字段有2个。
cpmo_cop（公司编码）和 cal_day（统计日期）
要考虑到索引所在的字段数据重复率越低越好，这样剩余数据越少，效率越高。

• 1.根据之前知道的，每天会新增3000+的数据，所以用 cal_day（统计日期）作为索引，过滤完数据剩下3000+，不会因为数据的增加而变化。
• 2.如果用cpmo_cop（公司编码）作为索引，指标名称有20个，第一天过滤后的数据为20条，100天后过滤的数据为2000条，并且会随着时间的推移数据量会越来越大。
• 所以，最终选择使用cal_day（统计日期）作为索引。结合第一次优化的sql，sql执行时间为0.1s

如果sql执行慢，同学们可以通过 EXPLAIN 查询一下sql的执行计划，进行sql优化