Mysql单表访问方法

在使用Mysql时我们平时使用最多的就是查询，通常查询SQL语句有性能问题时我们会通过EXPLAIN去分析SQL语句，例如下面的分析结果：

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_student | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

在分析结果中有很多栏，对于其他栏这里暂不介绍，而我们要关注的访问方法就是type这一栏。

Mysql执行查询语句的方法叫做访问方法，同一语句使用不同的访问方法执行，查询结果都是一样的，但是不同的查询方法的效率差距很大。优化器会选择成本最低的访问方法，所以理解访问方法有助于我们更好的使用索引。

注意：访问方法只是可能会使用该方式执行，但是也可能不使用该方式执行。这个跟表中数据量的大小有关，如果数据量太小，很可能直接全盘扫描更快，那么测试的示例其访问方法可能跟预想中不符。

const

通过主键或唯一二级索引与常数进行等值比较来定位一条记录，这种方法称为const。如果唯一索引是由多个列组成的，那么所有列都要进行等值比较。其中唯一二级索引不限制null的个数，所以即使是唯一二级索引IS NLL也不会使用const访问。

-- 主键访问
SELECT * FROM t_user WHERE id = 1;
-- 唯一索引访问
SELECT * FROM t_user WHERE user_name = '小明';
-- 多列组成唯一索引访问
SELECT * FROM t_user WHERE first_name = '小' AND last_name = '明';

上面三个访问方法其访问方法都是const，但是下面两种则不会是const

-- 唯一索引 IS NULL
SELECT * FROM t_user WHERE user_name IS NULL;
-- 多列组成唯一索引，只有部分列是等值访问
SELECT * FROM t_user WHERE first_name = '小';

ref

在使用二级索引与常数列进行等值比较时，它首先会通过二级索引找到对应的记录，然后回表找到记录的完整信息。由于二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录。对于这种搜索二级索引列与常数进行等值比较，采用二级索引来执行查询的方法为ref。但是这里有两个特殊的点需要注意一下：

• 对二级索引列进行IS NULL查询。二级索引允许存储NULL值，而且数量也没有限制，但是为NULL的值放在B+树的最左侧。所以查询时只需要从第一个为NULL的值开始往右匹配直到不满足则结束。所以二级索引IS NULL访问方法也是ref。

• 多列组成的二级索引只要最左边的连续索引列是与常数进行等值比较，也可能采用ref。例如A、B、C三列组成的二级索引，当条件为A=1或者A=1 and B=1时其访问方法也可能是ref。但是如果是B = 1、C = 1,A = 1 and C = 1时则不会是ref。

-- 二级索引（非唯一索引）与常数等值比较
SELECT * FROM t_user WHERE age = 18;
-- 二级索引（包括唯一索引）IS NULL
EXPLAIN SELECT * FROM t_user WHERE user_name IS NULL;
-- 二级索引（包括唯一索引）左边连续列与常数比较
EXPLAIN SELECT * FROM t_user WHERE first_name = '小';

ref_or_null

有时候我们不仅想找出二级索引列的值等于某个常数的记录，还想找出值为NULL的记录也找出来，例如下面的SQL语句：

SELECT * FROM t_user WHERE age = 18 OR age IS NULL;

range

前面介绍的基本上都是索引列与某个常数进行等值比较，但是有时候我们的搜索条件会更加复杂，例如有时候查询的是一个区间：

SELECT * FROM t_user WHERE age > 18;
SELECT * FROM t_user WHERE age IN (18,19,20);

对于像查询范围这类的查询，其扫描的是若干个单点区间或者多个区间的查询，那么可以使用range访问方法。

index

Mysql中二级索引只需要存储索引列和主键，而聚簇索引需要存储所有的列和主键，所以二级索引的大小远远小于聚簇索引，当一个查询不需要进行回表，那么将直接利用二级索引进行全表扫描这种访问方法叫做index。

SELECT first_name FROM t_user;

例如上面的查询，因为没有查询条件所以无法使用索引，但是其返回值只有first_name字段。对于Mysql而言，它有两种选择可以执行查询：

• 通过聚簇索引扫描所有的数据从数据中返回指定字段。

• 通过二级索引扫描所有数据从二级索引中返回指定字段。

