金三銀四已經開始啦，準備了SQL優化的13連問，内容非常幹！相信大家看完一定會有幫助的。

1.日常工作中，你是怎麼優化SQL的？

大家可以從這幾個次元回答這個問題：

分析慢查詢日志
使用explain檢視執行計劃
索引優化
深分頁優化
避免全表掃描
避免傳回不必要的資料（如select具體字段而不是select*）
使用合适的資料類型（如可以使用int類型的話，就不要設計為varchar）
優化sql結構（如join優化等等）
适當分批量進行 (如批量更新、删除)
定期清理無用的資料
适當分庫分表
讀寫分離

2. 是否遇到過深分頁問題，如何解決

我們可以通過減少回表次數來優化。一般有标簽記錄法和延遲關聯法。

标簽記錄法

就是标記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪裡了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

假設上一次記錄到100000，則SQL可以修改為：

select  id,name,balance FROM account where id > 100000 limit 10;
複制代碼

這樣的話，後面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是這種方式有局限性：需要一種類似連續自增的字段。

延遲關聯法

延遲關聯法，就是把條件轉移到主鍵索引樹，然後減少回表。假設原生SQL是這樣的的，其中id是主鍵，create_time是普通索引

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
複制代碼

使用延遲關聯法優化，如下：

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN 
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) 
AS acct2 on acct1.id= acct2.id;
複制代碼

優化思路就是，先通過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID内連接配接，這樣後面直接走了主鍵索引了，同時也減少了回表。

3. 聊聊explain執行計劃

當explain與SQL一起使用時，MySQL将顯示來自優化器的有關語句執行計劃的資訊。即MySQL解釋了它将如何處理該語句，包括有關如何連接配接表以及以何種順序連接配接表等資訊。

一條簡單SQL，使用了explain的效果如下：

一般來說，我們需要重點關注type、rows、filtered、extra、key。

3.1 type

type表示連接配接類型，檢視索引執行情況的一個重要名額。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：這種類型要求資料庫表中隻有一條資料，是const類型的一個特例，一般情況下是不會出現的。
const：通過一次索引就能找到資料，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
ref : 常用于非主鍵和唯一索引掃描。
ref_or_null：這種連接配接類型類似于ref，差別在于MySQL會額外搜尋包含NULL值的行
index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
unique_subquery：類似于eq_ref，條件用了in子查詢
index_subquery：差別于unique_subquery，用于非唯一索引，可以傳回重複值。
range：常用于範圍查詢，比如：between ... and 或 In 等操作
index：全索引掃描
ALL：全表掃描

3.2 rows

該清單示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準确值。

3.3 filtered

該列是一個百分比的值，表裡符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎傳回的資料在經過過濾後，剩下滿足條件的記錄數量的比例。

3.4 extra

該字段包含有關MySQL如何解析查詢的其他資訊，它一般會出現這幾個值：

Using filesort：表示按檔案排序，一般是在指定的排序和索引排序不一緻的情況才會出現。一般見于order by語句
Using index ：表示是否用了覆寫索引。
Using temporary: 表示是否使用了臨時表,性能特别差，需要重點優化。一般多見于group by語句，或者union語句。
Using where : 表示使用了where條件過濾.
Using index condition：MySQL5.6之後新增的索引下推。在存儲引擎層進行資料過濾，而不是在服務層過濾，利用索引現有的資料減少回表的資料。

3.5 key

該清單示實際用到的索引。一般配合possible_keys列一起看。

注意:有時候，explain配合show WARNINGS; （可以檢視優化後,最終執行的sql），效果更佳哦。

4.說說大表的優化方案

資料庫設計優化

合理的資料庫設計可以極大地提高查詢效率。我們在設計大表時，可以考慮拆分表、使用分區表、添加索引等方式來優化表結構。同時也要避免使用大量備援字段、避免頻繁使用join查詢等操作。

索引優化

對于大表的查詢操作，索引優化是非常重要的一環。可以考慮增加或者修改索引、使用覆寫索引、使用聯合索引等方式來提高查詢效率。同時也要注意定期清理備援的索引以及對于經常使用的查詢語句建立索引。

分區優化

