首页 > 数据库 >TYPE（3）—mysql执行计划（四十九)

TYPE（3）—mysql执行计划（四十九)

时间：2022-11-01 17:41:05浏览次数：41  
标签：ref TYPE s1 四十九 key mysql NULL type key1


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)

放弃索引直接全盘扫描

标签：ref,TYPE,s1,四十九,key,mysql,NULL,type,key1
From： https://blog.51cto.com/u_15856702/5814516

相关文章

  • Extra（5）—mysql执行计划（五十一)
    前面说了有type,simple表示普通查询或者连接查询，primary代表union最左边的select，unionresult代表union查询的临时表去重，所以unionall没有去重功能，subquery代表in的子查询......
  • Select type&partitions (2)—mysql执行计划（四十八)
    前面说了explain的table是表名，显示在前面的代表驱动表，正常select会出现不同的id，但如果子查询本来是两个select，但被优化成连接查询，就会导致是相同的id，union查询会出现临时表，i......
  • key&key_len&ref&filtered（4）—mysql执行计划（五十)
    前面说了system是精确存储引擎和只存一条数据，const是主键和唯一索引才能达到的效率访问，ref是二级索引等值查询，或者联合索引全部等值，如果联合索引单个查询，则是index，ref_not是......
  • id，table列(1)—mysql执行计划（四十七)
    前面说了semi-join，这个是在where或者on语句后面，in里面，并且外层的条件必须用and与子查询连接，semi-join的作用就是，不管子查询有多少条数据返回，都不管，外层都只查询出来外层表数......
  • Semi-join使用条件，派生表优化 (3)—mysql基于规则优化（四十六)
    前面说了子查询里有no/any/all不能用limit，groupby，orderby等，他会被查询优化器优化掉，子查询可能会物化转成内连接semi-join查询，物化就是会吧子查询看做一个表，如果数据太大，超......
  • 子查询注意事项&semi-join(2)—mysql基于规则优化（四十五)
    前面说了mysql会吧一些冗余的sql语句查询优化重写，比如多于的括号，比如有的外连接其实跟内连接类似，可以优化查询表的顺序。子查询又分为相关和不相关子查询，如果子查询过滤条件......
  • 不重用的undo日志 (2)—mysql进阶（六十五)
    前面我们说了undo日志在insert，update，delete存储的日志格式，delete存储的type是trx_undo_del_mark_rec里面有个参数oldroll_pointer会指向insert的地址值，恢复需要的数据。Sel......
  • undo日志insert，update，delete (1)—mysql进阶（六十四)
    前面说了redo日志为了保证系统宕机的情况下，能够恢复数据，恢复数据是在以checkpoint_lsn为起始位子来恢复，在该值之前的都是已经持久化到磁盘的，可以为了提升效率而放弃，而之后的......
  • redo log(1)—mysql进阶（五十九)
    上篇文章说了我们可以用begin和statrtransaction，提交可以commit，rollback回滚，可以指定回滚到保存点，也可以设置全局变量setautocommitoff。也会隐式提交，比如开启事务后，如......
  • 【MySQL】常见错误汇总
    一、MySQL无法重新启动（cmd）netstopmysql手动关闭mysql后netstartmysql无法重启，提示服务没有报告任何错误直接上网搜启动问题的话可能解决不了，因为原因很多样。所以先......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99