但是Mysql会选择使用第二种方式，即扫描二级索引。因为扫描二级索引的成本会远远小于聚簇索引。

all

该方式就是我们所说的全表扫描。如果说查询无法使用任何索引，或者使用二级索引加回表的方式比全表扫描还高，此时Mysql会使用all的方式进行访问。

索引合并

通过上面的内容我们了解了访问方法的概念。在实际开发中我们的查询条件中可能会存在多个条件，可能多个条件字段都有索引，那么Mysql该如何处理呢？

通常情况下Mysql只会选择使用一个索引，但是部分特殊情况下，Mysql可能会用到多个索引。如果在一个查询中使用了多个索引来完成一次查询，那么我们称该执行方法为index merge，也就是我们所说的索引合并。

交集索引合并

例如有下面的查询，其中a和b分别为普通二级索引。

select * from t where a = 1 and b = 1;

如果它们进行交集索引合并的话，那么整个过程大概如下：

• 从a二级索引对应的B+树中取出a=1的相关记录。

• 从b二级索引对应的B+树中取出b=1的相关记录。

• 二级索引记录包含了索引列和主键，所以我们计算出这两个结果集中主键的交集。

• 根据上面一步进行回表操作，也就是从聚簇索引中把指定ID值的完整数据返回给用户。

虽然交集索引合并性能很好，但是它存在一个限制，它要求二级索引查找的主键本身就是有序的。对于该限制而言，则要求必须满足以下的情况：

• 二级索引列不是等值匹配的情况，对于多个列组成的联合索引，每个列必须是等值匹配，不能出现只匹配部分列的情况。例如下面的查询就不能使用交集索引合并：

select * from t where a > 1 and b = 1;

• 主键列可以是范围匹配。例如下面的SQL支持索引合并：

select * from t where a = 1 and id > 5;

看到这里你肯定很疑惑为什么有如此怪的限制。

二级索引带出的主键本身有序

前面在讲进行交集索引合并的第三步有说过，需要从二级索引中获取到主键然后计算交集。

例如现在有两个有序的集合1,3,4和2,3,6进行取交集，那么过程如下：

• 先取从两个集合中取最小的值，因为1<2，所以1丢弃，然后取第一个集合中的3。

• 因为3>2，所以把2丢弃，再从第二个集合中取3。

• 因为3=3，所以把3加入到最后的交集中，继续按照前面的方式进行直到所有数据取完。

• 后面所有的元素都不相等，所以最后就只有3才是交集元素。

而上面的整个过程其时间复杂度为O(n)，其速度还是比较快的。如果上面两个集合中的元素不是有序的，那么就需要先排序然后再使用刚刚的方式来进行取交集。这也就是为什么该方式需要主键有序了。

二级索引列不是等值匹配

二级索引列不是等值匹配也无法使用交集索引合并，这个很好解释，其主要原因还是因为其主键不是有序的。例如有如下SQL：

select * from t where a > 1 and b = 1;

假设二级索引a的结构如下，可以很明显的看见，如果a为等值情况时其主键是有序的。但是如果a是多值的情况下，那么其主键就不是有序的了。

主键列可以是范围匹配

如果明白了前面的点，这个点就很好理解了。聚簇索引本身就是主键有序的，即使是主键在一个范围内，它还是有序的。

并集索引合并

前面的交集索引合并适用于条件之间是AND的关系，如果关系为OR时，则可以使用并集索引合并。其与交集索引合并比较类似，不同的在于其最后是对主键求并集。

排序并集索引合并

并集索引合并使用条件太苛刻，必须保证各个二级索引列进行等值匹配条件才可能被利用，例如下面的查询：

select * from t where a > 1 OR b < 1;

此时可以使用排序并集索引合并。它与并集索引合并不同的地方在于，根据条件从二级索引中获取到记录后，增加了按照记录中的ID进行排序这么一步，这样就和并集索引合并没有区别了。

虽然存在排序并集索引合并，但是其只适合二级索引获取的记录较少，这样对二级索引的主键进行排序的成本也不会太高。

使用联合索引替代索引合并

索引合并并非一定会出现，只是说可能会出现索引合并提高查询的情况。但是如果我们业务中有这样的情况，我们应该优先考虑创建联合索引。这样就不用考虑它会不会进行索引合并，而是直接使用联合索引。