MySQL 索引结构

谈到 MYSQL 索引服务端的同学应该是熟悉的不能再熟悉，新建表的时候怎么着都知道先来个主键索引，对于经常查询的列也会加个索引加快查询速度。那么 MYSQL 索引都有哪些类型呢？索引结构是什么样的呢？有了索引是如何检索数据的呢？我们围绕这些问题来探讨一下。

你认为应该如何查询数据

上一节谈到 InnoDB 引擎的时候聊过在 InnoDB 引擎是面向行存储的，数据都是存储在磁盘的数据页中，数据页里面按照固定的行格式存储着每一行数据。

InnoDB存储引擎是 B+ 树索引组织的，所以数据即索引，索引即数据。B+ 树的叶子节点存储的都是数据段的数据。InnoDB 引擎对数据的存储必须依赖于主键，主键对应的索引叫做聚集索引。如果不幸的是你建表没有建主键，InnoDB 会从表字段中寻找第一个非空的唯一索引作为聚集索引，如果还是不幸找不到，InnoDB 会生成一个不可见的名为 ROW_ID 的列，该列是一个 6 字节的自增数字，用来创建聚集索引。

小Tips:

对于 ROW_ID 列的自增实现其实是来自于一个全局自增序列，这意味着所有使用到 ROW_ID 作为聚集索引的表都共享该序列，如果在高并发的情况就有保证不了唯一性的可能。

大家都知道 MYSQL 中索引是使用 B+ 树的数据结构，在此也就不故弄玄虚。但是大家有没有想过除了 B+ 树还有什么数据结构也可以用于索引检索呢？我们不妨来看看。

二叉树

对于二叉树而言，每个节点只能有两个子节点，如果是一颗单边二叉树，查询某个节点的次数与节点所处的高度相同，时间复杂度为O(n)；如果是一颗平衡二叉树，查找效率高出一半，时间复杂度为O(Log2n)。

并且二叉树还有另一个坏处，二叉树上的每一个节点都是数据节点，那么对于一个比较高的数如果要获取最下面的数据遍历的节点数将会很消耗性能。

Hash 表

散列表的好处是散列查询单条数据比较快，但是坏处也比较多，比如 Hash 碰撞的解决，范围查找等等。

B 树

B树是二叉树的升级版，又叫平衡多路查找树。它和平衡二叉树的区别在于：

  1. 平衡二叉树最多两个子树，而 B 树每个节点都可以有多个子树，M 阶 B 树表示每个节点最多有M个子树。
  2. 平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ k 介于阶数 M 和 M/2 之间，M/2 向上取整）。
  3. 所有叶子节点均在同一层、叶子节点除了包含关键字和关键字记录的指针外也有指向其子节点的指针，只不过其指针地址都为 null 。

另外，它们相同的点是节点数据也是按照左小右大的顺序排列。我们用一张图来对比它们的区别：

1

B+ 树

说到 B 树就连着B+树一起说了。B+ 树是应文件系统所需而产生的一种 B 树的变形树（文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据）非叶子节点只保存索引，不保存实际的数据）。

4

这里有一个很重要的一点就是 B+ 树的非叶子节点不保存数据，只有索引。一棵 m 阶的 B+ 树和 m 阶的 B 树的异同点在于：

  1. 节点的子树数和关键字数相同（B 树是关键字数比子树数少一）；
  2. 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针（节点的关键字表示的是子树中的最大数，在子树中同样含有这个数据），且叶子结点本身依关键字的大小自小而大的顺序链接。 (而 B 树的叶子节点并没有包括全部需要查找的信息)；
  3. 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而 B 树的非终节点也包含需要查找的有效信息)。
  4. 叶子节点之间通过指针连接。

对于 B 树和 B+ 树来说，两种数据结构都是为了减少磁盘 I/O 读写过于频繁而生，本身节点的个数是有限的，采用多叉结构就是为了让每一层放尽可能多的节点以此来降低整棵树的高度。但是为什么 InnoDB 索引结构最终选择了 B+ 树而不是B 树呢？

B+ 树的磁盘读写代价更低

