前言
本文主要給大家分享了關于sql語句優化的一般步驟，分享出來供大家參考學習，下面話不多說了，來一起看看詳細的介紹吧。
一、通過 show status 命令了解各種 sql 的執行頻率
mysql 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務器狀態信息，也可以在操作系統上使用 mysqladmin extend-status 命令獲取這些消息。
show status 命令中間可以加入選項 session（默認） 或 global：
# Com_xxx 表示每個 xxx 語句執行的次數。mysql> show status like 'Com_%';
我們通常比較關心的是以下幾個統計參數：
上面這些參數對于所有存儲引擎的表操作都會進行累計。下面這幾個參數只是針對 innodb 的，累加的算法也略有不同：
通過以上幾個參數，可以很容易地了解當前數據庫的應用是以插入更新為主還是以查詢操作為主，以及各種類型的 sql 大致的執行比例是多少。對于更新操作的計數，是對執行次數的計數，不論提交還是回滾都會進行累加。
對于事務型的應用，通過 Com_commit 和 Com_rollback 可以了解事務提交和回滾的情況，對于回滾操作非常頻繁的數據庫，可能意味著應用編寫存在問題。
此外，以下幾個參數便于用戶了解數據庫的基本情況：
二、定義執行效率較低的 sql 語句
1. 通過慢查詢日志定位那些執行效率較低的 sql 語句，用 --log-slow-queries[=file_name] 選項啟動時，mysqld 寫一個包含所有執行時間超過 long_query_time 秒的 sql 語句的日志文件。
2. 慢查詢日志在查詢結束以后才記錄，所以在應用反映執行效率出現問題的時候慢查詢日志并不能定位問題，可以使用 show processlist 命令查看當前 mysql 在進行的線程，包括線程的狀態、是否鎖表等，可以實時的查看 sql 的執行情況，同時對一些鎖表操作進行優化。
三、通過 explain 分析低效 sql 的執行計劃
測試數據庫地址：https://downloads.mysql.com/docs/sakila-db.zip（本地下載）
統計某個 email 為租賃電影拷貝所支付的總金額，需要關聯客戶表 customer 和 付款表 payment ， 并且對付款金額 amount 字段做求和（sum） 操作，相應的執行計劃如下：
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: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)
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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 33.33 Extra: Using where1 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: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 filtered: 100.00 Extra: Using index1 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: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1350 filtered: 100.00 Extra: Using index condition1 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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 23 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.01 sec)
索引 idx_fk_customer_id 是非唯一索引，查詢條件為等值查詢條件 customer_id = 350, 所以掃描索引的類型為 ref。ref 還經常出現在 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: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 26 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)
5.type=eq_ref,類似 ref，區別就在使用的索引時唯一索引，對于每個索引的鍵值，表中只要一條記錄匹配；簡單的說，就是多表連接中使用 primary key 或者 unique index 作為關聯條件。
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: ALLpossible_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_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.b.film_id rows: 1 filtered: 100.00 Extra: Using where2 rows in set, 1 warning (0.03 sec)
6.type=const/system,單表中最多有一個匹配行，查起來非常迅速，所以這個匹配行中的其他列的值可以被優化器在當前查詢中當作常量來處理，例如，根據主鍵 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: constpossible_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 不用訪問表或者索引，直接就能夠得到結果：
mysql> explain select 1 from dual where 1 /G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used1 row in set, 1 warning (0.00 sec)
類型 type 還有其他值，如 ref_or_null (與 ref 類似，區別在于條件中包含對 null 的查詢)、index_merge(索引合并優化)、unique_subquery (in 的后面是一個查詢主鍵字段的子查詢)、index_subquery(與 unique_subquery 類似，區別在于 in 的后面是查詢非唯一索引字段的子查詢)等。
show warnings 命令
執行explain 后再執行 show warnings，可以看到sql 真正被執行之前優化器做了哪些 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: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL2 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 字段中能夠看到優化器自動去除了 1=1 恒成立的條件，也就是說優化器在改寫 sql 時會自動去掉恒成立的條件。
explain 命令也有對分區的支持.
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: 0MySQL [sakila]> explain select * from customer_part where customer_id=130/G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer_part partitions: p2 type: constpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warnings (0.00 sec)
可以看到 sql 訪問的分區是 p2。
四、通過 performance_schema 分析 sql 性能
舊版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已經不允許使用 profiles 了，推薦用
performance_schema 分析sql。
五、通過 trace 分析優化器如何選擇執行計劃。
mysql5.6 提供了對 sql 的跟蹤 trace，可以進一步了解為什么優化器選擇 A 執行計劃而不是 B 執行計劃，幫助我們更好的理解優化器的行為。
使用方式：首先打開 trace ，設置格式為 json，設置 trace 最大能夠使用的內存大小，避免解析過程中因為默認內存過小而不能夠完整顯示。
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)
接下來執行想做 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: 01 row in set (0.00 sec)六、 確定問題并采取相應的優化措施
經過以上步驟，基本就可以確認問題出現的原因。此時可以根據情況采取相應的措施，進行優化以提高執行的效率。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答
