第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

blues001 · 更新于 2020-10-19

SQL 語句優(yōu)化

MySQL 的優(yōu)化主要指 SQL 語句的優(yōu)化和 MySQL Server 的優(yōu)化，相對來說，SQL 優(yōu)化相對更為重要，也更考驗功力。本小節(jié)將講解 SQL 語句優(yōu)化的一般思路，以及相應(yīng)方法。

1. SQL優(yōu)化的一般步驟

當(dāng)碰到一個存在性能問題的 MySQL 數(shù)據(jù)庫時，一般按照如下步驟進行分析解決：

  1. 定位問題 SQL；
  2. 分析 SQL 執(zhí)行計劃；
  3. 分析 SQL Profile；
  4. 實施優(yōu)化措施。

2. 定位問題SQL

定位 MySQL 的問題 SQL，主要有兩種方法，查看當(dāng)前線程（show processlist）和慢日志。一般來說，當(dāng)前發(fā)生的問題用到 show processlit，事后分析用到慢日志。

2.1 查看當(dāng)前線程

通過 show processlist 命令查看當(dāng)前正在執(zhí)行的sql語句，包括執(zhí)行狀態(tài)，是否鎖表，執(zhí)行時長等。

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 語句比較復(fù)雜，而且執(zhí)行量較大，通過 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，慢日志有一個閾值參數(shù) long_query_time，單位是秒，比如該參數(shù)設(shè)置為 1，那么執(zhí)行時長超過 1 秒的 SQL 都會被記錄到慢日志文件：

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

3. 分析 SQL 執(zhí)行計劃

找到問題 SQL 后，通過 explain 命令查看執(zhí)行計劃：

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，表示全表掃描，需要遍歷全表所有的數(shù)據(jù)；
  • type 等于 index，表示索引全掃描，需要遍歷整個索引來查找需要的數(shù)據(jù)；
  • type 等于 range，表示索引范圍掃描，掃描索引部分?jǐn)?shù)據(jù)即可查找需要的數(shù)據(jù)，常見操作有大于、小于、between；
  • type 等于 ref，使用唯一或非唯一索引的前綴掃描，返回查找到的單獨值；
  • type 等于 eq_ref，使用唯一索引，且僅有一條記錄匹配；
  • type 等于 const，表中僅有一行數(shù)據(jù)是匹配的。

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 執(zhí)行過程中的步驟和相應(yīng)消耗時間，從執(zhí)行結(jié)果可以看到，Sending data 這個狀態(tài)是耗時最長的。

5. 實施優(yōu)化措施

我們找到問題 SQL，并分析原因后，就得采取相應(yīng)措施進行優(yōu)化，以提高 SQL 語句的執(zhí)行效率。

在分析 SQL 執(zhí)行計劃這一小節(jié)的例子中，我們可以看到執(zhí)行計劃是 type 等于 ALL，表示需要對表customer 進行全表掃描才能找到相應(yīng)數(shù)據(jù)，這時我們要對字段 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)

從執(zhí)行計劃，可以看出，掃描行數(shù)從20965行減少至10行，查找效率可以大大提升。

6. 小結(jié)

本小節(jié)主要介紹了 SQL 語句優(yōu)化的一般思路以及相應(yīng)方法。

請記住以下優(yōu)化 SQL 的步驟和方法，熟練掌握后，在一定程度上可以提高工作效率。

  1. 定位問題 SQL；
  2. 分析 SQL 執(zhí)行計劃；
  3. 分析 SQL Profile；
  4. 實施優(yōu)化措施。