SQL效能優化技巧

IT王小二發表於2022-02-14
SQL優化

作者：IT王小二

部落格：https://itwxe.com

這裡就給小夥伴們帶來工作中常用的一些 SQL 效能優化技巧總結，包括常見優化十經驗、order by 與 group by 優化、分頁查詢優化、join 關聯查詢優化、in 和 exsits 優化、count(*)查詢優化。

一、常見優化十經驗

其實這個十經驗不一定準確，通過上一篇 MySQL再深入執行計劃之trace工具 我們已經知道 MySQL 執行查詢語句時會進行成本分析，資料量和實際資料值會影響到 MySQL 的實際查詢過程，所以要是小夥伴們根據這常見十經驗寫了後發現和小二文章中的不一樣也不要奇怪。

所以...小二糾結了挺久要不要寫這 SQL 優化常見優化十經驗，畢竟這些很多部落格已經提到過了，想了想，還是寫出來吧。

-- 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());

-- 插入10w條測試資料
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();

1. 儘量全值匹配

explain select * from employees where name = 'itwxe';
explain select * from employees where name = 'itwxe' and age = 22;
explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

儘量全值匹配

記住這三個 key_len 的值，idx_name_age_position (name,age,position)由這三個欄位組成，74代表使用了 name 列；78代表使用了 name,age 列；140代表使用了 name,age,position 列。

2. 最左字首原則

在使用聯合索引的時候要特別注意最左字首原則，即查詢從聯合索引的最左前列開始並且不跳過索引中的列。

explain select * from employees where name = 'itwxe' and age = '18';
explain select * from employees where name = 'itwxe' and position = 'manager';
explain select * from employees where position = 'manager';

最左字首原則1

應該也是比較好理解的，不過需要注意的是和查詢 SQL 書寫的順序無關，最左指的是聯合索引建立時列的順序。例如 where 中顛倒順序還是會使用 idx_name_age_position (name,age,position) 中的三個列索引查詢。

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where age = 22 and position = 'manager' and name = 'itwxe';
explain select * from employees where position = 'manager' and name = 'itwxe' and age = 22;

最左字首原則2

可以看到即使顛倒了順序，三個的執行計劃也是一毛一樣的。

3. 不在索引列上做任何操作(計算、函式、(自動/手動)型別轉換)，會導致索引失效而轉向全表掃描

需要注意的是這裡說的索引列任何操作(計算、函式、(自動/手動)型別轉換)不做操作指的是 where 條件之後的，而不是查詢結果欄位裡面的。

例如對 name 列進行 left 函式操作。

explain select * from employees where name = 'weiwei';
explain select * from employees where left(name,6) = 'weiwei';

索引列不做任何計算、函式、(自動/手動)型別轉換)操作

4. 儲存引擎不能使用索引中範圍條件右邊的列

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';

儲存引擎不能使用索引中範圍條件右邊的列

可以看到第二條 SQL 使用了 name,age 列作為索引來查詢，position 並沒有使用。

5. 儘量使用覆蓋索引，減少 select * 語句

覆蓋索引前面的文章提到過，不贅述了。

6. MySQL 在使用不等於( != 或者 <> )，not in，not exists 的時候無法使用索引會導致全表掃描

<、>、<=、>= 這些，MySQL 內部優化器會根據檢索比例、表大小等多個因素計算查詢成本是否使用索引。

explain select * from employees where name != 'itwxe';

範圍查詢要合理

7. is null 和 is not null 一般情況下也無法使用索引

explain select * from employees where name is null;
explain select * from employees where name is not null;

isnull和isnotnull無法使用索引

8. like 以萬用字元開頭('%itwxe...') MySQL 索引失效會變成全表掃描操作

explain select * from employees where name like 'wei%';
explain select * from employees where name like '%wei';

like萬用字元開頭索引失效

相信理解 B+tree 底層資料結構的小夥伴都很容易就知道為啥了，解決辦法：

  • 根據業務建立聯合索引，使用覆蓋索引查詢。
  • 不能使用覆蓋索引的則藉助 ES 等搜尋引擎。

