42 張圖帶你擼完 MySQL 優化

程式設計師cxuan發表於2021-07-26
MySql優化

Hey guys，這裡是程式設計師cxuan，歡迎你閱讀我最新一期的文章，這篇文章是 MySQL 調優的彙總版，我加了一下日常開發過程中的調優經驗，希望對各位小夥伴們有所幫助。下面開始正文。

一般傳統網際網路公司很少接觸到 SQL 優化問題，其原因是資料量小，大部分廠商的資料庫效能能夠滿足日常的業務需求，所以不需要進行 SQL 優化，但是隨著應用程式的不斷變大，資料量的激增，資料庫自身的效能跟不上了，此時就需要從 SQL 自身角度來進行優化，這也是我們這篇文章所討論的。

SQL 優化步驟

當面對一個需要優化的 SQL 時，我們有哪幾種排查思路呢？

通過 show status 命令瞭解 SQL 執行次數

首先，我們可以使用 show status 命令檢視伺服器狀態資訊。show status 命令會顯示每個伺服器變數 variable_name 和 value，狀態變數是隻讀的。如果使用 SQL 命令，可以使用 like 或者 where 條件來限制結果。like 可以對變數名做標準模式匹配。

image-20210725220256076

圖我沒有截全，下面還有很多變數，讀者可以自己嘗試一下。也可以在作業系統上使用 mysqladmin extended-status 命令來獲取這些訊息。

但是我執行 mysqladmin extended-status 後，出現這個錯誤。

image-20210725220304607

應該是我沒有輸入密碼的原因，使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 後，問題解決。

這裡需要注意一下 show status 命令中可以新增統計結果的級別，這個級別有兩個

  • session 級： 預設當前連結的統計結果
  • global 級：自資料庫上次啟動到現在的統計結果

如果不指定統計結果級別的話，預設使用 session 級別。

對於 show status 查詢出來的統計結果，有兩類引數需要注意下，一類是以 Com_ 為開頭的引數，一類是以 Innodb_ 為開頭的引數。

下面是 Com_ 為開頭的引數，引數很多，我同樣沒有截全。

image-20210725220310637

Com_xxx 表示的是每個 xxx 語句執行的次數，我們通常關心的是 select 、insert 、update、delete 語句的執行次數，即

  • 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 操作刪除的行數。

通過上面這些引數執行結果的統計，我們能夠大致瞭解到當前資料庫是以更新（包括插入、刪除）為主還是查詢為主。

除此之外，還有一些其他引數用於瞭解資料庫的基本情況。

  • Connections：查詢 MySQL 資料庫的連線次數，這個次數是不管連線是否成功都算上。
  • Uptime：伺服器的工作時間。
  • Slow_queries：滿查詢次數。
  • Threads_connected：檢視當前開啟的連線的數量。

下面這個部落格彙總了幾乎所有 show status 的引數，可以當作參考手冊。

https://blog.csdn.net/ayay_870621/article/details/88633092

定位執行效率較低的 SQL

定位執行效率比較慢的 SQL 語句，一般有兩種方式

  • 可以通過慢查詢日誌來定位哪些執行效率較低的 SQL 語句。

MySQL 中提供了一個慢查詢的日誌記錄功能，可以把查詢 SQL 語句時間大於多少秒的語句寫入慢查詢日誌，日常維護中可以通過慢查詢日誌的記錄資訊快速準確地判斷問題所在。用 --log-slow-queries 選項啟動時，mysqld 會寫一個包含所有執行時間超過 long_query_time 秒的 SQL 語句的日誌檔案，通過檢視這個日誌檔案定位效率較低的 SQL 。

比如我們可以在 my.cnf 中新增如下程式碼，然後退出重啟 MySQL。

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

通常我們設定最長的查詢時間是 2 秒，表示查詢時間超過 2 秒就記錄了，通常情況下 2 秒就夠了，然而對於很多 WEB 應用來說，2 秒時間還是比較長的。

也可以通過命令來開啟：

我們先查詢 MySQL 慢查詢日誌是否開啟

show variables like "%slow%";

image-20210725220321492

啟用慢查詢日誌

set global slow_query_log='ON';

image-20210725220329215

然後再次查詢慢查詢是否開啟

image-20210725220338780

如圖所示，我們已經開啟了慢查詢日誌。

