sql优化的一般步骤

发布时间：2023-09-07 12:41:56 所属栏目：MySql教程来源：

导读：当碰到一个存在性能问题的 MysqL 数据库时，一般按照如下步骤进行分析解决：

定位问题 sql；
分析 sql 执行计划；
分析 sql Profile；

1. 定位问题sql
定位 MysqL 的问题 sql，主要有两种方法，查看当前线程

当碰到一个存在性能问题的 MysqL 数据库时，一般按照如下步骤进行分析解决：

定位问题 sql；
分析 sql 执行计划；
分析 sql Profile；

1. 定位问题sql
定位 MysqL 的问题 sql，主要有两种方法，查看当前线程（show processlist）和慢日志。一般来说，当前发生的问题用到 show processlit，事后分析用到慢日志。

1.1 查看当前线程
通过 show processlist 命令查看当前正在执行的sql语句，包括执行状态，是否锁表，执行时长等。

MysqL> show processlist\G
*************************** . row ***************************
Id:
User: root
Host: localhost
: tempdb
Command: Query
Time:
State: starting
Info: show processlist
*************************** . row ***************************
Id:
User: root
Host: localhost
: tempdb
Command: Query
Time:
State: starting
Info: select * from customer where balance=;
rows in set ( sec)
有时 sql 语句比较复杂，而且执行量较大，通过 show processlist 来查看不太方便，这时可以通过表information_schema.processlist 进行查看，还可以自定义查询方式。

1.2 慢日志
通过分析慢日志定位存储性能问题的 sql，慢日志有一个阈值参数 long_query_time，单位是秒，比如该参数设置为 1，那么执行时长超过 1 秒的 sql 都会被记录到慢日志文件：

想要快速分析慢日志的 sql，建议使用 percona 公司的慢日志分析工具 pt-query-digest。

2. 分析 sql 执行计划
找到问题 sql 后，通过 explain 命令查看执行计划：

MysqL> explain select * from customer where balance=\G
*************************** . row ***************************
id:
select_type: SIMPLE
table: customer
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows:
Extra: Using where
row in set, warning ( sec)
其中 select_type 表示 select 类型，一般值为 simple、primary、union、subquery。type 表示访问类型，常见值有（性能由差到好）：ALL、index、range、ref、eq_ref、const：

type 等于 ALL，表示全表扫描，需要遍历全表所有的数据；
type 等于 index，表示索引全扫描，需要遍历整个索引来查找需要的数据；
type 等于 range，表示索引范围扫描，扫描索引部分数据即可查找需要的数据，常见操作有大于、小于、between；
type 等于 ref，使用唯一或非唯一索引的前缀扫描，返回查找到的单独值；
type 等于 eq_ref，使用唯一索引，且仅有一条记录匹配；
type 等于 const，表中仅有一行数据是匹配的。

3. 分析 sql Profile
想要进一步分析 sql，可以通过 show profiles 命令：

MysqL> select * from customer where balance=;
MysqL> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------+
| | | select @@profiling |
| | | SELECT DATABASE() |
| | | select * from customer where balance= |
+----------+------------+-----------------------------------------+
rows in set, warning ( sec)
MysqL> show profile for query ;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | |
| checking permissions | |
| opening tables | |
| init | |
| System lock | |
| optimizing | |
| statistics | |
| preparing | |
| executing | |
| Sending data | |
| end | |
| query end | |
| closing tables | |
| freeing items | |
| cleaning up | |
+----------------------+----------+
rows in set, warning ( sec)
show profile for query 可以看出这条 sql 执行过程中的步骤和相应消耗时间，从执行结果可以看到，Sending data 这个状态是耗时最长的。

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!