将大表按照某個列分成多個分區表，每個分區表的資料量較小，可以提高查詢和更新的性能。分區表還可以幫助在維護表結構的同時，減少鎖表時間，提高并發處理能力。

資料清理歸檔

對于一些曆史資料或者無用資料，可以進行定期歸檔，避免資料過多造成SQL查詢效率降低。同時也要注意對于大表進行定期的資料備份以及緊急資料恢複的準備工作。

緩存優化

對于一些經常被查詢的資料，可以使用緩存優化。使用Redis等緩存中間件來緩存常用的資料，以減少查詢資料庫的次數，提高查詢效率。

SQL語句優化

在編寫SQL查詢語句時，要盡可能地簡單明了，避免複雜的查詢語句，同時也要避免一些不必要的查詢操作。對于複雜的查詢語句，可以使用Explain執行計劃來進行優化。同時也要注意避免使用OR等耗費性能的操作符。

分庫分表

如果資料量千萬級别，需要考慮分庫分表哈。分庫分表相關知識點，可以看我之前這篇文章哈，我們為什麼要分庫分表？

5.哪些因素可能導緻MySQL慢查詢？

慢查詢一般有以下這些原因：

大家有興趣可以看下。我之前寫的這篇文章哈：盤點MySQL慢查詢的12個原因

6.如何使用索引優化SQL查詢？

添加合适索引（在where、group by、order by等後面的字段添加合适索引）
選擇合适的索引類型 (B-tree索引适合範圍查詢、哈希索引适合等值查詢)
注意不适合加索引的場景（資料量少的表，更新頻繁的字段，區分度低的字段）
加索引的時候，需要考慮覆寫索引，減少回表，考慮聯合索引的最左字首原則
explain檢視SQL的執行計劃，确認是否會命中索引。
注意索引并不是越多越好，通常建議在單個表中不要超過5個索引。因為索引會占用磁盤空間，索引更新代價高。

7.聊聊慢SQL的優化思路

檢視慢查詢日志記錄，分析慢SQL
explain分析SQL的執行計劃
profile 分析執行耗時
Optimizer Trace分析詳情
确定問題并采用相應的措施

7.1 檢視慢查詢日志記錄，分析慢SQL

如何定位慢SQL呢、我們可以通過slow log來檢視慢SQL。預設的情況下呢，MySQL資料庫是不開啟慢查詢日志（slow query log）呢。是以我們需要手動把它打開。

檢視下慢查詢日志配置，我們可以使用show variables like 'slow_query_log%'指令，如下：

slow query log表示慢查詢開啟的狀态
slow_query_log_file表示慢查詢日志存放的位置

我們還可以使用show variables like 'long_query_time'指令，檢視超過多少時間，才記錄到慢查詢日志，如下：

long_query_time表示查詢超過多少秒才記錄到慢查詢日志。

我們可以通過慢查日志，定位那些執行效率較低的SQL語句，重點關注分析。

7.2 explain檢視分析SQL的執行計劃

當定位出查詢效率低的SQL後，可以使用explain檢視SQL的執行計劃。

一條簡單SQL，使用了explain的效果如下：

一般來說，我們需要重點關注type、rows、filtered、extra、key。

7.3 profile 分析執行耗時

explain隻是看到SQL的預估執行計劃，如果要了解SQL真正的執行線程狀态及消耗的時間，需要使用profiling。開啟profiling參數後，後續執行的SQL語句都會記錄其資源開銷，包括IO，上下文切換，CPU，記憶體等等，我們可以根據這些開銷進一步分析目前慢SQL的瓶頸再進一步進行優化。

profiling預設是關閉，我們可以使用show variables like '%profil%'檢視是否開啟，如下：

可以使用set profiling=ON開啟。開啟後，可以運作幾條SQL，然後使用show profiles檢視一下。

show profiles會顯示最近發給伺服器的多條語句，條數由變量profiling_history_size定義，預設是15。如果我們需要看單獨某條SQL的分析，可以show profile檢視最近一條SQL的分析。也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）檢視具體一條的SQL語句分析。

除了檢視profile ，還可以檢視cpu和io，如上圖。

7.4 Optimizer Trace分析詳情

