SQL 语句优化

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

1. SQL优化的一般步骤

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

  1. 定位问题 SQL;
  2. 分析 SQL 执行计划;
  3. 分析 SQL Profile;
  4. 实施优化措施。

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行,查找效率可以大大提升。

6. 小结

本小节主要介绍了 SQL 语句优化的一般思路以及相应方法。

请记住以下优化 SQL 的步骤和方法,熟练掌握后,在一定程度上可以提高工作效率。

  1. 定位问题 SQL;
  2. 分析 SQL 执行计划;
  3. 分析 SQL Profile;
  4. 实施优化措施。