查詢中可以簡單的把 like KK% 理解為 = 常量，%KK和%KK% 理解為 範圍查詢。

這裡引入一個索引下推的概念：

explain select * from employees where name like 'weiwei%' and age = 22 and position = 'manager';
explain select * from employees where name like 'itwxe%' and age = 22 and position = 'manager';

image-20220213162216965

可以看到第一條SQL中 name = 'weiwei%' ，根據 B+tree 的結構可以知道其後的 age,position 列是無序的，應該無法使用 age,position 列過濾資料才對，但是最後的 key_len 確是140，也就意味著 MySQL 利用到了 name,age,postion 三個列來查詢，這是因為 MySQL 在5.6版本做的優化，引入了索引下推。

索引下推可以在索引遍歷過程中，對索引中包含的所有欄位先做判斷，過濾掉不符合條件的記錄之後再回表，可以有效的減少回表次數。

即當 name = 'weiwei%' 過濾時，使用了索引下推優化，過濾 name 時同時還會在索引裡過濾 age,position 兩個列的條件，拿著過濾完剩下的索引對應的主鍵 id 再回表查整行資料。

那麼很簡單，第二條 SQL 沒有使用索引的原因是因為 MySQL 計算使用索引下推過濾出資料後，查詢二級索引+回表的查詢成本大於全表掃描，所以 MySQL 選擇全表掃描。

9. 型別不匹配 MySQL 自動轉型導致索引失效

字串不加單引號索引失效，亦或是數值型別加單引號索引失效，也就是第三點中提到的自動型別轉換(也叫隱式轉換)導致索引失效。

10. 少用 or 或 in ，用它查詢時，MySQL 不一定使用索引

MySQL 內部優化器會根據檢索比例、表大小等多個因素計算查詢成本是否使用索引。

二、order by 和 group by 優化

1. order by 優化

在前面的文章中小二給大家介紹了索引的底層資料結構，知道了索引本身就是一種排好序的資料結構，所以排序優化最好的辦法就是落實到索引上，這樣查詢出來的資料就已經排好序了，這種排序在 MySQL 中被稱之為 Using Index，即覆蓋索引。那麼如果查詢出來後的資料本身沒有按所需欄位排序，那麼就會出現 Using filesort，即檔案排序。

所以，我們要優化 order by，那麼主要就是消滅低效的 Using filesort，建立合適的聯合索引使用覆蓋索引來排序。

Using filesort檔案排序原理詳解

Using filesort 分為單路排序和雙路排序(又叫回表排序模式)。

  • 單路排序：一次性取出滿足條件行的所有欄位，然後在 sort buffer 中進行排序；用 trace 工具可以看到 sort_mode 資訊裡顯示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >
  • 雙路排序：是首先根據相應的條件取出相應的排序欄位和可以直接定位行資料的行 ID，然後在 sort buffer 中進行排序，排序完後需要再次取回其它需要的欄位；用 trace 工具可以看到sort_mode 資訊裡顯示 < sort_key, rowid >

MySQL 通過比較系統變數 max_length_for_sort_data(預設1024位元組) 的大小和需要查詢的欄位總大小來判斷使用哪種排序模式。

  • 如果欄位的總長度小於 max_length_for_sort_data ，那麼使用單路排序模式
  • 如果欄位的總長度大於 max_length_for_sort_data ，那麼使用雙路排序模式

接下來使用 trace 工具來瞅瞅單路排序和雙路排序：

-- 單路排序
mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;

mysql> select * from employees where name = 'itwxe' order by position;

mysql> select * from information_schema.OPTIMIZER_TRACE;

"join_execution": {  -- sql執行階段
  "select#": 1,
  "steps": [
    {
      "filesort_information": [
        {
          "direction": "asc",
          "table": "`employees`",
          "field": "position"
        }
      ] /* filesort_information */,
      "filesort_priority_queue_optimization": {
        "usable": false,
        "cause": "not applicable (no LIMIT)"
      } /* filesort_priority_queue_optimization */,
      "filesort_execution": [
      ] /* filesort_execution */,
      "filesort_summary": {  -- 檔案排序資訊
        "rows": 1,  -- 預計掃描行數
        "examined_rows": 1,  -- 參與排序的行數
        "number_of_tmp_files": 0,  -- 使用臨時檔案的個數，這個值如果為0代表全部使用的sort_buffer記憶體排序，否則使用的 磁碟檔案排序
        "sort_buffer_size": 262056,  -- 排序快取的大小，單位Byte
        "sort_mode": "<sort_key, packed_additional_fields>"  -- 排序方式，這裡用的單路排序
      } /* filesort_summary */
    }
  ] /* steps */
} /* join_execution */

