mysql性能优化研究工具-EXPLAIN.pptxVIP

MYSQL性能优化如何让你的程序更高效的运行执行计划-EXPLAIN执行计划（select_type ）##SIMPLEexplain select code from country where code =ABW;explain select * from country where name =ABW;##UNIONEXPLAIN select * from country union allselect * from country1##MATERIALIZED 被物化的子查询EXPLAIN select * from city a,country b where a.CountryCode = b.Code and b.`Name` in (select Name from country WHERE Region like C%);##SUBQUERY 存在子查询EXPLAIN select * from city a,country b where a.CountryCode = b.Code and b.`Name` = (select Name from country WHERE code=ABW);执行计划（type ）#从左到右 由差到好#const、eq_reg、ref、range、indexhe和ALL##(1)SYSTEM#CONST的特例，当表上只有一条元组匹配EXPLAIN select * from a;#(2)CONST 主键查询EXPLAIN select * from country where code=ABW;#(3)EQ_REF 基于索引 内表只有唯一一条元组与之对应EXPLAIN select * from city a,country b where a.CountryCode = b.Code and b.`Name`=Aruba执行计划（type ）#（4）REF 基于索引的查询EXPLAIN select * from country where region=Caribbean;#（5）RANGE 范围扫描EXPLAIN select * from country where region like C%;#(6)ALL 全表扫描EXPLAIN select * from country;#(7)UNIQUE_SUBQUERY 在子查询中基本唯一索引进行查询EXPLAIN select * from country1 where Name = (select Name from country WHERE code=ABW);#(8)INDEX_SUBQUERY 在子查询中，基于除唯一索引之外的索引进行扫描EXPLAIN select * from country1 where Name = (select Name from country WHERE region=Caribbean);执行计划（Extra）##(1)using where 用了where子句来过滤元组EXPLAIN select * from country where Continent=Caribbean;##(2)using temporary 用临时表保存中间结果explain select count(Continent),Continent from country GROUP BY Continent order by Continent;##(3)using filesort 使用文件完成排序操作explain select LocalName,Continent from country order by LocalName,Continent;##(4)using index索引使用来读取数据EXPLAIN select region from country;执行计划（Extra）##(5)using join buffer使用了连接缓存(Block Nested Loop，连接算法是块嵌套循环连接)explain select * from city a INNER join country b on b.`Code` = a.CountryCode and b.Name in (select Name from country WHERE Region like C%);EXPLAIN select * from city a,country b where a.CountryCode = b.Code and b.`Name` in (select Name from country WHERE Region like C%);##(6)imp