mysql索引理解

要理解的问题

• 1.什么是索引
• 2.为什么要用索引
• 3.索引实现的方式
• 4.BTree理解
• 5.B+ Tree理解
• 6.B+ Tree脱颖而出的原因
• 7.聚合索引实现的原则：最左前缀原则的理解
• 8.索引sql分类以及创建
• 9.索引失效的情况
• 10.后记

1.什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构.是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

重点：排好序的存储结构
注：（图暂时都没画，因为突然自己画图软件不会用）

2. 为什么要用索引

col1	col2
10	20
8	20
11	20
6	20

既然提到为什么需要索引，那么我们就先来说下没有索引的查找方式把。
没有索引的时候，数据库表查找数据的时候采用的是顺序查找，至到找到正确的位置，这个时间复杂度也就是O(n)。
而加入了索引之后，就会根据索引去查找，然后找到对应索引的地址值，去找到信息。以二叉树为例子进行举例。
算法结构可视化网址

你之前查找6的时候第四次才能查到，而现在第三次就可以查到。使用二叉树的话时间复杂度就是O(log n)
注：
二叉查找树（binary search tree , BST）或者是一棵空树；或者是具有以下性质的二叉树：
⑴ 若它的左子树不空，则其左子树中所有结点的值不大于根结点的值；
⑵ 若它的右子树不空，则其右子树中所有结点的值不小于根结点的值；
⑶ 它的左、右子树都是二叉查找树。
对于大数据量的表使用索引之后，查询效率会有显著的提升。

3.索引实现的方式

可以打开navicat，然后找到一个表，鼠标右键点击，然后选择设计表，点击索引

查看索引方法，可以看到有HASH和BTREE，那么又有一个问题出现了。
为什么不使用二叉树呢？
我们知道二叉树有一些性质：
1.在二叉树的第i层上最多有 2i个结点。
2.高度为h的二叉树至多有 2 h+1-1 个结点。
3.对任何一棵二叉树T，如果其终端结点数为n0，度为 2 的结点数为n2，则n0 = n2
4. n 个结点的完全二叉树的高度为 logn 。
5. 含有 n≥1 个结点的二叉树的高度至多为 n-1；高度至少为 log n。
6. 如果对一棵有 n 个结点的完全二叉树的结点进行编号，则对任一结点 i(1≤i ≤n)， 有 ⑴ 如果 i=1，则结点 i 是二叉树的根，无双亲；如果 i>1，则其双亲结点 PARENT(i)是
结点 ⎣ 2/i ⎦。 ⑵ 如果 2i>n，则结点 i 无左孩子；否则其左孩子是结点 2i。 ⑶ 如果 2i+1>n，则结点 i 无右孩子；否则其右孩子是结点 2i+1。
在这里我们就看一下有n个节点的二叉树，其存储高度为log n,每个节点要存放一块内存，然后我们看一下每块内存的大小，这个在mysql 中都有定义的

我们可以看到就是16KB，如果我们一个节点一个16kb的话，这样对于千万数量级的数据来说用二叉树的话有两个不便之处
1.所需内存过大
2.树的高度太大，查找次数太多，速度变慢。
因为这些原因就忽略掉了二叉树这种存储结构，使用B Tree、B+Tree、HASH表方式。
注：
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

4.BTree理解

BTree(平衡多路查找树)，是一种常见的数据结构。
对于一棵m阶B-tree，每个结点至多可以拥有m个子结点。各结点的关键字和可以拥有的子结点数都有限制，规定m阶B-tree中，根结点至少有2个子结点，除非根结点为叶子节点，相应的，根结点中关键字的个数为1m–m-1；非根结点至少有[m/2]（[]，向上取整）个子结点，相应的，关键字个数为[m/2]-1~m-1。
B-Tree有如下特性：
1、关键字集合分布在整棵树中；
2、任何一个关键字出现且只出现在一个结点中；
3、搜索有可能在非叶子结点结束；
4、其搜索性能等价于在关键字全集内做一次二分查找；
5、自动层次控制；
B-tree中，每个结点包含：
1、本结点所含关键字的个数；
2、指向父结点的指针；
3、关键字；
4、指向子结点的指针；

B+ Tree理解

BTREE和B+Tree的区别：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data
（2）为所有叶子节点增加了一个链指针
B+Tree是Btree的进阶，顺序指针的好处就在于方便我们进行范围查找。

6.B+ Tree脱颖而出的原因

1.二分查找
2.顺序指针
B+ Tree树我们可以举一个例子，存储引擎中一个页是14kB，这也就是我们B+Tree树上每个节点的大小，假设一个索引的key加上指针为14B，那么一个页里面可以存放大概1170个索引，然后每个结点又有1170个指针指向子节点，每个子节点又同样包含1170个索引，这样在B+ Tree树的第二层就已经存储了11701170=1368900个索引，假设第三层是叶节点，也就是存放的为索引+指针+data，就按1KB来算,这样也能存储了大概136890016=21902400，这也就是说一千万的数据量我就需要查找三次就可以查找到对应的值，当然这个只是理论上的计算，具体的设计还要看怎么配置。那么就会有疑问了，如果我们使用HASH表的话不是更快吗？
HASH表，会计算出来每个key的hash值，然后存放到链表里面，这个我们查找的时候根据hsah值一下就可以查找到对应的链表，但是为什么我们不使用HASH表呢？这个时候就要看顺序指针，因为我们在查找的过程中除了精确查找之外，还存在范围查找，hash表这个时候就比不上B+TREE了，因为B+Tree存在顺序指针，直接可以顺序的查找下去

7.聚合索引实现的原则：最左前缀原则的理解

前面我们说的都是单个索引的情况，但是在实际使用中，我们会存在多个索引也就是聚合索引，那么聚合索引就要理解最左前缀原则。单索引可以理解为聚合索引为1的情况，mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）
关于最左前缀的使用，有下面两条说明：
0.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式