sql語句優(yōu)化的一般步驟詳解

更新時間：2017年09月01日 11:00:02 作者：waterandair

網(wǎng)上關(guān)于SQL優(yōu)化的教程很多，但是比較雜亂，近日有空整理了一下，寫出來跟大家分享，下面這篇文章主要給大家分享介紹了關(guān)于sql語句優(yōu)化的一般步驟，需要的朋友可以參考借鑒，下面隨著小編來一起學習學習吧。

前言

本文主要給大家分享了關(guān)于sql語句優(yōu)化的一般步驟，分享出來供大家參考學習，下面話不多說了，來一起看看詳細的介紹吧。

一、通過 show status 命令了解各種 sql 的執(zhí)行頻率

mysql 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務器狀態(tài)信息，也可以在操作系統(tǒng)上使用 mysqladmin extend-status 命令獲取這些消息。

show status 命令中間可以加入選項 session（默認）或 global：

session （當前連接）
global （自數(shù)據(jù)上次啟動至今）

# Com_xxx 表示每個 xxx 語句執(zhí)行的次數(shù)。
mysql> show status like 'Com_%';

我們通常比較關(guān)心的是以下幾個統(tǒng)計參數(shù)：

Com_select : 執(zhí)行 select 操作的次數(shù)，一次查詢只累加 1。
Com_insert : 執(zhí)行 insert 操作的次數(shù)，對于批量插入的 insert 操作，只累加一次。
Com_update : 執(zhí)行 update 操作的次數(shù)。
Com_delete : 執(zhí)行 delete 操作的次數(shù)。

上面這些參數(shù)對于所有存儲引擎的表操作都會進行累計。下面這幾個參數(shù)只是針對 innodb 的，累加的算法也略有不同：

Innodb_rows_read : select 查詢返回的行數(shù)。
Innodb_rows_inserted : 執(zhí)行 insert 操作插入的行數(shù)。
Innodb_rows_updated : 執(zhí)行 update 操作更新的行數(shù)。
Innodb_rows_deleted : 執(zhí)行 delete 操作刪除的行數(shù)。

通過以上幾個參數(shù)，可以很容易地了解當前數(shù)據(jù)庫的應用是以插入更新為主還是以查詢操作為主，以及各種類型的 sql 大致的執(zhí)行比例是多少。對于更新操作的計數(shù)，是對執(zhí)行次數(shù)的計數(shù)，不論提交還是回滾都會進行累加。

對于事務型的應用，通過 Com_commit 和 Com_rollback 可以了解事務提交和回滾的情況，對于回滾操作非常頻繁的數(shù)據(jù)庫，可能意味著應用編寫存在問題。

此外，以下幾個參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況：

Connections ：試圖連接 mysql 服務器的次數(shù)。
Uptime ：服務器工作時間。
Slow_queries : 慢查詢次數(shù)。

二、定義執(zhí)行效率較低的 sql 語句

1. 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語句，用 --log-slow-queries[=file_name] 選項啟動時，mysqld 寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 sql 語句的日志文件。

2. 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才記錄，所以在應用反映執(zhí)行效率出現(xiàn)問題的時候慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}，可以使用 show processlist 命令查看當前 mysql 在進行的線程，包括線程的狀態(tài)、是否鎖表等，可以實時的查看 sql 的執(zhí)行情況，同時對一些鎖表操作進行優(yōu)化。

三、通過 explain 分析低效 sql 的執(zhí)行計劃

測試數(shù)據(jù)庫地址：https://downloads.mysql.com/docs/sakila-db.zip（本地下載）

統(tǒng)計某個 email 為租賃電影拷貝所支付的總金額，需要關(guān)聯(lián)客戶表 customer 和付款表 payment ，并且對付款金額 amount 字段做求和（sum）操作，相應的執(zhí)行計劃如下：

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

select_type: 表示 select 類型，常見的取值有：
     simple：簡單表，及不使用表連接或者子查詢
     primary：主查詢，即外層的查詢
     union：union 中的第二個或后面的查詢語句
     subquery：子查詢中的第一個 select
table ：輸出結(jié)果集的表
type ：表示 mysql 在表中找到所需行的方式，或者叫訪問類型，常見類型性能由差到最好依次是：all、index、range、ref、eq_ref、const，system、null：

1.type=ALL，全表掃描，mysql 遍歷全表來找到匹配的行：

mysql> explain select * from film where rating > 9 \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 33.33
 Extra: Using where
1 row in set, 1 warning (0.01 sec)

2.type=index, 索引全掃描，mysql 遍歷整個索引來查詢匹配的行

