《面試官別再問》你的資料庫是怎麼優化？

 SQL 查詢優化是資料庫效能提升的關鍵。以下為您優化及整理了原文章的內容，使其更具結構性與條理，並補充了一些現代資料庫實踐中的考量。

---

SQL 查詢速度慢的常見原因與優化策略

SQL 查詢速度慢是資料庫系統常見的挑戰，原因多種多樣，從資料庫設計到查詢語句本身都可能存在問題。理解這些原因並採取適當的優化策略至關重要。

查詢速度慢的常見原因

1. 索引問題：
   • 缺少索引或未有效利用索引： 這是最常見的問題，當查詢涉及的欄位沒有建立索引，或雖然有索引但查詢條件未能使其生效時，資料庫需要進行全表掃描，效率極低。
   • 索引設計不當： 濫用索引、索引選擇不當（如對不常查詢的欄位建立索引），反而可能增加寫入負擔並降低性能。
2. I/O 瓶頸：
   • I/O 吞吐量小： 硬碟讀寫速度限制，導致資料讀取或寫入成為瓶頸。
3. 資料庫設計缺陷：
   • 未創建計算列（Computed Columns）： 對於某些需要複雜計算的結果，若沒有預先計算並儲存為計算列，每次查詢都需要重新計算，影響性能。
   • 不合理的資料庫範式或反範式： 過度正規化可能導致過多的 JOIN 操作；而反範化不足則可能造成大量重複數據或寫入效率低下。
4. 資源不足：
   • 內存不足： 資料庫系統無法將足夠的數據和索引緩存在內存中，頻繁進行硬碟 I/O。
   • 網路速度慢： 資料庫服務器與應用服務器之間的網路延遲或帶寬不足。
5. 併發與鎖定：
   • 鎖定或死鎖： 多個事務同時操作相同資源時可能導致鎖定，阻礙其他查詢執行，嚴重時甚至造成死鎖。
   • 讀寫競爭： sp_lock、sp_who 等工具顯示的活躍用戶和資源競爭，表明讀寫操作相互阻塞。
6. 不當的查詢設計：
   • 查詢返回不必要的行和列： 獲取過多的數據會增加資料傳輸和處理的時間。
   • 查詢語句未優化： SQL 語句本身寫得不夠高效，未能充分利用資料庫特性。
   • 返回數據量過大： 單次查詢返回數百萬甚至數億條數據，會耗盡資源。可以考慮分批查詢或使用分頁。

SQL 查詢優化方法

以下是針對上述問題的具體優化策略：

一、索引優化

1. 合理建立索引（Indexes）：
   • 索引是提升查詢速度最有效的方式。深入理解資料庫索引的工作原理（B-Tree、Hash 等），針對常用查詢的 WHERE 子句、JOIN 條件、ORDER BY 和 GROUP BY 欄位建立索引。
   • 避免過度索引，因為索引會增加寫入（INSERT, UPDATE, DELETE）的開銷和儲存空間。
   • 複合索引： 對於多個欄位同時作為查詢條件的情況，建立複合索引可以更有效。注意索引中欄位的順序，應將查詢中最常用的欄位放在前面。
   • 唯一索引（Unique Index）： 如果欄位值唯一，使用唯一索引可以進一步提升查詢效率，因為資料庫一旦找到匹配項即可停止搜索。

二、SQL 語句優化

1. 避免使用 SELECT *：
   • 只選擇你需要的列，不要返回不必要的數據。這能減少資料傳輸量、內存消耗和硬碟 I/O。
2. 善用 LIMIT 1：
   • 當你確定查詢結果只會有一條記錄時（例如檢查是否存在某條記錄），使用 LIMIT 1 可以讓資料庫在找到第一條符合條件的記錄後立即停止搜索，顯著提升效率。
   • 範例： SELECT 1 FROM user WHERE country = 'China' LIMIT 1; 比 SELECT * FROM user WHERE country = 'China'; 更高效。
3. 優化比較運算符（Symbol Operators）：
   • 當使用 > 或 < 進行範圍查詢時，如果能將其改寫為 >= 或 <= 配合已索引的欄位，有時能讓資料庫更有效地利用索引。例如，COLUMN >= 15 可能比 COLUMN > 16 更能利用索引的精確查找特性，取決於資料分佈和索引類型。
4. 謹慎使用通配符（Wildcard）和模糊查詢 LIKE：
   • % 作為前綴的模糊查詢 (LIKE '%hello%' 或 LIKE '%hello') 無法利用欄位上的索引，導致全表掃描，在大資料量下性能極差。
   • LIKE 'hello%' (後綴通配符) 可以有效利用索引。
   • 替代方案： 對於全文搜索，應考慮使用資料庫內建的全文索引（Full-Text Index）或專門的全文搜索引擎（如 Elasticsearch）。
5. 避免使用 NOT 運算符：
   • NOT LIKE, NOT IN, NOT EXISTS, != 等反向操作符通常會導致資料庫進行全表掃描，因為它需要檢查所有不符合條件的記錄。
   • 盡量改用正向查詢，如 LIKE, IN, EXISTS, = 等。
