mysql索引优化案例

字符集不一致导致索引无效

现象

sql

 explain select DATE_FORMAT(c.closed_at, '%Y-%m-%d') as days, 
    (
            select count(DISTINCT m.chat_uid)
            from module_talk_chat_log_metric m FORCE  INDEX (ix_chat_uid)
            where m.chat_uid = c.uid
                and m.`engineer_first_log_uid` 
        )
 as noResponseCount
from module_talk_chat c
where c.provider_id = 1160 
    and c.deleted = false
    and c.status = 'closed'
    and ((c.created_at >= '2022-10-13'
            and c.created_at <= '2022-10-19 23:59:59')
        or (c.closed_at >= '2022-10-13'
            and c.closed_at <= '2022-10-19 23:59:59'))

执行计划

首先我们先看DEPENDENT SUBQUERY原理

参考:https://www.cnblogs.com/LQBlog/p/10723158.html#autoid-5-3-2

知道原理我们看外层结果集数,理论上1000多行遍历子查询走索引也不至于很慢

但是如果没走索引每次遍历子查询都全表扫描性能就会急剧下降

后来通过对比发现2个表的字段字符集不一致

 将module_talk_chat_log_metric表的字符集修改为utf8mb4

/* 请确认以下SQL符合您的变更需求，务必确认无误后再提交执行 */

ALTER TABLE `module_talk_chat_log_metric` 
    AVG_ROW_LENGTH=0,
    MODIFY COLUMN `chat_uid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '会话id' AFTER `provider_id`
;

再看执行计划

原理

首先字符集不一致只是表象,真正的原因是隐式转换导致的索引无效

如果子查询返回的列类型为utf8mb4，父查询后跟着的参数是utf8则会产生隐式类型转换，导致索引失效，反之则不会，原因在于utf8mb4是utf8的超集，兼容前者。

如果module_talk_chat_log_metric是utf8mb4则不会触发查询字段的隐式转换 可以命中索引

尽量带上所有条件

简单描述一下 后面遇到sql再贴 以下只要带上一个表的provider_id 就行，但是尽量都带上 因为给数据库可以根据2个表的条件和索引选择小表做驱动表

 select * from `ticket` t 
 join `ticket_comment` tm on tm.`ticket_id` =t.`id` 
 where tm.`provider_id` =2 and t.`provider_id` =2

大数据量sum\count sql优化

场景:查询服务商的容量是否超限制规则

1个工单=b  1个会话1b   附件则取附件zise

当数据量有几百万的时候，sum性能会很低 查询需要4秒以上 数据越多则耗时越长

explain select sum(a.`size`)  size
from attachment a
where a.provider_id = 17755
    and (a.deleted = 0
        or a.deleted is null)
        

1.优化方案

每天晚上做全量统计并存储记录统计时间

白天获取服务商使用数量则通过全量统计的数值加上全量统计后的数据

select sum(a.`size`)  size
from attachment a
where a.provider_id = 17755
    and (a.deleted = 0
        or a.deleted is null) and created_at>={同步时间}

针对历史数据删除则需要用户手动刷新(重复以上操作 并记录新的同步时间)因为考虑到用户针对移除释放空间 都会回来看空间用量,发现没变一般都会主动点击刷新按钮

方案2

按年或者月 或者日维护一个数据量.当移除或者修改数据则判断移除数据时间找到制定数据量进行+ - 或许就把当天的进行一次全量？;

为什么按 年或者月 或者日来拆分 因为如果只使用一个字段 某个时间段错误则全部错误了。。

埋点可以使用订阅binlog的方式