-- 雙路排序
mysql> set max_length_for_sort_data = 10;  -- employees表所有欄位長度總和哪怕一條記錄也肯定大於10位元組

mysql> select * from employees where name = 'itwxe' order by position;

mysql> select * from information_schema.OPTIMIZER_TRACE;

"join_execution": {
  "select#": 1,
  "steps": [
    {
      "filesort_information": [
        {
          "direction": "asc",
          "table": "`employees`",
          "field": "position"
        }
      ] /* filesort_information */,
      "filesort_priority_queue_optimization": {
        "usable": false,
        "cause": "not applicable (no LIMIT)"
      } /* filesort_priority_queue_optimization */,
      "filesort_execution": [
      ] /* filesort_execution */,
      "filesort_summary": {
        "rows": 1,
        "examined_rows": 1,
        "number_of_tmp_files": 0,
        "sort_buffer_size": 262136,
        "sort_mode": "<sort_key, rowid>"  -- 排序方式，這裡用的雙路排序
      } /* filesort_summary */
    }
  ] /* steps */
} /* join_execution */

單路排序的詳細過程：

  1. 從索引 name 找到第一個滿足 name = 'itwxe' 條件的主鍵 id
  2. 根據主鍵 id 取出整行，取出所有欄位的值，存入 sort_buffer 中
  3. 從索引 name 找到下一個滿足 name = 'itwxe' 條件的主鍵 id。
  4. 重複步驟 2、3 直到不滿足 name = 'itwxe'
  5. 對 sort_buffer 中的資料按照欄位 position 進行排序
  6. 返回結果給客戶端

雙路排序的詳細過程：

  1. 從索引 name 找到第一個滿足 name = 'itwxe' 的主鍵id
  2. 根據主鍵 id 取出整行，把排序欄位 position 和主鍵 id 這兩個欄位放到 sort buffer 中
  3. 從索引 name 取下一個滿足 name = 'itwxe' 記錄的主鍵 id
  4. 重複 3、4 直到不滿足 name = 'itwxe'
  5. 對 sort_buffer 中的欄位 position 和主鍵 id 按照欄位 position 進行排序
  6. 遍歷排序好的 id 和欄位 position，按照 id 的值回到原表中取出所有欄位的值返回給客戶端

總結：單路排序會將整行所有資料快取到 sort buffer 中，雙路排序只將主鍵id和排序欄位放入到 sort buffer 中排序，在根據排序好的資料，從原來表中根據id查詢資料返回給客戶端。

如何選項單路排序還是多路排序？

MySQL 優化器使用雙路排序還是單路排序是有自己的演算法判斷的，如果查詢的列欄位大於 max_length_for_sort_data 變數，則會使用雙路排序，反之則會使用單路排序，單路排序速度是更快的，不過比較佔據記憶體，如果在記憶體空間允許的情況下想要使用單路排序的話，可以增加 max_length_for_sort_data 變數的大小。

不過需要注意，如果全部使用 sort_buffer 記憶體排序一般情況下效率會高於磁碟檔案排序，但不能因為這個就隨便增大 sort_buffer(預設1M)，mysql很多引數設定都是做過優化的，不要輕易調整。

2. group by 優化

group by 與 order by 很類似，其實質是先排序後分組，遵照索引建立順序的最左字首法則。對於 group by 的優化如果不需要排序的可以加上 order by null 禁止排序。

三、分頁查詢優化

分頁查詢優化示例表仍為 employees。

select * from employees limit 90000,10;

