MySQL索引原理

一、 什么是索引

索引是帮助MySQL高效查询和检索数据的排好序的数据结构，索引的数据结构包括：二叉树、红黑树、Hash树、B-树、B+树

二、 索引的优缺点

优点：

1. 加快查询和检索数据的速度

2. 唯一索引可以保证数据库中一行数据的唯一性

缺点:

1. 维护索引需要消失时间，对数据增删改时，数据有索引，索引也需要改，降低sql效率

2. 索引需要物理文件存储，消耗磁盘空间

使用索引一定能提高查询性能吗?
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

三、 索引数据结构

1. 二叉树

特点：左边小于父节点，右边大于等于父节点

缺点：如果是单边的增长数据链的时候，二叉树会变成链表，那么二叉树就没有意义了

2. 红黑树

特点：在二叉树的基础上，可以自平衡

缺点：在数据量比较大的情况下，树的高度不可控，如果需要查询的数据在叶子节点，要遍历树的高度

3.Hash表

特点：对索引值进行hash运算，得到磁盘文件指针，查询where index=1的等值预算的时候性能非常高

缺点：空间能力差，做范围查询where index>1的时候不好做索引

4.B-Tree

叶节点具有相同的深度，叶节点的指针为空

所有索引元素不重复

节点中的数据索引从左到右递增排列

对比:比红黑树楼层低，查询效率高

缺点：每个索引对应一个值，导致索引占用空间太大

5. B+树

特点：

非叶子节点不存储数据data，只存储索引（冗余），可以放更多的索引

叶子节点包含所有索引字段

叶子节点用指针连接，提高区间访问的性能

五、 索引查询原理

为什么上千万的表，用索引查任然很快？

mysql底层使用B+Tree数据结构，一个节点页大小为16kb（show global status like 'Innodb_page_size'可以查出来）

一个行高为3的B+Tree能存多少数据？

假设存bigint（8b）类型数据，每个索引元素边上有个一个空的存储空间（6b），用于存储下一个磁盘文件空间地址，16kb/(8b+6b) = 1170,一个非叶子节点可以存储大概1170个索引；叶子节点除了存储索引还会存储索引，还会存储数据data(索引所在行的数据的磁盘文件地址或索引所在行的其他列)，假设叶子节点索引+data占用1kb；那个一个行高为3的B+Tree可以存储大概1170x1170x16大概2千万条数据

高版本的msql将非叶子节点索引放在内存中，只有叶子节点放在磁盘中，查询的时候在内存中用折半查到定位到数据的磁盘地址。

六、索引存储

存储位置：mysql安装目录data下，每个数据库有个目录，里面存储每个表的数据，每个表对应各自的文件。

存储方式：索引不一样存储方式不一样

1. MyISAM：非聚集索引（非聚簇索引），有三个文件，*.frm(表结构信息、框架)、*.MYD（MyISAMData行记录数据）、*.MYI（MyISAMIndex索引）

        查询会回表：先在一个文件查到索引，去另一个文件去拿数据，所以会比聚集索引慢

Col1等数据放在*.MYD文件

2. InnoDB：

(1) 聚集索引（聚簇索引），有两个文件，*.frm(表结构信息、框架)、*.ibd（数据和索引）

        表数据文件按B+Tree组织一个索引结构文件

        聚集索引：叶节点包含完整的数据记录

(2) InnoDB二级索引数据存的是所在行的主键

七、索引类型

1. 主键索引（primary key）

表的主键使用的索引，一张表只能有一个，不可重复

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

2. 二级索引(辅助索引)

二级索引存储的数据主键，二级索引包括：

(1) 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
(2) 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
(3) 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
(4) 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

3. 聚集索引与非聚集索引

聚集索引

即索引结构和数据存储在一起，如InnoDB引擎B+Tree非叶子节点存储索引，叶子节点存储索引和数据。

优点：查询非常快，因为B+树是一颗多叉平衡树，叶子节点又是有序的，定位到索引节点就相当于定位到数据了

缺点：

(1) 依赖有序性：因为B+树是多路平衡树，如果插入数据不是有序的，需要排序，如果是字符串（比如UUID）类型的索引，比较大小比较难，插入或者查询速度会比较慢

(2) 更新付代大：索引列数据被修改时，修改索引的同时，还要修改叶子节点的数据，修改代价大，所以一般主键是不可修改的

非聚集索引

索引结构和数据分开存放的索引，二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：

更新代价小：叶子节点不存放数据

缺点：

(1) 依赖有序性：和聚集索引一样，不赘述

(2) 可能会回表（二次查找）：通过索引查到指针或者主键后，要再查询一次拿到数据

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，被称之为“覆盖索引”。即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

非聚集索引不一定会回表查询：覆盖索引

八、创建索引注意事项

1.选择合适的字段创建索引：

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

九、使用索引的一些建议

1. 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引。
2. 避免 where 子句中对字段施加函数，这会造成无法命中索引。
3. 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
4. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。
5. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能。

十、为什么InnoDB表要建主键，且建议使用整型自增主键

InnoDB表需要一个B+Tree的数据结构组织所有数据，如果有主键，默认使用主键索引来组织整张表，如果没有mysql会自己找一个列没有重复数据的列，当做索引元素来组织整张表，如果找不到，mysql会自己维护一个隐藏列来组织表结构。

自增主键推荐是整型：因为索引是按照顺序（递增）排列的，所以存储和查询要比大小，整型比大小快，如果是UUID这样的主键，要比较字符串要逐位参考ASCII码表比较，如果就最后一位不一样，要比较到字符串的最后一位；而且字符串占用空间大。

自增主键推荐自增：因为索引是按照顺序（递增）排列的，如果是递增的只需要往B+Tree的最后放就行了，非自增的会发树分裂、自平衡。自增效率高。

十一、为什么一般不用Hash索引

Hash不支持范围查找

哈希索引底层实现是数组+链表，查询最快

mysql底层的自己实现了hash算法，hash冲突率很低

十二、为什么索引不用B-Tree作为底层实现

B-Tree没有区间指针，范围查找没有办法在子节点索引树上进行查找，只能全表扫描或者从根节点重新查找。同时B树每个索引都是存放数据的，所以一个非叶子节点可以存放的索引树会小很多，所以树的高度会比B+Tree高很多，查询就会慢很多

十三、索引最左前缀原则

联合主键，按字段从左到右顺序一次比较，先比第一个，第一个相同比第二个…

十四、 MyISAM引擎和InnoDB引擎对比

1. 是否支持行级锁

MyISAM：只有表级锁（table-leve locking），

InnoDB：支持行级别锁（row-level locking）和表级别锁，默认行锁

也就是说，MyISAM锁就锁住整张表，在并发写的情况下很不友好。所以InnoDB在并发写的性能更好

2. 是否支持事务

MyISAM：不支持事务

InnoDB：支持事务，有提交（commit）和回滚（rollback）事务的能力

3. 是否支持外键

MyISAM：不支持

InnoDB：支持

拓展：一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。

4. 是否支持数据库异常崩溃后数据恢复

MyISAM：不支持

InnoDB：支持，在数据库崩溃后，重启会恢复到崩溃前的状态。恢复依赖redo log（重做日志，事务的持久性也是依赖这个日志得以保证的）

5. 是否支持 MVCC

MyISAM：不支持

InnoDB：支持

MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。MyISAM连行级锁都不支持肯定支持不了MVCC。

十五、引擎选型

        大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点。

        不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用