B+ 树内部非叶子节点本身并不存储数据，所以非叶子节点的存储代价相比 B 树就小的多。存储容量减少同时也缩小了占用盘块的数量，那么数据的聚集程度直接也影响了查询磁盘的次数。

B+ 树查询效率更加稳定

树高确定的前提下所有的数据都在叶子节点，那么无论怎么查询所有关键字查询的路径长度是固定的。

B+ 树对范围查询的支持更好

B+ 树所有数据都在叶子节点，非叶子节点都是索引，那么做范围查询的时候只需要扫描一遍叶子节点即可；而 B 树因为非叶子节点也保存数据，范围查询的时候要找到具体数据还需要进行一次中序遍历。

MyISAM 和 InnoDB 索引组织的区别

在 MYSQL 中索引属于存储引级别的概念，存储引擎不同，索引的实现方式也不一样。我们分别看看看 MyISAM 和 InnoDB 中都是如何实现索引功能。

MyISAM 实现

MyISAM 也是使用 B+ 树作为索引存储结构，他的叶子节点 data 域存放的是数据的物理地址，即索引结构和真正的数据结构其实是分开存储的。

2

InnoDB 索引实现

MyISAM 索引和数据是分离的，但是在 InnoDB 中却大不相同，InnoDB 中采用主键索引的方式，所有的数据都保存在主键索索引中。

3

所以这也是为什么 InnoDB 要求每个表都必须要有主键的原因。本身就是基于主键来组织的数据存储。

索引类型

以下所有索引类型都是基于 InnoDB 引擎。

主键索引

主键索引也就是我们说的聚集索引。上面说过主键索引是基于主键来创建的 B+ 树索引结构，如果没有指定主键，也找不到任何一列不重复的列可以作为主键的情况下，InnoDB 会新增一个隐藏列 RowId 作为主键继而创建聚集索引。

二级索引(非主键索引)

二级索引就是指除了主键索引外的索引。主键索引和所有的二级索引都是各自维护各自的 B+ 树结构，但是有个不同的地方在于，二级索引的叶子节点存储的不是数据，而是主键索引对应的主键值。

即二级索引不再保存一份 data 数据，而是去主键索引中查数据。那么对于二级索引查找一条数据索要做的操作就是：

  1. 首先在二级索引中找到叶子节点对应的数据主键值；
  2. 根据这个主键值去聚集索引中找到真正对应的数据行。

所以这里需要两次 B+ Tree 查找。

覆盖索引

覆盖索引简单来说就是只查询索引就能获取到数据不必再回表查询，换句话说要查询的列已经被索引列覆盖。

使用覆盖索引有如下优点：

  1. 索引项通常比记录要小，所以 MySQL 访问更少的数据；
  2. 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的 I/O；
  3. 大多数据引擎能更好的缓存索引。比如 MyISAM 只缓存索引;
  4. 覆盖索引对于 InnoDB 表尤其有用，因为 InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
  5. 覆盖索引不能是任何索引，只有 B Tree 索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引( Memory 和 Falcon 就不支持)。
联合索引

有的时候我们会对多个列建立一个索引，这种索引被称为联合索引。而关于联合索引的建立和使用，从工作开始你的各位 “师长” 都在教导你要遵循 “左前匹配原则”，那到底是为什么呢？什么是左前匹配原则呢？

比如我们有这样一张表：

CREATE TABLE `test_tb` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) NOT NULL,
  `b` varchar(10) NOT NULL,
  `c` varchar(10) NOT NULL,
  `d` int(10) NOT NULL,
  `e` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

上表建立了一个联合索引：idx_a_b_c。下面给出一个 SQL， 大家看它会不会走索引查询：

select  *  from test_tb where b = '10';

很显然根据 “左前匹配原则” 肯定不会走索引查询，最终还是全表扫描。

原因就在于联合索引的结构上。上面对 a,b,c 三个字段建立索引，那么对应的 B+ Tree 索引结构每个节点其实是按照三个字段的前后顺序排列的，即 a 字段检索在最前面，然后是b，然后是c。如果你的查询不是按照这个顺序来检索，是不会被这个索引识别的。

