TYPE（3）—mysql执行计划（四十九)
Simple：普通的sql查询，连接查询
Primary：union左边的表。
Union：union和union all都叫几个select，除了最左边的是primary，其他都是union。
Union result：union去重临时表。
Subquery：子查询，不相关查询，没有转semi-join，并且mysql优化器选择了物化表查询，因为物化，只执行一次。
Dependnet subquery：相关子查询，因为没有物化，需要执行多次。
Dependent union：子查询里union除了最左边的select。
Derived：from派生查询，并且没有转成连接查询。
Materialized：物化之后，并且与外层连接查询。
Select type&partitions (2)—mysql执行计划（四十八)
TYPE
前面我们说过了mysql执行sql语句会采用什么方法，比如const，ref，ref_or_null,range,index,all，这个type就代表执行sql的查询方法，但我们前面只说了innoDB存储引擎进行表单访问的一些方法，完整的我们下面介绍一下。
System
当表里只有一条记录，并且这里只有统计数据是精确的，才可以用到这个方法，注意innoDB之前说过都是估算，所以这里必须是myISAM或者memory
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) 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) mysql> explain select * from innoDB_tb;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | innoDB_tb | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)从上可以看到，这种是system，如果是innoDB存储引擎则是all。
Const
当我们用主键或者唯一二级索引，就是火箭一样的const速度，
mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)eq_ref
在连接查询时，被驱动表查询是主键或者唯一二级索引等值查询的时候，则type是eq_ref。（如果该唯一索引和主键是联合索引，就必须所有的都等值匹配）
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9688 | 100.00 | NULL || 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.s1.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+2 rows in set, 1 warning (0.01 sec)ref
当普通二级索引可以等值查询走b+树的时候，type就是ref。
fulltext
全文索引，略过
ref_or_null
当普通二级索引等值查询时候，当前列有null值
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 9 | 100.00 | Using index condition |+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)index_merge
前面我们说过，如果没有建立联合索引的情况下，单个索引查询会合并索引，当and 的时候用intersection合并，当or的时候，用union合并，主要减少回表开销，在索引树过滤。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+| 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 14 | 100.00 | Using union(idx_key1,idx_key3); Using where |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+1 row in set, 1 warning (0.01 sec)unique_subquery
eq_ref是两表查询被驱动使用主键或者唯一索引执行计划查询数据库，这个是在in语句里，如果查询优化器将in转成exists，并且采用主键查询，
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)index_subquery
与上面类似，只是in的子查询用的是普通索引
mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key1,idx_key3 | idx_key3 | 303 | func | 1 | 10.00 | Using where |+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.01 sec)range
范围区间查询就会用到
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 27 | 100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 294 | 100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)index
当使用索引覆盖，需要扫描索引的所有数据时候
mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 9688 | 10.00 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)如图所示，我们的列和过滤条件刚好在同一个联合索引里，这时候就会需要扫描联合索引。因为二级索引树只有索引和主键，聚簇索引树有全部的数据，所以扫描联合索引树的代价更小。
ALL
mysql> EXPLAIN SELECT * FROM s1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)放弃索引直接全盘扫描
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-09-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
