為什么給數據表加了索引，寫入速度反而變慢了

為數據表增加索引后之所以會導致寫入（包括插入、更新、刪除）操作的速度變慢，其根本原因在于索引本質上是一個獨立的、需要與主表數據保持實時同步的“數據結構”。這一機制的核心邏輯涵蓋五個方面：因為索引本質上是一個“獨立的數據結構”需要被“同步維護”、每次“插入”新數據時都必須向所有索引中“新增”條目、每次“刪除”數據時也必須“移除”所有索引中的對應條目、當“更新”被索引的列時更涉及到“刪除舊索引”與“添加新索引”的復雜操作、以及索引的數量與復雜度直接導致了“寫操作”的成本增加。

具體來說，當一條新數據被插入主表時，數據庫不僅要完成數據本身的寫入，還必須承擔一項額外的“維護”工作：數據庫需要將新數據中被索引的列的值，分別地、按照預設的排序規則，插入到每一個相關的索引結構中去。這個“插入索引”的過程本身就是一個相對耗時的磁盤操作，并且表的索引越多，這份“額外負擔”就越重，從而使得總體的寫入性能呈現出明顯的下降。

一、問題的“本質”、索引的“雙面性”

在數據庫性能優化的世界里，索引常被譽為提升查詢速度的“銀彈”。然而，這顆“銀彈”卻是一枚具有“雙刃劍”效應的硬幣，它在為“讀”操作帶來數量級性能提升的同時，也必然地會對“寫”操作征收一筆不菲的“性能稅”。

索引的核心價值在于加速“讀”操作。一個沒有索引的數據表在進行條件查詢時，數據庫只能進行“全表掃描”，即逐行檢查每一條記錄直到找到匹配的數據。這種方式在數據量小時尚可接受，但在一個擁有數千萬行記錄的大表中，一次全表掃描可能是秒級甚至分鐘級的操作。一個設計良好的索引就如同書籍的“目錄”，它允許數據庫通過高效的查找算法快速“定位”到所需數據所在的物理位置，將查詢的時間復雜度從“線性”級別降低到“對數”級別，從而實現查詢速度的巨大飛躍。

這份極致的“讀取”效率的代價，就是在每一次“寫入”數據時都需要付出額外的“維護成本”。一個索引并非一個簡單的“標記”，它是一個真實存在的、需要占用磁盤空間、并需要被嚴格維護的獨立“數據結構”（最常見的是B+樹結構）。“寫”操作（包括插入、刪除、更新）不僅僅是在修改“主數據表”，更是在同時修改“主數據表”和其身上所附帶的“每一個索引”。索引越多，這張“稅單”就越長，寫入操作的總耗時也就越久。因此，是否要為一個表、一個列添加索引，以及添加什么樣的索引，本質上是一場關于“讀性能”與“寫性能”之間的深刻“權衡取舍”。正如經濟學家托馬斯·索維爾所言：“世上沒有解決方案，只有利弊權衡。”

二、“插入”操作的“寫放大”效應

讓我們首先來剖析最簡單的“插入”操作是如何因為索引的存在而變得“昂貴”的。

在一個沒有任何索引的“裸表”中，當一條新的記錄需要被插入時，數據庫所做的工作相對簡單：它只需要在數據文件中找到一塊足夠大的、可用的空白空間，然后將這條新的行數據寫入即可。

然而，當這張表擁有了一個或多個索引之后，一次看似簡單的“插入”在其在數據庫內部會觸發一系列復雜得多的“連鎖反應”。首先是寫入主表數據，這個步驟與無索引時基本相同。其次是同步更新所有索引，這是性能開銷的核心來源。對于這張表上所存在的每一個索引，數據庫都必須執行一次“索引更新”操作。數據庫需要從剛剛插入的那行新數據中提取出與該索引相關的“鍵值”，然后必須在這個索引的、獨立的、通常是巨大的B+樹結構中從根節點開始進行一次查找，以定位到這個新的“鍵值”應該被“插入”的、那個正確的、符合排序規則的“葉子節點”位置。最后數據庫將這個新的“索引條目”（包含了鍵值和指向主表行的物理地址指針）插入到那個葉子節點中。如果這個插入操作導致了葉子節點“分裂”，那么其所引發的、對樹狀結構的“平衡性”調整將是更進一步的性能開銷。

這個“寫入數據 -> 查找索引位置 -> 插入索引條目”的過程，其成本會隨著“索引數量”的增加而成倍地“疊加”。如果一張表上有5個索引，那么每一次的插入操作就意味著需要進行1次主表的數據寫入和5次獨立的、復雜的索引樹寫入。對于那些“寫入”極其頻繁的“流水日志”類數據表，過多的索引無疑是一場性能的災難。

