MySQL 5.7下InnoDB对COUNT(*)的优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 5.7下InnoDB对COUNT(*)的优化

0、导读

饱受诟病的InnoDB表COUNT(*)性能问题在5.7下做了优化,果真如此吗?

1、经典需求:InnoDB表COUNT(*)

InnoDB引擎表经常被抱怨执行COUNT(*)的效率太差,因此此类需求通常会被建议用其他方法来满足,比如另外加一个计数器表,或者用SHOW TABLE STATUS查看大概数量。

不过,从MySQL 5.7.2起,这个问题得到了解决,我们来看看。

2、MySQL 5.7版本InnoDB对COUNT(*)的优化

MySQL每发布一个新版本,都会放出相应的Release Notes,我们注意到5.7.2版本的发布说明中提到:

InnoDB: SELECT COUNT(*) FROM t statements now invoke a single handler call to the storage engine to scan the clustered index and return the row count to the Optimizer. Previously, a row count was typically performed by traversing a smaller secondary index and invoking a handler call for each record. A single handler call to the storage engine to count rows in the clustered index generally improves SELECT COUNT(*) FROM t performance. However, in the case of a large clustered index and a significantly smaller secondary index, performance degradation is possible compared to performance using the previous, non-optimized implementation. For more information, see Limits on InnoDB Tables.

简单地说就是:COUNT(*)会选择聚集索引,进行一次内部handler函数调用,即可快速获得该表总数。我们可以通过执行计划看到这个变化,例如:

image.png

很明显,在查询优化器阶段就已经得到优化了,相比效率应该杠杠的吧,我们稍后再来对比看看。

补充说下,5.7以前的版本中,COUNT(*)请求通常是:扫描普通索引来获得这个总数。也来看看5.6下的执行计划是怎样的:

image.png

可以看到,可以利用覆盖索引来完成COUNT(*)请求。

3、对比测试

先看一组测试数据:

count(*)对比测试

MySQL 5.6.33

MySQL 5.7.15

相差

表数据量

8976914

9000270

100.26%

耗时(秒)

5.459952

1.142340

20.92%

可以看到,两次数据量相当,但SQL耗时5.7约只有5.6的1/5,这个效率还是不错的吧。

我们来看看5.6和5.7版本下的status和profiling对比情况:

image.png

4、别高兴得太早

看完上面的对比测试,相信您已经心动了吧,但还别高兴得太早哦,官方文档里其实埋了一个伏笔:

InnoDB: SELECT COUNT(*) FROM t statements now invoke a single handler call to the storage engine to scan the clustered index and return the row count to the Optimizer. Previously, a row count was typically performed by traversing a smaller secondary index and invoking a handler call for each record. A single handler call to the storage engine to count rows in the clustered index generally improves SELECT COUNT(*) FROM t performance. However, in the case of a large clustered index and a significantly smaller secondary index, performance degradation is possible compared to performance using the previous, non-optimized implementation. For more information, see Limits on InnoDB Tables.

简言之,就是说如果聚集索引较大(或者说表数据量较大),没有完全加载到buffer pool中的话,有可能反而会更慢,还不如用原先的方式。

下面我们来测试下,读取tpcc测试表stock,该表有1亿行记录,表空间文件约65GB,而innodb buffer pool只分配了12G,这时候再看下对比数据:

count(*)对比测试

MySQL 5.6.33

MySQL 5.7.15

相差

表数据量

1亿

1亿

0.00%

耗时(秒)

693.66

5331.69

768.63%

在这种情况下,5.7版本反而慢的夸张,悲剧啊~

那么在5.7下的大表,有没有办法仍旧采用以前的方法来做COUNT(*)统计呢。当然可以了,我们可以强制指定普通索引,不过还需要加上WHERE条件,否则还是不行。后来搜了下,发现这是个bug,印风(zhaiwx)已经报告给官方了,bug id:81854。

image.png

这次的SQL执行耗时和在5.6下的就基本一样了。

4、后记

5.7版本整体挺赞的,不过还是有不少地方需要完善,期待能更成熟起来。

参考

1. MySQL 5.7.2 Release Notes:http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html

2. Limits on InnoDB Tableshttp://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
115 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
9天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
9天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
10天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
1月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
116 0
|
3月前
|
监控 关系型数据库 MySQL
在Linux中,mysql的innodb如何定位锁问题?
在Linux中,mysql的innodb如何定位锁问题?
|
3月前
|
SQL 存储 关系型数据库
"MySQL增列必锁表?揭秘InnoDB在线DDL,让你的数据库操作飞一般,性能无忧!"
【8月更文挑战第11天】在数据库领域,MySQL凭借其稳定高效的表现深受开发者喜爱。对于是否会在给数据表添加列时锁表的问题,MySQL的行为受版本、存储引擎等因素影响。从5.6版起,InnoDB支持在线DDL,可在改动表结构时保持表的可访问性,避免长时间锁表。而MyISAM等则需锁表完成操作。例如,在使用InnoDB的表上运行`ALTER TABLE users ADD COLUMN email VARCHAR(255);`时,通常不会完全锁表。虽然在线DDL提高了灵活性,但复杂操作或大表变更仍可能暂时影响性能。因此,进行结构变更前应评估其影响并择机执行。
71 6
|
5月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
199 57
|
4月前
|
存储 SQL 关系型数据库
(十三)MySQL引擎篇:半道出家的InnoDB为何能替换官方的MyISAM?
MySQL是一款支持拔插式引擎的数据库,在开发过程中你可以根据业务特性,从支持的诸多引擎中选择一款适合的,例如MyISAM、InnoDB、Merge、Memory(HEAP)、BDB(BerkeleyDB)、Example、Federated、Archive、CSV、Blackhole.....