6. COUNT vs. EXISTS：
   • 如果僅判斷是否存在記錄，應使用 EXISTS 而非 COUNT。COUNT 會掃描所有符合條件的記錄並計數，而 EXISTS 只要找到一條符合條件的記錄就會立即停止。
   • 範例： SELECT 1 FROM TABLE WHERE EXISTS (SELECT 1 FROM ANOTHER_TABLE WHERE ...) 優於 SELECT COUNT(*) FROM TABLE WHERE ... > 0。
7. WILDCARD vs. SUBSTR：
   • 當欄位有索引時，使用 LIKE 'value%' 比 SUBSTR(COLUMN, 1, 1) = 'value' 更能利用索引。SUBSTR 操作會導致索引失效，進行全表掃描。
8. 優化 MAX 和 MIN 運算符：
   • 如果對已建立索引的欄位使用 MAX() 和 MIN()，查詢速度會非常快，因為資料庫可以直接通過索引樹獲取最大或最小值。
   • 如果僅是為了獲取最大/最小值而為欄位建立索引，則需要權衡，但通常情況下，它們是伴隨其他查詢而使用的。
9. 避免在 IN 子查詢中使用未優化的查詢：
   • 嵌套的 IN 子查詢可能效率低下。考慮將子查詢轉換為 JOIN 操作或使用 EXISTS。
   • 範例： SELECT * FROM TABLE, (SELECT COLUMN FROM ANOTHER_TABLE) AS dummytable WHERE dummytable.COLUMN = TABLE.COLUMN; 通常比 SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM ANOTHER_TABLE); 更高效。
10. 使用 UNION 而非 OR (在特定情況下)：
    • 在某些 MySQL 版本或特定查詢模式下，使用 OR 連結的條件可能導致索引失效。將其拆分為多個 SELECT 語句並用 UNION 連結，有時可以讓每個子查詢單獨利用索引，從而提升整體性能。
    • 範例：
      SQL

      SELECT * FROM TABLE WHERE COLUMN_A = 'value'
      UNION
      SELECT * FROM TABLE WHERE COLUMN_B = 'value';
      

      可能比 SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B = 'value'; 更快。

三、資料庫結構與設計優化

1. 選擇合適的資料類型（Types）：
   • 盡量使用最小但足夠容納數據的資料類型。例如，MEDIUMINT 通常比 INT 佔用空間更少。對於 email 或短文本，VARCHAR 比 LONGTEXT 更合適。
   • 更小的資料類型可以減少儲存空間，加速硬碟 I/O，並提高內存緩存效率。
2. 優化主鍵（Primary Index）：
   • 將主鍵設為盡可能小且簡單的資料類型（如 INT UNSIGNED AUTO_INCREMENT）。小的主鍵可以使索引更緊湊，提升查詢效率。
   • 即使有其他唯一欄位（如 Email），也應單獨設置一個整型自增主鍵，以優化內部資料結構和 JOIN 操作。
3. 字串索引（String Indexing）：
   • 對於長字串欄位，如果只需要對其前綴進行索引（如 VARCHAR(255) 中的前 100 個字元），可以只索引前綴，減少索引大小，提升性能。
4. 盡可能使用 NOT NULL：
   • 除非有特殊需求，否則將欄位設定為 NOT NULL。NULL 值需要額外的儲存空間來標記其是否為空，並會使查詢處理變得複雜。
5. 固定長度表優勢：
   • 如果表中的所有欄位都是固定長度類型（如 CHAR, INT, DATE，不包含 VARCHAR, TEXT, BLOB），那麼資料庫可以更快地計算記錄偏移量，從而加速讀取。
   • 可透過垂直分割將變長欄位分離到單獨的表中。
6. 垂直分割（Vertical Partitioning）：
   • 將一個表的列拆分成多個表，例如將不常用或更新頻率高的欄位分離到單獨的表中。這樣可以減少主表的寬度，提高查詢效率，減少不必要的緩存失效。
   • 示例： 用戶表中的 last_login 字段，每次登錄都會更新，可能導致查詢緩存失效。將其分離到單獨的表可以減少對主表的影響。
   • 注意事項： 分割後的表不應頻繁地進行 JOIN，否則可能導致性能下降。

四、系統與架構優化

1. 查詢緩存優化（Query Cache）：
   • 確保資料庫的查詢緩存開啟，並優化查詢以使其能夠利用緩存。避免使用不確定的函數（如 CURDATE(), NOW(), RAND()），它們會使查詢結果不確定，導致無法使用查詢緩存。
   • 範例： 用變量替代 CURDATE()：$today = date("Y-m-d"); SELECT username FROM user WHERE signup_date >= '$today'。
