開發人員不得不知的MySQL索引和查詢最佳化

基數是資料列所包含的不同值的數量，例如，某個資料列包含值 1、3、7、4、7、3，那麼它的基數就是 4。

索引的基數相對於資料錶行數較高（也就是說，列中包含很多不同的值，重覆的值很少）的時候，它的工作效果最好。

如果某資料列含有很多不同的年齡，索引會很快地分辨資料行；如果某個資料列用於記錄性別（只有“M”和“F”兩種值），那麼索引的用處就不大；如果值出現的機率幾乎相等，那麼無論搜尋哪個值都可能得到一半的資料行。

在這些情況下，最好根本不要使用索引，因為查詢最佳化器發現某個值出現在表的資料行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是“30%”。

索引失效的原因有如下幾點：

• 對索引列運算，運算包括（+、-、*、/、！、<>、%、like’%_’（% 放在前面）。

• 型別錯誤，如欄位型別為 varchar，where 條件用 number。

• 對索引應用內部函式，這種情況下應該要建立基於函式的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此時應該建 ROUND (t.logicdb_id) 為索引。

  MySQL 8.0 開始支援函式索引，5.7 可以透過虛擬列的方式來支援，之前只能新建一個 ROUND (t.logicdb_id) 列然後去維護。

• 如果條件有 or，即使其中有條件帶索引也不會使用（這也是為什麼建議少使用 or 的原因），如果想使用 or，又想索引有效，只能將 or 條件中的每個列加上索引。

• 如果列型別是字串，那一定要在條件中資料使用引號，否則不使用索引。

• B-tree 索引 is null 不會走，is not null 會走，點陣圖索引 is null，is not null 都會走。

• 組合索引遵循最左原則。

索引的建立需要註意以下幾點：

• 最重要的肯定是根據業務經常查詢的陳述句。

• 儘量選擇區分度高的列作為索引，區分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示欄位不重覆的比率，比率越大我們掃描的記錄數就越少。

• 如果業務中唯一特性最好建立唯一鍵，一方面可以保證資料的正確性，另一方面索引的效率能大大提高。

EXPLIAN 基本用法如下：

• desc 或者 explain 加上你的 SQL。

• extended explain 加上你的 SQL，然後透過 show warnings 可以檢視實際執行的陳述句，這一點也是非常有用的，很多時候不同的寫法經 SQL 分析後，實際執行的程式碼是一樣的。

• 索引改寫(covering index)：需要查詢的資料在索引上都可以查到不需要回表 EXTRA 列顯示 using index。

• ICP特性(Index Condition Pushdown)：本來 index 僅僅是 data access 的一種訪問樣式，存數引擎透過索引回表獲取的資料會傳遞到 MySQL Server 層進行 where 條件過濾。

  5.6 版本開始當 ICP 開啟時，如果部分 where 條件能使用索引的欄位，MySQL Server 會把這部分下推到引擎層，可以利用 index 過濾的 where 條件在儲存引擎層進行資料過濾。

  EXTRA 顯示 using index condition。需要瞭解 MySQL 的架構圖分為 Server 和儲存引擎層。

• 索引合併(index merge)：對多個索引分別進行條件掃描，然後將它們各自的結果進行合併(intersect/union)。

  一般用 or 會用到，如果是 AND 條件，考慮建立複合索引。EXPLAIN 顯示的索引型別會顯示 index_merge，EXTRA 會顯示具體的合併演演算法和用到的索引。

Extra 欄位使用：

• using filesort：說明 MySQL 會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。

  MySQL 中無法利用索引完成的排序操作稱為“檔案排序”，其實不一定是檔案排序，內部使用的是快排。

• using temporary：使用了臨時表儲存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。

• using index：表示相應的 SELECT 操作中使用了改寫索引（Covering Index），避免訪問了表的資料行，效率不錯。

• impossible where：where 子句的值總是 false，不能用來獲取任何元組。

• select tables optimized away：在沒有 group by 子句的情況下基於索引最佳化 MIN/MAX 操作或者對於 MyISAM 儲存引擎最佳化 COUNT(*) 操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成最佳化。

• distinct：最佳化 distinct 操作，在找到第一匹配的元組後即停止找同樣值的操作。

using filesort、using temporary 這兩項出現時需要註意下，這兩項是十分耗費效能的。

在使用 group by 的時候，雖然沒有使用 order by，如果沒有索引，是可能同時出現 using filesort，using temporary 的。

因為 group by 就是先排序在分組，如果沒有排序的需要，可以加上一個 order by NULL 來避免排序，這樣 using filesort 就會去除，能提升一點效能。

type 欄位使用：

