| 
咱们通常听到说尽量不要使用 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 淌若你认为写得可以麻豆 周处除三害，谨记点赞哦！
|
