mysql--索引

来源：https://zhuanlan.zhihu.com/p/453658511

 

1、什么是索引

　　索引其实是⼀种数据结构，能够帮助我们快速的检索数据库中的数据

 

2、索引的优点缺点

　　优点 ：
　　　　1、提⾼数据检索的效率，降低数据库 IO 成本
　　　　2、通过索引对数据进⾏排序，降低数据排序成本，降低 CPU 的消耗
　　缺点：
　　　　1、建⽴索引需要占⽤物理空间

 　　　　2、降低表的增删改效率，因为每次对表进⾏数据修改，需要动态维护索引，带着增删改时间变⻓。

 

3、什么情况下需要建索引

　　1、主键⾃动创建唯⼀索引

　　2、较频繁作为查询的字段
　　3、查询中的排序字段，查询中统计或者分组的字段

 

 

4、什么情况下不需要建索引

　　1、表记录太少的字段

　　2、经常增删改的字段
　　3、唯⼀性太差的字段，不适合单独创建索引，⽐如性别，名族，政治⾯貌

 

5、索引有哪⼏种分类

　　1、普通索引：是基本的索引，它没有任何限制

　　2、唯⼀索引：索引列的值必须唯⼀，但是允许有空值。如果是组合的唯⼀索引，则列值的组合必须唯⼀。⼀个表可以有多个唯⼀索引。

　　3、主键索引：是特殊的唯⼀索引，⼀个表只能有⼀个主键，不允许有空值。⼀个表只能有⼀个主键索引。

　　4、组合索引：多个列组合为⼀个索引，实际开发总推荐使⽤组合索引。

　　5、全⽂索引： FULLTEXT ⽤于搜索很⻓⼀篇⽂章的时候，效果最好。只能⽤于 InnoDB 或 MyISAM 表，只能为 CHAR、 VARCHAR、 TEXT 列创建

 

6、索引的数据结构有哪些

　　索引的数据结构主要有 B+ 树和哈希表，对应的索引分别为 B+树索引和 Hash 索引， InnoDB 引擎类型有 B+ 树索引和 Hash 索引，默认的索引类型是B+树索引。

　　

　　1、 Hash 索引介绍

　　　　哈希索引是基于哈希表实现的，当我们要给某张表某列增加索引时候，存储引擎会对这列进⾏哈希计算，得到哈希码，将哈希码的值作为哈希表的key值，将指向数据⾏作为哈希表的value值。这样查找⼀个数据的时间复杂度是O(1)，⼀般都⽤于精确查找。

　　

　　缺点：

　　　　1、Hash索引仅仅能满足=, IN 和 <=> 查询，不能用于范围查询。

　　　　2、Hash 索引无法被用来数据的排序

　　　　3、Hash索引不能利用部分索引键查询

　　　　4、Hash索引任何时候都不能避免全表扫描

　　

　　

　　2 、B 树

　　相对于 CPU 和内存的操作，磁盘的 IO开销很大，非常容易造成系统的性能瓶颈。为什么索引能提升数据库的查询效率，根本原因就是在于减少了查询过程的IO次数。

　　

 

　　

　　B 树的两个特点：

　　1、树内的每个节点都存储数据

　　2、叶子节点之间没有指针连接

 

　　3、B+ 树

　　

 

　　

　　B+ 树和B-树有几点不同：

　　1、非叶子节点只存储 键值信息

　　2、所有的叶子之间有一个链指针

　　3、数据都存放在叶子节点中

 

7、为什么 B+ 树比 B 树更适合实现数据库索引

　　1、B+树叶子节点之间用有序链表连接，所以扫描全部的数据，只需要扫描一遍叶子节点，这点有利于扫库和范围查询。

　　2、B+树相比B树减少了 I/O次数，B+树的非叶子节点只存储索引，因此单页的数据可以存储更多的索引。

