一次慢SQL优化

问题

查询语句

SELECT `type`,COUNT(*) as num FROM th_mkt_push_message_box_? where user_id = '?' AND is_read = ? GROUP BY `type`;

慢SQL分布情况

可以看出慢SQL基本集中在中午12点与下午2点。

分析

Step 1 影响范围

  根据影响范围确定问题等级，制定修改方案与排期。该慢SQL为首页接口，QPS100+，慢SQL出现集中在12点和16点左右频率2s一次，平均耗时100ms，暂时未对业务产生影响。

Step 2 缓存问题

  因为C端场景对RT时间比较敏感，所以在分析C端的慢SQL时需要先确定该场景下有没有使用缓存来提高访问速度。
  该场景下使用Codis缓存了用户未读消息数量，默认过期时间为7天，

消息插入

• num == 0，从数据库同步缓存数据
• num > 0，缓存num原子自增

接口查询

• num == 0，从数据库同步缓存数据
• num > 0，直接返回缓存数据

  用户的未读消息数量通常是不为0的，因此缓存可以处理大部分的请求数据。但是仍有一小部分流量会击穿缓存访问到数据库，如果数据库这部分用户的未读消息也为0，就会造成缓存穿透。
  另外，消息插入缓存数量自增时并没有更新缓存时间，所以慢SQL集中出现在下午12点与16点左右，实际上这个时间段是插入的流量高峰期，随着部分缓存过期，造成了缓存雪崩。

Step 3 数据量

  目前消息数据量保持在4亿左右，每日插入量200万左右，每日凌晨执行过期计划过期200万左右数据。数据存储使用分表形式，分了64张表，每张表数据量在600万左右，不会影响索引查询。

Step 4 SQL语句

首先Explain一下慢SQL语句

主要看下type（查询类型），key（使用索引），rows（扫描行数）和Extra属性。
这条查询走了索引，使用index索引（userId），因为索引信息未覆盖查询结果导致了回表查询。

小结

缓存失效问题

   使用缓存要注意的问题

• 缓存的失效策略
• 缓存的同步策略

消息插入:当缓存不为0时,自增时需要更新消息过期时间。
接口查询:缓存为0时，可以不走同步策略，因为只有用户一周内无消息，或者刚清空消息时缓存消息可能为0。

SQL优化问题

 user_id = '?' AND is_read = ? GROUP BY `type`;

  根据上面SQL语句应该建立联合索引(user_id,is_read,type)，来确保查询时不需要再次过滤条件，返回时不需要回表查询。

• Using index； 索引覆盖
• Using index；Using where；索引覆盖且过滤结果
• Using index Condition；索引未全覆盖，筛选条件为前导列
• Using where； 索引未覆盖，筛选条件非索引

反思

  传统的OLTP系统，在设计阶段就会考虑到数据量和访问量的问题。系统上线后，在系统没有bug的情况下依靠SQL优化很难解决慢SQL问题，因此SQL优化的优先级并不高，如果上述步骤依然没办法解决问题，只能考虑动态扩展数据库资源。
  在需要分表的场景下，要在设计阶段把查询的场景的索引确定好，不然项目上线以后加索引就变成了一个十分繁琐的操作。