原理到高性能查詢(xún)優(yōu)化實(shí)戰(zhàn))
1. 項(xiàng)目概述為什么我們總在談?wù)撍饕绻銓?xiě)過(guò)SQL尤其是處理過(guò)稍微有點(diǎn)規(guī)模的表大概率聽(tīng)過(guò)這樣的抱怨“這查詢(xún)?cè)趺催@么慢” 或者在某個(gè)深夜你盯著一個(gè)執(zhí)行時(shí)間長(zhǎng)達(dá)十幾秒的簡(jiǎn)單SELECT語(yǔ)句開(kāi)始懷疑人生。然后有經(jīng)驗(yàn)的老手會(huì)走過(guò)來(lái)輕飄飄地問(wèn)一句“加索引了嗎” 這句話(huà)幾乎成了數(shù)據(jù)庫(kù)性能調(diào)優(yōu)領(lǐng)域的“萬(wàn)能鑰匙”。今天我們就來(lái)徹底解構(gòu)這把鑰匙聊聊MySQL索引——這個(gè)號(hào)稱(chēng)優(yōu)化查詢(xún)速度的“不二法門(mén)”它到底是如何工作的我們又該如何正確地使用它而不是被它“坑”到。簡(jiǎn)單來(lái)說(shuō)索引就像一本書(shū)的目錄。沒(méi)有目錄你想找某個(gè)知識(shí)點(diǎn)只能一頁(yè)一頁(yè)翻全表掃描有了目錄你可以直接翻到對(duì)應(yīng)的頁(yè)碼通過(guò)索引定位。MySQL索引的核心價(jià)值就是通過(guò)額外的數(shù)據(jù)結(jié)構(gòu)最常見(jiàn)的是B樹(shù)為特定的列或列組合建立快速查找的路徑從而將數(shù)據(jù)檢索的時(shí)間復(fù)雜度從O(n)降低到O(log n)甚至O(1)。但索引并非免費(fèi)的午餐它需要占用額外的磁盤(pán)和內(nèi)存空間并在數(shù)據(jù)增刪改時(shí)帶來(lái)維護(hù)開(kāi)銷(xiāo)。因此理解索引、用好索引本質(zhì)上是一場(chǎng)在查詢(xún)速度與維護(hù)成本之間的精準(zhǔn)權(quán)衡。這篇文章適合所有與MySQL打交道的開(kāi)發(fā)者、DBA甚至是對(duì)數(shù)據(jù)庫(kù)性能感興趣的業(yè)務(wù)人員。無(wú)論你是正在被慢查詢(xún)困擾還是想未雨綢繆地設(shè)計(jì)高效表結(jié)構(gòu)理解索引的底層原理和最佳實(shí)踐都是你繞不開(kāi)的必修課。接下來(lái)我會(huì)從設(shè)計(jì)思路、核心原理、實(shí)操構(gòu)建到避坑指南帶你完整走一遍索引優(yōu)化的實(shí)戰(zhàn)之路。2. 索引的核心原理與數(shù)據(jù)結(jié)構(gòu)抉擇要玩轉(zhuǎn)索引不能只停留在“該加就加”的層面必須理解其內(nèi)部引擎是如何工作的。這決定了我們?yōu)楹芜x擇某種索引以及為何在某些場(chǎng)景下索引會(huì)“失效”。2.1 B樹(shù)MySQL索引的絕對(duì)主力MySQL的InnoDB存儲(chǔ)引擎默認(rèn)使用B樹(shù)作為索引的數(shù)據(jù)結(jié)構(gòu)尤其是聚簇索引Clustered Index和二級(jí)索引Secondary Index。為什么是B樹(shù)而不是哈希表、二叉樹(shù)或者B樹(shù)首先B樹(shù)是一種多路平衡查找樹(shù)。想象一下一棵非?！芭帧钡臉?shù)每個(gè)節(jié)點(diǎn)非葉子節(jié)點(diǎn)可以有很多個(gè)孩子。這種結(jié)構(gòu)使得樹(shù)的高度非常低。對(duì)于千萬(wàn)級(jí)甚至億級(jí)的表B樹(shù)的高度通常也只有3-4層。這意味著要找到任何一條數(shù)據(jù)最多只需要進(jìn)行3-4次磁盤(pán)I/O因?yàn)闃?shù)的一層通常對(duì)應(yīng)一次磁盤(pán)頁(yè)面讀取。磁盤(pán)I/O是數(shù)據(jù)庫(kù)操作中最耗時(shí)的部分減少I(mǎi)/O次數(shù)就是提升性能的關(guān)鍵。其次B樹(shù)的所有數(shù)據(jù)記錄或者說(shuō)行數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)并且葉子節(jié)點(diǎn)之間通過(guò)指針雙向鏈接。這帶來(lái)了兩大好處范圍查詢(xún)高效因?yàn)槿~子節(jié)點(diǎn)是鏈表連接的所以進(jìn)行WHERE column BETWEEN A AND B這類(lèi)范圍查詢(xún)時(shí)一旦找到起始點(diǎn)就可以順著鏈表順序掃描效率極高。這是哈希索引無(wú)法做到的。查詢(xún)穩(wěn)定性好由于所有查詢(xún)最終都要走到葉子節(jié)點(diǎn)所以任何一次查詢(xún)的I/O次數(shù)都是穩(wěn)定的都等于樹(shù)的高度。不會(huì)像二叉樹(shù)那樣在數(shù)據(jù)不平衡時(shí)退化成鏈表導(dǎo)致性能急劇下降。最后B樹(shù)的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值索引列的值和指向子節(jié)點(diǎn)的指針不存儲(chǔ)實(shí)際的行數(shù)據(jù)。這使得單個(gè)節(jié)點(diǎn)能容納更多的鍵值進(jìn)一步降低了樹(shù)的高度。注意MEMORY存儲(chǔ)引擎支持哈希索引它對(duì)于等值查詢(xún)非?？鞄缀跏荗(1)但不支持范圍查詢(xún)和排序。所以除非你的場(chǎng)景全是精準(zhǔn)匹配否則B樹(shù)是更通用、更可靠的選擇。2.2 聚簇索引與非聚簇索引數(shù)據(jù)的物理排列之謎這是理解MySQLInnoDB索引性能的關(guān)鍵分水嶺。聚簇索引決定了表中數(shù)據(jù)行的物理存儲(chǔ)順序。一張表有且只有一個(gè)聚簇索引。在InnoDB中如果你定義了主鍵PRIMARY KEY那么主鍵就是聚簇索引如果沒(méi)有定義主鍵InnoDB會(huì)選擇第一個(gè)所有列都不為NULL的唯一索引UNIQUE KEY作為聚簇索引如果還沒(méi)有InnoDB會(huì)隱式創(chuàng)建一個(gè)名為GEN_CLUST_INDEX的隱藏聚簇索引。聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是完整的數(shù)據(jù)行。這意味著當(dāng)你通過(guò)主鍵查詢(xún)時(shí)InnoDB在索引B樹(shù)的葉子節(jié)點(diǎn)上就直接拿到了所有數(shù)據(jù)無(wú)需二次查找這是最快的訪(fǎng)問(wèn)路徑。非聚簇索引或叫二級(jí)索引的葉子節(jié)點(diǎn)存儲(chǔ)的則不是完整數(shù)據(jù)行而是該索引鍵值 對(duì)應(yīng)行的主鍵值。例如你在user_name列上建了一個(gè)索引那么這棵B樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的是(user_name, id)這樣的對(duì)假設(shè)id是主鍵。這就引出了回表操作當(dāng)通過(guò)user_name索引查找到目標(biāo)記錄時(shí)得到的只是主鍵id為了獲取該行其他列的數(shù)據(jù)如email,ageInnoDB必須拿著這個(gè)id值回到聚簇索引的B樹(shù)中再查找一次。回表意味著額外的磁盤(pán)I/O是性能的主要損耗點(diǎn)之一。因此一個(gè)常見(jiàn)的優(yōu)化手段就是覆蓋索引。2.3 覆蓋索引避免回表的性能利器覆蓋索引不是一種新的索引類(lèi)型而是一種利用索引的優(yōu)化手段。如果一個(gè)索引包含了查詢(xún)語(yǔ)句所需要的所有字段那么MySQL就可以直接在索引的葉子節(jié)點(diǎn)拿到全部數(shù)據(jù)而無(wú)需回表。例如有一張用戶(hù)表users(id PK, user_name, age, city)并在(user_name, city)上建立了聯(lián)合索引。需要回表的查詢(xún)SELECT * FROM users WHERE user_name ‘Alice‘;雖然用到了(user_name, city)索引但SELECT *需要age等未包含在索引中的列所以必須回表。覆蓋索引查詢(xún)SELECT user_name, city FROM users WHERE user_name ‘Alice‘;查詢(xún)的字段user_name和city都包含在聯(lián)合索引中引擎直接在索引葉子節(jié)點(diǎn)就拿到了結(jié)果速度極快。在EXPLAIN分析SQL時(shí)如果Extra字段出現(xiàn)了Using index就表示使用了覆蓋索引這是查詢(xún)性能極佳的標(biāo)志。3. 索引類(lèi)型與適用場(chǎng)景深度解析知道了原理我們來(lái)看看MySQL給我們提供了哪些“武器”以及它們各自最適合的戰(zhàn)場(chǎng)。3.1 單列索引與聯(lián)合索引如何排列組合單列索引是最基礎(chǔ)的索引只針對(duì)一個(gè)列建立。它適用于WHERE、ORDER BY或GROUP BY子句中只涉及單個(gè)列的查詢(xún)。聯(lián)合索引復(fù)合索引則是針對(duì)多個(gè)列建立的索引例如INDEX idx_name_city (name, city)。它的核心規(guī)則是最左前綴匹配原則。這個(gè)原則意味著索引可以用于查詢(xún)條件中包含了索引最左邊連續(xù)一個(gè)或多個(gè)列的查詢(xún)。假設(shè)有聯(lián)合索引(A, B, C)能有效使用的查詢(xún)WHERE A1WHERE A1 AND B2WHERE A1 AND B2 AND C3WHERE A1 ORDER BY B。不能或不能完全使用的查詢(xún)WHERE B2跳過(guò)了最左的AWHERE A1 AND C3跳過(guò)了中間的BC字段無(wú)法利用索引的有序性進(jìn)行高效查找但A字段仍然可以用WHERE A1 AND B2范圍查詢(xún)A1之后B無(wú)法再以索引排序的方式被使用設(shè)計(jì)聯(lián)合索引時(shí)列的順序至關(guān)重要。一個(gè)經(jīng)驗(yàn)法則是將區(qū)分度最高唯一值最多的列放在左邊等值查詢(xún)的列放在范圍查詢(xún)的列左邊經(jīng)常用于排序或分組的列也要考慮放在索引中合適的位置。3.2 唯一索引與普通索引不僅僅是唯一性約束**唯一索引UNIQUE KEY**除了提供查詢(xún)優(yōu)化還強(qiáng)制了列值的唯一性約束。在插入或更新時(shí)MySQL需要檢查唯一性這會(huì)帶來(lái)一點(diǎn)點(diǎn)額外的開(kāi)銷(xiāo)。但更重要的是對(duì)于唯一索引在INSERT ... ON DUPLICATE KEY UPDATE或REPLACE INTO語(yǔ)句中它有特殊的行為邏輯。**普通索引INDEX或KEY**則沒(méi)有唯一性約束。在僅考慮查詢(xún)性能且不需要唯一性保證時(shí)普通索引是更輕量的選擇。這里有一個(gè)關(guān)于更新性能的經(jīng)典討論Change Buffer的優(yōu)化。對(duì)于非唯一索引當(dāng)需要更新一個(gè)不在InnoDB緩沖池Buffer Pool中的數(shù)據(jù)頁(yè)時(shí)InnoDB可以將這個(gè)更新操作緩存在Change Buffer中從而避免立即進(jìn)行昂貴的隨機(jī)磁盤(pán)I/O。等到未來(lái)某個(gè)時(shí)刻當(dāng)對(duì)應(yīng)的數(shù)據(jù)頁(yè)被讀入內(nèi)存時(shí)再將Change Buffer中的修改合并Merge進(jìn)去。這對(duì)于寫(xiě)多讀少的業(yè)務(wù)場(chǎng)景如日志系統(tǒng)性能提升顯著。而唯一索引因?yàn)橐⒓礄z查唯一性無(wú)法使用Change Buffer優(yōu)化。這是選擇普通索引而非唯一索引的一個(gè)深層性能考量點(diǎn)。3.3 全文索引與空間索引特殊場(chǎng)景的專(zhuān)用工具**全文索引FULLTEXT**用于解決文本內(nèi)容的模糊搜索問(wèn)題特別是LIKE ‘%keyword%‘這種無(wú)法使用前綴索引的低效查詢(xún)。在InnoDB中它有自己的倒排索引結(jié)構(gòu)支持自然語(yǔ)言模式和布爾模式搜索能對(duì)詞語(yǔ)進(jìn)行分詞和相關(guān)性評(píng)分。對(duì)于博客、文章、商品描述等文本搜索場(chǎng)景它是比LIKE高效得多的選擇。**空間索引SPATIAL**用于地理空間數(shù)據(jù)類(lèi)型如GEOMETRY,POINT。它基于R-Tree實(shí)現(xiàn)可以高效處理“查找附近的地點(diǎn)”、“判斷圖形是否相交”等空間查詢(xún)。這類(lèi)索引通常在使用MySQL進(jìn)行GIS應(yīng)用開(kāi)發(fā)時(shí)才會(huì)涉及。4. 索引創(chuàng)建與管理的實(shí)戰(zhàn)指南理論說(shuō)再多不如動(dòng)手建一個(gè)。但創(chuàng)建索引并非一勞永逸它需要持續(xù)的管理和優(yōu)化。4.1 如何創(chuàng)建合適的索引從SQL模式出發(fā)不要憑感覺(jué)創(chuàng)建索引而應(yīng)該從具體的、高頻的、慢的SQL語(yǔ)句出發(fā)。使用EXPLAIN或EXPLAIN FORMATJSON命令是第一步。-- 分析一個(gè)慢查詢(xún) EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status ‘shipped‘ ORDER BY create_time DESC;查看EXPLAIN輸出中的關(guān)鍵字段type訪(fǎng)問(wèn)類(lèi)型從優(yōu)到劣大致是system const eq_ref ref range index ALL。至少應(yīng)該達(dá)到range級(jí)別追求ref或const。key實(shí)際使用的索引。rows預(yù)估需要掃描的行數(shù)越少越好。Extra額外信息出現(xiàn)Using filesort文件排序或Using temporary使用臨時(shí)表通常意味著需要優(yōu)化。針對(duì)上面的查詢(xún)一個(gè)可能的優(yōu)化索引是(user_id, status, create_time)。這樣WHERE條件中的兩個(gè)等值查詢(xún)列都在最左并且ORDER BY的列也包含在索引中可能避免額外的排序操作如果create_time是降序創(chuàng)建索引時(shí)可以指定(user_id, status, create_time DESC)。創(chuàng)建索引的語(yǔ)法很簡(jiǎn)單-- 創(chuàng)建普通索引 CREATE INDEX idx_user_status ON orders(user_id, status); -- 創(chuàng)建唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 創(chuàng)建全文索引 CREATE FULLTEXT INDEX ft_content ON articles(content);4.2 索引的維護(hù)與重建何時(shí)該動(dòng)手索引會(huì)隨著數(shù)據(jù)的增刪改而產(chǎn)生碎片。碎片化嚴(yán)重的索引會(huì)占用更多空間并且降低查詢(xún)效率。如何判斷索引是否需要維護(hù)查看索引空間碎片率可以通過(guò)INFORMATION_SCHEMA.TABLES中的DATA_FREE等字段估算或使用SHOW TABLE STATUS LIKE ‘table_name‘。觀(guān)察查詢(xún)性能是否出現(xiàn)緩慢的、無(wú)原因的下降。維護(hù)操作主要有兩種優(yōu)化表OPTIMIZE TABLEOPTIMIZE TABLE your_table;這會(huì)重建表整理數(shù)據(jù)頁(yè)和索引頁(yè)的碎片。對(duì)于InnoDB表它相當(dāng)于執(zhí)行了ALTER TABLE ... FORCE是一個(gè)重量級(jí)、會(huì)鎖表的操作務(wù)必在業(yè)務(wù)低峰期進(jìn)行。重建索引ALTER TABLE ... DROP INDEX ADD INDEX對(duì)于非聚簇索引可以先刪除再重建。對(duì)于聚簇索引即主鍵重建意味著重建整個(gè)表代價(jià)更高。實(shí)操心得對(duì)于核心業(yè)務(wù)大表我通常會(huì)建立一個(gè)定期的、在低峰期執(zhí)行的維護(hù)窗口使用pt-online-schema-change或gh-ost等在線(xiàn)DDL工具進(jìn)行索引的增刪改以避免長(zhǎng)時(shí)間鎖表影響業(yè)務(wù)。對(duì)于碎片整理如果表非常大OPTIMIZE TABLE可能不現(xiàn)實(shí)有時(shí)選擇性重建部分關(guān)鍵索引是更可行的方案。4.3 索引的代價(jià)與選擇策略懂得取舍創(chuàng)建索引前必須權(quán)衡其代價(jià)空間代價(jià)每個(gè)索引都是一棵B樹(shù)需要占用磁盤(pán)空間。索引越多空間消耗越大。時(shí)間代價(jià)DML操作每次執(zhí)行INSERT、UPDATE、DELETE操作時(shí)MySQL不僅要更新數(shù)據(jù)還要更新所有相關(guān)的索引。索引越多寫(xiě)操作越慢。維護(hù)代價(jià)索引需要被監(jiān)控和維護(hù)。我的個(gè)人策略是優(yōu)先為高頻查詢(xún)的WHERE、ORDER BY、GROUP BY、JOIN ON條件列創(chuàng)建索引。使用聯(lián)合索引代替多個(gè)單列索引當(dāng)查詢(xún)經(jīng)常同時(shí)使用多個(gè)列時(shí)。控制索引數(shù)量。一張表的索引數(shù)量不宜過(guò)多例如超過(guò)5-6個(gè)就需要審視。對(duì)于寫(xiě)非常頻繁的表更要吝嗇地創(chuàng)建索引?？紤]使用前綴索引。對(duì)于很長(zhǎng)的字符串列如VARCHAR(255)可以只對(duì)前N個(gè)字符建立索引以節(jié)省空間。關(guān)鍵是選擇足夠長(zhǎng)的前綴以保證較高的區(qū)分度。ALTER TABLE table_name ADD INDEX idx_name (column_name(N));避免在區(qū)分度極低的列上建索引。例如“性別”列只有‘M‘/‘F‘兩個(gè)值建索引的收益幾乎為零優(yōu)化器很可能直接忽略它而選擇全表掃描。5. 高級(jí)優(yōu)化策略與執(zhí)行計(jì)劃深度解讀掌握了基礎(chǔ)我們進(jìn)入更深入的優(yōu)化層面理解優(yōu)化器如何選擇索引以及如何引導(dǎo)它做出最佳選擇。5.1 索引選擇性?xún)?yōu)化器選擇索引的核心依據(jù)索引選擇性Selectivity是指不重復(fù)的索引值基數(shù)Cardinality與表總記錄數(shù)#T的比值選擇性 基數(shù) / #T。選擇性越高越接近1索引的價(jià)值就越大。優(yōu)化器會(huì)根據(jù)預(yù)估的查詢(xún)成本來(lái)選擇索引而選擇性是成本估算的關(guān)鍵輸入。一個(gè)高選擇性的索引可以幫助過(guò)濾掉大部分?jǐn)?shù)據(jù)。你可以通過(guò)SHOW INDEX FROM your_table;查看索引的基數(shù)Cardinality這個(gè)值是采樣估算的有時(shí)可能不準(zhǔn)確可以使用ANALYZE TABLE your_table;來(lái)更新統(tǒng)計(jì)信息。5.2 索引下推ICP減少回表的神奇優(yōu)化索引下推是MySQL 5.6引入的一項(xiàng)重要優(yōu)化全稱(chēng)是Index Condition Pushdown。在沒(méi)有ICP的情況下存儲(chǔ)引擎通過(guò)索引檢索到數(shù)據(jù)返回給Server層再由Server層根據(jù)WHERE條件進(jìn)行過(guò)濾。有了ICP之后存儲(chǔ)引擎可以在取出索引的同時(shí)就根據(jù)索引中包含的列進(jìn)行條件判斷將不滿(mǎn)足條件的記錄直接過(guò)濾掉從而減少回表次數(shù)和返回給Server層的數(shù)據(jù)量。例如表t有聯(lián)合索引(zipcode, lastname, firstname)查詢(xún)?yōu)镾ELECT * FROM t WHERE zipcode‘95054‘ AND lastname LIKE ‘%etrunia%‘ AND address LIKE ‘%Main Street%‘;無(wú)ICP存儲(chǔ)引擎根據(jù)zipcode‘95054‘找到所有索引條目然后回表取出完整行交給Server層。Server層再過(guò)濾lastname和address。有ICP存儲(chǔ)引擎根據(jù)zipcode‘95054‘找到索引條目后在索引內(nèi)部就利用索引中包含的lastname列進(jìn)行LIKE ‘%etrunia%‘過(guò)濾注意這里lastname是范圍查詢(xún)但I(xiàn)CP仍然可以利用它進(jìn)行初步過(guò)濾。只將滿(mǎn)足zipcode和lastname條件的記錄的主鍵取出來(lái)回表最后再在Server層過(guò)濾address。這大大減少了回表次數(shù)。在EXPLAIN的Extra列中如果出現(xiàn)Using index condition就表示使用了ICP。5.3 多范圍讀MRR與批量鍵訪(fǎng)問(wèn)BKA這是另外兩項(xiàng)針對(duì)范圍查詢(xún)和關(guān)聯(lián)查詢(xún)的優(yōu)化。MRR對(duì)于范圍查詢(xún)傳統(tǒng)的做法是每從索引中拿到一個(gè)主鍵ID就立即回表讀取一行。MRR優(yōu)化會(huì)先將索引中掃描得到的主鍵ID放入緩沖區(qū)進(jìn)行排序然后按照主鍵順序去回表讀取數(shù)據(jù)。將隨機(jī)磁盤(pán)I/O轉(zhuǎn)變?yōu)楦樞虻腎/O可以顯著提升性能。EXPLAIN中Extra列顯示Using MRR。BKA是對(duì)MRR在關(guān)聯(lián)查詢(xún)JOIN中的延伸應(yīng)用。當(dāng)被驅(qū)動(dòng)表通常是右表可以使用索引進(jìn)行關(guān)聯(lián)時(shí)BKA會(huì)批量地將驅(qū)動(dòng)表左表關(guān)聯(lián)鍵值傳遞給被驅(qū)動(dòng)表利用MRR機(jī)制進(jìn)行批量檢索減少了對(duì)被驅(qū)動(dòng)表的訪(fǎng)問(wèn)次數(shù)。這些優(yōu)化通常由優(yōu)化器自動(dòng)判斷是否啟用在大多數(shù)情況下保持系統(tǒng)變量optimizer_switch中mrron和batched_key_accesson即可。6. 常見(jiàn)索引失效場(chǎng)景與排查實(shí)戰(zhàn)即使創(chuàng)建了索引查詢(xún)也可能沒(méi)有使用這就是所謂的“索引失效”。以下是實(shí)戰(zhàn)中最常踩的坑。6.1 導(dǎo)致索引失效的典型操作對(duì)索引列進(jìn)行運(yùn)算或函數(shù)操作WHERE YEAR(create_time) 2023會(huì)導(dǎo)致無(wú)法使用create_time上的索引。應(yīng)改為WHERE create_time ‘2023-01-01‘ AND create_time ‘2024-01-01‘。隱式類(lèi)型轉(zhuǎn)換如果列是字符串類(lèi)型VARCHAR但查詢(xún)寫(xiě)成了WHERE id 123id是字符串123是數(shù)字MySQL會(huì)進(jìn)行隱式轉(zhuǎn)換導(dǎo)致索引失效。務(wù)必保持類(lèi)型一致。使用OR連接非索引列WHERE indexed_column ‘A‘ OR non_indexed_column ‘B‘。如果OR一側(cè)的列沒(méi)有索引優(yōu)化器可能會(huì)選擇全表掃描?？梢钥紤]改寫(xiě)為UNION或分別查詢(xún)。LIKE以通配符開(kāi)頭WHERE name LIKE ‘%John‘無(wú)法使用name上的普通索引。如果必須這樣做考慮使用全文索引。WHERE name LIKE ‘John%‘則可以使用索引前綴匹配。不符合最左前綴原則如前所述對(duì)于聯(lián)合索引(A,B,C)查詢(xún)WHERE B1是無(wú)法使用該索引的。索引列參與比較在索引列上使用!、、NOT IN、NOT EXISTS時(shí)優(yōu)化器可能認(rèn)為需要掃描的數(shù)據(jù)量太大從而放棄索引。IS NULL和IS NOT NULL在某些情況下也可能導(dǎo)致索引失效取決于列中NULL值的比例。優(yōu)化器誤判當(dāng)表中數(shù)據(jù)量很少或者優(yōu)化器通過(guò)統(tǒng)計(jì)信息估算出使用索引的成本高于全表掃描時(shí)它會(huì)選擇不使用索引。這時(shí)可以使用FORCE INDEX提示強(qiáng)制使用索引但更根本的方法是更新統(tǒng)計(jì)信息ANALYZE TABLE。6.2 使用EXPLAIN進(jìn)行深度診斷EXPLAIN是你的最佳診斷工具。除了前面提到的字段還要關(guān)注possible_keys可能用到的索引。如果這里為空基本可以確認(rèn)查詢(xún)條件或表結(jié)構(gòu)有問(wèn)題。key_len實(shí)際使用的索引長(zhǎng)度?？梢詭湍闩袛嗍褂昧寺?lián)合索引的多少部分。例如一個(gè)INT列且非空在索引中長(zhǎng)度為4。如果key_len是4說(shuō)明只用了聯(lián)合索引的第一列。ref顯示索引的哪一列被用于查找。filtered存儲(chǔ)引擎層過(guò)濾后剩余記錄所占的百分比。這個(gè)值越接近100越好。一個(gè)更強(qiáng)大的工具是EXPLAIN FORMATJSON或EXPLAIN ANALYZEMySQL 8.0它們能提供更詳細(xì)的成本信息和實(shí)際執(zhí)行數(shù)據(jù)。6.3 索引失效排查清單當(dāng)遇到慢查詢(xún)時(shí)可以按以下清單快速排查檢查查詢(xún)條件是否有對(duì)索引列進(jìn)行計(jì)算、函數(shù)調(diào)用、類(lèi)型轉(zhuǎn)換檢查L(zhǎng)IKE語(yǔ)句通配符是否在開(kāi)頭檢查聯(lián)合索引查詢(xún)條件是否符合最左前綴原則檢查OR條件OR兩側(cè)的列是否都有索引使用EXPLAIN確認(rèn)索引是否被使用key字段掃描類(lèi)型type是否合理檢查數(shù)據(jù)分布是否因?yàn)閿?shù)據(jù)量太少或索引選擇性太低導(dǎo)致優(yōu)化器放棄索引執(zhí)行ANALYZE TABLE更新統(tǒng)計(jì)信息。檢查系統(tǒng)變量某些優(yōu)化如ICP、MRR是否被關(guān)閉7. 索引設(shè)計(jì)與優(yōu)化實(shí)戰(zhàn)案例剖析讓我們通過(guò)幾個(gè)具體的場(chǎng)景將前面的理論串聯(lián)起來(lái)。7.1 案例一電商訂單查詢(xún)優(yōu)化場(chǎng)景訂單表orders有數(shù)千萬(wàn)數(shù)據(jù)常見(jiàn)查詢(xún)1) 按用戶(hù)分頁(yè)查訂單2) 后臺(tái)按時(shí)間范圍、狀態(tài)查訂單。原始表結(jié)構(gòu)CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, amount DECIMAL(10,2), status TINYINT COMMENT ‘1待支付 2已支付 3已發(fā)貨 4已完成 5已取消‘, create_time DATETIME, update_time DATETIME ); -- 只有一個(gè)主鍵索引問(wèn)題查詢(xún)SELECT * FROM orders WHERE user_id ? ORDER BY create_time DESC LIMIT 0, 20非常慢。分析與優(yōu)化高頻查詢(xún)索引為(user_id, create_time)創(chuàng)建聯(lián)合索引。user_id用于快速定位用戶(hù)訂單create_time用于按時(shí)間排序并且索引本身有序可以避免ORDER BY帶來(lái)的文件排序Using filesort。CREATE INDEX idx_user_create ON orders(user_id, create_time DESC);注意在MySQL 8.0中可以指定索引的排序順序?yàn)镈ESC以更好地優(yōu)化ORDER BY ... DESC查詢(xún)。后臺(tái)查詢(xún)索引后臺(tái)查詢(xún)條件多變可能涉及status、create_time范圍等?？梢詣?chuàng)建(status, create_time)的聯(lián)合索引來(lái)覆蓋按狀態(tài)和時(shí)間篩選的查詢(xún)。如果status的選擇性不高可以將其放在后面。更復(fù)雜的查詢(xún)可能需要多個(gè)索引或根據(jù)最常用的查詢(xún)模式來(lái)設(shè)計(jì)。覆蓋索引嘗試如果前臺(tái)查詢(xún)只需要部分字段如id, user_id, status, amount, create_time可以考慮創(chuàng)建一個(gè)包含這些字段的聯(lián)合索引(user_id, create_time, status, amount)讓該查詢(xún)實(shí)現(xiàn)覆蓋索引性能達(dá)到極致。7.2 案例二社交平臺(tái)動(dòng)態(tài)流優(yōu)化場(chǎng)景動(dòng)態(tài)表feeds用戶(hù)關(guān)注很多人需要查詢(xún)“我關(guān)注的人發(fā)布的最新動(dòng)態(tài)”。原始查詢(xún)SELECT * FROM feeds WHERE author_id IN (SELECT followed_id FROM follows WHERE follower_id ?) ORDER BY publish_time DESC LIMIT 20;問(wèn)題IN子查詢(xún)效率可能不高尤其是關(guān)注人數(shù)多時(shí)。feeds表上如果只有author_id或publish_time的單列索引這個(gè)查詢(xún)會(huì)非常吃力。分析與優(yōu)化索引設(shè)計(jì)在feeds表上創(chuàng)建(author_id, publish_time DESC)的聯(lián)合索引。這樣對(duì)于IN列表里的每一個(gè)author_id都可以高效地按時(shí)間倒序取出其動(dòng)態(tài)。查詢(xún)改寫(xiě)有時(shí)可以將IN子查詢(xún)改為JOIN但在這個(gè)場(chǎng)景下核心瓶頸在于feeds表的索引。優(yōu)化后的索引能確保從每個(gè)作者取數(shù)據(jù)時(shí)都是高效的。更深層問(wèn)題如果用戶(hù)關(guān)注了上千人IN列表會(huì)很長(zhǎng)MySQL優(yōu)化器可能表現(xiàn)不佳。對(duì)于超大規(guī)模粉絲列表這種設(shè)計(jì)本身可能達(dá)到極限。此時(shí)需要考慮引入“推模式”或“推拉結(jié)合模式”將動(dòng)態(tài)預(yù)先聚合到用戶(hù)的個(gè)人時(shí)間線(xiàn)表中查詢(xún)就變成了簡(jiǎn)單的SELECT * FROM user_timeline WHERE user_id ? ORDER BY time DESC這是另一個(gè)架構(gòu)層面的優(yōu)化話(huà)題了。7.3 案例三避免過(guò)度索引與索引合并場(chǎng)景用戶(hù)表users在email、phone、username上分別建立了單列索引。一個(gè)查詢(xún)是SELECT id FROM users WHERE email ‘a(chǎn)b.com‘ OR phone ‘123456‘;問(wèn)題MySQL 5.0支持索引合并優(yōu)化。對(duì)于這個(gè)查詢(xún)優(yōu)化器可能會(huì)分別使用email索引和phone索引進(jìn)行掃描然后將結(jié)果合并Using union。這比全表掃描好但不如一個(gè)高效的聯(lián)合索引。分析與優(yōu)化識(shí)別索引合并EXPLAIN會(huì)顯示type為index_mergeExtra中顯示Using union(idx_email, idx_phone)。評(píng)估必要性索引合并通常是優(yōu)化器在缺少理想聯(lián)合索引時(shí)的補(bǔ)救措施。它的效率通常低于一個(gè)直接的聯(lián)合索引掃描因?yàn)樯婕皟纱嗡饕檎液徒Y(jié)果去重。優(yōu)化方案如果email和phone經(jīng)常在OR條件中同時(shí)出現(xiàn)可以考慮創(chuàng)建一個(gè)聯(lián)合索引(email, phone)或(phone, email)。但注意聯(lián)合索引對(duì)WHERE email ? AND phone ?的查詢(xún)友好對(duì)OR查詢(xún)不一定有效。更通用的優(yōu)化是審視業(yè)務(wù)邏輯看是否能將OR查詢(xún)拆分成兩個(gè)查詢(xún)通過(guò)應(yīng)用層或UNION來(lái)合并結(jié)果。有時(shí)維持兩個(gè)單列索引并接受索引合并可能是更靈活的選擇因?yàn)樗瑫r(shí)支持了email ?和phone ?的獨(dú)立查詢(xún)。索引的世界遠(yuǎn)不止于此還有自適應(yīng)哈希索引、不可見(jiàn)索引、降序索引等更多高級(jí)特性。但萬(wàn)變不離其宗核心永遠(yuǎn)是理解B樹(shù)的工作原理、聚簇/非聚簇索引的區(qū)別、最左前綴原則以及優(yōu)化器的成本模型。在實(shí)際工作中我習(xí)慣將索引優(yōu)化看作一個(gè)持續(xù)的迭代過(guò)程監(jiān)控慢查詢(xún)?nèi)罩居肊XPLAIN分析有針對(duì)性地創(chuàng)建或調(diào)整索引然后觀(guān)察效果。記住沒(méi)有銀彈最好的索引策略永遠(yuǎn)是貼合你的具體數(shù)據(jù)和查詢(xún)模式的策略。最后一個(gè)小建議在測(cè)試環(huán)境進(jìn)行大的索引變更前用真實(shí)數(shù)據(jù)量和查詢(xún)負(fù)載進(jìn)行基準(zhǔn)測(cè)試是避免生產(chǎn)事故的最后一重保險(xiǎn)。 MySQL索引深度解析：從B+樹(shù)原理到高性能查詢(xún)優(yōu)化實(shí)戰(zhàn)
1. 項(xiàng)目概述為什么我們總在談?wù)撍饕绻銓?xiě)過(guò)SQL尤其是處理過(guò)稍微有點(diǎn)規(guī)模的表大概率聽(tīng)過(guò)這樣的抱怨“這查詢(xún)?cè)趺催@么慢” 或者在某個(gè)深夜你盯著一個(gè)執(zhí)行時(shí)間長(zhǎng)達(dá)十幾秒的簡(jiǎn)單SELECT語(yǔ)句開(kāi)始懷疑人生。然后有經(jīng)驗(yàn)的老手會(huì)走過(guò)來(lái)輕飄飄地問(wèn)一句“加索引了嗎” 這句話(huà)幾乎成了數(shù)據(jù)庫(kù)性能調(diào)優(yōu)領(lǐng)域的“萬(wàn)能鑰匙”。今天我們就來(lái)徹底解構(gòu)這把鑰匙聊聊MySQL索引——這個(gè)號(hào)稱(chēng)優(yōu)化查詢(xún)速度的“不二法門(mén)”它到底是如何工作的我們又該如何正確地使用它而不是被它“坑”到。簡(jiǎn)單來(lái)說(shuō)索引就像一本書(shū)的目錄。沒(méi)有目錄你想找某個(gè)知識(shí)點(diǎn)只能一頁(yè)一頁(yè)翻全表掃描有了目錄你可以直接翻到對(duì)應(yīng)的頁(yè)碼通過(guò)索引定位。MySQL索引的核心價(jià)值就是通過(guò)額外的數(shù)據(jù)結(jié)構(gòu)最常見(jiàn)的是B樹(shù)為特定的列或列組合建立快速查找的路徑從而將數(shù)據(jù)檢索的時(shí)間復(fù)雜度從O(n)降低到O(log n)甚至O(1)。但索引并非免費(fèi)的午餐它需要占用額外的磁盤(pán)和內(nèi)存空間并在數(shù)據(jù)增刪改時(shí)帶來(lái)維護(hù)開(kāi)銷(xiāo)。因此理解索引、用好索引本質(zhì)上是一場(chǎng)在查詢(xún)速度與維護(hù)成本之間的精準(zhǔn)權(quán)衡。這篇文章適合所有與MySQL打交道的開(kāi)發(fā)者、DBA甚至是對(duì)數(shù)據(jù)庫(kù)性能感興趣的業(yè)務(wù)人員。無(wú)論你是正在被慢查詢(xún)困擾還是想未雨綢繆地設(shè)計(jì)高效表結(jié)構(gòu)理解索引的底層原理和最佳實(shí)踐都是你繞不開(kāi)的必修課。接下來(lái)我會(huì)從設(shè)計(jì)思路、核心原理、實(shí)操構(gòu)建到避坑指南帶你完整走一遍索引優(yōu)化的實(shí)戰(zhàn)之路。2. 索引的核心原理與數(shù)據(jù)結(jié)構(gòu)抉擇要玩轉(zhuǎn)索引不能只停留在“該加就加”的層面必須理解其內(nèi)部引擎是如何工作的。這決定了我們?yōu)楹芜x擇某種索引以及為何在某些場(chǎng)景下索引會(huì)“失效”。2.1 B樹(shù)MySQL索引的絕對(duì)主力MySQL的InnoDB存儲(chǔ)引擎默認(rèn)使用B樹(shù)作為索引的數(shù)據(jù)結(jié)構(gòu)尤其是聚簇索引Clustered Index和二級(jí)索引Secondary Index。為什么是B樹(shù)而不是哈希表、二叉樹(shù)或者B樹(shù)首先B樹(shù)是一種多路平衡查找樹(shù)。想象一下一棵非?！芭帧钡臉?shù)每個(gè)節(jié)點(diǎn)非葉子節(jié)點(diǎn)可以有很多個(gè)孩子。這種結(jié)構(gòu)使得樹(shù)的高度非常低。對(duì)于千萬(wàn)級(jí)甚至億級(jí)的表B樹(shù)的高度通常也只有3-4層。這意味著要找到任何一條數(shù)據(jù)最多只需要進(jìn)行3-4次磁盤(pán)I/O因?yàn)闃?shù)的一層通常對(duì)應(yīng)一次磁盤(pán)頁(yè)面讀取。磁盤(pán)I/O是數(shù)據(jù)庫(kù)操作中最耗時(shí)的部分減少I(mǎi)/O次數(shù)就是提升性能的關(guān)鍵。其次B樹(shù)的所有數(shù)據(jù)記錄或者說(shuō)行數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)并且葉子節(jié)點(diǎn)之間通過(guò)指針雙向鏈接。這帶來(lái)了兩大好處范圍查詢(xún)高效因?yàn)槿~子節(jié)點(diǎn)是鏈表連接的所以進(jìn)行WHERE column BETWEEN A AND B這類(lèi)范圍查詢(xún)時(shí)一旦找到起始點(diǎn)就可以順著鏈表順序掃描效率極高。這是哈希索引無(wú)法做到的。查詢(xún)穩(wěn)定性好由于所有查詢(xún)最終都要走到葉子節(jié)點(diǎn)所以任何一次查詢(xún)的I/O次數(shù)都是穩(wěn)定的都等于樹(shù)的高度。不會(huì)像二叉樹(shù)那樣在數(shù)據(jù)不平衡時(shí)退化成鏈表導(dǎo)致性能急劇下降。最后B樹(shù)的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值索引列的值和指向子節(jié)點(diǎn)的指針不存儲(chǔ)實(shí)際的行數(shù)據(jù)。這使得單個(gè)節(jié)點(diǎn)能容納更多的鍵值進(jìn)一步降低了樹(shù)的高度。注意MEMORY存儲(chǔ)引擎支持哈希索引它對(duì)于等值查詢(xún)非?？鞄缀跏荗(1)但不支持范圍查詢(xún)和排序。所以除非你的場(chǎng)景全是精準(zhǔn)匹配否則B樹(shù)是更通用、更可靠的選擇。2.2 聚簇索引與非聚簇索引數(shù)據(jù)的物理排列之謎這是理解MySQLInnoDB索引性能的關(guān)鍵分水嶺。聚簇索引決定了表中數(shù)據(jù)行的物理存儲(chǔ)順序。一張表有且只有一個(gè)聚簇索引。在InnoDB中如果你定義了主鍵PRIMARY KEY那么主鍵就是聚簇索引如果沒(méi)有定義主鍵InnoDB會(huì)選擇第一個(gè)所有列都不為NULL的唯一索引UNIQUE KEY作為聚簇索引如果還沒(méi)有InnoDB會(huì)隱式創(chuàng)建一個(gè)名為GEN_CLUST_INDEX的隱藏聚簇索引。聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是完整的數(shù)據(jù)行。這意味著當(dāng)你通過(guò)主鍵查詢(xún)時(shí)InnoDB在索引B樹(shù)的葉子節(jié)點(diǎn)上就直接拿到了所有數(shù)據(jù)無(wú)需二次查找這是最快的訪(fǎng)問(wèn)路徑。非聚簇索引或叫二級(jí)索引的葉子節(jié)點(diǎn)存儲(chǔ)的則不是完整數(shù)據(jù)行而是該索引鍵值 對(duì)應(yīng)行的主鍵值。例如你在user_name列上建了一個(gè)索引那么這棵B樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的是(user_name, id)這樣的對(duì)假設(shè)id是主鍵。這就引出了回表操作當(dāng)通過(guò)user_name索引查找到目標(biāo)記錄時(shí)得到的只是主鍵id為了獲取該行其他列的數(shù)據(jù)如email,ageInnoDB必須拿著這個(gè)id值回到聚簇索引的B樹(shù)中再查找一次。回表意味著額外的磁盤(pán)I/O是性能的主要損耗點(diǎn)之一。因此一個(gè)常見(jiàn)的優(yōu)化手段就是覆蓋索引。2.3 覆蓋索引避免回表的性能利器覆蓋索引不是一種新的索引類(lèi)型而是一種利用索引的優(yōu)化手段。如果一個(gè)索引包含了查詢(xún)語(yǔ)句所需要的所有字段那么MySQL就可以直接在索引的葉子節(jié)點(diǎn)拿到全部數(shù)據(jù)而無(wú)需回表。例如有一張用戶(hù)表users(id PK, user_name, age, city)并在(user_name, city)上建立了聯(lián)合索引。需要回表的查詢(xún)SELECT * FROM users WHERE user_name ‘Alice‘;雖然用到了(user_name, city)索引但SELECT *需要age等未包含在索引中的列所以必須回表。覆蓋索引查詢(xún)SELECT user_name, city FROM users WHERE user_name ‘Alice‘;查詢(xún)的字段user_name和city都包含在聯(lián)合索引中引擎直接在索引葉子節(jié)點(diǎn)就拿到了結(jié)果速度極快。在EXPLAIN分析SQL時(shí)如果Extra字段出現(xiàn)了Using index就表示使用了覆蓋索引這是查詢(xún)性能極佳的標(biāo)志。3. 索引類(lèi)型與適用場(chǎng)景深度解析知道了原理我們來(lái)看看MySQL給我們提供了哪些“武器”以及它們各自最適合的戰(zhàn)場(chǎng)。3.1 單列索引與聯(lián)合索引如何排列組合單列索引是最基礎(chǔ)的索引只針對(duì)一個(gè)列建立。它適用于WHERE、ORDER BY或GROUP BY子句中只涉及單個(gè)列的查詢(xún)。聯(lián)合索引復(fù)合索引則是針對(duì)多個(gè)列建立的索引例如INDEX idx_name_city (name, city)。它的核心規(guī)則是最左前綴匹配原則。這個(gè)原則意味著索引可以用于查詢(xún)條件中包含了索引最左邊連續(xù)一個(gè)或多個(gè)列的查詢(xún)。假設(shè)有聯(lián)合索引(A, B, C)能有效使用的查詢(xún)WHERE A1WHERE A1 AND B2WHERE A1 AND B2 AND C3WHERE A1 ORDER BY B。不能或不能完全使用的查詢(xún)WHERE B2跳過(guò)了最左的AWHERE A1 AND C3跳過(guò)了中間的BC字段無(wú)法利用索引的有序性進(jìn)行高效查找但A字段仍然可以用WHERE A1 AND B2范圍查詢(xún)A1之后B無(wú)法再以索引排序的方式被使用設(shè)計(jì)聯(lián)合索引時(shí)列的順序至關(guān)重要。一個(gè)經(jīng)驗(yàn)法則是將區(qū)分度最高唯一值最多的列放在左邊等值查詢(xún)的列放在范圍查詢(xún)的列左邊經(jīng)常用于排序或分組的列也要考慮放在索引中合適的位置。3.2 唯一索引與普通索引不僅僅是唯一性約束**唯一索引UNIQUE KEY**除了提供查詢(xún)優(yōu)化還強(qiáng)制了列值的唯一性約束。在插入或更新時(shí)MySQL需要檢查唯一性這會(huì)帶來(lái)一點(diǎn)點(diǎn)額外的開(kāi)銷(xiāo)。但更重要的是對(duì)于唯一索引在INSERT ... ON DUPLICATE KEY UPDATE或REPLACE INTO語(yǔ)句中它有特殊的行為邏輯。**普通索引INDEX或KEY**則沒(méi)有唯一性約束。在僅考慮查詢(xún)性能且不需要唯一性保證時(shí)普通索引是更輕量的選擇。這里有一個(gè)關(guān)于更新性能的經(jīng)典討論Change Buffer的優(yōu)化。對(duì)于非唯一索引當(dāng)需要更新一個(gè)不在InnoDB緩沖池Buffer Pool中的數(shù)據(jù)頁(yè)時(shí)InnoDB可以將這個(gè)更新操作緩存在Change Buffer中從而避免立即進(jìn)行昂貴的隨機(jī)磁盤(pán)I/O。等到未來(lái)某個(gè)時(shí)刻當(dāng)對(duì)應(yīng)的數(shù)據(jù)頁(yè)被讀入內(nèi)存時(shí)再將Change Buffer中的修改合并Merge進(jìn)去。這對(duì)于寫(xiě)多讀少的業(yè)務(wù)場(chǎng)景如日志系統(tǒng)性能提升顯著。而唯一索引因?yàn)橐⒓礄z查唯一性無(wú)法使用Change Buffer優(yōu)化。這是選擇普通索引而非唯一索引的一個(gè)深層性能考量點(diǎn)。3.3 全文索引與空間索引特殊場(chǎng)景的專(zhuān)用工具**全文索引FULLTEXT**用于解決文本內(nèi)容的模糊搜索問(wèn)題特別是LIKE ‘%keyword%‘這種無(wú)法使用前綴索引的低效查詢(xún)。在InnoDB中它有自己的倒排索引結(jié)構(gòu)支持自然語(yǔ)言模式和布爾模式搜索能對(duì)詞語(yǔ)進(jìn)行分詞和相關(guān)性評(píng)分。對(duì)于博客、文章、商品描述等文本搜索場(chǎng)景它是比LIKE高效得多的選擇。**空間索引SPATIAL**用于地理空間數(shù)據(jù)類(lèi)型如GEOMETRY,POINT。它基于R-Tree實(shí)現(xiàn)可以高效處理“查找附近的地點(diǎn)”、“判斷圖形是否相交”等空間查詢(xún)。這類(lèi)索引通常在使用MySQL進(jìn)行GIS應(yīng)用開(kāi)發(fā)時(shí)才會(huì)涉及。4. 索引創(chuàng)建與管理的實(shí)戰(zhàn)指南理論說(shuō)再多不如動(dòng)手建一個(gè)。但創(chuàng)建索引并非一勞永逸它需要持續(xù)的管理和優(yōu)化。4.1 如何創(chuàng)建合適的索引從SQL模式出發(fā)不要憑感覺(jué)創(chuàng)建索引而應(yīng)該從具體的、高頻的、慢的SQL語(yǔ)句出發(fā)。使用EXPLAIN或EXPLAIN FORMATJSON命令是第一步。-- 分析一個(gè)慢查詢(xún) EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status ‘shipped‘ ORDER BY create_time DESC;查看EXPLAIN輸出中的關(guān)鍵字段type訪(fǎng)問(wèn)類(lèi)型從優(yōu)到劣大致是system const eq_ref ref range index ALL。至少應(yīng)該達(dá)到range級(jí)別追求ref或const。key實(shí)際使用的索引。rows預(yù)估需要掃描的行數(shù)越少越好。Extra額外信息出現(xiàn)Using filesort文件排序或Using temporary使用臨時(shí)表通常意味著需要優(yōu)化。針對(duì)上面的查詢(xún)一個(gè)可能的優(yōu)化索引是(user_id, status, create_time)。這樣WHERE條件中的兩個(gè)等值查詢(xún)列都在最左并且ORDER BY的列也包含在索引中可能避免額外的排序操作如果create_time是降序創(chuàng)建索引時(shí)可以指定(user_id, status, create_time DESC)。創(chuàng)建索引的語(yǔ)法很簡(jiǎn)單-- 創(chuàng)建普通索引 CREATE INDEX idx_user_status ON orders(user_id, status); -- 創(chuàng)建唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 創(chuàng)建全文索引 CREATE FULLTEXT INDEX ft_content ON articles(content);4.2 索引的維護(hù)與重建何時(shí)該動(dòng)手索引會(huì)隨著數(shù)據(jù)的增刪改而產(chǎn)生碎片。碎片化嚴(yán)重的索引會(huì)占用更多空間并且降低查詢(xún)效率。如何判斷索引是否需要維護(hù)查看索引空間碎片率可以通過(guò)INFORMATION_SCHEMA.TABLES中的DATA_FREE等字段估算或使用SHOW TABLE STATUS LIKE ‘table_name‘。觀(guān)察查詢(xún)性能是否出現(xiàn)緩慢的、無(wú)原因的下降。維護(hù)操作主要有兩種優(yōu)化表OPTIMIZE TABLEOPTIMIZE TABLE your_table;這會(huì)重建表整理數(shù)據(jù)頁(yè)和索引頁(yè)的碎片。對(duì)于InnoDB表它相當(dāng)于執(zhí)行了ALTER TABLE ... FORCE是一個(gè)重量級(jí)、會(huì)鎖表的操作務(wù)必在業(yè)務(wù)低峰期進(jìn)行。重建索引ALTER TABLE ... DROP INDEX ADD INDEX對(duì)于非聚簇索引可以先刪除再重建。對(duì)于聚簇索引即主鍵重建意味著重建整個(gè)表代價(jià)更高。實(shí)操心得對(duì)于核心業(yè)務(wù)大表我通常會(huì)建立一個(gè)定期的、在低峰期執(zhí)行的維護(hù)窗口使用pt-online-schema-change或gh-ost等在線(xiàn)DDL工具進(jìn)行索引的增刪改以避免長(zhǎng)時(shí)間鎖表影響業(yè)務(wù)。對(duì)于碎片整理如果表非常大OPTIMIZE TABLE可能不現(xiàn)實(shí)有時(shí)選擇性重建部分關(guān)鍵索引是更可行的方案。4.3 索引的代價(jià)與選擇策略懂得取舍創(chuàng)建索引前必須權(quán)衡其代價(jià)空間代價(jià)每個(gè)索引都是一棵B樹(shù)需要占用磁盤(pán)空間。索引越多空間消耗越大。時(shí)間代價(jià)DML操作每次執(zhí)行INSERT、UPDATE、DELETE操作時(shí)MySQL不僅要更新數(shù)據(jù)還要更新所有相關(guān)的索引。索引越多寫(xiě)操作越慢。維護(hù)代價(jià)索引需要被監(jiān)控和維護(hù)。我的個(gè)人策略是優(yōu)先為高頻查詢(xún)的WHERE、ORDER BY、GROUP BY、JOIN ON條件列創(chuàng)建索引。使用聯(lián)合索引代替多個(gè)單列索引當(dāng)查詢(xún)經(jīng)常同時(shí)使用多個(gè)列時(shí)。控制索引數(shù)量。一張表的索引數(shù)量不宜過(guò)多例如超過(guò)5-6個(gè)就需要審視。對(duì)于寫(xiě)非常頻繁的表更要吝嗇地創(chuàng)建索引?？紤]使用前綴索引。對(duì)于很長(zhǎng)的字符串列如VARCHAR(255)可以只對(duì)前N個(gè)字符建立索引以節(jié)省空間。關(guān)鍵是選擇足夠長(zhǎng)的前綴以保證較高的區(qū)分度。ALTER TABLE table_name ADD INDEX idx_name (column_name(N));避免在區(qū)分度極低的列上建索引。例如“性別”列只有‘M‘/‘F‘兩個(gè)值建索引的收益幾乎為零優(yōu)化器很可能直接忽略它而選擇全表掃描。5. 高級(jí)優(yōu)化策略與執(zhí)行計(jì)劃深度解讀掌握了基礎(chǔ)我們進(jìn)入更深入的優(yōu)化層面理解優(yōu)化器如何選擇索引以及如何引導(dǎo)它做出最佳選擇。5.1 索引選擇性?xún)?yōu)化器選擇索引的核心依據(jù)索引選擇性Selectivity是指不重復(fù)的索引值基數(shù)Cardinality與表總記錄數(shù)#T的比值選擇性 基數(shù) / #T。選擇性越高越接近1索引的價(jià)值就越大。優(yōu)化器會(huì)根據(jù)預(yù)估的查詢(xún)成本來(lái)選擇索引而選擇性是成本估算的關(guān)鍵輸入。一個(gè)高選擇性的索引可以幫助過(guò)濾掉大部分?jǐn)?shù)據(jù)。你可以通過(guò)SHOW INDEX FROM your_table;查看索引的基數(shù)Cardinality這個(gè)值是采樣估算的有時(shí)可能不準(zhǔn)確可以使用ANALYZE TABLE your_table;來(lái)更新統(tǒng)計(jì)信息。5.2 索引下推ICP減少回表的神奇優(yōu)化索引下推是MySQL 5.6引入的一項(xiàng)重要優(yōu)化全稱(chēng)是Index Condition Pushdown。在沒(méi)有ICP的情況下存儲(chǔ)引擎通過(guò)索引檢索到數(shù)據(jù)返回給Server層再由Server層根據(jù)WHERE條件進(jìn)行過(guò)濾。有了ICP之后存儲(chǔ)引擎可以在取出索引的同時(shí)就根據(jù)索引中包含的列進(jìn)行條件判斷將不滿(mǎn)足條件的記錄直接過(guò)濾掉從而減少回表次數(shù)和返回給Server層的數(shù)據(jù)量。例如表t有聯(lián)合索引(zipcode, lastname, firstname)查詢(xún)?yōu)镾ELECT * FROM t WHERE zipcode‘95054‘ AND lastname LIKE ‘%etrunia%‘ AND address LIKE ‘%Main Street%‘;無(wú)ICP存儲(chǔ)引擎根據(jù)zipcode‘95054‘找到所有索引條目然后回表取出完整行交給Server層。Server層再過(guò)濾lastname和address。有ICP存儲(chǔ)引擎根據(jù)zipcode‘95054‘找到索引條目后在索引內(nèi)部就利用索引中包含的lastname列進(jìn)行LIKE ‘%etrunia%‘過(guò)濾注意這里lastname是范圍查詢(xún)但I(xiàn)CP仍然可以利用它進(jìn)行初步過(guò)濾。只將滿(mǎn)足zipcode和lastname條件的記錄的主鍵取出來(lái)回表最后再在Server層過(guò)濾address。這大大減少了回表次數(shù)。在EXPLAIN的Extra列中如果出現(xiàn)Using index condition就表示使用了ICP。5.3 多范圍讀MRR與批量鍵訪(fǎng)問(wèn)BKA這是另外兩項(xiàng)針對(duì)范圍查詢(xún)和關(guān)聯(lián)查詢(xún)的優(yōu)化。MRR對(duì)于范圍查詢(xún)傳統(tǒng)的做法是每從索引中拿到一個(gè)主鍵ID就立即回表讀取一行。MRR優(yōu)化會(huì)先將索引中掃描得到的主鍵ID放入緩沖區(qū)進(jìn)行排序然后按照主鍵順序去回表讀取數(shù)據(jù)。將隨機(jī)磁盤(pán)I/O轉(zhuǎn)變?yōu)楦樞虻腎/O可以顯著提升性能。EXPLAIN中Extra列顯示Using MRR。BKA是對(duì)MRR在關(guān)聯(lián)查詢(xún)JOIN中的延伸應(yīng)用。當(dāng)被驅(qū)動(dòng)表通常是右表可以使用索引進(jìn)行關(guān)聯(lián)時(shí)BKA會(huì)批量地將驅(qū)動(dòng)表左表關(guān)聯(lián)鍵值傳遞給被驅(qū)動(dòng)表利用MRR機(jī)制進(jìn)行批量檢索減少了對(duì)被驅(qū)動(dòng)表的訪(fǎng)問(wèn)次數(shù)。這些優(yōu)化通常由優(yōu)化器自動(dòng)判斷是否啟用在大多數(shù)情況下保持系統(tǒng)變量optimizer_switch中mrron和batched_key_accesson即可。6. 常見(jiàn)索引失效場(chǎng)景與排查實(shí)戰(zhàn)即使創(chuàng)建了索引查詢(xún)也可能沒(méi)有使用這就是所謂的“索引失效”。以下是實(shí)戰(zhàn)中最常踩的坑。6.1 導(dǎo)致索引失效的典型操作對(duì)索引列進(jìn)行運(yùn)算或函數(shù)操作WHERE YEAR(create_time) 2023會(huì)導(dǎo)致無(wú)法使用create_time上的索引。應(yīng)改為WHERE create_time ‘2023-01-01‘ AND create_time ‘2024-01-01‘。隱式類(lèi)型轉(zhuǎn)換如果列是字符串類(lèi)型VARCHAR但查詢(xún)寫(xiě)成了WHERE id 123id是字符串123是數(shù)字MySQL會(huì)進(jìn)行隱式轉(zhuǎn)換導(dǎo)致索引失效。務(wù)必保持類(lèi)型一致。使用OR連接非索引列WHERE indexed_column ‘A‘ OR non_indexed_column ‘B‘。如果OR一側(cè)的列沒(méi)有索引優(yōu)化器可能會(huì)選擇全表掃描?？梢钥紤]改寫(xiě)為UNION或分別查詢(xún)。LIKE以通配符開(kāi)頭WHERE name LIKE ‘%John‘無(wú)法使用name上的普通索引。如果必須這樣做考慮使用全文索引。WHERE name LIKE ‘John%‘則可以使用索引前綴匹配。不符合最左前綴原則如前所述對(duì)于聯(lián)合索引(A,B,C)查詢(xún)WHERE B1是無(wú)法使用該索引的。索引列參與比較在索引列上使用!、、NOT IN、NOT EXISTS時(shí)優(yōu)化器可能認(rèn)為需要掃描的數(shù)據(jù)量太大從而放棄索引。IS NULL和IS NOT NULL在某些情況下也可能導(dǎo)致索引失效取決于列中NULL值的比例。優(yōu)化器誤判當(dāng)表中數(shù)據(jù)量很少或者優(yōu)化器通過(guò)統(tǒng)計(jì)信息估算出使用索引的成本高于全表掃描時(shí)它會(huì)選擇不使用索引。這時(shí)可以使用FORCE INDEX提示強(qiáng)制使用索引但更根本的方法是更新統(tǒng)計(jì)信息ANALYZE TABLE。6.2 使用EXPLAIN進(jìn)行深度診斷EXPLAIN是你的最佳診斷工具。除了前面提到的字段還要關(guān)注possible_keys可能用到的索引。如果這里為空基本可以確認(rèn)查詢(xún)條件或表結(jié)構(gòu)有問(wèn)題。key_len實(shí)際使用的索引長(zhǎng)度?？梢詭湍闩袛嗍褂昧寺?lián)合索引的多少部分。例如一個(gè)INT列且非空在索引中長(zhǎng)度為4。如果key_len是4說(shuō)明只用了聯(lián)合索引的第一列。ref顯示索引的哪一列被用于查找。filtered存儲(chǔ)引擎層過(guò)濾后剩余記錄所占的百分比。這個(gè)值越接近100越好。一個(gè)更強(qiáng)大的工具是EXPLAIN FORMATJSON或EXPLAIN ANALYZEMySQL 8.0它們能提供更詳細(xì)的成本信息和實(shí)際執(zhí)行數(shù)據(jù)。6.3 索引失效排查清單當(dāng)遇到慢查詢(xún)時(shí)可以按以下清單快速排查檢查查詢(xún)條件是否有對(duì)索引列進(jìn)行計(jì)算、函數(shù)調(diào)用、類(lèi)型轉(zhuǎn)換檢查L(zhǎng)IKE語(yǔ)句通配符是否在開(kāi)頭檢查聯(lián)合索引查詢(xún)條件是否符合最左前綴原則檢查OR條件OR兩側(cè)的列是否都有索引使用EXPLAIN確認(rèn)索引是否被使用key字段掃描類(lèi)型type是否合理檢查數(shù)據(jù)分布是否因?yàn)閿?shù)據(jù)量太少或索引選擇性太低導(dǎo)致優(yōu)化器放棄索引執(zhí)行ANALYZE TABLE更新統(tǒng)計(jì)信息。檢查系統(tǒng)變量某些優(yōu)化如ICP、MRR是否被關(guān)閉7. 索引設(shè)計(jì)與優(yōu)化實(shí)戰(zhàn)案例剖析讓我們通過(guò)幾個(gè)具體的場(chǎng)景將前面的理論串聯(lián)起來(lái)。7.1 案例一電商訂單查詢(xún)優(yōu)化場(chǎng)景訂單表orders有數(shù)千萬(wàn)數(shù)據(jù)常見(jiàn)查詢(xún)1) 按用戶(hù)分頁(yè)查訂單2) 后臺(tái)按時(shí)間范圍、狀態(tài)查訂單。原始表結(jié)構(gòu)CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, amount DECIMAL(10,2), status TINYINT COMMENT ‘1待支付 2已支付 3已發(fā)貨 4已完成 5已取消‘, create_time DATETIME, update_time DATETIME ); -- 只有一個(gè)主鍵索引問(wèn)題查詢(xún)SELECT * FROM orders WHERE user_id ? ORDER BY create_time DESC LIMIT 0, 20非常慢。分析與優(yōu)化高頻查詢(xún)索引為(user_id, create_time)創(chuàng)建聯(lián)合索引。user_id用于快速定位用戶(hù)訂單create_time用于按時(shí)間排序并且索引本身有序可以避免ORDER BY帶來(lái)的文件排序Using filesort。CREATE INDEX idx_user_create ON orders(user_id, create_time DESC);注意在MySQL 8.0中可以指定索引的排序順序?yàn)镈ESC以更好地優(yōu)化ORDER BY ... DESC查詢(xún)。后臺(tái)查詢(xún)索引后臺(tái)查詢(xún)條件多變可能涉及status、create_time范圍等?？梢詣?chuàng)建(status, create_time)的聯(lián)合索引來(lái)覆蓋按狀態(tài)和時(shí)間篩選的查詢(xún)。如果status的選擇性不高可以將其放在后面。更復(fù)雜的查詢(xún)可能需要多個(gè)索引或根據(jù)最常用的查詢(xún)模式來(lái)設(shè)計(jì)。覆蓋索引嘗試如果前臺(tái)查詢(xún)只需要部分字段如id, user_id, status, amount, create_time可以考慮創(chuàng)建一個(gè)包含這些字段的聯(lián)合索引(user_id, create_time, status, amount)讓該查詢(xún)實(shí)現(xiàn)覆蓋索引性能達(dá)到極致。7.2 案例二社交平臺(tái)動(dòng)態(tài)流優(yōu)化場(chǎng)景動(dòng)態(tài)表feeds用戶(hù)關(guān)注很多人需要查詢(xún)“我關(guān)注的人發(fā)布的最新動(dòng)態(tài)”。原始查詢(xún)SELECT * FROM feeds WHERE author_id IN (SELECT followed_id FROM follows WHERE follower_id ?) ORDER BY publish_time DESC LIMIT 20;問(wèn)題IN子查詢(xún)效率可能不高尤其是關(guān)注人數(shù)多時(shí)。feeds表上如果只有author_id或publish_time的單列索引這個(gè)查詢(xún)會(huì)非常吃力。分析與優(yōu)化索引設(shè)計(jì)在feeds表上創(chuàng)建(author_id, publish_time DESC)的聯(lián)合索引。這樣對(duì)于IN列表里的每一個(gè)author_id都可以高效地按時(shí)間倒序取出其動(dòng)態(tài)。查詢(xún)改寫(xiě)有時(shí)可以將IN子查詢(xún)改為JOIN但在這個(gè)場(chǎng)景下核心瓶頸在于feeds表的索引。優(yōu)化后的索引能確保從每個(gè)作者取數(shù)據(jù)時(shí)都是高效的。更深層問(wèn)題如果用戶(hù)關(guān)注了上千人IN列表會(huì)很長(zhǎng)MySQL優(yōu)化器可能表現(xiàn)不佳。對(duì)于超大規(guī)模粉絲列表這種設(shè)計(jì)本身可能達(dá)到極限。此時(shí)需要考慮引入“推模式”或“推拉結(jié)合模式”將動(dòng)態(tài)預(yù)先聚合到用戶(hù)的個(gè)人時(shí)間線(xiàn)表中查詢(xún)就變成了簡(jiǎn)單的SELECT * FROM user_timeline WHERE user_id ? ORDER BY time DESC這是另一個(gè)架構(gòu)層面的優(yōu)化話(huà)題了。7.3 案例三避免過(guò)度索引與索引合并場(chǎng)景用戶(hù)表users在email、phone、username上分別建立了單列索引。一個(gè)查詢(xún)是SELECT id FROM users WHERE email ‘a(chǎn)b.com‘ OR phone ‘123456‘;問(wèn)題MySQL 5.0支持索引合并優(yōu)化。對(duì)于這個(gè)查詢(xún)優(yōu)化器可能會(huì)分別使用email索引和phone索引進(jìn)行掃描然后將結(jié)果合并Using union。這比全表掃描好但不如一個(gè)高效的聯(lián)合索引。分析與優(yōu)化識(shí)別索引合并EXPLAIN會(huì)顯示type為index_mergeExtra中顯示Using union(idx_email, idx_phone)。評(píng)估必要性索引合并通常是優(yōu)化器在缺少理想聯(lián)合索引時(shí)的補(bǔ)救措施。它的效率通常低于一個(gè)直接的聯(lián)合索引掃描因?yàn)樯婕皟纱嗡饕檎液徒Y(jié)果去重。優(yōu)化方案如果email和phone經(jīng)常在OR條件中同時(shí)出現(xiàn)可以考慮創(chuàng)建一個(gè)聯(lián)合索引(email, phone)或(phone, email)。但注意聯(lián)合索引對(duì)WHERE email ? AND phone ?的查詢(xún)友好對(duì)OR查詢(xún)不一定有效。更通用的優(yōu)化是審視業(yè)務(wù)邏輯看是否能將OR查詢(xún)拆分成兩個(gè)查詢(xún)通過(guò)應(yīng)用層或UNION來(lái)合并結(jié)果。有時(shí)維持兩個(gè)單列索引并接受索引合并可能是更靈活的選擇因?yàn)樗瑫r(shí)支持了email ?和phone ?的獨(dú)立查詢(xún)。索引的世界遠(yuǎn)不止于此還有自適應(yīng)哈希索引、不可見(jiàn)索引、降序索引等更多高級(jí)特性。但萬(wàn)變不離其宗核心永遠(yuǎn)是理解B樹(shù)的工作原理、聚簇/非聚簇索引的區(qū)別、最左前綴原則以及優(yōu)化器的成本模型。在實(shí)際工作中我習(xí)慣將索引優(yōu)化看作一個(gè)持續(xù)的迭代過(guò)程監(jiān)控慢查詢(xún)?nèi)罩居肊XPLAIN分析有針對(duì)性地創(chuàng)建或調(diào)整索引然后觀(guān)察效果。記住沒(méi)有銀彈最好的索引策略永遠(yuǎn)是貼合你的具體數(shù)據(jù)和查詢(xún)模式的策略。最后一個(gè)小建議在測(cè)試環(huán)境進(jìn)行大的索引變更前用真實(shí)數(shù)據(jù)量和查詢(xún)負(fù)載進(jìn)行基準(zhǔn)測(cè)試是避免生產(chǎn)事故的最后一重保險(xiǎn)。