優化SQL語句的一般步驟

NO IMAGE

    當面對一個有SQL效能的資料庫時,我們可以從以下步驟來對資料庫進行分析。

1.通過show status命令瞭解各種SQL的執行頻率

    MySQL客戶端連線成功後,通過show [session | global] status命令可以提供伺服器狀態資訊。session為當前連線的統計結果;global為資料庫啟動至今的統計結果;預設為session。
    以下為常用的統計引數:
    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:慢查詢的次數。

2.定位執行效率較低的SQL語句

    通過以下兩種方式執行效率較低的SQL語句:
    1) 通過慢查詢日誌定位執行效率較低的SQL語句,用–log-slow-queries[=filename]選項啟動時,mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的日誌檔案。
    2)慢查詢日誌在查詢結束後才記錄,所以在應用反映執行效率出現問題的時候查詢慢查詢日誌並不能定位問題,可以使用show processlist命令檢視當前MySQL在進行的執行緒,包括執行緒的狀態、是否鎖表等,可以實時檢視SQL的執行情況,同時對一些鎖表操作進行優化。

3.通過explain分析低效SQL的執行計劃

    查詢到效率低的SQL語句後,可以通過explain或者desc命令獲取MySQL如何執行select語句的資訊,包括在select語句執行過程中表如何連線和連線的順序。
    以下是explain語句返回引數:
    select_type:表示select的型別,常見的取值有SIMPLE(簡單表,不使用表連線或子查詢)、PRIMARY(主查詢,即外層的查詢)、UNION(UNION中的第二個或者後面的查詢語句)、SUBQUERY(子查詢中的第一個select)等。
    table:輸出結果集的表。
    type:表示表的連線型別,效能由好到差的連線型別為system(表中僅有一行,即常量表)、const(單表中最多有一個匹配行,例如primary key或者unique index)、eq_ref(對於前面的每一行,在此表中只查詢一條記錄,簡單來說就是多表連線中使用primary key或unique index)、ref(與eq_ref類似,但是使用普通的索引)、ref_or_null(與ref類似,區別在於條件中包含對NULL的查詢)、index_merge(索引合併優化)、unique_subquery(in的後面是一個查詢主鍵欄位的子查詢)、index_subquery(與unique_subquery類似,區別是在in後面是查詢非唯一索引欄位的子查詢)、range(單表中範圍查詢)、index(對於前面的每一行,都通過查詢索引來得到資料)、all(對於前面的每一行,都通過掃描全表來得到資料)。
    possible_keys:表示查詢時,可能使用的索引。
    key:實際使用的索引
    key_len:索引欄位的長度
    rows:掃描行的數量
    Extra:執行情況的說明和描述

4.確定問題並採取相應的優化措施

    通過explain語句得到的結果,我們可以確認問題出現的原因,採取相應的措施,進行優化提高執行的效率。