profile隻能檢視到SQL的執行耗時，但是無法看到SQL真正執行的過程資訊，即不知道MySQL優化器是如何選擇執行計劃。這時候，我們可以使用Optimizer Trace，它可以跟蹤執行語句的解析優化執行的全過程。

我們可以使用set optimizer_trace="enabled=on"打開開關，接着執行要跟蹤的SQL，最後執行select * from information_schema.optimizer_trace跟蹤，如下：

大家可以檢視分析其執行樹，會包括三個階段：

join_preparation：準備階段
join_optimization：分析階段
join_execution：執行階段

7.5 确定問題并采用相應的措施

最後确認問題，就采取對應的措施。

多數慢SQL都跟索引有關，比如不加索引，索引不生效、不合理等，這時候，我們可以優化索引。
我們還可以優化SQL語句，比如一些in元素過多問題（分批），深分頁問題（基于上一次資料過濾等），進行時間分段查詢
SQl沒辦法很好優化，可以改用ES的方式，或者數倉。
如果單表資料量過大導緻慢查詢，則可以考慮分庫分表
如果資料庫在刷髒頁導緻慢查詢，考慮是否可以優化一些參數，跟DBA讨論優化方案
如果存量資料量太大，考慮是否可以讓部分資料歸檔

我之前寫了一篇文章，有關于導緻慢查詢的12個原因，大家看一看一下哈:盤點MySQL慢查詢的12個原因

8.一條sql執行過長的時間，你如何優化，從哪些方面入手？

這道面試題，其實跟慢SQl排查解決有點像，是以大家回答得時候，可以參考上一小節哈。我們可以從這幾個方面入手哈：

确定瓶頸
索引優化
優化SQL語句
資料庫參數優化
分析鎖的情況
資料庫硬體更新

确定瓶頸

首先，通過檢視MySQL日志，慢查詢日志，explain分析SQL的執行計劃，profile 分析執行耗時，Optimizer Trace分析詳情等操作，确定查詢執行的瓶頸在哪裡。隻有确定了瓶頸，才能有針對性地進行優化。

索引優化

在确定了瓶頸之後，可以考慮通過增加索引來優化查詢效率。可以根據查詢語句的條件，增加相應的索引，進而加快查詢速度。但是索引也會帶來一些負面影響，如占用磁盤空間，降低寫入效率等，是以需要根據具體情況權衡。

優化SQL語句

有些SQL語句本身可能存在一些問題，如join操作過于頻繁，使用了不必要的子查詢等，這些都會導緻查詢效率低下。可以通過優化SQL語句來減少不必要的操作，進而提高查詢效率。

資料庫參數優化

資料庫參數也會影響查詢效率，可以通過修改資料庫參數來優化查詢效率，如修改記憶體緩存大小、修改連接配接池大小等。不同的資料庫參數優化方式不同，需要根據具體情況進行調整。

分析鎖的情況

查詢執行時間過長有可能是由于鎖的問題導緻的，需要分析查詢語句中是否存在鎖的問題，如果存在鎖的問題，可以考慮增加鎖的并發度，進而提高查詢效率。

資料庫硬體更新

如果以上方法都無法解決問題，可以考慮對資料庫硬體進行更新，如增加 CPU 數量、加快磁盤讀寫速度等，進而提高資料庫的整體性能。

9. 列舉一下，常用的資料庫設計優化技巧？

字段盡量避免使用NULL
合理選擇資料類型
字段選擇合适的長度
正确使用索引
盡量少定義text類型
合理的資料表結構設計
适當的備援設計
優化SQL查詢語句
一張表的字段不宜過多

10.列舉日常開發中，列舉十個書寫高品質SQL的小技巧

查詢SQL盡量不要使用select *，而是select具體字段。
小表驅動大表
優化你的like語句
盡量避免在索引列上使用mysql的内置函數
如果插入資料過多，考慮批量操作。
多用limit
小表驅動大表
exist & in合理利用
in元素不要過多
盡量用union all替換union

大家可以參考我之前這篇文章哈：後端程式員必備：書寫高品質SQL的30條建議

11.index merge了解過嘛？

index merge是什麼？

在MySQL中，當執行一個查詢語句需要使用多個索引時，MySQL可以使用索引合并(Index Merge)來優化查詢性能。具體來說，索引合并是将多個單列索引或多個聯合索引合并使用，以滿足查詢語句的需要。

