MySQL索引

1. 索引

1.1 概念

所谓“索引”就相当于是在数据库中构建一个特殊的“目录”（一系列特定的数据结构，存放在硬盘）

通过这样的数据结构，加快查询的速度，尽可能避免针对表数据的遍历操作

因为select这样的查询操作，默认是按照“遍历”的方式来完成查询的，比如：指定 where 语句进行条件查询，遍历每一行，把这一行数据带入到条件中，看是否成立，该遍历时间复杂度为O(N)

tip：此处的每一次取一行数据，都是要读取硬盘的，虽然也是 O(N)，但是和内存中操作的 O(N) 是有本质区别的

1.2 缺点

1) 引入索引，需要消耗额外的存储空间

对于“后端开发”（也叫服务器开发）来说，可以通过加硬盘的操作忽略该缺点影响

但是对于数据库在一些“嵌入式设备”上使用的，硬件条件比较紧张，就不能通过随便加硬盘解决了

2) 引入索引，确实能提高 查询 的效率，但可能会影响 增删改 的效率（有可能会更快、不变、更慢）

更慢是因为 增删改 时，需要同步更新维护索引；更快是因为可以通过条件判断的方式来进行删除等操作

1.3 使用场景

如果要对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询
• 该数据库表的插入操作及对这些列的修改操作频率较低
• 索引会占用额外的磁盘空间

满足以上条件时，则可考虑对表中的这些字段创建索引，以提高查询效率

反之，如果非条件查询列，或者经常做插入、修改等操作、或磁盘空间不足时，不考虑创建索引

1.4 使用

创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时，会自动创建对应列的索引

1. 查看索引

show index from 表名;

 如果是针对 id 列创建的索引，下面查询时，以 id 为条件时，才能使索引生效，才能提升查询速度

---

2. 创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

示例：创建班级表中，name 字段的索引

创建索引是一个危险操作

如果是针对空表，或表中数据比较少（几千、几万...），这样的数据量对于计算机来说，不算啥，创建索引也就谈不上危险不危险

一旦表的数据量比较大，千万级别... 此时创建索引操作，就可能会触发大量的硬盘IO，直接把机器卡死了

---

3. 删除索引

drop index 索引名 on 表名;

示例：删除班级表中 name 字段的索引

---

2. 索引背后的数据结构

所谓“构建索引”其实就是引入一些数据结构，对数据进行存储，从而提高查找的速度

数据结构中，二叉搜索树和哈希表可以提高查询效率，但其都不适合给数据库做索引

1) 二叉搜索树最大的问题在于“二叉”，当要保存的元素多的时候，就会使整个树的高度变得很高

一旦高度太高，比较的次数就会变多，硬盘上每次多比较一次都会占用很多资源

2) 哈希表最大的问题在于，其只能进行“相等”查询，无法进行 > < 这样的“范围查询”，也无法进行 like 模糊查询

tip：哈希表是通过哈希函数把查询的 key 映射成数组下标（ key1 < key2 != hash(key1) < hash(key2) ）

2.1 B 树

B 树，也可以写成 B - 树（这里的 - 不是减号，而是连接符）

B 树就是 N 叉搜索树，每个节点可以有多个子树（树的度为 N ），这样就可以降低树的高度了，每个节点上就会存储多个 key 值

像这样某个节点上保存了 N 个 key 就能延伸出 N+1 个子树

此时进行查询时，针对每个节点都需要比较多次，才能确定下一步走哪个区间

此时虽然高度降低了，但是每个节点的比较次数变多了，即使这样也比二叉搜索树的优势大很多

这里每个节点访问的时候只需要一次硬盘 IO ，即可将这个节点上的内容都读取出来，接下来就是在内存中进行比较了

此处与二叉搜索树相比，主要目的不是为了减少比较的次数，而是要减少 硬盘 IO 的次数

---

2.2 B+ 树

B+ 树，针对 B 树做出进一步改进的数据结构，B+ 树也是 N 叉搜索树

B+ 树与 B 树的区别

1) B+ 树有 N 个 key ，划分出 N 个区间

2) 父节点中的 key 值会在下面子节点中再次出现（以子节点中最大值的形式）

3) B+ 树把叶子节点像链表一样首位相连，所有的叶子节点就构成了整个数据的全集，此时进行“范围查询”就会非常方便

B+ 树的优势

1) N 叉搜索树，高度比较低，硬盘 IO 次数比较少

2) 叶子节点是全集，并且用链表结构连接，非常便于“范围查询”

3) B+ 树所有的查询都是落在叶子节点上完成的，任何一次查询经历的 IO 次数和比较次数都是差不多的，称其查询的开销是稳定的（“稳定”是一个很大的优势，稳定意味着成本是容易被预估出来的）

4) 由于 B+ 树中叶子节点是全集，非叶子节点上不必存储“数据行”（数据库中的“表”只是一个逻辑上的结构，在物理上是通过 B+ 树这样的结构来组织的），只需要存储索引列的 key 即可，使得非叶子节点消耗的空间比较少，这样的数据甚至可以全部加载到内存中，进一步减少硬盘 IO 的次数