国内最专业的IT技术学习网

UI设计

当前位置:主页 > UI设计 >

MySQL EXPLAIN结果集分析 - 附带大量案例

发布时间:2019/09/17标签:   索引    点击量:

原标题:MySQL EXPLAIN结果集分析 - 附带大量案例
MySQL EXPLAIN结果集分析 - 附带大量案例EXPLAIN:检查SQL语句的履行打算EXPLAIN下令能够关心咱们深刻懂得MySQL基于开支的优化器,还能够取得许多能够被优化器斟酌到的拜访战略的细节,以及当运转SQL语句时哪类战略估计会被优化器采纳,在优化慢查问时十分有效。履行explain以后成果集包括以下信息+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+上面将对每一个值停止说明1、idid用来标识全部查问中SELELCT语句的次序,在嵌套查问中id越大的语句越先履行,该值能够为NULLid假如雷同,从上往下顺次履行。id差别,id值越大,履行优先级越高,假如行援用其余行的并集成果,则该值能够为NULL2、select_typeselect_type表现查问应用的范例,有上面几种:simple: 简略的select查问,没有union或许子查问mysql>explainselect*fromtestwhereid=1000;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|1|SIMPLE|test|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+primary: 最外层的select查问mysql>explainselect*from(select*fromtestwhereid=1000)a;+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+|1|PRIMARY|<derived2>|system|NULL|NULL|NULL|NULL|1|NULL||2|DERIVED|test|const|PRIMARY|PRIMARY|8|const|1|NULL|+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+union: union中的第二个或随后的select查问,不依靠于内部查问的成果集mysql>explainselect*fromtestwhereid=1000unionallselect*fromtest2;+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+|1|PRIMARY|test|const|PRIMARY|PRIMARY|8|const|1|NULL||2|UNION|test2|ALL|NULL|NULL|NULL|NULL|67993|NULL||NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+

版权信息Copyright © 银河官网 版权所有    ICP备案编号:鲁ICP备09013610号