MySQL 索引

索引是对数据库表中一列或多列的值进行排序的一种结构。通俗点来讲，索引就像书的目录，可以帮助我们尽快找到想看的内容。对应到数据库，索引实际上就是为了提高查询效率。

索引的类型

• 普通索引：没限制，可重复，可为空
• 主键索引：索引列值唯一，不允许为空
• 唯一索引：索引值唯一，允许为空
• 全文索引：建立在文本类型（CHAR，VARCHAR，TEXT）上的索引，字符串较大时，普通索引查询效率低，建议使用全文索引
• 空间索引（了解）：按照空间关键建立的索引
• 前缀索引（了解）：建立在文本类型上指定长度的索引

按照索引使用到的列数又可以分为单列索引和组合索引：

• 单列索引：建立在某一个列上的索引
• 组合索引：多个列组合建立一个索引

无论哪种类型的索引，默认使用 B+树 结构来存储索引结构。

---

最左匹配原则

使用组合索引时，查询满足最左匹配原则，其中该原则具备以下特性：

1. 查询 sql 根据索引顺序依次匹配，直到遇到范围查询（>、<、like、between），范围查询列也会用到索引，它右边的列不会用用到
2. 查询 sql 只用到 ‘=’ 和 ‘in’ 操作时，任意顺序的判断条件都不影响索引使用（不按照建立索引顺序）
3. 查询 sql 没有用到左边列时，右边列不能用到索引

建立 (a,b,c,d) 索引

• where a = 1 and b = 2 and c > 3 and d =4 ，d 列就不会用到索引，因为左侧 c 列用到了范围查询
• where a = 1 and b = 2 和 where b = 2 and a =1 效果相同，都会用到索引，因为只有 ‘=’ 和 ‘in’ 操作
• where b = 2 不会用到索引，因为左侧 a 列没有加判断

具体原因是使用聚合索引时，b 列索引会在 a 列索引的基础上建立，而 c 列索引又会在 b 列索引基础上建立，结果是只有 a 列索引在全局是满足索引顺序的，b列 索引在每个 a 列索引块上满足索引顺序，但全局层面不满足索引顺序，只使用 b列 进行索引查询会出现问题。

---

索引的使用

MySQL 中通过以下 sql 建立索引：

// 直接创建索引
CREATE INDEX 索引名称 ON 表名称(列名称,列名称2...)
// 修改表添加索引
ALTER TABLE 表名称 ADD INDEX 索引名称(列名称1,列名称2...);
// 创建表的同时创建索引
CREATE TABLE 表名称 (
	'id' int(11) NOT NULL AUTO_INCREMENT ,
	'title' varchar(32) NOT NULL ,
	PRIMARY KEY('id'),
	INDEX 索引名称(对应列)
);
// 删除索引
DROP INDEX 索引名称 ON 表名称;
ALTER TABLE 表名称 drop index 索引名称;

只有一列就是单列索引，多个列组合就是聚合索引。通过以下 sql 判断查询操作是否用到索引：

EXPLAIN 查询语句;

观察 type 字段，ALL 表示没有用到索引，const 表示唯一索引

---

索引的优劣势

优势：提高查询效率，降低排序成本

劣势：建立索引需要占据磁盘空间、每次增删改都需要刷新索引，降低除查询外所有操作的效率

---

索引的结构

MySQL 数据保存在磁盘（常用存储引擎）中，查询数据时需要将数据加载到内存，磁盘 IO 效率很低，优化索引的重点在于减少 IO

• Hash 表：Key - Value 形式，key 保存索引，value 存储数据或数据地址。等值查询时效率高，范围查询时效率很低

• 二叉查找树：左边节点值小，右边节点值大。节点值保存索引，每个节点内部存储数据或数据地址。正常情况下查询复杂度 log2n，极端情况下可能出现只有右子树或左子树的情况，此时查询效率很低。

• 平衡二叉树：在二叉树的基础上保证左右子树高度相差不超过1，缺点是每个节点只有保存一行数据，数据非常多时二叉树很高，此时查询效率会很差，而且范围查询不方便

• B树：在平衡二叉树的基础上优化，每个节点存储多个元素、元素包含键值和数据，键值从小到大排序：
  
  每次磁盘 IO 默认读取一页数据（16k），假设每个二叉树节点 16 字节，B树就可以把 1000+（16 * 1024 / 16）个节点放在一起，默认每次读取一个 B树 节点的数据量，相比平衡二叉树，B树 大大减少了磁盘 IO 次数， 但 B树 在范围查询效率仍然不高（跨磁盘块）

• B+树：在 B树 的基础上再优化，非叶子节点不再存储数据，所有索引键值存储在叶子节点上，叶子节点之间通过指针互相连接：
  
  相比 B树，B+树 理论上更矮一点（非叶子节点可以保存更多索引），查询效率也就更高。并且 B+ 树在范围查询时，直接根据指针遍历即可，效率更高。

---

MySQL 索引

• MyISAM 默认使用 B+树 建立索引，叶子节点中键值保存索引，数据为索引所在行的地址。在 MyISAM 中普通索引和主键索引在结构上没有区别，只是普通索引可以为 NULL 和重复，主键索引不行

• InnoDB 默认也使用 B+树 建立索引，其中 InnoDB 表都有一个聚簇索引，聚簇索引等同于主键索引，表没有主键索引时默认创建一个 ROWID 字段建立聚簇索引。

InnoDB 中除聚簇索引外其它索引都叫辅助索引，聚簇索引对应 B+ 树叶子节点保存具体索引行数据，辅助索引 B+ 树叶子节点保存聚簇索引建立用到的唯一 ID，也就是说用到辅助索引时，首先查到唯一 ID，使用该 ID 再去聚簇索引查询具体行数据，这种再查的方式又叫回表。

覆盖索引：覆盖索引是避免回表的一种优化，有时我们只查询辅助索引列值时，查到就可以直接返回，不用回表。使用组合索引在有些场景下可以提升查询效率，避免回表。