慢查詢日誌會在查詢結束以後才記錄，所以在應用反應執行效率出現問題的時候慢查詢日誌並不能定位問題，此時應該使用 show processlist 命令檢視當前 MySQL 正在進行的執行緒。包括執行緒的狀態、是否鎖表等，可以實時的檢視 SQL 執行情況。同樣，使用mysqladmin processlist語句也能得到此資訊。

image-20210725220348702

下面就來解釋一下各個欄位對應的概念

  • Id ：Id 就是一個標示，在我們使用 kill 命令殺死程式的時候很有用，比如 kill 程式號。
  • User：顯示當前的使用者，如果不是 root，這個命令就只顯示你許可權範圍內的 SQL 語句。
  • Host：顯示 IP ，用於追蹤問題
  • Db：顯示這個程式目前連線的是哪個資料庫，為 null 是還沒有 select 資料庫。
  • Command：顯示當前連線鎖執行的命令，一般有三種：查詢 query，休眠 sleep，連線 connect。
  • Time：這個狀態持續的時間，單位是秒
  • State：顯示當前 SQL 語句的狀態，非常重要，下面會具體解釋。
  • Info：顯示這個 SQL 語句。

State 列非常重要，關於這個列的內容比較多，讀者可以參考一下這篇文章

https://blog.csdn.net/weixin_34357436/article/details/91768402

這裡面涉及執行緒的狀態、是否鎖表等選項，可以實時的檢視 SQL 的執行情況，同時對一些鎖表進行優化。

通過 EXPLAIN 命令分析 SQL 的執行計劃

通過以上步驟查詢到效率低的 SQL 語句後，可以通過 EXPLAIN 或者 DESC 命令獲取 MySQL 如何執行 SELECT 語句的資訊，包括在 SELECT 語句執行過程中表如何連線和連線的順序。

比如我們使用下面這條 SQL 語句來分析一下執行計劃

explain select * from test1;

image-20210725220359537

上表中涉及內容如下

  • select_type：表示常見的 SELECT 型別，常見的有 SIMPLE，SIMPLE 表示的是簡單的 SQL 語句，不包括 UNION 或者子查詢操作，比如下面這段就是 SIMPLE 型別。

image-20210725220950577

PRIMARY ，查詢中最外層的 SELECT（如兩表做 UNION 或者存在子查詢的外層的表操作為 PRIMARY，內層的操作為 UNION），比如下面這段子查詢。

image-20210725221000038

UNION，在 UNION 操作中，查詢中處於內層的 SELECT（內層的 SELECT 語句與外層的 SELECT 語句沒有依賴關係時）。

SUBQUERY：子查詢中首個SELECT（如果有多個子查詢存在），如我們上面的查詢語句，子查詢第一個是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。

  • table ，這個選項表示輸出結果集的表。

  • type，這個選項表示表的連線型別，這個選項很有深入研究的價值，因為很多 SQL 的調優都是圍繞 type 來講的，但是這篇文章我們主要圍繞優化方式來展開的，type 這個欄位我們暫時作為了解，這篇文章不過多深入。

    type 這個欄位會牽扯到連線的效能，它的不同型別的效能由好到差分別是

    system ：表中僅有一條資料時，該表的查詢就像查詢常量表一樣。

    const ：當表中只有一條記錄匹配時，比如使用了表主鍵（primary key）或者表唯一索引（unique index）進行查詢。

    eq-ref ：表示多表連線時使用表主鍵或者表唯一索引，比如

    select A.text, B.text where A.ID = B.ID

    這個查詢語句，對於 A 表中的每一個 ID 行，B 表中都只能有唯一的 B.Id 來進行匹配時。

    ref ：這個型別不如上面的 eq-ref 快，因為它表示的是因為對於表 A 中掃描的每一行，表 C 中有幾個可能的行，C.ID 不是唯一的。

    ref_or_null ：與 ref 類似，只不過這個選項包含對 NULL 的查詢。

    index_merge ：查詢語句使用了兩個以上的索引，比如經常在有 and 和 or 關鍵字出現的場景，但是在由於讀取索引過多導致其效能有可能還不如 range（後面說）。

    unique_subquery ：這個選項經常用在 in 關鍵字後面，子查詢帶有 where 關鍵字的子查詢中，用 sql 來表示就是這樣

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

    range ：索引範圍查詢，常見於使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等運算子的查詢中。

    index ：索引全表掃描，把索引從頭到尾掃一遍。

    all ： 這個我們接觸的最多了，就是全表查詢，select * from xxx ，效能最差。