• system：表只有一行記錄（等於系統表），這是 const 型別的特例，平時不會出現。

• const：如果透過索引依次就找到了，const 用於比較主鍵索引或者 unique 索引。因為只能匹配一行資料，所以很快。如果將主鍵置於 where 串列中，MySQL 就能將該查詢轉換為一個常量。

• eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描。

• ref：非唯一性索引掃描，傳回匹配某個單獨值的所有行。本質上也是一種索引訪問，它傳回所有匹配某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應該屬於查詢和掃描的混合體。

• range：只檢索給定範圍的行，使用一個索引來選擇行。key 列顯示使用了哪個索引，一般就是在你的 where 陳述句中出現 between、、in 等的查詢。

  這種範圍掃描索引比全表掃描要好，因為只需要開始於縮印的某一點，而結束於另一點，不用掃描全部索引。

• index：Full Index Scan ，index 與 ALL 的區別為 index 型別只遍歷索引樹，這通常比 ALL 快，因為索引檔案通常比資料檔案小。

  也就是說雖然 ALL 和 index 都是讀全表，但 index 是從索引中讀取的，而 ALL 是從硬碟讀取的。

• all：Full Table Scan，遍歷全表獲得匹配的行。

CHARACTER_LENGTH(同CHAR_LENGTH)方法傳回的是字元數，LENGTH 函式傳回的是位元組數，一個漢字三個位元組。

select count(distinct left(test,5))/count(*) from table；越趨近 1 越好。

MySQL 的 utf8 最大是 3 個位元組不支援 emoji 表情符號，必須只用 utf8mb4。需要在 MySQL 配置檔案中配置客戶端字符集為 utf8mb4。

JDBC 的連線串不支援配置 characterEncoding=utf8mb4，最好的辦法是在連線池中指定初始化 SQL。

例如：hikari 連線池，其他連線池類似 spring . datasource . hikari . connection – init – sql =set names utf8mb4。否則需要每次執行 SQL 前都先執行 set names utf8mb4。

一般使用 _bin 和 _genera_ci：

• utf8_genera_ci 不區分大小寫，ci 為 case insensitive 的縮寫，即大小寫不敏感。

• utf8_general_cs 區分大小寫，cs 為 case sensitive 的縮寫，即大小寫敏感，但是目前 MySQL 版本中已經不支援類似於 ***_genera_cs 的排序規則，直接使用 utf8_bin 替代。

• utf8_bin 將字串中的每一個字元用二進位制資料儲存，區分大小寫。

那麼，同樣是區分大小寫，utf8_general_cs 和 utf8_bin 有什麼區別？

• cs 為 case sensitive 的縮寫，即大小寫敏感；bin 的意思是二進位制，也就是二進位制編碼比較。

• utf8_general_cs 排序規則下，即便是區分了大小寫，但是某些西歐的字元和拉丁字元是不區分的，比如 ä=a，但是有時並不需要 ä=a，所以才有 utf8_bin。

• utf8_bin 的特點在於使用字元的二進位制的編碼進行運算，任何不同的二進位制編碼都是不同的，因此在 utf8_bin 排序規則下：ä<>a。

SQLyog 中初始連線指定編碼型別使用連線配置的初始化命令，如下圖：

• 如果有主鍵或者唯一鍵衝突則不插入：insert ignore into。
  

• 如果有主鍵或者唯一鍵衝突則更新，註意這個會影響自增的增量：INSERT INTO room_remarks(room_id,room_remarks)VALUE(1,”sdf”) ON DUPLICATE KEY UPDATE room_remarks = “234”。
  

• 如果有就用新的替代，values 如果不包含自增列，自增列的值會變化：REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,”sdf”)。
  

• 備份表：CREATE TABLE user_info SELECT * FROM user_info。
  

• 複製表結構：CREATE TABLE user_v2 LIKE user。

• 從查詢陳述句中匯入：INSERT INTO user_v2 SELECT * FROM user 或者 INSERT INTO user_v2(id,num) SELECT id,num FROM user。
  

• 連表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id。
  

• 連表刪除：DELETE user FROM user,black WHERE user.id=black.id。

• 共享鎖：select id from tb_test where id = 1 lock in share mode。
  

• 排它鎖：select id from tb_test where id = 1 for update。

最佳化時用到： 

• 強制使用某個索引：select * from table force index(idx_user) limit 2。
  

• 禁止使用某個索引：select * from table ignore index(idx_user) limit 2。
  

• 禁用快取(在測試時去除快取的影響)：select SQL_NO_CACHE from table limit 2。

• 檢視字符集：SHOW VARIABLES LIKE ‘character_set%’。

• 檢視排序規則：SHOW VARIABLES LIKE ‘collation%’。

