商务网站 活,电子商务发展的前景,城网站建设,北京市建设中心网站使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句#xff0c;从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。
MySQL查询过程 通过explain我们可以获得以下信息#xff1a; 表的读取顺序 数据读取操作的操作类型 哪些索引可以被使用 …使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。
MySQL查询过程 通过explain我们可以获得以下信息 表的读取顺序 数据读取操作的操作类型 哪些索引可以被使用 哪些索引真正被使用 表的直接引用 每张表的有多少行被优化器查询了
Explain使用方式: explainsql语句, 通过执行explain可以获得sql语句执行的相关信息
explain select * from users;type字段中有哪些常见的值
type字段在 MySQL 官网文档描述如下 The join type. For descriptions of the different types. type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。
下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:
-- 完整的连接类型比较多
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
-- 简化之后,我们可以只关注一下几种
system const eq_ref ref range index ALL 一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整 下面介绍type字段不同值表示的含义:
type类型解释system不进行磁盘IO,查询系统表,仅仅返回一条数据const查找主键索引,最多返回1条或0条数据. 属于精确查找eq_ref查找唯一性索引,返回数据最多一条, 属于精确查找ref查找非唯一性索引,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是多条.range查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: 、 、in 、betweenindex查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.ALL不使用任何索引,直接进行全表扫描
Extra有哪些主要指标各自的含义是什么
Extra 是 EXPLAIN 输出中另外一个很重要的列该列显示MySQL在查询过程中的一些详细信息
extra类型解释Using filesortMySQL中无法利用索引完成的排序操作称为 “文件排序”Using index表示直接访问索引就能够获取到所需要的数据覆盖索引不需要通过索引回表Using index condition搜索条件中虽然出现了索引列但是有部分条件无法使用索引 会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。Using join buffer使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询Using where意味着全表扫描或者在查找使用索引的情况下但是还有查询条件不在索引字段当中
实际使用示例
先解析一条sql语句看出现什么内容
EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address FROM uchome_space AS s,uchome_spacefield AS f WHERE 1 AND s.groupid0 AND s.uidf.uid
1. id
SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序看下面这条sql
EXPLAIN SELECT * FROM (SELECT* FROM uchome_space LIMIT 10) AS s
它的执行结果为 可以看到这时的id变化了
如果是子查询id序号会自增id值越大优先级就越高越先被执行
id 相同与不同同时存在
mysql EXPLAIN - select * from(select * from tb_order tb1 where tb1.id 1) s1,tb_user tb2 where s1.tb_user_id tb2.id;
------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | PRIMARY | tb2 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DERIVED | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
------------------------------------------------------------------------------------------
2.select_type
select类型数据读取操作的操作类型它有以下几种值
2.1 simple
它表示简单的select,没有union和子查询
2.2 primary
最外面的select,在有子查询的语句中最外面的select查询就是primary,上图中就是这样
2.3 union
union语句的第二个或者说是后面那一个.现执行一条语句
explain select * from uchome_space limit 10 union select * from uchome_space limit 10,10
会有如下结果 第二条语句使用了union
2.4 dependent union
UNION中的第二个或后面的SELECT语句取决于外面的查询
2.5 union result
从UNION表获取结果的selectUNION的结果,如上面所示 2.6 DERIVED
在FROM列表中包含的子查询会被标记为DERIVED(衍生表)MYSQL会递归执行这些子查询把结果集放到零时表中。 还有几个参数这里就不说了不重要
3 table
该行数据是关于哪张表输出的行所用的表这个参数显而易见容易理解
4 type
连接类型。有多个参数先从最佳类型到最差类型介绍 重要且困难
访问类型 由好到差system const eq_ref ref range index ALL
4.1 system
表仅有一行这是const类型的特列平时不会出现这个也可以忽略不计
4.2 const
表最多有一个匹配行const用于比较primary key 或者unique索引。因为只匹配一行数据所以很快
记住一定是用到primary key 或者unique并且只检索出两条数据的 情况下才会是const,看下面这条语句
explain SELECT * FROM asj_admin_log limit 1,结果是 虽然只搜索一条数据但是因为没有用到指定的索引所以不会使用const.继续看下面这个
explain SELECT * FROM asj_admin_log where log_id 111 log_id是主键所以使用了const。所以说可以理解为const是最优化的
4.3 eq_ref
对于eq_ref的解释mysql手册是这样说的:对于每个来自于前面的表的行组合从该表中读取一行。这可能是最好的联接类型除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用比较带索引的列。看下面的语句
explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid uchome_space.uid
得到的结果是下图所示。很明显mysql使用eq_ref联接来处理uchome_space表。 目前的疑问 4.3.1 为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成 explain select * from uchome_space,uchome_spacefield where uchome_space.uid uchome_spacefield.uid 结果还是一样需要说明的是uid在这两个表中都是primary
4.4 ref
对于每个来自于前面的表的行组合所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀或如果键不是UNIQUE或PRIMARY KEY换句话说如果联接不能基于关键字选择单个行的话则使用ref。如果使用的键仅仅匹配少量行该联接类型是不错的。
看下面这条语句 explain select * from uchome_space where uchome_space.friendnum 0得到结果如下这条语句能搜出1w条数据 4.5 ref_or_null
该联接类型如同ref但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
上面这五种情况都是很理想的索引使用情况
4.6 index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下key列包含了使用的索引的清单key_len包含了使用的索引的最长的关键元素。
4.7 unique_subquery
4.8 index_subquery
4.9 range
给定范围内的检索使用一个索引来检查行。看下面两条语句
explain select * from uchome_space where uid in (1,2)
explain select * from uchome_space where groupid in (1,2)
uid有索引groupid没有索引结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显
explain select * from uchome_space where friendnum 17
这样的语句是不会使用range的它会使用更好的联接类型就是上面介绍的ref
只检索给定范围的行使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、 、 、in 等查询这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点而结束于另一点不用扫描全部索引。
4.10 index
该联接类型与ALL相同除了只有索引树被扫描。这通常比ALL快因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表但index是从索引中读取的而all是从硬盘中读的
当查询只使用作为单索引一部分的列时MySQL可以使用该联接类型。
FUll Index Scan 扫描遍历索引树(扫描全表的索引从索引中获取数据)。
4.11 ALL
对于每个来自于先前的表的行组合进行完整的表扫描。如果表是第一个没标记 const的表这通常不好并且通常在它情况下 很差。通常可以增加更多的索引而不要使用 ALL使得行能基于前面的表中的常数值或列值被检索出。
全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化
5 possible_keys
提示使用哪个索引会在该表中找到行不太重要
显示可能应用在这张表的索引一个或者多个。查询涉及到的字段若存在索引则该索引将被列出但不一定被查询实际使用。
6 keys
MYSQL使用的索引简单且重要
实际使用到的索引。如果为NULL则没有使用索引。查询中如果使用了覆盖索引则该索引仅出现在key列表中。覆盖索引select 后的 字段与我们建立索引的字段个数一致。
7 key_len
MYSQL使用的索引长度
表示索引中使用的字节数可通过该列计算查询中使用的索引长度。在不损失精确性的情况下长度越短越好。key_len 显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出来的。
8 ref
ref列显示使用哪个列或常数与key一起从表中选择行。
显示索引的哪一列被使用了如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值。 9 rows
显示MYSQL执行查询的行数简单且重要数值越大越不好说明没有用好索引
(每张表有多少行被优化器查询):根据表统计信息及索引选用的情况大致估算找到所需记录需要读取的行数。
10 Extra
该列包含MySQL解决查询的详细信息。
10.1 Distinct
MySQL发现第1个匹配行后停止为当前的行组合搜索更多的行。一直没见过这个值
10.2 Not exists
10.3 range checked for each record
没有找到合适的索引
10.4 using filesort
MYSQL手册是这么解释的“MySQL需要额外的一次传递以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序并按排序顺序检索行。”目前不太明白
我的理解如果存在排序并且取出的列包括text类型会使用到using filesort这会非常慢
10.5 using index
只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解就是说明是否使用了索引
explain select * from ucspace_uchome where uid 1的extra为using indexuid建有索引
explain select count(*) from uchome_space where groupid1 的extra为using where(groupid未建立索引)
10.6 using temporary
为了解决查询MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
出现using temporary就说明语句需要优化了举个例子来说
EXPLAIN SELECT ads.id FROM ads, city WHERE city.city_id 8005 AND ads.status online AND city.ads_idads.id ORDER BY ads.id desc
id select_type table type possible_keys key key_len ref rows filtered Extra ------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ------------------------------- 1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using temporary; Using filesort 1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
这条语句会使用using temporary,而下面这条语句则不会
EXPLAIN SELECT ads.id FROM ads, city WHERE city.city_id 8005 AND ads.status online AND city.ads_idads.id ORDER BYcity.ads_id desc
id select_type table type possible_keys key key_len ref rows filtered Extra ------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- --------------------------- 1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using where; Using filesort 1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
这是为什么呢他俩之间只是一个order by不同MySQL 表关联的算法是 Nest Loop Join是通过驱动表的结果集作为循环基础数据然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据然后合并结果。EXPLAIN 结果中第一行出现的表就是驱动表Important!以上两个查询语句驱动表都是 city如上面的执行计划所示
对驱动表可以直接排序对非驱动表的字段排序需要对循环查询的合并结果临时表进行排序 Important!
因此order by ads.id desc 时就要先 using temporary 了
驱动表的定义 1指定了联接条件时满足查询条件的记录行数少的表为[驱动表] 2未指定联接条件时行数少的表为[驱动表]Important!。
永远用小结果集驱动大结果集
当不确定是用哪种类型的join时让mysql优化器自动去判断我们只需写select * from t1,t2 where t1.field t2.field
通俗解释select * from table order by field
其中filed建普通索引这种情况会使用到using temporary,因为虽然这时候使用到了索引但因为扫描的是全表mysql优化器会判断反正是搜索全表而且要排序因为这时候要回行我还不如不沿着索引找数据直接全部检索出所有数据来在排序。
如果语句这么写 select * from table where field 1 order by field.mysql优化器就会这么判断这时候不是搜全表我需要先根据where条件,沿着索引树搜出想要的相应索引数据在回行一边找索引一边回行。这时候就不需要临时表了
10.7 using where
WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行如果Extra值不为Using where并且表联接类型为ALL或index查询可能会有一些错误。这个说明不是很理解因为很多很多语句都会有where条件而type为all或index只能说明检索的数据多并不能说明错误useing where不是很重要但是很常见
如果想要使查询尽可能快应找出Using filesort 和Using temporary的Extra值。
10.8 Using sort_union(...) , Using union(...) , Using intersect(...)
这些函数说明如何为index_merge联接类型合并索引扫描
10.9 Using index for group-by
类似于访问表的Using index方式Using index for group-by表示MySQL发现了一个索引可以用来查询GROUP BY或DISTINCT查询的所有列而不要额外搜索硬盘访问实际的表。并且按最有效的方式使用索引以便对于每个组只读取少量索引条目。 知识来源马士兵教育
MYSQL explain详解_Venlenter的博客-CSDN博客
Mysql中explain作用详解_Mysql_脚本之家