上面就是 type 內容的大致解釋，關於 type 我們經常會在 SQL 調優的環節使用 explain 分析其型別，然後改進查詢方式，越靠近 system 其查詢效率越高，越靠近 all 其查詢效率越低。

image-20210725221018222

  • possible_keys ：表示查詢時，可能使用的索引。
  • key ：表示實際使用的索引。
  • key_len ：索引欄位的長度。
  • rows ：掃描行的數量。
  • filtered ：通過查詢條件查詢出來的 SQL 數量佔用總行數的比例。
  • extra ：執行情況的描述。

通過上面的分析，我們可以大致確定 SQL 效率低的原因，一種非常有效的提升 SQL 查詢效率的方式就是使用索引，接下來我會講解一下如何使用索引提高查詢效率。

索引

索引是資料庫優化中最常用也是最重要的手段，通過使用不同的索引可以解決大多數 SQL 效能問題，也是面試經常會問到的優化方式，圍繞著索引，面試官能讓你造出火箭來，所以總結一點就是索引非常非常重！要！不只是使用，你還要懂其原！理！

索引介紹

索引的目的就是用於快速查詢某一列的資料，對相關資料列使用索引能夠大大提高查詢操作的效能。不使用索引，MySQL 必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大查詢資料所花費的時間就越多。如果表中查詢的列有索引，MySQL 能夠快速到達一個位置去搜尋資料檔案，而不必檢視所有資料，那麼將會節省很大一部分時間。

索引分類

先來了解一下索引都有哪些分類。

  • 全域性索引(FULLTEXT)：全域性索引，目前只有 MyISAM 引擎支援全域性索引，它的出現是為了解決針對文字的模糊查詢效率較低的問題，並且只限於 CHAR、VARCHAR 和 TEXT 列。
  • 雜湊索引(HASH)：雜湊索引是 MySQL 中用到的唯一 key-value 鍵值對的資料結構，很適合作為索引。HASH 索引具有一次定位的好處，不需要像樹那樣逐個節點查詢，但是這種查詢適合應用於查詢單個鍵的情況，對於範圍查詢，HASH 索引的效能就會很低。預設情況下，MEMORY 儲存引擎使用 HASH 索引，但也支援 BTREE 索引。
  • B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹，它有很多變種，最常見的就是 B+ Tree，它被 MySQL 廣泛使用。
  • R-Tree 索引：R-Tree 在 MySQL 很少使用，僅支援 geometry 資料型別，支援該型別的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種，相對於 B-Tree 來說，R-Tree 的優勢在於範圍查詢。

從邏輯上來對 MySQL 進行分類，主要分為下面這幾種

  • 普通索引：普通索引是最基礎的索引型別，它沒有任何限制 。建立方式如下

    create index normal_index on cxuan003(id);

    image-20210725221035791

    刪除方式

    drop index normal_index on cxuan003;

    image-20210725221050699

  • 唯一索引：唯一索引列的值必須唯一，允許有空值，如果是組合索引，則列值的組合必須唯一，建立方式如下

    create unique index normal_index on cxuan003(id);

    image-20210725221100948

  • 主鍵索引：是一種特殊的索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時建立主鍵索引。

    CREATE TABLE `table` (
         `id` int(11) NOT NULL AUTO_INCREMENT ,
         `title` char(255) NOT NULL ,
         PRIMARY KEY (`id`)
)

    image-20210725221205132

  • 組合索引：指多個欄位上建立的索引，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。使用組合索引時遵循最左字首原則，下面我們就會建立組合索引。

  • 全文索引：主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較，目前只有 char、varchar，text 列上可以建立全文索引，建立表的適合新增全文索引

    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

    當然也可以直接建立全域性索引

    CREATE FULLTEXT INDEX index_content ON article(content)

索引使用

索引可以在建立表的時候進行建立，也可以單獨建立，下面我們採用單獨建立的方式，我們在 cxuan004 上建立字首索引

image-20210725221243114

我們使用 explain 進行分析，可以看到 cxuan004 使用索引的情況

image-20210725221251567

如果不想使用索引，可以刪除索引，索引的刪除語法是

image-20210725221259304

索引使用細則

我們在 cxuan005 上根據 id 和 hash 建立一個複合索引，如下所示

create index id_hash_index on cxuan005(id,hash);

image-20210725221312587

然後根據 id 進行執行計劃的分析

explain select * from cxuan005 where id = '333';

image-20210725221329531

可以發現，即使 where 條件中使用的不是複合索引（Id 、hash），索引仍然能夠使用，這就是索引的字首特性。但是如果只按照 hash 進行查詢的話，索引就不會用到。

explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';

image-20210725221338261

如果 where 條件使用了 like 查詢，並且 % 不在第一個字元，索引才可能被使用。

對於複合索引來說，只能使用 id 進行 like 查詢，因為 hash 列不管怎麼查詢都不會走索引。

explain select * from cxuan005 where id like '%1';

image-20210725221345937

可以看到，如果第一個字元是 % ，則沒有使用索引。

explain select * from cxuan005 where id like '1%';

image-20210725221354590

如果使用了 % 號，就會觸發索引。

如果列名是索引的話，那麼對列名進行 NULL 查詢，將會觸發索引。

explain select * from cxuan005 where id is null;

image-20210725221402396

還有一些情況是存在索引但是 MySQL 並不會使用的情況。

  • 最簡單的，如果使用索引後比不使用索引的效率還差，那麼 MySQL 就不會使用索引。

  • 如果 SQL 中使用了 OR 條件，OR 前的條件列有索引，而後面的列沒有索引的話，那麼涉及到的索引都不會使用，比如 cxuan005 表中，只有 id 和 hash 欄位有索引，而 info 欄位沒有索引，那麼我們使用 or 進行查詢。

    explain select * from cxuan005 where id = 111 and info = 'cxuan';

    image-20210725221411013

    我們從 explain 的執行結果可以看到，雖然 possible_keys 選項上仍然有 id_hash_index 索引，但是從 key、key_len 可以得知，這條 SQL 語句並未使用索引。

  • 在帶有複合索引的列上查詢不是第一列的資料，也不會使用索引。

    explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';

    image-20210725221418223

  • 如果 where 條件的列參與了計算，那麼也不會使用索引

    explain select * from cxuan005 where id + '111' = '666';

    image-20210725221424993

  • 索引列使用函式，一樣也不會使用索引

    explain select * from cxuan005 where concat(id,'111') = '666';

    image-20210725221433421

  • 索引列使用了 like ，並且 % 位於第一個字元，則不會使用索引。

  • 在 order by 操作中，排序的列同時也在 where 語句中，將不會使用索引。

  • 當資料型別出現隱式轉換時，比如 varchar 不加單引號可能轉換為 int 型別時，會使索引無效，觸發全表掃描。比如下面這兩個例子能夠顯而易見的說明這一點

    image-20210725221450982

  • 在索引列上使用 IS NOT NULL 操作

    image-20210725221500897

  • 在索引欄位上使用 <>，!=。不等於操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。

    image-20210725221516453

關於設定索引但是索引沒有生效的場景還有很多，這個需要小夥伴們工作中不斷總結和完善，不過我上面總結的這些索引失效的情景，能夠覆蓋大多數索引失效的場景了。

檢視索引的使用情況

在 MySQL 索引的使用過程中，有一個 Handler_read_key 值，這個值表示了某一行被索引值讀的次數。 Handler_read_key 的值比較低的話，則表明增加索引得到的效能改善不是很理想，可能索引使用的頻率不高。

還有一個值是 Handler_read_rnd_next，這個值高則意味著查詢執行效率不高，應該建立索引來進行搶救。這個值的含義是在資料檔案中讀下一行的請求數。如果正在進行大量的表掃描，Handler_read_rnd_next 的值比較高，就說明表索引不正確或寫入的查詢沒有利用索引。

image-20210725221523808

MySQL 分析表、檢查表和優化表

對於大多數開發者來說，他們更傾向於解決簡單 SQL的優化，而複雜 SQL 的優化交給了公司的 DBA 來做。

下面就從普通程式設計師的角度和你聊幾個簡單的優化方式。

MySQL 分析表

分析表用於分析和儲存表的關鍵字分佈，分析的結果可以使得系統得到準確的統計資訊，使得 SQL 生成正確的執行計劃。如果用於感覺實際執行計劃與預期不符，可以執行分析表來解決問題，分析表語法如下

analyze table cxuan005;

image-20210725221609223

分析結果涉及到的欄位屬性如下

Table：表示表的名稱；

Op：表示執行的操作，analyze 表示進行分析操作，check 表示進行檢查查詢，optimize 表示進行優化操作；

Msg_type：表示資訊型別，其顯示的值通常是狀態、警告、錯誤和資訊這四者之一；

Msg_text：顯示資訊。

對錶的定期分析可以改善效能，應該成為日常工作的一部分。因為通過更新表的索引資訊對錶進行分析，可改善資料庫效能。

