学一学Mysql中的索引
索引
先聊聊什么是索引
索引是为了什么：让Mysql查询数据的速度变快。
具体来说，我们把数据库想象成书，而索引就相当于它的目录。
来看下索引的语法：
//普通索引
CREATE INDEX indexName ON table_name (column_name)
//修改时创建索引
ALTER table tableName ADD INDEX indexName(columnName)
//创建表时创建索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
在index 前面加上unique就是创建唯一索引
//删除索引的方法
DROP INDEX [indexName] ON mytable;
索引的底层数据结构
索引是与C++中的map相似的，都是需要做出Key-value这样的结构，借鉴map的实现方式使用哈希表、或者红黑树（实际上是另外一种搜索树）。
哈希表
把一个值key通过一种算法映射成某一个数字，这个数字可以理解为一个数组的下标，而这个数组每一个元素都是我们的value，这样就实现了key-value的配对。但是有一个问题：我们可以找到这样一种算法可以实现key和value的一对一映射吗？答案是不行。我们把使用的算法叫做哈希算法，这样的问题就是哈希冲突。
那怎么解决哈希冲突？一个很经典的方法————开链法。还记得我们之前说过用一个数组存value吗？现在我们这个数组不再只存一个value了，我们把所有的key-value都存在这个数组里面，常见的做法是使用一个链表来存，当然也有用红黑树来存储的。
不过有一个很严重的问题，哈希表是依赖 ‘=’ 来存储key-value键值对的，也就是是说相邻的key值之间是没有什么联系的，也就是说无法使用区间查询。
B-树
借鉴map的做法，我们可以用二叉搜索树来维护key-value结构，我们都知道朴素的二叉树会退化成链，时间复杂度会退化为O(n)，所以我们需要平衡树或者红黑树来保证这一颗树的高度为logn。
但是，Mysql中的innoDB却使用的是另外一种二叉搜索树————B树和B+树。至于为什么我们先放下不谈，先来看一看什么是B树和B+树。
B+树是B树的升级版，所以先来聊一聊B树，
B树是多叉树，也就是每个节点有多个子节点。对于每一个节点有如下成员：
- \(n\),关键字个数；
- \(key[n]\),关键字数组，以非降序排列，即 对任意 \(i(2\le i\le n)\)有\(key[i-1]\le key[i]\)；
- \(isLeaf\),检验是否为叶子节点，所有叶子节点都在同一层；
- \(c[n+1]\),指针数组，指向该节点的孩子节点，叶子节点没有孩子；
- \(t\),一个static的int变量，规定非根节点的关键字个数\(t-1 \le n \le 2t\)，但需要注意我们将 \(2t-1\)个关键字的节点称之为满，而不是\(2t\)，这很重要。
父亲与孩子节点的关系：
定义：\(SonKey[i]\)表示\(c[i]\)指向的孩子节点的所有key值。
那么需要满足：\(SonKey[i] \le key[i] \le SonKey[i+1]\)
这样我们处理了两件事：1.每一个节点的构成 2.父节点与子节点之间的关系，在这基础上我们就可以建出一颗B-树。当然这还远远不够。
我们先做一个很数学的推理：B-树的高度是多少？
如果\(n \ge 1\)，一颗\(n\)个关键字、高度为\(h\)、最小度数\(t \le 2\)的B-树的高度\(h\)为\(h \le log_t \frac{n+1}{2}\)。
证明如下：
根节点至少有1个关键字，故必然至少有2个孩子，即高度为1的节点有两个节点，这两个非根节点至少有t个孩子，即高度为2有2t个节点，进而得出结论高度为h有\(2t^h\)个节点。
必然存在
\(n \ge 1+(t-1)\sum_{i=1}^{h}2t^{i-1} = 2t^h-1\)
\(=>t^h \le \frac{n+1}{2}\)
\(=>h \le log_t \frac{n+1}{2}\)
搜索树的高度是非常重要的，这直接决定了搜索的性能。
B-树的增删查改
查找
直接看算法导论的伪代码
Search(x,k)
i = 1;
while(i <= n && k > key[i]) i++;
if(i <= n && k == key[i]) return (x,i);
if(isLeaf) return NULL;
return Search(c[i],k);
本质上和二叉树的搜索逻辑相同，只不过在每一个节点中不只一个值，而是一个数组，导论中给出的算法是O(n)的，但是对于升序序列我们显然可以使用二分来优化，也就是说B-树的查找的最差时间复杂度大概为\(log_22t*log_t \frac{n+1}{2}\)。
插入
在插入之前我们需要找到对应的位置然后把这个关键字插入这个节点，这与查找的逻辑类似，这里就不再赘述。
现在的问题是如果这个节点满了怎么办？
注意一件事情，一个节点是满的，那么这个节点有\(2t-1\)个关键字，为了保证之后的插入可以正常进行，我们需要把该节点分裂为两个节点。
分裂的逻辑是这样的：
按照中间的关键字\(key[mid]\)将该节点分为\([1,mid-1]\)和\([mid+1,1]\)两个部分，而我们将中间关键字插入父节点，这两个新部分就是新的子节点。但注意我们需要将中间关键字插入父节点，这也是一个插入节点的过程，也就是说我们需要向上递归。
删除
删除比插入更加复杂，算法导论中列举了三种情况：
假设关键字为\(k\)，节点为\(x\)
a、\(k\)在\(x\)中，且\(x\)是叶子节点，直接删除\(k\)。
b、\(k\)在\(x\)中，且\(x\)不是叶子节点
- 如果\(x\)中关键字\(k\)的前驱子节点\(y\)至少有\(t\)个关键字，在\(y\)找到一个\(k\)的前驱\(k'\)，递归的删除\(k'\)，并且用\(k'\)代替\(k\)。
- 假设\(y\)不足\(t\)个关键字，则选择关键字\(k\)的后继子节点\(z\)，递归的执行这个过程。
- 否则，前驱节点\(y\)和后继节点\(z\)都只有\(t-1\)节点，那将\(z\)合并到\(y\)和\(k\)，此时\(y\)中有\(2t-1\)个关键字，现在\(x\)中的\(k\)和指向\(z\)的指针都可以删除了，并且我们要在\(y\)中递归的删除\(k\)。
c、\(k\)不在\(x\)中,但是\(k\)在\(x\)的某一个子节点\(c[i]\)中，并且\(c[i]\)只有\(t-1\)个关键字
- \(c[i]\)只有\(t-1\)个关键字，但是兄弟节点至少有\(t\)个关键字，我们需要把x的某个关键字下降到\(c[i]\)，将兄弟节点的一个关键字上升到\(x\)，将对应的指针放入\(c[i]\)
- \(c[i]\)以及其兄弟都是\(t-1\)个关键字，合并\(c[i]\)和其中一个兄弟，并将\(x\)的一个关键字下降到合并的子节点中。
现在终于写完B树的逻辑了。。。。
为什么使用B树？
在时间复杂度方面，B树真的会比红黑树好吗？虽然B树更加矮小，但是B树的每一个节点中仍然有一条链表需要查询，我们很难说B树就一定比红黑树更好。事实上，B树的优化并不在算法的时间或者空间复杂度上，我们需要考虑的是另外一个问题——\(磁盘IO\)。
在数据库中，索引不光在内存中，还可以在磁盘当中。磁盘的容量比主存大得多了，大概100倍，然而问题就是磁盘的IO读取要慢得多的。
如果使用红黑树这样的一个节点存储一个关键字，在访问每一个节点的时候都要进行磁盘IO，这太浪费了。所以我们需要磁盘存储更多的信息，实际上B树就是为磁盘或其他直接存取的辅助存储设备而设计的一种平衡搜索树。
\(InnoDB\)的索引模型
InnoDB中，表是根据主键顺序以索引的形式存放的，这就是存储方式的表被称之为索引组织表。InnoDB使用B+树索引模型，所以数据都在B+树中，每一个索引都有对应的B+树。
所谓B+树实际上是B树的一种变种，每一种数据我们都要放在叶子节点中，内部节点类似于路标，来指示叶子节点的位置。
索引分为主键索引和非主键索引，主键索引的叶子节点存的是整行数据，InnoDB中也叫聚簇索引。非主键索引的叶子节点是主键的内容。
那么他们的查询，有什么区别呢？
一张表，有ID，value两个索引，其中ID是主键索引。我们在查询的时候如果查询的是ID这个主键索引就直接搜索，而查询pwd的时候我们需要先查询value这颗树得到ID之后，再到ID这颗索引树，搜索两遍显然是不好的，这就叫回表。
覆盖索引
来看一条SQL语句：
select * from table where value between 1 and 5;
number不是主键索引，所以需要回表，并且number有1~5五个值所以需要回表五次。
但如果是这样：
select ID from table where value between 1 and 5;
ID作为主键是不需要回表的，因为ID的信息在value树中的，我们称之为覆盖索引。
最左前缀原则
这个原则是在联合索引时使用的。建立一个联合索引(id,name)，我们都知道B+树是需要关键字存在大小比较的，我们称之为存在偏序关系，而我们这个联合索引是无法直接比较的，所以我们需要定义一个原则来保证联合索引可以建立一颗B+树，这就是我们的最左前缀原则的由来。
最左匹配原则是这样的：
在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
如果用一段代码描述联合索引(a,b)就是：
bool operator < (T t1,T t2)const {
if(t1.a == t2.a) return t1.b < t2.b;
return t1.a < t2.a;
}
索引下推
有一个联合索引(name,age)，主键索引id
select * from tuser
where name like '韩%' and age=22;
我们会通过韩这个姓找到第一个满足条件的主键索引id。再逐渐进行回表扫描，对age = 22进行筛选。而索引下推做到的是在使用联合索引的时候直接判断age=22，之后再进行回表扫描。这是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。
浙公网安备 33010602011771号