有關sql語句優化的教程

326

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

前言

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

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

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

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

  • session （當前連接）

  • global （自數據上次啟動至今）

#?Com_xxx?表示每個?xxx?語句執行的次數。  mysql>?show?status?like?'Com_%';

我們通常比較關心的是以下幾個統計參數：

  • Com_select : 執行 select 操作的次數，一次查詢只累加 1。

  • Com_insert : 執行 insert 操作的次數，對于批量插入的 insert 操作，只累加一次。

  • Com_update : 執行 update 操作的次數。

  • Com_delete : 執行 delete 操作的次數。

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

  • Innodb_rows_read : select 查詢返回的行數。

  • Innodb_rows_inserted : 執行 insert 操作插入的行數。

  • Innodb_rows_updated : 執行 update 操作更新的行數。

  • Innodb_rows_deleted : 執行 delete 操作刪除的行數。

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

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

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

  • Connections ： 試圖連接 mysql 服務器的次數。

  • Uptime ： 服務器工作時間。

  • Slow_queries : 慢查詢次數。

二、定義執行效率較低的 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:?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 ： 輸出結果集的表

  • 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?filmG    ***************************?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&gt;?explain?select?*?from?payment?where?customer_id?&gt;=?300?and?customer_id?<p>4.type=ref, 使用非唯一索引掃描或唯一索引的前綴掃描，返回匹配某個單獨值的記錄行，例如：<br></p><p class="jb51code"><br></p><pre class="brush:sql;">mysql&gt;?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 還經常出現在 join 操作中：

mysql&gt;?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，區別就在使用的索引時唯一索引，對于每個索引的鍵值，表中只要一條記錄匹配；簡單的說，就是多表連接中使用 primary key 或者 unique index 作為關聯條件。

mysql&gt;?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,單表中最多有一個匹配行，查起來非常迅速，所以這個匹配行中的其他列的值可以被優化器在當前查詢中當作常量來處理，例如，根據主鍵 primary key 或者唯一索引 unique index 進行查詢。

mysql&gt;?create?table?test_const?(  ?-&gt;??test_id?int,  ?-&gt;??test_context?varchar(10),  ?-&gt;??primary?key?(`test_id`),  ?-&gt;?);  ?  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 不用訪問表或者索引，直接就能夠得到結果：

mysql&gt;?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 類似，區別在于條件中包含對 null 的查詢)、index_merge(索引合并優化)、unique_subquery (in 的后面是一個查詢主鍵字段的子查詢)、index_subquery(與 unique_subquery 類似，區別在于 in 的后面是查詢非唯一索引字段的子查詢)等。

  • possible_keys : 表示查詢時可能使用的索引。

  • key ：表示實際使用索引

  • key-len : 使用到索引字段的長度。

  • rows ： 掃描行的數量

  • extra：執行情況的說明和描述，包含不適合在其他列中顯示但是對執行計劃非常重要的額外信息。

show warnings 命令

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

MySQL?[sakila]&gt;?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]&gt;?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]&gt;?CREATE?TABLE?`customer_part`?(  ?-&gt;?`customer_id`?smallint(5)?unsigned?NOT?NULL?AUTO_INCREMENT,  ?-&gt;?`store_id`?tinyint(3)?unsigned?NOT?NULL,  ?-&gt;?`first_name`?varchar(45)?NOT?NULL,  ?-&gt;?`last_name`?varchar(45)?NOT?NULL,  ?-&gt;?`email`?varchar(50)?DEFAULT?NULL,  ?-&gt;?`address_id`?smallint(5)?unsigned?NOT?NULL,  ?-&gt;?`active`?tinyint(1)?NOT?NULL?DEFAULT?'1',  ?-&gt;?`create_date`?datetime?NOT?NULL,  ?-&gt;?`last_update`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,  ?-&gt;?PRIMARY?KEY?(`customer_id`)  ?-&gt;?  ?-&gt;?)?partition?by?hash?(customer_id)?partitions?8;  Query?OK,?0?rows?affected?(0.06?sec)    MySQL?[sakila]&gt;?insert?into?customer_part?select?*?from?customer;  Query?OK,?599?rows?affected?(0.06?sec)  Records:?599?Duplicates:?0?Warnings:?0    MySQL?[sakila]&gt;?explain?select?*?from?customer_part?where?customer_id=130G  ***************************?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 訪問的分區是 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]&gt;?set?optimizer_trace="enabled=on",end_markers_in_json=on;  Query?OK,?0?rows?affected?(0.00?sec)    MySQL?[sakila]&gt;?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&gt;?select?rental_id?from?rental?where?1=1?and?rental_date?&gt;=?'2005-05-25?04:00:00'?and?rental_date??select?*?from?information_schema.optimizer_traceG  ***************************?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)

六、 確定問題并采取相應的優化措施

經過以上步驟，基本就可以確認問題出現的原因。此時可以根據情況采取相應的措施，進行優化以提高執行的效率。

? 版權聲明
THE END
數據庫
# 操作系統
喜歡就支持一下吧
點贊6 分享