MySQL 檢查表

資料庫經常可能遇到錯誤，比如資料寫入磁碟時發生錯誤，或是索引沒有同步更新，或是資料庫未關閉 MySQL 就停止了。遇到這些情況，資料就可能發生錯誤： Incorrect key file for table: ' '. Try to repair it. 此時，我們可以使用 Check Table 語句來檢查表及其對應的索引。

check table cxuan005;

image-20210725221721399

檢查表的主要目的就是檢查一個或者多個表是否有錯誤。Check Table 對 MyISAM 和 InnoDB 表有作用。Check Table 也可以檢查檢視的錯誤。

MySQL 優化表

MySQL 優化表適用於刪除了大量的表資料，或者對包含 VARCHAR、BLOB 或則 TEXT 命令進行大量修改的情況。MySQL 優化表可以將大量的空間碎片進行合併，消除由於刪除或者更新造成的空間浪費情況。它的命令如下

optimize table cxuan005;

image-20210725221753373

我的儲存引擎是 InnoDB 引擎，但是從圖可以知道，InnoDB 不支援使用 optimize 優化，建議使用 recreate + analyze 進行優化。optimize 命令只對 MyISAM 、BDB 表起作用。

常用 SQL 優化

前面我們介紹了使用索引來優化 MySQL ，那麼對於 SQL 的各種語法，句法來說，應該怎樣優化呢？下面，我會從 SQL 命令的角度來聊一波 SQL 優化。

匯入的優化

對於 MyISAM 型別的表，可以通過下面這種方式匯入大量的資料

ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;

這兩個命令用來開啟或者關閉 MyISAM 表非唯一索引的更新。在匯入大量的資料到一個非空的 MyISAM 表時，通過設定這兩個命令，可以提高匯入的效率。對於匯入大量資料到一個空的 MyISAM 表，預設就是先匯入資料然後才建立索引，所以不用進行設定。

但是對於 InnoDB 搜尋引擎的表來說，這樣做不能提高匯入效率，我們有以下幾種方式可以提高匯入的效率：

  1. 因為 InnoDB 型別的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效的提高匯入資料的效率。如果 InnoDB 表沒有主鍵，那麼系統會預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。
  2. 在匯入資料前執行 SET UNIQUE_CHECKS = 0，關閉唯一性校驗，在匯入結束後執行SETUNIQUE_CHECKS = 1，恢復唯一性校驗，可以提高匯入的效率。
  3. 如果應用使用自動提交的方式，建議在匯入前執行 SET AUTOCOMMIT = 0，關閉自動提交，匯入結束後再執行 SET AUTOCOMMIT = 1，開啟自動提交，也可以提高匯入的效率。

insert 的優化

當進行插入語句的時候，可以考慮採用下面這幾種方式進行優化

  • 如果向同一張表插入多條資料的話，最好一次性插入，這樣可以減少資料庫建立連線 -> 斷開連線的時間，如下所示
insert into test values(1,2),(1,3),(1,4)
  • 如果向不同的表插入多條資料，可以使用 insert delayed 語句提高執行效率。delayed 的含義是讓 insert 語句馬上執行，要麼資料都會放在記憶體的佇列中，並沒有真正寫入磁碟。
  • 對於 MyISAM 表來說，可以增加 bulk_insert_buffer_size 的值提高插入效率。
  • 最好將索引和資料檔案在不同的磁碟上存放。

group by 的優化

在使用分組和排序的場景下，如果先進行 Group By 再進行 Order By 的話，可以指定 order by null 禁止排序，因為 order by null 可以避免 filesort ，filesort 往往很耗費時間。如下所示

explain select id,sum(moneys) from sales2 group by id order by null;

order by 的優化

在執行計劃中，經常可以看到 Extra 列出現了 filesort，filesort 是一種檔案排序，這種排序方式比較慢，我們認為是不好的排序，需要進行優化。

image-20210629093920867

優化的方式是要使用索引。

我們在 cxuan005 上建立一個索引。

create index idx on cxuan005(id);

image-20210629095458317

然後我們使用查詢欄位和排序相同的順序進行查詢。

explain select id from cxuan005 where id > '111' order by id;

image-20210629095636413

可以看到，在這次查詢中，使用的是 Using index。這表明我們使用的是索引。

如果建立索引和 order by 的順序不一致，將會使用 Using filesort。

explain select id from cxuan005 where id > '111' order by info;

image-20210629101103501

