MySQL · 引擎特性 · InnoDB COUNT(*) 优化(?)
2023-09-14 09:03:17 时间
在5.7版本中,InnoDB实现了新的handler的records接口函数,当你需要表上的精确记录个数时,会直接调用该函数进行计算。
实际上records接口函数是在优化阶段调用的,在满足一定条件时,直接去计算行级计数。其explain出来的结果相比老版本也有所不同,这里我们使用sysbench的sbtest表来进行测试,共200万行数据。
mysql show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT 0, `c` char(120) NOT NULL DEFAULT , `pad` char(60) NOT NULL DEFAULT , PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 1 row in set (0.00 sec) mysql explain select count(*) from sbtest1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Select tables optimized away 1 row in set, 1 warning (0.00 sec)
注意这里Extra里为”Select tables optimized away”,表示在优化器阶段已经被优化掉了。如果给id列带上条件的话,则回退到之前的逻辑
mysql explain select count(*) from sbtest1 where id 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 960984 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)
在WL#6742中,为InnoDB实现了handler的records函数接口
函数栈
opt_sum_query |-- get_exact_record_count |-- ha_records |-- ha_innobase::records |-- row_scan_index_for_mysqlHA_HAS_RECORDS:引擎flag,表示是否可以把count(*)下推到引擎层 总是使用聚集索引来进行计算行数 只需要读取主键值,无需去读取外部存储列(row_prebuilt_t::read_just_key),如果行记录较大的话,就可以节省客观的诸如内存拷贝之类的操作开销 计算过程可中断,每检索1000条记录,检查事务是否被中断 由于只有一次引擎层的调用,减少了Server层和InnoDB的交互,避免了无谓的内存操作或格式转换 对于分区表,在5.7版本已经下推到innodb层,因此分区表的计算方式(ha_innopart::records)是针对每个分区调用ha_innobase::records,再将结果累加起来
由于总是强制使用聚集索引,缺点很明显:当二级索引的大小远小于聚集索引,且数据不在内存中时,使用二级索引显然要快些,因此文件IO更少。如下例:
默认情况下检索所有行(以下测试都是在清空buffer pool时进行的):
mysql select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (3.92 sec)
即时强制指定索引也没用 :(
mysql select count(*) from sbtest1 force index(k_1); +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (3.86 sec)
但如果带上一个简单的条件,让select count(*)走索引k_1,耗费的时间立马下降了….
mysql select count(*) from sbtest1 where k 0; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1.05 sec)
个人认为这算是一个性能退化,退一步讲,如果用户知道force index能够走一个更好的索引来计算行数,优化器应该做出选择,而不是总是无条件选择聚集索引,提了个Bug到官方
从WL#6742还提到了一个尚未公布的WL#6605,从其只言片语中可以推断官方有意向实现即时获得行数:
The next worklog, WL#6605, is intended to return the COUNT(*) through this handler::records() interface almost immediately in all conditions just by keeping track if the base committed count along with transaction deltas
让我们继续对新版本保持期待吧 :)
华为大佬的“百万级”MySQL笔记,基础+优化+架构一键搞定 MySQL不用多说,大家都知道它是目前最为活跃热门的开源数据库,由于成本低,操作简易的特点,所以在互联网企业中被广泛使用,即使是头部的BATJ。由此可见,想要在互联网行业混得风生水起,或者说想要进入BATJ等一线互联网公司,那么熟练掌握MySQL必定是一块必要的敲门砖。
我又吊打面试官了,凭借MySQL海量数据优化(理论+实战) 朋友们,又见面了,上篇文章咱们讲到MySQL分库分表的方法,这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法,文章干货较多,建议三连。 提示:以下是本篇文章正文内容,案例仅供参考
MySQL优化 在我们的实际场景中经常会遇到sql查询较慢的问题,今天特地写一篇文章来聊聊我对于MySQL调优相关内容的知识,以及从哪些点去进行优化.
db匠 rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
相关文章
- 【Mysql 学习】memory存储引擎
- 【案例】利用innodb_force_recovery 解决MySQL服务器crash无法重启问题
- MySQL 存储引擎介绍
- Mysql存储引擎
- mysql的服务器构成
- Coreseek + Sphinx + Mysql + PHP构建中文检索引擎
- Mysql 之 添加innodb支持
- 查看MySQL是否支持InnoDB引擎以及不支持的解决办法
- mysql 存储引擎对索引的支持
- 数据库内核月报 - 2015 / 08-MySQL · 社区动态 · MySQL5.6.26 Release Note解读
- 从管理员角度分析:MySQL表引擎中MyISAM和InnoDB的对比
- MySQL-proxy实现读写分离详细步骤
- 〖Python 数据库开发实战 - Python与MySQL交互篇①〗- MySQL Connector 驱动模块的连接语法
- 第34讲:MySQL中常用的几种存储引擎以及如何选择
- MySQL技术内幕读书笔记(二)——InnoDB存储引擎
- java使用Mysql批量更新(不存在就插入,存在就更新)
- 【整理】Linux下中文检索引擎coreseek4安装,以及PHP使用sphinx的三种方式(sphinxapi,sphinx的php扩展,SphinxSe作为mysql存储引擎)
- .net core WebAPI 初探及连接MySQL
- MySQL(8)常用的条件查询命令详解
- Mysql建立数据库时创建数据库时指定数据库的字符模式
- MySQL安装教程