mysql索引优化案例

文章主目录

• 字符集不一致导致索引无效
• 尽量带上所有条件
• 大数据量sum\count sql优化
• Using join buffer (Block Nested Loop)
• mysql搜索时快时慢问题 
• 避免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则不会触发查询字段的隐式转换 可以命中索引

回到顶部

尽量带上所有条件

注意:在我们知道哪个表是小表的条件则不用这样做 参考:https://www.cnblogs.com/LQBlog/p/16807067.html#autoid-5-0-0

简单描述一下 后面遇到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的方式

回到顶部

Using join buffer (Block Nested Loop)

现象测试反馈某个接口耗时44秒

 

定位到异常sql

 select u.created_at, u.last_login_at, u.mobile_phone, u.contact_email, u.name
    , u.nickname, u.external_id, u.id, u.contact_mobile_phone, u.email
    , u.updated_at, u.provider_id, ue.name_pinyin, ue.nickname_pinyin
from user u
    left join user_extra ue on ue.user_id = u.id
where u.provider_id = 141  
    and (u.deleted = 0
        or u.deleted is null)
    and (u.suspended = 0
        or u.suspended is null)
    and u.type = 'customer'
    and u.valid = 1
order by u.`created_at`  desc
limit 18

分析执行计划

 

 

 问题原因

参考:MySql优化- join匹配原理(一)

驱动表为u，我们将上面的sql拆解出来 查询结果为15万条  15万去对象嵌套查询非驱动表ue每次都进行了全表扫描

select
  count(1)
from
  user u
where
  u.provider_id = 141
  and (
    u.deleted = 0
    or u.deleted is null
  )
  and (
    u.suspended = 0
    or u.suspended is null
  )
  and u.type = 'customer'
  and u.valid = 1

看看ue的索引 是有个组合索引的 正常带上ue的provider

再看执行计划

回到顶部

mysql搜索时快时慢问题 

SELECT u.created_at, u.last_login_at, u.mobile_phone, u.phone, u.name
    , u.nickname, u.external_id, u.id, u.email, u.provider_id
    , ue.name_pinyin, ue.nickname_pinyin
FROM user u
    LEFT JOIN user_extra ue
    ON ue.user_id = u.id
        AND ue.provider_id = 7899
WHERE u.provider_id = 7899
    AND (u.deleted = 0
        OR u.deleted IS NULL)
    AND (u.suspended = 0
        OR u.suspended IS NULL)
    AND u.type = 'customer'
    AND u.valid = '1'
    AND (u.email LIKE '%11%'
        OR u.`name` LIKE '%11%'
        OR ue.`name_pinyin` LIKE '%11%'
        OR u.mobile_phone LIKE '%11%'
        OR u.nickname LIKE '%11%'
        OR ue.`nickname_pinyin` LIKE '%11%'
        OR u.phone LIKE '%11%'
        OR u.contact_email LIKE '%11%'
        OR u.contact_mobile_phone LIKE '%11%'
        OR u.external_id = '11'
        OR u.id IN (
            SELECT urt.user_id
            FROM user_r_tag urt
                LEFT JOIN tag t ON t.id = urt.tag_id
            WHERE t.name LIKE '%11%'
                AND t.provider_id = 7899
        ))
ORDER BY u.id DESC
LIMIT 9

执行计划

 

 但是实际搜索只要了25毫秒

 

换一个搜索条件

 SELECT u.created_at, u.last_login_at, u.mobile_phone, u.phone, u.name
    , u.nickname, u.external_id, u.id, u.email, u.provider_id
    , ue.name_pinyin, ue.nickname_pinyin
FROM user u
    LEFT JOIN user_extra ue
    ON ue.user_id = u.id
        AND ue.provider_id = 7899
WHERE u.provider_id = 7899
    AND (u.deleted = 0
        OR u.deleted IS NULL)
    AND (u.suspended = 0
        OR u.suspended IS NULL)
    AND u.type = 'customer'
    AND u.valid = '1'
    AND (u.email LIKE '%13128273410%'
        OR u.`name` LIKE '%13128273410%'
        OR ue.`name_pinyin` LIKE '%13128273410%'
        OR u.mobile_phone LIKE '%13128273410%'
        OR u.nickname LIKE '%13128273410%'
        OR ue.`nickname_pinyin` LIKE '%13128273410%'
        OR u.phone LIKE '%13128273410%'
        OR u.contact_email LIKE '%13128273410%'
        OR u.contact_mobile_phone LIKE '%13128273410%'
        OR u.external_id = '11'
        OR u.id IN (
            SELECT urt.user_id
            FROM user_r_tag urt
                LEFT JOIN tag t ON t.id = urt.tag_id
            WHERE t.name LIKE '%13128273410%'
                AND t.provider_id = 7899
        ))
ORDER BY u.id DESC
LIMIT 9

同样了执行计划耗时6秒

原理，因为limit9 搜索的条件在全表扫描前几百行或者几千行就找到满足条件的数据就直接返回了。避免了全表扫描

如果恰好搜索一个不存在的数据 或者结果不足9条的 还是会触发全表扫描

 

或者这里改用count 就会100%全表扫描，因为要扫描完后才能计算出数据量

回到顶部

避免mysql错误的选择驱动表

需求是查询指定时间范围内有些工单日志的工单

 

SQL

SELECT
  COUNT(DISTINCT tl.ticket_id)
FROM
  ticket_log tl
  LEFT JOIN ticket t ON t.id = tl.ticket_id
WHERE
  tl.provider_id = 8124
  AND t.provider_id = 8124
  AND (
    t.destroyed = 0
    OR t.destroyed IS NULL
  )
  AND tl.created_at >= '2022-10-27 09:44:00'
  AND tl.created_at < '2023-07-07 10:45:00'

 

线上CPU告警,定位到这个sql执行要19秒

排查

1.先看执行计划，看执行计划没什么问题，但是确实是耗时10多秒

从执行计划上看是走得provider_id索引其他2个字段区分度不高，晒出的数据99%都是完整的provider_id数据

t表作为驱动表整个服务商所有数据去join嵌套查询筛选确实很慢,

2.看看ticket_log索引情况

 不走created_at因为一个工单又几十条log数据很大，所以mysql选择t表作为驱动表

3.先产生工单再产生log工单也加个创建时间筛选呢

发现还是没有走我们期望的索引，这里是mysql left join +or会影响mysql的选择

4.有时候尽量带上更多的条件可以让mysql更好的选择索引，有时候mysql业务错误的选择索引如果我们能确定驱动表则不要带上不必要的条件

去掉t.provider_id后

 优化后 10秒 到3秒。3秒时确实数据量很大无法优化了

误导

刚开始我想在t表也加上时间，我想着工单是先有工单再有日志，结果理解错误了。发现2017年的单子还在写日志。因为客户配置了自动化程序批量打标签。如果这样改就数据少了很多

select   t.ti   from `ticket` t 
 join ticket_log tl on t.id=tl.`ticket_id` 
where tl.provider_id = 8124
  AND t.`provider_id` = 8124
  AND (
    t.destroyed = 0
    OR t.destroyed IS NULL
  )
  AND tl.created_at >= '2022-10-27 09:44:00'
  AND tl.created_at < '2023-07-07 10:45:00'
  AND t.created_at >= '2022-10-27 09:44:00'
  AND t.created_at < '2017-02-30 11:36:15'

 

总结

如果我们能确定哪个表适合做驱动表，则多个表重复的条件不带则尽量不带