MySql索引分析
一、MySql索引的介绍
1、定义
索引是帮助MySQL高效获取数据的数据结构，其本质是数据结构。
简单的可理解为一句话：排好序的快速查找数据结构，用于排序和快速查找。
一般来索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
2、常见疑问
为啥都说，索引不能建立在频繁更新的字段上？
原因是：因为你在平凡更新数据的时候，索引也同时会去维护操作数据的指向，这样我们在频繁更新数据的时候就会慢。
3、索引的优势和劣势
优势：
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对是数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的 。
- 虽然大大提高了查询的速度，同时也会降低更新表的速度。
二、索引的分类
单值索引：即一个索引只包含一个单列，一个表可以有多个单列索引(一般不能建立5个索引)。
唯一索引：索引列的值必须唯一，但允许为空值。
复合索引：即一个索引包含多列。
三、索引的数据结构
- 二叉树
- 红黑树
- Hash表
- B-Tree
1、二叉树数据结构的索引
二叉树算法索引数据结构普分布图
如果我们要查select * from t where col2=89这行数据情况：
对应图的右边就有个二叉树的算法，结构就是一个key,value的存储结构，key就是我们的索引，value就是索引字段所在行的那个磁盘地址文件指针。
根据二叉树的特点，右边的子节点是大于父节点的，左边小于父节点，分析：
如果col2数据是依次递增的情况，那按照我们的二叉树算法的特性，就变成了单边增长的情况了，最终就会变成一个链表的形式。
出现链表的形式就会像上图， 这样我如果要查col2=0006，会查6次，很显然不符合我的要求。
2、红黑树数据结构的索引
测验分析图：
根据图上的编号得出：
其实红黑树也是一种二叉树，它叫做二叉平衡树，如果单边增长的太多，就会自动去平衡，随着数据量的增大，会不停的去移动平衡。
对第一种二叉算法的优化：
在使用红黑树数据结构后相比第一种的二叉树查找要优一些，很明显，查col2=0006只用了3次。
劣势：
假如在我们实际的项目中，有百万级的数据，通过测验分析图和二叉平衡树的特点，会出现这棵树的高度越来越大，这样我们查最底下的数据，这样从上往下查，又会查很多次，随着数据量的增大，红黑树数据结构算法不符合实际的情况。
2、B-Tree和B+Tree数据结构的索引
问题？
在红黑树的基础上再次优化，优化的点很显然就是控制我们树的高度问题，让这棵树只有3-5层的高度就能存放千万级的数据问题？
方案设想：
让每个节点存放更多的索引元素，让树横向发展，控制树的高度，这样其实就是我们B-Tree结构。
B-Tree分布图：
规则：
mysql给我们的一个节点设置成16kb,在运行的时候先将一个节点放入到我们的内存当中，
疑问？
那千万级的数据完全可以放在一个节点呀，这样树的高度是1，这样查不是很快吗？问题是如果是千万级的数据都放在一个节点，这样一进来就将这千万级的数据打入到内存，很明显这样会消耗我们的很大内存，显然是不合适的。
B+Tree分布图：
B+Tree特点：
- B+Tree是一颗多叉平衡树，其叶子节点也是一个二叉树。
- 与B-Tree的区别在于，B+Tree多了非叶子节点，从上图可以看出，第一个和第二个节点存放都是索引元素，并没有存放data值，叶子节点存放了所有的索引元素，而且每个节点还多了个指向箭头。
为啥要这样分布呢？为啥会有非叶子阶段？
原因是：一个节点存放的索引是有大小限制的，如mysql默认设置一个节点为16kb,如果非叶子节点不存放我们的data数据指针，这样我们一个节点就可以存放更多的索引元素。
四、根据不同存储引擎来分析B+Tree的索引
1、MyISAM存储引擎索引实现
解析图
查看数据库的data文件可以看出MyISAM表在我们的磁盘上存放了三个文件分别是：
.frm文件：存放表结构定义的数据。
.MYD文件：存放数据的文件。
.MYI文件：存放索引的文件。
索引查找过程：
比如我们现在要查找上图中的col2=89的元素，那么执行过程就是：
通过B+Tree依次比对查找，查找到一个节点就放到我们内存当中，最终查找了索引89这行索引元素也就是key，就可以找到这个key对应value，这个vlue就是当前索引所在那一行的磁盘文件地址指针，然后这个指针就会去我们的.MYD文件快速的定位到需要查找的这一行。
2、InnoDB存储引擎索引实现
解析图
查看数据库的data文件可以看出InnoDB表在我们的磁盘上存放了二个文件分别是：
.frm文件：存放表结构定义的数据。
.ibd文件：存放数据和索引数据文件，将其数据文件和索引数据文件合并到一个文件了。
本身就利用了b+tree的结构存储模式。
特点：
- 与MyISAM分析图对比明显看出叶子节点存储实施有所不同的，b+tree叶子节点不仅存放了索引的元素还存储了具体的数据。
- 它是个聚集索引，而MyISAM是一个非聚集索引，这两者的定义的就是来源于我们的存储方式的不同，下面我们来分析下：
InnoDB存储引擎叶子节点把索引和数据都存放在一起了，也就是全部存放在.ibd文件了，不需要像MyISAM存储引擎那 样，数据和索引分开单独文件存储，也InnoDB存储引擎不需要去找myi文件了， 很显然查找的效率比MyISAM存储引 擎要快。
InnoDB表为啥必须要有主键？并且推荐使用整形的自增主键？
mysql设计如此必须要有主键？
如果你没有建立主键，mysql后台会给你默认选择一个字段加一个主键，因为它必须要主键，才能组织数据。
为啥整形的主键？
如果用字符型的uuid的方式去设置主键的话，那比较起来就麻烦了，字符串要转成我们的ACSII码然后在进行比较很显然比我们的整形直接拿来比较效率要低，占用的空间上整形也比字符串要少，节约空间。
为啥要自增？
b+tree索引的特性是从左到右依次递增的过程，在我们叶子节点有个指针，这样顺序指定会加快我们的查找效率。
如果不是递增去存放的，实例情况：1，2，3，4，5，6，7，9，10。这里缺少了一个8，如果我在10后面再去插入8，这时候我们的树就会分裂，树做了自动平衡，这些都是有性能开销的，会做大量的运算。
五、哪些情况下适合建立索引？哪些情况下不适合建立索引
哪些情况下适合建立索引？
- 主键自动建立唯一索引。
- 频繁作为查询条件的字段应该创建索引。
- 查询中与其他表关联的字段，外键关系建立索引。
- 频繁更新的字段不适合建立索引。
- where条件里面用不到的字段不创建索引。
- 单键/组合索引选择问题(在高并发下倾向选择组合索引)。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度。
- 查询中统计或者分组的字段需要创建索引，(因为分组会设计到排序的问题)。
哪些情况下不适合建立索引？
- 表记录太少。
- 经常增删改的表：提高了查询的速度，同时却降低了更新表的速度，因为MySQL在更新表数据的同时还要更新保存下索引文件。
- 数据重复且分布平均的表字段，应该只为最经常查询和最经常排序的数据列建立索引。
- 注意如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。（如：性别字段）