三高Mysql - Mysql索引和查詢優化（偏實戰部分）

實戰部分回挑選一些比較常見的情況，事先強調個人使用的是mysql 8.0.26，所以不同版本如果出現不同測試結果也不要驚訝，新版本會對於過去一些不會優化的查詢進行優化。

實戰部分承接上一篇文章：三高Mysql - Mysql索引和查詢優化講解（偏理論部分） - 掘金 (juejin.cn)

前置準備

這裡還是要再囉嗦一遍，所有的資料庫和表均來自官方的sakila-db，作為學習和熟悉mysql資料庫操作非常好。

sakila-db

sakila-db是什麼？國外很火的一個概念，指的是國外電影租賃市場外國人使用租賃的方式進行電影的觀看，過去十分受外國人的喜歡，這裡拿出來介紹是因為後續的內容都用到了這個案例，所以我們需要提前把相關的環境準備好，從如下地址進行下載：

下載地址：https://dev.mysql.com/doc/ind...

work-bench

work-bench是官方開發的資料庫關係圖的視覺化工具，使用官方案例的具體關係圖展示效果如下，通過這些圖可以看到Sakila-db之間的大致關係：

work-bench 是免費軟體，下載地址如下：
https://dev.mysql.com/downloa...

安裝workbench和下載sakila-db的過程這裡不做記錄，在執行的時候需要注意先建立一個資料庫執行Sheme檔案，然後執行data的sql檔案，最終藉助navicat中檢視資料和表結構關係：

正文部分

where查詢太慢怎麼辦？

遇到where查詢太慢，我們第一步是需要分析資料型別的組成以及資料表的設定是否合理，其次我們可以使用explain對於查詢語句進行分析，使用方式十分簡單在需要優化的查詢語句前面新增explain語句，對於所有的查詢來說，覆蓋索引的查詢方式是最優解，因為覆蓋索引不需要回表查資料。

覆蓋索引：覆蓋索引是查詢方式，他不是一個索引，指的是在查詢返回結果的時候和使用的索引是同一個，這時候可以發現他壓根不需要回表，直接查輔助索引樹就可以得到資料，所以覆蓋索引的查詢效率比較高。

如何使用sql語句檢視某一個表的建表語句：
回答：使用show create table 表名稱即可。

那麼什麼情況下會使用覆蓋索引：

查詢欄位為輔助索引的欄位或者聚簇索引的欄位。
符合最左匹配原則，如果不是最左匹配則不能走索引。

我們使用上面提到的sakila-db進行實驗，這裡可以使用inventory表作為實驗，但是這個表需要進行一些調整，下面請看具體的sql：

CREATE TABLE `inventory_test` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  -- KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
  -- CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  -- CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我們將原始的sql建表語句只保留一個輔助索引，比如在上面的語句中刪除了idx_fk_film_id索引，下面刪除這個索引之後的實驗效果：

explain select * from inventory_test where film_id = 1;
-- 案例1. 不符合最左原則不走索引 
-- 1  SIMPLE  inventory_test    ALL          1  100.00  Using where

explain select * from inventory_test where store_id = 1;
-- 案例2: 使用了輔助索引（聯合索引）：
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  

explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例3:  正常使用索引 
-- 1 SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

explain select film_id,store_id from inventory_test where store_id = 1;
-- 案例4:  覆蓋索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

explain select film_id,store_id from inventory_test where film_id = 1;
-- 案例5: 正常使用索引，但是type存在區別 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index

explain select inventory_id,film_id,store_id from inventory_test where film_id = 1;
-- 案例6: 使用索引返回結果，但是type存在區別 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index

explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例7: 覆蓋索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

案例1和案例2是較為典型的索引最左匹配原則的錯誤使用反面教材，也是很多新手建立索引但是可能用錯的陷阱之一，最左匹配原則指的是where條件需要從建立索引的最左列開始進行搜尋，可以看到這裡的星號和建表的時候欄位的順序是一樣的，也就是inventory_id，film_id,store_id，last_update，所以是雖然是select *但是是正常走索引的。