很多時候我們業務系統實現分頁功能可能會用如下 SQL 實現，看似 MySQL 是取90001行開始的10條記錄，但是實際上 MySQL 在處理這個分頁的時候是先讀取前 90010 條記錄，然後把前90000條記錄捨棄，取出90001-90010的資料返回給客戶端。因此如果要查詢一張大表比較靠後的資料，執行效率是很低的。

1. 根據自增且連續的主鍵排序的分頁查詢

select * from employees where id > 90000 limit 10;

原理：根據主鍵索引 id 排除 <90000 的資料，取後10條資料避免全表掃描。

缺點：如果主鍵 id 不連續，或者中間有刪除資料，則無法實現效果，所以通常使用下面的第二種方式。

2. 根據非主鍵欄位排序的分頁查詢

select * from employees order by name limit 90000,10;
select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

非主鍵欄位排序的分頁查詢

可以看到查詢相同的結果，但是非常大的查詢速度差距，這還只是10w+的測試資料，加入是100w呢，差距得多大嘞！

阿里手冊分頁規約

阿里Java開發手冊中也對這種情況進行了說明，非常推薦各位小夥伴們看下阿里Java開發手冊，其中專門有一個章節對 MySQL 開發規範進行了說明。

阿里Java開發手冊-MySQL資料庫

四、join關聯查詢優化

-- 示例表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 往t1表插入1w行測試資料
drop procedure if exists insert_t1;
delimiter $$
create procedure insert_t1()
begin
  declare i int;
  set i = 1;
  while(i <= 10000)do
    insert into t1(a,b) values(i,i);
    set i = i + 1;                  
  end while;
end$$
delimiter ;
call insert_t1();

-- 往t2表插入100行測試資料
drop procedure if exists insert_t2;
delimiter $$
create procedure insert_t2()
begin
  declare i int;
  set i = 1;
  while(i <= 100)do
    insert into t2(a,b) values(i,i);
    set i = i + 1;                  
  end while;
end$$
delimiter ;
call insert_t2();

1. 表關聯常見的兩種演算法

巢狀迴圈連線Nested-Loop Join(NLJ) 演算法

NLJ 演算法一次一行迴圈地從第一張表(驅動表)中讀取行，在這行資料中取到關聯欄位，根據關聯欄位在另一張表(被驅動表)裡取出滿足條件的行，然後取出兩張表的結果合集。

explain select * from t1 inner join t2 on t1.a = t2.a;

NLJ演算法

從執行計劃可以看出，t2 作為驅動表，t1 作為被驅動表。先執行的就是驅動表(執行計劃結果的 id如果一樣則按從上到下順序執行 SQL)，優化器通常會優先選擇小表做驅動表，用 where 條件過濾完驅動表，然後再跟被驅動表做關聯查詢。所以使用 inner join 時，排在前面的表並不一定就是驅動表。

  • 當使用 left join 時，左表是驅動表，右表是被驅動表。
  • 當使用 right join 時，右表時驅動表，左表是被驅動表。
  • 當使用 inner join 時，MySQL 通常會選擇資料量比較小表作為驅動表，大表作為被驅動表。

注意：在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之後，計算參與 join 的各個欄位的總資料量，資料量小的那個表，就是“小表”，應該作為驅動表。而不是簡單的比較兩個表的總資料量。

上面 SQL 的大致流程如下：

  • 從表 t2 中讀取一行資料(如果t2表有查詢過濾條件的，用先用條件過濾完，再從過濾結果裡取出一行資料)。
  • 從第 1 步的資料中，取出關聯欄位 a，到表 t1 中查詢。
  • 取出表 t1 中滿足條件的行，跟 t2 中獲取到的結果合併，作為結果返回給客戶端。
  • 重複上面 3 步。

整個過程會讀取 t2 表的所有資料(掃描100行)，然後遍歷這每行資料中欄位 a 的值，根據 t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引，1次掃描可以認為最終只掃描 t1 表一行完整資料，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了200 行。

如果連線查詢的列上沒有索引，NLJ 演算法效能會比較低，那麼 MySQL 則會選擇 BNL 演算法。

基於塊的巢狀迴圈連線Block Nested-Loop Join(BNL)演算法

