MySQL索引深入理解底层数据结构

什么是索引？

是帮助MYSQL高效获取数据的数据结构
实现了高级查找算法的数据结构
索引一般以文件形式存储在磁盘上

在存储的表结构，在磁盘上每个表结构存储的是两份文件，frm存储的是表结构，ibd文件存储的是索引文件

数据结构与算法

二叉树

二叉树的全称是: Binary Search Tree
逻辑约束：从小到大，即存储的键比我的根节点小，那么就存储在左节点，否则存储在右边的节点上。主要是用来缩短一半的查找效率。

红黑树

	红黑树的英文全称：Red-Black-Tree
	在二叉树的基础上做了一定的优化操作。
	逻辑约束主要有：
	1、节点是红色或黑色；
	2、根节点是黑色；
	3、每个红色节点的两个子节点都是黑色；
	4、新挂入的节点默认是黑色。

	平衡措施：
	1、变色；2、自旋。
	何为自旋？
	1、2、3、4、5、6、7...
	当插入三个数字的时候，红黑树会自动的进行旋转，即将中间的数字设为根节点，小数字设置为左边的节点，大一点的数字设置为右边的节点。

B-Tree

B-Tree的逻辑约束：
1、度(Degree)-节点的数据存储个数；
2、叶节点具有相同的深度；
3、叶节点的指针为空；
4、节点中的key从左到右递增排列

B+Tree

非叶子节点不存储data，只存储key，可以增大度；
所有叶子节点都有一个链指针；
顺序访问指针，提高区间访问的性能。

Hash

键值唯一，哈希索引明显有绝对优势；
无法完成范围查询检索；
无法利用索引完成排序，以及like 'xxx%'这样的部分模糊查询；
不支持多列联合索引的；
因为哈希碰撞的问题，索引效率是极低的。

聚簇索引与非聚簇索引、联合索引

InnoDB聚簇索引

InnoDB非聚簇索引

MyISAM索引

联合索引

常见面试题

• 聚簇索引和非聚簇索引的区别？

    聚簇索引是索引结构和数据放在一块的，非聚簇索引是索引结构和数据不在一块，可能放的只是一个标识。
  

• InnoDB怎么保证必有主键？

    (1)在PRIMARY KEY表上定义a时，InnoDB将其用作聚簇索引，为表定义一个主键；
    (2)如果在表中没有使用PRIMARY KEY定义主键，MYSQL会UNIQUE在所有键列所在的位置找到第一个索引，NOT NULL并将InnoDB用作聚集索引；
    (3)如果表没有索引PRIMARY KEY或没有合适的UNIQUE索引，则在InnoDB内部生成一个隐藏的聚集索引cEN_cuUST_INDEX，该索引在包含行lD值的合成列上命名。这些行由InnoDB分配给该表中各行的ID排序。行ID是一个6字节的字段，随着插入新行而单调增加。因此，按行ID排序的行实际上在插入顺序上。
  

• 为什么推荐使用整型的自增主键？

    (1)存储空间，整型的存储空间小
    (2)索引维护的成本
    		整型添加快，UUID可能存在重新生成索引的情况
  

• 为什么非主键索引结构叶子节点存储的是主键值而不是数据？

    (1)存储的空间：用时间换我们的空间
    (2)数据的一致性
  

• InnoDB和MyISAM的区别？

    MyISAM引擎在叶子节点上存储的是数据的地址，拿到数据的地址，再通过地址请求磁盘I/O获得数据；
    在创建表结构的时候，会在磁盘上创建三个文件，分别是frm,MYI，MYD;
    InnoDB在叶子节点上存储的是数据；
  

索引的执行计划EXPLAIN

规则：
1、全值匹配；
2、最左前缀法则；
3、不要在索引列上做任何操作(计算、函数)
4、存储引擎不能使用索引中范围条件右边的条件；
5、尽量使用覆盖索引(只访问索引的查询)
6、mysql在使用不等于的时候会失效；
7、is null,is not null使用索引；
8、like以通配符开头mysql索引失效会变成全表扫描；
9、字符串不加单引号索引失效；
10、少用or,用它连接很多情况下索引会失效；

• 联合索引规则

    1、最左匹配left；
    2、全值匹配ref；
    3、支持范围查询
  

BAT一线互联网公司工作技术