目录
1、索引基数
基数是数据列所包含的不同值的数量，例如，某个数据列包含值 1、3、7、4、7、3，那么它的基数就是 4。
索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。
如果某数据列含有很多不同的年龄，索引会很快地分辨数据行；如果某个数据列用于记录性别（只有“M”和“F”两种值），那么索引的用处就不大；如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。
在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。
2、索引失效
- 对索引列运算，运算包括（+、-、*、/、！、<>、%、like'%_'（% 放在前面）。
- 类型错误，如字段类型为 varchar，where 条件用 number。
- 对索引应用内部函数，这种情况下应该要建立基于函数的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此时应该建 ROUND (t.logicdb_id) 为索引。
- MySQL 8.0 开始支持函数索引，5.7 可以通过虚拟列的方式来支持，之前只能新建一个 ROUND (t.logicdb_id) 列然后去维护。
- 如果条件有 or，即使其中有条件带索引也不会使用（这也是为什么建议少使用 or 的原因），如果想使用 or，又想索引有效，只能将 or 条件中的每个列加上索引。
- 如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引。
- B-tree 索引 is null 不会走，is not null 会走，位图索引 is null，is not null 都会走。
- 组合索引遵循最左原则。联合索引index(a, b, c)：where a = 1 and b = 1 and c = 1 是可以命中索引，类型为 ref 类型；where b = 1 and c = 1 是无法命中索引，类型为 ALL；where a > 1 and b = 1 and c = 1 是可以命中索引的（只要有最左的a索引），索引类型是 range
- 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。（in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in）
explain命令：https://blog.csdn.net/Dopamy_BusyMonkey/article/details/49128767#5%E3%80%81explain
3、索引建立注意
- 最重要的肯定是根据业务经常查询的语句。
- 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重复的比率，比率越大我们扫描的记录数就越少。
- 如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高。
- 查询中与其它表关联的字段，外键关系建立索引
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 频繁更新的字段不适合创建索引
4、索引结构
- 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结构。
- B树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：m/2 - 1 <= j <= m - 1；一个节点的子节点数量会比关键字个数多1，这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间，非常形象，也容易和后面的B+树区分。由于数据同时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。（m阶为一节点至多有m棵子树 ，也就是说B树上的结点最多只能有m棵子树）
- B+树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：m/2 - 1 <= j <= m-1；子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中，且叶子节点间增加了横向的指针，这样顺序遍历所有数据将变得非常容易。
- B*树：一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是：1、每个非根节点所包含的关键字个数 j 满足：m2/3 - 1 <= j <= m；2、非叶节点间添加了横向指针。
4.1、B树的插入和删除
4.1.1、B树的插入操作
插入操作是指插入一条记录，即（key, value）的键值对。如果B树中已存在需要插入的键值对，则用需要插入的value替换旧的value。若B树不存在这个key,则一定是在叶子结点中进行插入操作。
1）根据要插入的key的值，找到叶子结点并插入。
2）判断当前结点key的个数是否小于等于m-1，若满足则结束，否则进行第3步。
3）以结点中间的key为中心分裂成左右两部分，然后将这个中间的key插入到父结点中，这个key的左子树指向分裂后的左半部分，这个key的右子支指向分裂后的右半部分，然后将当前结点指向父结点，继续进行第3步。
下面以5阶B树为例，介绍B树的插入操作，在5阶B树中，结点最多有4个key,最少有2个key
a）在空树中插入39
此时根结点就一个key，此时根结点也是叶子结点
b）继续插入22，97和41
根结点此时有4个key
c）继续插入53
插入后超过了最大允许的关键字个数4，所以以key值为41为中心进行分裂，结果如下图所示，分裂后当前结点指针指向父结点，满足B树条件，插入操作结束。当阶数m为偶数时，需要分裂时就不存在排序恰好在中间的key，那么我们选择中间位置的前一个key或中间位置的后一个key为中心进行分裂即可。
d）依次插入13，21，40，同样会造成分裂，结果如下图所示。
e）依次插入30，27, 33 ；36，35，34 ；24，29，结果如下图所示。
f）插入key值为26的记录，插入后的结果如下图所示。
当前结点需要以27为中心分裂，并向父结点进位27，然后当前结点指向父结点，结果如下图所示。
进位后导致当前结点（即根结点）也需要分裂，分裂的结果如下图所示。
分裂后当前结点指向新的根，此时无需调整。
g）最后再依次插入key为17,28,29,31,32的记录，结果如下图所示。
在实现B树的代码中，为了使代码编写更加容易，我们可以将结点中存储记录的数组长度定义为m而非m-1，这样方便底层的结点由于分裂向上层插入一个记录时，上层有多余的位置存储这个记录。同时，每个结点还可以存储它的父结点的引用，这样就不必编写递归程序。
一般来说，对于确定的m和确定类型的记录，结点大小是固定的，无论它实际存储了多少个记录。但是分配固定结点大小的方法会存在浪费的情况，比如key为28,29所在的结点，还有2个key的位置没有使用，但是已经不可能继续在插入任何值了，因为这个结点的前序key是27,后继key是30,所有整数值都用完了。所以如果记录先按key的大小排好序，再插入到B树中，结点的使用率就会很低，最差情况下使用率仅为50%。
4.1.2、B树的删除操作
删除操作是指，根据key删除记录，如果B树中的记录中不存对应key的记录，则删除失败。
1）如果当前需要删除的key位于非叶子结点上，则用后继key（这里的后继key均指后继记录的意思）覆盖要删除的key，然后在后继key所在的子支中删除该后继key。此时后继key一定位于叶子结点上，这个过程和二叉搜索树删除结点的方式类似。删除这个记录后执行第2步
2）该结点key个数大于等于Math.ceil(m/2)-1，结束删除操作，否则执行第3步。
3）如果兄弟结点key个数大于Math.ceil(m/2)-1，则父结点中的key下移到该结点，兄弟结点中的一个key上移，删除操作结束。
否则，将父结点中的key下移与当前结点及它的兄弟结点中的key合并，形成一个新的结点。原父结点中的key的两个孩子指针就变成了一个孩子指针，指向这个新结点。然后当前结点的指针指向父结点，重复上第2步。
有些结点它可能即有左兄弟，又有右兄弟，那么我们任意选择一个兄弟结点进行操作即可。
下面以5阶B树为例，介绍B树的删除操作，5阶B树中，结点最多有4个key,最少有2个key
a）原始状态
b）在上面的B树中删除21，删除后结点中的关键字个数仍然大于等2，所以删除结束。
c）在上述情况下接着删除27。从上图可知27位于非叶子结点中，所以用27的后继替换它。从图中可以看出，27的后继为28，我们用28替换27，然后在28（原27）的右孩子结点中删除28。删除后的结果如下图所示。
删除后发现，当前叶子结点的记录的个数小于2，而它的兄弟结点中有3个记录（当前结点还有一个右兄弟，选择右兄弟就会出现合并结点的情况，不论选哪一个都行，只是最后B树的形态会不一样而已），我们可以从兄弟结点中借取一个key。所以父结点中的28下移，兄弟结点中的26上移,删除结束。结果如下图所示。
d）在上述情况下接着32，结果如下图。
当删除后，当前结点中只key，而兄弟结点中也仅有2个key。所以只能让父结点中的30下移和这个两个孩子结点中的key合并，成为一个新的结点，当前结点的指针指向父结点。结果如下图所示。
当前结点key的个数满足条件，故删除结束。
e）上述情况下，我们接着删除key为40的记录，删除后结果如下图所示。
同理，当前结点的记录数小于2，兄弟结点中没有多余key，所以父结点中的key下移，和兄弟（这里我们选择左兄弟，选择右兄弟也可以）结点合并，合并后的指向当前结点的指针就指向了父结点。
同理，对于当前结点而言只能继续合并了，最后结果如下所示。
合并后结点当前结点满足条件，删除结束。
4.2、B+树的插入和删除
4.2.1、B+树的定义
各种资料上B+树的定义各有不同，一种定义方式是关键字个数和孩子结点个数相同。这里我们采取维基百科上所定义的方式，即关键字个数比孩子结点个数小1，这种方式是和B树基本等价的。上图就是一颗阶数为4的B+树。
除此之外B+树还有以下的要求。
1）B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。
2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。
3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。
4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
4.2.2、B+树的插入操作
1）若为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点也是根结点，插入操作结束。
2）针对叶子类型结点：根据key值找到叶子结点，向这个叶子结点插入记录。插入后，若当前结点key的个数小于等于m-1，则插入结束。否则将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前m/2个记录，右结点包含剩下的记录，将第m/2+1个记录的key进位到父结点中（父结点一定是索引类型结点），进位到父结点的key左孩子指针向左结点,右孩子指针向右结点。将当前结点的指针指向父结点，然后执行第3步。
3）针对索引类型结点：若当前结点key的个数小于等于m-1，则插入结束。否则，将这个索引类型结点分裂成两个索引结点，左索引结点包含前(m-1)/2个key，右结点包含m-(m-1)/2个key，将第m/2个key进位到父结点中，进位到父结点的key左孩子指向左结点, 进位到父结点的key右孩子指向右结点。将当前结点的指针指向父结点，然后重复第3步。
下面是一颗5阶B树的插入过程，5阶B数的结点最少2个key，最多4个key。
a）空树中插入5
b）依次插入8，10，15
c）插入16
插入16后超过了关键字的个数限制，所以要进行分裂。在叶子结点分裂时，分裂出来的左结点2个记录，右边3个记录，中间key成为索引结点中的key，分裂后当前结点指向了父结点（根结点）。结果如下图所示。
当然我们还有另一种分裂方式，给左结点3个记录，右结点2个记录，此时索引结点中的key就变为15。
d）插入17
e）插入18，插入后如下图所示
当前结点的关键字个数大于5，进行分裂。分裂成两个结点，左结点2个记录，右结点3个记录，关键字16进位到父结点（索引类型）中，将当前结点的指针指向父结点。
当前结点的关键字个数满足条件，插入结束。
f）插入若干数据后
g）在上图中插入7，结果如下图所示
当前结点的关键字个数超过4，需要分裂。左结点2个记录，右结点3个记录。分裂后关键字7进入到父结点中，将当前结点的指针指向父结点，结果如下图所示。
当前结点的关键字个数超过4，需要继续分裂。左结点2个关键字，右结点2个关键字，关键字16进入到父结点中，将当前结点指向父结点，结果如下图所示。
当前结点的关键字个数满足条件，插入结束。
4.2.3、B+树的删除操作
如果叶子结点中没有相应的key，则删除失败。否则执行下面的步骤
1）删除叶子结点中对应的key。删除后若结点的key的个数大于等于Math.ceil(m-1)/2 – 1，删除操作结束,否则执行第2步。
2）若兄弟结点key有富余（大于Math.ceil(m-1)/2 – 1），向兄弟结点借一个记录，同时用借到的key替换父结（指当前结点和兄弟结点共同的父结点）点中的key，删除结束。否则执行第3步。
3）若兄弟结点中没有富余的key,则当前结点和兄弟结点合并成一个新的叶子结点，并删除父结点中的key（父结点中的这个key两边的孩子指针就变成了一个指针，正好指向这个新的叶子结点），将当前结点指向父结点（必为索引结点），执行第4步（第4步以后的操作和B树就完全一样了，主要是为了更新索引结点）。
4）若索引结点的key的个数大于等于Math.ceil(m-1)/2 – 1，则删除操作结束。否则执行第5步
5）若兄弟结点有富余，父结点key下移，兄弟结点key上移，删除结束。否则执行第6步
6）当前结点和兄弟结点及父结点下移key合并成一个新的结点。将当前结点指向父结点，重复第4步。
注意，通过B+树的删除操作后，索引结点中存在的key，不一定在叶子结点中存在对应的记录。
下面是一颗5阶B树的删除过程，5阶B数的结点最少2个key，最多4个key。
a）初始状态
b）删除22,删除后结果如下图
删除后叶子结点中key的个数大于等于2，删除结束
c）删除15，删除后的结果如下图所示
删除后当前结点只有一个key,不满足条件，而兄弟结点有三个key，可以从兄弟结点借一个关键字为9的记录,同时更新将父结点中的关键字由10也变为9，删除结束。
d）删除7，删除后的结果如下图所示
当前结点关键字个数小于2，（左）兄弟结点中的也没有富余的关键字（当前结点还有个右兄弟，不过选择任意一个进行分析就可以了，这里我们选择了左边的），所以当前结点和兄弟结点合并，并删除父结点中的key，当前结点指向父结点。
此时当前结点的关键字个数小于2，兄弟结点的关键字也没有富余，所以父结点中的关键字下移，和两个孩子结点合并，结果如下图所示。
5、索引的目的
减少磁盘IO次数，众所周知，MySQL的数据实际是存储在文件中，而磁盘IO的查找速度是要远小于内存速度的，所以减少磁盘IO的次数能很大程度的提高MySQL性能。
磁盘IO时间 = 寻道 + 磁盘旋转 + 数据传输时间
机械硬盘的连续读写性能很好，但随机读写性能很差。
- 顺序访问：内存访问速度是硬盘访问速度的6~7倍（
kafka的特点，以后有机会的话再讲一讲） - 随机访问：内存访问速度就要比硬盘访问速度快上10万倍以上
随机读写时，磁头需要不停的移动，时间都浪费在了磁头寻址上。 而在实际的磁盘存储里，是很少顺序存储的，因为这样的维护成本会很高。
6、B+树索引的存储
InnoDB 引擎为例
6.1、聚簇索引
每个InnoDB表都有一个称为聚集索引的特殊索引，该索引是按照表的主键构造的一棵B+树。根据示例数据构建如图所示
特点：
- 叶子节点存放了整张表的所有行数据。
- 非叶子节点并不存储行数据，是为了能存储更多索引键，从而降低B+树的高度，进而减少IO次数。
- 聚集索引的存储在物理上并不是连续的，每个数据页在不同的磁盘块，通过一个双向链表来进行连接。
查找：假设要查找数据项6
- 把根节点由磁盘块0加载到内存，发生一次IO，在内存中用二分查找确定6在3和9之间；
- 通过指针P2的磁盘地址，将磁盘2加载到内存，发生第二次IO，再在内存中进行二分查找找到6，结束。
这里只进行了两次IO，实际上，每个磁盘块大小为4K，3层的B+树可以表示上百万的数据，也就是每次查找只需要3次IO，所以索引对性能的提高将是巨大的。
Mysql 选择聚集索引：
每张InnoDB表有且只有一个聚集索引，那它是怎么选择索引的呢？
- 一般情况，用
PRIMARY KEY来作为聚集索引。 - 如果没有定义
PRIMARY KEY，将会用第一个UNIQUE且NOT NULL的列来作为聚集索引。 - 如果表没有合适的
UNIQUE索引，会内部根据行ID值生成一个隐藏的聚簇索引GEN_CLUST_INDEX。
所以在建表的时候，如果没有逻辑唯一且非空列时，可以添加一个auto_increment的列，方便建立一个聚集索引。
6.2、非聚簇索引
非聚集索引又叫辅助索引，叶子节点并不包含行记录数据，而是存储了聚集索引键。根据示例数据（idx_name索引）构建如图2-2所示辅助索引：
特点：
- 每个表可以有多个辅助索引
- 通过辅助索引查数据时，先查找辅助索引获得聚集索引的主键，然后通过主键索引来查找完整的行记录。
- 通过非主键索引比主键索引查找速度要慢一倍。
查找：获取NAME=Jake的数据：
第一阶段：通过辅助索引查到主键索引的主键
- 把idx_name索引的根节点由磁盘块0加载到内存，发生一次IO，查找到在P2指针中
- 根据P2指针的磁盘地址，加载磁盘块2到内存，发生第二次IO，查找到Jake节点以及它的主键索引9
第二阶段：通过主键索引找到完整的行记录
- 把根节点由磁盘块0加载到内存，发生一次IO，在内存中用二分查找确定9在P3指针中
- 通过指针P3的磁盘地址，将磁盘3加载到内存，发生第二次IO，再在内存中进行二分查找找到9，以及它的行记录
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
