MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

14 | count(*)这么慢,我该怎么办?

林晓斌 2018-12-14
在开发系统的时候,你可能经常需要计算一个表的行数,比如一个交易系统的所有变更记录总数。这时候你可能会想,一条 select count(*) from t 语句不就解决了吗?
但是,你会发现随着系统中记录数越来越多,这条语句执行得也会越来越慢。然后你可能就想了,MySQL 怎么这么笨啊,记个总数,每次要查的时候直接读出来,不就好了吗。
那么今天,我们就来聊聊 count(*) 语句到底是怎样实现的,以及 MySQL 为什么会这么实现。然后,我会再和你说说,如果应用中有这种频繁变更并需要统计表行数的需求,业务设计上可以怎么做。

count(*) 的实现方式

你首先要明确的是,在不同的 MySQL 引擎中,count(*) 有不同的实现方式。
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;
而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
这里需要注意的是,我们在这篇文章里讨论的是没有过滤条件的 count(*),如果加了 where 条件的话,MyISAM 表也是不能返回得这么快的。
在前面的文章中,我们一起分析了为什么要使用 InnoDB,因为不论是在事务支持、并发能力还是在数据安全方面,InnoDB 都优于 MyISAM。我猜你的表也一定是用了 InnoDB 引擎。这就是当你的记录数越来越多的时候,计算一个表的总行数会越来越慢的原因。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(130)

  • 包包up 置顶
    从并发系统性能的角度考虑,应该先插入操作记录,再更新计数表。

    知识点在《行锁功过:怎么减少行锁对性能的影响?》
    因为更新计数表涉及到行锁的竞争,先插入再更新能最大程度地减少了事务之间的锁等待,提升了并发度。

    作者回复: 好几个同学说对,你第一个标明出处👍🏿

    2018-12-14
    174
  • 倪大人 置顶
    看到有同学说会话A是幻读,其实图一的会话B才是幻读吧?

    作者回复: 这些都不叫幻读,幻读的意思是“用一个事务里面,后一个请求看到的比之前相同请求看到的,多了记录出来”。
    改了不算

    大家关注一下这个问题。
    好问题

    2018-12-15
    12
  • 果然如此 置顶
    一、请问计数用这个MySQL+redis方案如何:
    1.开启事务(程序中的事务)
    2.MySQL插入数据
    3.原子更新redis计数
    4.如果redis更新成功提交事务,如果redis更新失败回滚事务。

    二、.net和java程序代码的事务和MySQL事务是什么关系,有什么相关性?

    作者回复: 1. 好问题,不会还是没解决我们说的一致性问题。如果在3、4之间插入了 Session B的逻辑呢

    2. 我估计就是启动事务(执行begin),结束时提交(执行commit)吧,没有了解过所有框架,不确定哈

    2018-12-14
    8
  • 发条橙子 。 置顶
    老师 ,我这边有几个问题 :

    1. 看到老师回复评论说 count(id) 也是走普通索引 ,那是不是也算是优化了 , 我以为 count(字段) 是走的聚集索引 。老师的意思是 count(字段) 是走二级索引,但是不一定是数据最少的索引树的意思是么

    2. count(*) 的话, innodb 还会有取数判空这样的判断逻辑么 ,还是直接取行数 +1 了 , 还是按所取索引类型分情况。 允许为 null 的索引是不是行数比较少, 取的总数会不会有问题呢

    3. 我这边试了一下 , 库里总共 30w 数据 。 第一次用 count(*) 是 120多ms , 第二次就是 60多 ms 。 第三次用了 count(1) ,也是60多ms 。 请问 count(*) 这两次的前后时间差是什么原因,也会走缓存 ?

    4. 另一个问题是一个题外话 ,我看老师的例子事务级别应该都是 rr 。 我偶然看到我们公司事务隔离级别是 rc 。 我比较惊讶,就去问 DBA 为什么是 rc 而不是默认的 rr 。 她说一般都是用的 rc ,我想问现在公司一般都是 rc 么, 请问老师现在用的隔离级别是什么 ?? 在我的印象里 ,rr 保证事务的隔离性会更好一些吧 。 我google 了一下, rc 会不会在某些场景下出现一些问题,但是没有查出来相关结果。老师能不能讲解一下,rc 的话会在哪些场景下会踩坑么 。 (我之前码代码都是按照 rr 级别下的思维码的代码)

    作者回复: 1. 如果有索引用到这个字段的话,比较大可能会用到这个索引,比主键索引小

    2. 索引字段就算是NULL,上面的id也不是的

    3. 进了Buffer pool 的原因吧

    4. 嗯,rc用得挺多的,但是原因可能只是因为“以前是这么用的”。 使用rc可能有问题,也可能没问题。但是我觉得DBA不知道为什么这么选,这个是问题。

    rc本身的问题其实前面我们说过一些,比如不是一致性读。后面也会有文章说到。

    2018-12-15
    3
    6
  • 北天魔狼
    老师说过:事务开启后,更新操作放到最后。较少锁等待时间的影响
    2018-12-14
    22
  • 三木子
    一直以为带*查询效率是最差的,平时查询特意加了 count(ID) 查询。罪过啊。

    作者回复: 😄 来得及来得及

    2018-12-15
    21
  • 某、人
    老师我先问个本章之外的问题:
    1.rr模式下,一张表上没有主键和唯一键,有二级索引c.如果是一张大表,删除一条数据delete t where c=1.
    在主库上利用二级索引,在根据虚拟的主键列回表删除还挺快.但是在备库上回放特别慢,而且状态是system lock,是因为binlog event里没有包含虚拟主键列.导致在备库回放的时候,必须全表扫描,耗时特别久?还是其他原因

    2.回放过程中,在备库delete一条语句是被阻塞的,insert又是可以的,说明只在记录上的X锁没有gap锁。
    但是如果在主库session A begin,delete where c=1.在开启一个session B,在主库上操作也是delete阻塞,insert正常.不过等session A执行完成,不提交.insert都阻塞了,说明最后上了gap锁。有点没明白这儿的上锁逻辑是什么?

    3.还有就是备库回放binlog,相对于主库的一条update语句流程来说,从库回放哪些流程是省略了的啊,
    server层的应该都省略了,应该主要是引擎层的回放,这里有点模糊从库是怎么回放的binlog event?
    因为第一个问题从库回放的时候,从库上的二级索引貌似没起作用,直接就在聚簇索引上做的更新。

    感谢老师

    作者回复: 1. 对,这个是个bug, 从库上会全表扫描。MariaDB 的版本有解决这个问题。生产上我们最好不允许没有主键的表

    2. 按照你问的,gap锁没问题了。delete 被锁是因为行锁吧。从库重放就是因为走全表扫描按行锁下来触发的

    3. 出现这个问题肯定是binlog设置了row格式。
    这样binlog里面有所有值。如果你有主键的话,就是主键查,没有的话…就是全表了

    2018-12-14
    1
    11
  • 菜鸡一只
    count(id)和count(这段)都是要把每一行的该字段值取出来,然后判断是否为空,那为什么count(id)的效率要高?

    作者回复: count(id)可能会选择最小的索引来遍历
    而count(字段)的话,如果字段上没有索引,就只能选主键索引

    2019-02-01
    1
    6
  • 某、人
    谈谈自己的理解,有不对之处还请老师指出:
    数据一致性问题目前来说主要分为三类
    1.主从不一致
    解决办法:半同步复制after_commit,after_sync,MGR(after_prepare)。但是都不能完成满足完全实时一致,由于等待的ack点不同,相对来说一致性的强度是递增.
    2.数据库与缓存的不一致
    解决办法:读操作直接读缓存,写操作先更新到数据库,淘汰缓存(程序需要保证两个操作的原子性).由于该key的缓存已经清理掉,那么下次读的时候需要先读数据库,在重建缓存.
    由于redis是单线程,保证了一个操作的原子性.可以通过设置appendfsync always来保证每次操作都把该操作记录并落盘到aof文件里(不过一般redis该值为everysec),毕竟使用redis的目的不是为了保证acid.还是要根据业务来选择
    3.一个事务跨多个节点或者多种数据库(分库分表和银行转账这种例子)
    目前好像都是通过2pc,3pc来保证的。

    count(字段值):如果该字段上有null值.每行的行头有一个标记位,标记该行是否为null.所以多了一层判断。相对更耗时
    count(主键id):即便是选择的有null值的二级索引,但是也可以挺快的正确计数。因为null的话字段值虽然为null,但是该行上主键id以及指向聚簇索引该id的指针还是存在的,所以不影响计数,也不用做判断,直接遍历该二级索引,取出id值,按行累加就行。
    count(1)和count(*):看官方文档上说是5.7.18版本之前是扫描聚簇索引,之后是二级索引。虽然不取值,只计数。但是二级索引比聚簇索引需要扫描的页数相对来说更少,这应该也是一种优化,不过我做测试percona版本的5.6都是选择了二级索引
    这期干货挺多的,学会了如果某表上有count比较多的操作,最好是用count(1)或者count(*),然后选择一列占用字节数最少的建立索引(比如tinyint类型)

    还有个问题请教下老师:
    1.如果某列设置为not null建立索引.那么是不是count(id)走该索引和count(该列)效率是不是一样的?都不用做判断,两者都是需要把整个二级索引传给server层计数?还是说count(id)只需要传id,而count(字段)只需要传字段值给server层做计数?
    2018-12-16
    1
    6
  • 崔根禄
    老师:
    1.count(*) 不取值,
             InnoDB还做遍历表的操作吗,也不用给server层返回值吗?
    2.count(1) 不取值,
            但是要遍历表。原文中:
            “server 层对于返回的每一行,放一个数字“1”进去”
            这个“返回的每一行” :到底返回的啥?是每一行记录吗?还是形式的返回空行,然后用1填充?

    3. count(1),count(*),count(主键id)
           这三个做比较,哪个会快?时间消耗在哪个环节?
          是否遍历表;是否取值;返回给server层内容 细节上从哪个角度考虑?
             
    2018-12-14
    6
  • 斜面镜子 Bill
    先插入操作纪录,再更新计数表,因为计数表相当于热点行,加锁时间需要考虑足够短!
    2018-12-14
    6
  • 不忘初心
    对于 count(主键 id) ,server层拿到ID,判断ID是不可能为空的按行累加。这个地方,是不是又点问题,既然是主键ID,是一定不会为空的,这个server层还需要判断不为空吗

    作者回复: 嗯,代码就是这么写的 我也觉得可以优化一下… 不过现在就这样😓

    2018-12-26
    5
  • WL
    把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
    1.
    count(*)的实现方式在MySAM引擎和InnoDB引擎的实现方式各是怎么样的? 为什么会有这种不同?
    2.
    使用缓存保存count总数存在什么问题?
    3.
    如果使用一场单独的表来记录其他各张表的记录数的话,是怎么解决统计结果不精确的问题的?
    4.
    count(字段),count(id),count(1), count(*)各自是怎么样的执行机制, 效率排序是怎么样的?

    2018-12-16
    5
  • godtrue
    1:又刷新了认知,先给结论(之前不知从哪看的,以为count(主键id)性能最佳)
    按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以老师建议,尽量使用 count(*)。
    2:count(*)这么慢,我该怎么办?
    要么忍,要么自己动手记录,如果自己记录的话,老师建议使用数据库来弄,感觉使用数据库自己弄的思路可以建议MySQL实现一下?
    3:count()的语义是啥?
    首先,不同的存储引擎实现方式不同
    MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;
    而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
    以下针对innodb来说
    count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加,最后返回累计值。
    4:count(字段)怎么计数?
    4-1:如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
    4-2:如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。
    从引擎返回的字段会涉及到解析数据行,以及拷贝字段值的操作。
    5:count(主键 id)怎么计数?
    对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。从引擎返回的 主键id 会涉及到解析数据行,以及拷贝字段值的操作。
    6:count(1)怎么计数?
    对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
    7:count(*)怎么计数?
    对于count(*)来说,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,按行累加。
    8:现在终于弄明白这些count()背后的计算原理啦!非常感谢!另外,分析这些count()的原则如下:
    8-1:server 层要什么就给什么;
    8-2:InnoDB 只给必要的值;
    8-3:现在的优化器只优化了 count(*) 的语义为“取行数”,其他“显而易见”的优化并没有做。
    这几条原则对别的性能差别的分析也是OK的吧?
    达到同样的目标,谁绕的弯越多做的事情越多就会越慢,性能自然不咋滴!不过知道每种达到目的的路径轨迹是一个难点,如果知道谁不喜欢走捷径呢?
    2019-07-01
    4
  • 小动物很困
    我记得有一个并发插入的方法,就是说计数表对同一个表开很多行,然后计数增加对这些数据随机做加法,当做count操作的时候取sum,这样对行锁竞争会削弱.

    作者回复: 是的,咱们专栏07篇也有类似的介绍😆

    2019-01-22
    3
  • Bin
    答:先插入操作记录,再更新计数表。
           在InnoDB事务中,行锁是在需要的时候才加上, 等到事务结束(commit)的时候才释放。
           案例中的更新操作很多时候都是更新同一个数据对象, 如果是先更新计数表,那么持有的锁时间会更长.
    2018-12-14
    3
  • 陈天境
    碰到大部分情形都是带条件查询的count,,这个怎么解?

    作者回复: 索引条件过滤完后还多少行?如果行数少(几百行?)就没关系直接执行了

    2018-12-14
    3
  • 2009532140
    关键,很多查询不仅仅是单表的计数。大部分情况下是对表联合查询的计数。是不是没有特殊好的思路了
    2019-06-25
    2
  • Zzz
    老师,对于自增主键,批量插入是否会存在阻塞的情况?

    作者回复: 为什么会呢?不会的

    2019-01-22
    1
    2
  • 萧萧木叶
    歪个楼请教个业务中遇到的问题:
    表结构如下:
     CREATE TABLE `tablename` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `uid` bigint(20) NOT NULL DEFAULT '0',
      `status` tinyint(1) NOT NULL DEFAULT '1' ,
      `date` varchar(8) NOT NULL DEFAULT '0' COMMENT '日期',
      `source` varchar(20) NOT NULL DEFAULT '' COMMENT '来源',
      `ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
      `etime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_uid_date` (`uid`,`date`),
      KEY `idx_ctime` (`ctime`)
    ) ENGINE=InnoDB AUTO_INCREMENT=685725 DEFAULT CHARSET=utf8

    其中 UNIQUE KEY `idx_uid_date` (`uid`,`date`)

    查询指定date下uid个数:
    方式一:select count(uid) from tablename where date = '20181201';
    +------------+
    | count(uid) |
    +------------+
    | 8330 |
    +------------+
    方式二:select count(distinct uid) from tablename where date = '20181201';
    +---------------------+
    | count(distinct uid) |
    +---------------------+
    | 8243
    +---------------------+
    方式三:
    select count(*) from (select distinct uid from tablename where date = '20181201') as t;
    +----------+
    | count(*) |
    +----------+
    | 8330 |
    +----------+
    与方式一查询结果一致

    问题:为何方式二和方式一、三的结果不一样呢?
    2018-12-26
    2
收起评论
99+
返回
顶部