又是一年秋招季
后台回复【java面试】获取3G面试资料
后台回复【python面试】获取面试资料
SELECT COUNT( * ) FROM TABLE 是个再常见不过的 SQL 需求了。 在 MySQL 的使用规范中,我们一般使用事务引擎 InnoDB 作为(一般业务)表的存储引擎,在此前提下,COUNT( * )操作的时间复杂度为 O(N),其中 N 为表的行数。 而 MyISAM 表中可以快速取到表的行数。这些实践经验的背后是怎样的机制,以及为什么需要/可以是这样,就是此文想要探讨的。 先来看一下概况,MySQL COUNT( * ) 在 2 种存储引擎中的部分问题: 下面就带着这些问题,以 InnoDB 存储引擎为主来进行讨论。 一、InnoDB 全表 COUNT( * )主要问题:
1、执行过程是怎样的?
2、如何计算 count?影响 count 结果的因素有哪些? 3、count 值存在哪里?涉及的数据结构是怎样的? 4、为什么 InnoDB 只能通过扫表来实现 count( * )?(见本文最后的问题) 5、全表COUNT( * )作为 table scan 类型操作的一个 case,有什么风险? 6、COUNT(* )操作是否会像“SELECT * ”一样可能读取大字段涉及的溢出页? 1、执行框架 – 循环: 读取 + 计数?1.1、基本结论:
全表扫描,一个循环解决问题。
循环内: 先读取一行,再决定该行是否计入 count。
循环内是一行一行进行计数处理的。
2.1、COUNT( * ) 前置流程回忆 – 从 Client 端发 SQL 到 sub_select 函数
为了使看到的调用过程不太突兀,我们还是先回忆一下如何执行到 sub_select 函数这来的: (1)MySQL-Client 端发送 SQL 语句,根据 MySQL 通信协议封包发送。 (2)Mysql-Server 端接收数据包,由协议解析出 command 类型 ( QUERY ) 及 SQL 语句 ( 字符串 ) 。 (3)SQL 语句经过解析器解析输出为 JOIN 类的对象,用于结构化地表达该 SQL 语句。 PS: 这里的 JOIN 结构,不仅仅是纯语法结构,而是已经进行了语义处理,粗略地说,汇总了表的列表 ( table_list )、目标列的列表 ( target_list )、WHERE 条件、子查询等语法结构。 在全表 COUNT( * )-case 中,table_list = [表“t”(别名也是“t”)],target_list = [目标列对象(列名为“COUNT( * )”)],当然这里没有 WHERE 条件、子查询等结构。 (4)JOIN 对象有 2 个重要的方法: JOIN::optimize(), JOIN::exec(),分别用于进行查询语句的优化 和 查询语句的执行。join->optimize(),优化阶段 (稍后 myisam 下全表 count( * ) 操作会涉及这里的一点内容)。
join->exec(),执行阶段 ( 重点 ),包含了 InnoDB 下全表count( * ) 操作的执行流程。
2.2、COUNT( * ) 流程 ( 于 sub_select 函数中 )
上层的流程与代码是比较简单的,集中在 sub_select 函数中,其中 2 类函数分别对应于前面”执行框架”部分所述的 2 个步骤 – 读取、计数。先给出结论如下: (1)读取一行:从相对顶层的 sub_select 函数经过一番调用,最终所有分支将调用到 row_search_mvcc 函数中,该函数就是用于从 InnoDB 存储引擎所存储的 B+-tree 结构中读取一行到内存中的一个 buf (uchar * ) 中,待后续处理使用。 这里会涉及行锁的获取、MVCC 及行可见性的问题。当然对 于 SELECT COUNT( * ) 这类快照读而言,只会涉及 MVCC 及其可见性,而不涉及行锁。详情可跳至“可见性与 row_search_mvcc 函数”部分。 (2)计数一行: 代码层面,将会在 evaluate_join_record 函数中对所读取的行进行评估,看其是否应当计入 count 中 ( 即是否要 count++ )。 简单来说,COUNT(arg) 本身为 MySQL 的函数操作,对于一行来说,若括号内的参数 arg ( 某列或整行 ) 的值若不是 NULL,则 count++,否则对该行不予计数。详情可跳至“ Evaluate_join_record 与列是否为空”部分。 这两个阶段对 COUNT( * )结果的影响如下: (两层过滤) SQL 层流程框架相关代码摘要如下: Q:代码层面,第一步骤(读取一行)有 2 个分支,为什么? A: 从 InnoDB 接口层面考虑,分为 “读第一行” 和 “读下一行”,是 2 个不同的执行过程,读第一行需要找到一个 ( cursor ) 位置并做一些初始化工作让后续的过程可递归。 正如我们如果用脚本/程序来进行逐行的扫表操作,实现上就会涉及下面 2 个 SQL: 具体涉及到此例的代码,SQL 层到存储引擎层的调用关系,读取阶段的调用栈如下:(供参考) 我们可以看到,无论是哪一个分支的读取,最终都殊途同归于 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 这条记录呢? A: MySQL 采取”读到什么就是什么”的策略,即 X-count( * ) 在后面可以读到 100 这条记录。2.4、evaluate_join_record 与列是否为空
Q:某一行如何计入 count? A: 两种情况会将所读的行计入 count: (1)如果 COUNT 函数中的参数是某列,则会判断所读行中该列定义是否 Nullable 以及该列的值是否为 NULL;若两者均为是,则不会计入 count,否则将计入 count。e.g. SELECT COUNT(col_name) FROM t
col_name 可以是主键、唯一键、非唯一键、非索引字段
e.g-1. SELECT COUNT(*) FROM t
e.g-2. SELECT COUNT(B.*) FROM A LEFT JOIN B ON A.id = B.id
共性:共性存在于 SQL 层,即 SQL 解析之后的数据结构是一致的,count 变量都是存在于作为结果列的 Item_sum_count 类型对象中;返回给客户端的过程也类似 – 对该 count 变量进行赋值并经由 MySQL 通信协议返回给客户端。
区别:InnoDB 的 count 值计算是在 SQL 执行阶段进行的;而 MyISAM 表本身在内存中有一份包含了表 row_count 值的 meta 信息,在 SQL 优化阶段通过存储引擎的标记给优化器一个 hint,表明该表所用的存储引擎保存了精确行数,可以直接获取到,无需再进入执行器。
Java开发微服务畅购商城实战-全357集【附代码课件】
Java微服务实战谷粒商城-296集【附代码课件】
Spring Boot开发小而美的个人博客【附课件和源码】
基于Python flask框架的租房项目实战【附资料和源码】
PyQt5开发与实战视频【附课件和源码】
最全最详细数据结构与算法视频-【附课件和源码】
2020年微信小程序全栈项目之喵喵交友【附课件和源码】
你点的每一个在看,我都认真当成了喜欢