當使用索引合并時，MySQL會選擇最優的索引組合來執行查詢，進而避免了全表掃描和排序操作，提高了查詢效率。而對于使用多個單列索引的查詢語句，MySQL也可以使用索引合并來優化查詢性能。

大家可以看一個使用index merge的例子：

假設有一個名為orders的表，包含order_id、customer_id、product_id、order_date等字段，其中order_id、customer_id、product_id三個字段都建有索引。

如果要查詢customer_id為1，order_date在2022年1月1日到2022年2月1日之間的訂單記錄，可以使用以下SQL語句：

SELECT *
FROM orders
WHERE customer_id = 1
AND order_date >= '2022-01-01'
AND order_date < '2022-02-01'
複制代碼

在執行該查詢語句時，MySQL可以使用customer_id索引和order_date索引來優化查詢。如果使用單個索引，則需要掃描整個索引樹來比對查詢條件；但如果使用索引合并，則可以先使用customer_id索引來過濾出符合條件的記錄，然後再使用order_date索引來進一步過濾記錄，進而大大減少了掃描的記錄數，提高了查詢效率。

大家可以使用EXPLAIN關鍵字可以檢視查詢計劃，确認是否使用了索引合并。例如，執行以下語句：

EXPLAIN SELECT *
FROM orders
WHERE customer_id = 1
AND order_date >= '2022-01-01'
AND order_date < '2022-02-01'
複制代碼

如果查詢計劃中出現了Using index merge的資訊，則表示該查詢使用了索引合并優化。

12. order by查詢效率慢,如何優化.

大家是否還記得order by查詢為什麼會慢嘛?

order by排序，分為全字段排序和rowid排序。它是拿max_length_for_sort_data和結果行資料長度對比，如果結果行資料長度超過max_length_for_sort_data這個值，就會走rowid排序，相反，則走全字段排序。

rowid排序，一般需要回表去找滿足條件的資料，是以效率會慢一點.如果是order by排序,可能會借助磁盤檔案排序的話，效率就更慢一點.

如何優化order by的檔案排序?

因為資料是無序的，是以就需要排序。如果資料本身是有序的，那就不會再用到檔案排序啦。而索引資料本身是有序的，我們通過建立索引來優化order by語句。
我們還可以通過調整max_length_for_sort_data、sort_buffer_size等參數優化；

大家忘記order by的話,可以看我之前的這篇文章哈: 看一遍就了解：order by詳解

13. group by 查詢慢的話,如何優化呀.

group by一般用于分組統計，它表達的邏輯就是根據一定的規則，進行分組。日常開發中，我們使用得比較頻繁。如果不注意，很容易産生慢SQL。

group by可能會慢在哪裡？因為它既用到臨時表，又預設用到排序。有時候還可能用到磁盤臨時表。

如果執行過程中，會發現記憶體臨時表大小到達了上限（控制這個上限的參數就是tmp_table_size），會把記憶體臨時表轉成磁盤臨時表。
如果資料量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。

如何優化group by呢?

group by 後面的字段加索引
order by null 不用排序
盡量隻使用記憶體臨時表
使用SQL_BIG_RESULT

日常開發中，我們經常會使用到group by。親愛的小夥伴，你是否知道group by的工作原理呢？group by和having有什麼差別呢？group by的優化思路是怎樣的呢？使用group by有哪些需要注意的問題呢？本文将跟大家一起來學習，攻克group by~

使用group by的簡單例子
group by 工作原理
group by + where 和 having的差別
group by 優化思路
group by 使用注意點
一個生産慢SQL如何優化

1. 使用group by的簡單例子

group by一般用于分組統計，它表達的邏輯就是根據一定的規則，進行分組。我們先從一個簡單的例子，一起來複習一下哈。

假設用一張員工表，表結構如下：

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `id_card` varchar(20) NOT NULL COMMENT '身份證号碼',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年齡',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='員工表';

表存量的資料如下：

我們現在有這麼一個需求：統計每個城市的員工數量。對應的 SQL 語句就可以這麼寫：

select city ,count(*) as num from staff group by city;

