风离不摆烂学习日志Day21 SQL语句优化笔记

排查思路

首先查看Sql语句是否走索引

explain + SQL查询语句

# type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下 如果是ALl 或者 Index 则表示没走索引 可根据具体情况查看是否 需要创建索引 或者考虑索引失效的几种情况

索引失效的情况

1、使用 != 或者 <> 导致索引失效
2、类型不一致导致索引失效
3、函数导致索引失效
4、运算符导致索引失效
5、模糊搜索导致索引失效
6、NOT IN、NOT EXISTS导致索引失效，
in 是走索引的
7、IS NULL走索引，IS NOT NULL不走索引

Sql语句优化案例1

优化前：

explain
select pg.channel_group_id
from PromotionChannelGroupChannel as pg
where pg.channel_group_id in (select pcg.group_id
                              from PromotionChannelGroup as pcg
                              where pcg.parent_group_id = ?
                                and pcg.company_id = ?
                                and pcg.state = 1)
   or pg.channel_group_id = ?;

在 in 里使用了范围查找 导致走了 index全表扫描 可修改为以下的Sql语句

explain
select pg.channel_group_id
from PromotionChannelGroupChannel as pg
         left join PromotionChannelGroup as pcg on pg.channel_group_id = pcg.group_id
where (pcg.parent_group_id = ?
   or pg.channel_group_id = ?)
  and pcg.company_id = ?
  and pcg.state = 1;

Sql语句优化案例2

优化前：

explain SELECT pc.channel_name                       AS channelName,
       pc.channel_id                         AS channelId,
       count(company_account_id)             AS pv,
       count(DISTINCT pr.company_account_id) AS uv,
       pc.channel_key                        AS utmSource
from PageRecord AS pr
         INNER JOIN PromotionChannel AS pc ON pc.company_id = ? AND pr.utm_referer = pc.channel_key AND pc.source = 3
WHERE pr.company_id = ?
  AND pc.state = 1
  AND pr.data_time between ? AND ?
  AND pr.utm_referer != ''
  AND pr.state = 1
GROUP BY pc.channel_id
ORDER BY pv DESC, uv DESC;

当无法使用索引的时候，group by 使用两种策略来完成：临时表或者文件排序。 上面的sql语句就是使用了临时表和文件排序image-20230210170656100

优化后

explain select * from (SELECT pc.channel_name                       AS channelName,
               pc.channel_id                         AS channelId,
               count(company_account_id)             AS pv,
               count(DISTINCT pr.company_account_id) AS uv,
               pc.channel_key                        AS utmSource
        from PageRecord AS pr
                 INNER JOIN PromotionChannel AS pc ON pc.company_id = 83 AND pr.utm_referer = pc.channel_key AND pc.source = 3
        WHERE pr.company_id = 83
          AND pc.state = 1
          AND pr.data_time between '2023-02-04 00:00:00' AND '2023-02-10 23:59:59'
          AND pr.utm_referer != ''
          AND pr.state = 1

        ORDER BY pv DESC, uv DESC) as temp group by channelId;

解决方案 查询做为整体但是不分组 当做临时表 然后在临时表外根据表中的字段分组

PS 额外的情况

比如重复调用 调用10次这个Sql语句 可能单次的查询时长为 0.2s 但是 多次累加就会很长时间了 解决办法是从代码层面修改逻辑 或者加缓存