关于mysql:MySQL索引详解

热衷学习，热衷生存！😄

积淀、分享、成长，让本人和别人都能有所播种！😄

一、什么是索引？索引有什么作用？

索引是一种用于疾速查问和检索数据的数据接口。罕用的索引数据结构有：B树、B+树、Hash表。

索引的作用就相当于目录的作用。比方：咱们在查字典的时候如果没有目录，咱们就只能一页一页去查找字，速度很慢，如果有目录，咱们只须要查找字所在的页数，而后间接翻到那一页就能够了。

索引的长处毛病

长处

• 索引能够让咱们更快的检索出咱们须要查找的数据，这也是创立索引的最次要的起因。
• 能够通过创立惟一索引，保障数据库行数据的唯一性。

毛病

• 创立索引和保护索引须要消耗更多的工夫。当对表中的数据进行增删改时候，如果数据有索引，那么索引也须要动静的批改，会升高SQL执行效率。
• 索引须要应用物理文件贮存，会耗费肯定的空间。

然而，应用索引肯定能进步查问性能吗？

大多数状况下，索引查问都是比全表扫描的速度要快的，然而如果数据库的数据量不大，那么应用索引不肯定能带来很大的晋升。

二、索引的底层数据结构

罕用的索引底层数据结构有三种：B树、B+树、Hash表。

Hash表

哈希表就是键值对（key-value）的汇合，通过键（key）能够疾速查找到对应的值（value），查问速度靠近O(1)。

哈希表通过哈希算法（也叫散列算法）计算key对应的index，而后通过index就能够失去对应的value。哈希算法有一个Hash抵触的问题，也就是多个key最初失去的index雷同，通常应用链地址法后果Hash抵触的问题，就是将哈希抵触的数据寄存在链表中。比方JDK1.8之前你的HashMap就是通过链地址法来解决哈希抵触，JDK1.8当前HashMap为了缩小链表过长搜寻工夫过长引入了红黑树。

既然哈希表这么快，为什么MySQL没有应用其作为索引的数据结构呢？

次要有以下两个起因：

1. Hash抵触问题：咱们下面也提到过Hash 抵触了，不过对于数据库来说这还不算最大的毛病。
2. Hash表不反对程序查问和范畴查问：这是Hash表最大的毛病，也是MySQL不将Hash表作为索引数据结构最重要的起因。

B树&B+树

B树也称B-树，全称为多路均衡查找树，B+树是B树的一种变体。

B树&B+树之间的差别

• B树的所有节点既寄存键key也存放数据data，而B+树只有叶子节点寄存key和data，非叶子节点只寄存key。
• B树的叶子节点都是独立的，而B+树的叶子节点都有一条援用链指向它相近的节点。
• B树的检索过程是相当于对范畴内的每个节点的关键词做二分查找法，可能还没到叶子节点检索就完结了，而B+树的检索效率就很稳固，任何查找都是从根节点到叶子节点的过程，叶子节点的程序检索很显著。

为什么B+ 树比B 树更适宜作为索引？

1. B+树的磁盘读写代价更低

   B+树的data都寄存在叶子节点，非叶子节点只寄存key，而B树的分支节点既寄存data也寄存key，这将导致B+树的层高会小于B树，所以B+树均匀的IO次数会小于B树。

2. B+树的查问效率更稳固

   B树查问可能在非叶子节点就完结了，而B+树因为data只寄存在叶子节点，所以查问必须从根节点到叶子节点，所以查问效率更稳固。

3. B+树更便于遍历

   因为B+树的data都寄存在叶子节点，非叶子节点只寄存key，所以只需遍历叶子节点即可，而B树非叶子夜店也寄存data，要找到具体的数据须要进行二分查找。

4. B+树更善于范畴查问

   B+树叶子节点寄存data，且data是按顺序排列的双向链表，所以范畴查问更快。而B树范畴只能二分查找。

5. B+树占用内存空间小

   B+树非叶子节点不寄存data比拟小，在内存无限的状况下，相比于B树索引能够加载更多B+ 树索引。

三、索引类型

主键索引（Primary Key）

数据表的主键列应用的就是主键索引。

一张数据表有且只有一个主键，并且主键不能为null，不能反复。

在MySQL的InnoDB的表中，如果表没有设置主键时，InnoDB会主动先检查表中是否有惟一索引（Unique Key）且不存在为null的字段，如果有则抉择该字段为默认的主键，如果没有InnoDB会主动创立一个6Byte的自增主键。

主键索引如下图：

二级索引（辅助索引）

二级索引又称为辅助索引，二级索引的叶子节点存储的数据是主键。当应用二级索引的时候，先通过二级索引查问到主键，而后通过主键获取数据。

当咱们创立所以的时候须要抉择索引类型，比方：惟一索引、一般索引、前缀索引、全文索引，这些都是属于二级索引。

• 惟一索引（Unique Key）：惟一索引也是一种束缚。惟一索引的属性列不容许呈现反复的数据，然而容许数据为Null，一张表容许创立多个惟一索引。应用惟一索引的目标大部分是为了保障该属性列的唯一性，而不是为了查问效率。
• 一般索引（Index）：一般索引的惟一作用就是为了疾速查找数据，一张表容许创立多个一般索引，并容许数据反复和为null。
• 前缀索引（Prefix）：前缀索引只实用于字符串类型的数据。前缀索引是对文本的前几个字符创立索引，相比一般索引建设的数据更小。
• 全文索引（Full Text）：全文索引次要是为了检索大文本数据中的关键字信息，是目前搜索引擎数据库应用的一种技术。

二级索引如下图：

四、聚簇索引和非聚簇索引

聚簇索引

聚簇索引是索引构造和数据寄存在一起的索引，主键索引属于聚簇索引。

在MySQL中，InnoDB引擎的表的.idb文件就蕴含了该表的索引和数据，该表的索引B+树非叶子节点寄存索引，叶子节点寄存索引和索引对应的数据。

聚簇索引的长处

• 聚簇索引的查问速度十分快，因为B+树本就是一颗多均衡二叉树，叶子节点寄存索引和数据，非叶子节点寄存索引，且叶子节点也是有序的，定位到索引就能够失去数据。

聚簇索引的毛病

• 依赖有序的数据：因为B+树是多路均衡树，如果索引的数据不是有序的，那么就须要在插入时排序，如果数据是整数类型还好，否则相似字符串或者UUID这种又长又难比拟的数据，插入或者查找的速度必定慢。
• 更新代价大：如果对索引列的数据批改时，那么对应的索引也须要批改，而且聚簇索引的叶子节点还寄存这数据，批改代价很大，所以对于主键索引来说，主键个别是不能够被批改的。

非聚簇索引

下面说到的二级索引（辅助索引）都是非聚簇索引，非聚簇索引叶子节点只存储主键值。首先通过二级索引找到主键值，再通过主键索引找到数据。

非聚簇索引的长处

• 更新代价小：因为非聚簇索引的叶子节点值寄存索引不存放数据，所以更新代价小。

非聚簇索引的毛病

• 跟聚簇索引一样也是依赖有序的数据。
• 可能会二次查问（回表）：这应该是非聚簇索引最大的毛病，当通过索引查问到对应的主键时，可能还须要依据主键再到表中查问。

聚簇索引和非聚簇索引如下图：

非聚簇索引肯定要回表查问吗（笼罩索引）？

举个栗子，用户筹备应用SQL查问用户名，而用户名字刚好创立了索引，查问SQL如下：

SELECT name FROM TABLE WHERE name = 'zhangsan';

那么这个索引的key自身就是name，查问对应的name间接返回就行了，无需返回表查问。

即便是 MYISAM 也是这样，尽管 MYISAM 的主键索引的确须要回表， 因为它的主键索引的叶子节点寄存的是指针。然而如果 SQL 查的就是主键呢?

SELECT id FROM TABLE WHERE id = 1;

主键索引自身的 key 就是主键，查到返回就行了。这种状况就称之为笼罩索引了。

五、笼罩索引

如果一个索引笼罩所有须要查问的字段，咱们就称之为笼罩索引。在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键，最终要是要回表，这样就会比较慢，笼罩索引就是能够把索引间接返回不须要做回表操作。

笼罩索引即须要查问的字段正好是索引的字段，那么间接依据该索引，就能够查到数据了， 而无需回表查问。

