msyql中Explain的用法详解
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-10 10:41:33 浏览: 评论:0
Explain在mysql的作用我想大家都明白,显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句.
一.语法:explain < table_name >
例如:explain select * from t3 where id=3952602;
二.explain输出解释,代码如下:
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
1.id
我的理解是SQL执行的顺利的标识,SQL从大到小的执行.例如:
- mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
- | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
- | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
- | 3 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
很显然这条SQL是从里向外的执行,就是从id=3 向上执行.
2.select_type
就是select类型,可以有以下几种:
(1) SIMPLE
简单SELECT(不使用UNION或子查询等) 例如:
- mysql> explain select * from t3 where id=3952602;
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
- | 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
(2).PRIMARY
我的理解是最外层的select.例如:
- mysql> explain select * from (select * from t3 where id=3952602) a ;
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
- | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
- | 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
(3).UNION
UNION中的第二个或后面的SELECT语句.例如
- mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
- | 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
- | 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
- |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
(4).DEPENDENT UNION
UNION中的第二个或后面的SELECT语句,取决于外面的查询,代码如下:
- mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;
- +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
- | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
- | 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
- | 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
- |NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
- +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
(4).UNION RESULT
UNION的结果,代码如下:
- mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
- | 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
- | 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
- |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
(5).SUBQUERY
子查询中的第一个SELECT,代码如下:
Tags: msyql Explain用法
相关文章
- ·windows命令行msyqldump导出数据库备份(2014-10-08)
- ·mysql explain 用法详解(2014-10-14)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)