我要评论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关键字来查看单表查询的访问方式。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论