三、“刪除”操作的“同步清理”

與“插入”操作的邏輯類似，“刪除”操作同樣需要為索引付出“同步維護”的代價。在一個沒有索引的表中，刪除一條記錄的核心是找到那行數據并將其從數據文件中移除或標記為“已刪除”。

而在一個有索引的表中，其過程則要復雜得多。第一步是定位并刪除主表數據，如果刪除的條件恰好能夠利用到某個索引，那么“定位”這一步會非常快。第二步是同步刪除所有索引中的條目。在刪除了主表的行數據之后，數據庫必須再次遍歷這張表上的所有索引，并在每一個索引的B+樹結構中都找到并刪除那個指向剛剛被刪除的、那一行數據的“索引條目”。

如果不進行這個“同步清理”的操作，那么這些索引中就會殘留下來大量指向“空地址”的“僵尸”索引條目。這不僅會浪費磁盤空間，更會在未來的查詢中引入不必要的計算和錯誤。

四、“更新”操作的“雙重打擊”

“更新”操作對于索引維護而言，是最復雜、也最能體現其“代價”的場景。我們需要將其分為兩種截然不同的情況來討論。

第一種情況是更新“非索引”列。例如 UPDATE users SET age = 31 WHERE id = 123;，假設age這個列上沒有建立索引。在這種情況下，數據庫只需要定位到id=123的行并直接地在“原地”修改age字段的值即可。因為所有被索引的列（例如可能存在的name或create_time列）其值并沒有發生任何變化，所以所有的索引結構都無需進行任何的修改。這次操作的成本相對較低。

第二種情況是更新“索引”列，這是性能殺手。例如 UPDATE users SET name = '張三' WHERE id = 123;，假設name這個列上存在一個索引。在數據庫的索引維護機制中，一次對“索引列”的“更新”操作，其本質幾乎等同于一次“刪除舊索引條目”加上一次“插入新索引條目”的、“雙倍”成本的操作。數據庫首先需要定位到id=123的行，然后更新主表中的name字段。此時數據庫必須去name列的索引樹中進行一次復雜的維護：它需要根據“舊”的值找到并刪除那個原始的索引條目，然后它需要根據“新”的值“張三”在索引樹中重新尋找一個新的、符合排序規則的位置并插入一個新的索引條目。一個簡單的、只修改了一行數據的“更新”指令在底層可能會觸發對多個、獨立的、分布在磁盤不同位置的“索引”文件進行多次的、復雜的“讀-刪-寫”操作，這種現象被稱為“寫放大”。

五、平衡的“藝術”、索引設計策略

既然索引是一把“雙刃劍”，那么在實踐中我們該如何進行“權衡”和“優化”，以求在“讀性能”和“寫性能”之間找到一個最佳的“平衡點”呢？

首先需要深刻理解業務的“讀寫比”，這是進行所有**索引設計**決策的第一個也是最重要的問題：“對于這張表，其日常的主要負載是‘讀’操作還是‘寫’操作？” 對于“讀多寫少”的場景，例如“商品信息”表或用于“數據分析”的報表系統，我們可以也應該為其建立相對完善的、多維度的索引來最大化地提升其核心價值——“快速查詢”。而對于“寫多讀少”的場景，例如用于記錄“用戶行為”的“日志”表，其索引的創建必須保持極致的“克制”，每一個新增的索引都可能成為其“寫入”性能的“瓶頸”。

其次，索引應該是“精準”的，而非“盲目”的。索引應該只為那些在WHERE, JOIN, ORDER BY子句中被頻繁使用的列而建立。為一個幾乎從未被用于“查詢條件”的列建立索引是毫無意義的純粹的“負資產”。如果一個查詢常常需要同時對多個列進行過濾，那么創建一個包含了這多個列的“聯合索引”其效率遠高于為每一個列都單獨地創建一個“獨立索引”。如果一個查詢所需要返回的所有字段恰好都已經包含在了某個索引之中，那么數據庫就無需再去“回”到主表中去讀取數據。這種只查詢“索引”就能滿足所有需求的查詢被稱為“覆蓋索引”，其性能極高。

最后，隨著數據的不斷“增刪改”，索引的內部結構可能會產生“碎片”導致其查詢效率下降。數據庫管理員需要定期地對索引進行“重建”或“重組”來保持其最佳的性能狀態。