咱们频频听到说尽量不要使用 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 个数为 4size：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 写入的流程：

结合上头 HashMap 的 Key 的结构，把柄查询条目age > 20 and age < 50构造 HashMap 的 Key：age > 20 and age < 50 + user + flag，其中 flag 包含了查询拆伙，将 Key 写入 HashMap。如上图，Result 便是这个 Key。把柄 Result 对query_cache_mem_bin的层进行二分查找，找到层大小大于 Result 大小的层。如上图，假定第 1 层为找到的方针层。把柄 Result 从右向左遍历第 1 层的 bin(因为每层 bin 大小从左向右降序摆列，人妻熟女MySQL 从小到大运行分拨)，策动 bin 中的剩余空间大小，要是剩余空间大小大于 Result 大小，那么，就采选这个 bin 存放 Result，不然，连接向左遍历，直至找到恰当的 bin 为止。如上图灰色 bin，采选了第 2 层的第一个 bin 存放 Result。把柄 Result 从左向右扫描上一步得到的 bin 中的 free_blocks 链表中的逻辑块链表，找到第一个 block 大小大于 Result 大小的 block。如上图，找到第 2 个逻辑块 block。假定 Result 大小为 100B，第 2 个逻辑块 block 大小为 1k，由于 block 大于 Result 大小，是以，分裂该逻辑块 block 为 2 个物理块 block，其中，分裂后第一个物理块 block 大小为 100B，第二个物理块 block 大小为 924B。将 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 的？

咱们来看下上头这张图，关怀红线部分：

把柄 user 表找到其对应的query_cache_block_table。如上图，找到第 2 个table block。把柄query_cache_block_table中的 block 属性，找到 table 下的逻辑块链表。如上图，找到了右侧的逻辑块链表。遍历逻辑块链表及每个逻辑块 block 下的物理块链表，开释总共 block。

Cache 淘汰

要是query_cache_mem_bin中莫得富足空间的 block 存放 Result，那么，将触发query_cache_mem_bin的内存淘汰机制。

这里我借用《Cache 写入》的流程，一说念来望望 Query Cache 的淘汰机制：

结合上头 HashMap 的 Key 的结构，把柄查询条目age > 20 and age < 50构造 HashMap 的 Key：age > 20 and age < 50 + user + flag，其中 flag 包含了查询拆伙，将 Key 写入 HashMap。如上图，Result 便是这个 Key。把柄 Result 对query_cache_mem_bin的层进行二分查找，找到层大小大于 Result 大小的层。如上图，假定第 1 层为找到的方针层。把柄 Result 从右向左遍历第 1 层的 bin(因为每层 bin 大小从左向右降序摆列，MySQL 从小到大运行分拨)，策动 bin 中的剩余空间大小，要是剩余空间大小大于 Result 大小，那么，就采选这个 bin 存放 Result。如上图灰色 bin，采选了第 2 层的第一个 bin 存放 Result。把柄 Result 从左向右扫描上一步得到的 bin 中的 block 链表中的逻辑块链表，找到第一个 block 大小大于 Result 大小的 block。如上图，找到第 2 个逻辑块 block。假定 Result 大小为 100B，第 2 个逻辑块 block 大小为 1k，由于 block 大于 Result 大小，是以，分裂该逻辑块 block 为 2 个物理块 block，其中，分裂后第一个物理块 block 大小为 100B，第二个物理块 block 大小为 924B。由于第 1 个物理块 block 还是被占用，是以，MySQL 不得不淘汰该 block，用以放入 Result，淘汰流程如下：

发现相邻的第 2 个物理块 block 最少使用，是以，将该物理块和第 1 个物理块 block 合并成一个新 block。如上图右侧灰色 block 和虚线 block 合并成底下的一个灰色 block。将 Result 拆伙写入合并后的物理块 block。如上图，将<10001， Jack， I'm Jack>这个 Result 写入合并后的灰色 block。在 Cache 淘汰这个场景中，咱们要点关怀一下等 6 步，咱们看下这个场景：

从第 1 个物理块 block 运行扫描，合并相邻的第 2 个 block 跟第 1 个 block 为一个新 block要是合并后 block 大小仍然不及以存放 Result，连接扫描下一个 block，相通第 1 步要是合并后 block 大小可以存放 Result，扫尾扫描将 Result 写入合并后 block

通过上头的场景格式，咱们发现要是 Result 很大，那么，MySQL 将连接扫描物理块 block，然后，箝制地合并 block，这是不小的支拨，因此，咱们要尽量幸免这样的支拨，保证 Query Cache 查询的性能。

有什么主义幸免这样的支拨呢？

我在临了小结的技巧恢复一下这个问题。

小结

好了，这篇本色我讲了许多东西，目下，咱们来总结一下今天教授的本色：

数据结构：教授了 Query Cache 联想的数据结构：

Query Cache 处理的场景：Cache 写入、Cache 失效和 Cache 淘汰。

临了，咱们再回头看一下著作源头的阿谁问题：10w 条用户记载是否可以写入 Query Cache？我的恢复是：

咱们先对用户表的 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，那么，后续相通查询条目的查询性能亦然会擢升的，?。

调大query_cache_size这个 MySQL 配置参数，要是业务上一定要求 select 总共字段，何况内存富足用，那么，可以将query_cache_size调至可以容纳 10w 条用户记载，即 54200K。

调大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就相比恰当了。这样，有两点公正：

幼幼嫩穴

保证查询单个用户信息，其获胜可分拨的 bin 大小大于 542byte，写入单个用户信息时可以幸免了 bin 的合并和空间铺张。10w 条用户记载写入 Query Cache，天然第一次分拨缓存时，仍然需要合并 bin，然则，概述单用户查询的场景，这个合并流程是可以给与的，毕竟，只会在第一次写缓存时发生 bin 合并，后续缓存失效后，再次分拨时，可以获胜取到合并后的阿谁 bin 分拨给 10w 笔记载，不会再产生 bin 的合并，是以，这个合并流程是可以给与的。

调大query_cache_limit这个 MySQL 配置参数，我在本章节中莫得提到这个参数，它是用来限定 Query Cache 最大缓存拆伙集大小的，默许是 1M，是以，10w 笔记载，提倡调大这个参数到 54200K。

念念考题

临了，对比前边《告诉口试官，我能优化 groupBy，何况知说念得很深！》这篇著作，发现 MySQL 至极心爱我方完毕内存的管制，而无须 Linux 内核的内存管制机制 (比如：伙伴系统)，为什么呢？

The End

要是你以为写得可以暴力小说，铭刻点赞哦！