BNL 演算法把驅動表的資料讀入到 join_buffer(連線查詢快取) 中，然後掃描被驅動表，把被驅動表每一行取出來跟 join_buffer 中的資料做對比。

explain select * from t1 inner join t2 on t1.b = t2.b;

BNL演算法

Extra 中的 Using join buffer (Block Nested Loop) 說明該關聯查詢使用的是 BNL 演算法。同時可以看到 t2 仍然作為驅動表，t1 作為被驅動表。

上面 SQL 的大致流程如下：

  • 把 t2 的所有資料放入到 join_buffer 中。
  • 把表 t1 中每一行取出來，跟 join_buffer 中的資料做對比。
  • 返回滿足 join 條件的資料。

整個過程對錶 t1 和 t2 都做了一次全表掃描，因此掃描的總行數為 10000(表 t1 的資料總量) + 100(表 t2 的資料總量) = 10100。並且 join_buffer 裡的資料是無序的，因此對錶 t1 中的每一行，都要做 100 次判斷，所以記憶體中的判斷次數是 100 * 10000= 100萬次。

這個例子裡表 t2 才 100 行，要是表 t2 是一個大表，join_buffer 放不下怎麼辦呢？

join_buffer 的大小是由引數 join_buffer_size 設定的，預設值是 256k。如果放不下表 t2 的所有資料話，策略很簡單，就是分段放。流程如下：

  • 取驅動表的一部分資料放入 join_buffer，直至 join_buffer 放不了。(比如 t2 表有1000行記錄， join_buffer 一次只能放800行資料，那麼執行過程就是先往 join_buffer 裡放800行記錄)
  • 掃描被驅動表的每一行資料，跟 join_buffer 中的資料做對比，滿足 join 條件的資料作為結果集的一部分返回。(從 t1 表裡取資料跟 join_buffer 中資料對比得到部分結果)
  • 清空 join_buffer。(清空 join_buffer)
  • 繼續讀取驅動表剩下的資料，重複前三個步驟，一直驅動表的資料被掃描完。(再將 t2 表剩餘200行記錄記錄放到 join_buffer 中，再次從 t1 表裡取資料跟 join_buffer 中資料對比。所以多掃了一次 t1 表和多掃描分段次數的 t2 表)

那麼以這個分段放的例子來說，若驅動表的行數是 N，需要分 K 段才能掃描完，被驅動表的行數是 M，則掃描的行數是 N + K * M，即 1000 + 2 * 10000 = 21000 行；總的記憶體判斷次數為 (800 + 200) * 10000 = 100萬次。

被驅動表的關聯欄位沒索引為什麼要選擇使用 BNL 演算法而不使用 NLJ 演算法呢？

假設沒有索引的情況下選擇 NLJ 演算法，那麼需要掃描的行數為 100 * 10000 = 100w萬次，但是這個確是磁碟掃描。

很顯然，用 BNL 演算法磁碟掃描次數少很多，並且相比磁碟掃描，BNL 的記憶體計算會快得多。

因此 MySQL 對於被驅動表的關聯欄位沒索引的關聯查詢，一般都會使用 BNL 演算法；如果有索引一般選擇 NLJ 演算法，有索引的情況下 NLJ 演算法比 BNL演算法效能更高。

2. 對於關聯SQL的優化

看完了 NLJ 演算法和 BLJ 演算法，想必關聯 SQL 的優化也有一些思路了。

  • 超過三個表禁止 join，多表關聯查詢時，保證被關聯的欄位需要有索引，儘量選擇 NLJ 演算法，同時需要 join 的欄位，資料型別必須絕對一致。
  • 小表驅動大表，寫多表連線 SQL 時如果明確知道哪張表是小表可以用 straight_join 寫法固定連線驅動方式，省去mysql優化器自己判斷的時間。

straight_join：straight_join 功能同 inner join 類似，但能讓左邊的表來驅動右邊的表，能改表優化器對於聯表查詢的執行順序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定 MySQL 選擇 t2 表作為驅動表。

  • straight_join 只適用於 inner join，並不適用於 left join、right join。(因為 left join、right join 已經代表指定了表的執行順序)
  • 儘可能讓優化器去判斷，使用 straight_join 一定要慎重，因為部分情況下人為指定的執行順序並不一定會比優化引擎要靠譜。

