什么是索引?

索引是一种用于疾速查问和检索数据的数据结构。常见的索引构造有: 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.思考在字符串类型的字段上应用前缀索引代替一般索引

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

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

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