MySQL 支援兩種方式的排序，filesort 和 index，Using index 是指 MySQL 掃描索引本身完成排序。index 效率高，filesort 效率低。

order by 在滿足下面這些情況下才會使用 index

  • order by 語句使用索引最左前列。
  • 使用 where 子句與 order by 子句條件列組合滿足索引最左前列。

優化巢狀查詢

巢狀查詢是我們經常使用的一種查詢方式，這種查詢方式可以使用 SELECT 語句來建立一個單獨的查詢結果，然後把這個結果當作巢狀語句的查詢範圍用在另一個查詢語句中。使用時子查詢可以將一個複雜的查詢拆分成一個個獨立的部分，邏輯上更易於理解以及程式碼的維護和重複使用。

但是某些情況下，子查詢的效率不高，一般使用 join 來替代子查詢。

使用巢狀查詢的 SQL 語句進行 explain 分析如下

explain select c05.id from cxuan005 c05 where id not in (select id from cxuan003);

image-20210629152925407

從 explain 的結果可以看出，主表的查詢是 index ，子查詢是 index_subquery ，這兩個執行效率都不高。我們使用 join 來優化後的分析計劃如下。

explain select c05.id from cxuan005 c05 left join cxuan003 c03 on c05.id = c03.id;

image-20210629153729525

從 explain 分析結果可以看到，主表查詢和子查詢分別是 index 和 ref，而 ref 的執行效率相對較高，一般 type 的效率由高到低是 System-->const-->eq_ref-->ref--> fulltext-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all 。

count 的優化

count 我們大家用的太多了，一般都用來統計某一列結果集的行數，當 MySQL 確認括號內的表示式不可能為空時，實際上就是在統計行數。

其實 count 還有另一層統計方式：統計某個列值的數量，在統計列值數量的時候，它預設不會統計 NULL 值。

我們經常犯的一個錯誤就是，在括號內指定一個列但是卻希望統計結果集的行數。如果想要知道結果集行數的話，最好使用 count(*)。

limit 分頁的優化

通常我們的系統會進行分頁，一般情況下我們會使用 limit 加上偏移量來實現。同時還會加上 order by 語句進行排序。如果使用索引的情況下，效率一般不會有什麼問題，如果沒有使用索引的話，MySQL 就可能會做大量的檔案排序操作。

通常我們可能會遇到比如 limit 1000 , 50 這種情況，拋棄 1000 條，只取 50 條，這樣的代價非常高，如果所有頁面被訪問的頻率相同，那麼這樣的查詢平均需要訪問半個表的資料。

要優化這種查詢，要麼限制分頁的數量，要麼優化大偏移量的效能。

SQL 中 IN 包含的值不應該太多

MySQL 中對 IN 做了相應的優化，MySQL 會將全部的常量儲存在一個陣列裡面，如果數值較多，產生的消耗也會變大，比如

select name from dual where num in(4,5,6)

像這種 SQL 語句的話，能用 between 使用就不要再使用 in 了。

只需要一條資料的情況

如果只需要一條資料的情況下，推薦使用 limit 1，這樣會使執行計劃中的 type 變為 const。

如果沒有使用索引，就儘量減少排序

儘量用 union all 來代替 union

union 和 union all 的差異主要是前者需要將結果集合並後再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。當然，union all 的前提條件是兩個結果集沒有重複資料。

where 條件優化

  • 避免在 WHERE 字句中對欄位進行 NULL 判斷

  • 避免在 WHERE 中使用 != 或 <> 操作符

  • 不建議使用 % 字首模糊查詢，例如 LIKE “%name”或者LIKE “%name%”，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE “name%”。

  • 避免在 where 中對欄位進行表示式操作，比如 **select user_id,user_project from table_name where age*2=36 ** 就是一種表示式操作，建議改為 **select user_id,user_project from table_name where age=36/2 **

  • 建議在 where 子句中確定 column 的型別，避免 column 欄位的型別和傳入的引數型別不一致的時候發生的型別轉換。

查詢時，儘量指定查詢的欄位名

我們在日常使用 select 查詢時，儘量使用 select 欄位名 這種方式，避免直接 **select * **，這樣增加很多不必要的消耗（cpu、io、記憶體、網路頻寬）；而且查詢效率比較低。

另外，我自己肝了六本 PDF，全網傳播超過10w+ ，微信搜尋「程式設計師cxuan」關注公眾號後，在後臺回覆 cxuan ，領取全部 PDF，這些 PDF 如下

免費領取六本 PDF

相關文章