乐趣区

关于java:数据库索引总结二

jiezi

什么是索引?

索引是一种用于疾速查问和检索数据的数据结构。常见的索引构造有: B 树，B+ 树和 Hash。

索引的作用就相当于目录的作用。打个比方: 咱们在查字典的时候，如果没有目录，那咱们就只能一页一页的去找咱们须要查的那个字，速度很慢。如果有目录了，咱们只须要先去目录里查找字的地位，而后间接翻到那一页就行了。

为什么要用索引? 索引的优缺点剖析

索引的长处

能够大大放慢 数据的检索速度（大大减少的检索的数据量）, 这也是创立索引的最次要的起因。毕竟大部分零碎的读申请总是大于写申请的。 另外，通过创立唯一性索引，能够保障数据库表中每一行数据的唯一性。

索引的毛病

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

B 树和 B + 树区别

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

Hash 索引和 B+ 树索引优劣剖析

Hash 索引定位快

Hash 索引指的就是 Hash 表，最大的长处就是可能在很短的工夫内，依据 Hash 函数定位到数据所在的地位，这是 B + 树所不能比的。

Hash 抵触问题

晓得 HashMap 或 HashTable 的同学，置信都晓得它们最大的毛病就是 Hash 抵触了。不过对于数据库来说这还不算最大的毛病。

Hash 索引不反对程序和范畴查问(Hash 索引不反对程序和范畴查问是它最大的毛病。

试想一种状况:

`SELECT * FROM tb1 WHERE id < 500;` 

*   1

B+ 树是有序的，在这种范畴查问中，劣势十分大，间接遍历比 500 小的叶子节点就够了。而 Hash 索引是依据 hash 算法来定位的，难不成还要把 1 – 499 的数据，每个都进行一次 hash 计算来定位吗? 这就是 Hash 最大的毛病了。

索引类型

主键索引(Primary Key)

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

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

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

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，能够定位主键的地位。

惟一索引，一般索引，前缀索引等索引属于二级索引。

PS: 不懂的同学能够暂存疑，缓缓往下看，前面会有答案的，也能够自行搜寻。

  1. 惟一索引 (Unique Key)：惟一索引也是一种束缚。 惟一索引的属性列不能呈现反复的数据，然而容许数据为 NULL，一张表容许创立多个惟一索引。建设惟一索引的目标大部分时候都是为了该属性列的数据的唯一性，而不是为了查问效率。
  2. 一般索引 (Index)： 一般索引的惟一作用就是为了疾速查问数据，一张表容许创立多个一般索引，并容许数据反复和 NULL。
  3. 前缀索引(Prefix)：前缀索引只实用于字符串类型的数据。前缀索引是对文本的前几个字符创立索引，相比一般索引建设的数据更小，因为只取前几个字符。
  4. 全文索引(Full Text)：全文索引次要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库应用的一种技术。Mysql5.6 之前只有 MYISAM 引擎反对全文索引，5.6 之后 InnoDB 也反对了全文索引。

二级索引:

汇集索引与非汇集索引

汇集索引

汇集索引即索引构造和数据一起寄存的索引。主键索引属于汇集索引。

在 Mysql 中，InnoDB 引擎的表的 .ibd文件就蕴含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引 (B+ 树) 的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

汇集索引的长处

汇集索引的查问速度十分的快，因为整个 B + 树自身就是一颗多叉均衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

汇集索引的毛病

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

非汇集索引

非汇集索引即索引构造和数据离开寄存的索引。

二级索引属于非汇集索引。

MYISAM 引擎的表的.MYI 文件蕴含了表的索引，该表的索引 (B+ 树) 的每个叶子非叶子节点存储索引，叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。

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

非汇集索引的长处

更新代价比汇集索引要小。非汇集索引的更新代价就没有汇集索引那么大了，非汇集索引的叶子节点是不存放数据的

非汇集索引的毛病

  1. 跟汇集索引一样，非汇集索引也依赖于有序的数据
  2. 可能会二次查问(回表) : 这应该是非汇集索引最大的毛病了。当查到索引对应的指针或主键后，可能还须要依据指针或主键再到数据文件或表中查问。

这是 Mysql 的表的文件截图:

汇集索引和非汇集索引:

非汇集索引肯定回表查问吗(笼罩索引)?

非汇集索引不肯定回表查问。

试想一种状况，用户筹备应用 SQL 查问用户名，而用户名字段正好建设了索引。

 `SELECT name FROM table WHERE username='guang19';` 

*   1

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

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

`SELECT id FROM table WHERE id=1;` 

*   1

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

笼罩索引

如果一个索引蕴含（或者说笼罩）所有须要查问的字段的值，咱们就称之为“笼罩索引”。咱们晓得在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢笼罩索引就是把要查问出的列和索引是对应的，不做回表操作！

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

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

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

笼罩索引:

索引创立准则

单列索引

单列索引即由一列属性组成的索引。

联结索引(多列索引)

联结索引即由多列属性组成索引。

最左前缀准则

假如创立的联结索引由三个字段组成:

`ALTER TABLE table ADD INDEX index_name (num,name,age)` 

*   1

那么当查问的条件有为:num / (num AND name) / (num AND name AND age)时，索引才失效。所以在创立联结索引时，尽量把查问最频繁的那个字段作为最左 (第一个) 字段。查问的时候也尽量以这个字段为第一条件。

但可能因为版本起因(我的 mysql 版本为 8.0.x), 我创立的联结索引，相当于在联结索引的每个字段上都创立了雷同的索引:

无论是否合乎最左前缀准则，每个字段的索引都失效:

索引创立留神点

最左前缀准则

尽管我目前的 Mysql 版本较高，如同不恪守最左前缀准则，索引也会失效。然而咱们仍应恪守最左前缀准则，免得版本更迭带来的麻烦。

抉择适合的字段

1. 不为 NULL 的字段

索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查问，但又防止不了为 NULL，倡议应用 0,1,true,false 这样语义较为清晰的短值或短字符作为代替。

2. 被频繁查问的字段

咱们创立索引的字段应该是查问操作十分频繁的字段。

3. 被作为条件查问的字段

被作为 WHERE 条件查问的字段，应该被思考建设索引。

4. 被常常频繁用于连贯的字段

常常用于连贯的字段可能是一些外键列，对于外键列并不一定要建设外键，只是说该列波及到表与表的关系。对于频繁被连贯查问的字段，能够思考建设索引，进步多表连贯查问的效率。

不适合创立索引的字段

1. 被频繁更新的字段应该谨慎建设索引

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

2. 不被常常查问的字段没有必要建设索引

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

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

4. 留神防止冗余索引

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

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

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

应用索引肯定能进步查问性能吗?

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

退出移动版