insert时调用本身字段_selectcount(*)执行时的底层原理

SELECT COUNT( * ) FROM TABLE 是个再常见不过的 SQL 需求了。

在 MySQL 的使用规范中&＃xff0c;我们一般使用事务引擎 InnoDB 作为(一般业务)表的存储引擎&＃xff0c;在此前提下&＃xff0c;COUNT( * )操作的时间复杂度为 O(N)&＃xff0c;其中 N 为表的行数。

而 MyISAM 表中可以快速取到表的行数。这些实践经验的背后是怎样的机制&＃xff0c;以及为什么需要/可以是这样&＃xff0c;就是此文想要探讨的。

先来看一下概况&＃xff0c;MySQL COUNT( * ) 在 2 种存储引擎中的部分问题&＃xff1a;

下面就带着这些问题&＃xff0c;以 InnoDB 存储引擎为主来进行讨论。

一、InnoDB 全表 COUNT( * )

主要问题&＃xff1a;

1、执行过程是怎样的&＃xff1f;

2、如何计算 count&＃xff1f;影响 count 结果的因素有哪些&＃xff1f;

3、count 值存在哪里&＃xff1f;涉及的数据结构是怎样的&＃xff1f;

4、为什么 InnoDB 只能通过扫表来实现 count( * )&＃xff1f;(见本文最后的问题)

5、全表COUNT( * )作为 table scan 类型操作的一个 case&＃xff0c;有什么风险&＃xff1f;

6、COUNT(* )操作是否会像“SELECT * ”一样可能读取大字段涉及的溢出页&＃xff1f;

1、执行框架 – 循环: 读取 &＃43; 计数&＃xff1f;

1.1、基本结论&＃xff1a;

• 全表扫描&＃xff0c;一个循环解决问题。

• 循环内: 先读取一行&＃xff0c;再决定该行是否计入 count。

• 循环内是一行一行进行计数处理的。

1.2、说明&＃xff1a;

简单 SELELCT-SQL 的执行框架&＃xff0c;类比 INSERT INTO … SELECT 是同样的过程。

下面会逐步细化如何读取与计数 ( count&＃43;&＃43; ) 。

2、执行过程&＃xff1f;

执行过程部分&＃xff0c;分为 4 个部分&＃xff1a;

(1)COUNT( * ) 前置流程: 从 Client 端发 SQL 语句&＃xff0c;到 MySQL-Server 端执行 SELECT 之前&＃xff0c;为后面的一些阐述做一铺垫。

(2)COUNT( * ) 流程: 简要给出代码层面的流程框架及 2 个核心步骤的重点调用栈部分。

(3)读取一行: 可见性及 row_search_mvcc 函数&＃xff0c;介绍可见性如何影响 COUNT( * ) 结果。

(4)计数一行: Evaluate_join_record 与列是否为空&＃xff0c;介绍计数过程如何影响 COUNT( * ) 结果。

如果读者希望直接看如何进行 COUNT( * )&＃xff0c;那么也可以忽略 (1)&＃xff0c;而直接跳到 (2) 开始看。

2.1、COUNT( * ) 前置流程回忆 – 从 Client 端发 SQL 到 sub_select 函数

为了使看到的调用过程不太突兀&＃xff0c;我们还是先回忆一下如何执行到 sub_select 函数这来的&＃xff1a;

(1)MySQL-Client 端发送 SQL 语句&＃xff0c;根据 MySQL 通信协议封包发送。

(2)Mysql-Server 端接收数据包&＃xff0c;由协议解析出 command 类型 ( QUERY ) 及 SQL 语句 ( 字符串 ) 。

(3)SQL 语句经过解析器解析输出为 JOIN 类的对象&＃xff0c;用于结构化地表达该 SQL 语句。

PS: 这里的 JOIN 结构&＃xff0c;不仅仅是纯语法结构&＃xff0c;而是已经进行了语义处理&＃xff0c;粗略地说&＃xff0c;汇总了表的列表 ( table_list )、目标列的列表 ( target_list )、WHERE 条件、子查询等语法结构。

在全表 COUNT( * )-case 中&＃xff0c;table_list &＃61; [表“t”(别名也是“t”)]&＃xff0c;target_list &＃61; [目标列对象(列名为“COUNT( * )”)]&＃xff0c;当然这里没有 WHERE 条件、子查询等结构。

(4)JOIN 对象有 2 个重要的方法: JOIN::optimize(), JOIN::exec()&＃xff0c;分别用于进行查询语句的优化 和 查询语句的执行。

• join->optimize()&＃xff0c;优化阶段 (稍后 myisam 下全表 count( * ) 操作会涉及这里的一点内容)。

• join->exec()&＃xff0c;执行阶段 ( 重点 )&＃xff0c;包含了 InnoDB 下全表count( * ) 操作的执行流程。

