6. 优化SQL步骤&explain执行计划

最新推荐文章于 2024-11-17 08:30:00 发布

cailyn_

最新推荐文章于 2024-11-17 08:30:00 发布

阅读量275

点赞数

分类专栏： mysql 文章标签： mysql explain执行计划

本文链接：https://blog.youkuaiyun.com/weixin_40408979/article/details/119205739

版权

mysql 专栏收录该内容

9 篇文章

订阅专栏

本文详细指导如何通过查看SQL执行频率、定位低效SQL、explain分析执行计划，以及show profile和trace深入剖析，提升数据库查询性能。涉及explain的各项指标和优化技巧，助你掌握MySQL查询优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

优化SQL步骤

一、优化SQL步骤

一、优化SQL步骤

1.1 查看SQL执行频率

通过show [session|global] status命令可以提供服务器状态信息。
show[session|global] status可以根据需要加上参数“session”或者“global”来显示session级（当前连接）的计结果和global级（自数据库上次启动至今）的统计结果。
如果不写，默认使用参数是“session”。
下面的命令显示了当前session中所有统计参数的值：

show status like 'com_______';

 show status like 'Innodb_rows_%';

Com_xxx表示每个xxx语句执行的次数，我们通常比较关心的是以下几个统计参数。

Com_***:这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** :这几个参数只是针对InnoDB存储引擎的，累加的算法也略有不同。

1.2 定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的SQL语句。

慢查询日志:通过慢查询日志定位那些执行效率较低的SQL语句，用–log-slow-queries[=ﬁle_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。具体可以查看本书第26章中日志管理的相关部分。
show processlist:慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4）db列，显示这个进程目前连接的是哪个数据库
5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6）time列，显示这个状态持续的时间，单位是秒
7）state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copyingtotmptable、sortingresult、sendingdata等状态才可以完成
8）info列，显示这个sql语句，是判断问题语句的一个重要依据

1.3 explain分析执行计划

通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
查询SQL语句的执行计划：

explain select * from tb_item where id = 1;

explain select * from tb_item where title = '阿尔卡特(OT-979)冰川白联通3G手机3';

1.3.1环境准备

CREATE TABLE `t_role`(
	`id`varchar(32) NOT NULL,
	`role_name`varchar(255) DEFAULT NULL,
	`role_code`varchar(255)DEFAULT NULL,
	`description`varchar(255)DEFAULT NULL,
	PRIMARY KEY(`id`),
	UNIQUE KEY`unique_role_name`(`role_name`)
)ENGINE=InnoDB DEFAULT CHARSET = utf8;



CREATE TABLE `t_user`(
	`id`varchar(32)NOT NULL,
	`username`varchar(45)NOT NULL,
	`password`varchar(96)NOT NULL,
	`name`varchar(45)NO TNULL,
	PRIMARY KEY(`id`),
	UNIQUE KEY`unique_user_username`(`username`)
)ENGINE=InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE`user_role`(
	`id`int(11)NOT NULL auto_increment,
	`user_id`varchar(32)DEFAULT NULL,
	`role_id`varchar(32)DEFAULT NULL,
	PRIMARY KEY(`id`),
	KEY`fk_ur_user_id`(`user_id`),
	KEY`fk_ur_role_id`(`role_id`),
	CONSTRAINT`fk_ur_role_id`FOREIGN KEY(`role_id`)REFERENCES`t_role`(`id`)ON
	DELETE NO ACTION ON UPDATE NO ACTION,
	CONSTRAINT`fk_ur_user_id`FOREIGN KEY(`user_id`)REFERENCES`t_user`(`id`)ON
	DELETE NO ACTION ON UPDATE NO ACTION
)ENGINE=InnoDB DEFAULT CHARSET = utf8;

1.3.2 explain之id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种：
1）id相同表示加载表的顺序是从上到下。

explain select * from t_role r,t_user u,user_role ur where r.id=ur.role_id and
u.id=ur.user_id;

2）id不同id值越大，优先级越高，越先被执行。

EXPLAIN SELECT*FROM t_role WHERE id=(SELECT role_id FROM user_role WHERE user_id
=(SELECT id FROM t_user WHERE username = 'stu1'))

3）id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_roler,(SELECT * FROM user_role ur WHERE ur.`user_id`=
'2')a WHERE r.id=a.role_id;

1.3.3 explain之select_type

表示SELECT的类型，常见的取值，如下表所示：

1.3.4 explain之table

展示这一行的数据是关于哪一张表的

1.3.5 explain之type

type显示的是访问类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏以此是：

NULL>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。

1.3.6 explain之key

possible_keys:显示可能应用在这张表的索引，一个或多个。
key：实际使用的索引，如果为NULL，则没有使用索引。
key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

1.3.7 explain之rows

扫描行的数量。

1.3.8 explain之extra

其他的额外的执行计划信息，在该列展示。

1.4 show proﬁle分析SQL

show proﬁles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_proﬁling参数，能够看到当前MySQL是否支持proﬁle：
默认proﬁling是关闭的，可以通过set语句在Session级别开启proﬁling：

set profiling = 1;//开启profiling开关；

通过proﬁle，我们能够更清楚地了解SQL执行的过程。首先，我们可以执行一系列的操作，如下图所示：

show databases;
use db01;
show tables;
select * from tb_item where id<5;
select count(*) from tb_item;

执行完上述命令之后，再执行show proﬁles指令，来查看SQL语句执行的耗时：

通过showproﬁle forqueryquery_id语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

TIP： Sendingdata状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sendingdata状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：

1.5 trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划,而不是选择B计划。
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace = "enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句：

select * from tb_item where id<4;

最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

select * from information_schema.optimizer_trace\G;