MySQL 的单表查询

Posted on In

从最简单的单表查询开始，能够更加简单直接地接触到 MySQL 中最基础的一些访问方法，结合 MySQL 的行记录、数据页以及索引结构的知识，在出现性能问题时才可以更有底气地应对。

在开始之前，首先我们创建一张表，其中 id 列创建聚簇索引，username 和 email 列创建二级索引，code 列创建唯一的二级索引，而 province、city 和 county 这三列创建一个联合索引。接着通过程序插入随机数据 100 万条。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table user
(
    id         int auto_increment primary key,
    username   varchar(100) null,
    code       int          null,
    email      varchar(100) null,
    avatar     varchar(100) null,
    province   varchar(100) null,
    city       varchar(100) null,
    county     varchar(100) null,
    key idx_username (username),
    unique key idx_code (code),
    key idx_email (email),
    key idx_address (province, city, county)
) ENGINE = InnoDB CHARSET = utf8;

访问方法（access method）

对于单表来说，MySQL 查询能够执行的方式大致有两种，一种是使用全表扫描，即扫描表的每一行记录，把符合条件的记录加入到结果集中。另一种就是使用索引查询，但是针对索引的查询也细分为很多种，比如针对主键或者唯一二级索引的等值查询，普通二级索引的等值查询，索引列的范围查询等等，这些都是 MySQL 执行查询时的访问方法。

const

通过主键或者唯一二级索引进行等值查询时使用的就是 const，意为常数级别的访问方法，它的代价几乎可以忽略。比如：

1
2
3
4
5
6
7
explain select * from user where id = 12286;

+--+-----------+------+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table |partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
+--+-----------+------+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE     |user  |NULL      |const|PRIMARY      |PRIMARY|4      |const|1   |100     |NULL |
+--+-----------+------+----------+-----+-------------+-------+-------+-----+----+--------+-----+
1
2
3
4
5
6
7
explain select * from user where code = 12286;

+--+-----------+------+----------+-----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table |partitions|type |possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+------+----------+-----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |user  |NULL      |const|idx_code     |idx_code|5      |const|1   |100     |NULL |
+--+-----------+------+----------+-----+-------------+--------+-------+-----+----+--------+-----+

与主键不同的是，使用唯一二级索引作为查询条件时，如果没有覆盖索引，那么就需要回表。同时由于唯一二级索引并不限制 NULL（没有填充数据）的数量，所以在进行判空查询时无法使用 const，比如：

1
2
3
4
5
6
7
explain select * from user where code is null;

+--+-----------+------+----------+----+-------------+--------+-------+-----+----+--------+---------------------+
|id|select_type|table |partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra                |
+--+-----------+------+----------+----+-------------+--------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |user  |NULL      |ref |idx_code     |idx_code|5      |const|4   |100     |Using index condition|
+--+-----------+------+----------+----+-------------+--------+-------+-----+----+--------+---------------------+

ref

通过二级索引进行等值查询时使用的就是 ref，由于索引列的值相同的记录基本上是连续的，因此这种访问方式也是比较快的。但是二级索引的等值查询不是一定使用 ref 的访问方式，这取决于查询条件所匹配到的记录条数，如果匹配到的记录较少，那么回表的代价还是比较低的，此时可以使用 ref 的方式；如果匹配到的记录较多，那么 MySQL 可能会选择使用全表扫描的方式执行查询。

1
2
3
4
5
6
7
explain select * from user where username = 'Bob';

+--+-----------+------+----------+----+-------------+------------+-------+-----+----+--------+-----+
|id|select_type|table |partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
+--+-----------+------+----------+----+-------------+------------+-------+-----+----+--------+-----+
|1 |SIMPLE     |user  |NULL      |ref |idx_username |idx_username|303    |const|1724|100     |NULL |
+--+-----------+------+----------+----+-------------+------------+-------+-----+----+--------+-----+

对于包含多个索引列的二级索引（也叫联合索引）来说，只要符合最左匹配原则就可能采用 ref 的访问方式。比如：

1
2
3
4
5
6
7
explain select * from user where province = '山东';

+--+-----------+------+----------+----+-------------+-----------+-------+-----+----+--------+-----+
|id|select_type|table |partitions|type|possible_keys|key        |key_len|ref  |rows|filtered|Extra|
+--+-----------+------+----------+----+-------------+-----------+-------+-----+----+--------+-----+
|1 |SIMPLE     |user  |NULL      |ref |idx_address  |idx_address|303    |const|1   |100     |NULL |
+--+-----------+------+----------+----+-------------+-----------+-------+-----+----+--------+-----+
1
2
3
4
5
6
7
explain select * from user where province = '山东省' and city = '济南市';

