深入原理64式：27 mysql知识总结

目标:
整理mysql知识，主要包含如下内容:
1、索引
2、锁
3、分库分表
4、高可用与复制
5、两阶段提交与三阶段提交
6、事务/隔离级别/范式/内外连接/触发器
7、优化
8、sql基础

第一部分: 索引

1 B树
1.1 基础知识
含义: 多路平衡查找树
阶: B树中所有结点的孩子结点数的最大值，用m表示
特点:
1) 树的每个结点至多有m棵子树(m-1个关键字)
2) 若根结点不是终端结点，则至少有两棵子树
3) 除根节点以外的所有非叶结点至少有m/2向上取整棵子树
4) 所有非叶节点结构如下:
n Po K1 P1 K2 ... Kn Pn
解释:
K表示关键字，P表示指向子树根节点的指针，n为结点中关键字个数。
满足: Pi-1所指子树的所有结点的关键字都小于Ki, Pi所指子树的所有结点的关键字都大于Ki。
5) 所有叶节点都出现在同一层次上，并且不带信息。表示是查找失败结点。

总结: m棵子树，非叶节至少有m/2向上取整棵子树，Pi-1所指节点<Ki<Pi所指节点

三阶B树样例:
                    18 33
               
        12            23 30            48
       
    10        15    20 21 24    31    45 47 50 52
  空  空  空 空 空空空空空空空空空空空空空空空空

 
1.2 B树高度(磁盘存取次数)
B树的高度不包括最后的不带任何信息的叶节点所处的那一层。
若n>=1,对任意一棵包含n个关键字，高度为h,阶数为m的B树:
1) 每个结点最多有m棵子树, m-1个关键字，满足:
n <= (m-1)*(1 + m + m^2 + ... + m^(h-1)) = m^h - 1
即: h >= LOGm(n+1)

2) 若每个结点中关键字个数最少，第一层有1个结点，第二层有2个结点，
除根节点以外的每个非终端结点至少有m/2向上取整棵子树，第三层至少有2[m/2],
第h+1层至少有2([m/2])^(h-1)，对于关键字个数为n的B树，叶节点即查找不成功的结点为
n+1,n+1>=2([m/2])^(h-1)，即 h <= LOG[m/2]((n+1)/2) + 1

1.3 B树的查找
每个结点都是多个关键字的有序表，根据该结点的子树所做的多路分支决定。
B树查找包含:
1)在B树种找结点
2)在结点内找关键字(可采用折半查找等于K的关键字)
查找到某个结点后，先在有序表中进行查找，若找到则查找成功，否则按照对应指针信息到所
指的子树中取查找。当查找到叶节点，则查找失败。

1.4 B树的插入
1) 定位
利用B树查找算法，找出插入该关键字的最底层中某个非叶节点
2) 插入
每个非失败结点的关键字个数都在[m/2向上取整-1, m-1]之间。
当插入的关键字个数<=m-1,则直接插入；否则，需要进行分裂
3) 分裂
取一个新结点，将插入key后的原结点从中间位置将关键分为两部分，
左部分包含的关键字放在原结点，
右部分包含的关键字放在新结点，
中间位置m/2的结点插入到原结点的父节点中。若父节点关键字个数也超出上限，则继续分裂。

样例:
m为3的B树如下:
    30
20        50 52
插入60
    30
20        50 52 60
分裂后
    30 52
20      50    60

1.5 B树的删除
要使得删除后的结点中的关键字个数>=m/2向上取整-1，因此设计结点的合并问题。
1.5.1) 当所删除的关键字k不在终端结点(最底层非叶节点)中时，
1) 如果小于k的子树中关键字个数 > m/2向上取整-1，则找出k的前驱值k*，用k*取代k,
再递归删除k*即可。
2) 如果大于k的子树中关键字个数 > m/2向上取整-1，则找出k的后继值k*,用k*取代k，
再递归删除k*即可。
3) 如果前后两个子树中关键字个数均为m/2向上取整-1，则直接将两个子结点合并，直接删除k即可。