（實際幹活時候千萬不要這麼做，這裡是為了演示偷懶而已）

不用星號我使用亂序的列查詢會怎麼樣，其實這時候如果你把查詢列的資料換一下會.....沒啥影響，隨意調換查詢列順序依然可以走索引。

接下來是案例3 - 案例7的幾個查詢，這幾個查詢意圖解釋的是針對覆蓋索引使用的細節問題，在上面的測試案例語句當中可以看到案例4由於查詢的結果和where條件都是使用了索引的，所以最終mysql使用了完整的覆蓋索引，同時符合聯合索引的最左匹配原則，所以查詢的效率達到了ref級別（這個級別暫時簡單理解就是非常快就行）。

接著案例5又把where條件換了一下，可以看到雖然還是走了索引，但是效率一下子就低了下來，因為他不符合最左匹配原則，另外這個案例5的查詢級別可以理解為它需要把整個輔助索引也就是聯合索引的樹掃完再去進行where篩選，效率自然就不如直接檢索排序索引值快了，但是index這個級別還是比ALL這個龜速快不少。

理解了上面的這一層意思，再來理解案例6和7就很簡單了，可以看到只多了一個主鍵列查詢。

這裡讀者可能會覺得你這上面不是說返回結果全是索引列才會覆蓋麼，怎麼加入了主鍵列還是奏效呢？主鍵不是在聚簇索引上嘛不是需要回表麼？其實這兩個問題很好回答，因為輔助索引當中key儲存的確實是索引列的值，但是他的索引值放的是主鍵ID，當mysql在搜尋索引列的時候發現這裡多了一個列，但是又發現這個列是主鍵，所以最後發現可以直接通過聯合索引直接返回結果不需要回表，所以這樣覆蓋索引的條件同樣是成立的。

如果讀者不清楚查詢explain結果列代表的含義，可以參考下面的內容對比：

id: 首先，一個select就會出現一個id, 通常在複雜的查詢裡面會包含多張表的查詢，比如join, in等等
select_type：這個表示的是查詢的型別
table：表名稱
partitions：這個表示表空間，分割槽的概念
type : 比如查詢的優化等級, const, index, all，分別代表了聚簇索引，二級索引(輔助索引)，全表掃描的查詢搜尋方式
Possiblekeys：和type一樣確定訪問方式，確定有哪些索引可以選擇，
key：確定有哪些可以提供選擇，同時提供索引的對應長度
key_len：表示的是索引的長度
ref：等值匹配的時候出現的一些匹配的相關資訊
Rows：預估通過所索引或者別的方式讀取多少條資料
filtered：經過搜尋條件過濾之後的剩餘資料百分比。
extra：額外的資訊不重要，主要用於使用者判定查詢走了什麼索引。

總結

通過上面的案例我們可以從下面的角度思考來如何提升索引查詢速度：

使用覆蓋索引查詢方式提高效率，再次強調覆蓋索引不是索引是優化索引查詢一種方式。
如果資料不只使用索引列那麼就構不成覆蓋索引。
可以優化sql語句或者優化聯合索引的方式提高覆蓋索引的命中率。

如何確認選擇用什麼索引？

這裡涉及一個索引基數（cardinality）的問題，索引基數是什麼，其實就是利用演算法和概率學統計的方式確定最優化的索引方案，這個值可以通過show index from 表名的方式進行獲取，比如下面的200和121就是索引基數（cardinality）。

因為索引基數的存在如果索引不符合我們到使用預期可以嘗試強制使用某索引。

> show index from actor;
actor  0  PRIMARY  1  actor_id  A  200        BTREE      YES  
actor  1  idx_actor_last_name  1  last_name  A  121        BTREE      YES

索引基數的定義官方文件的介紹：

下面一坨東西簡單來說就是mysql會根據基數的數值根據一定的演算法選擇使用索引，但是有時候如果查詢不能符合預期要求就需要強制使用索引了。