　　3、B+树的查询效率更加稳定，任何关键字的查找必须走一条从根叶子节点的路。所有关键字查询的路径相同，导致任何一个数据的查询效率相同。

 

8、聚集索引相对非聚集索引的区别

　　1、聚集索引

　　聚簇索引就是按照每张表的主键构造一个B+树，同时叶子节点中存放的就是整张表的行记录数据，聚集索引的叶子节点称为数据页。这个特性也决定了索引组织表中的数据也是索引的一部分，每张表只能拥有一个聚簇索引。

　　

　　1、如果表设置了主键，则主键就是聚簇索引。

　　2、如果表没有主键，则会默认第一个NOT NULL，且唯一的（UNIQUE）的列作为聚簇索引。

　　3、如果以上都没有，则会默认一个隐藏的 row_id 作为聚簇索引。

　　聚集索引的叶子节点就是整张表的行记录，InnoDB 主键使用的是聚簇索引。聚簇索引要比非聚簇索引的查询效率高。

 

　　2、非聚集索引

　　InnoDB 的普通索引叶子节点存放的是 主键（聚集索引）的值。

 

create table user(
    id int(10) auto_increment,
    name varchar(30),
    age tinyint(4),
    primary key (id),
    index idx_age (age)
    )engine=innodb charset=utf8mb4;
    
id 字段是聚簇索引，age 字段是普通索引（二级索引）  

 

 

 

 

 

 

 

 

1. 先通过普通索引 age=40 定位到主键值 id=3

2. 再通过聚集索引 id=3 定位到行记录数据(就是上面这一步)

 

8、什么是回表查询

　　上面的非聚簇索引查询就是回表查询。

　　先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

 

9、什么是覆盖索引

　　只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

　　如果我们把上面的非聚簇索引查询的sql改下

　　　　select id,age from user where age = 30; 

　　这个sql我们是不是就不用回表查询了，因为在非聚簇索引的叶子节点上已经有id和age的值。所以根本不需要拿着id的值再去聚簇索引定位行记录数据了。也就是在这一颗索引树上就可以完成对数据的检索，这样就实现了覆盖索引。

 

　　如果这个sql是

　　select id,age,name from user where age = 30;

　　那就不能实现索引覆盖了，因为name的值在age索引树上是没有的，还是需要拿着id的值再去聚簇索引定位行记录数据。但是如果我们对age和name做一个组合索引idx_age_name(age,name),那就又可以实现索引覆盖了。

 

10、什么是最左匹配原则

　　如果我们创建了(age, name)的组合索引，那么其实相当于创建了(age)、(age, name)两个索引，这被称为最佳左前缀特性。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

　　最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

　　这是为什么呢？

　　我们这里以组合索引(age, name)来画一下索引树就明白了。

 

　　我们再仔细观察索引结构，可以看到索引key在排序上，首先按age排序，age相等的节点中，再按name排序。因此，如果查询条件是age或age和name联查时，是可以应用到索引的。如果查询条件是单独使用name，因为无法确定age的值，因此无法使用索引。

　　所以在实际开发中如果你创建了(age, name)的组合索引，那就根本无需再去单独创建age的索引。同时也建议创建组合索引，只是在创建的时候需要考虑将最常用字段的列放在最左边，依次递减。

 

　　

11、索引失效的场景有哪些

　　

1. 组合索引未使用最左前缀，例如组合索引（age，name），where name='张三'不会使用索引;

2. or会使索引失效。如果查询字段相同，也可以使用索引。例如where age=20 or age=30（索引生效），where age=20 or name=‘张三’（这里就算你age和name都单独建索引，还是一样失效）;

3. 如果列类型是字符串，不使用引号。例如where name=张三(索引失效），改成where name=‘张三’(索引有效);

4. like未使用最左前缀，where A like '%China';

5. 在索引列上做任何操作计算、函数，会导致索引失效而转向全表扫描;

 

12、索引的设计原则

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

• 利用最左前缀原则。