mysql的优化步骤_MySQL优化SQL语句的步骤

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

1.使用 EXPLAIN 查看执行计划

使用方式 explain sql。

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

1. id：每个执行计划都有一个id，如果是联合查询，这里还将会有多个id。

2. select_type：表示 select 的查询类型，常见的有 SIMPLE(普通查询，即没有联合查询、子查询)、PRIMARY(主查询)、UNION(UNION 中后面的查询)、SUBQUERY(子查询)等。

3. table：当前执行计划查询的表，如果给表起了别名，这里显示的就是别名信息。

4. partitions：访问的分区表信息。

5. type：表示从表中查询到行所执行的方式，这个是SQL优化中一个很重要的指标，结果值从好到差依次为：system>const>eq_ref>ref>range>index>ALL。

system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据

eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一主键索引作为关联条件。

ref：非唯一索引扫描或者唯一索引的最左匹配原则扫描。

range：索引范围扫描，比如：>，

index：索引全表扫描，此时遍历整个索引树。

ALL：表示全表扫描，需要遍历全表找到需要的行。

6. possible_keys：可能使用到的索引。

7. key：实际使用的索引。

8. ref：关联id信息。

9. rows：查找到记录扫描的行数。

10. filtered：查找所需要的记录占总扫描数的比例。

11. 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版本之后才支持该功能)：

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

如果没有开启该功能可以设置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：清理结束！

感谢您的浏览！