+--+-----------+------+----------+----+-------------+-----------+-------+-----------+----+--------+-----+
|id|select_type|table |partitions|type|possible_keys|key        |key_len|ref        |rows|filtered|Extra|
+--+-----------+------+----------+----+-------------+-----------+-------+-----------+----+--------+-----+
|1 |SIMPLE     |user  |NULL      |ref |idx_address  |idx_address|606    |const,const|1   |100     |NULL |
+--+-----------+------+----------+----+-------------+-----------+-------+-----------+----+--------+-----+
1
2
3
4
5
6
7
explain select * from user where city = '济南市';

+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |user  |NULL      |ALL |NULL         |NULL|NULL   |NULL|989489|10      |Using where|
+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列值为 NULL 的记录也找出来，此时使用的就是 ref_or_null 的访问方法，比如：

1
2
3
4
5
6
7
explain select * from user where username = 'Bob' or username is null;

+--+-----------+------+----------+-----------+-------------+------------+-------+-----+----+--------+---------------------+
|id|select_type|table |partitions|type       |possible_keys|key         |key_len|ref  |rows|filtered|Extra                |
+--+-----------+------+----------+-----------+-------------+------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |user  |NULL      |ref_or_null|idx_username |idx_username|303    |const|1725|100     |Using index condition|
+--+-----------+------+----------+-----------+-------------+------------+-------+-----+----+--------+---------------------+

上面的查询相当于分别从索引树中找出 username is null 和 username = ‘Bob’ 的两个连续的记录范围，然后根据记录中的主键值回表查询完整的用户记录。

range

如果使用索引列进行范围查询，一般会使用 range 访问方法。比如：

1
2
3
4
5
6
7
explain select * from user where username in ('Bob', 'Alice');

+--+-----------+------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+
|id|select_type|table |partitions|type |possible_keys|key         |key_len|ref |rows|filtered|Extra                |
+--+-----------+------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |user  |NULL      |range|idx_username |idx_username|303    |NULL|3425|100     |Using index condition|
+--+-----------+------+----------+-----+-------------+------------+-------+----+----+--------+---------------------+
1
2
3
4
5
6
7
explain select * from user where code >= 122 and code <= 300;

+--+-----------+------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table |partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |user  |NULL      |range|idx_code     |idx_code|5      |NULL|179 |100     |Using index condition|
+--+-----------+------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

从上面的例子看，范围一般有单点区间和连续区间，单点区间只要多次使用索引的等值查询即可，而连续区间则只要找到区间的起始点，之后沿着该点向后顺序查询即可，速度也是比较快的。

总的来说，只要索引列和常数使用比较符号（等于、不等于、大于等于等等），in 和 not in，is null 和 is not null，以及 between 和 like 连接起来就可以产生一个区间，就可以使用 range 访问方法，但是 like 比较特殊，只有符合最左前缀匹配原则时才可以。

index_merge

有些特殊的查询会在一个查询中使用到多个索引，这种查询使用的访问方法就是 index_merge，即索引合并。索引合并的算法一般有三种：Intersection、Union、Sort-Union。

Intersection

所谓交集合并就是将从多个索引中查询到的结果取交集，比如下面这个：

1
2
3
4
5
6
7
explain select * from user where username = 'Bob' and email = 'orghxctxih@cxmxz.gmz';

+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+----------------------------------------------------+
|id|select_type|table |partitions|type       |possible_keys         |key                   |key_len|ref |rows|filtered|Extra                                               |
+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+----------------------------------------------------+
|1 |SIMPLE     |user  |NULL      |index_merge|idx_username,idx_email|idx_email,idx_username|303,303|NULL|2   |100     |Using intersect(idx_email,idx_username); Using where|
+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+----------------------------------------------------+

整个过程就是分别将根据索引列查询得出的结果，然后根据结果中的主键值取交集，最后回表查询。这里我们可能会疑惑为什么不先拿到一个根据索引列查询的结果，然后在它的基础上进行回表查询并过滤第二个条件呢？这其实就要比较两种方式的成本。我们知道，读取二级索引是顺序 I/O，而回表有很大可能是随机 I/O，虽然使用交集合并的方式需要读取多个二级索引，但是多个索引合并后，交集的记录数一定要比只读取一个索引的记录数要少，需要回表的记录数也少，所以成本更低。

一个查询使用多个索引列，有时候可能并不会像我们预料的那样使用索引合并，这取决于索引列能够匹配到的记录个数，如果记录数比较少，可能只是单纯的使用 ref 访问方式。

一般只有在多个二级索引进行等值匹配（联合索引需要每个列都进行匹配，不能只匹配部分列）时才会使用 Intersection 索引合并，但是有些特殊的情况也可以使用，比如在主键列使用范围匹配的时候：