執行結果如下：

這條SQL語句的邏輯很清楚啦，但是它的底層執行流程是怎樣的呢？

2. group by 原理分析

2.1 explain 分析

我們先用explain檢視一下執行計劃

explain select city ,count(*) as num from staff group by city;

Extra 這個字段的Using temporary表示在執行分組的時候使用了臨時表
Extra 這個字段的Using filesort表示使用了排序

group by 怎麼就使用到臨時表和排序了呢？我們來看下這個SQL的執行流程

2.2 group by 的簡單執行流程

explain select city ,count(*) as num from staff group by city;

我們一起來看下這個SQL的執行流程哈

建立記憶體臨時表，表裡有兩個字段city和num；
全表掃描staff的記錄，依次取出city = 'X'的記錄。

判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄 (X,1);
如果臨時表中有city='X'的行的行，就将x 這一行的num值加 1；

周遊完成後，再根據字段city做排序，得到結果集傳回給用戶端。

這個流程的執行圖如下：

臨時表的排序是怎樣的呢？

就是把需要排序的字段，放到sort buffer，排完就傳回。在這裡注意一點哈，排序分全字段排序和rowid排序

如果是全字段排序，需要查詢傳回的字段，都放入sort buffer，根據排序字段排完，直接傳回

如果是rowid排序，隻是需要排序的字段放入sort buffer，然後多一次回表操作，再傳回。

怎麼确定走的是全字段排序還是rowid 排序排序呢？由一個資料庫參數控制的，max_length_for_sort_data

對排序有興趣深入了解的小夥伴，可以看我這篇文章哈。

看一遍就了解：order by詳解

3. where 和 having的差別

group by + where 的執行流程
group by + having 的執行流程
同時有where、group by 、having的執行順序

3.1 group by + where 的執行流程

有些小夥伴覺得上一小節的SQL太簡單啦，如果加了where條件之後，并且where條件列加了索引呢，執行流程是怎樣？

好的，我們給它加個條件，并且加個idx_age的索引，如下：

select city ,count(*) as num from staff where age> 30 group by city;
//加索引
alter table staff add index idx_age (age);

再來expain分析一下：

explain select city ,count(*) as num from staff where age> 30 group by city;

從explain 執行計劃結果，可以發現查詢條件命中了idx_age的索引，并且使用了臨時表和排序

Using index condition:表示索引下推優化，根據索引盡可能的過濾資料,然後再傳回給伺服器層根據where其他條件進行過濾。這裡單個索引為什麼會出現索引下推呢？explain出現并不代表一定是使用了索引下推，隻是代表可以使用，但是不一定用了。大家如果有想法或者有疑問，可以加我微信讨論哈。

執行流程如下：

建立記憶體臨時表，表裡有兩個字段city和num；
掃描索引樹idx_age，找到大于年齡大于30的主鍵ID
通過主鍵ID，回表找到city = 'X'

判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄 (X,1);
如果臨時表中有city='X'的行的行，就将x 這一行的num值加 1；

繼續重複2,3步驟，找到所有滿足條件的資料，
最後根據字段city做排序，得到結果集傳回給用戶端。

3.2 group by + having 的執行

如果你要查詢每個城市的員工數量，擷取到員工數量不低于3的城市，having可以很好解決你的問題，SQL醬紫寫：

select city ,count(*) as num from staff  group by city having num >= 3;

查詢結果如下：

having稱為分組過濾條件，它對傳回的結果集操作。

3.3 同時有where、group by 、having的執行順序

如果一個SQL同時含有where、group by、having子句，執行順序是怎樣的呢。

比如這個SQL：

select city ,count(*) as num from staff  where age> 19 group by city having num >= 3;

執行where子句查找符合年齡大于19的員工資料
group by子句對員工資料，根據城市分組。
對group by子句形成的城市組，運作聚集函數計算每一組的員工數量值；
最後用having子句選出員工數量大于等于3的城市組。

3.4 where + having 差別總結

having子句用于分組後篩選，where子句用于行條件篩選
having一般都是配合group by 和聚合函數一起出現如(count(),sum(),avg(),max(),min())
where條件子句中不能使用聚集函數，而having子句就可以。
having隻能用在group by之後，where執行在group by之前

