数据库内核月报 － 2015 / 10-MySQL · 答疑解惑 · 索引过滤性太差引起CPU飙高分析

在操作数据库系统的时候，有个常识就是在建表的时候一定要建索引。为什么要建索引呢？

这里以mysql的innodb存储引擎为例，因为innodb会以索引的排序为基准建立b+树，这样在检索数据的时候就可以通过b+树来查找，查找算法的时间复杂度是o(logn)级别的，避免全表扫描带来的性能下降和额外资源损耗。

理论上一个表所有的字段都可以建索引，那么给哪些字段建索引效果好呢？

一个想法是给频繁在sql的where条件中出现的字段建立索引，这样可以保证通过索引来查找数据。

有一点是经常被忽略的，那就是索引的过滤性。比如我们给一个整型字段加索引，而这个字段在几乎所有的记录上的值都是1（过滤性很差），那么我们通过这个索引来查找数据就会遍历大部分无关记录，造成浪费。

我们知道update语句也是通过索引来查找待更新的数据的，而且update会给索引查找的记录加上x锁，因此索引过滤性不好不但造成性能下降，还有可能造成锁争夺和锁等待的损耗。

下面给出一个具体的因为索引过滤性太差引起cpu飙高的case，在rds的线上实例曾出现过类似的case。

在mysql里我们建立这样一个表：

然后我们给sbtest1加点数据，并且让索引k_1(k)的过滤性不好，表内一共10000000条数据，索引k只有2个值50,51，如下所示：

然后我们用sysbench开32个并发的update，update语句如下：

执行show full processlist\g，可以看到这些update的状态大多处于”searching rows for update”的状态。

“searching rows for update”即mysql正在寻找待更新的记录的状态，正常情况这个状态是非常快就结束的，但是这里却长时间处于这个状态，为什么呢？

由于表的索引过滤性太差，每个线程在查找的时候会遇到很多冲突的记录。

这时候对系统用一下top命令，可以发现这个mysql实例cpu飚的很高，我们再用perf工具看一下cpu飙高的mysql调用堆栈是怎么样的，如下所示：

我们看到耗cpu最高的调用函数栈是…<code>mutex_spin_wait</code>-&gt;<code>ut_delay</code>，属于锁等待的逻辑。innodb在这里用的是自旋锁，锁等待是通过调用ut_delay做空循环实现的，会消耗cpu。这里证明了上面的判断是对的。

在这个case里涉及到的锁有记录锁、<code>lock_sys-&gt;mutex</code>和<code>trx_sys-&gt;mutex</code>，究竟是哪个锁等待时间最长呢？我们可以用下面的方法确认一下：

从上面的表可以确认，lock_mutex（在mysql源码里对应的是<code>lock_sys-&gt;mutex</code>）的锁等待累积时间最长（sum_timer_wait）。lock_sys表示全局的innodb锁系统，在源码里看到innodb加/解某个记录锁的时候（这个case里是x锁），同时需要维护lock_sys，这时会请求lock_sys-&gt;mutex。

在这个case里，因为在searching rows for update的阶段频繁地加/解x锁，就会频繁请求<code>lock_sys-&gt;mutex</code>，导致<code>lock_sys-&gt;mutex</code>锁总等待时间过长，同时在等待的时候消耗了大量cpu。

当我们将索引改成过滤性好的（比如字段n），再做上述实验，就看不到那么多线程堵在”searching rows for update”的阶段，而且实例的cpu消耗也降了很多。

通过以上实验，我们看到索引过滤性不好可能带来灾难性的结果：语句hang住以及主机cpu耗尽。因此我们在设计表的时候，应该对业务上的数据有充分的估计，选择过滤性好的字段作为索引。