表列中不同值的數量。當查詢引用具有關聯索引的列時，每列的基數會影響最有效的訪問方法。
例如，對於具有唯一約束的列，不同值的數量等於表中的行數。如果一個表有一百萬行，但特定列只有 10 個不同的值，
則每個值（平均）出現 100,000 次。 SELECT c1 FROM t1 WHERE c1 = 50 等查詢因此可能會返回 1 行或大量行，
並且資料庫伺服器可能會根據 c1 的基數以不同方式處理查詢。

如果列中的值分佈非常不均勻，則基數可能不是確定最佳查詢計劃的好方法。例如，SELECT c1 FROM t1 WHERE c1 = x;
當 x=50 時可能返回 1 行，當 x=30 時可能返回一百萬行。在這種情況下，您可能需要使用索引提示來傳遞有關哪種
查詢方法對特定查詢更有效的建議。

基數也可以應用於多個列中存在的不同值的數量，例如在複合索引中。
參考：列、複合索引、索引、索引提示、持久統計、隨機潛水、選擇性、唯一約束。

原文：
The number of different values in a table column. When queries refer to columns that have an 
associated index, the cardinality of each column influences which access method is most 
efficient. For example, for a column with a unique constraint, the number of different 
values is equal to the number of rows in the table. If a table has a million rows but 
only 10 different values for a particular column, each value occurs (on average) 100,000 times.
 A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of 
 rows, and the database server might process the query differently depending on the cardinality 
 of c1.

If the values in a column have a very uneven distribution, the cardinality might not be 
a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x;
 might return 1 row when x=50 and a million rows when x=30. In such a case, you might need 
 to use index hints to pass along advice about which lookup method is more efficient for a 
 particular query.

Cardinality can also apply to the number of distinct values present in multiple columns, 
as in a composite index.

See Also column, composite index, index, index hint, persistent statistics, random dive,
 selectivity, unique constraint.

如何讓sql強制使用索引

可以使用from表之後接條件語句：force index(索引) 的方式進行處理，使用強制索引的情況比較少，除非優化器真的選擇了不符合預期的優化規則並且嚴重影響查詢效能，使用強制索引的案例如下：

select * from actor force index(idx_actor_last_name);

count()慢的原因是什麼？

count函式不用多介紹，作用是查詢結果的行數，但是需要注意優化器在處理過程中會比對並且排除掉結果為null的值的資料，這意味著在行數很大的時候如果使用不正確count會因為比對資料操作進而降低查詢效率。

所以這裡我們只要記住一個特定的規則，那就是隻要是涉及行數的查詢，那就使用select(*)，原因僅僅是mysql官方針對這個做了專門的優化，也不需要去糾結為什麼官方要給select(*)做優化，只能說約定大於配置，下面是常見的查詢效能之間的對比：

count(非索引欄位)：理論上是最慢的，因為對於每一行結果都要判斷是否為null。
count(索引欄位)：雖然走了索引，但是依然需要對每一行結果判斷是否為null。
count(1)：雖然不涉及欄位了，但是這種方式依然需要對1進行判斷是否為null。
count(*)：Mysql官方進行優化，查詢效率最快，只需要記住這種方式即可。

索引下推

索引下推實現版本為Mysql5.6以上。

作用：本質上是為了減少輔助索引（或者說二級索引）回表次數的一種優化手段。

案例：請看下面的建表語句，這裡比較關鍵的是建立了store_id和film_id的聯合索引。

以下面的SQL語句為例，如果是5.6之前的版本雖然他是覆蓋索引的查詢方式但卻是不能使用索引的，資料進過索引查詢之後雖然store_id是順序排序的但是film_id是亂序的，在索引檢索的時候由於沒有辦法順序掃描（如果不清楚索引組織結構可以多看幾遍B+樹索引構造）它需要一行行使用主鍵回表進行查詢，查詢實際需要使用每一行的inentory_id回表4次去匹配film_id是否為3。