mysql> explain select title form film\G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: index
possible_keys: NULL
  key: idx_title
 key_len: 767
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

3.type=range,索引范圍掃描，常見于<、<=、>、>=、between等操作：

mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: range
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: NULL
 rows: 1350
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.07 sec)

4.type=ref, 使用非唯一索引掃描或唯一索引的前綴掃描，返回匹配某個單獨值的記錄行，例如：

mysql> explain select * from payment where customer_id = 350 \G 
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: const
 rows: 23
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.01 sec)

索引 idx_fk_customer_id 是非唯一索引，查詢條件為等值查詢條件 customer_id = 350, 所以掃描索引的類型為 ref。ref 還經(jīng)常出現(xiàn)在 join 操作中：

mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 599
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.b.customer_id
 rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5.type=eq_ref,類似 ref，區(qū)別就在使用的索引時唯一索引，對于每個索引的鍵值，表中只要一條記錄匹配；簡單的說，就是多表連接中使用 primary key 或者 unique index 作為關(guān)聯(lián)條件。

mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: sakila.b.film_id
 rows: 1
 filtered: 100.00
 Extra: Using where
2 rows in set, 1 warning (0.03 sec)

6.type=const/system,單表中最多有一個匹配行，查起來非常迅速，所以這個匹配行中的其他列的值可以被優(yōu)化器在當前查詢中當作常量來處理，例如，根據(jù)主鍵 primary key 或者唯一索引 unique index 進行查詢。

mysql> create table test_const (
 ->  test_id int,
 ->  test_context varchar(10),
 ->  primary key (`test_id`),
 -> );
 
insert into test_const values(1,'hello');

explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: test_const
 partitions: NULL
 type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 4
  ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 1 warning (0.00 sec)

7.type=null, mysql 不用訪問表或者索引，直接就能夠得到結(jié)果：

mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: NULL
 partitions: NULL
 type: NULL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
 Extra: No tables used
1 row in set, 1 warning (0.00 sec)

　　類型 type 還有其他值，如 ref_or_null (與 ref 類似，區(qū)別在于條件中包含對 null 的查詢)、index_merge(索引合并優(yōu)化)、unique_subquery (in 的后面是一個查詢主鍵字段的子查詢)、index_subquery(與 unique_subquery 類似，區(qū)別在于 in 的后面是查詢非唯一索引字段的子查詢)等。

possible_keys : 表示查詢時可能使用的索引。
key ：表示實際使用索引
key-len : 使用到索引字段的長度。
rows ：掃描行的數(shù)量
extra：執(zhí)行情況的說明和描述，包含不適合在其他列中顯示但是對執(zhí)行計劃非常重要的額外信息。

show warnings 命令

執(zhí)行explain 后再執(zhí)行 show warnings，可以看到sql 真正被執(zhí)行之前優(yōu)化器做了哪些 sql 改寫：

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

從 warning 的 message 字段中能夠看到優(yōu)化器自動去除了 1=1 恒成立的條件，也就是說優(yōu)化器在改寫 sql 時會自動去掉恒成立的條件。

explain 命令也有對分區(qū)的支持.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT '1',
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: customer_part
 partitions: p2
  type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: const
  rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warnings (0.00 sec)

可以看到 sql 訪問的分區(qū)是 p2。

四、通過 performance_schema 分析 sql 性能

舊版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已經(jīng)不允許使用 profiles 了，推薦用
performance_schema 分析sql。

五、通過 trace 分析優(yōu)化器如何選擇執(zhí)行計劃。

mysql5.6 提供了對 sql 的跟蹤 trace，可以進一步了解為什么優(yōu)化器選擇 A 執(zhí)行計劃而不是 B 執(zhí)行計劃，幫助我們更好的理解優(yōu)化器的行為。

使用方式：首先打開 trace ，設(shè)置格式為 json，設(shè)置 trace 最大能夠使用的內(nèi)存大小，避免解析過程中因為默認內(nèi)存過小而不能夠完整顯示。

MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

接下來執(zhí)行想做 trace 的 sql 語句，例如像了解租賃表 rental 中庫存編號 inventory_id 為 4466 的電影拷貝在出租日期 rental_date 為 2005-05-25 4:00:00 ~ 5:00:00 之間出租的記錄：

mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

六、確定問題并采取相應的優(yōu)化措施

經(jīng)過以上步驟，基本就可以確認問題出現(xiàn)的原因。此時可以根據(jù)情況采取相應的措施，進行優(yōu)化以提高執(zhí)行的效率。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章: