乐趣区

关于mysql:MySQL索引详解

jiezi

热衷学习，热衷生存！😄

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

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

索引是一种用于疾速查问和检索数据的数据接口。罕用的索引数据结构有：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…

退出移动版