• where 陳述句的解析順序是從右到左，條件儘量放 where 不要放 having。

• 採用延遲關聯(deferred join)技術最佳化超多分頁場景，比如 limit 10000,10,延遲關聯可以避免回表。

• distinct 陳述句非常損耗效能，可以透過 group by 來最佳化。

• 連表儘量不要超過三個表。

踩坑總結如下：

• 如果有自增列，truncate 陳述句會把自增列的基數重置為 0，有些場景用自增列作為業務上的 ID 需要十分重視。

• 聚合函式會自動濾空，比如 a 列的型別是 int 且全部是 NULL，則 SUM(a) 傳回的是 NULL 而不是 0。

• MySQL 判斷 null 相等不能用 “a=null”，這個結果永遠為 UnKnown，where 和 having 中，UnKnown 永遠被視為 false，check 約束中，UnKnown 就會視為 true 來處理。所以要用“a is null”處理。

MySQL 在表資料量很大的時候，如果修改表結構會導致鎖表，業務請求被阻塞。

MySQL 在 5.6 之後引入了線上更新，但是在某些情況下還是會鎖表，所以一般都採用 PT 工具( Percona Toolkit)。

如對錶新增索引：

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)" 
D=fission_show_room_v2,t=room_favorite_info --execute

有時候如果線上請求超時，應該去關註下慢查詢日誌，慢查詢的分析很簡單，先找到慢查詢日誌檔案的位置，然後利用 mysqldumpslow 去分析。

查詢慢查詢日誌資訊可以直接透過執行 SQL 命令檢視相關變數，常用的 SQL 如下：

-- 檢視慢查詢配置
-- slow_query_log 慢查詢日誌是否開啟
-- slow_query_time 指定了慢查詢的閾值
-- long_query_time 指定了慢查詢的閾值
-- log_queries_not_using_indexes 是否記錄所有沒有利用索引的查詢
SHOW VARIABLES LIKE'%quer%';

--檢視慢查詢是日誌還是表的形式
SHOW VARIABLES LIKE'log_output'

-- 檢視慢查詢的數量
SHOW GLOBAL STATUS LIKE'solw_queries';

mysqldumpslow 的工具十分簡單，我主要用到的引數如下：

• -t：限制輸出的行數，我一般取前十條就夠了。

• -s：根據什麼來排序預設是平均查詢時間 at，我還經常用到 c 查詢次數，因為查詢次數很頻繁但是時間不高也是有必要最佳化的，還有 t 查詢時間，檢視那個陳述句特別卡。

• -v：輸出詳細資訊。

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500。

如果你執行了一個 SQL 的操作，但是遲遲沒有傳回，你可以透過查詢行程串列看看它的實際執行狀況。

如果該 SQL 十分耗時，為了避免影響線上可以用 kill 命令殺死行程，透過檢視行程串列也能直觀的看下當前 SQL 的執行狀態；如果當前資料庫負載很高，在行程串列可能會出現，大量的行程夯住，執行時間很長。

命令如下：

--檢視行程串列
SHOW PROCESSLIST;
--殺死某個行程
kill 183665

如果你使用的 SQLyog，那麼也有圖形化的頁面，在選單欄→工具→顯示→行程串列。

在行程串列頁面可以右鍵殺死行程，如下所示：

在對公司慢查詢日誌做最佳化的時候，很多時候可能是忘了建索引，像這種問題很容易解決，加個索引就行了。但是有幾種情況就不是簡單加索引能解決了：

考慮這樣一個場景，獲取使用者粉絲串列資訊，加入分頁是十個，其實像這樣的 SQL 是十分簡單的，透過連表查詢效能也很高。

但是有時候，很多開發採用了取出一串 ID，然後迴圈讀每個 ID 的資訊，這樣如果 ID 很多對資料庫的壓力是很大的，而且效能也很低。

很多時候，業務上都會有排行榜這種，發現公司有很多地方直接採用資料庫做計算，在對一些大表做聚合運算的時候，經常超過五秒，這些 SQL 一般很長而且很難最佳化。

像這種場景，如果業務允許（比如一致性要求不高或者是隔一段時間才統計的），可以專門在從庫裡面做統計。另外我建議還是採用 Redis 快取來處理這種業務。

在慢查詢日誌中發現了一些超大分頁的慢查詢如 Limit 40000，1000，因為 MySQL 的分頁是在 Server 層做的，可以採用延遲關聯在減少回表。

但是看了相關的業務程式碼正常的業務邏輯是不會出現這樣的請求的，所以很有可能是有惡意使用者在刷介面，最好在開發的時候也對介面加上校驗攔截這些惡意請求。