左前匹配原则

上面说到联合索引会遵循左前匹配原则，那么什么是左前匹配呢？

其实就是字面意义上的从建立索引的第一个字段开始先匹配查询条件，如果当前查询条件不是第一个字段那么就不会走该索引。

另外对于联合索引的使用也有一些限制，比如说：

遇到范围查询 ( > ,<, between, like) 就会停止匹配

比如哦我们看这个 SQL：

select * from test_tb where a = '1' and b = '3' and d < 20 and c = '5';

大家觉得这个 SQL 会如何使用索引呢？

其实这 SQL 在前面a，b的查询中是会走联合索引的，但是在经历了d的查询之后，到了c就不会使用索引了，因为d的查询已经将索引的顺序打乱了，从 d 条件过后就没有办法直接使用联合索引。

在索引列上做操作（函数，自定义计算）

同样对索引列做计算也是无法直接应用索引，不言自明，索引是对已有的数据进行归纳排序，你计算之后的数据是新的内容，索引并没有包含这些数据，无从查起。

查询条件包含 or，可能导致索引失效

比如有一张 user 表：

CREATE TABLE `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `userId` int(11) NOT NULL,  
  `age` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_userId` (`userId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下查询语句：

select * from user where userId=3 or age > 20;

大家觉得这句会走索引吗？

我们可以分析一下，如果 userId 查询走了索引这没问题，但是遇到 age > 20 这肯定没法走索引，那么前面 userId 走索引做一次 索引扫描就没有意义，所以优化器认为这种情况下不如一开始就走全表扫描更省事。

但是如果 or 前后的两个字段都加了索引那么可能会走，这种要依情况而定。

字符串类型的索引字段作为查询条件时一定要用引号括起来，否则索引不生效

上面我们没有说的一点是，B+Tree 索引结构的 key 都是用数字表示的，因为数字比较省空间，就算是字符串格式的字段，最终也会被转为二进制表示。但是对于不加引号的字符串，MSYQL 会自动做一次隐式转换将字符串转为浮点类型，这就导致不匹配。

like 通配符可能导致索引失效

使用 like 模糊查询并不是所有的都会失效，只有以 “%” 开头的 like 查询才会失效。

左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

这个比较不常见，一般来说同一个库中的表使用的编码格式应该是一样的，但是不排除老项目新老表有区别。

索引的缺点

上面一直在谈论索引的优点，凡事有利就有弊，它也不是没有缺点的：

  • 磁盘空间占用。这个对于当前磁盘比买菜还便宜的硬件大通货时代其实算不上问题，但是要注意的是如果当前 MySQL 服务所在的机器有很多的大表，并且还创建了每一种可能的组合的索引，那么索引文件提及的增长可能超乎你的想象。

  • 维护索引对更新类操作所带来的耗时。当对索引涉及到的列做更新或者新增操作时都会去维护相关的索引，这里也是一个耗时的点，所以索引不在多，而在精。

检查一条 SQL 是否是 bad SQL - 执行计划

在 MySQL 中如何知道一条 sql 到底有没有用到索引呢？MySQL 提供了 explain 关键字来查询一条 sql 的执行效率。

比如我们有一张 user 表：

CREATE TABLE `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `userId` int(11) NOT NULL,  
  `age` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_userId` (`userId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询下面 sql 的查询效率：

mysql> explain select * from user where id = 3;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | user  | const  | PRIMARY    | PRIMARY | 4    | const |     1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

执行计划各个字段的含义如下：

列名 含义
id 执行序号，MSYQL 会按照从大到小的顺序执行
select_type 查询类型：SIMPLE: 简单查询
PRIMARY: 外层查询
SUBQUERY: 子查询
DERIVED: 派生查询（FROM 中包含的子查询）
UNION: UNION 中第二个或后面的那个查询
UNION RESULT: UNION 的结果
table 引用的表
partitions 所属分区
type 访问类型官方文档，常见访问类型：
system : 只有一条记录的表（=系统表）
const : 通过索引一次就查询到
eq_ref : 唯一索引等值扫描
ref : 非唯一索引等值扫描
range : 范围索引扫描
index : 索引扫描
all : 全表扫描
possible_keys 可能使用的索引（优化前）
key 实际使用的索引（优化后）
key_len 使用索引的长度
ref 上述表的连接匹配条件（哪些列或常量被用于查找索引列上的值）
rows 必须扫描的行数
Extra 附加信息官方文档，常见附加信息：
Using filesort : mysql 无法利用索引完成排序操作
Using temporary : 使用了临时表保存中间结果
Using index : select 操作使用了覆盖索引
Using where : 使用 where 过滤
using join buffer : 使用了连接缓存
impossible where : where 子句的值总是 false，不能用来获取任何记录
distinct : 优化 distinct，在找到第一个匹配的记录后停止扫描同样值的动作

这么多字段我们挑几个重点来解释一下：

id

执行序号，id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按select 出现的顺序增长的。id 越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

key_len

key_len 长度表示在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。

ken_len 计算规则如下：

  • 字符串 ：char(n)：n 字节长度； varchar(n)：n 字节存储字符串长度，如果是 utf-8， 则长度是 3n+2，这里的长度与字符集有直接关系；
  • 数值类型：tinyint：1 字节；smallint：2 字节 ；int：4 字节； bigint：8字节；
  • 时间类型 ：date：3字节；timestamp：4字节；datetime：8字节。

如果字段允许为 NULL，需要 1 字节记录是否为 NULL； 索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引。

type

type 显示的是访问类型，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

下面对这几个类型简要说明：

  • system
    该表只有一行（如：系统表）。这是 const 连接类型的特例。

  • const
    该表最多只有一个匹配行，在整个查询过程中这个表最多只会有一条匹配的行，用到了 primary key 或者unique 索引。

    比如主键查询肯定只有一条记录被匹配到。

  • eq_ref
    对于前面表格中的每个行组合，从该表中读取一行。除了 system 和 const 类型之外，这是最好的连接类型。当连接使用索引的所有部分且索引是 索引 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用它。

    SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

  • ref
    表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

  • fulltext
    使用 FULLTEXT 索引执行连接。

  • ref_or_null

    该连接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。

    SELECT * FROM ref_table WHERE key_column IS NULL;

  • index_merge
    该连接类型表示使用了索引合并优化方法。

    SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

  • unique_subquery
    此类型替换 以下形式的 eq_ref 某些 IN子查询：

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery
    此连接类型类似于 unique_subquery。它替换 IN 子查询，但它适用于以下形式的子查询中的非唯一索引：

    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range
    给定范围内的检索，使用一个索引来检查行。通常发生在在索引列上使用范围查询，如 >，<，in 等时，非索引列是 ALL。

  • index
    按索引次序扫描，先读索引，再读实际的行，结果也是全表扫描，主要优点是避免了排序。（索引是排好序的，并且 all 是从硬盘中读的，index 可能不在硬盘上。s

  • ALL
    对前面表格中的每个行组合进行全表扫描。如果表是第一个未标记的表 const，通常不好，并且在所有其他情况下通常 非常糟糕。通常，您可以ALL通过添加基于常量值或早期表中的列值从表中启用行检索的索引来避免

row

这一列是 MYSQL 估计要读取并检测的行数，注意这个不是结果集的行数。

Extra

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示 MySQL 在查询过程中的一些详细信息。

  • Distinct：MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。
  • Not exists：MySQL 能够对查询进行 LEFT JOIN 优化，发现1个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record：MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知。可能部分索引可以使用。
  • Using filesort：看到这个的时候，查询就需要优化了。MySQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
  • Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary：为了解决查询，MySQL 需要创建一个临时表来容纳结果。看到这个就需要进行优化了，这通常发生在对不同的列集进行 order by 上，而不是 group by 上。
  • Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union| Using union|Using intersect：这些函数说明如何为 index_merge 联接类型合并索引扫描。
  • Using index for group-by：类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。

了解了执行计划，当你不确定一条 sql 查询效率的时候 就可以使用 Explain 来查看。

posted @ 2020-08-25 14:26  rickiyang  阅读(10702)  评论(0)  编辑  收藏  举报