数据库
mysql
MySQL逻辑架构
mysql索引
mysql性能分析(explain)
mysql事务
MVCC
SQL JOINS
本文档使用 MrDoc 发布
-
+
首页
mysql性能分析(explain)
## explain是什么? **模拟优化器查看执行计划** 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的**性能瓶颈** ## explain能干什么? - <font color='red'>表的读取顺序</font> - 那些索引可以使用 - 数据读取操作的操作类型 - <font color='red'>那些索引被实际使用</font> - 表之间的引用 - <font color='red'>每张表有多少行被物理查询</font> ## explain怎么玩? <font color='red'>explain + SQL语句</font> ``` 实例: explain select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id; ``` ## 各字段解释 ### id查询序列号 select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 关注点:<font color='red'>每个id号码,表示一趟独立的查询。一个sql 的查询趟数越少越好。</font> 四种情况: 1. id相同,执行顺序由上至下。例如上图 2. id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 3. id既有相同又有不同 4. id为null最后执行 ### select_type 查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询 | 查询类型 | 描述 | | ---------------------- | ------------------------------------------------------------ | | `SIMPLE` | 简单的 SELECT(没有 使用UNION或者 子查询(PS:单表查询)) | | `PRIMARY` | 最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂)案例1 | | `DERIVED` | 在from 查询语句中的(派生,嵌套很多)子查询。需要临时表。<br />EXPLAIN select * from t1, (select 2) as v; | | `SUBQUERY` | 在SELECT或WHERE列表中包含了子查询。案例2 | | `DEPENDENT SUBQUERY` | 第一个查询是子查询,依赖于外部查询(相关查询)。案例3 | | `UNCACHEABLE SUBQUERY` | 子查询结果不能被缓存, 而且必须重写(分析)外部查询的每一行。案例4 | | `UNION` | UNION中的第二个或后面的SELECT语句。案例5 | | `UNION RESULT` | 结果集是通过union 而来的。案例5 | ### table 显示这一行的数据是关于哪表的 ### partitions 代表分区表中的命中情况,非分区表,该项为null ### type  type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > <font color='yellow'>range</font> ><font color="orange"> index</font> > <font color="red">ALL </font> **一般来说,得保证查询至少达到range级别,最好能达到ref。** 常见:system > const > eq_ref > ref > range > index > ALL **system**:表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计 **const**:针对主键或唯一索引的等值查询扫描,最多只返回一行数据。const 查询速度非常快, 因为它仅仅读取一次即可。例如:select * from t1 where t1.id=1 **eq_ref**:多表连接中使用primary key或者 unique key作为关联条件。例如:select * from t1,t2 where t1.id=t2.id **ref**:当搜索的索引列不是主键也不是唯一索引时才会发生。例如:select * from t1 where t1.content='';(content列创建了单值索引) **range**:只检索给定范围的行,一般就是在你的where语句中出现了between、<、>、!=等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。例如:select * from t1 where t1.id<10 **index**:该联接类型与ALL相同,只是只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(explain select id from t1) **all**:Full Table Scan,将遍历全表以找到匹配的行。(explain select * from t1) 其他: **index_merge**:在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的sql中。 **ref_or_null**:类似于ref,在需要null值的情况下。查询优化器会选择用ref_or_null连接查询。例如:explain select * from t1 where content = "" or content is null; **index_subquery**:利用索引来关联子查询,不再全表扫描。 **unique_subquery**:该联接类型类似于index_subquery。 子查询中的唯一索引。 ### possible_keys 显示当前查询可能用到的索引,一个或多个。查询涉及到的字段上若存在索引 则列出,**但不一定被查询实际使用** ### key 当前查询实际使用的索引。如果为NULL则没有使用索引。 **查询中若使用了覆盖索引,则该索引和查询的select字段重叠**  ### key_len 表示索引中使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。 key_len字段能够帮你检查是否充分的利用上了索引。  如何计算: 1. 先看索引上字段的类型 + 长度。比如 int=4;varchar(20) =20;char(20) =20 字符串:char(n)=n;varchar(n)=n 数值类型:tinyint=1;smallint=2;int=4;bigint=8 时间类型:date=3;timestamp=4;datetime=8 2. 如果是varchar或者char这种字符串字段,视字符集要乘不同的值。比如:utf-8要乘 3,GBK要乘2 3. varchar要额外加2个字节 4. 允许为NULL的字段额外加1个字节 索引字段最好不要为NULL,因为NULL让统计更加复杂,并且需要额外一个字节的存储空间。 备注:**key_len不包含order by/group by使用到的索引列。** ### ref 哪些列或常量被用于查找索引列上的值。显示索引的哪一列被使用了,如果可能的话,是一个常数。常见的有:const(常量),func,NULL,字段名 ### rows rows列显示MySQL认为它执行查询时必须检查的行数。**越少越好** ### filtered 通过查询条件获取的最终记录行数 占 通过type字段指明的搜索方式搜索出来的记录行数 的 百分比。 这个值越高越好:说明更多的数据是通过索引搜素出来的。 ### extra 不适合在其他列中显示但十分重要的额外信息 <font color="red">**Using filesort**</font>:文件排序,说明使用了非索引字段排序,此时mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。解决方案:对排序字段创建索引  <font color="red">**Using temporary**</font>:使了用临时表保存中间结果,常见于排序 order by 和分组查询 group by。group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。  **USING index**:利用索引进行了查询、排序或分组。例如:explain select * from t1 where content='xxx';(content创建了索引) **Using where**:表明使用了where过滤。explain select * from t1 where content>'';(content创建了索引) <font color="red">**using join buffer**</font>:使用了连接缓存,非主键关联  **impossible where**:where子句的值总是false。(EXPLAIN select * from t_emp where false;) ## 小结 **表的读取顺序:id**(趟数越少越好) 那些索引可以使用:possible_keys **数据读取操作的操作类型:type**(system > const > eq_ref > ref > range > index > ALL) **那些索引被实际使用:key** **那些索引列被实际使用:key_len** 表之间的引用:table **每张表有多少行被物理查询:rows**(越小越好) **额外的重要信息:extra** 避免:Using filesort(排序)、Using temporary(分组)、using join buffer(多表联查) ## explain的局限性 1. EXPLAIN不考虑各种Cache 2. EXPLAIN不能显示MySQL在执行查询时所作的优化工作 3. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况 4. 部分统计信息是估算的,并非精确值
admin
2025年2月22日 20:49
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Rancher
Jenkins
ADMIN-UI
VBEN-ADMIN-UI
RUST-FS
MinIO
mindoc
Markdown文件
PDF文档(打印)
分享
链接
类型
密码
更新密码