【MySQL】InnoDB单表访问方法

1、背景

mysql通过查询条件查询到结果的过程就叫访问方法，一条查询语句的访问方法有很多种，接下来我们就来讲一下各种访问方法。

2、环境

创建表：

mysql> CREATE TABLE test2-> (->     id INT AUTO_INCREMENT PRIMARY KEY,->     str1 VARCHAR(255),->     str2 VARCHAR(255),->     str3 CHAR(5),->     str4 VARCHAR(255),->     str5 CHAR(10),->     INDEX idx_str1 (str1),->     UNIQUE INDEX idx_str3 (str3),->     INDEX idx_str4_str5 (str4, str5)-> ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

插入100条数据：

mysql> INSERT INTO test2 (str1, str2, str3, str4, str5) VALUES->                                                      ('value1', 'data1', 'abc', 'value4_1', 'value5_1'),->                                                      ('value2', 'data2', 'def', 'value4_2', 'value5_2'),->														...->                                                      ('value99', 'data99', 'yz91', 'value4_99', 'value5_99'),->                                                      ('value100', 'data100', 'yz92', 'value4_100', 'value5_100');
Query OK, 100 rows affected (0.02 sec)
Records: 100  Duplicates: 0  Warnings: 0

3、访问类型

【1】const

通过主键索引或者唯一索引查询一条记录的方法就为const，可以通过explain关键字来看查询语句的访问方式，通过主键查询示例：

mysql> explain select * from test2 where id = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

type字段就是访问方式，我们再看看通过唯一索引查询的示例：

mysql> explain select * from test2 where str3 = 'abc';
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | const | idx_str3      | idx_str3 | 16      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

【2】ref

使用普通二级索引进行等值匹配时，访问类型就为ref，示例如下：

mysql> explain select * from test2 where str1 = 'value7';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | idx_str1      | idx_str1 | 767     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

【3】ref_or_null

二级索引进行等值匹配时，又想把值为NULL的查询出来，这种查询类型就为ref_or_null，先把上面插入的数据部分记录的str1字段改为NULL，sql如下：

mysql> update test2 set str1 = NULL where id in (3, 6, 8, 9, 34, 78, 89);
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

再看查询类型：

mysql> explain select * from test2 where str1 = 'value7' or str1 = null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------
---------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra|
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------
---------------+
|  1 | SIMPLE      | test2 | NULL       | ref_or_null | idx_str1      | idx_str1 | 768     | const |    2 |   100.00 | Using i
ndex condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------
---------------+
1 row in set, 1 warning (0.00 sec)

【4】range

顾名思义范围查询就是range，示例如下：

mysql> explain select * from test2 where id > 2 and id < 7;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

【5】index

使用组合索引中非最左边作为查询条件时，并且查询的字段不需要回表，这个时候就会将组合索引叶子节点全部扫描一遍，这种查询方式就叫index，示例如下：

mysql> explain select str4, str5 from test2 where str5 = 'value5_15';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------
----------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra|
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------
----------------+
|  1 | SIMPLE      | test2 | NULL       | index | idx_str4_str5 | idx_str4_str5 | 799     | NULL |  100 |    10.00 | Using whe
re; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------
----------------+
1 row in set, 1 warning (0.00 sec)

【6】all

对主键索引所在的叶子节点进行全表扫描就叫all，示例如下：

mysql> explain select * from test2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4、总结

mysql中优化器会将我们的查询条件进行优化，我们可以通过explain关键字来查看单表查询的访问方式。