mysql explain 用法详解
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-14 14:18:17 浏览: 评论:0
mysql explain 可以用来分析你要查询的sql语句的各项参数值,下面我们有详细的实例有需要的可以看看,代码如下:
EXPLAIN table == DESC table == SHOW COLUMNS FORM table
EXPLAIN [EXTENDED|PARTITIONS] SELECT... --显示该语句将使用哪一个索引以及何时进行多表查询与使用到的表顺序,代码如下:
- mysql> EXPLAIN SELECT * FROM BOOKS WHERE BOOK_ID=1;
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | 1 | SIMPLE | BOOKS | const | PRIMARY | PRIMARY | 4 | const | 1 | |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- 1 row in set (0.00 sec)
POSSIBLE_KEYS字段列举出了用于查找数据的索引,而KEY字段指示我们实际上用到了XX索引,如果POSSIBLE_KEYS字段的值显示NULL,那么说明没有用到索引.
SELECT_TYPE
SIMPLE 指示简单SELECT语句,没有子查询或者UNION
PRIMARY 当使用子查询时,这是主要的SELECT语句
UNION 当使用子查询时,这是主要的SELECT语句
DEPENDENT UNION 当使用UNION时,这并不是第一个SELECT语句,取决于主查询
UNION RESULT UINON查询
SUBQUERY 子查询中的第一个SELECT语句
DEPENDENT SUBQUERY 子查询中的第一个SELECT语句,取决于主查询
DERIVED 来自于子查询的表
UNCACHEABLE SUBQUERY 指示子查询中的结果不能缓存,因此必须对主查询中的每一行重新评价.
UNCACHEABLE UNION 指示子查询的UNION中,结果不能缓存,因此必须对主查询中的每一行重新评价.
这是在官网上的说明,代码如下:
- EXPLAIN Syntax
- EXPLAIN [EXTENDED] SELECT select_options
- Or:
- EXPLAIN tbl_name
The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE:
When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.
例如如下代码:
- 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 | |
- +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
(5).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 | |
- +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
(6).SUBQUERY,子查询中的第一个SELECT,代码如下:
- mysql> explain select * from t3 where id = (select id from t3 where id=3952602 ) ;
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
- | 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 | SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index | --phpfensi.com
- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
(7).DEPENDENT SUBQUERY,子查询中的第一个SELECT,取决于外面的查询,代码如下:
- mysql> explain select id from t3 where id in (select id from t3 where id=3952602 ) ;
- +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
- | 1 | PRIMARY | t3 | index | NULL | PRIMARY | 4 | NULL | 1000 | Using where; Using index |
- | 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
- +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
(8).DERIVED,派生表的SELECT(FROM子句的子查询),代码如下:
- 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 | |
- +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
Tags: mysql explain用法
相关文章
- ·怎样使MySQL安全以对抗解密高手(2013-11-11)
- ·MYSQL用户root密码为弱口令的攻击(2013-11-11)
- ·Mysql数据库的安全配置、实用技巧(2013-11-11)
- ·Mssql和Mysql的安全性分析(2013-11-11)
- ·MySQL安全问题的一点心得(2013-11-11)
- ·PHP常用MySql操作(2013-11-13)
- ·MySQL数据库下载漏洞攻击技术(2013-11-14)
- ·MySQL3.23.31之前版本的安全漏洞(2013-11-14)
- ·通过Mysql的语句生成后门木马的方法(2013-11-14)
- ·MySQL服务器内部安全数据目录访问(2013-11-14)
- ·MySQL数据库中的安全解决方案(2013-11-14)
- ·如何保护MySQL 中的重要数据(2013-11-14)
- ·为你的MySQL数据库加铸23道安全门(2013-11-14)
- ·Mysql的Root密码遗忘的解决办法(2013-11-14)
- ·如何开启MySQL的远程帐号(2013-11-14)
- ·我的Mysql碰到了 1067 错误(2013-11-27)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)