(5)join->exec() 经过若干调用&＃xff0c;将调用到 sub_select 函数来执行简单 SQL&＃xff0c;包括 COUNT( * ) 。

(6)END of sub_select 。

2.2、COUNT( * ) 流程 ( 于 sub_select 函数中 )

上层的流程与代码是比较简单的&＃xff0c;集中在 sub_select 函数中&＃xff0c;其中 2 类函数分别对应于前面”执行框架”部分所述的 2 个步骤 – 读取、计数。先给出结论如下&＃xff1a;

(1)读取一行&＃xff1a;从相对顶层的 sub_select 函数经过一番调用&＃xff0c;最终所有分支将调用到 row_search_mvcc 函数中&＃xff0c;该函数就是用于从 InnoDB 存储引擎所存储的 B&＃43;-tree 结构中读取一行到内存中的一个 buf (uchar * ) 中&＃xff0c;待后续处理使用。

这里会涉及行锁的获取、MVCC 及行可见性的问题。当然对 于 SELECT COUNT( * ) 这类快照读而言&＃xff0c;只会涉及 MVCC 及其可见性&＃xff0c;而不涉及行锁。详情可跳至“可见性与 row_search_mvcc 函数”部分。

(2)计数一行: 代码层面&＃xff0c;将会在 evaluate_join_record 函数中对所读取的行进行评估&＃xff0c;看其是否应当计入 count 中 ( 即是否要 count&＃43;&＃43; )。

简单来说&＃xff0c;COUNT(arg) 本身为 MySQL 的函数操作&＃xff0c;对于一行来说&＃xff0c;若括号内的参数 arg ( 某列或整行 ) 的值若不是 NULL&＃xff0c;则 count&＃43;&＃43;&＃xff0c;否则对该行不予计数。详情可跳至“ Evaluate_join_record 与列是否为空”部分。

这两个阶段对 COUNT( * )结果的影响如下: (两层过滤)

SQL 层流程框架相关代码摘要如下:

Q&＃xff1a;代码层面&＃xff0c;第一步骤(读取一行)有 2 个分支&＃xff0c;为什么&＃xff1f;

A&＃xff1a;从 InnoDB 接口层面考虑&＃xff0c;分为 “读第一行” 和 “读下一行”&＃xff0c;是 2 个不同的执行过程&＃xff0c;读第一行需要找到一个 ( cursor ) 位置并做一些初始化工作让后续的过程可递归。

正如我们如果用脚本/程序来进行逐行的扫表操作&＃xff0c;实现上就会涉及下面 2 个 SQL&＃xff1a;

具体涉及到此例的代码&＃xff0c;SQL 层到存储引擎层的调用关系&＃xff0c;读取阶段的调用栈如下&＃xff1a;(供参考)

我们可以看到&＃xff0c;无论是哪一个分支的读取&＃xff0c;最终都殊途同归于 row_search_mvcc 函数。

以上是对 LOOP 中的代码做一些简要的说明&＃xff0c;下面来看 row_search_mvcc 与 evaluate_join_record 如何输出最终的 count 结果。

2.3、行可见性及 row_search_mvcc 函数

这里我们主要通过一组 case 和几个问题来看行可见性对 COUNT( * ) 的影响。

Q&＃xff1a;对于“SELECT COUNT( * ) FROM t”或者“SELECT MIN(id) FROM t”操作&＃xff0c;第一次的读行操作读到的是表 t 中 ( B&＃43; 树最左叶节点 page 内 ) 的最小记录吗&＃xff1f;( ha_index_first 为何也调用 row_search_mvcc 来获取最小 key 值&＃xff1f;)

A&＃xff1a;不一定。即使是 MIN ( id ) 也不一定就读取的是 id 最小的那一行&＃xff0c;因为也同样有行可见性的问题&＃xff0c;实际上 index_read 取到的是 当前事务内语句可见的最小 index 记录。这也反映了前面提到的 join_read_first 与 join_read_next “殊途同归”到 row_search_mvcc 是理所应当的。

Q&＃xff1a;针对图中最后一问&＃xff0c;如果事务 X 是 RU ( Read-Uncommitted ) 隔离级别&＃xff0c;且 C-Insert ( 100 ) 的完成是在 X-count( * ) 执行过程中 ( 仅扫描到 5 或 10 这条记录 ) 完成的&＃xff0c;那么 X-count( * ) 在事务 C-Insert ( 100 ) 完成后&＃xff0c;能否在之后的读取过程中看到 100 这条记录呢&＃xff1f;

A&＃xff1a;MySQL 采取”读到什么就是什么”的策略&＃xff0c;即 X-count( * ) 在后面可以读到 100 这条记录。

2.4、evaluate_join_record 与列是否为空

Q&＃xff1a;某一行如何计入 count&＃xff1f;

A&＃xff1a;两种情况会将所读的行计入 count:

