MySQL之索引
索引的本质
索引(Index)是排好序的,能够快速查找数据的数据结构,能够帮助 MySQL 高效获取数据。
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
下图是一种可能的索引方式:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。
为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录的地址指针,这样就可以运用 二叉查找在一定的复杂度内获取到相应数据,从而快速检索出符合条件的记录。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
索引如果没有特别指明,都是指B-Tree(多路搜索树,并不一定是二叉树)结构组织的索引。
其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引。
除了B+树索引,还有哈希索引。
索引的优势和劣势
优势
- 提高数据检索的效率,降低磁盘IO成本
- 数据排序,降低CPU消耗
劣势
- 索引本质也是一张表,保存着索引字段和指向实际记录的指针,所以也要占用数据库空间,一般而言,索引表占用的空间是数据表的1.5倍
- 索引虽然能提高查询速度,但是会降低表的更新速度,因为更新数据时,也要更新索引
MySQL的索引
Btree 索引
MySQL 使用的是 Btree 索引。
【初始化介绍】
浅蓝色的块称之为磁盘块,由数据项(深蓝色所示)和指针(黄色 所示)组成。
磁盘块 1 包含数据项 17 和 35,包含指针 P1、P2、P3, P1 表示小于 17 的磁盘块,P2 表示在 17 和 35 之间的磁盘块,P3 表示大于 35 的磁盘块。
真实数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点不存储真实数据,只存储指引搜索方向的数据项,如 17、35 并不真实存在于数据表中。
【查找过程】
如果要查找数据项 29,那么首先把磁盘块 1 由磁盘加载到内存,此时发生一次 IO。
在内存中用二分查找确定 29 在 17 和 35 之间,锁定磁盘块 1 的 P2 指针,内存时间因为非常短(相比磁盘IO)可以忽略不计。通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存,发生第二次 IO。
29 在 26 和 30 之间,锁定