码云 MySQL教程 2024-08-12

61. SQL 语句优化

文章目录隐藏

1. SQL 优化的一般步骤
2. 定位问题 SQL

2.1 查看当前线程
2.2 慢日志

3. 分析 SQL 执行计划
4. 分析 SQL Profile
5. 实施优化措施
6. 小结

MySQL 的优化主要指 SQL 语句的优化和 MySQL Server 的优化，相对来说，SQL 优化相对更为重要，也更考验功力。本小节将讲解 SQL 语句优化的一般思路，以及相应方法。

1. SQL 优化的一般步骤

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

定位问题 SQL；
分析 SQL 执行计划；
分析 SQL Profile；
实施优化措施。

2. 定位问题 SQL

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

2.1 查看当前线程

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

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 5866557
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 5866711
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 1
  State: starting
   Info: select * from customer where balance=10;
2 rows in set (0.00 sec)

有时 SQL 语句比较复杂，而且执行量较大，通过 show processlist 来查看不太方便，这时可以通过表 information_schema.processlist 进行查看，还可以自定义查询方式。

mysql> select * from information_schema.processlist order by info desc\G
*************************** 1. row ***************************
     ID: 5866557
   USER: root
   HOST: localhost
     DB: tempdb
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from information_schema.processlist order by info desc
*************************** 2. row ***************************
     ID: 5866711
   USER: root
   HOST: localhost
     DB: tempdb
COMMAND: Sleep
   TIME: 261
  STATE: 
   INFO: NULL
2 rows in set (0.00 sec)

2.2 慢日志

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

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

3. 分析 SQL 执行计划

找到问题 SQL 后，通过 explain 命令查看执行计划：

mysql> explain select * from customer where balance=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20965
        Extra: Using where
1 row in set, 1 warning (0.00 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，表中仅有一行数据是匹配的。

4. 分析 SQL Profile

想要进一步分析 SQL，可以通过show profiles命令：

mysql> select * from customer where balance=10;

mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration   | Query                                   |
+----------+------------+-----------------------------------------+
|        1 | 0.00015800 | select @@profiling                      |
|        2 | 0.00017150 | SELECT DATABASE()                       |
|        3 | 0.00512225 | select * from customer where balance=10 |
+----------+------------+-----------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000083 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000032 |
| init                 | 0.000042 |
| System lock          | 0.000010 |
| optimizing           | 0.000010 |
| statistics           | 0.000017 |
| preparing            | 0.000013 |
| executing            | 0.000002 |
| Sending data         | 0.003163 |
| end                  | 0.000003 |
| query end            | 0.000007 |
| closing tables       | 0.000007 |
| freeing items        | 0.000105 |
| cleaning up          | 0.000015 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

show profile for query可以看出这条 SQL 执行过程中的步骤和相应消耗时间，从执行结果可以看到，Sending data 这个状态是耗时最长的。

5. 实施优化措施

我们找到问题 SQL，并分析原因后，就得采取相应措施进行优化，以提高 SQL 语句的执行效率。

在分析 SQL 执行计划这一小节的例子中，我们可以看到执行计划是 type 等于 ALL，表示需要对表 customer 进行全表扫描才能找到相应数据，这时我们要对字段 balance 增加索引。

mysql> alter table customer add index idx_balance(balance);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from customer where balance=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_balance
          key: idx_balance
      key_len: 6
          ref: const
         rows: 10
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

从执行计划，可以看出，扫描行数从 20965 行减少至 10 行，查找效率可以大大提升。