mysql-单表访问方法(访问类型)

测试表

mysql> show create table single_table\G *************************** 1. row *************************** Table: single_table Create Table: CREATE TABLE `single_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1` varchar(100) DEFAULT NULL, `key2` int(11) DEFAULT NULL, `key3` varchar(100) DEFAULT NULL, `key_part1` varchar(100) DEFAULT NULL, `key_part2` varchar(100) DEFAULT NULL, `key_part3` varchar(100) DEFAULT NULL, `common_field` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_key2` (`key2`), KEY `idx_key1` (`key1`), KEY `idx_key3` (`key3`), KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`) ) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 1 row in set (0.01 sec) mysql> show create table single_table2\G *************************** 1. row *************************** Table: single_table2 Create Table: CREATE TABLE `single_table2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1` varchar(100) DEFAULT NULL, `key2` int(11) DEFAULT NULL, `key3` varchar(100) DEFAULT NULL, `key_part1` varchar(100) DEFAULT NULL, `key_part2` varchar(100) DEFAULT NULL, `key_part3` varchar(100) DEFAULT NULL, `common_field` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_key2` (`key2`), KEY `idx_key1` (`key1`), KEY `idx_key3` (`key3`), KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`) ) ENGINE=InnoDB AUTO_INCREMENT=20003 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

访问方法

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

MyISAM 存储引擎

mysql> CREATE TABLE t(i int) Engine=MyISAM; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t VALUES(1); Query OK, 1 row affected (0.01 sec) # 只有一条记录，此时 storage engine 是 MyISAM mysql> explain select * from t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) # 修改 storage engine mysql> alter table t engine=InnoDB; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> explain select * from t; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)

Memory 引擎

# 修改为 Memory 引擎 mysql> alter table t engine=Memory; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> explain select * from t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

const

等值查询之主键列

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

等值查询之 unique 列

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

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id; +----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+ | 1 | SIMPLE | single_table | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | single_table2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.single_table.id | 1 | 100.00 | NULL | +----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)

ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref

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

key1 列有二级索引。先在二级索引 idx_key1 查询满足 key1 = ‘abc’ 的 id 值，然后拿着 id 值去聚簇索引上查询。

ref_or_null

查询的某一列既有等值查询，又有 NULL 值查询。

mysql> explain select * from single_table where key1 = 'abc' or key1 is null; +----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | single_table | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 2 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

index_merge

range

查询索引列的某个范围的值。

mysql> explain select * from single_table where key2 in (1368,1644) or (key2 >= 38 and key2 <=79); +----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | single_table | NULL | range | idx_key2 | idx_key2 | 5 | NULL | 44 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

index

select 中所有的列正好是某个二级索引使用的列。直接遍历二级索引的叶子节点得到符合条件的数据。不用回表查询聚簇索引。

mysql> explain select key_part1, key_part2, key_part3 from single_table where key_part2 = 'power_to_go'; +----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | single_table | NULL | index | NULL | idx_key_part | 909 | NULL | 9921 | 10.00 | Using where; Using index | +----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)

ALL

遍历整个聚簇索引。

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

Reference

单表访问方法explain-type-column