高级数据库学习

参考资料：
[1]. What does eq_ref and ref types mean in MySQL explain
[2]. MySQL 是怎样运行的：从根儿上理解 MySQL
[3]. 超全面MySQL语句加锁分析（上篇）（求转）
[4]. 超全面MySQL语句加锁分析（中篇）（求转）
[5]. 超全面MySQL语句加锁分析（下篇）（求转）

B+树索引

索引是为了快速查找记录，针对数据页做的简易目录就叫做索引。key，是对应页的所有记录中最小的主键值，page_no是没有必要从小到大连续的，页已经根据目录项的顺序在逻辑上连续了，前面的页的记录都小于后面页的记录，这样我们查找的时候首先在索引上进行二分法查找到页的位置，然后再在页里根据槽再进行二分查找。

上面的方法带来了两个问题：
1.记录非常多的情况下，页的数量也非常多，索引的数量也非常多。
2.删除页的时候删除掉目录项，因为索引是用数组来表示的，需要移动较多的元素。
因此索引页根数据一样用页来存储，每条索引页当做记录一样。

当记录太多的时候，用多个索引页来存放索引

索引页为了快速查找也需要进行索引，这样就变成了嵌套目录了...

于是这样就变成了B+树，树的非叶子节点是数据页的索引，叶子节点是数据页。

• 表空间
  页(64KB)----64---->区(1MB)----256---->组(256MB)
  区是为了连续的64个页，为了在取相邻数据的时候减少随机IO
  段：一颗B+树的叶子节点和非叶子节点分为两个段，一个索引两个段，段是以区为单位的，但是这样小的表占用的空间太大，所以很小的表开始分配的时候是从碎片区（碎片区直属于表空间，并不属于任何一个段）中获取单独的页，当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面（占用少于32个碎片区页面的时候）或者一些完整的区的集合。

表空间可以看做一个内存池，区是连续的页，所以是为了数据能够连续存储而设置的存储概念。

IN子查询

IN查询的时候会先把子查询建立为临时表（可能放在内存，也看放在硬盘），然后设置索引，再去查主查询。这样是为了防止内存不够，建立索引也可以加快主查询。IN的时候我们会想到用哈希来做比较快，所以临时表建在内存的时候，索引也是建的哈希索引。
建立了临时表之后，接下来两个表进行查询的时候实际上可以转化为内连接，可以转而用内连接的优化方法来进行优化查询。
既然可以转化为内连接，那么一开始能不能不建立临时表，直接进行内连接呢？但是直接连接后第一个表会重复多次，而且我们只在乎这边的记录是否有对应的存在在另外一个表中，因为我们是用IN，所以我们提出了下面半连接的概念。
将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。半连接是MYSQL内部的概念。
下面这两句是相同的

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

半连接

SELECT s1.* FROM s1 SEMI JOIN s2
    ON s1.key1 = s2.common_field
    WHERE key3 = 'a';

• IN子查询的转化
  如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询。

• 派生表的优化
  MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。

• Buffer Pool 磁盘的缓存地带
  在内存中分配一大块内存，切分成页的大小，然后每次加载磁盘的某个页到其中一个页，下次再遇到同样的页不必到磁盘中读取，说白了就是缓存。
  每次在内存中更新完数据之后，页面就变成了脏页，为了减少写入磁盘的次数，每次内存修改完并不会马上写入到磁盘。

• 事务与数据恢复
  数据库数据写入的过程：首先是将数据写入到内存Buffer Pool，然后在内存中的log buffer中写入修改的页面和内容，然后适时将log buffer同步到磁盘log file，用log buffer是为了减少数据页面的刷新，可能数据页面只是修改一两个字节，那么用log buffer记录起来就很省空间。我们只是把数据写入到内存Buffer Pool，所以数据实际上还没有持久化，我们修改的记录在内存log buffer和磁盘log file中，但只要我们也就写入到磁盘log file，我们后面就有办法重新恢复。如果Buffer Pool对应的页面也就写入到磁盘，那么log buffer和log file的内容就会被删除，没有必要存在了。
  如果发生了异常，我们可以从log file中读取出修改的记录，然后进行对页面进行修改。

• IS锁与IX锁
  相比较S锁和X锁，IS锁和IX锁是只是一个标志，是为了拦住真正的S锁和X锁，所以IS锁和IX锁之间是兼容的，所以有如下的兼容关系。

	IS	IX
S	兼容	不兼容
X	不兼容	不兼容

IS和IX之间都是标志，表示真正的锁，所以都是互相兼容的。

	IS	IX
IS	兼容	兼容
IX	兼容	兼容

学生在教学楼门口加IS锁时，是不关心教学楼门口是否有IX锁的，维修工在教学楼门口加IX锁时，是不关心教学楼门口是否有IS锁或者其他IX锁的。IS和IX锁只是为了判断当前时间教学楼里有没有被占用的教室用的，也就是在对教学楼加S锁或者X锁时才会用到。

• 锁结构

  
  

• 死锁
  两个事务中的两条查找语句分别查找并加锁，第一条利用的是二级索引进行查找， 第二条利用的是主键在聚簇索引中查找，查找出来的记录主键相同，第一条先对二级索引进行加锁，第二条对聚簇索引进行加锁，接着他们都要继续分别对聚簇索引和二级索引的记录进行加锁，这个时候就形成了死锁，双方都持有一个锁，都要分别获取对方的锁。
  死锁之后，两个事务都无法运行下去，必须选择一个进行回滚，对性能影响比较大。

• READ UNCOMMITTED/READ COMMITTED隔离级别加锁总结
  具体参照 思维导图 和[3]

韦恩图的意思要读懂：
A与B圆相交，从左到右分别是
A.key 独有
A.key and B.key 都有
B.key 独有
然后将这些拿去join
以左上角的图为例，A left join B的意思就是，A的全部加上A和B的共同部分key。
左下的图，在左连接的基础上去掉中间部分，然后取B.key为null部分，中间部分B肯定不为null，因为是A.key和B.key共有的部分。
最下面全部都有的这张图，语法上因为没有full outer join，所以可以用union (distinct)把左连接和右连接组合起来。

• 主从复制

  
  
  

• mycat
  数据库中间件：连接数据库和java程序（也可以说是用户）
  干什么：1、读写分离。 2、数据分片。3、多数据源整合
  有了mycat中间件，数据库的配置可以放在mycat，java程序只需要跟mycat进行互动即可，多了个中间层，数据库变化的时候，部署起来很方便，java程序不需要重新部署。
  垂直拆分和水平拆分：
  分库：
  超过500百万左右条，MYSQL有压力，需要分开
  原则：表之间不会互相关联
  分表：
  跨库join-ER：在mycat中备注关联的主键，主需要配置主表的分表方式，另外一个表会根据关联的主键选择对应的库。

• 全局表
  全局表：每个库都要用，但是比较小，每个库都备份一个。