select * from inventory_3 where store_id in (1,2) and film_id = 3;

按照人的思維看起來是很不合理的，因為我們可以發現按照正常的邏輯有一種搜尋的方法是通過“跳躍“索引的方式進行掃描，當掃描到索引列如果不符合條件，則直接跳躍索引到下一個索引列，有點類似我們小時候”跳房子“方式來尋找自己需要的沙袋（索引資料）。

那麼索引下推是如何處理上面這種情況的呢？雖然film_id是沒有辦法順序掃描的也不符合索引的排列規則，但是發現可以根據遍歷film_id彙總索引之後再回表查呀！比如根據查詢條件搜尋遍歷找到film=3之後再根據二級索引列對應的主鍵去查主索引，這時候只需要一次回表就可以查到資料，此時原本應該根據每個二級索引的主鍵值進行回表變為遍歷索引並找到索引值之後再回表，最終達到減少回表次數的效果，這也是前面為什麼說索引下推是為了減少了回表的次數的答案。

索引下推的開啟和關閉可以參考如下命令：

-- 索引下推變數值：
mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

-- 關閉索引下推
set optimizer_switch='index_condition_pushdown=off';
-- 開啟索引下推
set optimizer_switch='index_condition_pushdown=on';

鬆散索引和緊湊索引

關於鬆散索引和緊湊索引可以看下面兩個文件對比參考閱讀：

MySql 中文文件 - 8.2.1.15 GROUP BY 最佳化 | Docs4dev

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.17 GROUP BY Optimization

鬆散索引和緊湊索引的概念不是特別好理解，鬆散索引和緊湊索引實際上就是當MySQL 利用索引掃描來實現GROUP BY的時候，並不需要掃描所有滿足條件的索引鍵即可完成操作得出結果，僅僅處理的情況細節不同。

過去Mysql對於group by操作是構建臨時表並且在臨時表上操作，在使用索引的情況下，分組查詢是可以走索引的：

explain select last_name from actor  GROUP BY last_name
-- 1    SIMPLE    actor        index    idx_actor_last_name    idx_actor_last_name    182        200    100.00    Using index

由於group by 操作和order by操作不走索引的時候可能會產生臨時表，同時group by 操作擁有和order by 類似的排序操作，有時候我們分組查詢不止一個欄位，所以可能會出現多列索引情況，所以此時mysql對於多列聯合索引分組查詢進一步優化，提供了鬆散索引和緊湊索引多概念，

鬆散索引在官方有下面的定義：

當徹底使用索引掃描實現group by操作的時候，只需要使用部分的索引列就可以完成操作
雖然Btree的二級索引內部是排序並且要求索引是順序訪問的，但是對於group by最大的優化是掃描這種順序索引的時候where條件沒必要完全貼合所有索引key，

上面定義有兩個個關鍵詞：徹底和不完全，where條件沒必要完全貼合索引鍵。為了更好理解我們這裡使用了官方給的例子，假設在 tablet1(c1,c2,c3,c4)上有一個索引idx(c1,c2,c3)。鬆散索引掃描訪問方法可用於以下查詢：

-- 可以不使用所有索引欄位，可以走聯合索引
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
-- 去重操作內部也會進行隱式的分組行為
SELECT DISTINCT c1, c2 FROM t1;
-- 分組的極值查詢可以使用鬆散索引，因為c2和c1依然有序
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
-- 分組前的where 條件
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-- 對於c3的極值操作依然和c1,c2構成索引
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
-- 支援範圍查詢的同時走鬆散索引
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-- 最後一列等值查詢依然可以視為鬆散索引
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;    
-- 鬆散索引可以作用於下面的查詢
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

鬆散索引需要滿足下面的條件：

分組查詢是單表查詢
group by的條件必須同一個索引順序索引的連續位置。
group by的同時只能使用max或者min兩個聚合函式（但是在5.5之後，新增了更多函式支援）。
如果應用group by以外欄位條件必須用常量形式存在。
必須使用完整的索引值，也就意味著like這樣的字首索引是不適用的。

