网站运营难做吗,互联网营销行业,一网科技有限公司,html5 手机网站页面实例转载自 select count(*)底层究竟干了啥么#xff1f;
“SELECT COUNT( * ) FROM t” 是个再常见不过的 SQL 需求了。在 MySQL 的使用规范中#xff0c;我们一般使用事务引擎 InnoDB 作为(一般业务)表的存储引擎#xff0c;在此前提下#xff0c;COUNT( * )操作的时间复杂…转载自 select count(*)底层究竟干了啥么
“SELECT COUNT( * ) FROM t” 是个再常见不过的 SQL 需求了。在 MySQL 的使用规范中我们一般使用事务引擎 InnoDB 作为(一般业务)表的存储引擎在此前提下COUNT( * )操作的时间复杂度为 O(N)其中 N 为表的行数。
而 MyISAM 表中可以快速取到表的行数。这些实践经验的背后是怎样的机制以及为什么需要/可以是这样就是此文想要探讨的。
先来看一下概况: MySQL COUNT( * ) 在 2 种存储引擎中的部分问题 INNODBMYISAM执行耗时O(N)其中 N 是表的行数无论是否考虑 IO 因素O(1)执行过程扫描全表根据数据来计算行数取到表级 meta 信息的 row_count 值流程执行过程如何2 种存储引擎在哪里开始分道扬镳 原因为何必须要扫表计算 count而不能存储一个 row_count 变量为何可以记录 count 变量问题Count 值如何计算存在哪里Count 值如何获取
下面就带着这些问题以 InnoDB 存储引擎为主来进行讨论。 一、InnoDB 全表 COUNT( * )
主要问题:
执行过程是怎样的如何计算 count影响 count 结果的因素有哪些count 值存在哪里涉及的数据结构是怎样的为什么 InnoDB 只能通过扫表来实现 count( * )(见本文最后的问题)全表COUNT( * )作为 table scan 类型操作的一个 case有什么风险COUNT(* )操作是否会像“SELECT * ”一样可能读取大字段涉及的溢出页
1. 执行框架 – 循环: 读取 计数
1.1 基本结论
全表扫描一个循环解决问题。循环内: 先读取一行再决定该行是否计入 count。循环内是一行一行进行计数处理的。
1.2 说明
简单 SELELCT-SQL 的执行框架类比 INSERT INTO … SELECT 是同样的过程。 下面会逐步细化如何读取与计数 ( count ) 。
2. 执行过程
引述: 执行过程部分分为 4 个部分:
COUNT( * ) 前置流程: 从 Client 端发 SQL 语句到 MySQL-Server 端执行 SELECT 之前为后面的一些阐述做一铺垫。COUNT( * ) 流程: 简要给出代码层面的流程框架及 2 个核心步骤的重点调用栈部分。读取一行: 可见性及 row_search_mvcc 函数介绍可见性如何影响 COUNT( * ) 结果。计数一行: Evaluate_join_record 与列是否为空介绍计数过程如何影响 COUNT( * ) 结果。
如果读者希望直接看如何进行 COUNT( * )那么也可以忽略 (1)而直接跳到 (2) 开始看。
2.1 COUNT( * ) 前置流程回忆 – 从 Client 端发 SQL 到 sub_select 函数
为了使看到的调用过程不太突兀我们还是先回忆一下如何执行到 sub_select 函数这来的 MySQL-Client 端发送 SQL 语句根据 MySQL 通信协议封包发送。Mysql-Server 端接收数据包由协议解析出 command 类型 ( QUERY ) 及 SQL 语句 ( 字符串 ) 。SQL 语句经过解析器解析输出为 JOIN 类的对象用于结构化地表达该 SQL 语句。 PS: 这里的 JOIN 结构不仅仅是纯语法结构而是已经进行了语义处理粗略地说汇总了表的列表 ( table_list )、目标列的列表 ( target_list )、WHERE 条件、子查询等语法结构。 在全表 COUNT( * )-case 中table_list [表“t”(别名也是“t”)]target_list [目标列对象(列名为“COUNT( * )”)]当然这里没有 WHERE 条件、子查询等结构。 JOIN 对象有 2 个重要的方法: JOIN::optimize(), JOIN::exec()分别用于进行查询语句的优化 和 查询语句的执行。 join-optimize()优化阶段 (稍后 myisam 下全表 count( * ) 操作会涉及这里的一点内容)。join-exec()执行阶段 ( 重点 )包含了 InnoDB 下全表count( * ) 操作的执行流程。join-exec() 经过若干调用将调用到 sub_select 函数来执行简单 SQL包括 COUNT( * ) 。END of sub_select 。
2.2 COUNT( * ) 流程 ( 于 sub_select 函数中 )
上层的流程与代码是比较简单的集中在 sub_select 函数中其中 2 类函数分别对应于前面”执行框架”部分所述的 2 个步骤 – 读取、计数。先给出结论如下
读取一行从相对顶层的 sub_select 函数经过一番调用最终所有分支将调用到 row_search_mvcc 函数中该函数就是用于从 InnoDB 存储引擎所存储的 B-tree 结构中读取一行到内存中的一个 buf (uchar * ) 中待后续处理使用。 这里会涉及行锁的获取、MVCC 及行可见性的问题。当然对 于 SELECT COUNT( * ) 这类快照读而言只会涉及 MVCC 及其可见性而不涉及行锁。详情可跳至“可见性与 row_search_mvcc 函数”部分。 计数一行: 代码层面将会在 evaluate_join_record 函数中对所读取的行进行评估看其是否应当计入 count 中 ( 即是否要 count )。 简单来说COUNT(arg) 本身为 MySQL 的函数操作对于一行来说若括号内的参数 arg ( 某列或整行 ) 的值若不是 NULL则 count否则对该行不予计数。详情可跳至“ Evaluate_join_record 与列是否为空”部分。
这两个阶段对 COUNT( * )结果的影响如下: (两层过滤) 微观宏观读取一行过滤1过滤1row_search_mvcc函数决定语句将要能看到的那一行决定语句能看到多少行处理一行过滤2过滤2evaluate_join_record函数决定该行(NULL)是否被计数决定可见行中计数多少行,即最终结果是多少行
SQL 层流程框架相关代码摘要如下:
1210 enum_nested_loop_state1211 sub_select(JOIN *join, QEP_TAB *const qep_tab,bool end_of_records)1212 {1213 DBUG_ENTER(sub_select);... ... // 此处省略1000字1265 while (rc NESTED_LOOP_OK join-return_tab qep_tab_idx)1266 {1267 int error;// 第一步从存储引擎中获取一行1268 if (in_first_read)1269 {1270 in_first_read false;// 第一步首次读取扫描第一个满足条件的记录// 初始化cursor从”头”扫描到某个位置// 类似: SELECT id FROM t LIMIT 1;1271 error (*qep_tab-read_first_record)(qep_tab);1272 }1273 else// 第一步后续读取在前次扫描的位置上继续遍历找到一个满足条件的记录// 类似: SELECT id FROM t WHERE id $last_id LIMIT 1;1274 error info-read_record(info);... ... // 此处省略1000字// 第二步处理刚刚取出的一行1291 rc evaluate_join_record(join, qep_tab);... ... // 此处省略1000字1303 DBUG_RETURN(rc);1304 }
Q 代码层面第一步骤(读取一行)有 2 个分支为什么
A从 InnoDB 接口层面考虑分为 “读第一行” 和 “读下一行”是 2 个不同的执行过程读第一行需要找到一个 ( cursor ) 位置并做一些初始化工作让后续的过程可递归。
正如我们如果用脚本/程序来进行逐行的扫表操作实现上就会涉及下面 2 个 SQL
// SELECT id FROM t LIMIT 1; OR SELECT MIN(id)-1 FROM t; - $last_id// SELECT id FROM t WHERE id $last_id LIMIT 1;
涉及到此例的代码SQL 层到存储引擎层的调用关系读取阶段的调用栈如下(供参考)
sub_select 函数中从 SQL 层到 InnoDB 层的函数调用关系(同颜色、同缩进 表示同一层)Ø (*qep_tab-read_first_record) ()| -- join_read_first(tab)| -- tab-read_record.read_recordjoin_read_next;| -- table-file-ha_index_init()| -- handler::ha_index_init(uint idx, bool sorted)| -- ha_innobase::index_init()| -- table-file-ha_index_first()| -- handler::ha_index_first(uint idx, bool sorted)| -- ha_innobase::index_first()| -- ha_innobase::index_read()| -- row_search_mvcc()初始化cursor并将其放到一个有效的初始位置上;Ø info-read_record (info)| -- join_read_next(info)| -- info-table-file-ha_index_next(info-record))| -- handler::ha_index_next(uchar * buf)| -- ha_innobase::index_next(uchar * buf)| -- general_fetch(buf, ROW_SEL_NEXT, 0)| -- row_search_mvcc()“向前”移动一次cursor;
我们可以看到无论是哪一个分支的读取最终都殊途同归于 row_search_mvcc 函数。
以上是对 LOOP 中的代码做一些简要的说明下面来看 row_search_mvcc 与 evaluate_join_record 如何输出最终的 count 结果。
2.3 行可见性及 row_search_mvcc 函数
这里我们主要通过一组 case 和几个问题来看行可见性对 COUNT( * ) 的影响。 Q对于“SELECT COUNT( * ) FROM t”或者“SELECT MIN(id) FROM t”操作第一次的读行操作读到的是表 t 中 ( B 树最左叶节点 page 内 ) 的最小记录吗( ha_index_first 为何也调用 row_search_mvcc 来获取最小 key 值)
A不一定。即使是 MIN ( id ) 也不一定就读取的是 id 最小的那一行因为也同样有行可见性的问题实际上 index_read 取到的是 当前事务内语句可见的最小 index 记录。这也反映了前面提到的 join_read_first 与 join_read_next “殊途同归”到 row_search_mvcc 是理所应当的。
Q针对图中最后一问如果事务 X 是 RU ( Read-Uncommitted ) 隔离级别且 C-Insert ( 100 ) 的完成是在 X-count( * ) 执行过程中 ( 仅扫描到 5 或 10 这条记录 ) 完成的那么 X-count( * ) 在事务 C-Insert ( 100 ) 完成后能否在之后的读取过程中看到 100 这条记录呢
AMySQL 采取”读到什么就是什么”的策略即 X-count( * ) 在后面可以读到 100 这条记录。
2.4 evaluate_join_record 与列是否为空
Q某一行如何计入 count
A两种情况会将所读的行计入 count:
如果 COUNT 函数中的参数是某列则会判断所读行中该列定义是否 Nullable 以及该列的值是否为 NULL若两者均为是则不会计入 count否则将计入 count。 e.g. SELECT COUNT(col_name) FROM tcol_name 可以是主键、唯一键、非唯一键、非索引字段如果 COUNT 中带有 * 则会判断这部分的整行是否为 NULL如果判断参数为 NULL则忽略该行否则 count。
e.g-1. SELECT COUNT(*) FROM te.g-2. SELECT COUNT(B.*) FROM A LEFT JOIN B ON A.id B.id
Q 特别地对于 SELECT COUNT(id) FROM t其中 id 字段是表 t 的主键则如何
A效果上等价于 COUNT( * )。因为无论是 COUNT( * )还是 COUNT ( pk_col ) 都是因为有主键从而充分断定索取数据不为 NULL这类 COUNT 表达式可以用于获取当前可见的表行数。
Q 用户层面对 InnoDB COUNT( * ) 的优化操作问题
A这个问题是业界熟悉的一个问题扫描非空唯一键可得到表行数但所涉及的字节数可能会少很多(在表的行长与主键、唯一键的长度相差较多时)相对的 IO 代价小很多。
相关调用栈参考如下:
参考一:evaluate_join_record()| -- rc (*qep_tab-next_select)(join, qep_tab1, 0);| -- end_send_group(...)| -- init_sum_functions(join-sum_funcs, join-sum_funcs_end[idx1]))| -- (*func_ptr)-reset_and_add()| -- Item_sum::aggregator_clear()| -- Item_sum::aggregator_add()| -- update_sum_func(Item_sum **func_ptr)| -- (*func_ptr)-add()| -- Item_sum::aggregator_add()参考二: (Item_sum::aggregator_add)((Item_sum *) (*func_ptr))-aggregator_add()| -- (Item_sum *)this-aggr-add()| -- ((Aggregator_simple *) aggr)-item_sum-add()| -- if (! aggr-arg_is_null(false))| ------ ((Item_sum_count *)aggr-item_sum)-count; 二、数据结构:
Qcount 值存储在哪个内存变量里
**A **SQL 解析后存储于表达 COUNT( * ) 这一项中((Item_sum_count*)item_sum)-count
如下图所示回顾我们之前“COUNT( * )前置流程”部分提到的 JOIN 结构。 即 SQL 解析器为每个 SQL 语句进行结构化将其放在一个 JOIN 对象 ( join ) 中来表达。在该对象中创建并填充了一个列表 result_field_list 用于存放结果列列表中每个元素则是一个结果列的 ( Item_result_field* ) 对象 ( 指针 ) 。
在 COUNT( * )-case 中结果列列表只包含一个元素( Item_sum_count: public Item_result_field ) 类型对象 ( name “COUNT( * )”)其中该类所特有的成员变量 count即为所求。 三、MyISAM 全表 COUNT( * )
由于 MyISAM 引擎并不常用于实际业务中仅做简要描述如下
MyISAM-COUNT( * ) 操作是 O(1) 时间复杂度的操作。每张 MyISAM 表中存放了一个 meta 信息-count 值在内存中与文件中各有一份内存中的 count 变量值通过读取文件中的 count 值来进行初始化。SELECT COUNT( * ) FROM t 会直接读取内存中的表 t 对应的 count 变量值。内存中的 count 值与文件中的 count 值由写操作来进行更新其一致性由表级锁来保证。表级锁保证的写入串行化使得同一时刻所有用户线程的读操作要么被锁要么只会看到一种数据状态。四、几个问题
QMyISAM 与 InnoDB 在 COUNT( * ) 操作的执行过程在哪里开始分道扬镳
共性共性存在于 SQL 层即 SQL 解析之后的数据结构是一致的count 变量都是存在于作为结果列的 Item_sum_count 类型对象中返回给客户端的过程也类似 – 对该 count 变量进行赋值并经由 MySQL 通信协议返回给客户端。区别InnoDB 的 count 值计算是在 SQL 执行阶段进行的而 MyISAM 表本身在内存中有一份包含了表 row_count 值的 meta 信息在 SQL 优化阶段通过存储引擎的标记给优化器一个 hint表明该表所用的存储引擎保存了精确行数可以直接获取到无需再进入执行器。QInnoDB 中为何无法向 MyISAM 一样维护住一个 row_count 变量
A从 MVCC 机制与行可见性问题中可得到原因每个事务所看到的行可能是不一样的其 count( * ) 结果也可能是不同的反过来看则是 MySQL-Server 端无法在同一时刻对所有用户线程提供一个统一的读视图也就无法提供一个统一的 count 值。
PS: 对于多个访问 MySQL 的用户线程 ( COUNT( * ) ) 而言决定它们各自的结果的因素有几个:
一组事务执行前的数据状态(初始数据状态)。有时间重叠的事务们的执行序列 (操作时序事务理论表明 并发事务操作的可串行化是正确性的必要条件)。事务们各自的隔离级别(每个操作的输入)。
其中 1、2 对于 Server 而言都是全局或者说可控的只有 3 是每个用户线程中事务所独有的属性这是 Server 端不可控的因素因此 Server 端也就对每个 COUNT( * ) 结果不可控了。
QInnoDB-COUNT( * ) 属 table scan 操作是否会将现有 Buffer Pool 中其它用户线程所需热点页从 LRU-list 中挤占掉从而其它用户线程还需从磁盘 load 一次突然加重 IO 消耗可能对现有请求造成阻塞
AMySQL 有这样的优化策略将扫表操作所 load 的 page 放在 LRU-list 的 oung/old 的交界处 ( LRU 尾部约 3/8 处 )。这样用户线程所需的热点页仍然在 LRU-list-young 区域而扫表操作不断 load 的页则会不断冲刷 old 区域的页这部分的页本身就是被认为非热点的页因此也相对符合逻辑。
PS: 个人认为还有一种类似的优化思路是限定扫描操作所使用的 Buffer Pool 的大小为 O(1) 级别但这样做需要付出额外的内存管理成本。
QInnoDB-COUNT( * ) 是否会像 SELECT * FROM t 那样读取存储大字段的溢出页(如果存在)
A否。因为 InnoDB-COUNT( * ) 只需要数行数而每一行的主键肯定不是 NULL因此只需要读主键索引页内的行数据而无需读取额外的溢出页。
本文作者贾春生
