亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

blues001 · 更新于 2020-10-19

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. 實施優化措施。