如果想要判定查詢是否使用鬆散索引可以根據explain的extra內容是否為Using index for group-by確認。

下面我們用更實際SQL來介紹，假設在 tablet1(c1,c2,c3,c4)上有一個索引idx(c1,c2,c3)。鬆散索引掃描訪問方法可用於以下查詢：


-- 自我實驗：鬆散索引
EXPLAIN SELECT COUNT(DISTINCT film_id, store_id), COUNT(DISTINCT store_id, film_id) FROM inventory_test;
-- 1  SIMPLE  inventory_test    range  idx_store_id_film_id  idx_store_id_film_id  3    4  100.00  Using index for group-by (scanning)
-- 自我實驗：鬆散索引
EXPLAIN SELECT COUNT(DISTINCT store_id), SUM(DISTINCT store_id) FROM inventory_test;
-- 1  SIMPLE  inventory_test    range  idx_store_id_film_id  idx_store_id_film_id  1    4  100.00  Using index for group-by (scanning)

-- 但是如果查詢的不是同一個索引，不滿足最左原則是不走鬆散索引的，而是走更快的索引掃描：
EXPLAIN SELECT COUNT(DISTINCT store_id), SUM(DISTINCT store_id) FROM inventory_test;
EXPLAIN SELECT COUNT(DISTINCT film_id), SUM(DISTINCT film_id) FROM inventory_test;
-- 1    SIMPLE    inventory_test        range    idx_store_id_film_id    idx_store_id_film_id    1        4    100.00    Using index for group-by (scanning)
-- 1    SIMPLE    inventory_test        index    idx_store_id_film_id    idx_store_id_film_id    3        3    100.00    Using index

緊湊索引

和鬆散索引區別的是緊湊索引使用前提是必須是全索引掃描或者範圍索引掃描，當鬆散索引沒有生效時使得group by 依然有可能避免建立臨時表，緊湊索引需要讀取所有滿足條件的索引鍵才會工作，然後根據讀取的資料完成group by 操作。

為了使緊湊索引查詢這種方法奏效在查詢中的所有列都要有恆定的相等條件，比如必須GROUP BY鍵之前或之間的部分鍵。

在緊湊索引掃描方式下，先對索引執行範圍掃描（range scan），再對結果元組進行分組。為了更好的理解，可以看一下相關的案例：

在GROUP BY中存在一個缺口，但是它被條件c2='a'所覆蓋。

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY沒有以鍵的第一部分開始，但是有一個條件為這部分提供了一個常數。

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

我們按照官方給的案例實驗一下，首先是表結構，我們在下面表中建立聯合索引：

CREATE TABLE `inventory_test` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

下面是個人使用緊湊索引的案例，當where條件是常量值並且是針對索引的常量值的時候，group by就可以走索引，但是如果where條件是非索引欄位依然需要全表掃描，注意這裡group的欄位並不是按照聯合索引的最左字首處理的依然可以走索引，這就是mysql對於分組操作的一系列優化了。

-- 緊湊索引
EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where store_id = 1 GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where last_update > '2022-02-02 23:20:45' GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ALL  idx_store_id_film_id        3  33.33  Using where; Using temporary

EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where last_update = '2022-02-02 23:20:45' GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ALL  idx_store_id_film_id        3  33.33  Using where; Using temporary

建議讀者多讀一讀官方文件加深這兩個概念理解。

order by如何優化？

什麼是中間結果集？

對於常規的sort語句，由於需要對於搜尋的結果按照某一欄位進行大小排序，而為了讓這個操作順利完成，mysql會把這個操作放到硬碟或者記憶體完成。

排序的基本步驟和原理

對於涉及排序的語句，它的大致工作原理如下：

選取查詢欄位，根據where進行條件查詢。
查詢結果集生成sort_buffer，如果記憶體不夠，需要在硬碟建立中間表進行排序。
將中間表根據Order 欄位進行排序。
回表生成完整結果集，組裝返回結果。

