mysql索引--从一次慢查询优化说起

一条慢查询日志

 

mysql的索引是一把双刃剑，如果使用得当，会给系统带来极大的性能提升；相反的如果使用不当，则可能会带来灾难性的后果。最可怕的是前期很难发现，随着数据量的增加以及业务高峰期的临近，问题才会突然暴露出来。

 

本周我所负责的一个系统就出来了类似的问题，庆幸的是处理及时，没有造成灾难性的后果。今天抽时间把事情的经过记录下来，在未来的时间里鞭策自己：对新成员必须进行sql语句基础规范的学习，并对每个人每次上线的sql语句必须进行code review。

 

这个系统是一个新业务，并且也已经上线正常运行了一段时间。随着618大促的临近业务量增加，问题才暴露出来。本周三晚上加班，突然收到DBA信息说我们mysql所在的服务器cpu利用率在近20分钟内急剧攀升，快到90%，让我们排查是不是我们的应用导致的（该mysql服务器上有多个应用对应的多个数据库）。事发突然，立即向DBA索要慢查询日志进行分析，日志如下（已屏蔽业务信息）：

 

从慢查询日志中可以看到，最消耗性能的语句是“SELECT xxx_pc_act_profile”，该语句在26分钟内（Time range: 2017-05-31 20:20:02 to 20:46:04），执行7618次，平均每次大约113ms，已经到了无法容忍的地步。并且不幸的是，这张表确实是属于我们系统。

 

解决问题步骤

 

第一步：停服务

由于该mysql服务器中还有其他应用数据库，为了防止影响其他业务，第一步就是立即决定停掉我们这个子系统服务（权衡影响范围）。再次观察msyql服务器情况，cpu使用率恢复正常，进一步说明确实是由于该业务引起。

 

第二步：初步确定索引问题

分析这个出问题的语句，是一个select语句：

      SELECT

        xxx,xxx,xxx,xxx

        FROM   xxx_pc_act_profile

         where 

          and start_time <= '2017-05-31 20:30:00'

 and end_time >= '2017-05-31 20:30:00'

 and valid_flag = 1

          and status = 1

          and brandIds = '94924'

        order by weight desc desc

 

可以看到这里的where语句里有很多查询条件，还有order by语句，由于select语句导致的性能问题，可以99%的确定是索引设置不当引起的。

 

第三步：分析执行计划，以及索引命中情况

查看执行计划：explain  select xxx from xxx_pc_act_profile where xxx;

发现查询命中索引'idx_status'，看起来像是在一个状态字段上建了索引。进一步确认，证实status字段是一个状态字段（0-正常，1-下线）。

至此定位到问题原因：错误的在“低基数列创建索引”。

 

第四步：查看表索引的创建明细

CREATE TABLE `xxx_pc_act_profile` (

  --省略字段

  PRIMARY KEY (`id`),

  KEY `idx_url` (`url`),

  KEY `idx_third_cate` (`third_cate`),

  KEY `idx_start_time` (`start_time`),

  KEY `idx_end_time` (`end_time`),

  KEY `idx_status` (`status`),

  KEY `idx_valid_flag` (`valid_flag`),

  KEY `idx_pre_cate_level` (`pre_cate_level`),

  KEY `idx_confirm_flag` (`confirm_flag`),

  KEY `idx_last_publish_date` (`last_publish_date`),

  KEY `idx_valid_query` (`start_time`,`end_time`,`status`,`valid_flag`)

) ENGINE=InnoDB  COMMENT='xxx活动画像表'

 

看到这里惊呆了，这都创建了些什么索引。初步列举问题：

1、索引创建太多（普通索引是B-TREE，需要单独的存储空间）。

2、对低基数列创建索引，如：status、valid_flag 等。

3、对字符串类型创建索引，如:third_cate等。

4、对无用字段创建索引：url，这个索引根本就没用。

也行还会发现其他很多的问题。

 

到这里突然发现自己应该负很大的责任：对新同事的培养，平时都只是停留在java coding上，尤其是现在大部分业务都有redis缓存挡在上一层，对sql的基础规范没有组织学习，上线前的code review也没有覆盖到sql。

 

msyql创建索引的基本原则

 

通过这个反面教材，快速的总结了在创建索时的注意事项（基本原则）：

1、不要在低基数列创建索引。浪费索引存储空间，并且不会提高查询效率。

2、尽量不要在经常被修改的字段上建索引，会增加插入的成本，以及提高死锁发生的概率。例如本示例不会在weight字段加索引

