关于mysql:谁说不能使用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中蕴含以下几个属性：
- 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写入的过程：

联合下面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。
依据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的？

咱们来看下下面这张图，关注红线局部：

依据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_block	寄存了一条SQL的查问后果
Query_cache_mem_bin	query_cache_block的内存治理构造
Query_cache_block_table	一张表对应一个block_table，不便疾速生效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就比拟适合了。
这样，有两点益处：
1. 保障查问单个用户信息，其间接可调配的bin大小大于542byte，写入单个用户信息时能够防止了bin的合并和空间节约。
2. 10w条用户记录写入Query Cache，尽管第一次调配缓存时，依然须要合并bin，然而，综合单用户查问的场景，这个合并过程是能够承受的，毕竟，只会在第一次写缓存时产生bin合并，后续缓存生效后，再次调配时，能够间接取到合并后的那个bin调配给10w条记录，不会再产生bin的合并，所以，这个合并过程是能够承受的。
调大query_cache_limit这个MySQL配置参数，我在本章节中没有提到这个参数，它是用来管制Query Cache最大缓存后果集大小的，默认是1M，所以，10w条记录，倡议调大这个参数到54200K。

思考题

最初，比照后面《通知面试官，我能优化groupBy，而且晓得得很深！》这篇文章，发现MySQL特地喜爱本人实现内存的治理，而不必Linux内核的内存管理机制(比方：搭档零碎)，为什么呢？

The End

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