MySQL优化器特性(五）单表访问路径
数据库的访问路径(access path)是指从表中获取数据的方式，一般可以通过扫描表或通过索引获取数据。想熟练掌握SQL优化技能，首先需要熟悉单表访问路径。本文先简单介绍MySQL支持的各种单表访问路径。后续文章中，我们将介绍这些访问路径的运行方式，以及MySQL如何评估这些访问路径的成本。
（以下例子都是基于mysql 8.0.31版本测试）。
测试数据
构造一个简单的测试表和测试数据
mysql> create table numbers(a int, primary key(a)); Query OK, 0 rows affected (0.01 sec) mysql> insert into numbers values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> create table test_ror( id int not null auto_increment, a int, b int, c int, key idx_a(a), key idx_b(b), key idx_c(c), primary key(id) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test_ror(a,b,c) select t1.a, t2.a, t3.a from (select a from numbers where a <= 3) t1, (select a from numbers where a <= 5) t2, (select a from numbers where a <= 7) t3; Query OK, 105 rows affected (0.00 sec) Records: 105 Duplicates: 0 Warnings: 0 CREATE TABLE `test_ror2` ( `id` int NOT NULL AUTO_INCREMENT, `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ac` (`a`,`c`), KEY `idx_bc` (`b`,`c`) ) ENGINE=InnoDB; mysql> insert into test_ror2(a,b,c) -> select t1.a, t2.a, t3.a -> from (select a from numbers where a <= 7) t1, -> (select a from numbers where a <= 11) t2, -> (select a from numbers where a <= 1) t3; Query OK, 77 rows affected (0.00 sec) Records: 77 Duplicates: 0 Warnings: 0
访问路径
数据库的访问路径(access path)是指从表中获取数据的方式，一般可以通过扫描表或通过索引获取数据。MySQL中，单表的访问路径(explain 列中的type字段）包括：
ALL：全表扫描。
缺少索引，或者由于其他原因无法使用索引。
type为ALL
mysql> explain select * from test_ror where a+0=1; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test_ror | NULL | ALL | NULL | NULL | NULL | NULL | 105 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
ref：索引单值扫描
使用单个索引的单个条件的等值匹配。对于组合索引，如果只传入了索引前缀字段的等值条件，也是ref。
mysql> explain select a from test_ror where a=1; +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test_ror | NULL | ref | idx_a | idx_a | 5 | const | 35 | 100.00 | Using index | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
ref or null
在ref的条件下，加上了or col is null条件
mysql> explain select a from test_ror where a=1 or a is null; +----+-------------+----------+------------+-------------+---------------+-------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------------+---------------+-------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | test_ror | NULL | ref_or_null | idx_a | idx_a | 5 | const | 36 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------------+---------------+-------+---------+-------+------+----------+--------------------------+
range：索引范围扫描
使用范围(如 <, <=, >, >=, between, in 多个条件)，或者同一个索引字段多个条件使用or相连
mysql> explain select * from test_ror where c = 1 or c = 2; +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test_ror | NULL | range | idx_c | idx_c | 5 | NULL | 30 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
mysql> explain select * from test_ror where a > 3; +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test_ror | NULL | range | idx_a | idx_a | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
index_merge(intersect)
不同的条件用到了不同的索引，而且这些条件使用and相连。
Extra中显示Using intersect。
mysql> explain select * from test_ror where b=1 and c=1; +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+ | 1 | SIMPLE | test_ror | NULL | index_merge | idx_b,idx_c | idx_c,idx_b | 5,5 | NULL | 3 | 100.00 | Using intersect(idx_c,idx_b); Using where | +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+-------------------------------------------+
使用index intersect有一个前提条件：参与到intersect中的索引，都需要使用等值匹配。如果是组合索引，则索引的所有字段都需要有等值匹配的条件。这个限制是为了保证索引扫描获取的rowid都有序，合并时不需要再额外排序rowid。如果索引扫描获取的rowid无序，则需要排序后才能合并，mysql认为排序有额外开销，就不会采用index intersect的访问路径。
index_merge(union)
不同的条件用到了不同的索引，而且这些条件使用or相连。
Extra中显示Using union
mysql> explain select * from test_ror where b=1 or c=1; +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | test_ror | NULL | index_merge | idx_b,idx_c | idx_b,idx_c | 5,5 | NULL | 33 | 100.00 | Using union(idx_b,idx_c); Using where | +----+-------------+----------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
index_merge(sort_union)
执行计划Extra字段中显示Using sort_union。
因为索引扫描获取到的rowid可能无序（where条件中只传入了组合索引的前缀字段的条件），mysql需要对rowid进行排序后，才能进行合并去重。
mysql> explain select * from test_ror2 where a=1 or b=1; +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+ | 1 | SIMPLE | test_ror2 | NULL | index_merge | idx_ac,idx_bc | idx_ac,idx_bc | 5,5 | NULL | 18 | 100.00 | Using sort_union(idx_ac,idx_bc); Using where | +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
传入组合索引的所有字段的查询条件，则无需排序就可以进行index union：
mysql> explain select * from test_ror2 where (a=1 and c=1) or (b=1 and c=1); +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+ | 1 | SIMPLE | test_ror2 | NULL | index_merge | idx_ac,idx_bc | idx_ac,idx_bc | 10,10 | NULL | 18 | 100.00 | Using union(idx_ac,idx_bc); Using where | +----+-------------+-----------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
需要注意的是，mysql中目前并不存在sort intersect的访问路径。
覆盖索引
查询涉及到的字段全部包含在索引中，无须回表。
Extra中显示Using index
range和ref查询都可以使用覆盖索引
mysql> explain select a from test_ror where a > 1; +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test_ror | NULL | range | idx_a | idx_a | 5 | NULL | 70 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
mysql> explain select a from test_ror where a = 1; +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test_ror | NULL | ref | idx_a | idx_a | 5 | const | 35 | 100.00 | Using index | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
index，索引扫描
无法使用range或ref访问路径，但是查询涉及到的字段都在某个索引中。
type为index，Extra显示Using index
mysql> explain select a from test_ror where a+0=1; +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test_ror | NULL | index | NULL | idx_a | 5 | NULL | 105 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
const
主键或惟一索引等值查询。对于组合索引或主键，索引的所有字段都需要传入等值条件。
mysql> explain select * from test_ror where id = 1; +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_ror | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
索引条件下推
Extra中显示Using index condition
mysql> explain select * from test_ror2 where a<=1 and c > 0; +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test_ror2 | NULL | range | idx_ac | idx_ac | 5 | NULL | 11 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
优化器参数
通过设置optimizer_switch参数选项，可以开启或关闭优化器的某些特性。
和index merge相关的选项有：
index_merge： index merge总开关。
index_merge_intersection：控制index intersect
index_merge_union: 控制index union
index_merge_sort_union：控制index sort union
索引条件下推：
index_condition_pushdown
以上选项默认都开启。