中間結果集特點

如果中間表比較小則放到記憶體中，判定什麼時候會存在於記憶體中Mysql提供了sort_buffer_size的引數，它負責控制中間結果集的大小，如果優化記憶體需要調整降低這個引數值，但是如果想要優化查詢的時間，則需要調大這個引數。

回表生成完整結果集

回表生成完整結果集這個操作其實也不是總是執行的，會根據會話引數max_length_for_sort_data進行判斷，如果當前查詢小於這個數值，會生成一個全欄位中間表結果可以直接從全欄位中間表獲取，但是如果大於這個數值那麼就只會生成排序欄位+主鍵中間表（類似二級索引），所以這時候顯然查詢一遍是無法找到的，需要回表才能完成操作。

需要注意排序欄位+主鍵中間表看起來像是二級索引但是實際上和二級索引完全沒有關係，只是一個簡單列表需要反覆去主表獲取資料。

總結：全欄位中間表>max_length_for_sort_data>排序欄位+主鍵中間表，數值並不是越大越好越大越影響查詢效率。

排序查詢優化點

根本問題在於排序的結果是中間結果集，雖然結果集可以在記憶體中處理，但是他有最為本質的問題那就是中間表不存在索引並且導致索引失效，所以為了讓中間表可以走索引我們可以使用索引覆蓋的方式。

優化手段：索引覆蓋，也是最高效的處理方式。索引覆蓋可以跳過生成生成中間結果集，直接輸出查詢結果。

order by的欄位為索引（或者聯合索引的最左邊）。
其他欄位（條件、輸出）均在上述索引中。
索引覆蓋可以跳過中間結果集，直接輸出查詢結果。

什麼是索引覆蓋？
覆蓋索引：覆蓋索引是查詢方式而不是一個索引，指的是一個sql語句中包括查詢條件和返回結果均符合索引使用條件，當然在Mysql5.6之後增加索引下推，滿足下推條件的也可以走覆蓋索引。

比如下面的語句並不會生成中間結果集並且可以有效利用索引：

explain select film_id, title from film order by title;
-- 1    SIMPLE    film        index        idx_title    514        1000    100.00    Using index

總結：提升排序查詢速度

給order by欄位增加索引，或者where欄位使用索引，讓查詢可以走覆蓋索引的方式。
調整sort_buffer_size大小，或者調整max_length_for_sort_data的大小，讓排序儘量在記憶體完成。

函式操作索引失效的問題

通過下面的案例可以得知，如果我們對於索引的欄位進行了類似函式的操作那麼mysql會放棄使用索引，另外一種情況是日期函式比如month()函式也會使得索引失效。

小貼士：很多人以為函式操作是那些sum()，count()函式，實際上對於欄位的加減乘除操作都可以認為是函式操作，因為底層需要呼叫計算機的暫存器完成相關指令操作。另外這裡需要和簽名的索引下推和鬆散緊湊索引做區分，鬆散和緊湊索引針對分組操作索引優化，索引下推到了5.6才被正式引入。大多數舊版本的mysql系統是沒法享受使用函式操作同時還能走索引的。

-- sql1：對於索引欄位進行函式操作
EXPLAIN SELECT
  title 
FROM
  film   
WHERE
  title + '22' = 'ACADEMY DINOSAUR' 
  AND length + 11 = 86;
  -- 1  SIMPLE  film    ALL          1000  100.00  Using where
  
-- sql2：如果對於其他欄位使用函式操作，但是索引欄位不進行 函式操作依然可以走索引
EXPLAIN SELECT
  title 
FROM
  film 
WHERE
  title  = 'ACADEMY DINOSAUR' 
  AND length + 11 = 86;
  -- 1  SIMPLE  film    ref  idx_title  idx_title  514  const  1  100.00  Using where

時間函式如何優化：

我們要如何優化時間函式呢？有一種比較笨的方式是使用 between and 替代，比如要搜尋5月份，就使用5月的第一天到5月的最後一天，具體的優化案例如下：