3、删除冗余索引，没有用到的索引必须全部删除，避免不必要的空间浪费。本示例中url 索引是无用的。

4、不要创建太多的索引，因为在插入数据时，索引也需要插入。索引太多会导致插入性能下降。本示例优化后 只剩两个索引。

5、不要在非null列创建索引，如果值为null时，建议替换成1或-1等常量。本示例start_time、end_time两个字段优化为非空。

6、如果查询是多条件，不要为每个条件字段创建索引，而是创建复合索引，因为mysql只用使用1个索引。

7、创建复合索引，注意左匹配原则，尽量考虑重用性。比如创建复合索引index(a、b、c),相当于同时创建了index(a) index(a、b) index(a、b、c)。

8、创建复合索引，需要注意把区分度最大的放到最前面（如果与第6点冲突，需要自己根据业务平衡下）。

 

通过查找资料，还有其他几点：

 

9、主键最好使用自增型，保证数据连续性（mysql innodb 主键默认采用b+tree，索引和数据放在同一个btree中），不要使用uuid、hash、md5等

10、少使用外键，会导致两张表数据变更时相互影响。尽量通过业务实现。

11、不要使用前匹配的like查询，会导致索引失效。可以使用后匹配like，如"xxx%"。

12、在字符串列上创建索引，尽量使用前缀索引。前缀基数根据具体业务，在匹配度和存储量（索引的存储量）之前做一个平衡。

13、不要使用 not in\like，会导致索引失效。not in可以用not exists替换。in和or所在列最好有索引

 

（ps：普通java开发，非dba总结，不全的地方，还望有DBA大神补充下）。

 

本次事故示例 按照上面的原则对索引进行优化：最终去掉了以前的所有索引，根据具体业务，只新建了两个复合索引（其他查询都可以重用复合索引中的部分）。

（ps：实际修复步骤：新建一张字段信息相同的表，并创建新的索引，再把老表中的数据同步到新表）

 

至此 该问题解决，期间丢失部分业务数据，但庆幸的是该系统是一个外围系统，损失还在可控范围内。

 

新问题 Duplicate PRIMARY

 

在解决上述索引问题的过程中，我始终觉得这张表的主键创建方式会导致问题。根据上述主键的创建原则：“主键最好使用自增型”，但上述表的主键不满足该规则：

  PRIMARY KEY (`id`),

 

在问题修复后，我们持续的对日志进行不定期的检查，果然又有新的发现，日志中偶尔会报错：

Duplicate entry 'xxx' for key 'PRIMARY'

 

问题很明显，就是数据在插入时，发现改主键id ‘xxx’已经存在，报主键重复写入冲突错误。立即提取代码分析问题，这里的主键id是另外一张表X的主键，根据业务查询X表，对满足条件的记录进行加工后插入该新表。其代码逻辑如下：

 

------开启spring 事务 省略代码 -------

xxxPcActProfile oldInfo = xxxDao.getById(newInfo.getId);//先查询该主键id对应的记录是否存在

if (oldInfo == null){

     xxxDao.insert(newInfo);//如果不存在，就插入

}else{

      xxxDao.update(newInfo);//如果已存在，就修改

}

------提交spring 事务 省略代码 -------

 

初步看该代码没有问题啊，在一个事务里面，怎么会出现插入时“主键冲突”呢。

其实不要被事务所欺骗，我们来分析下在高并发情况下，两个相同id的插入请求：

 

                     事务1                                           事务2

1、        判断id：123是否存在                  判断id：123是否存在

2、       判断结果：不存在，进行插入       判断结果：不存在，进行插入    

3、         插入id为123的记录                     等待

4、         插入完成                                     插入失败，id：123已存在

5、         关闭事务                                      关闭事务

 

由于mysql的插入是不可分割的指令是原子性的，必须得等待其中一条插入完成后，另一条才能插入。这就导致了上述“主键冲突”异常的发生。   

 

该问题导致的后果：在发现已经存在后，应该执行修改，但冲突后直接抛出异常，修改操作没有被执行，导致修改数据丢失。该问题在高并发的情况下，还会经常出现。

 

最终的解决办法：采用 insert duplicate update语句，问题得到解决，语法如下：

 

INSERT INTO table (xx,xx,xx) VALUES (xx,xx,xx) ON DUPLICATE KEY UPDATE ....

 

至此问题得以解决。但仍心有余悸，加强sql编写规范学习，并对所有sql进行code review势在必行。

 

 

 

评论

1 楼 果粒儿我是 2017-10-17  

分析的很仔细，受益良多！