數據採樣率優化

6.1 簡介

現在讓我們從理論轉向實踐。

“從理論上講，理論和實踐之間沒有區別。實際上，他們是。”

我們生活在現實世界中，所有的軟件產品最終都是為活著的人創造的。這些活著的人對加載緩慢的網站和運行緩慢的程序感到非常惱火。

而如果一個數據庫查詢花費的時間超過一秒，這是不可接受的。用戶根本不會使用頁面/功能如此緩慢的產品。

但通常，為了顯示一個頁面，您需要對數據庫執行幾十次查詢。如果它們是按順序執行的，那麼您將不再有第二個限制，但假設每個請求 100 毫秒。

以下是程序員加速數據庫查詢的前 5 種方法：

1. 為數據庫中的表添加索引。
2. 重寫和優化查詢。
3. 在數據庫端啟用（和配置）緩存。
4. 在客戶端啟用緩存。
5. 執行數據庫反規範化。

大多數情況下，您已經熟悉了所有這些內容，因此以下內容只是實用建議。

6.2 指數

眾所周知，使用數據庫佔據了幾乎所有站點的大部分工作。它正在處理最常成為 Web 應用程序瓶頸的數據庫。

在本文中，我想提供有關使用 MySQL 的實用建議。

我馬上說：

• 這篇文章是關於 MySQL 的，儘管一般的事情可能適用於任何 DBMS。
• 文中所寫均為個人觀點，並非最終真理。
• 建議並不假裝是新的，而是對文獻閱讀和個人經驗進行概括的結果。
• 在本文的框架內，我不會涉及 MySQL 配置問題。

使用MySQL時遇到的問題可以分為以下三組（按重要性排序）：

1. 不使用或濫用索引。
2. 錯誤的數據庫結構。
3. 不正確\次優的 SQL 查詢。

讓我們仔細看看這些組中的每一個。

使用索引

不使用或濫用索引是最常減慢查詢速度的原因。對於那些不熟悉索引工作機製或尚未閱讀手冊的人，我強烈建議您閱讀它。

使用索引的提示：

• 您不需要為所有內容編制索引。很多時候，人們在不理解其含義的情況下，只是簡單地索引了一個表的所有字段。索引加快獲取速度，但減慢行插入和更新速度，因此每個索引的選擇必須有意義。
• 表徵索引的主要參數之一是選擇性，即索引中不同元素的數量。索引具有兩個或三個可能值的字段是沒有意義的。這樣的索引幾乎沒有什麼好處。
• 索引的選擇應該從對給定表的所有查詢的分析開始。通常，在這樣的分析之後，您可以創建一個綜合指數，而不是三個或四個指數。
• 使用複合索引時，索引中字段的順序很關鍵。
• 不要忘記覆蓋索引。如果查詢中的所有數據都可以從索引中檢索到，那麼 MySQL 將不會直接訪問該表。此類請求將很快執行。例如，對於SELECT name FROM user WHERE login='test'帶有索引 (login, name) 的查詢，不需要訪問表。有時向複合索引添加一個額外的字段是有意義的，這將使索引覆蓋並加快查詢速度。
• 對於行索引，通常只索引行的一部分就足夠了。這可以顯著減小索引大小。
• 如果%在開頭，則LIKE(SELECT * FROM table WHERE field LIKE '%test')不會使用索引。
• FULLTEXT索引僅與MATCH ... AGAINST語法一起使用。

6.3 數據庫結構

設計良好的數據庫是快速高效地使用數據庫的關鍵。另一方面，設計糟糕的數據庫總是讓開發人員頭疼。

數據庫設計技巧：

1. 使用盡可能小的數據類型。數據類型越大，表越大，獲取數據所需的磁盤訪問次數就越多。使用一個非常方便的過程：SELECT * FROM table_name PROCEDURE ANALYSE();確定最小可能的數據類型。
2. 在設計階段觀察正常形式。通常程序員在這個階段已經求助於非規範化。然而，在大多數情況下，在項目開始時，結果如何會很不明顯。對錶進行非規範化比遭受次優非規範化的表要容易得多。有時JOIN它比不正確的非規範化表工作得更快。
3. 不要使用NULL列，除非你有意識地需要它們。

6.4 SQL查詢。

同樣，人們常常希望用原生 SQL 重寫所有查詢，以便查詢盡可能快。如果您決定這樣做，那麼這裡有一些提示：

1. 避免請求循環。SQL 是一種集合語言，編寫查詢不應使用函數語言，而應使用集合語言。
2. 避免*在查詢中使用（星號）。請隨意準確列出您選擇的字段。這將減少獲取和發送的數據量。另外，不要忘記覆蓋索引。即使您確實選擇了表中的所有字段，也最好將它們列出來。首先，它提高了代碼的可讀性。使用星號時，不查看表是不可能知道表中有哪些字段的。其次，今天您的表有五個INT列，一個月後又添加了一個TEXT和BLOB，星號保持原樣。
3. 分頁時，要獲取記錄總數，使用SQL_CALC_FOUND_ROWS和SELECT FOUND_ROWS();使用時SQL_CALC_FOUND_ROWS MySQL，緩存選定的行數（在應用 LIMIT 之前），使用時，SELECT FOUND_ROWS()僅返回此緩存值而無需重新執行查詢。
4. 不要忘記有INSERT多個插入的語法。一個查詢將比循環中的多個查詢快一個數量級。
5. LIMIT在不需要所有數據的地方使用。
6. 在選擇之後代替INSERT… ON DUPLICATE KEY UPDATE…andINSERT或UPDATE，通常代替REPLACE.
7. 不要忘記這個驚人的功能GROUP_CONCAT。它可以幫助解決複雜的查詢。