美文网首页
MySQL-删除数据和count(*)原理

MySQL-删除数据和count(*)原理

作者: ging_efcf | 来源:发表于2020-07-20 14:03 被阅读0次

    delete删除数据原理

    在InndoDB存储引擎中,delete删除操作是把需要删除的数据或者页标记为已删除,后面如果有需要,直接复用即可。这些被标记为已经删除的数据,看起来就像空洞一样。所以看起来虽然delete了,但是表文件大小并不会改变。

    如果想删除这些空洞,达到收缩表空间的目的,可以使用alter table t engine=InnoDB来重建表,内部流程如下:

    1. 新建一个表结构相同的表b

    2. 把数据,从表a按照主键递增的顺序一行一行读出来然后插入表b

    3. 操作完毕后,用表b替换表a

    在mysql5.6之前,这个操作在整个过程中,不能有更新操作。mysql5.6以后引入的Online DDL,可以对上面的步骤进行优化。

    • 首先,复习一下onlineDDL的概念:在之前的笔记MySQL-全句锁、表锁和元数据锁中,学习元数据锁的时候也提到过online DDL的概念,核心就是先申请一个MDL写锁,成功以后会降级为MDL读锁,然后做真正的DDL操作,操作完毕后再申请MDL写锁。

    • 其次,在做真正的DDL操作时,既然不阻塞DML操作,那么对表的DML操作如何体现到新表中呢?MySQL的实现是把对表的更新操作记录在一个日志文件(row log)中,等到拷贝完原数据到到临时表后,再对这个临时表应用row log中的修改。


    count(*)原理

    在不同的存储引擎中,对count(*)的实现是不同的

    • MyISAM中,会保存总行数到磁盘中,每次select count(*)会直接返回这个值

    • InnoDB中,计算count(*)的时候,需要把数据从引擎中一行一行读出来,然后计算累加值

    为什么InnoDB不把数据保存起来?

    在之前的文章 MySQL-事务中的一致性读和锁定读的具体原理中,我们学习过事务,了解了MVCC,知道不同时刻启动的事务,拿到的事务视图是不一样的,在可重复读的隔离级别设置下,事务中读取数据始终是一致的。这个问题其实就和MVCC有关,既然每个事务视图拿到的数据可能是不一样的,那么就无法只存储一个值,来代表行数。而且,MyISAM看起来把行数存储为一个值,查询的时候性能较高,但是一旦查询条件中使用了where,那么就无法使用这个值了。

    不同count写法的性能对比

    首先需要明确,count(XX),统计的是XX不为null的行数。

    在实际统计时,有人会用count(*),有人用count(1),还有其他用count(字段)的,那么这些写法有什么差别呢?

    我们假设没有where条件,直接查询

    整个过程分两步:

    1. InnoDB存储引擎查询数据结果集

    2. Server层根据结果集进行遍历统计

    InnoDB存储引擎查询数据结果集时

    InnoDB会在所有的索引中,选择一个最小的索引来进行数据查询

    • 如果有普通索引,就用最小的普通索引

    • 如果没有普通索引,用主键索引

    针对不同的查询写法,返回的数据结果集中的取值也不同

    • count(1)和count(*):InnoDB存储引擎返回对应的数据列表,但是不取值,可以理解为返回一个List<Data> 但是data中没有任何字段的。

    • count(字段):InnoDB存储引擎返回对应的数据列表,需要取得对应字段的值

    Server层根据结果集进行遍历统计
    • count(1)和count(*):server层拿到数据后

      • 如果是count(1):server层在每行插入一个1,因为1肯定不为null,所以直接遍历统计行数

      • 如果是count(*):server层直接遍历统计行数

    • count(字段):

      • 如果是主键id和定义时不为空的字段:server层直接进行遍历统计,和count(1)和count(*)比,还有复制字段的开销

      • 定义时可以为空的字段:server层需要取出字段,再判断一下是否为null,不为空的才统计

    总结

    在没有where查询条件时,我们可以看到,不同count写法的性能优劣如下:

    count(*) ≈count(1) > count(字段)

    相关文章

      网友评论

          本文标题:MySQL-删除数据和count(*)原理

          本文链接:https://www.haomeiwen.com/subject/gfaekktx.html