文档章节

MySQL count(*) 优化

IT--小哥
 IT--小哥
发布于 2017/07/21 11:29
字数 1014
阅读 25
收藏 0

在MySQL 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_mysql
  • HA_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时进行的,当数据量大于buffer pool 时,执行效率会大大下降):

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (3.92 sec)

即时强制指定索引也没用 MySQL 5.7 InnoDB COUNT(*)优化

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到官方,在MySQL 5.7.18已经还原为原来的版本,原话如下:

由于MySQL 5.7.2对Count(*)中引入了修改,导致在某些情况下,Innodb通过遍历聚集索引而不是较小的辅助索引来计算行数,因此性能有所倒退。在MySQL 5.7.18中修改被还原。

其他

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 5.7 InnoDB COUNT(*)优化

参考:http://mysql.taobao.org/monthly/2016/06/10/

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

本文转载自:http://mysql.taobao.org/monthly/2016/06/10/

IT--小哥
粉丝 47
博文 148
码字总数 151015
作品 0
东城
数据库管理员
私信 提问
mysql中select的count星和count1有区别么

之前一直都任务select count(1) from tab的效率要高于count(),今天看了下执行计划才发现原来一直都是一个误解,mysql的优化器会自动转换。 直接上例子 explain SELECT count() FROM employ...

王小明123
2012/09/03
2.2K
0
mysql的count(*)的优化,获取千万级数据表的总行数

一、前言 这个问题是今天朋友提出来的,关于查询一个1200w的数据表的总行数,用count(*)的速度一直提不上去。找了很多优化方案,最后另辟蹊径,选择了用explain来获取总行数。 二、关于count...

凯文加内特
02/15
102
0
MongoDB和MySQL中的large skip问题、count问题

large skip 在为数据分页时,一般要skip多少记录并limit多少记录,例如在MySQL中: SELECT * FROM large_table ORDER BY id LIMIT 10000, 30 这个过程是很慢的,因为数据库需要从第一个记录开...

樂天
2014/10/03
599
0
高性能MySQL——Count(1) OR Count(*)?

count(列名)某个字段值为NULL时,不统计 如果问一个程序员MySQL中SELECT COUNT(1)和SELECT COUNT()有什么区别,会有很多人给出这样的答案“SELECT COUNT()”最终会转化成“SELECT COUNT(1),...

吴伟祥
02/20
33
0
mysql磁盘查询和查询优化的几点问题

mysql中有没有类似于sql server查询本地磁盘使用情况的语句呢?select TABLE_SCHEMA能查询出表空间大小,磁盘大小通过mysql能否得到呢? 还有select count(*)如何最大的优化呢?上百万的数据...

缪斯的情人
2012/11/22
263
5

没有更多内容

加载失败,请刷新页面

加载更多

Redis集群搭建

服务器资源 ip 账号 配置 操作系统 xxx.70 root/xxx 磁盘50G(/)+150G(/home)、内存16G、CPU 16core CentOS Linux release 7.2.1511 (Core) xxx.74 root/xxx 磁盘50G(/)+150G(/home)、......

jxlgzwh
31分钟前
5
0
avro

一、 ```我们已经接触过很多序列化框架(或者集成系统),比如protobuf、hessian、thrift等,它们各有优缺点以及各自的实用场景,Avro也是一个序列化框架,它的设计思想、编程模式都和thi...

hexiaoming123
33分钟前
5
0
QML TextInput的字体超出控件范围

本文链接:https://blog.csdn.net/chyuanrufeng/article/details/54691998 问题描述 :QML TextInput输入内容超过TextInput的大小 当输入过多的字符串时,会出现内容超过TextInput的大小,字...

shzwork
34分钟前
4
0
《Java 8 in Action》Chapter 10:用Optional取代null

1965年,英国一位名为Tony Hoare的计算机科学家在设计ALGOL W语言时提出了null引用的想法。ALGOL W是第一批在堆上分配记录的类型语言之一。Hoare选择null引用这种方式,“只是因为这种方法实...

HelloDeveloper
35分钟前
4
0
进击的 Java ,云原生时代的蜕变

作者| 易立 阿里云资深技术专家<br /> <br />导读:云原生时代的来临,与Java 开发者到底有什么联系?有人说,云原生压根不是为了 Java 存在的。然而,本文的作者却认为云原生时代,Java 依然...

阿里巴巴云原生
37分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部