一 問題重現
夜黑風高的某一晚，突然收到一條運營後臺資料庫慢sql的報警，耗時竟然達到了60s。
看了一下，還好不是很頻繁，內心會更加從容排查問題，應該是特定條件下沒有走到索引導致，如果頻繁出現慢查詢，可能會將資料庫連線池打滿，導致資料庫不可用，從而導致應用不可用。
二 問題排查
報警自帶定位慢sql語句，這個是很早就上線的一條sql語句，下面對sql語句進行了簡化：
select * from xxx where gear_id=xxx and examine=xxx order by id desc limit 10，這是個簡單的根據流量池gear_id查詢，按照主鍵id倒序進行分頁查詢10條資料的語句。
在examine=2時查詢速度很快，但是在examine=3時，查詢速度極慢，然後分別在不同的examine下檢視執行計劃，得到的執行計劃都是一致的。
檢視執行計劃，發現possible_keys中有idx_gear_id索引，但是實際用到的key卻是PRIMARY,並且extra中明確用了where條件進行資料過濾。到現在就明白了這個sql是在主鍵聚簇索引上進行掃描，然後用where語句條件進行過濾，時間耗費在這了。
這個也解釋了為什麼examine在不同狀態下的耗時不一樣，取決於where過濾掃描的行數，掃描行數越多，執行越慢，但同一個問題是都沒走到我們已有的索引idx_gear_id。
當單表數量較小時，無論有沒有索引，或者走主鍵索引掃描或者普通索引都很快，很容易忽略這些問題，此時的表現就是你好，我好，大家好，然後隨著資料量的增大，當達到千萬級別或者億級時，慢查詢的問題就凸顯出來了。
三 原理剖析
為什麼mysql會選擇這個不合適的主鍵聚簇索引?
以常用的InnoDb儲存引擎為例，看一下聚簇索引和非聚簇索引查詢區別:
聚簇索引：通常就是按照每張表的主鍵構造一顆B+樹，葉子節點中存放的就是整張表的行記錄資料，即資料和主鍵都在索引上
非聚簇索引：表的二級索引欄位（比如唯一索引，聯合索引等）構造的一顆B+樹，葉子節點儲存的是Key欄位+主鍵值，即非聚集索引的葉節仍然是索引節點，但它有一個指向最終資料索引的指標。
聚簇索引查詢原理：
非聚簇索引查詢原理（二級索引查詢）：
由以上的索引資料結構可以看出，因為聚簇索引將索引和資料儲存在同一個B+樹中，因此通常從聚簇索引中獲取資料比非聚簇索引更快，而非聚簇索引在獲取到葉子節點的主鍵後，需要再次查詢主鍵索引，即回表查詢行記錄資料。當然如果查詢的列只是索引欄位，比如查詢姓名和年齡，可以建立聯合索引，即索引儲存的內容即為需要查詢的內容，這種查詢速度往往比主鍵索引更快，這種索引查詢又稱為覆蓋索引。
什麼是回表？
將以上的索引資料對映成常見的使用者表user的索引為例,上面的聚簇索引就是以id欄位為主鍵的索引,name欄位為非聚簇索引，還有age等其他表欄位是非索引欄位，示例sql：select * from user where id = 1; 這條 SQL 語句就不需要回表。原因是根據主鍵的查詢方式，則只需要搜尋 id聚簇索引這棵 B+ 樹，就可以查到對應的資料。
但當我們使用非聚簇索引 name 這個索引來查詢 name = b 的記錄時就要用到回表。原因是透過 name 這個二級索引查詢方式，則需要先搜尋 name 索引樹，然後得到主鍵 id，即PK的值為 1，再到主鍵id聚簇索引樹再搜尋一次。這種根據二級索引查詢到主鍵id,再根據主鍵id查詢主鍵聚簇索引的過程就稱為回表。
回到為什麼mysql會選擇這個不合適的主鍵聚簇索引問題本身，mysql執行器認為使用二級索引查出來的資料太多了，還需要基於磁碟做臨時儲存進行排序，然後排序取出10條，然後進行回表查詢欄位，效能可能會很差，所以採用了直接採用了按順序掃描主鍵聚簇索引，和where條件gear_id=xxx and examine=xxx進行對比，最多放10條即可，這種情況就是數量小的時候沒問題，但是當資料量大的時候，就需要一直掃描所有的資料，直到查到符合where條件的10條資料為止，同時耗時也急劇增長。
四 解決問題
為了快速解決問題，可以採用強制索引force index，即在寫sql語句時指定使用具體的索引
sql示例 ：select * from xxx force index (idx_gear_id) where gear_id=xxx and examine=3 order by id desc limit 10,強制使用idx_gear_id這個索引。
以下為使用強制索引的執行計劃：
可以看到實際使用的索引key就是idx_gear_id,執行耗時在幾百毫秒，運營後臺的業務人員完全可接受。
五 長期最佳化
由於表的資料越來越多，查詢條件錯綜複雜，還有用json欄位查詢問題，決定將資料異構到es查詢，將json欄位打平，es天然支援複雜的查詢條件，查詢響應更快。
es資料同步方案：
在ES資料同步鏈路中，透過京東科技中介軟體DTS監聽資料庫的binlog，將索引欄位（查詢條件欄位）及業務唯一id寫入ES。
在業務運營查詢時，根據複雜的查詢條件，先去ES查詢，將業務唯一id查出，再根據業務唯一id去DB中查詢業務明細資料，同時解決了業務查詢的複雜性和查詢效能。
作者：京東科技 張石磊
來源：京東雲開發者社群 轉載請註明來源