目录
一、索引的分类
InnoDB 中的索引
- 按功能: 普通索引、唯一索引、主键索引、全文索引、空间索引等
- 按物理实现方式: 聚簇索引、非聚簇索引
- 按照作用字段个数: 单列索引、联合索引
- 8.0新特性索引: (1)隐藏索引,从 MySQL 8.x 开始支持,要删索引之前先把它设为隐藏索引,优化器将忽略该索引,如果对系统没影响再真的删除;(2)降序索引,即可以按降序排列的索引
聚簇索引(主键索引)
非聚簇索引(二级索引、辅助索引)
- 二级索引只放查找值和主键,因为这样让一页可以放更多行,使得B+树更扁、更矮。
- 事实上,二级索引的非叶节点中也放了主键,相当于是索引字段和主键的联合索引,因为索引字段可能会重复(即使是 UNIQUE 的字段也可能重复,比如多个 NULL 或 MVCC 产生的重复)。
- 覆盖索引可以让二级索引避免回表,提升效率。
联合索引
将几个字段放一起构建的二级索引即为联合索引,这些字段从左往右依次比较,所以有最左前缀原则。
MyISAM 中的索引
- MyISAM 中的索引与 InnoDB 中的索引的主要区别在于,MyISAM 中的索引的叶节点存储的是主键(或索引字段)+ 地址,要先找到地址,再通过地址去取数据,所以 MyISAM 中的索引必需要“回表”一次,相当于都是二级索引。
- 在 MyISAM 中,当用变长格式存数据时索引中存的是地址偏移量,当用定长格式存数据时索引中存的是行号。
- 虽然 MyISAM 中的索引必须“回表”,但是实际上也是很快的,因为是直接根据地址去取,而不是像 InnoDB 中那样再走一遍索引。
二、索引的细节知识
B+树的细节
- B+ 树一般不超过4层,因为三层1亿条数据,四层1000亿条数据;同时根节点常驻内存,因此磁盘 I/O 一般不超过 3 次。
- 主键一般为递增的 ID,因为 B+ 树顺序插入快,向中间插入的话会导致页的分裂,会很慢。
- 一般把主键定为不可更新,否则 B+ 树的重建代价很高。
- B+ 树页面分裂时,新建一个页,把该页数据分一半放入新页,然后把中间值存入父节点;若父节点也满了,则父节点按相同步骤分裂(所以根节点位置不会动,也所以 B+ 树每个页面至少有 2 个数据)。
为什么索引使用B+树,而不是Hash、二叉树、红黑树、B-Tree?
- Hash 索引: 只适合等值查询,不适合范围查询(因为无序,范围查询还是一个一个找);但是 InnoDB 和 MyISAM 虽然没有Hash索引,却有自适应Hash索引,把多次查询的数据放入Hash索引中,提高检索效率。
- 二叉树: 太高了,磁盘 I/O 太多(甚至可能退化成链表,直接变成 O ( n ) O(n) O(n) )。
- 红黑树: 是一种特化的平衡二叉树,还是太高了,磁盘 I/O 太多;MySQL 数据量很大的时候,索引的体积也会很大。
- B-Tree: B-Tree的叶节点和非叶节点都保存数据,这导致(1)B-树比B+树更高,磁盘 I/O 更多;(2)B-树的查询所需的 I/O 次数不稳定,在叶节点就多,在非叶节点就少,而B+树则是稳定的 O ( l o g n ) O(logn) O(logn) ;(3)B+树的叶节点是双向链表,B-树不是,所以B+树范围查询更快。
三、索引与优化
索引覆盖
二级索引(非聚簇索引)中的索引字段 + 主键覆盖了SELECT
、JOIN
和WHERE
中要用到的所有字段,就不用回表了,高效率。
索引下推
MySQL 5.6 的新特性,当使用联合索引(字段A和字段B)时,找到符合 A 条件的数据先不急着回表,而是用字段 B 的条件先把找到的数据过滤一遍,这样可以有效减少要回表的数据量。
下图为索引下推的举例
索引合并
索引合并就是一次查询中同时使用两个索引来执行。
索引合并的方法分为以下三种:
- 交集(Intersection): 同时在两个索引中进行搜索,将两个索引搜索到的记录的主键进行比较。若相同则进行回表,然后两个索引继续搜索;若不相同则丢弃主键较小的那个(因为二级索引中索引字段相同的数据是按照主键值排序的),并让该索引继续搜索下一个,另一个索引则等待。不断循环上述过程,直到两个索引都完成遍历,进行回表并返回给客户端的那些记录即是要找的全部数据。例如查询
SELECT * FROM single_table WHERE key1 = 'a' AND key2 = 'b'
。 - 并集(Union): 同时在两个索引中进行搜索,将两个索引搜索到的记录的主键进行比较。若相同则进行回表(只回表了一次,相当于去重),然后两个索引继续搜索;若不相同则主键较小的那个进行回表(因为二级索引中索引字段相同的数据是按照主键值排序的),并让该索引继续搜索下一个,另一个索引则等待。不断循环上述过程,直到两个索引都完成遍历,进行回表并返回给客户端的那些记录即是要找的全部数据。例如查询
SELECT * FROM single_table WHERE key1 = 'a' OR key2 = 'b'
。 - 排序-并集(Sort-Union): 使用上面两个索引合并方法的前提是“二级索引中索引字段相同的数据是按照主键值排序的”,这有时是个过于苛刻的条件,在很多情况下都无法满足。因此可以先让两个索引各自进行遍历搜索,并各自将结果进行排序,然后再用 Union 方法将结果进行合并。例如查询
SELECT * FROM single_table WHERE key1 < 'a' OR key2 > 'z'
。
适合使用索引的情况
1-5是根据字段用途,6-8是根据字段特点,9-11是其他注意事项
- 经常作为
WHERE
查询条件的字段。 - 经常
GROUP BY
和ORDER BY
的字段(索引已经排序了,进行GROUP BY
和ORDER BY
就很简单了)。 DISTINCT
的字段。- 用作
UPDATE
和DELETE
的WHERE
条件的字段。 - 多表
JOIN
连接基于的字段。
– - 有唯一性限制的字段。
- 区分度高(散列性高)的字段。
- 类型小的字段。
– - 使用字符串字段的前缀创建索引(但是用了前缀索引就不能索引覆盖了,而且也不能用于排序了)。
- 联合索引中使用最频繁的字段放左侧(最左前缀原则)。
- 在多个字段都要创建索引的情况下,联合索引优于单值索引。
不适合使用索引的情况
1-3是根据字段特点,4-5是根据表的特点,6-7是其他注意事项
- 在
where
中使用不到的字段 - 有大量重复数据的字段
- 无序的字段
– - 数据量小的表最好不要用索引
- 避免对经常更新的表创建过多的索引
– - 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
索引失效的情况
- 使用
!=
或<>
导致索引失效 - 类型不一致导致索引失效
- 使用函数导致索引失效
- 自动类型转化导致索引失效(将
WHERE name = '123';
写成了WHERE name = 123;
) - 查询的范围条件放前面导致索引失效(如
WHERE student.age = 30 AND student.class_id > 20 AND student.name = 'abc';
,范围条件要放最后) LIKE
以通配符%
开头导致索引失效OR
的条件中存在没有索引的字段(如WHERE age = 10 OR class_id = 100;
且class_id
没有索引)
关联查询优化
- 外连接中的被驱动表要有索引
- 用于
JOIN
的字段,数据类型要保持绝对一致,否则会导致索引失效情况 2 2 2。 - 外连接
JOIN
时,选择小表为驱动表,大表为被驱动表,以减少外层循环的次数。 - 内连接
INNER JOIN
时,MySQL 会先为每个表计算成本最低的访问方法,再计算各种连接顺序的成本,选择成本最小的方式进行连接。 - 能直接多表关联的尽量直接关联,尽量不用子查询,减少查询的趟数(子查询生成的临时表建不了索引)。
- 尽量不要使用
NOT IN
或者NOT EXISTS
,用LEFT JOIN xxx ON xx WHERE xx IS NULL
替代。
大表优化
- 读/写分离,一主一从、一主多从或双主双从等,主写从读
- 垂直拆分(分布式系统)
- 水平拆分