mysql优化必备explain关键字_MySQL
程序员文章站
2024-01-30 22:17:52
...
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防注入和XSS攻击通用过滤