MySQL索引篇笔记

索引作用：加速SQL的查询速度。

索引缺点：

索引的使用涉及到磁盘IO操作，索引并非越多越好，过多的索引会导致CPU使用率居高不下。数据的改动会造成索引文件改动，过多磁盘IO造成CPU复合太重。

索引分类：

物理上分为聚集索引和非聚集索引。索引是否聚集看数据和索引是否分开存放。

逻辑上：

1. 普通索引：没有任何限制，可以给任何类型字段创建普通索引（一张表的一次sql查询只能用一个索引）
2. 唯一性索引：使用Unique修饰的字段，值不能重复，如：主键索引
3. 主键索引：使用Primary Key 修饰的字段会自动创建索引
4. 单列索引：在一个字段上创建索引
5. 多列索引：表的多个字段上创建索引（多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上）
6. 全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度

索引底层原理：

索引底层实现是B树。关于为什么是B树而不用二叉平衡树：二叉平衡树存2000W数据要构建25层，读取一个索引最坏要花费25次磁盘IO。一个m=500的B树，最多三层即可。因此B树用的磁盘IO数少。

数据索引的底层操作流程：例如

select * from student where uid=5;

流程为 ：uid有索引 ==》 存储引擎 ==》 kernel ==》 磁盘IO（读取索引文件） ==》内存上 ==》用索引的数据构建B树加速索引。（注：数据索引保存在磁盘，操作系统管理内存以页面为单位，花费磁盘IO读取内存以块为单位）

B+树是B树的一个优化，B+树每一个非叶子节点，只存放key ，不放data（存储在叶子节点）。

MySQL最终为什么要采用B+树存储索引结构呢？

1. B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。
2. B-树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
3. 在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

哈希索引

考虑到哈希表的数据结构，哈希表中元素没有任何顺序可言，只能进行等值比较。范围搜索、前缀搜索、order by排序这些操作哈希索引都不适合。哈希索引只适合基于内存的存储引擎。

InooDB自适应哈希索引

InooDB存储引擎若检测到同样的二级索引不断被使用，那么它会根据这个二级索引树（B+）上的二级索引值，在内存上构建一个哈希索引，来加速搜索。自适应哈希索引本身数据维护也是要耗费性能的，并不一定会提升性能。可根据参数指标，具体分析是否关掉。