深入浅出了解mysql索引

一、什么是索引？

数据库的索引大家应该都不陌生，当sql执行时间过长，我们一般都是给某个字段加个索引吧。那么为什么要加索引呢？
索引的出现是为了提高数据查询的效率，就像书本的目录一样，通过目录我可以快速定位到想要的数据。
索引其实就是一种用于快速查询和检索数据的数据结构，它是在存储引擎层实现的，所以不同的存储引擎具有不同的索引类型和实现。

二、索引的优缺点

优点

1. 能够大幅减少服务器扫描的数据行数。
2. 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）
3. 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

基于以上所以能够提升查询的效率

缺点

1. 占用物理存储空间：索引需要使用物理文件存储，也会耗费一定空间。
2. 创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的维护，会降低sql执行效率。
3. innoDb引擎可能会由于索引数据删除，或者页分裂等原因，导致数据页产生空洞，或者表中部分数据删除，但是索引还保留未释放。导致占用空间过大。解决这种问题通常通过重建索引来解决，通过alter table T engine=InnoDB这个语句来重建索引。

所以并不是索引越多越好，一定要针对业务需求来选择合适的索引。

三、常见的索引模型

索引一般常用的三种数据结构为：哈希表、有序数组、搜索树。

1. 哈希表

哈希表是一种 key-value（键值对的存储结构），通过key来查询value只需要 O(1)的时间复杂度。

优点

查询效率高。

缺点

1. 哈希结构是一种无序结构，无法用于排序和分组。
2. 只支持精确查找，无法用于部分查找和范围查找。

哈希表这种结构只适用于等值查询的场景，比如 Memcached及其他一些NoSQL引擎。

2. 有序数组

因为数据是有序的，所以可以通过二分查找法查询数据，时间复杂度为O(log(N))。

优点

在等值查询和范围查询中的性能优秀。

缺点

为了保持数据的有序，在删除和插入的时候需要移动后面所有的记录，影响性能。

有序数组只适用于静态存储引擎，数据保存以后，不在更改或者改动非常少。

3. 搜索树

二叉搜索树的特点是：每个节点的左节点小于父节点，右节点大于父节点。一次查询的时间复杂度为O(log(N))。
数据库存储的时候一般都是选择N叉树而不是二叉树，因为索引不止存于内存中，还要保存到硬盘上，为了防止数据过于分散，每次需要访问多个数据块，所以要使用N叉树，减少磁盘访问。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

四、B树 和 B+树的区别

1. B树的所有节点既存放键(key)也存放 数据(data);而B+树只有叶子节点存放key和data，其他内节点只存放key。
2. B树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
3. B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

五、MySQL索引

目前mysql使用最广泛的引擎是Innodb。InnoDB使用的是B+树结构。每一个索引在InnoDB中都有对应的一个B+树。因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多，因为B+Tree的有序性，所以除了用于查找，还可以用于排序和分组。

从图中可以看出，根据叶子节点的内容，索引类型可以分为主键索引(也称作聚蔟索引)和非主键索引(二级索引)。
主键索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
非主键索引的叶子节点的data域记录着主键的值，因此在使用非主键索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。这称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

1. 索引的维护

B+树为了维护索引的有序性，所以在插入新值的时候需要做必要维护，如果是插入操作，那么后面的数据都需要挪动。如果当前所处的数据页数据已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂，不仅影响性能，还影响数据页的利用率，原本一个数据页的数据分裂为两个，整体利用率降低大约50%。
所以基于此，最好是使用自增主键，这样每次都是追加数据，不会涉及挪动其他数据，也不会触发叶子节点的分裂

除了性能外，在存储空间上由于非主键索引的叶子节点上记录的是主键的值，所以，选择不同的值做主键也会在存储空间上造成一定影响。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

什么场景用业务字段做主键？

1.只有一个索引
2.该索引必须是唯一索引

2. 索引类型

主键索引(Primary Key)

数据表的主键列使用的就是主键索引，也叫聚簇索引。
一张数据表有只能有一个主键，并且主键不能为null，不能重复。
在Mysql中，InnoDB引擎的表的 .ibd文件就包含了该表的索引和数据，对于InnoDB引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

二级索引(辅助索引)

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

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

唯一索引(Unique Key)

唯一索引也是一种约束。**唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。**建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index)

普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。

前缀索引(Prefix)

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

全文索引(Full Text)

全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

3. 索引优化

单列索引

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
例如下面的语句

SELECT a FROM t WHERE b + 1 = 5;

联合索引（多列索引）

在需要使用多个列作为条件进行查询时，使用联合索引比使用多个单列索引性能更好。例如下面的语句中，最好把 b 和 c 设置为联合索引。

SELECT a FROM t WHERE b = 1 AND c = 2;

覆盖索引

索引包含所有需要查询的字段的值。
例如下面的语句中ID 为主键,而ID的值已经在b索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 b 已经“覆盖了”我们的查询需求，我们称为覆盖索引

SELECT ID FROM t WHERE b = 2;

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

想上图这种情况，我们设置姓名和年龄为联合索引。（name,age）。
如果执行下面的语句

SELECT ID FROM t WHERE name like '张%';

这时，你也能够用上这个索引，查找到第一个符合条件的记录，然后向后遍历，直到不满足条件为止。不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

原则上是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推

如果是下面这个语句

select * from t where name like '张%' and age= 18 ;

在name满足查询条件时，MySQL5.6之前，只能从主键1开始一个个回表。到主键索引上找出数据行，再对比字段值。而MySQL5.6引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

过程如下图

在无索引下推中,Innodb并不会去看age的值，只是把姓名第一个为张的数据取出来回表。需要回表三次.
而在索引下推中，Innodb在联合索引内部（name,age）就判断了age的值。只需要回表一次就可以.

4. 什么样的字段适合做索引

不为NULL的字段

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

被频繁查询的字段

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

被作为条件查询的字段

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

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

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

5. 什么样的字段不适合做索引

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

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

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

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

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

注意避免冗余索引

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

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

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

6. 索引的使用条件

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

对于中到大型的表，索引就非常有效；

但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。