优秀的编程知识分享平台

网站首页 > 技术文章 正文

谁说不能使用select *（不能用sql实现的函数）

nanyue 2024-08-27 18:14:26 技术文章 7 ℃

我们经常听到说尽量不要使用 select *，你看我们都说是尽量了，所以，看完这篇文章就知道为什么这么说了。

导读

我们先来回顾一下交友平台用户表的表结构：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `user_id` int(8) DEFAULT NULL COMMENT '用户id',
  `user_name` varchar(29) DEFAULT NULL COMMENT '用户名',
  `user_introduction` varchar(498) DEFAULT NULL COMMENT '用户介绍',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  KEY `index_un_age_sex` (`user_name`,`age`,`sex`),
  KEY `index_age_sex` (`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

其中，user_introduction字段：用户介绍，里面允许用户填写非常长的内容，所以，我将这个字段的设为varchar(498)，加上其他字段，单条记录的长度可能就会比较大了，这时，如果执行下面这条 SQL：

select user_id, user_name, user_introduction from user where age > 20 and age < 50
复制代码

假设用户表中已经存储 300w 条记录，执行上面的 SQL，会发生什么情况呢？

对 MySQL 有初步了解的同学肯定知道Query Cache，它的作用就是缓存查询结果，通过首次查询时，建立 SQL 与结果的映射关系，相同 SQL 再次查询时，可以命中Query Cache，以此来提升后续相同查询的效率。

因此，对于上面的 SQL 查询，MySQL 可以在首次执行这条 SQL 后，将查询结果写入Query Cache，下次相同 SQL 执行时，可以从Query Cache中取出结果返回。

但是，你有没有想过，如果满足查询条件的用户数超过 10w，那么，这 10w 条记录能否完全写进Query Cache呢？

今天，我就从Query Cache的结构说起，逐步揭晓答案。

在《导读》中我提到 MySQL 通过建立 SQL 与查询结果的映射关系来实现再次查询的快速命中，那么，问题来了：为了实现这样的一个映射关系，总得有个结构承载这样的关系吧！那么，MySQL 使用什么结构来承载这样的映射关系呢？

或许你已经想到了：HashMap！没错，MySQL 的确使用了 HashMap 来表达 SQL 与结果集的映射关系。进而我们就很容易想到这个 HashMap 的 Key 和 Value 是什么了。

  • Key：MySQL 使用query + database + flag组成一个 key。这个 key 的结构还是比较直观的，它表示哪个库的哪条 SQL 使用了Query Cache。
  • Value：MySQL 使用一个叫query_cache_block的结构作为 Map 的 value，这个结构存放了一条 SQL 的查询结果。

Query Cache Block

那么，一条 SQL 的查询结果又是如何存放在query_cache_block中的呢？下面我们就结合《导读》中的 SQL，来看看一个query_cache_block的结构：

如上图所示，一个query_cache_block主要包含 3 个核心字段：

  • used：存放结果集的大小。MySQL 通过 block 在内存中的偏移量 + 这个大小来获取结果集。如上图，假设《导读》中 SQL 查询的结果为<10001, Jack, I'm Jack>，那么，used为这个查询结果的大小。
  • type：Block 的类型。包含{FREE, QUERY, RESULT, RES_CONT, RES_BEG, RES_INCOMPLETE, TABLE, INCOMPLETE}这几种类型。这里我重点讲解QUERY和RESULT，其他类型你可以自行深入了解。
    • QUERY：表示这个 block 中存放的是查询语句。为什么要缓存查询语句呢？在并发场景中，会存在多个会话执行同一条查询语句，因此，为了避免重复构造《导读》中所说的 HashMap 的 Key，MySQL 缓存了查询语句的 Key，保证查询 Query Cache 的性能。
    • RESULT：表示这个 block 中存放的是查询结果。如上图，《导读》中 SQL 的查询结果<10001, Jack, I'm Jack>放入 block，所以，block 类型为 RESULT。
  • n_tables：查询语句使用的表的数量。那么，block 又为什么要存表的数量呢？因为 MySQL 会缓存 table 结构，一张 table 对应一个 table 结构，多个 table 结构组成一条链表，MySQL 需要维护这条链表增删改查，所以，需要 n_tables 字段。

现在我们知道了一个query_cache_block的结构了，下面我简称block。

现在有这么一个场景：

已知一个block的大小是 1KB，而《导读》中的查询语句得到的结果记录数有 10w，它的大小有 1MB，那么，显然一个block放不下 1MB 的结果，此时，MySQL 会怎么做呢？

为了能够缓存 1MB 的查询结果，MySQL 设计了一个双向链表，将多个block串联起来，1MB 的数据分别放在链表中多个block里。于是，就有了下面的结构：逻辑块链表。

图中，MySQL 将多个block通过一个双向链表串联起来，每个block就是我上面讲到的block结构。通过双向链表我们就可以将一条查询语句对应的结果集串联起来。

比如针对《导读》中 SQL 的查询结果，图中，前两个 block 分别存放了两个满足查询条件的结果：<10001，Jack，I'm Jack>和<10009，Lisa，I'm Lisa>。同时，两个 block 通过双向指针串联起来。

还是《导读》中的 SQL 案例，已知一个block的大小是 1K，假设 SQL 的查询结果为<10001，Jack，I'm Jack>这一条记录，该记录的大小只有 100Byte，那么，此时查询结果小于block大小，如果把这个查询结果放到 1K 的block里，就会浪费1024-100=924 字节的block空间。所以，为了避免block空间的浪费，MySQL 又引入了一个新结构：

如上图，下面的物理块就是 MySQL 为了解决block空间浪费引入的新结构。该结构也是一个多block组成的双向链表。

以《导读》中的 SQL 为例，已知 SQL 查询的结果为<10001，Jack，I'm Jack>，那么，将逻辑块链表和物理块链表结合起来，这个结果在block中是如何表达的呢？

  • 如上图，逻辑块链表的第一个 block 存放了<10001，Jack，I'm Jack>这个查询结果。
  • 由于查询结果大小为 100B，小于 block 的大小 1K，所以，见上图，MySQL 将逻辑块链表中的第一个 block 分裂，分裂出下面的两个物理块 block，即红色箭头部分，将<10001，Jack，I'm Jack>这个结果放入第一个物理块中。其中，第一个物理块 block 大小为 100B，第二个物理块 block 大小为 924B。

讲完了query_cache_block，我想你应该对其有了较清晰的理解。但是，我在上面多次提到一个 block 的大小，那么，这个 block 的大小又是如何决定的呢？为什么 block 的大小是 1K，而不是 2K，或者 3K 呢？

要回答这个问题，就要涉及 MySQL 对 block 的内存管理了。MySQL 为了管理好 block，自己设计了一套内存管理机制，叫做query_cache_memory_bin。

下面我就详细讲讲这个query_cache_memory_bin。

Query Cache Memory Bin

MySQL 将整个 Query Cache 划分多层大小不同的多个query_cache_memory_bin（简称 bin），如下图：

说明：

  • steps：为层号，如上图中，从上到下分为 0、1、2、3 这 4 层。
  • bin：每一层由多个 bin 组成。其中，bin 中包含以下几个属性：
    • 第 0 层：400K / 1 = 400K
    • 第 1 层：100K / 2 = 50K
    • 第 2 层：25K / 3 = 9K，从最左边的 bin 开始分配大小：
    • 第 3 层：6K / 4 = 2K，从最左边的 bin 开始分配大小：
    • 第 1 个 bin：9K
    • 第 2 个 bin：8K
    • 第 3 个 bin：8K
    • 第 1 个 bin：2K
    • 第 2 个 bin：2K
    • 第 3 个 bin：1K
    • 第 4 个 bin：1K
    • 第 0 层：400K
    • 第 1 层：100K
    • 第 2 层：25K
    • 第 3 层：6K
    • 第 0 层：bin 个数为 1
    • 第 1 层：bin 个数为 2
    • 第 2 层：bin 个数为 3
    • 第 3 层：bin 个数为 4
    • size：bin 的大小
    • free_blocks：空闲的query_cache_block链表。每个 bin 包含一组query_cache_block链表，即逻辑块链表和物理块链表，也就是《Query Cache Block》中我讲到的两个链表组成一组query_cache_block。
    • 每层 bin 的个数通过下面的公式计算得到：bin 个数 = 上一层 bin 数量总和 + QUERY_CACHE_MEM_BIN_PARTS_INC） * QUERY_CACHE_MEM_BIN_PARTS_MUL其中，QUERY_CACHE_MEM_BIN_PARTS_INC = 1 ，QUERY_CACHE_MEM_BIN_PARTS_MUL = 1.2因此，如上图，得到各层的 bin 个数如下：
    • 每层都有其固定大小。这个大小的计算公式如下：第 0 层的大小 = query_cache_size >> QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2 >> QUERY_CACHE_MEM_BIN_STEP_PWR2其余层的大小 = 上一层的大小 >> QUERY_CACHE_MEM_BIN_STEP_PWR2其中，QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2 = 4，QUERY_CACHE_MEM_BIN_STEP_PWR2 = 2因此，假设query_cache_size = 25600K，那么，得到计算各层的大小如下：
    • 每层中的 bin 也有固定大小，但最小不能小于QUERY_CACHE_MIN_ALLOCATION_UNIT。这个 bin 的大小的计算公式采用对数逼近法如下：bin 的大小 = 层大小 / 每一层 bin 个数，无法整除向上取整其中，QUERY_CACHE_MIN_ALLOCATION_UNIT = 512B因此，如上图，得到各层 bin 的大小如下：

通过对 MySQL 管理 Query Cache 使用内存的讲解，我们应该猜到 MySQL 是如何给query_cache_block分配内存大小了。我以上图为例，简单说明一下：

由于每个 bin 中包含一组query_cache_block链表 (逻辑块和物理块链表)，如果一个 block 大小为 1K，这时，通过遍历 bin 找到一个大于 1K 的 bin，然后，把该 block 链接到 bin 中的 free_blocks 链表就行了。具体过程，我在下面会详细讲解。

在了解了query_cache_block、query_cache_memory_bin这两种结构之后，我想你对 Query Cache 在处理时用到的数据结构有了较清晰的理解。那么，结合这两种数据结构，我们再看看 Query Cache 的几种处理场景及实现原理。

Cache 写入

我们结合《导读》中的 SQL，先看一下 Query Cache 写入的过程：

  1. 结合上面 HashMap 的 Key 的结构，根据查询条件age > 20 and age < 50构造 HashMap 的 Key：age > 20 and age < 50 + user + flag，其中 flag 包含了查询结果，将 Key 写入 HashMap。如上图，Result 就是这个 Key。
  2. 根据 Result 对query_cache_mem_bin的层进行二分查找，找到层大小大于 Result 大小的层。如上图，假设第 1 层为找到的目标层。
  3. 根据 Result 从右向左遍历第 1 层的 bin(因为每层 bin 大小从左向右降序排列，MySQL 从小到大开始分配)，计算 bin 中的剩余空间大小，如果剩余空间大小大于 Result 大小，那么，就选择这个 bin 存放 Result，否则，继续向左遍历，直至找到合适的 bin 为止。如上图灰色 bin，选择了第 2 层的第一个 bin 存放 Result。
  4. 根据 Result 从左向右扫描上一步得到的 bin 中的 free_blocks 链表中的逻辑块链表，找到第一个 block 大小大于 Result 大小的 block。如上图，找到第 2 个逻辑块 block。
  5. 假设 Result 大小为 100B，第 2 个逻辑块 block 大小为 1k，由于 block 大于 Result 大小，所以，分裂该逻辑块 block 为 2 个物理块 block，其中，分裂后第一个物理块 block 大小为 100B，第二个物理块 block 大小为 924B。
  6. 将 Result 结果写入第 1 个物理块 block。如上图，将<10001, Jack, I'm Jack>这个 Result 写入灰色的物理块 block。

Cache 失效

当一个表发生改变时，所有与该表相关的 cached queries 将失效。一个表发生变化，包含多种语句，比如 INSERT, UPDATE, DELETE, TRUNCATE TABLE,ALTER TABLE, DROP TABLE, 或者 DROP DATABASE。

Query Cache Block Table

为了能够快速定位与一张表相关的 Query Cache，将这张表相关的 Query Cache 失效，MySQL 设计一个数据结构：Query_cache_block_table。如下图：

我们来看一下这个结构包含的核心属性：

  • block：与一张表相关的query_cache_block链表。如上图是 user 表的query_cache_block_table，该 block 中的 block 属性指向了逻辑块 block 链表，该链表中第 1 个 block 包含《导读》中 SQL 的查询结果<10001, Jack, I'm Jack>。

和 Query Cache 的 HashMap 结构一样，为了根据表名可以快速找到对应的query_cache_block，MySQL 也设计了一个表名跟query_cache_block映射的 HashMap，这样，MySQL 就可以根据表名快速找到query_cache_block了。

通过上面这些内容的讲解，我想你应该猜到了一张表变更时，MySQL 是如何失效 Query Cache 的？

我们来看下上面这张图，关注红线部分：

  1. 根据 user 表找到其对应的query_cache_block_table。如上图，找到第 2 个table block。
  2. 根据query_cache_block_table中的 block 属性，找到 table 下的逻辑块链表。如上图，找到了右侧的逻辑块链表。
  3. 遍历逻辑块链表及每个逻辑块 block 下的物理块链表，释放所有 block。

Cache 淘汰

如果query_cache_mem_bin中没有足够空间的 block 存放 Result，那么，将触发query_cache_mem_bin的内存淘汰机制。

这里我借用《Cache 写入》的过程，一起来看看 Query Cache 的淘汰机制：

  1. 结合上面 HashMap 的 Key 的结构，根据查询条件age > 20 and age < 50构造 HashMap 的 Key：age > 20 and age < 50 + user + flag，其中 flag 包含了查询结果，将 Key 写入 HashMap。如上图，Result 就是这个 Key。
  2. 根据 Result 对query_cache_mem_bin的层进行二分查找，找到层大小大于 Result 大小的层。如上图，假设第 1 层为找到的目标层。
  3. 根据 Result 从右向左遍历第 1 层的 bin(因为每层 bin 大小从左向右降序排列，MySQL 从小到大开始分配)，计算 bin 中的剩余空间大小，如果剩余空间大小大于 Result 大小，那么，就选择这个 bin 存放 Result。如上图灰色 bin，选择了第 2 层的第一个 bin 存放 Result。
  4. 根据 Result 从左向右扫描上一步得到的 bin 中的 block 链表中的逻辑块链表，找到第一个 block 大小大于 Result 大小的 block。如上图，找到第 2 个逻辑块 block。
  5. 假设 Result 大小为 100B，第 2 个逻辑块 block 大小为 1k，由于 block 大于 Result 大小，所以，分裂该逻辑块 block 为 2 个物理块 block，其中，分裂后第一个物理块 block 大小为 100B，第二个物理块 block 大小为 924B。
  6. 由于第 1 个物理块 block 已经被占用，所以，MySQL 不得不淘汰该 block，用以放入 Result，淘汰过程如下：
  7. 发现相邻的第 2 个物理块 block 最少使用，所以，将该物理块和第 1 个物理块 block 合并成一个新 block。如上图右侧灰色 block 和虚线 block 合并成下面的一个灰色 block。
  8. 将 Result 结果写入合并后的物理块 block。如上图，将<10001, Jack, I'm Jack>这个 Result 写入合并后的灰色 block。

在 Cache 淘汰这个场景中，我们重点关注一下第 6 步，我们看下这个场景：

  1. 从第 1 个物理块 block 开始扫描，合并相邻的第 2 个 block 跟第 1 个 block 为一个新 block
  2. 如果合并后 block 大小仍然不足以存放 Result，继续扫描下一个 block，重复第 1 步
  3. 如果合并后 block 大小可以存放 Result，结束扫描
  4. 将 Result 写入合并后 block

通过上面的场景描述，我们发现如果 Result 很大，那么，MySQL 将不断扫描物理块 block，然后，不停地合并 block，这是不小的开销，因此，我们要尽量避免这样的开销，保证 Query Cache 查询的性能。

有什么办法避免这样的开销呢？

我在最后小结的时候回答一下这个问题。

小结

好了，这篇内容我讲了很多东西，现在，我们来总结一下今天讲解的内容：

  1. 数据结构：讲解了 Query Cache 设计的数据结构：数据结构说明Query_cache_block存放了一条 SQL 的查询结果Query_cache_mem_binquery_cache_block 的内存管理结构Query_cache_block_table一张表对应一个 block_table，方便快速失效 query cache
  2. Query Cache 处理的场景：Cache 写入、Cache 失效和 Cache 淘汰。

最后，我们再回头看一下文章开头的那个问题：10w 条用户记录是否可以写入 Query Cache？我的回答是：

  1. 我们先对用户表的 10w 记录大小做个计算：用户表包含 user_id(8)，user_name(29)，user_introduction(498)，age(3)，sex(1) 这几个字段，按字段顺序累加，一条记录的长度为 8+30(varchar 类型长度可以多存储 1 或 2byte)+500+3+1=542byte，那么，10w 条记录最大长度为542 * 10w = 54200000byte。如果要将 10w 条记录写入 Query Cache，则需要将近 54200K 大小的 Query Cache 来存储这 10w 条记录，而 Query Cache 大小默认为 1M，所以，如果字段 user_introduction 在业务上非必须出现，请在 select 子句中排除该字段，减少查询结果集的大小，使结果集可以完全写入 Query Cache，** 这也是为什么 DBA 建议开发不要使用 select 的原因，但是如果 select 取出的字段都不大，查询结果可以完全写入 Query Cache，那么，后续相同查询条件的查询性能也是会提升的，。
  2. 调大query_cache_size这个 MySQL 配置参数，如果业务上一定要求 select 所有字段，而且内存足够用，那么，可以将query_cache_size调至可以容纳 10w 条用户记录，即 54200K。
  3. 调大query_cache_min_res_unit这个 MySQL 配置参数，使 MySQL 在第一次执行查询并写入 Query Cache 时，尽可能不要发生过多的 bin 合并，减少物理块 block 链表的合并开销。那么，query_cache_min_res_unit调成多少合适呢？这需要结合具体业务场景综合衡量，比如，在用户中心系统中，一般会有一个会员中心的功能，而这个功能中，用户查询自己的信息是一个高频的查询操作，为了保证这类操作的查询性能，我们势必会将这个查询结果，即单个用户的基本信息写入 Query Cache，在我的回答的第 1 条中，我说过一条用户记录最大长度为 542byte，结合 10w 条用户记录需要 54200K 的 Query Cache，那么，设置query_cache_min_res_unit = 542byte就比较合适了。这样，有两点好处：
    1. 保证查询单个用户信息，其直接可分配的 bin 大小大于 542byte，写入单个用户信息时可以避免了 bin 的合并和空间浪费。
    2. 10w 条用户记录写入 Query Cache，虽然第一次分配缓存时，仍然需要合并 bin，但是，综合单用户查询的场景，这个合并过程是可以接受的，毕竟，只会在第一次写缓存时发生 bin 合并，后续缓存失效后，再次分配时，可以直接取到合并后的那个 bin 分配给 10w 条记录，不会再产生 bin 的合并，所以，这个合并过程是可以接受的。
  4. 调大query_cache_limit这个 MySQL 配置参数，我在本章节中没有提到这个参数，它是用来控制 Query Cache 最大缓存结果集大小的，默认是 1M，所以，10w 条记录，建议调大这个参数到 54200K。

思考题

最后，对比前面《告诉面试官，我能优化 groupBy，而且知道得很深！》这篇文章，发现 MySQL 特别喜欢自己实现内存的管理，而不用 Linux 内核的内存管理机制 (比如：伙伴系统)，为什么呢？

The End

如果你觉得写得不错，记得点赞哦！

Tags：

猜你喜欢

最近发表
标签列表