(1)如果 COUNT 函数中的参数是某列&＃xff0c;则会判断所读行中该列定义是否 Nullable 以及该列的值是否为 NULL&＃xff1b;若两者均为是&＃xff0c;则不会计入 count&＃xff0c;否则将计入 count。

• e.g. SELECT COUNT(col_name) FROM t

• col_name 可以是主键、唯一键、非唯一键、非索引字段

(2)如果 COUNT 中带有 * &＃xff0c;则会判断这部分的整行是否为 NULL&＃xff0c;如果判断参数为 NULL&＃xff0c;则忽略该行&＃xff0c;否则 count&＃43;&＃43;。

• e.g-1. SELECT COUNT(*) FROM t

• e.g-2. SELECT COUNT(B.*) FROM A LEFT JOIN B ON A.id &＃61; B.id

Q&＃xff1a;特别地&＃xff0c;对于 SELECT COUNT(id) FROM t&＃xff0c;其中 id 字段是表 t 的主键&＃xff0c;则如何&＃xff1f;

A&＃xff1a;效果上等价于 COUNT( * )。因为无论是 COUNT( * )&＃xff0c;还是 COUNT ( pk_col ) 都是因为有主键从而充分断定索取数据不为 NULL&＃xff0c;这类 COUNT 表达式可以用于获取当前可见的表行数。

Q&＃xff1a;用户层面对 InnoDB COUNT( * ) 的优化操作问题

A&＃xff1a;这个问题是业界熟悉的一个问题&＃xff0c;扫描非空唯一键可得到表行数&＃xff0c;但所涉及的字节数可能会少很多(在表的行长与主键、唯一键的长度相差较多时)&＃xff0c;相对的 IO 代价小很多。

相关调用栈参考如下&＃xff1a;

二、数据结构

Q&＃xff1a;count 值存储在哪个内存变量里&＃xff1f;

A&＃xff1a;SQL 解析后&＃xff0c;存储于表达 COUNT( * ) 这一项中&＃xff0c;((Item_sum_count*)item_sum)->count

如下图所示回顾我们之前“COUNT( * )前置流程”部分提到的 JOIN 结构。

即 SQL 解析器为每个 SQL 语句进行结构化&＃xff0c;将其放在一个 JOIN 对象 ( join ) 中来表达。在该对象中创建并填充了一个列表 result_field_list 用于存放结果列&＃xff0c;列表中每个元素则是一个结果列的 ( Item_result_field* ) 对象 ( 指针 ) 。

在 COUNT( * )-case 中&＃xff0c;结果列列表只包含一个元素&＃xff0c;( Item_sum_count: public Item_result_field ) 类型对象 ( name &＃61; “COUNT( * )”)&＃xff0c;其中该类所特有的成员变量 count即为所求。

三、MyISAM 全表 COUNT( * )

由于 MyISAM 引擎并不常用于实际业务中&＃xff0c;仅做简要描述如下&＃xff1a;

1、MyISAM-COUNT( * ) 操作是 O(1) 时间复杂度的操作。

2、每张 MyISAM 表中存放了一个 meta 信息-count 值&＃xff0c;在内存中与文件中各有一份&＃xff0c;内存中的 count 变量值通过读取文件中的 count 值来进行初始化。

3、SELECT COUNT( * ) FROM t 会直接读取内存中的表 t 对应的 count 变量值。

4、内存中的 count 值与文件中的 count 值由写操作来进行更新&＃xff0c;其一致性由表级锁来保证。

5、表级锁保证的写入串行化使得&＃xff0c;同一时刻所有用户线程的读操作要么被锁&＃xff0c;要么只会看到一种数据状态。

四、几个问题

Q&＃xff1a;MyISAM 与 InnoDB 在 COUNT( * ) 操作的执行过程在哪里开始分道扬镳&＃xff1f;

• 共性&＃xff1a;

  共性存在于 SQL 层&＃xff0c;即 SQL 解析之后的数据结构是一致的&＃xff0c;count 变量都是存在于作为结果列的 Item_sum_count 类型对象中&＃xff1b;

  返回给客户端的过程也类似 – 对该 count 变量进行赋值并经由 MySQL 通信协议返回给客户端。

• 区别&＃xff1a;InnoDB 的 count 值计算是在 SQL 执行阶段进行的&＃xff1b;而 MyISAM 表本身在内存中有一份包含了表 row_count 值的 meta 信息&＃xff0c;在 SQL 优化阶段通过存储引擎的标记给优化器一个 hint&＃xff0c;表明该表所用的存储引擎保存了精确行数&＃xff0c;可以直接获取到&＃xff0c;无需再进入执行器。

原文链接&＃xff1a;blog.didiyun.com/index.php/2019/01/08/mysql-count

如果感觉推送内容不错&＃xff0c;不妨右下角点个在看&＃xff0c;感谢支持&＃xff01;