2. EXPLAIN 你的 SELECT 查詢：
   • EXPLAIN 關鍵字可以讓你了解 MySQL 如何執行你的 SQL 語句，包括索引使用、表掃描類型、排序方式等。這是分析和優化查詢瓶頸的關鍵工具。
   • 關注： type (連接類型，越靠前越好，如 const, eq_ref, ref 優於 ALL, index)、rows (估計掃描的行數)、key (實際使用的索引)、Extra (如 Using filesort, Using temporary 表示額外操作，應盡量避免)。
3. 使用儲存引擎（Storage Engine）：
   • 根據應用需求選擇合適的儲存引擎。
     • MyISAM： 適用於讀取密集型應用，特別是 SELECT COUNT(*) 性能極佳。但寫操作（INSERT, UPDATE, DELETE）會鎖定整張表。
     • InnoDB： 支持行級鎖定，在寫操作多的高併發應用中表現更優。支持事務（ACID），適合需要資料完整性和可靠性的場景。
4. Prepared Statements：
   • 預編譯語句（Prepared Statements）可以帶來性能和安全性雙重優勢。
   • 安全性： 自動處理參數綁定，有效防止 SQL 注入攻擊。
   • 性能： 對於重複執行的相同查詢，資料庫只需解析一次，減少了重複解析的開銷。特別是二進制協議傳輸時效率更高。
   • 雖然部分版本不支持查詢緩存，但在許多場景下仍是推薦的選擇。
5. 無緩衝查詢（Unbuffered Query）：
   • mysql_unbuffered_query() (PHP) 可以在查詢返回第一行數據後立即開始處理，而無需等待所有結果返回。這可以節省大量內存，尤其適用於處理大量結果集的情況。
   • 限制： 無法使用 mysql_num_rows() 或 mysql_data_seek()，且下次查詢前需調用 mysql_free_result()。
6. IP 地址儲存為 UNSIGNED INT：
   • 將 IP 地址儲存為 UNSIGNED INT (4 個字節) 而非 VARCHAR(15)，可以節省空間並提高查詢效率，尤其在需要進行範圍查詢時。
   • 使用 INET_ATON() (字符串轉整數) 和 INET_NTOA() (整數轉字符串) 進行轉換。
7. 拆分大的 DELETE 或 INSERT 語句：
   • 在線上環境中執行大量數據的 DELETE 或 INSERT 操作時，應將其拆分為小批次執行，並在每次操作後給予短暫的間隔（LIMIT 配合延遲）。
   • 大規模的操作會鎖定表，可能導致網站響應中斷甚至服務器崩潰。
8. 對象關係映射器（ORM）：
   • ORM 工具（如 Doctrine）可以提供可靠的性能提升，尤其是在**惰性加載（Lazy Loading）**方面，即只在需要時才從資料庫中獲取數據。
   • ORM 也支持將多個 SQL 語句打包成事務執行，比單獨執行更快。
   • 注意事項： 不當的 ORM 使用可能產生大量的小查詢，反而降低性能。
9. 謹慎使用“永久鏈接”（Persistent Connections）：
   • 理論上，永久鏈接可以減少重複建立資料庫連接的開銷。但在高併發環境下，這可能導致連接數過多、內存佔用高、文件句柄耗盡等問題，甚至造成應用程序或服務器崩潰。通常不建議在 Apache 等高併發環境中使用。

五、架構層面優化

1. Web 服務器與資料庫分離：
   • Web 服務器使用負載均衡伺服器，將流量分散到多個應用實例。
   • MySQL 服務器採用主從架構（Master-Slave），實現讀寫分離，將讀操作分流到從庫，減輕主庫壓力。
2. 選擇合適的儲存引擎： 如前所述，根據業務場景選擇 MyISAM 或 InnoDB。
3. 資料庫設計：
   • 遵循三範式（或根據實際需求適當反範式）：確保資料的完整性、減少冗餘。
   • 分區分表（Partitioning/Sharding）： 對於極大的數據表，可以考慮垂直或水平分區，將數據分散到不同的物理存儲或資料庫實例，提高查詢和管理效率。
4. 開啟 MySQL 緩存： 除了查詢緩存，還有其他緩存機制（如 InnoDB Buffer Pool）需要合理配置。
5. 程式碼層：
   • 在保證結果正確的前提下，選擇效率更高、更節省資源的 SQL 語句。

總結

SQL 查詢優化是一個複雜且持續的過程，沒有一勞永逸的解決方案。上述優化建議涵蓋了從資料庫結構、SQL 語句撰寫到系統架構等多個層面。在實際應用中，需要：

• 持續監控： 使用資料庫監控工具實時了解資料庫性能指標。
• 剖析（Profiling）： 定期對慢查詢進行剖析，找出瓶頸所在。
• 逐步實施： 每次只應用一項優化，並測試其效果，避免引入新的問題。
• 根據實際數據和場景決策： 許多建議需要結合實際的數據分佈、查詢模式和業務需求來判斷是否適用。

通過綜合運用這些策略，可以顯著提升資料庫查詢性能，為應用程序提供更好的響應速度和用戶體驗。