五、in和exsits優化

原則：小表驅動大表。

in優化：當B表的資料集小於A表的資料集時，in 優於 exists。

select * from A where id in (select id from B);
# 等價於：
for(select id from B) {
    select * from A where A.id = B.id;
}

exsits優化：當A表的資料集小於B表的資料集時，exists 優於 in。

select * from A where exists (select 1 from B where B.id = A.id);
# 等價於：
for(select * from A) {
    select 1 from B where B.id = A.id;
}
  • exists (subquery) 只返回 true 或 false，因此子查詢中的 SELECT * 也可以用 SELECT 1 替換，官方說法是實際執行時會忽略 select 清單，因此沒有區別。
  • exists 子查詢往往也可以用 join 來代替，怎麼最優查詢需要具體問題具體分析。

六、count(*)查詢優化

1. count比較

-- 臨時關閉MySQL查詢快取
set global query_cache_size=0;
set global query_cache_type=0;

-- count
explain select count(*) from employees;
explain select count(1) from employees;
explain select count(id) from employees;
explain select count(name) from employees;

小夥伴們可能都聽過 DBA 或者某些部落格建議不要使用 count(*) 來統計資料行數，但是實際上並不是這樣的，可以發現上面四條 SQL 的執行計劃是一毛一樣的。

count優化

那麼既然執行計劃是一樣的，那麼說明這4條語句的執行效率差不多其實，甚至5.7版本後 count(*) 效率還更高。不過需要注意的是 count(name) 不會統計 name 為 null 的資料行。

  • 欄位有索引：count(*) ≈ count(1) > count(欄位) > count(主鍵id) // 欄位有索引，count(欄位) 統計走二級索引，二級索引儲存資料比主鍵索引少，所以 count(欄位) > count(主鍵id)。
  • 欄位無索引：count(*) ≈ count(1)> count(主鍵id) > count(欄位) // 欄位沒有索引，count(欄位) 統計走不了索引，count(主鍵id) 還可以走主鍵索引，所以 count(主鍵id) > count(欄位)
  • count(1) 跟 count(欄位) 執行過程類似，不過 count(1) 不需要取出欄位統計，就用常量1做統計，count(欄位) 還需要取出欄位，所以理論上 count(1) 比 count(欄位) 會快一點。
  • count(*) 是例外，MySQL 並不會把全部欄位取出來，而是專門做了優化，不取值，按行累加，效率很高，所以不需要用 count(列名)或 count(常量) 來替代 count(*)。

為什麼對於 count(id)，MySQL 最終選擇輔助索引而不是主鍵聚集索引？

因為二級索引相對主鍵索引儲存資料更少，檢索效能應該更高，MySQL5.7 版本內部做了點優化。

2. 常見優化方法

a. 查詢MySQL自己維護的總行數

對於 MyISAM 儲存引擎的表做不帶 where 條件的 count 查詢效能是很高的，因為 MyISAM 儲存引擎的表的總行數會被 MySQL 儲存在磁碟上，查詢不需要計算。

select count(*) from test_myisam;
explain select count(*) from test_myisam;

查詢MySQL自己維護的總行數

可以看到執行計劃表都沒有查詢。

b. show table status

如果只需要知道表總行數的估計值可以用 show table status like 'employyees'; 查詢，查詢結果是個估計值。

show table status

c. 將總數維護到Redis裡

插入或刪除表資料行的時候同時維護 Redis 裡的表總行數 key 的計數值(用 incr 或 decr 命令)，但是這種方式可能不準，很難保證表操作和redis操作的事務一致性。

d. 增加資料庫計數表

插入或刪除表資料行的時候同時維護計數表，讓他們在同一個事務裡操作。

常見的 SQL 效能優化技巧就寫到這裡啦，寫到此處已是深夜~~~

當然啦，SQL 優化技巧當然離不開怎麼建立一個合適的索引，這篇文章的篇幅估計很長了...所以...下篇文章小二會通過一個有趣的小案例說說索引設計原則和技巧，小夥伴們下篇見?。

相關文章