MySQL索引

1. 什么是索引

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现.

数据库中的表, 数据, 索引之间的关系相当于书, 书籍内容和目录之间的关系; 索引所起到的作用和目录也相似, 可以用于快速定位, 检索数据, 索引可以提高数据库的性能, 避免出现顺序遍历的情况, 优化了查询速度.

2. 索引的工作原理

2.1 为什么 MySQL 的索引结构不使用哈希表或者二叉搜索树?

哈希表需要明确知道 key 值是什么, 才可以通过哈希函数计算得到下标, 进而进行查找得到数据, 这样在模糊匹配的时候, key 值无法确定, 就无法利用哈希表进行查找了.

二叉搜索树可以处理模糊查找的场景.

实质上, MySQL 的索引结构使用的是 B+ 树(N叉搜索树)这样的结构

2.2 B 树 / B- 树

B树启发于二叉搜索树, 二叉搜索树的特点是, 每个非叶子节点都有两个孩子节点, 但这样会导致在数据量非常大的情况下, 二叉搜索树的深度过深, 搜索的时候, 自根节点向下搜索需要访问的叶子结点也会非常多, 大大降低了查询效率.

B树结构如下:

特点:
每个节点都可以储存多个数据, 这些多个数据就划分出了一定的区间, 进一步数据结合着这些区间来摆放, 在搜索的时候就不用访问那么多的叶子节点, 而是直接在更加细化的区间里面去查找, 这样就减小了查找效率.

2.3 B+ 树

B+ 树实质上是 B 树的一种变形

结构如下:

特点:
a. B 树中的非叶子结点可能也会存储数据, 但是 B+ 树的数据一定存放在叶子结点上, 非叶子节点只是用来辅助进行查找
b. 每一层兄弟节点之间都是相互连通的(类似于链表), 这使得遍历起来更加方便, 尤其是指定区间查找的时候.

总结:
B+ 树相比于哈希表来说, 可以处理模糊查询的场景
B+ 树相比于二叉搜索树来说, 高度更低, 查找效率更高
B+ 树相比于 B 树来说:
(a) 单一节点可以储存更多的数据, 降低查询的 I/O 次数
(b) 所有的查询都要查找到叶子结点, 使得查询性能更稳定
© 所有的兄弟节点之间相连形成有序链表, 便于范围查找

3. 索引的使用场景
   (1) 数据量大, 且经常对这些列进行条件查询
   (2) 索引会降低插入和删除的效率, 所以该数据库的插入操作以及对这些列的修改操作频率要低
   (3) 索引会占用额外的磁盘空间

4. 索引的使用

在创建主键约束(PRIMARY KEY), 唯一约束(UNIQUE), 外键约束(FOREIGN KEY)时, 会自动创建该列的索引

通过主键索引查询的效率要比其他列的索引查询效率更高, 主键索引中最终叶子结点上存的数据, 就是一条一条的记录, 当一个表已经包含主键时, 数据库默认就是通过 B+ 树的结构来组织所有的记录; 而其他列上的索引, 叶子结点上存的实际上是对应记录的主键 id.
如果通过其他列来查找记录, 实质上需要查两次: 现在其他某列的索引中找到对应记录的 id , 然后拿着 id 去主键索引中查找(回表).

4.1 查看索引

show index from 表名;

4.2 创建索引

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

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

4. 删除索引

drop index 索引名 on 表名;