如主键索引，如果一条 SQL 须要查问主键，那么正好依据主键索引就能够查到主键。

再如一般索引，如果一条 SQL 须要查问 name，name 字段正好有索引， 那么间接依据这个索引就能够查到数据，也无需回表。

笼罩索引如下图：

六、联结索引

联结索引就是应用表中的多个字段创立的索引，也叫组合索引或者复合索引。

最左前缀匹配准则

最左前缀匹配准则指的是，在应用联结索引时，MySQL会依据联结索引中的字段程序，从左到右顺次到查问条件中去匹配，如果查问条件中存在与联结索引中最左侧字段相匹配的字段，则就会应用该字段过滤一批数据，直至联结索引中全副字段匹配实现，或者在执行过程中遇到范畴查问，如>、<、between和已%结尾的like查问等条件才会进行匹配。

所以咱们在应用联结索引时，能够将区分度高的字段放在最右边。

七、索引下推

索引下推是MySQL5.6版本中提供的一项索引优化性能，能够在非聚簇索引遍历过程中，对索引中蕴含的字段先做判断，过滤掉不符合条件的记录，缩小回表次数。

八、创立索引的注意事项

1. 抉择适合的字段创立索引：

   • 不为NULL字段：索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库比拟难优化。如果字段频繁被查问，但又防止不了为NULL，倡议应用0,1,ture,false这样语义较为清晰的短值或者短字符作为代替。
   • 被频繁查问的字段：咱们创立索引的字段应该是查问操作十分频繁的字段。
   • 被作为条件查问的字段：被作为WHERE条件查问的字段，应该被思考建设索引。
   • 频繁须要排序的字段：索引曾经排序，这样查问能够利用索引的排序，放慢排序查问工夫。
   • 被常常频繁用于连贯的字段：常常用于连贯的字段可能是一些外键列，对于外键并肯定要建设外键，只是说该列波及到表与表的关系。对于频繁被连贯查问的字段，能够思考建设索引，进步多表连贯查问的效率。

2. 被频繁更新的字段应该谨慎倡议索引。

   尽管索引能带来查问上的效率，然而保护索引的老本也是不小的。如果一个字段不被常常查问，反而被常常批改，那么就更不应该在这种字段上建设索引了。

3. 尽可能的思考建设联结索引而不是单列索引。

   因为索引是须要占用磁盘空间的，能够简略了解为每个索引都对应着一颗B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，但批改索引时，消耗的工夫也是很多的。如果是联结索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且批改数据的操作效率也会晋升。

4. 留神防止冗余索引。

   冗余索引指的是索引的性能雷同，可能命中索引(a, b)就必定能命中索引(a) ，那么索引(a)就是冗余索引。如name,city 和name 这两个索引就是冗余索引，可能命中前者的查问必定是可能命中后者的 在大多数状况下，都应该尽量扩大已有的索引而不是创立新索引。

5. 思考在字符串类型的字段上应用前缀索引代替一般索引。

   前缀索引仅限于字符串类型，较一般索引会占用更小的空间，所以能够思考应用前缀索引代替一般索引。

九、应用索引的一些倡议

• 对于中到大型表索引都是十分无效的，然而特大型表的话保护开销会很大，不适宜建索引。
• 防止 where 子句中对字段施加函数，这会造成无奈命中索引。
• 在应用 InnoDB 时应用与业务无关的自增主键作为主键，即应用逻辑主键，而不要应用业务主键。
• 删除长期未应用的索引，不必的索引的存在会造成不必要的性能损耗 MySQL 5.7 能够通过查问 sys 库的 schema_unused_indexes 视图来查问哪些索引从未被应用。
• 在应用limit offset 查问迟缓时，能够借助索引来进步性能。

十、MySQL 如何为表字段增加索引

1. 增加 PRIMARY KEY（主键索引）

   ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

2. 增加 UNIQUE(惟一索引)

   ALTER TABLE `table_name` ADD UNIQUE ( `column` );

3. 增加 INDEX(一般索引)

   ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

4. 增加 FULLTEXT(全文索引)

   ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

5. 增加多列索引

   ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

参考：https://javaguide.cn/database…