MySQL的Explain 及SQL优化的一般步骤

本文详细介绍了SQL查询的执行计划分析,包括type、rows、filtered、extra等关键指标的含义及其对查询效率的影响。讲解了如何通过explain分析SQL性能,以及关注点如全表扫描、索引使用、覆盖索引、索引下推等。同时,提到了慢查询日志的配置和使用,以及优化步骤,如分析慢查询、使用showprofile和trace等工具。通过对这些工具和概念的理解,可以有效提升SQL查询性能。
摘要由CSDN通过智能技术生成

Explain

使用 explain select * from tableA  查看SQL语句的执行计划时, 需要重点关注type、rows、filtered、extra。

Type

由上至下,效率越来越高

  • ALL::全表扫描 。从硬盘中读取

  • index::全表扫描。跟ALL的区别是: 使用索引进行遍历索引树读取

  • range ::使用索引范围扫描。就是针对一个有索引的字段,在指定范围中检索数据常用语<,<=,>=,between,in等操作

  • ref:: 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中。数据查询的时候如果命中的索引是二级索引不是唯一索引,测试查询速度也会很快,但是type是ref。另外如果是多字段的联合索引,那么根据最左匹配原则,从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。

  • eq_ref ::类似ref,区别在于使用的是唯一索引,使用主键的关联查询。在进行数据查询的过程中,如果SQL语句中在表连接情况下可以基于聚簇索引或者非null值的唯一索引记性数据扫描,那么此时type对应的值就会显示为eq_ref

  • const      如果type是const,说明在进行数据查询的时候,命中了primary key或唯一索引,此类数据查询速度非常快。

  • system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询

  • null MySQL不访问任何表或索引,直接返回结果

  • 虽然上至下,效率越来越高,但是根据cost模型,假设有两个索引idx1(a, b, c),idx2(a, c),SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1,那么是type为range,如果走idx2,那么type是ref;当需要扫描的行数,使用idx2大约是idx1的5倍以上时,会用idx1,否则会用idx2

Extra

  • Using index:表示相应的 select 操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错!如果同时出现 using where,意味着无法直接通过索引查找来查询到符合条件的数据。【不是使用select * ,而是使用select phone_number,就会用到覆盖索引。】

  • Using index condition:MySQL5.6之后新增的ICP,using index condtion就是使用了ICP(索引下推),在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。

  • Using where   查询时未找到可用的索引,进而通过where条件过滤获取所需数据,但要注意的是并不是所有带where语句的查询都会显示Using where。

  • Using temporary:使用了临时表保存中间结果,性能特别差,需要重点优化

  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。

possible_keys

表示哪些索引可以被Mysql的优化器进行选择,也就是索引候选者有哪些。

key

在possible_keys中实际选择的索引

key_len

表示索引的长度,和实际的字段属性以及是否为null都有关系。

rows

显示MySQL认为它执行查询时必须检查的行数。行数越少,效率越高!

filtered

这个是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下的记录满足条件的记录数量的比例。

一般的优化步骤

1、通过慢查日志等定位那些执行效率较低的SQL语句

查看是否开启慢查询:


show variables like "%slow%";

| slow_launch_time | 2 |  超过2秒定义为慢查询。
| slow_query_log | OFF |  慢查询关闭状态。
| slow_query_log_file | /data/mysql/var/db-Test2-slow.log | 慢查询日志的文件。



开启慢查询:

set global slow_query_log=on;
[mysqld]
log-slow-queries = /data/mysql/var/db-Test2-slow.log #日志目录。 
long_query_time = 1   #记录下查询时间查过1秒。 
log-queries-not-using-indexes  #表示记录下没有使用索引的查询。

2、explain 分析SQL的执行计划

3、show profile 分析

QL执行的线程的状态及消耗的时间。

默认是关闭的,开启语句“set profiling = 1;”

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

4、trace

trace分析优化器如何选择执行计划,通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值