explain是mysql的关键字吗_mysql优化必备explain关键字_MySQL

MySQLexplain

一.语法EXPLAIN select sid,filename,filetype from (SELECT * from by_disk_file where uid=830814) a

结果：

id:SQL执行顺序标示，SQL是从大到小执行的

select_type:为查询类型，有以下几种：

1.SIMPLE

简单SELECT(不使用UNION或子查询等)

例如：explain SELECT * from by_disk_file where uid=830814

2.PRIMARY

最外层的查询操作

例如：explain select * from (SELECT * from by_disk_file where uid=830814 )

3.UNION

UNION中的第二个或后面的SELECT语句

例如：explain select * from by_disk_file where uid=830814 union all select * from by_disk_file

4.DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

例如：explain select * from by_disk_file where uid in(select id from by_common_member where username='mm' union all select id from by_common_member where username='gg')

5.UNION RESULT

UNION的结果

例如：explain select * from by_disk_file where uid=830814 union all select * from by_disk_file UNION ALL select * from by_disk_file where uid=830814

UNION查询的步骤(根据以上内容)：

定义一个UNION结果集->UNION获得语句(从右到左)->第一条语句->结果全获取返回

6.SUBQUERY

子查询中的第一个查询explain select * from by_disk_file where uid=(select uid from by_common_member where username='mm')

7.DEPENDENT SUBQUERY

子查询中的一个查询,取决于外面的查询explain select * from by_disk_file where sid in (select sid from by_disk_file where uid=830814)

explain select * from by_disk_file where sid =(select sid from by_disk_file where uid=830814 LIMIT 1)

备注：如果外面的查询不是范围查询只会被查到一次，则为SUBQUERY

如果是使用in类似的范围查询，则为DEPENDENT SUBQUERY

8.DERIVED

派生表的查询(FROM子句的子查询)explain select sid,filename,filetype from (SELECT * from by_disk_file where uid=830814) a

本文原创发布php中文网，转载请注明出处，感谢您的尊重！