Mysql索引底层原理

索引的本质：

什么是索引？
mysql官方定义，索引是帮助Mysql高效获取数据的排好序的快速查找的数据结构。
即索引可以大大提高MySQL的检索速度 。

索引的优势

1，通过索引可以提高数据的检索效率，降低数据库的IO成本
2，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的劣势

1， 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，因此建立索引会占用磁盘空间。
2，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
3，索引只是调高效率的一个因素，如果你的mysql有大量的表，就需要花时间研究建立最优秀的索引，或优化查询。

索引的分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引（但这种情况可不是复合索引）。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：即一个索引包含多个列。

基本语法
column写一个就是单值索引，写多个就是复合索引。带UNIQUE就是唯一索引
创建：CREATE [UNIQUE] INDEX indexName ON tableName(columnName(length));
ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
删除：DROP INDEX [indexName] ON tableName;
查看：SHOW INDEX FROM tableName;
只用ALTER命令

索引的数据结构

二叉树
红黑树
Hash表
B-Tree
== 二叉树–>红黑树–>B树–>B+树（不断改进的过程）==

二叉树：即二叉查找树：左节点小于父节点，右节点大于父节点。
红黑树： 是一种自平衡二叉查找树，就是特化的平衡二叉树，就是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能，但它的左右子树高差有可能大于 1，所以红黑树不是严格意义上的平衡二叉树。它虽然是复杂的，但它的最坏情况运行时间也是非常良好的，并且在实践中是高效的： 它可以在O(log n)时间内做查找，插入和删除。
性质1. 节点是红色或黑色。
性质2. 根节点是黑色。
性质3.所有叶子都是黑色。（叶子是NULL节点）
性质4. 每个红色节点的两个子节点都是黑色。（从每个叶子到根的所有路径上不能有两个连续的红色节点）
性质5… 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
== 这些约束强制了红黑树的关键性质: 从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。*==

BTree（多路搜索树）

B+树：（多路平衡查找树）
每个节点横向上存储多个索引元素
叶节点存储所有的索引和数据

Mysql具体是怎么运用B+树来存储索引呢？

存储引擎：Myisam和InnoDB
存储引擎是形容表级别的，所以每张表的存储引擎可能不同。

MyISAM引擎 索引文件和数据文件是分离的（非聚集）：
.frm是表结构文件
.MYD存放的是表数据
.MYI存放表索引

InnoDB引擎 （聚集）：
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录(索引值+数据)
为什么InnoDB表必须有主键，并且推荐整型的自增主键？
为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
.frm存放表结构
.idb存放表的索引和数据

MyISAM存储引擎索引实现
假设执行一个查询语句： select * from table where Col1=49；
1，首先先去MYI文件找到索引49（MYI文件里索引的组织形式就是B+树）
2，然后根据找到的data地址直接去MYD文件定位到相应数据

InnoDB存储引擎索引实现:

再来说说，为什么InnoDB表必须有主键，并且推荐整型的自增主键？
因为mysql表数据文件本身就是按B+Tree组织的一个索引结构文件，它要求你必须要有主键，如果没有主键这个表数据是没办法组织的。
（那么有同学就会说哥们你这么说我就不同意了，我之前建了InnoDB的表就没设置主键也建成功了啊！）
那是为什么我告诉你，你没有设主键并不代表它没有主键，在mysql你建了一张表没有设主键，它会选择你可以唯一标识一条记录的那一列自动为你建一个主键，如果没有唯一标识的那一列，它会在表里面默认给你加一列，由它来帮你维护这唯一的主键索引，你是看不到的。== 说白了它必须要有这么一列主键，来帮你组织整张表的数据。所以InnoDB必须要有主键，因为它设计如此。==
为什么推荐使用整型自增主键，而不是像UUID这样的字符串，因为它在B+树上查找索引时，要不断地进行比较，那么你认为是比较整型数快还是字符串快？更何况整型数也比较节省空间。
为什么推荐自增？
因为B+树所有叶节点都会通过指针串起来的，从下图可以看出B+树的一个特性：它每个节点内部都是按照递增的顺序维护的，每个节点间也是按照从左到右递增的顺序维护的。 这就是B+树的一个特性== 每个节点都是按照从左到右递增的顺序来存储。==

再来说，mysql表的所有方法并不一定非要按照B+树来，例如上述所说的它可以有很多种，Hash结构就是其一。Hash结构大概就是这么回事：
它将索引和数据用Hash表来存储，即为每个索引建立唯一的hash值放在hash表里，对应后面跟其数据。
比方说，我们 select from table where Col1=20；
它会首先做一个hash运算 hash(20)=(唯一的散列值)即hash值，然后拿这个hash值去hash表里直接定位到相应的数据。
(估计有同学又会说了，这hash索引好像更牛逼一点啊，只进行了一次hash运行就能迅速定位到数据，而且hash运算又是非常快的)
但是，工作中99.9%以上情况都是用B+树，而不是hash。虽然感觉hash定位的效率更高。为什么？
假如这样，select * from table where Col1>20；查找大于20 的记录。hash只能定位确定的索引，是不是对范围查找hash就没辙了。这就是hash查找的缺点，对范围查找支持的非常差。
== 而B+树就很好的支撑范围查找了，因为B+树叶节点之间有指针，又要求了索引是自增的，所以查找大于20 的记录的时候，找到值为20的索引，接着就简单了，直接顺藤摸瓜把20 之后的节点全部拿出来就好了。==
B树呢？由于B树叶节点是没有指针把它们穿起来的，所以当B树进行范围查找时，不能进行节点间的访问，访问到20后，要返回到父节点，从父节点向下再去访问相邻节点49，依次进行返回访问返回访问…

所以是不是觉得还是B+树更牛逼些!

哪些情况需要创建索引呢？

1，主键自动建立唯一索引
2，频繁作为查询条件的字段应该创建索引
3，查询中与其它关联的字段，外键关系建立索引
4，频繁更新的字段不适合创建索引
5， where条件里用不到的字段不创建索引
6，单键/组合索引的选择问题，who？（在高并发下创建组合索引）
7，查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
8，查询中统计或者分组字段

哪些情况不要创建索引呢？

1，表记录太少
2，经常增删改的表
3，如果某个数据列包含许多重复的内容，为它建立索引就没有太大实际效果

性能分析

Explain
是什么？

能干嘛？

怎么用？
explain select * from table；

一般来说，得保证查询至少到达range级别，最好能到达ref。
说说这些类型都是什么意思