样例:
4阶B树如下
            5     7    9    
          3   6      8      10
删除7变为
            5      9
          3   6 8   10
         

1.5.2) 当被删除的关键字在终端结点(最底层非叶节点)
1) 直接删除关键字
若被删除关键字所在结点的关键字个数 > m/2向上取整-1，表明删除关键字满足B树定义，直接删除
2) 兄弟够借
若被删除关键字所在结点删除前关键字个数=m/2向上取整-1，且与此结点相邻的右(左)兄弟结点
的关键字个数>=m/2向上取整，则需要调整该及结点、右(左)兄弟结点及其双亲结点(父子换位法)，以达到新的平衡
    60    71
  5   65    74 86
删除65，兄弟够借，将删除结点的父节点取代待删除结点位置，将其右兄弟第一个结点取代待删除结点原来父节点的位置
    60  74
 5    71   86
 
3) 兄弟不够借
若被删除关键字所在结点删除前的关键字个数为m/2向上取整-1，左右兄弟结点关键字个数也是m/2向上取整-1，
则将关键字删除后与右(左)兄弟结点以及双亲结点中的关键字进行合并。
    60  74
 5    71   86
删除5,兄弟不够借
     74
60 71    86

若合并过程中，双亲结点关键字个数减为0，则删除该根结点，合并后的新结点称为根结点；
若双亲结点不是根结点，且关键字个数减少为m/2向上取整-2，则需要与兄弟结点进行调整或合并。
     

2 B+树
2.1 B+树条件
一棵m阶B+树满足如下条件:
1) 每个分治结点最多有m棵子树
2) 根节点至少有两个子树，其他每个分支结点至少有m/2向上取整棵子树
3) 结点的子树个数与关键字个数相等
4) 所有叶节点包含全部关键字以及指向相应记录的指针，而且叶节点中将按关键字大小排列，
并且相邻叶节点按大小顺序互相链接起来。
5) 所有分支结点中仅仅包含它的各个子结点中关键字的最大值以及指向其子结点的指针。

2.2 m阶B树与B+树的差别
1) B+树n个关键字的结点只含有n棵子树，B树n个关键字的结点有n+1棵子树
2) B+树中，每个结点(非根节点)的关键字个数n的范围:
m/2向上取整<=n<=m
根节点:
1<=n<=m
   B树中非根节点是:
m/2向上取整-1<=n<=m-1
根节点
1<=n<=m-1
3) B+树中，所有非叶节点仅仅起到索引作用，
非叶节点的关键字包含在叶节点中；
4) B+树的叶子结点包含全部关键字

B+树种包含两个头指针:
1个指向根节点，另一个指向关键字最小的叶节点。
可以对B+树进行两种查找运算:
1)从最小关键字开始的顺序查找
2)从根节点开始，进行多路查找

样例:
        ->                   60 85
    10   22   50   60                77    85
->10 16 22 40 50 55 60             69 77  80 85

2.3 B+树的范围查找
先从根结点开始查找到区间的起始值对应在叶节点中的位置，然后对该叶结点，通过
叶子节点之间的指针进行顺序查找，直到查找到区间的结束值。

3 mysql索引
3.1 索引基础
含义: 提高mysql查询效率的数据结构
优点: 提高键锁速度，唯一性索引可保证每一行数据的唯一性
缺点: 降低表的更新速度，索引占用磁盘文件
注意:
like语句中 "%aaa%"不会使用索引， like "aaa%"可以使用索引
不适用NOT IN和<>操作
不要在索引列上进行运算，会放弃索引进行全表扫描。
种类:
逻辑来分:
主键索引: 一张表只能有一个，不允许重复，不允许为NULL
唯一索引: 数据列不能重复，允许为NULL，例如身份证号
普通索引: 可以包含多个字段，允许重复和NULL
全文索引: 使得搜索关键词更为高效的一种索引。