explain select last_update from payment where month(last_update) =2;
-- last_update需要手動建立索引
-- 1  SIMPLE  payment    ALL          16086  100.00  Using where

如果需要優化上面的結果，我們可以使用其他的方式替換寫法：

explain select * from payment where last_update between '2006-02-01' and '2006-02-28';
-- 1  SIMPLE  payment    ALL  idx_payment_lastupdate        16086  50.00  Using where

這裡很奇怪，咋和上面說的不一樣呢？其實是因為last_update這個欄位使用的資料型別是timestamp，而timestamp在進行搜尋的時候由於優化器的判斷會放棄使用索引！所以解決辦法也比較簡單：使用force index 讓SQL 強制使用索引。

explain select  * from payment force index(idx_payment_lastupdate) where last_update between '2006-02-01' and '2006-02-28' ;
-- 1  SIMPLE  payment    range  idx_payment_lastupdate  idx_payment_lastupdate  5    8043  100.00  Using index condition

這裡經過實驗發現如果欄位是datetime，就可以直接用Between and索引，對於時間戳型別並沒有實驗，僅從現有的表設計來看結果如下：

-- 優化後
-- 1  SIMPLE  rental    range  rental_date  rental_date  5    182  100.00  Using index condition
explain select * from rental where rental_date between '2006-02-01' and '2006-02-28';

-- 1  SIMPLE  rental    ALL          16008  100.00  Using where
explain select * from rental where  month(rental_date) =2;

字元和數字比較：

字元和數字比較也是會出現函式轉化的同樣會導致索引失效，所以在等式匹配的時候需要確保被比較的型別左右兩邊一致，另外如果無法修改查詢可以使用cast函式進行補救，比如像下面這樣處理。

select * from city where cast(city_id as SIGNED int) = 1;

隱式字元編碼轉化：

如果兩個表欄位的編碼不一樣，也會出現索引失效的問題，因為底層需要對於編碼進行轉化，解決方式也比較簡單，在比較的時候，同時儘量比較字串保證編碼一致。那麼假設兩張表比較的時候，那個表的欄位需要轉化呢，比如A表的utf8和B表utf8mb4，A表中欄位需要和B表欄位進行比較的時候，需要將A表的欄位轉為和 B表的欄位一致。

這個就偷懶不實驗了，絕大多數情況下表的字符集編碼格式只要跟隨表級別基本不會出現不一致的問題......

order by rand()原理

select tilte, desciption from film order by rand() limit 1;
-- EXPLAIN select title, description from film order by rand() limit 1;
-- 1  SIMPLE  film    ALL          1000  100.00  Using temporary; Using filesort

rand()函式是十分耗費資料庫效能的函式，在日常使用過程中我們可能遇到需要臨時獲取一條資料的情況，這時候就有可能會使用rand()函式，下面是rand()函式的執行原理：

建立一個臨時表，臨時表欄位為rand、title、description。
從臨時表中獲取一行，呼叫rand()，把結果和資料放入臨時表，以此類推。
針對臨時表，把rand欄位+行位置（主鍵）放入到sort_buffer。

可以看到這裡最大的問題是出現了兩次中間結果集。

針對此問題可以使用下面的臨時方案進行處理，這個臨時方案可以看作是把rand()內部的工作拆開來進行處理，也是在不改動業務的情況下一種比較“笨”的解決方式：

select max(film_id),min(film_id) into @M,@N from film;
set @x=FLOOR((@M-@N+1) * rand() + @N);
EXPLAIN select title,description from film where film_id >= @X limit 1;

其他處理方式是使用業務和邏輯程式碼替代sql的內部處理，比如使用下面的方式進行處理：

查詢資料表總數 total。
total範圍內，隨機選取一個數字r。
執行下列的SQL：

select title,description from film limit r,1;

小結：

