关于mysql:谁说不能使用select

33次阅读

共计 9321 个字符，预计需要花费 24 分钟才能阅读完成。

导读

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

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 中蕴含以下几个属性：

    • 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 层：bin 个数为 1
      • 第 1 层：bin 个数为 2
      • 第 2 层：bin 个数为 3
      • 第 3 层：bin 个数为 4

    • 每层都有其固定大小。这个大小的计算公式如下：

      第 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，那么，失去计算各层的大小如下：

      • 第 0 层：400K
      • 第 1 层：100K
      • 第 2 层：25K
      • 第 3 层：6K

    • 每层中的 bin 也有固定大小，但最小不能小于 QUERY_CACHE_MIN_ALLOCATION_UNIT。这个 bin 的大小的计算公式采纳 对数迫近法 如下：

      bin 的大小 = 层大小 / 每一层 bin 个数，无奈整除向上取整

      其中，QUERY_CACHE_MIN_ALLOCATION_UNIT = 512B

      因而，如上图，失去各层 bin 的大小如下：

      • 第 0 层：400K / 1 = 400K
      • 第 1 层：100K / 2 = 50K

      • 第 2 层：25K / 3 = 9K，从最右边的 bin 开始调配大小：

        • 第 1 个 bin：9K
        • 第 2 个 bin：8K
        • 第 3 个 bin：8K

      • 第 3 层：6K / 4 = 2K，从最右边的 bin 开始调配大小：

        • 第 1 个 bin：2K
        • 第 2 个 bin：2K
        • 第 3 个 bin：1K
        • 第 4 个 bin：1K

通过对 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。
  7. 依据 Result 所在的 block，找到对应的 block_table，更新 table 信息到 block_table 中。

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。如下图：

这是一个双向链表，对于一条 SQL，如果蕴含多表联接，那么，就能够将这条 SQL 对应多张表链接起来，再插入这张链表，比方，咱们把 user 和t_user_view(访客表)联接，查问用户访客信息，那么，在图中，假如逻辑块链表寄存就是联表查问的后果，因而，咱们就看到 user 表和 t_user_view 都指向了该逻辑块链表。

咱们来看一下这个构造蕴含的外围属性：

  • block：与一张表相干的 query_cache_block 链表。如上图是 user 表的query_cache_block_table，该 block 中的 block 属性指向了逻辑块 block 链表，该链表中第 1 个 block 蕴含《导读》中 SQL 的查问后果<10001, Jack, I'm Jack>。
  • table：同样以 user 和t_user_view(访客表)联接，查问用户访客信息为例，这时，我对这个访客信息创立了视图，那么，MySQL 如何表白表的关系呢？为了解决这个问题，MySQL 引入了 table，通过这个 table 记录视图信息，视图起源表都指向这个 table 来表白表的关系。如上图，user和 t_user_view 都指向了 user_view，来示意user 和t_user_view(访客表)对应的视图是user_view。

和 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，淘汰过程如下：

    • 发现相邻的第 2 个物理块 block 起码应用，所以，将该物理块和第 1 个物理块 block 合并成一个新 block。如上图右侧灰色 block 和虚线 block 合并成上面的一个灰色 block。
  7. 将 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_bin query_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

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

正文完
mysql
发表至： mysql
2021-06-19
 0
关于mysql:HTAP-for-MySQL-在腾讯云数据库的演进
关于mysql:手把手教你用策略模式-写echarts的配置项option
关于mysql:MySQL-大批量插入如何过滤掉重复数据
关于mysql:关于MVCC的一些理解20220115