mysql高级_03_性能分析explain
explain: 查看执行计划
- 使用 explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL 是如歌处理SQL语句。分析查询语句或者表结构的性能瓶颈
一、 能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
explain + sql 语句
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
二、字段解释
1. id *
- SELECT 查询的序列号,包含一组数字表示查询中执行SELECT子句或操作表的顺序
- 三种情况
- id 相同 : 执行顺序从上到下
- id 不同 : id 大的先执行
- 两种组合: id 大的先执行,相同的从上到下
注意: 小表驱动大表
2. select_type
- 用于区分查询类型
- 有哪些?
- SIMPLE : 简单的 SELECT 查询,查询中不包含子查询或者 UNION
- PRIMARY : 查询中包含任意复杂的子部分,最外层查询则被标记为
- SUBQUERY : 在 SELECT 或者WHERE 列表中包含了子查询
- DERIVED : 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生),MySQL会递归执行这个子查询,把查询结果放在临时表中。
- UNION : 若第二个SELECT 出现在 UNION 之后,则被标记为 UNION, 若UNION 包含在FROM 子句的子查询中外层SELECT 被标记为DERIVED
- UNION RESULT : 从UNION 表获取结果的 SELECT
3. table
显示这一行的数据来自于哪张表
4. type *
- type显示的是访问类型,是 较为重要的一个指标,结果从最好到对坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > rabge > index > ALL
- 下面叙述一下比较常见的几个
system > const > eq_ref > ref > range > index > ALL
一般来说,得保证查询至少达到 range 级别 ,最好能达到 ref 。
4.1 system
- 表只有一行记录(等于系统表),这是 const 类型的特例
- 平时不会出现,这个可以忽略不计
4.2 const
- 表示通过索引一次就找到了。
- const 由于比较 primary key 或者 unique 索引,因为只匹配一行数据,所以很快。
- 如: 将主键置于 where列表中,mysql就能将该查询转换为一个常量。
4.3 eq_ref
- 唯一性索引,对于每个索引健,表中只有一条记录与之匹配。
- 常用于主键或者唯一非空索引扫描。
- 理解:找到一个就立马返回
4.4 ref
- 非唯一性索引扫描,返回匹配某个单独值的所有行。
- 本质上也是一种索引访问,它返回所有匹配某个单独的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找的扫描的混合体。
- 理解: 因为不是唯一的所以找到一个 还得继续往下找
4.5 range
- 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引
- 一般就是在where语句中出现 between、<、> 、 in 等查询
- 这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引
4.6 index
- 全索引扫描,只扫描索引树。
- 这个通常比ALL快,因为索引文件通常比数据文件小。
4.7 ALL
最差的,全表扫描
5. possible_keys & key *
- possible_keys
- 显示可能应用在这张表中的索引,一个或者多个。
- 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被实际查询使用。
- key
- 实际使用到的索引。如果为NULL ,则没有使用索引 (可能是根本没建索引,或者索引失效)
- 查询中若使用了覆盖索引,则该索引仅出现在 key 列表中
6. key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
- key_len 显示的值为 索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表 定义计算而得,不是通过表内检索出的
7. ref
- 显示索引的哪一列被使用了,如果可能的话,是一个常数。
- 哪些列或者常量被用于查找索引列上的值。
- 就是用什么查找索引
8. rows *
- 根据表统计信息及索引选用情况,大致估算数找到所需的记录所需要读取的行数。
9. Extra *
- 包含不适合在其他列显示,但十分重要的额外信息
9.1Using filesort
- 说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
- MySQL 中无法利用索引完成的排序操作称为“文件排序”。
9.2 Using temporary
- 使用临时表保存中间结果,MySQL 早对结果排序时使用临时表。
- 常见于 排序 order by 和 分组查询 group by
9.3 USING index
- 表示相应的 SELECT 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错
- 如果同时出现了 using where ,表明索引被用来只想索引健的查找
- 如果没有出现 using where ,表示索引用来读取数据而非执行查找动作。
- 理解: 就是SELECT 的数据列,只用从索引中就能够取得,不必读取数据行,MySQL 可以利用索引返回SELECT列表中的字段而不必根据索引再次读取数据文件,换句话说,查询列要被所建的索引覆盖
9.4 Using where
- 使用了where 过滤
9.5 Using join buffer
- 使用了连接缓存
9.6 impossible where
- where 子句的值总是false ,不能用来获取任何元祖
9.7 select tables optimzied away
- 没有 Group by 子句的情况下,基于索引优化 MIN/MAX操作
- 或者对于 MYISM 存储引擎优化 COUNT(*)操作,不必等到只想阶段再进行计算,查询执行计划生成的阶段即完成优化。
9.8 distinct
- 优化distinct 操作,在找到第一个匹配的元祖后即停止找同样值的动作。