转载

**在MySQL的InnoDB存储引擎中count(*)函数的优化**

写这篇文章之前已经看过了很多数据库方面的优化内容，大部分都是加索引、使用事务、要什么select什么等等。然而，只是停留在阅读的层面上，很少有实践，因为没有遇到真实的项目，一切都是纸上谈兵。实践是检验真理的唯一标准，于是就想在数据库上测试一些性能优化的方案，比如索引之类的，但是不想使用假的数据，于是就想着能不能抓取网上的一些数据来作分析，后来自己通过PHP抓取了一些数据(这个博文即将补上)，抓了大约110W的用户数据之后，当然需要统计一下具体的数量，于是我使用了以下的SQL语句（我使用的存储引擎是InnoDB）：

SELECT COUNT(*) FROM tbl_name;

然而，发现需要运行14-20s的时间才能看到结果。

在MySQL的InnoDB存储引擎中count(*)函数的优化

这样的时间开销在真实的环境的用户体验是十分差的，试想一下，打开一个页面还要等接近20s才能看到数据，别说20s，就算是3s也是十分差的，于是便想在这方面做优化。

存储引擎

在MySQL中，日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。

因为MyISAM会保存表的具体行数，因此这段代码在MyISAM存储引擎中执行，MyISAM只要简单地读出保存好的行数即可。因此，如果表中没有使用事务之类的操作，这是最好的优化方案。然而，InnoDB存储引擎不会保存表的具体行数，因此，在InnoDB存储引擎中执行这段代码，InnoDB要扫描一遍整个表来计算有多少行。

查询优化命令--Explain

要弄懂查询性能在哪，首先，需要知道导致查询缓慢的瓶颈在哪。explain命令显示的rows是核心的性能指标，rows大，说明mysql需要扫描的行数就多，绝大部分rows大的语句执行一定很快。所以优化语句基本上都是在优化rows。

首先，看看上面的语句：

在MySQL的InnoDB存储引擎中count(*)函数的优化

可以看到，mysql扫描了整个表来执行本次查询。

奇怪的地方

在数据表的设计中，我是添加了唯一索引的，但是后来有一个语句是根据其中一个字段统计数量，当时添加了一个普通的索引，当我再执行了一遍上面的SQL语句，发现只需要0.2-0.3s的时间就能统计出表中的行数。

在MySQL的InnoDB存储引擎中count(*)函数的优化

不禁吓了一跳，误打误撞就发现了优化的方法：在InnoDB中，除了唯一索引之外，在其他字段添加一个普通索引（称为辅助索引）就能够提升count(*)函数的性能。但是这是为什么呢？explain一下：

在MySQL的InnoDB存储引擎中count(*)函数的优化

同样是扫描一样的行数，为什么添加一个普通索引就可以提高这么多的性能？于是便开始查找资料和阅读文档弄懂这个问题。

count(*)函数执行原理

正如在不同的存储引擎中，count(*)函数的执行是不同的。在MyISAM存储引擎中，count(*)函数是直接读取数据表保存的行记录数并返回，而在InnoDB存储引擎中，count(*)函数是先从内存中读取表中的数据到内存缓冲区，然后扫描全表获得行记录数的。在使用count函数中加上where条件时，在两个存储引擎中的效果是一样的，都会扫描全表计算某字段有值项的次数。

索引原理

因为是添加了索引之后才得到性能上的提升，于是便想到从索引的角度来探索。

根据官方文档上的定义：索引是帮助MySQL高效获取数据的数据结构。可以得知，索引的本质就是数据结构，添加索引的目的就是为了提高查询的效率。

使用索引的查询可以类比到字典，如果要查”mysql“这个单词，我们首先会定位到m字母，然后在m字母下面的单词中找y字母，以此类推，直到找到mysql这个单词，就能看到它在第几页，然后就去该页获取该单词更多的信息。想象一下，如果没有索引，那你就要在字典里一页一页的翻阅，效率十分低下。使用索引就是通过这样不断地缩小查询的范围来筛选出最终的结果。