order by rand() limit 這個查詢的效率極其低下，因為他需要生成兩次中間表才能獲取結果，謹慎使用此函式。
解決方案有兩種：
- 臨時解決方案：在主鍵的最大值和最小值中選取一個。
- 好理解的方式處理：業務程式碼加limit處理
優點：在不改變業務的情況下直接通過調整SQL
缺點：模板程式碼比較難以記憶，並且並不是萬能的，因為可能不給你相關許可權
建議使用業務邏輯程式碼處理不使用rand()函式。

分頁查詢慢怎麼辦？

再次注意這裡實驗的時候使用的資料庫版本為8.0.26。

我們首先來看一下《高效能Mysql 第三版》 241-242頁怎麼說的，作者使用的也是sakila表，推薦的方式是使用延遲關聯的方法，比如把下面的sql進行優化：

-- 優化前
select film_id,description from film order by title limit 50,5;

-- 優化後
select film_id,description from film inner join (select film_id from film order by title limit 50, 5) as lim using(film_id)

第二種方式是當id符合某種排序規則並且業務剛好符合的時候可以使用between ...and替代

select * from film where film_id between 46 and 50 order position;

最後還有一種方式是利用排序的特性將資料排序之後獲取前面的行即可：

select * from film where film_id order position desc limit 5;

以上是關於《高效能Mysql 第三版》部分的介紹。下面來看下我們是否還有其他的辦法？

深分頁問題不管是面試還是日常開發中經常會遇到的問題，這和limit的語法特性有關，可以看下面的內容：

select * from film limit x,y;

limit的語句的執行順序如下：

先按照列查詢出所有的語句，如果有where語句則根據where查詢出資料
查詢資料並且加入結果集直到查詢到（x+y）條資料為止。
丟棄掉前面的x條，保留y條。
返回剩下的y條資料。

針對limit我們有下面的優化和處理方案：

1. 簡單優化：

如果主鍵是int自增並且主鍵是邏輯符合業務自增的，那麼我們可以使用下面的語句進行優化：

select * from film where id >= 10000 limit y;

2. 子查詢優化：

自查詢的優化方式是減少回表次數的一種方式，我們可以使用自查詢的方式，由於不同業務之間存在不同的處理方式，這裡給一個大致的處理模板：

select * from film where ID in (select id from film where title = 'BANG KWAI') limit 10000,10

這樣處理過後有兩個優點：

查詢轉為搜尋索引列，並且不需要磁碟IO。
雖然使用的是子查詢，但是因為搜尋的是索引列，所以效率還是比較高的。

3. 延遲關聯

和《高效能Mysql》的方式一樣，其實就是子查詢方式的一種優化版本，優化的思路也是把過濾資料變為走索引之後在進行排除，由於上文已經介紹過這裡就不再贅述了。

總結：

對於深分頁的問題我們一般有下面的優化思路：

如果主鍵符合自增或者符合業務排序，可以直接通過id>xxx 然後limit搜尋資料。
如果通過排序可以正確搜尋相關資料，則可以直接排序之後取條數即可。
延遲關聯，延遲關聯有兩種方式，第一種是使用in的子查詢，第二種是使用inner join，本質都是通過索引列的方式避免大資料的查詢，同時轉變為查索引的方式。
如果可以確認範圍，使用between and 替代。

總結

本節內容針對了一些實戰過程中可能經常遇到的一些問題處理進行闡述，其中稍微有些難度的部分在索引下推和緊湊索引部分，這些特性

參考資料

MySql 中文文件 - 8.2.1.15 GROUP BY 最佳化 | Docs4dev

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.17 GROUP BY Optimization

三高Mysql - Mysql索引和查詢優化（偏實戰部分）

三高Mysql - Mysql索引和查詢優化（偏實戰部分）

前置準備

sakila-db

work-bench

正文部分

where查詢太慢怎麼辦？

如何確認選擇用什麼索引？

count()慢的原因是什麼？

索引下推

鬆散索引和緊湊索引

order by如何優化？

函式操作索引失效的問題

order by rand()原理

分頁查詢慢怎麼辦？

總結

參考資料

相關文章