1
2
3
4
5
6
7
explain select * from user where id > 12205 and username = 'Bob';

+--+-----------+------+----------+-----------+--------------------+--------------------+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table |partitions|type       |possible_keys       |key                 |key_len|ref |rows|filtered|Extra                                             |
+--+-----------+------+----------+-----------+--------------------+--------------------+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |user  |NULL      |index_merge|PRIMARY,idx_username|idx_username,PRIMARY|307,4  |NULL|852 |100     |Using intersect(idx_username,PRIMARY); Using where|
+--+-----------+------+----------+-----------+--------------------+--------------------+-------+----+----+--------+--------------------------------------------------+

对于这种特殊情况，我们只要记住一点，即：二级索引的索引列相同的记录是按照主键排序的。因此使用主键进行范围查询的结果可以很容易地跟二级索引的等值查询的结果进行取交集。

Union

与 Intersection 类似的，只不过不再使用 and 取交集，而是使用 or 取并集。比如：

1
2
3
4
5
6
7
explain select * from user where username = 'Bob' or email = 'orghxctxih@cxmxz.gmz';

+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+------------------------------------------------+
|id|select_type|table |partitions|type       |possible_keys         |key                   |key_len|ref |rows|filtered|Extra                                           |
+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+------------------------------------------------+
|1 |SIMPLE     |user  |NULL      |index_merge|idx_username,idx_email|idx_username,idx_email|303,303|NULL|3317|100     |Using union(idx_username,idx_email); Using where|
+--+-----------+------+----------+-----------+----------------------+----------------------+-------+----+----+--------+------------------------------------------------+

在拿到两个结果集之后，由于这两个结果集都是按照主键排序的，因此按照主键值进行合并也是比较容易的，只有合并后的结果集也是按照主键排序，回表时才能够降低成本。

Sort-Union

一般使用 Union 索引合并的条件太苛刻，必须保证每个二级索引都是等值匹配，比如下面这种就无法使用：

1
2
3
4
5
6
7
explain select * from user where username in ('Bob', 'Alice') or code < 2205;

+--+-----------+------+----------+-----------+---------------------+---------------------+-------+----+----+--------+----------------------------------------------------+
|id|select_type|table |partitions|type       |possible_keys        |key                  |key_len|ref |rows|filtered|Extra                                               |
+--+-----------+------+----------+-----------+---------------------+---------------------+-------+----+----+--------+----------------------------------------------------+
|1 |SIMPLE     |user  |NULL      |index_merge|idx_code,idx_username|idx_username,idx_code|303,5  |NULL|5628|100     |Using sort_union(idx_username,idx_code); Using where|
+--+-----------+------+----------+-----------+---------------------+---------------------+-------+----+----+--------+----------------------------------------------------+

这是因为通过这两个索引列匹配到的结果集并不是按照主键排序的，但是我们可以将查询的结果再根据主键进行一次排序，这样就可以使用 Union 的方式进行索引合并。这种将多个根据索引列查询到的结果集进行再次排序，最后取并集的方式就是 Sort-Union。

index

当我们使用索引覆盖，但需要扫描全部的索引记录时，使用的就是 index 访问方法。比如：

1
2
3
4
5
6
7
explain select province, city, county from user where city = '青岛市';

+--+-----------+------+----------+-----+-------------+-----------+-------+----+------+--------+------------------------+
|id|select_type|table |partitions|type |possible_keys|key        |key_len|ref |rows  |filtered|Extra                   |
+--+-----------+------+----------+-----+-------------+-----------+-------+----+------+--------+------------------------+
|1 |SIMPLE     |user  |NULL      |index|NULL         |idx_address|909    |NULL|989489|10      |Using where; Using index|
+--+-----------+------+----------+-----+-------------+-----------+-------+----+------+--------+------------------------+

查询的字段都是索引列，因此可以使用覆盖索引，但是由于查询条件不符合最左前缀原则，所以只能对整个索引树（联合索引树）进行扫描，不过这颗树要比聚簇索引“小”很多，所以性能要比直接遍历聚簇索引（全表扫描）要好一些。

all

all 就是全表扫描，也就是直接遍历聚簇索引，比如：

1
2
3
4
5
6
7
explain select * from user where avatar = 'https://avatar.github.com/cjFQDo';

+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |user  |NULL      |ALL |NULL         |NULL|NULL   |NULL|989489|10      |Using where|
+--+-----------+------+----------+----+-------------+----+-------+----+------+--------+-----------+

写在最后

很多时候查询优化器并不会按照我们预想的方式进行查询，这都是基于查询成本考量下的结果。