mysql索引

前言

​ 总结一下关于 mysql 里面索引的知识

什么是索引

​ 索引是一种用于快速查询和检索数据的数据结构。索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

为什么要使用索引

使用索引的优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
• 使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 因为使用索引本来就是一个有序的结构了，在有序的结构上进行分组和排序效率较高
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 例如使用外键索引可以保证域完整性和引用完整性

使用索引的缺点

• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
• 创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

索引的实现方式

​ 索引有三种实现方式，分别是哈希索引、B树索引以及B+树索引。

哈希索引

​ 哈希索引定位快，能在较短的时间内快速定位到目标所在的位置，这个速度是其他两者无法比较的。

​ 但是哈希索引也有很明显的问题：

• 哈希冲突，由于哈希都是使用数组来存储，所以哈希冲突是无法避免的，处理哈希冲突成为哈希索引的一个缺点，但是这还不是最大的缺点。
• 不支持顺序和范围查询，因为哈希存储是无序的，所以无法做到范围查询，如果执行SELECT * FROM tb1 WHERE id < 500;这种语句，难道要查询500次，这显然效率低下。

B 树和 B+ 树索引

​ B 树指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

​ B+ 树 是基于 B 树和链表进行实现，B+树的叶子节点有一条引用链指向与它相邻的叶子节点，它即具有 B Tree 的平衡性，并且可以通过链表来提高区间查询的性能。

两者的区别：

• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的所有节点既存放 键(key) 也存放 数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

索引类型

主键索引

​ 数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

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

辅助索引

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

​ 唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚集索引和非聚集索引

聚集索引

​ 聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

聚集索引的优点

​ 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

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

非聚集索引

​ 非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点

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

非聚集索引的缺点

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

覆盖索引

​ 非聚集索引不一定要回表查询，例如某个表有主键索引(id)以及一个辅助索引(name)，这时候要执行语句。

SELECT id FROM table WHERE name='guang19';

​ 因为辅助索引里面存储的就是主键，那么就不需要再去主键索引里面查询了。

索引创建规则

合适的段

1.不为 NULL 的字段

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

2.被频繁查询的字段

​ 我们创建索引的字段应该是查询操作非常频繁的字段。

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

​ 被作为 WHERE 条件查询的字段，应该被考虑建立索引。

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

​ 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

不合适的段

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

​ 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

2.不被经常查询的字段没有必要建立索引

3.尽可能的考虑建立联合索引而不是单列索引

​ 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引

​ 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引

​ 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

最左前缀原则

​ MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            Copy to clipboardErrorCopied

​ 这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

​ 由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

​ 新版本好像解决这个问题了，但是还是需要注意一下，防止什么时候就在老版本遇到。

冗余索引

​ 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

​ MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

优化数据查询

1. 减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

联合索引在 B+ 树上的存储结构

​ 如果一个表的结构如下：

​ 那么当对 b、c 以及 d 建立索引的时候，索引的结构如下：

​ 也就是先对 b 进行排序，如果 b 值相等，那么再以 c 值进行排序，以此类推

赏

谢谢你的心意