4. 使用 group by 注意的問題

使用group by 主要有這幾點需要注意：

group by一定要配合聚合函數一起使用嘛？
group by的字段一定要出現在select中嘛
group by導緻的慢SQL問題

4.1 group by一定要配合聚合函數使用嘛？

group by 就是分組統計的意思，一般情況都是配合聚合函數如（count(),sum(),avg(),max(),min())一起使用。

count() 數量
sum() 總和
avg() 平均
max() 最大值
min() 最小值

如果沒有配合聚合函數使用可以嗎？

我用的是Mysql 5.7 ，是可以的。不會報錯，并且傳回的是，分組的第一行資料。

比如這個SQL：

select city,id_card,age from staff group by  city;

查詢結果是

大家對比看下，傳回的就是每個分組的第一條資料

當然，平時大家使用的時候，group by還是配合聚合函數使用的，除非一些特殊場景，比如你想去重，當然去重用distinct也是可以的。

4.2 group by 後面跟的字段一定要出現在select中嘛。

不一定，比如以下SQL：

select max(age)  from staff group by city;

執行結果如下：

分組字段city不在select 後面，并不會報錯。當然，這個可能跟不同的資料庫，不同的版本有關吧。大家使用的時候，可以先驗證一下就好。有一句話叫做，紙上得來終覺淺，絕知此事要躬行。

4.3 group by導緻的慢SQL問題

到了最重要的一個注意問題啦，group by使用不當，很容易就會産生慢SQL 問題。因為它既用到臨時表，又預設用到排序。有時候還可能用到磁盤臨時表。

如果執行過程中，會發現記憶體臨時表大小到達了上限（控制這個上限的參數就是tmp_table_size），會把記憶體臨時表轉成磁盤臨時表。

如果資料量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。

這些都是導緻慢SQL的x因素，我們一起來探讨優化方案哈。

5. group by的一些優化方案

從哪些方向去優化呢？

方向1：既然它預設會排序，我們不給它排是不是就行啦。
方向2：既然臨時表是影響group by性能的X因素，我們是不是可以不用臨時表？

我們一起來想下，執行group by語句為什麼需要臨時表呢？group by的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄并統計結果啦?

group by 後面的字段加索引
order by null 不用排序
盡量隻使用記憶體臨時表
使用SQL_BIG_RESULT

5.1 group by 後面的字段加索引

如何保證group by後面的字段數值一開始就是有序的呢？當然就是加索引啦。

我們回到一下這個SQL

select city ,count(*) as num from staff where age= 19 group by city;

它的執行計劃

如果我們給它加個聯合索引idx_age_city（age,city）

alter table staff add index idx_age_city(age,city);

再去看執行計劃，發現既不用排序，也不需要臨時表啦。

加合适的索引是優化group by最簡單有效的優化方式。

5.2 order by null 不用排序

并不是所有場景都适合加索引的，如果碰上不适合建立索引的場景，我們如何優化呢？

如果你的需求并不需要對結果集進行排序，可以使用order by null。

select city ,count(*) as num from staff group by city order by null

執行計劃如下，已經沒有filesort啦

5.3 盡量隻使用記憶體臨時表

如果group by需要統計的資料不多，我們可以盡量隻使用記憶體臨時表；因為如果group by 的過程因為記憶體臨時表放不下資料，進而用到磁盤臨時表的話，是比較耗時的。是以可以适當調大tmp_table_size參數，來避免用到磁盤臨時表。

5.4 使用SQL_BIG_RESULT優化

如果資料量實在太大怎麼辦呢？總不能無限調大tmp_table_size吧？但也不能眼睜睜看着資料先放到記憶體臨時表，随着資料插入發現到達上限，再轉成磁盤臨時表吧？這樣就有點不智能啦。

是以，如果預估資料量比較大，我們使用SQL_BIG_RESULT 這個提示直接用磁盤臨時表。MySQl優化器發現，磁盤臨時表是B+樹存儲，存儲效率不如數組來得高。是以會直接用數組來存

示例SQl如下：

select SQL_BIG_RESULT city ,count(*) as num from staff group by city;

執行計劃的Extra字段可以看到，執行沒有再使用臨時表，而是隻有排序