电商型网站设计企业,怎么在百度做原创视频网站,网站 筛选功能,国外企业网络平台转载自 在MySQL的InnoDB存储引擎中count(*)函数的优化
写这篇文章之前已经看过了很多数据库方面的优化内容#xff0c;大部分都是加索引、使用事务、要什么select什么等等。然而#xff0c;只是停留在阅读的层面上#xff0c;很少有实践#xff0c;因为没有遇到真实的项目…转载自 在MySQL的InnoDB存储引擎中count(*)函数的优化
写这篇文章之前已经看过了很多数据库方面的优化内容大部分都是加索引、使用事务、要什么select什么等等。然而只是停留在阅读的层面上很少有实践因为没有遇到真实的项目一切都是纸上谈兵。实践是检验真理的唯一标准于是就想在数据库上测试一些性能优化的方案比如索引之类的但是不想使用假的数据于是就想着能不能抓取网上的一些数据来作分析后来自己通过PHP抓取了一些数据(爬取数据博文)抓了大约110W的用户数据之后当然需要统计一下具体的数量于是我使用了以下的SQL语句我使用的存储引擎是InnoDB
SELECT COUNT(*) FROM zh_user;
然而发现需要运行14-20s的时间才能看到结果。 这样的时间开销在真实的环境的用户体验是十分差的试想一下打开一个页面还要等接近20s才能看到数据别说20s就算是3s也是十分差的于是便想在这方面做优化。
存储引擎
在MySQL中日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。
因为MyISAM会保存表的具体行数因此这段代码在MyISAM存储引擎中执行MyISAM只要简单地读出保存好的行数即可。因此如果表中没有使用事务之类的操作这是最好的优化方案。然而InnoDB存储引擎不会保存表的具体行数因此在InnoDB存储引擎中执行这段代码InnoDB要扫描一遍整个表来计算有多少行。
查询优化命令--Explain
要弄懂查询性能在哪首先需要知道导致查询缓慢的瓶颈在哪。explain命令显示的rows是核心的性能指标rows大说明mysql需要扫描的行数就多绝大部分rows大的语句执行一定很快。所以优化语句基本上都是在优化rows。
首先看看表的结构 表的当前索引 再看看Explain的结果 可以看到mysql扫描了整个表来执行本次查询。
奇怪的地方
在数据表的设计中我是添加了唯一索引的但是后来有一个语句是根据其中一个字段统计数量当时添加了一个普通的索引当我再执行了一遍上面的SQL语句发现只需要0.2-0.3s的时间就能统计出表中的行数。 不禁吓了一跳误打误撞就发现了优化的方法在InnoDB中除了唯一索引之外在其他字段添加一个普通索引称为辅助索引就能够提升count(*)函数的性能。但是这是为什么呢 加了索引之后的表结构 当前的索引 Explain一下 同样是扫描一样的行数为什么添加一个普通索引就可以提高这么多的性能于是便开始查找资料和阅读文档弄懂这个问题。
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 KEYInnoDB会选择第一个有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减少了性能就提高了。因此在InnoDB中如果有统计整张表的数量的需求可以考虑增加一个辅助索引。
