mysql学习计划_MySQL查询计划学习

最新推荐文章于 2024-09-09 15:14:22 发布

咪马3213mkq~

最新推荐文章于 2024-09-09 15:14:22 发布

阅读量233

点赞数

文章标签： mysql学习计划

本文链接：https://blog.csdn.net/weixin_28955231/article/details/113126506

版权

以select_type为线索：

1) SIMPLE：简单的SELECT，不实用UNION或者子查询

mysql> explain select * fromt2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

2) PRIMARY：最外层SELECT。

mysql> explain select * from (select * from t2 where id2=2) b;+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

2 rows in set (0.00 sec)

其中using where表示使用了where子句，过滤了元组

3) UNION：第二层，在SELECT之后使用了UNION。

mysql> explain select * from t1 union select * fromt2;+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

3 rows in set (0.00 sec)

其中using temporary表示使用了临时表保存中间结果。

4) DEPENDENT UNION：UNION语句中的第二个SELECT，依赖于外部子查询。

mysql> explain select * from t1 where id1 in (select id2 from t2 where id2<10 union select id3 from t3 where id3<5);

+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+

+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+

| 3 | DEPENDENT UNION | t3 | ref | id3 | id3 | 5 | func | 1 | Using where; Using index |

+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+

4 rows in set (0.00 sec)

其中possible_keys表示备选索引为id3，key表示经过优化器选定的索引为id3，type为ref表示使用单表t3扫描。

5) UNION RESULT：UNION的结果。

mysql> explain select * from t1 union select * fromt2;+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+

3 rows in set (0.00 sec)

6) SUBQUERY：子查询中的第一个SELECT。

mysql> explain select * from t1 where id1=(select id2 from t2 where id2=2);+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

2 rows in set (0.00 sec)

其中type为const表示访问常量表，where条件筛选后至多有一条元组匹配。

key为PRIMARY表示主键为选定的索引

type: 表示表的连接类型，性能由好到坏，依次为，

system，表中只有一行，常量表。

const，单表中最多有一行匹配，如pramary key 或者 unique index

eq_ref，对于前面的每一行，此表中只查询一条记录，比如多表连接中，使用primary key 或 unique index

ref，与eq_ref 类似，区别在于不是primary key 或qunique index, 而是普通索引。

ref_or_null，与ref 类似，区别在于查询中包含对null的查询。

index_merge，索引合并优化

unique_subquery，in的后面是一个查询主键字段的子查询。

index_subquery，与unique_subquery类似，区别在于是查询非唯一索引字段的子查询。

range，单表中的范围查询。

index，对于前面的每一行，都通过查询索引来得到数据。

all，全表扫描。

7) DERIVED：被驱动的SELECT子查询

mysql> explain select * from (select * from t2 where id2=2) b;+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

2 rows in set (0.00 sec)