mysql introduction - yaokun123/php-wiki GitHub Wiki
mysql 入门指南
1、小白眼中的 MySQL
首先，对于我们来说，MySQL 是个啥？我们从一个最简单的例子来回顾一下。
这可能就是最开始大家认知中的 MySQL。那 MySQL 中是怎么处理这个查询语句的呢？换句话说，它是如何感知到这串字符串是一个查询语句的？它是如何感知到该去哪张表中取数据？它是如何感知到该如何取数据？
到目前为止，都不知道。接下来我们一步一补来进行解析。
2、连接池
首先，要去 MySQL 执行命令，肯定是需要连接上 MySQL 服务器的，就像我们通过「用户名」和「密码」登陆网站一样。所以，我们首先要认识的就是连接池。
这种池化技术的作用很明显，复用连接，避免频繁的销毁、创建线程所带来的开销。除此之外，在这一层还可以根据你的账号密码对用户的合法性、用户的权限进行校验。
每一个连接都对应一个线程，「服务器」 和 「MySQL」 都一样，服务器的一个线程从服务器的连接池中取出一个连接，发起查询语句。MySQL 服务器的线程从连接池中取出一个线程，继续后续的流程。
那么后面的流程是啥呢？当然是分析 SQL 语句了。
3、分析器
很明显，MySQL 肯定得知道这个 SQL 是不是个合法的 SQL 语句，以及 SQL 语句到底要干啥？
就好像有个哥们疯狂的敲你家门，门打开了，下一步是干嘛？肯定得问他是谁？来干嘛？
所以，下一步就是要将 SQL 进行解析。解析完成之后，我们就知道当前的 SQL 是否符合语法，它到底要干嘛，是要查询数据？还是要更新数据？还是要删除数据？
很简单，我们肉眼能能明显看出来一条 SQL 语句是要干嘛。但电脑不是人脑，我们得让电脑也能看懂这条 SQL 语句，才能帮我们去做后面的事。
知道了这个 SQL 语言要干嘛之后，是不是就可以开始执行操作了呢？
并不是
4、优化器
MySQL 除了要知道这条 SQL 要干嘛，在执行之前，还得决定怎么干，怎么干是最优解。
5、执行器
执行器会掉用底层存储引擎的接口，来真正的执行 SQL 语句，在这里的例子就是查询操作。
至此，MySQL 这个黑盒子已经被我们一步一步的揭开了面纱。但是在揭开最后一片面纱的时候，我们又发现了新的黑盒子。那就是存储引擎。
我们到目前为止，就只知道它的名字，至于其如何存储数据，如何查询数据，一概不知。
6、存储引擎
MySQL 的存储引擎有很多的种类，分别适用于不同的场景。大家可以这么理解，存储引擎就是一个执行数据操作的接口（Interface），而底层的具体实现有很多类。
InnoDB、MyISAM、Memory、CSV、Archive、Blackhole、Merge、Federated、Example
用的最广泛的，就是 InnoDB 了。打从 MySQL 5.5 之后，InnoDB 就是 MySQL 默认的存储引擎了。
存储引擎可以分为两部分：内存 和 磁盘
所以，从宏观上来说，MySQL 就是把数据在缓存在内存中，鼓捣鼓捣，然后在某些时候刷入磁盘中去，就这么回事。
接下来，就让我们深入存储引擎 InnoDB 的底层原理中相当重要的一部分——内存架构。
简单来说，InnoDB 的内存由以下两部分组成：Buffer Pool 和 Log Buffer
从上面画的图就能够看出，Buffer Pool 是 InnoDB 中非常重要的一部分，MySQL 之所以这么快其中一个重要的原因就是其数据都存在内存中，而这个内存就是 Buffer Pool。
7、Buffer Pool
一般来说，宿主机的 80% 的内存都会分配给 Buffer Pool。这个很好理解，内存越大，就能够存下更多的数据。这样一来更多的数据将可以直接在内存中读取，可以大大的提升操作效率。
那 Buffer Pool 中到底是如何存储数据的呢？如果其底层的数据存储不进行特殊的设计、优化，那么 InnoDB 在取数据的时候除了整个遍历之外，没有其他的捷径。而如果那样做，MySQL 也不会获得今天这样的地位。
页
如果我们能想象一下，InnoDB 会如何组织内存的数据。想象一下，图书馆的书是直接一本一本的摊在地上好找，还是按照类目、名称进行分类、放到对应的书架上、再进行编号好找？结论自然不言而喻。Buffer Pool 也采用了同样的数据整合措施。
InnoDB 将 Buffer Pool 分成了一张一张的页（Pages），同时还有个 Change Buffer（后面会详细讲，这里先知道就行）。分成一页一页的数据就能够提升查询效率吗？那这个页里面到底是个啥呢？
可以从上图看到，页和页之间，实际上是有关联的，他们通过双向链表进行连接，可以方便的从某一页跳到下一页。
那数据在页中具体是如何存储的呢？
User Records
当然，光跳来跳去的并不能说明任何问题，我们还是揭开页（Pages）这个黑盒的面纱吧。
可以看到，主要就分为：上一页指针 、 下一页指针、User Records、其余字段
上一页指针、下一页指针就不多赘述，就是一个指针。重点我们需要了解 User Records。
User Records 就是一行一行的数据**（Rows）最终存储的地方，其中，行与行之间形成了单向链表**。
我们知道，在聚簇索引中，Key 实际上会按照 Primary Key 的顺序来进行排列。那在 User Records 中也会这样吗？我们插入一条新的数据到 User Records 中时，是否也会按照 Primary Key 的顺序来对已有的数据重排序？
如果每次插入数据都需要对 User Records 中的数据进行重排序，那么 MySQL 的江湖地位将再次不保。
虽然在图中看起来是按照「主键」的顺序存储的，但实际上是按照数据的插入顺序来存储的，先到的数据会在前面，后到的数据会在后面，只是每个 User Records 数据的指针指向的不是物理上的下一个，而是逻辑上的下一个。
看到这，那么问题来了，说好的不遍历呢？这不是打脸吗？因为从上图可以看出，我要找查找某个数据是否存在于当前的页（Pages）中，只能从头开始遍历这个单向链表。
就这？还敢号称高性能？当然，InnoDB 肯定不是这么搞的。这下就需要从「其余字段」中取出一部分字段了来解释了。
Infimum 和 Supremum
分别代表当前页（Pages）中的最大和最小的记录。
可以看到，有了 Infimum 和 Supremum，我们不需要再去遍历 User Records 就能够知道，要找的数据是否在当前的页中，大大的提升了效率。
但其实还是有问题，比如我需要查询的数据不在当前页中还好，那如果在呢？那是不是还是逃不了 O(N) 的链表遍历呢？算不算治标不治本？
这个时候，我们又需要从「其余字段」中抽一个概念出来了。
Page Directory
顾名思义，这玩意儿是个「目录」，可以看下图。
可以看到，每隔一段记录就会在 Page Directory 中有个记录，这个记录是一个指向 User Records 中记录的一个指针。
不知道这个设计有没有让你想起跳表（Skip List）。那这个 Page Directory 中的目录拿来干嘛呢？
有了 Page Directory，就可以对一页中的数据进行类似于跳表的中的查询。在 Page Directory 中找到对应的「位置」之后，再根据指针跳到对应的 User Records 上的单链表，进行查询。如此一来就避免了遍历全部的数据。
上面提到的「位置」，其实有个专业的名词叫「槽位（Slots）」。每一个槽位的数据都是一个指向了 User Records 某条记录的指针。
当我们新增每条数据的时候，就会同步的对 Page Directory 中的槽位进行维护。InnoDB 规定每隔 6 条记录就会创建一个 Slot。
Change Buffer
聊完了 Buffer Pool 中索引相关，剩下的就是 Change Buffer 了。Change Buffer是一块比较特殊的区域，其作用是用于存储那些当前不在 Buffer Pool 中的但是又被修改过的二级索引。
用流程来描述一下就是，当我们更新了非聚簇索引（二级索引）的数据时，此时应该是直接将其在Buffer Pool中的对应数据更新了即可，但是不凑巧的是，当前二级索引不在 Buffer Pool 中，此时将其从磁盘拉取到 Buffer Pool 中的话，并不是最优的解，因为该二级索引可能之后根本就不会被用到，那么刚刚昂贵的磁盘I/O操作就白费了。
所以，我们需要这么一个地方，来暂存对这些二级索引所做的改动。当被缓存的二级索引页被其他的请求加载到了Buffer Pool 中之后，就会将 Change Buffer 中缓存的数据合并到 Buffer Pool 中去。
当然，Change Buffer也不是没有缺点。当 Change Buffer 中有很多的数据时，全部合并到Buffer Pool可能会花上几个小时的时间，并且在合并的期间，磁盘的I/O操作会比较频繁，从而导致部分的CPU资源被占用。
那你可能会问，难道只有被缓存的页加载到了 Buffer Pool 才会触发合并操作吗？那要是它一直没有被加载进来，Change Buffer 不就被撑爆了？很显然，InnoDB在设计的时候考虑到了这个点。除了对应的页加载，提交事务、服务停机、服务重启都会触发合并。
过期策略
首先明确一点，此处的LRU算法和我们传统的LRU算法有一定的区别。为什么呢？因为实际生产环境中会存在全表扫描的情况，如果数据量较大，可能会将Buffer Pool中存下来的热点数据给全部替换出去，而这样就会导致该段时间MySQL性能断崖式下跌。
对于这种情况，MySQL有一个专用名词叫缓冲池污染。所以MySQL对LRU算法做了优化。
优化后的LRU 优化之后的链表被分成了两个部分，分别是 New Sublist 和 Old Sublist，其分别占用了 Buffer Pool 的5/8和3/8。
链表的前5/8，也就是 New Sublist 存放的是访问较为频繁的页，而后3/8也就是 Old Sublist 则是反问的不那么频繁的页。Old Sublist中的数据，会在后续Buffer Pool剩余空间不足、或者有新的页加入时被移除掉。
了解了链表的整体构造和组成之后，我们就以新页被加入到链表为起点，把整体流程走一遍。首先，一个新页被放入到Buffer Pool之后，会被插入到链表中 New Sublist 和 Old Sublist 相交的位置，该位置叫MidPoint。
默认情况下，由用户操作影响而进入到Buffer Pool中的数据，会被立即放到链表的最前端，也就是 New Sublist 的 Head 部分。但如果是MySQL启动时预加载的数据，则会放入MidPoint中，如果这部分数据被用户访问过之后，才会放到链表的最前端。
这样一来，虽然这些页数据在链表中了，但是由于没有被访问过，就会被移动到后1/4的 Old Sublist中去，直到被清理掉。
8、Log Buffer
Log Buffer用来存储那些即将被刷入到磁盘文件中的日志，例如Redo Log，该区域也是InnoDB内存的重要组成部分。Log Buffer的默认值为16M，如果我们需要进行调整的话，可以通过配置参数innodb_log_buffer_size来进行调整。
当Log Buffer如果较大，就可以存储更多的Redo Log，这样一来在事务提交之前我们就不需要将Redo Log刷入磁盘，只需要丢到Log Buffer中去即可。因此较大的Log Buffer就可以更好的支持较大的事务运行；同理，如果有事务会大量的更新、插入或者删除行，那么适当的增大Log Buffer的大小，也可以有效的减少部分磁盘I/O操作。
至于Log Buffer中的数据刷入到磁盘的频率，则可以通过参数innodb_flush_log_at_trx_commit来决定。