那么在数据库也是一样的，但显然在数据库里使用索引要复杂许多。

磁盘存取与预读

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。那么数据库在构建索引的时候就需要先从磁盘读取数据了，此时就要产生磁盘I/O消耗。而每次的数据读取，都要经历寻道时间、旋转延迟、传输时间三个部分。寻道时间是指磁臂移动到指定磁道所需要的时间，一般在5ms以内；旋转延迟就是磁盘转速；传输时间指的是将数据从磁盘读出并写入到内存的时间，这个时间较短，可以忽略不计。相对于内存存取，I/O存取的消耗要高几个数量级。因此，评价一个数据结构作为索引的优劣最重要的指标就是查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

从上面的描述可以得知磁盘I/O是非常高昂的操作，根据操作系统的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

计算机操作系统在这方面做了一些优化，当一次I/O时，不光把当前磁盘地址的数据读取到内存缓冲区内，而且把相邻的数据也都读取到内存缓冲区内。这样一来，在读取数据时产生的I/O就少了很多了。因为在数据库中，每一次I/O读取的数据我们称之为一页(page)，一般为4k或8k，也就是说，我们读取一页内的数据时，实际上才发生了一次I/O。

根据以上的描述，我们可以初步得出结论，增加索引前后的性能差距体现在磁盘读取过程。但是在添加新的索引之前，我是添加了一个唯一索引的，后来发现在mysql中，我添加的唯一索引被称为聚簇索引，而后面添加的索引称为辅助索引，因此，让我们再来看看聚簇索引和辅助索引的区别。

聚簇索引(clustered index)和辅助索引(secondary index)

聚簇索引(clustered index)

每一个InnoDB存储引擎下的表都有一个特殊的索引用来保存每一行的数据，称为聚簇索引。通常情况下，聚簇索引是主键的同义词。在InnoDB中，mysql是这样选择聚簇索引的：

如果表中定义了PRIMARY KEY，那么InnoDB就会使用它作为聚簇索引；

否则，如果没有定义PRIMARY KEY，InnoDB会选择第一个有NOT NULL约束的唯一索引作为PRIMARY KEY，然后InnoDB会使用它作为聚簇索引；

如果表中没有定义PRIMARY KEY或者合适的唯一索引。InnoDB会在一个合成的列中自动生成一个包含行ID的隐含的聚簇索引。这些行使用InnoDB赋予这些表的ID进行排序。行ID是6个字节的字段，且作为新行单一地自增。因此，根据行ID排序的行数据在物理上是根据插入的顺序进行排序。

聚簇索引如何加速查询

因为所有的行数据都跟聚簇索引存放在同一个地方，因此，通过聚簇索引访问数据行会更快。如果表十分大，跟使用不同地方保存数据和索引的存储组织来说，聚簇索引的结构会节省很多的I/O操作。（比如说，MyISAM使用了一个文件来保存数据以及另一个文件保存索引记录）。

辅助索引(secondary index)

除了聚簇索引之外的所有索引都被称为辅助索引。在InnoDB里，辅助索引的每一行记录都包含每一行的主键列，辅助索引指向主键。InnoDB使用这个主键来查找在聚簇索引中的行。如果主键很长，辅助索引会使用更多的空间，因此辅助索引有利于存储引擎拥有长度更短的主键。

因此可以得出结论：

在第一次使用了唯一索引(u_id)的时候，InnoDB使用了唯一索引作为表的聚簇索引。而在InnoDB存储引擎中，count(*)函数是先从内存中读取表中的数据到内存缓冲区，然后扫描全表获得行记录数的。因此，使用唯一索引作为聚簇索引的时候，InnoDB需要先读取110W条的数据到数据缓冲区中，这里发生了很多次I/O，因此造成了主要的时间消耗。而添加了辅助索引后，mysql在执行查询时会使用内部的优化机制：即使用辅助索引来统计数量。辅助索引保存的是index的值，此时只需要读取一个字段，I/O减少了，因此性能就提高了。

正文到此结束