-- 创建主键索引
alter table t add primary key add (`id`);
-- 创建唯一索引
alter table t add unique (`username`);
-- 创建普通索引
alter table t add index index_name (`username`);
-- 创建全文索引
alter table t add fulltext (`username`);

3.2 主键索引与唯一索引的区别
主键索引不能为NULL,唯一索引可以为NULL。
主键索引只能有1个，唯一索引可以有多个

3.3 主键索引为什么比普通索引查询快
因为普通索引查询多执行一次检索操作。查询主键时，只需要搜索主键的B+树，
普通索引查询会先查询该普通索引的索引树，得到主键后再去查询主键的B+树。

3.3 回表查询
普通索引查询到主键索引后，回到主键索引树查询的过程。

3.4 自增主键
使用自增主键作为表的主键:
自增主键连续，插入中减少页分裂。
自增主键:
优点: 减少页分裂
缺点: 数据过大可能会超出自增长取值范围，分库分表情况无法合表。
适用: 单库单表用自增主键，分库分表，可以使用分布式自增id。

3.5 查询一张表的所有索引
show index from <table_name>;
最多创建16个索引列。

3.6 让like %abc走索引查询
要让like查询走索引查询，查询字符不能以通配符(%)开始，可以使用reverse()函数来
创建一个函数索引:
select * from t where reverse(f) like reverse('%abc');

3.7 mysql联合索引注意什么
遵循最左匹配原则，例如联合索引key(a,b,c)，能触发
索引的搜索组合是a|ab|abc这三种查询。

3.8 联合索引的作用
用于多字段查询，key(a,b,c)等于建立了key(a), key(a,b), key(a,b,c)等三个索引
覆盖索引，例如对于联合索引key(a,b,c)，若使用:
select a,b,c from table where a=1 and b=1
就可以直接通过遍历索引取得数据，无需回表查询。

3.9 什么是最左匹配原则?它的生效原则有哪些?
最左匹配原则: 索引以最左边的为起点任何连续的索引都可以查询到，当遇到范围查询
(>,<,between,like)就会停止匹配。
生效原则:
例如表中有一个联合索引字段index(a,b,c)
where a=1 只使用了索引 a；
where a=1 and b=2 只使用了索引 a,b；
where a=1 and b=2 and c=3 使用a,b,c；
where b=1 or where c=1 不使用索引；
where a=1 and c=3 只使用了索引 a；
where a=3 and b like ‘xx%’ and c=3 只使用了索引 a,b。

3.10 列值为 NULL 时，查询会使用到索引吗？
在 MySQL 5.6 以上的 InnoDB 存储引擎会正常触发索引。

3.11 以下语句会走索引么？
select * from t where year(date)>2018;
不会走索引，因为在索引列上涉及到了运算。

3.12 能否给手机号的前 6 位创建索引？如何创建？
create index index_phone on t(phone(6));
alter table t add index index_phone(phone(6));

3.13 什么是前缀索引？为什么要用前缀索引？什么情况下适合使用前缀索引？
1)前缀索引是给某列的部分信息添加索引，例如给身份证的前10位添加索引。
2)前缀索引能减小索引文件大小。但不能用于order by中来触发前缀索引，
3)前缀索引适合于字符串较长，但是前几个字符就开始不同。

3.14 什么是页？
页是大小像等的存储块。数据库中将一个节点的大小设计为等于一个页，每个节点只需要一次磁盘
IO就可以载入。

3.15 索引的常见存储算法有哪些？
哈希+拉链法: 根据键找到其哈希值，将数据放入该哈希值对应的链表中。
有序数组存储发: 按顺序存储，优点: 二分查找可以快速找到数据，缺点:更新效率
搜索树: 以树的方式存储。

3.16 唯一索引和普通索引哪个性