MySQL优化SQL语句的步骤_vlambda技术博客

vlambda
2020-09-17

MySQL优化SQL语句的步骤

我们在执行一条SQL语句的时候，如果我们想要知道这条SQL语句查询了哪些表，有没有使用索引，获取数据的时候遍历了多少行数据，我们可以通过EXPLAIN命令来查看这些执行信息，这些执行信息统称为执行计划。

1.使用 EXPLAIN 查看执行计划

使用方式 explain sql。

下面对图中的每一个字段进行说明。

id：每个执行计划都有一个id，如果是联合查询，这里还将会有多个id。
select_type：表示 select 的查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起了别名，这里显示的就是别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，这个是SQL优化中一个很重要的指标，结果值从好到差依次为：system>const>eq_ref>ref>range>index>ALL。

system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据
eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一主键索引作为关联条件。
ref：非唯一索引扫描或者唯一索引的最左匹配原则扫描。
range：索引范围扫描，比如：>，<，between等操作。
index：索引全表扫描，此时遍历整个索引树。
ALL：表示全表扫描，需要遍历全表找到需要的行。

possible_keys：可能使用到的索引。
key：实际使用的索引。
ref：关联id信息。
rows：查找到记录扫描的行数。
filtered：查找所需要的记录占总扫描数的比例。

Extra：额外信息。

Extra通常会包含以下值：

示例表结构：

CREATE TABLE `user` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_unique_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

name字段创建了一个普通索引，age没有创建索引。

Using where：表示where条件中有非索引字段作为条件查询。
Using index：表示select语句使用了覆盖索引，即在索引树中就可以找到需要查询的数据而不需要回表。
Using where,Using index：表示查找的列被索引覆盖，且where条件字段有索引，且为模糊查询。
null：表示查询的列没有被索引覆盖，如果有where条件的话，则所有的where条件必须有索引，且非模糊查询。
Using index condition：表示查询列没有被索引覆盖，如果有查询条件where，则查询条件字段有索引，且为模糊查询。
Using temporary：表示使用了临时表存储数据，比如查询语句中使用了group by，distinct等。
Using filesort：表示使用了文件做排序。

2.使用Show Profile 分析 SQL 执行性能

Profile可以分析执行线程的状态和时间，还支持进一步选择ALL，CPU，MEMORY，BLOCK IO，CONTEXT SWITCHES等类型来查询SQL语句在不同系统资源上所消耗的时间。相关命令如下：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type参数：
    ALL：显示所有开销信息
    BLOCK IO：阻塞的输入输出次数
    CONTEXT SWITCHES：上下文切换相关开销信息
    CPU：显示CPU的相关开销信息
    IPC：接收和发送消息的相关开销信息
    MEMORY ：显示内存相关的开销，目前无用
    PAGE FAULTS ：显示页面错误相关开销信息
    SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数)
    SWAPS：显示swap交换次数的相关开销信息

通过 select @@have_profiling 查询数据库是否支持Show Profile功能（MySQL在5.0.37版本之后才支持该功能）：

MySQL优化SQL语句的步骤

通过 select @@profiling 查询是否开启了该功能：

MySQL优化SQL语句的步骤

如果没有开启该功能可以设置profiling的值为1开启session级别的profiling：

set profiling = 1;

Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。

set profiling_history_size = 20;
show variables like 'profiling_history_size';

通过show profiles命令可以拿到我们刚刚执行的SQL语句的Query_ID，我们可以再通过 show profile for query Query_ID语句就能查到对应的SQL语句在执行过程中线程的每个状态所消耗的时间了。

starting：开始
checking permissions：校验权限
Opening tables：打开表
init：初始化
System lock：系统锁定
optimizing：优化
statistics：统计
preparing：准备
executing：执行
Sending data：读取数据并返回给客户端
end：结束
query end：查询结束
closing tables：关闭表
freeing items：释放资源
cleaning up：清理

结束！

vlambda博客
学习文章列表