MySQL索引与两种引擎的索引实现

索引

索引概念

是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，它是一种数据结构
相当于一本书的目录，缩小扫描范围存在的一种机制。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）

例如：
select * from t_user where name=‘jack’;
如果没有给name创建索引，MySQL会进行全扫描，将name中的值每个值都比对一遍，效率低下

索引的优缺点

优点：是为了提高查询效率， 降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
缺点：创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加
索引需要占磁盘空间
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护

提示：

索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然
后插入数据，插入完成后再创建索引。

提醒

提醒1:在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。

提醒2:在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的的物理存储编号-

提醒3:在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyIsAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。自平衡二叉树:B-Tree

索引的数据结构

hash表

Key可以存储索引列，Value可以存储行记录或者行磁盘地址。 Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

二叉查找树

可能会不分叉，不稳定

平衡二叉树

等值查找性能接近于二分查找法，时间复杂度是 O(log2n) ，但是树的高度较高， 树的高度就等于每次查询数据时磁盘 IO 操作的次数。在表数据量大时，查询性能就会很差
不支持范围查询快速查找，范围查询时需要从根节点多次遍历

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要==先把磁盘中的数据加载到内存中，==磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。**访问二叉树的每个节点就会发生一次IO，**如果想要减少磁盘IO操作，就需要尽量降低树的高度

B树

本质是多叉平衡查找树，
节点中的元素包含键值和数据
叶子节点相同的深度，叶子节点之间没有指针连接，所有元素不重复
节点中的数据索引从左向右依次递增
查找过程：

B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大

为什么二叉平衡树转B树：

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）
因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。每个节点存储多个元素，在每个节点尽可能多的存储数据。一页可以存储1000个索引（16k/16=1000）

这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了

B+树

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题
树的高度更低，有顺序的，所以范围查找效率高
b+树是b树的变种，非叶子节点只存储键值 优点：放更多的索引
叶子节点包含了所有的索引项，只有叶子节点才会存储数据
比b树多了指针连接——>优点：提高范围查询的性能
有顺序的

主要区别：
B树：非叶子节点和叶子节点都会存储数据。范围查找效率低
B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。因为有序，并且相连，所以范围查找效率高

Mysql的索引实现

MyIsam索引

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
主键索引查找：

索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中

范围查找：

辅助索引
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

InnoDB索引

主键索引（聚簇索引）
InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

主键索引

辅助索引
除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。在age上添加辅助索引

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

组合索引
创建一个联合索引 a,b,c

最左匹配原则

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

覆盖索引

覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。
查询列和索引列是一致的，所以查询是也不会扫描表，会查询索引树,不用回表

优化

避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能

如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

联合索引的使用

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间

在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景