《MySQL 進階篇》十七：資料庫其他調優策略

ACatSmiling發表於2024-09-22
MySql資料庫

Author: ACatSmiling

Since: 2024-09-21

資料庫調優的措施

調優的目標

  • 儘可能節省系統資源，以便系統可以提供更大負荷的服務（吞吐量更大）。
  • 合理的結構設計和引數調整，以提高使用者操作響應的速度（響應速度更快）。
  • 減少系統的瓶頸，提高 MySQL 資料庫整體的效能。

如何定位調優問題

不過隨著使用者量的不斷增加，以及應用程式複雜度的提升，我們很難用 "更快" 去定義資料庫調優的目標，因為使用者在不同時間段訪問伺服器遇到的瓶頸不同，比如雙十一促銷的時候會帶來大規模的併發訪問。還有使用者在進行不同業務操作的時候，資料庫的事務處理和 SQL 查詢都會有所不同。因此還需要更加精細的定位，去確定調優的目標。

如何確定呢？一般情況下，有如下幾種方式：

  • 使用者的反饋（主要）
    • 使用者是服務的物件，因此他們的反饋是最直接的。
    • 雖然他們不會直接提出技術建議，但是有些問題往往是使用者第一時間發現的。要重視使用者的反饋，找到和資料相關的問題。
  • 日誌分析（主要）
    • 可以透過檢視資料庫日誌和作業系統日誌等方式找出異常情況，透過它們來定位遇到的問題。
    • 伺服器資源使用監控，透過監控伺服器的 CPU、記憶體、I/O 等使用情況，可以實時瞭解伺服器的效能使用，與歷史情況進行對比。
  • 資料庫內部狀況監控
    • 在資料庫的監控中，活動會話（Active Session）監控是一個重要的指標。透過它可以清楚地瞭解資料庫當前是否處於非常繁忙的狀態，是否存在 SQL 堆積等。
  • 其它
    • 除了活動會話監控以外也可以對事務、鎖等待等進行監控，這些都可以幫助我們對資料庫的執行狀態有更全面的認識。

調優的維度和步驟

需要調優的物件是整個資料庫管理系統，它不僅包括 SQL 查詢，還包括資料庫的部署配置、架構等。從這個角度來說，思考的維度就不僅僅侷限在 SQL 最佳化上了。透過如下的步驟進行梳理：

第 1 步：選擇適合的 DBMS

如果對事務性處理以及安全性要求高的話，可以選擇商業的資料庫產品。這些資料庫在事務處理和查詢效能上都比較強，比如採用 SQL Server、Oracle，那麼單表儲存上億條資料是沒有問題的。如果資料表設計得好，即使不採用分庫分表的方式，查詢效率也不差。

除此以外也可以採用開源的 MySQL 進行儲存，它有很多儲存引擎可以選擇，如果進行事務處理的話可以選擇 InnoDB，非事務處理可以選擇 MyISAM。

NoSQL 陣營包括鍵值型資料庫、文件型資料庫、搜尋引擎，列式儲存和圖形資料庫。這些資料庫的優缺點和使用場景各有不同，比如列式儲存資料庫可以大幅度降低系統的 I/O，適合於分散式檔案系統，但如果資料需要頻繁地增刪改，那麼列式儲存就不太適用了。

DBMS 的選擇關係到了後面的整個設計過程，所以第一步就是要選擇適合的 DBMS。如果已經確定好了 DBMS，那麼這步可以跳過。

第 2 步：最佳化表設計

選擇了 DBMS 之後就需要進行表設計了。而資料表的設計方式也直接影響了後續的 SQL 查詢語句。RDBMS 中，每個物件都可以定義為一張表，表與表之間的關係代表了物件之間的關係。如果用的是 MySQL，還可以根據不同表的使用需求，選擇不同的儲存引擎。除此以外，還有一些最佳化的原則可以參考：

  • 表結構要儘量遵循三正規化的原則。這樣可以讓資料結構更加清晰規範，減少冗餘欄位，同時也減少了在更新，插入和刪除資料時等異常情況的發生。
  • 如果查詢應用比較多，尤其是需要進行多表聯查的時候，可以採用反正規化進行最佳化。反正規化採用空間換時間的方式，透過增加冗餘欄位提高查詢的效率。
  • 表欄位的資料型別選擇，關係到了查詢效率的高低以及儲存空間的大小。一般來說，如果欄位可以採用數值型別就不要採用字元型別。字元長度要儘可能設計得短一些。針對字元型別來說，當確定字元長度固定時，就可以採用 CHAR 型別。當長度不固定時，通常採用 VARCHAR 型別。

資料表的結構設計很基礎，也很關鍵。好的表結構可以在業務發展和使用者量增加的情況下依然發揮作用，不好的表結構設計會讓資料表變得非常臃腫，查詢效率也會降低。

第 3 步：最佳化邏輯查詢

當建立好資料表之後，就可以對資料表進行增刪改查的操作了。這時首先需要考慮的是邏輯查詢最佳化。

SQL 查詢最佳化，可以分為邏輯查詢最佳化和物理查詢最佳化。邏輯查詢最佳化就是透過改變 SQL 語句的內容，讓 SQL 執行效率更高效，採用的方式是對 SQL 語句進行等價變換，對查詢進行重寫。

SQL 的查詢重寫包括了子查詢最佳化、等價謂詞重寫、檢視重寫、條件簡化、連線消除和巢狀連線消除等。

比如 EXISTS 子查詢和 IN 子查詢，會根據小表驅動大表的原則選擇適合的子查詢。在 WHERE 子句中會盡量避免對欄位進行函式運算，它們會讓欄位的索引失效。示例如下：

  • 查詢評論內容開頭為 abc 的內容都有哪些，如果在 WHERE 子句中使用了函式，語句就會寫成下面這樣：

    SELECT comment_id, comment_text, comment_time FROM product_comment WHERE SUBSTRING(comnment_text, 1, 3) = 'abc';

  • 採用查詢重寫的方式進行等價替換：

    SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_text LIKE 'abc%';

第 4 步：最佳化物理查詢

物理查詢最佳化是在確定了邏輯查詢最佳化之後，採用物理最佳化技術（比如索引等），透過計算代價模型對各種可能的訪問路徑進行估算，從而找到執行方式中代價最小的作為執行計劃。在這個部分中需要掌握的重點是對索引的建立和使用。

但索引不是萬能的，要根據實際情況來建立索引。那麼都有哪些情況需要考慮呢？這在前面幾章中已經進行了細緻的剖析。

SQL 查詢時需要對不同的資料表進行查詢，因此在物理查詢最佳化階段也需要確定這些查詢所採用的路徑，具體的情況包括：

  • 單表掃描：對於單表掃描來說，可以全表掃描所有的資料，也可以區域性掃描。
  • 兩張表的連線：常用的連線方式包括了巢狀迴圈連線、 HASH 連線和合並連線。
  • 多張表的連線：多張資料表進行連線的時候，順序很重要，因為不同的連線路徑查詢的效率不同，搜尋空間也會不同。在進行多表連線的時候，搜尋空間可能會達到很高的資料量級，巨大的搜尋空間顯然會佔用更多的資源，因此需要透過調整連線順序，將搜尋空間調整在一個可接受的範圍內。

第 5 步：使用 Redis 或 Memcached 作為快取

除了可以對 SQL 本身進行最佳化以外，還可以請外援提升查詢的效率。

因為資料都是存放到資料庫中，需要從資料庫層中取出資料放到記憶體中進行業務邏輯的操作，當使用者量增大的時候，如果頻繁地進行資料查詢，會消耗資料庫的很多資源。如果將常用的資料直接放到記憶體中，就會大幅提升查詢的效率

鍵值儲存資料庫可以幫我們解決這個問題。

常用的鍵值儲存資料庫有 Redis 和 Memcached，它們都可以將資料存放到記憶體中。

從可靠性來說， Redis 支援持久化，可以讓我們的資料儲存在硬碟上，不過這樣一來效能消耗也會比較大。而 Memcached 僅僅是記憶體儲存，不支援持久化。

通常對於查詢響應要求高的場景（響應時間短，吞吐量大)，可以考慮記憶體資料庫，畢竟術業有專攻。傳統的 RDBMS，都是將資料儲存在硬碟上，而記憶體資料庫則存放在記憶體中，查詢起來要快得多。不過使用不同的工具，也增加了開發人員的使用成本。

第 6 步：庫級最佳化

庫級最佳化是站在資料庫的維度上進行的最佳化策略，比如控制一個庫中的資料表數量。另外，單一的資料庫總會遇到各種限制，不如取長補短，利用 "外援" 的方式。透過主從架構最佳化讀寫策略，透過對資料庫進行垂直或者水平切分，突破單一資料庫或資料表的訪問限制，提升查詢的效能。

讀寫分離

如果讀和寫的業務量都很大，並且它們都在同一個資料庫伺服器中進行操作，那麼資料庫的效能就會出現瓶頸，這時為了提升系統的效能，最佳化使用者體驗，可以採用讀寫分離的方式降低主資料庫的負載，比如用主資料庫（master）完成寫操作，用從資料庫（slave）完成讀操作。

image-20231107091127166

image-20231107091152976

資料分片

對資料庫分庫分表。當資料量級達到千萬級以上時，有時候要把一個資料庫切成多份，放到不同的資料庫伺服器上，減少對單一資料庫伺服器的訪問壓力。如果你使用的是 MySQL，就可以使用 MySQL 自帶的分割槽表功能，當然你也可以考慮自己做垂直拆分（分庫）、水平拆分（分表）、垂直 + 水平拆分（分庫分表）。

image-20231107091350062

image-20231107091417143

  • 垂直分表：比如按照熱資料、冷資料進行分表。
  • 水平分表：比如按照日期範圍進行劃分。

但需要注意的是，分拆在提升資料庫效能的同時，也會增加維護和使用成本

最佳化 MySQL 伺服器

最佳化 MySQL 伺服器主要從兩個方面來最佳化，一方面是對硬體進行最佳化。另一方面是對MySQL 服務的引數進行最佳化。這部分的內容需要較全面的知識，一般只有專業的資料庫管理員才能進行這一類的最佳化。對於可以定製引數的作業系統，也可以針對 MySQL 進行作業系統最佳化。

最佳化伺服器硬體

伺服器的硬體效能直接決定著 MySQL 資料庫的效能。硬體的效能瓶頸直接決定 MySQL 資料庫的執行速度和效率。針對效能瓶頸提高硬體配置，可以提高 MySQL 資料庫查詢、更新的速度。

  1. 配置較大的記憶體。足夠大的記憶體是提高 MySQL 資料庫效能的方法之一。記憶體的速度比磁碟 I/O 快得多，可以透過增加系統的緩衝區容量使資料在記憶體中停留的時間更長，以讀少磁碟 I/O。
  2. 配置高速磁碟系統，以減少讀盤的等待時間，提高響應速度。磁碟的 I/O 能力，也就是它的尋道能力，目前的 SCSI 高速旋轉的是 7200 轉/分鐘，這樣的速度，一旦訪問的使用者量上去，磁碟的壓力就會過大，如果是每天的網站 pv（page view）在 150 w，這樣的一般的配置就無法滿足這樣的需求了。現在 SSD 盛行，在 SSD 上隨機訪問和順序訪問效能幾乎差不多，使用 SSD 可以減少隨機 I/O 帶來的效能損耗。
  3. 合理分佈磁碟 I/O，把磁碟 I/O 分散在多個裝置上，以減少資源競爭，提高並行操作能力。
  4. 配置多處理器，MySQL 是多執行緒的資料庫，多處理器可同時執行多個執行緒。

最佳化 MySQL 服務的引數

透過最佳化 MySQL 服務的引數可以提高資源利用率，從而達到提高 MySQL 伺服器效能的目的。

MySQL 服務的配置引數都在my.cnf或者my.ini檔案的[mysqld]組中，配置完引數以後，需要重新啟動 MySQL 服務才會生效。

下面對幾個對效能影響比較大的引數進行詳細介紹：

  • innodb_buffer_pool_size：這個引數是 MySQL 資料庫最重要的引數之一，表示 InnoDB 型別的表和索引的最大快取。它不僅僅快取索引資料，還會快取表的資料。這個值越大，查詢的速度就會越快。但是這個值太大會影響作業系統的效能。

  • key_buffer_size：表示索引緩衝區的大小。索引緩衝區是所有的執行緒共享。增加索引緩衝區可以得到更好處理的索引（對所有讀和多重寫）。當然，這個值不是越大越好，它的大小取決於記憶體的大小。如果這個值太大，就會導致作業系統頻繁換頁，也會降低系統效能。對於記憶體在 4 GB 左右的伺服器該引數可設定為 256 MB 或 384 MB。

  • table_cache：表示同時開啟的表的個數。這個值越大，能夠同時開啟的表的個數越多。實體記憶體越大，設定就越大。預設為 2402，調到 512 ~ 1024 最佳。這個值不是越大越好，因為同時開啟的表太多會影響作業系統的效能。

  • query_cache_size：表示查詢緩衝區的大小。可以透過在 MySQL 控制檯觀察，如果 qcache_lowmem_prunes 的值非常大，則表明經常出現緩衝不夠的情況，就要增加 query_cache_size 的值；如果 qcache_hits 的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢快取。

  • qcache_free_blocks：如果該值非常大，則表明緩衝區中碎片很多。MySQL 8.0 之後失效。該引數需要和 query_cache_type 配合使用。

  • query_cache_type：值是 0 時，所有的查詢都不使用查詢快取區。但是 query_cache_type = 0 並不會導致 MySQL 釋放 query_cache_size 所配置的快取區記憶體。

    • 當 query_cache_type = 1 時，所有的查詢都將使用查詢快取區，除非在查詢語句中指定 SQL_NO_CACHE，如 SELECT SQL_NO_CACHE FROM tbl_name。
    • 當 query_cache_type = 2 時，只有在查詢語句中使用 SQL_CACHE 關鍵字，查詢才會使用查詢快取區。使用查詢快取區可以提高查詢的速度，這種方式只適用於修改操作少且經常執行相同的查詢操作的情況。

  • sort_buffer_size：表示每個需要進行排序的執行緒分配的緩衝區的大小。增加這個引數的值可以提高 ORDER BY 或 GROUP BY 操作的速度。預設數值是 2097144 位元組（約 2 MB）。對於記憶體在 4 GB 左右的伺服器，推薦設定為 6 ~ 8 MB，如果有 100 個連線，那麼實際分配的總共排序緩衝區大小為 100 × 6 = 600 MB。

  • join_buffer_size = 8M：表示聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該引數對應的分配記憶體也是每個連線獨享。

  • read_buffer_size：表示每個執行緒連續掃描時為掃描的每個表分配的緩衝區的大小（位元組）。當執行緒從表中連續讀取記錄時需要到這個緩衝區。SET SESSION read_buffer_size=n 可以臨時設定該引數的值。預設為 64 KB，可以設定為 4 MB。

  • innodb_flush_log_at_trx_commit：表示何時將緩衝區的資料寫入日誌檔案，並且將日誌檔案寫入磁碟中。該引數對於 InnoDB 引擎非常重要。該引數有 3 個值，分別為 0、1 和 2。該引數的預設值為 1。

    • 值為 0 時，表示每秒 1 次的頻率，將資料寫入日誌檔案並將日誌檔案寫入磁碟。每個事務的 commit 並不會觸發前面的任何操作。該模式速度最快，但不太安全，mysqld 程序的崩潰會導致上一秒鐘所有事務資料的丟失。
    • 值為 1 時，表示每次提交事務時，將資料寫入日誌檔案並將日誌檔案寫入磁碟進行同步。該模式是最安全的，但也是最慢的一種方式。因為每次事務提交或事務外的指令都需要把日誌寫入（flush）硬碟。
    • 值為 2 時，表示每次提交事務時，將資料寫入日誌檔案，每隔 1 秒將日誌檔案寫入磁碟。該模式速度較快，也比 0 安全，只有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丟失。

  • innodb_log_buffer_size：這是 InnoDB 儲存引擎的事務日誌使用的緩衝區。為了提高效能，也是先將資訊寫入 Innodb Log Buffer中，當滿足 innodb_flush_log_trx_commit 引數所設定的相應條件（或者日誌緩衝區寫滿）之後，才會將日誌寫到檔案（或者同步到磁碟）中。

  • max_connections：表示允許連線到 MySQL 資料庫的最大數量，預設值是 151。如果狀態變數 connection_errors_max_connections 不為零，並且一直增長，則說明不斷有連線請求因資料庫連線數已達到允許最大值而失敗，這時可以考慮增大 max_connections 的值，在 Linux 平臺下，效能好的伺服器，支援 500 ~ 1000 個連線不是難事，需要根據伺服器效能進行評估設定。這個連線數不是越大越好，因為這些連線會浪費記憶體的資源，過多的連線可能會導致 MySQL 伺服器僵死。

  • back_log：用於控制 MySQL 監聽 TCP 埠時，設定的積壓請求棧大小。如果 MySQL 的連線數達到 max_connections 時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即 back_log，如果等待連線的數量超過 back_log，將不被授予連線資源，將會報錯。5.6.6 版本之前預設值為 50， 之後的版本預設為 50 + (max_connections / 5)，對於 Linux 系統推薦設定為小於 512 的整數，最大不超過 900。

    • 如果需要資料庫在較短的時間內處理大量連線請求， 可以考慮適當增大 back_log 的值。

  • thread_cache_size：執行緒池快取執行緒數量的大小，當客戶端斷開連線後將當前執行緒快取起來，當在接到新的連線請求時快速響應無需建立新的執行緒。這尤其對那些使用短連線的應用程式來說可以極大的提高建立連線的效率。那麼為了提高效能可以增大該引數的值。預設為 60，可以設定為 120。

    • 可以透過如下幾個 MySQL 狀態值，來適當調整執行緒池的大小：

      show global status like 'Thread%';
/*
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+-------------------+-------+
*/

    • 當 Threads_cached 越來越少，但 Threads_connected 始終不降，且 Threads_created 持續升高，可適當增加 thread_cache_size 的大小。

  • wait_timeout ：指定一個請求的最大連線時間 ，對於 4 GB 左右記憶體的伺服器，可以設定為 5 ~ 10。

  • interactive_timeout ：表示伺服器在關閉連線前等待行動的秒數。

這裡給出一份 my.cnf 的參考配置：

[mysqld]
port = 3306 
serverid = 1 
socket = /tmp/mysql.sock 
# 避免MySQL的外部鎖定，減少出錯機率增強穩定性
skip-locking 
# 禁止MySQL對外部連線進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠端主機連線授權都要使用IP地址方式，否則MySQL將無法正常處理連線請求back_log = 384
skip-name-resolve 
key_buffer_size = 256M 
max_allowed_packet = 4M 
thread_stack = 256K
table_cache = 128K 
sort_buffer_size = 6M 
read_buffer_size = 4M
read_rnd_buffer_size=16M 
join_buffer_size = 8M 
myisam_sort_buffer_size =64M t
able_cache = 512 thread_cache_size = 64 query_cache_size = 64M
tmp_table_size = 256M 
max_connections = 768 
max_connect_errors = 10000000
wait_timeout = 10 
# 該引數取值為伺服器邏輯CPU數量*2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支援H.T超執行緒，所以實際取值為4*2=8 
thread_concurrency = 8 
# 開啟該選項可以徹底關閉MySQL的TCP/IP連線方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項！否則將無法正常連線
skipnetworking 
table_cache=1024
# 預設為2M 
innodb_additional_mem_pool_size=4M 
innodb_flush_log_at_trx_commit=1
# 預設為1M
innodb_log_buffer_size=2M  
# 你的伺服器CPU有幾個就設定為幾，建議用預設一般為8 
innodb_thread_concurrency=8 
# 預設為16M，調到64~256最掛
tmp_table_size=64M

很多情況還需要具體情況具體分析！

示例

下面是一個電商平臺，類似京東或天貓這樣的平臺。商家購買服務，入住平臺，開通之後，商家可以在系統中上架各種商品，客戶透過手機 App、微信小程式等渠道購買商品，商家接到訂單以後安排快遞送貨。

剛剛上線的時候，系統執行狀態良好。但是，隨著入住的商家不斷增多，使用系統的使用者量越來越多，每天的訂單資料達到了 5 萬條以上。這個時候，系統開始出現問題，CPU 使用率不斷飆升。終於，雙十一或者 618 活動高峰的時候，CPU 使用率達到 99%，這實際上就意味著，系統的計算資源已經耗盡，再也無法處理任何新的訂單了。換句話說，系統已經崩潰了。

這個時候，我們想到了對系統引數進行調整，因為引數的值決定了資源配置的方式和投放的程度。為了解決這個問題，一共調整 3 個系統引數，分別是：

  • InnoDB_flush_log_at_trx_commit
  • InnoDB_buffer_pool_size
  • InnoDB_buffer_pool_instances

下面就說一說調整這三個引數的原因是什麼：

  1. 調整系統引數 InnoDB_flush_log_at_trx_commit。
    • 這個引數適用於 InnoDB 儲存引擎，電商平臺系統中的表用的儲存引擎都是 InnoDB。預設的值是 1，意思是每次提交事務的時候，都把資料寫入日誌，並把日誌寫入磁碟。這樣做的好處是資料安全性最佳，不足之處在於每次提交事務，都要進行磁碟寫入的操作。在大併發的場景下，過於頻繁的磁碟讀寫會導致 CPU 資源浪費，系統效率變低。
    • 這個引數的值還有 2 個可能的選項，分別是 0 和 2。現在把這個引數的值改成 2，這樣就不用每次提交事務的時候都啟動磁碟讀寫了，在大併發的場景下，可以改善系統效率，降低 CPU 使用率。即便出現故障，損失的資料也比絞小。
  2. 調整系統引數 InnoDB_buffer_pool_size。
    • 這個引數的意思是，InnoDB 儲存引擎使用快取來儲存索引和資料。這個值越大，可以載入到快取區的索引和資料量就越多，需要的磁碟讀寫就越少。
    • 因為 MySQL 伺服器是資料庫專屬伺服器，只用來執行 MySQL 資料庫服務，沒有其他應用了，而我們的計算機是 64 位機器，記憶體也有 128 GB。於是把這個引數的值調整為 64 GB。這樣一來，磁碟讀寫次數可以大幅降低，就可以充分利用記憶體，釋放出一些 CPU 的資源。
  3. 調整系統引數 InnoDB_buffer_pool_instances。
    • 這個引數可以將 InnoDB 的快取區分成幾個部分，這樣可以提高系統的並行處理能力，因為可以允許多個程序同時處理不同部分的快取區。
    • 把 InnoDB_buffer_pool_instances 的值修改為 64，意思就是把 InnoDB 的快取區分成 64 個分割槽，這樣就可以同時有多個程序進行資料操作，CPU 的效率就高多了。修改好了系統引數的值，要重啟 MySQL 資料庫伺服器。

總結，遇到 CPU 資源不足的問題，可以從下面 2 個思路去解決：

  • 疏通擁堵路段，消除瓶頸，讓等待的時間更短。
  • 開拓新的通道，增加並行處理能力。

最佳化資料庫結構

一個好的資料庫設計方案對於資料庫的效能常常會起到事半功倍的效果。合理的資料庫結構不僅可以使資料庫佔用更小的磁碟空間，而且能夠使查詢速度更快。資料庫結構的設計需要考慮資料冗餘、查詢和更新的速度、欄位的資料型別是否合理等多方面的內容。

拆分表：冷熱資料分離

拆分表的思路是，把 1 個包含很多欄位的表，拆分成 2 個或者多個相對較小的表，這樣做的原因是，這些表中某些欄位的操作頻率很高（熱資料），經常要進行查詢或者更新操作，而另外一些欄位的使用頻率卻很低（冷資料），冷熱資料分離，可以減小表的寬度。如果放在一個表裡面，每次查詢都要讀取大記錄，會消耗較多的資源。

MySQL 限制每個表最多儲存4096列，並且每一行資料的大小不能超過65535位元組。表越寬，把表裝載進記憶體緩衝池時所佔用的記憶體也就越大，也會消耗更多的 I/O。

冷熱資料分離的目的是：① 減少磁碟 I/O，保證熱資料的記憶體快取命中率。② 更有效的利用快取，避免讀入無用的冷資料。

假設會員 members 表儲存會員登入認證資訊。該表中有很多欄位，如 id、姓名、密碼、地址、電話、個人描述欄位。其中地址、電話、個人描述等欄位並不常用，可以將這些不常用的欄位分解出另一個表。將這個表取名叫 members_detail，表中有 member_id、address、telephone、description 等欄位。這樣就把會員表分成了兩個表，分別為 members 表和 members_detail 表。

建立這兩個表的 SQL 語句如下：

CREATE TABLE members (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(50) DEFAULT NULL,
  password varchar(50) DEFAULT NULL,
  last_login_time datetime DEFAULT NULL,
  last_login_ip varchar(100) DEFAULT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE members_detail (
  member_id int(11) NOT NULL DEFAULT 0,
  address varchar(255) DEFAULT NULL,
  telephone varchar(255) DEFAULT NULL,
  description text
);

如果需要查詢會員的基本資訊或詳細資訊，那麼可以用會員的 id 來查詢。如果需要將會員的基本資訊和詳細資訊同時顯示，那麼可以將 members 表和 members_detail 表進行聯合查詢，查詢語句如下：

SELECT * FROM members LEFT JOIN members_detail on members.id = members_detail.member_id;

透過這種分解，可以提高表的查詢效率。對於欄位很多且有些欄位使用不頻繁的表，可以透過這種分解的方式來最佳化資料庫的效能。

增加中間表

對於需要經常聯合查詢的表，可以建立中間表以提高查詢效率。透過建立中間表，把需要經常聯合查詢的資料插入中間表中，然後將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

首先，分析經常聯合查詢表中的欄位。然後，使用這些欄位建立一箇中間表，並將原來聯合查詢的表的資料插入中間表中。最後，使用中間表來進行查詢。

假設學生資訊表和班級表的 SQL 語句如下：

CREATE TABLE `class` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `className` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(40) DEFAULT NULL,
  `monitor` INT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `stuno` INT NOT NULL ,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
  `classId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

現在有一個模組，需要經常查詢帶有學生名稱（name）、學生所在班級名稱（className）、學生班級班長（monitor）的學生資訊。根據這種情況，可以建立一個 temp_student 表。temp_student 表中儲存學生名稱（stu_name）、學生所在班級名稱（className）和學生班級班長（monitor）資訊。建立表的語句如下：

CREATE TABLE `temp_student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `stu_name` INT NOT NULL ,
  `className` VARCHAR(20) DEFAULT NULL,
  `monitor` INT(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

接下來，從學生資訊表和班級表中查詢相關資訊儲存到臨時表中：

INSERT INTO temp_student(stu_name,className,monitor)
SELECT s.name, c.className, c.monitor FROM student AS s, class AS c WHERE s.classId = c.id

以後，可以直接從 temp_student 表中查詢學生名稱、班級名稱和班級班長，而不用每次都進行聯合查詢，這樣可以提高資料庫的查詢速度。

如果使用者資訊修改了，是不是會導致 temp_student 中的資料不一致的問題呢？如何同步資料呢？

方式 1：清空資料 ---> 重新新增資料。

方式 2：使用檢視。

增加冗餘欄位

設計資料庫表時應儘量遵循正規化理論的規約，儘可能減少冗餘欄位，讓資料庫設計看起來精緻、優雅。但是，合理地加入冗餘欄位可以提高查詢速度。

表的規範化程度越高，表與表之間的關係就越多，需要連線查詢的情況也就越多。尤其在資料量大，而且需要頻繁進行連線的時候，為了提升效率，我們也可以考慮增加冗餘欄位來減少連線。

參考前面章節的反正規化化。

最佳化資料型別

改進表的設計時，可以考慮最佳化欄位的資料型別。這個問題在大家剛從事開發時基本不算是問題。但是，隨著你的經驗越來越豐富，參與的專案越來越大，資料量也越來越多的時候，你就不能只從系統穩定性的角度來思考問題了，還要考慮到系統整體的穩定性和效率。此時，優先選擇符合儲存需要的最小的資料型別。

列的欄位越大，建立索引時所需要的空間也就越大，這樣一頁中所能儲存的索引節點的數量就越少，在遍歷時所需要的I/O次數也就越多，索引的效能也就越差。

具體來說:

  • 情況 1：對整數型別資料進行最佳化。
    • 遇到整數型別的欄位可以用INT 型 。這樣做的理由是，INT 型資料有足夠大的取值範圍，不用擔心資料超出取值範圍的問題。剛開始做專案的時候，首先要保證系統的穩定性，這樣設計欄位型別是可以的。但在資料量很大的時候，資料型別的定義，在很大程度上會影響到系統整體的執行效率。
    • 對於非負型的資料（如自增 ID、整型 IP）來說，要優先使用無符號整型UNSIGNED來儲存。因為無符號相對於有符號，同樣的位元組數，儲存的數值範圍更大。如 tinyint 有符號為 -128 ~ 127，無符號為 0 ~ 255，多出一倍的儲存空間。
  • 情況 2：既可以使用文字型別也可以使用整數型別的欄位，要選擇使用整數型別。
    • 跟文字型別資料相比，大整數往往佔用更少的儲存空間 ，因此，在存取和比對的時候，可以佔用更少的記憶體空間。所以，在二者皆可用的情況下，儘量使用整數型別，這樣可以提高查詢的效率。如：將 IP 地址轉換成整型資料。
  • 情況 3：避免使用 TEXT、BLOB 資料型別。
    • MySQL記憶體臨時表不支援TEXT、BLOB這樣的大資料型別，如果查詢中包含這樣的資料，在排序等操作時，就不能使用記憶體臨時表，必須使用磁碟臨時表進行。並且對於這種資料，MySQL 還是要進行二次查詢，會使 SQL 效能變得很差，但是不是說一定不能使用這樣的資料型別。
    • 如果一定要使用，建議把 BLOB 或是 TEXT 列分離到單獨的擴充套件表中，查詢時一定不要使用 SELECT *，而只需要取出必要的列，不需要 TEXT 列的資料時，不要對該列進行查詢。
  • 情況 4：避免使用 ENUM 型別。
    • 修改 ENUM 值需要使用 ALTER 語句。
    • ENUN 型別的 ORDER BY 操作效率低，需要額外操作。
    • 使用 TINYINT 來代替 ENUM 型別。
  • 情況 5：使用 TIMESTAMP 儲存時間。
    • TIMESTAMP 儲存的時間範圍 1970-01-0100:00:01 ~ 2038-01-19-03:14:07。
    • TIMESTAMP 使用 4 位元組，DATETIME 使用 8 位元組，同時 TIMESTAMP 具有自動賦值以及自動更新的特性。
  • 情況 6：用 DECIMAL 代替 FLOAT 和 DOUBLE 儲存精確浮點數。
    • 非精準浮點：FLOAT 和 DOUBLE。
    • 精準浮點: DECIMAL。
    • DECIMAL 型別為精準浮點數，在計算時不會丟失精度，尤其是財務相關的金融類資料。佔用空間由定義的寬度決定，每 4 個位元組可以儲存 3 位數字，並且小數點要佔用一個位元組。可用於儲存比 BIGINT 更大的整型資料。

總之，遇到資料量大的專案時，一定要在充分了解業務需求的前提下，合理最佳化資料型別，這樣才能充分發揮資源的效率，使系統達到最優。

最佳化插入記錄的速度

插入記錄時，影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等。根據這些情況可以分別進行最佳化。這裡我們分為 MyISAM 儲存引擎和 InnoDB 儲存引擎來講。

MyISAM 儲存引擎

禁用索引

對於非空表，插入記錄時，MySQL 會根據表的索引對插入的記錄建立索引。如果插入大量資料，建立索引就會降低插入記錄的速度。為了解決這種情況，可以在插入記錄之前禁用索引，資料插入完畢後再開啟索引。

禁用索引的語句如下：

ALTER TABLE table_name DISABLE KEYS;

重新開啟索引的語句如下：

ALTER TABLE table_name ENABLE KEYS;

若對空表批次匯入資料，則不需要進行此操作，因為 MyISAM 引擎的表，是在匯入資料之後才建立索引的。

禁用唯一性檢查

插入資料時，MySQL 會對插入的記錄進行唯一性校驗，這種唯一性校驗會降低插入記錄的速度。為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢合，等到記錄插入完畢後再開啟。

禁用唯一性檢查的語句如下：

SET UNIQUE_GHECKS=0;

開啟唯一性檢查的語句如下：

SET UNIQUE_GHECKS=1;
使用批次插入

插入多條記錄時，可以使用一條 INSERT 語句插入一條記錄，也可以使用一條 INSERT 語句插入多條記錄。

插入一條記錄的 INSERT 語句情形如下：

INSERT INTO student VALUES(1, 'zhangsan', 18, 1);
INSERT INTO student VALUES(2, 'lisi', 17, 1);
INSERT INTO student VALUES(3, 'wangwu', 17, 1);
INSERT INTO student VALUES(4, 'zhaoliu', 19, 1);

使用一條 INSERT 語句，插入多條記錄的情形如下：

INSERT INTO student VALUES
(1, 'zhangsan', 18, 1),
(2, 'lisi', 17, 1),
(3, 'wangwu', 17, 1),
(4, 'zhaoliu', 19, 1);

批次插入的速度要比逐條插入快。

使用 LOAD DATA INFILE 批次匯入

當需要批次匯入資料時，如果能用LOAD DATA INFILE語句，就儘量使用。因為 LOAD DATA INFILE 語句匯入資料的速度，比 INSERT 語句快。

InnoDB 儲存引擎

禁用唯一性檢查

插入資料之前，執行set unique_checks=0來禁止對唯一索引的檢查，資料匯入完成之後再執行set unique_checks=1。這個和 MyISAM 引擎的使用方法一樣。

禁用外來鍵檢查

插入資料之前，執行禁止對外來鍵的檢查，資料插入完成之後再恢復對外來鍵的檢查。

禁用外來鍵檢查的語句如下：

SET foreign_key_checks=0;

恢復對外來鍵的檢查語句如下：

SET foreign_key_checks=1;
禁止自動提交

插入資料之前禁止事務的自動提交，資料匯入完成之後，執行恢復自動提交操作。

禁止自動提交的語句如下:

SET autocommit=0;

恢復自動提交的語句如下：

SET autocommit=1;

使用非空約束

在設計欄位的時候，如果業務允許，建議儘量使用非空約束。這樣做的好處是：

  • 進行比較和計算時，省去要對 NULL 值的欄位判斷是否為空的開銷，提高儲存效率。
  • 非空欄位也容易建立索引。因為索引 NULL 列需要額外的空間來儲存，所以要佔用更多的空間。使用非空約束，就可以節省儲存空間（每個欄位 1 個 bit）。

分析表、檢查表與最佳化表

MySQL提供了分析表、檢查表和最佳化表的語句。分析表主要是分析關鍵字的分佈，檢查表主要是檢查表是否存在錯誤，最佳化表主要是消除刪除或更新造成的空間浪費。

分析表

MySQL 中提供了ANALYZE TABLE語句分析表，ANALYZE TABLE 語句的基本語法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

預設的，MySQL 服務會將 ANALYZE TABLE 語句寫到 binlog 中，以便在主從架構中，從服務能夠同步資料。可以新增引數 LOCAL 或者 NO_WRITE_TO_BINLOG 取消將語句寫到 binlog 中。

使用 ANALYZE TABLE分析表的過程中，資料庫系統會自動對錶加一個只讀鎖 。在分析期間，只能讀取表中的記錄，不能更新和插入記錄。ANALYZE TABLE 語句能夠分析 InnoDB 和 MyISAM 型別的表，但是不能作用於檢視。

ANALYZE TABLE 分析後的統計結果，會反應到Cardinality的值，該值統計了表中某一鍵所在的列不重複的值的個數。該值越接近表中的總行數，則在表連線查詢或者索引查詢時，就越優先被最佳化器選擇使用。也就是索引列的 Cardinality 的值，與表中資料的總條數差距越大，即使查詢的時候使用了該索引作為查詢條件，儲存引擎實際查詢的時候使用的機率就越小。下面透過例子來驗證下，Cardinality 可以透過SHOW INDEX FROM tablename檢視。

下面舉例說明，使用下面的語句建立一張 user 表。

  • 建立表並新增 1000 條記錄：

    # 建表
mysql> CREATE TABLE `user1` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `name` VARCHAR(255) DEFAULT NULL,
    ->   `age` INT DEFAULT NULL,
    ->   `sex` VARCHAR(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_name` (`name`) USING BTREE
    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

# 建立函式
mysql> DELIMITER //
mysql> CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
    -> BEGIN   
    -> DECLARE i INT DEFAULT 0;  
    -> SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
    -> RETURN i;  
    -> END //
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> DELIMITER ;

# 建立儲存過程
mysql> DELIMITER //
mysql> CREATE PROCEDURE  insert_user( max_num INT )
    -> BEGIN  
    -> DECLARE i INT DEFAULT 0;   
    ->  SET autocommit = 0;    
    ->  REPEAT  
    ->  SET i = i + 1;  
    ->  INSERT INTO `user1` ( NAME,age,sex ) 
    ->  VALUES ("atguigu",rand_num(1,20),"male");  
    ->  UNTIL i = max_num  
    ->  END REPEAT;  
    ->  COMMIT; 
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

# 向表中新增 1000 條記錄
mysql> CALL insert_user(1000);
Query OK, 0 rows affected (0.06 sec)

  • 檢視錶中的索引：

    image-20231107183245079

  • 執行下列語句：

    # 修改其中一條資料的 name 為 atguigu03，此時，資料庫中應該有兩個不同的 name 值
mysql> UPDATE user1 SET NAME = 'atguigu03' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 但此時 Cardinality 顯示 name 仍然只有一個，因為沒有更新
mysql> SHOW INDEX FROM user1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user1 |          0 | PRIMARY  |            1 | id          | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user1 |          1 | idx_name |            1 | name        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

  • 分析表之後，再次檢視：

    # 分析表
mysql> ANALYZE TABLE user1;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| atguigudb2.user1 | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.01 sec)

# Cardinality 中 name 的值變為 2，如果 name 越接近主鍵，說明其區分度越高
mysql> SHOW INDEX FROM user1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user1 |          0 | PRIMARY  |            1 | id          | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user1 |          1 | idx_name |            1 | name        | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

假如說我們把 id = 3的資料的 name 修改成 atguigudb04，此時直接檢視索引，發現 Cardinality 還是2，但是執行 ANALYZE 後，再檢視會發現 Cardinality 已經變成 3，說明 ANALYZE 啟動了重新整理資料的作用。

檢查表

MySQL 中可以使用CHECK TABLE語句來檢查表。CHECK TABLE 語句能夠檢查 InnoDB 和 MyISAM 型別的表是否存在錯誤。CHECK TABLE 語句在執行過程中也會給表加上只讀鎖 。

對於 MyISAM 型別的表，CHECK TABLE 語句還會更新關鍵字統計資料。而且，CHECK TABLE 也可以檢查檢視是否有錯誤，比如在檢視定義中被引用的表已不存在。該語句的基本語法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED |

其中，tbl_name 是表名；option 引數有 5 個取值，分別是 QUICK、FAST、MEDIUM、EXTENDED 和 CHANGED。各個選項的意義分別是：

  • QUICK：不掃描行，不檢查錯誤的連線。
  • FAST：只檢查沒有被正確關閉的表。
  • CHANGED：只檢查上次檢查後被更改的表和沒有被正確關閉的表。
  • MEDIUM：掃描行，以驗證被刪除的連線是有效的。也可以計算各行的關鍵字校驗和，並使用計算出的校驗和驗證這一點。
  • EXTENDED：對每行的所有關鍵字進行一個全面的關鍵字查詢。這可以確保表是 100% 一致的，但是花的時間較長。

option 只對 MyISAM 型別的表有效，對 InnoDB 型別的表無效。比如：

mysql> CHECK TABLE student;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| atguigudb2.student | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.40 sec)

該語句對於檢查的表可能會產生多行資訊。最後一行有一個狀態的Msg_type值，Msg_text 通常為 OK。如果得到的不是 OK，通常要對其進行修復；是 OK 說明表已經是最新的了。表已經是最新的，意味著儲存引擎對這張表不必進行檢查。

最佳化表

方式 1：使用 OPTIMIZE TABLE 命令

MySQL 中使用OPTIMIZE TABLE語句來最佳化表。但是，OPTILMIZE TABLE 語句只能最佳化表中的VARCHAR、BLOB 或TEXT型別的欄位。一個表使用了這些欄位的資料型別，若已經刪除了表的一大部分資料，或者已經對含有可變長度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）進行了很多更新 ，則應使用 OPTIMIZE TABLE 來重新利用未使用的空間，並整理資料檔案的碎片。

OPTIMIZE TABLE 語句對 InnoDB 和 MyISAM 型別的表都有效，該語句在執行過程中也會給表加上只讀鎖。

OPTIMIZE TABLE 語句的基本語法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
  • LOCAL | NO_WRITE_TO_BINLOG 關鍵字的意義和分析表相同，都是指定不寫入二進位制日誌。

示例：

mysql> CREATE TABLE t1(id INT, name VARCHAR(15)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

# 最佳化MyISAM表
mysql> OPTIMIZE TABLE t1;
+---------------+----------+----------+-----------------------------+
| Table         | Op       | Msg_type | Msg_text                    |
+---------------+----------+----------+-----------------------------+
| atguigudb2.t1 | optimize | status   | Table is already up to date |
+---------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t2(id INT, name VARCHAR(15)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

# 最佳化 InnoDB 表
mysql> OPTIMIZE TABLE t2;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| atguigudb2.t2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| atguigudb2.t2 | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)

Msg_text 顯示的 "Table does not support optimize, doing recreate + analyze instead" 是正常的，可以檢視官網 https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html 驗證。

最佳化過程：在 MyISAM 中，是先分析這張表，然後會整理相關的 MySQL datafile，之後回收未使用的空間；在 InnoDB 中，回收空間是簡單透過 Alter table 進行整理空間。在最佳化期間，MySQL 會建立一個臨時表，最佳化完成之後會刪除原始表，然後會將臨時表 rename 成為原始表。

說明：在多數的設定中，根本不需要執行 OPTIMIZE TABLE。即使對可變長度的行進行了大量的更新，也不需要經常執行，每週一次或每月一次即可，並且只需要對特定的表執行。

示例：

1. 新建一張表，使用儲存過程往裡面放入100W資料，或者更多一些，爭取能夠以兆的單位顯示
2. 檢視伺服器上資料檔案的大小，檔案目錄是/var/1ib/mysq1/所在的資料庫
3. 刪除二分之一的資料，然後再檢視當前資料檔案的大小，會發現此時大小是不變的
4. 使用OPTIMIZE tablename命令最佳化表
5. 再檢視當前資料檔案的大小，會發現此時大小已經變化了，做了空間的回收

最佳化前：

image-20231107190317730

最佳化後：

image-20231107190342411

方式 2：使用 mysqlcheck 命令
# mysqlcheck 是 Linux 中的 rompt，-o 是代表 optimize，最佳化特定表
$ mysqlcheck -o database_name table_name -h127.0.0.1 -uroot -p123456
# 或最佳化所有表
$ mysqlcheck -o --all-databases -h127.0.0.1 -uroot -p123456

使用 Docker 建立的 MySQL 容器，需要加上 -h 引數，指定主機。

示例：

xisun@xisun-develop:~/mysql/mysql-8.0.33-linux-glibc2.17-x86_64-minimal/bin$ ./mysqlcheck -o atguigudb2 -h127.0.0.1 -uroot -p123456 -P3306
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
atguigudb2.class_comment
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
atguigudb2.class_comment1
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
atguigudb2.student
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
atguigudb2.t1                                      Table is already up to date
atguigudb2.t2
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
atguigudb2.user1
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

小結

上述這些方法都是有利有弊的。比如：

  • 修改資料型別，節省儲存空間的同時，你要考慮到資料不能超過取值範圍。
  • 增加冗餘欄位的時候，不要忘了確保資料一致性。
  • 把大表拆分，也意味著你的查詢會增加新的連線，從而增加額外的開銷和運維的成本。

因此，一定要結合實際的業務需求進行權衡。

大表最佳化

當 MySQL 單表記錄數過大時，資料庫的 CRUD 效能會明顯下降，一些常見的最佳化措施如下。

限定查詢的範圍

禁止不帶任何限制資料範圍條件的查詢語句。比如：當使用者在查詢訂單歷史的時候，可以控制在一個月的範圍內。

讀寫分離

經典的資料庫拆分方案，主庫負責寫，從庫負責讀。

  • 一主一從模式：

    image-20231107123115352

  • 雙主雙從模式：

    image-20231107123134706

垂直拆分

當資料量級達到千萬級以上時，有時候我們需要把一個資料庫切成多份，放到不同的資料庫伺服器上，減少對單一資料庫伺服器的訪問壓力。

image-20231107123238030

垂直拆分的優點： 可以使得列資料變小，在查詢時減少讀取的 Block 數，減少 I/O 次數。此外，垂直分割槽可以簡化表的結構，易於維護。

垂直拆分的缺點： 主鍵會出現冗餘，需要管理冗餘列，並會引起 JOIN 操作。此外，垂直拆分會讓事務變得更加複雜。

水平拆分

特點：

  • 儘量控制單表資料量的大小，建議控制在1000 萬以內。1000 萬並不是 MySQL 資料庫的限制，但是過大會造成修改表結構、備份、恢復都會有很大的問題。此時可以用歷史資料歸擋（應用於日誌資料），水平分表（應用於業務資料）等手段來控制資料量大小。

  • 這裡主要考慮業務資料的水平分表策略。將大的資料表按照某個屬性維度分拆成不同的小表，每張小表保持相同的表結構。比如可以按照年份來劃分，把不同年份的資料放到不同的資料表中。2017 年、2018 年和 2019 年的資料就可以分別放到三張資料表中。

  • 水平分表僅是解決了單一表資料過大的問題，但由於表的資料還是在同一臺機器上，其實對於提升 MySQL 併發能力沒有什麼意義，所以水平拆分最好分庫，從而達到分散式的目的。

    image-20231107124344079

水平拆分能夠支援非常大的資料量儲存，應用端改造也少，但分片事務難以解決，跨節點 JOIN 效能較差，邏輯複雜。《Java 工程師修煉之道》的作者推薦儘量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種複雜度，一般的資料表在最佳化得當的情況下，支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，儘量選擇客戶端分片架構，這樣可以減少一次和中介軟體的網路 I/O。

下面補充一下資料庫分片的兩種常見方案：

  • 客戶端代理：分片邏輯在應用端，封裝在 JAR 包中，透過修改或者封裝 JDBC 層來實現。比如噹噹網的 Sharding-JDBC、阿里的 TDDL，是兩種比較常用的實現。

  • 中介軟體代理：在應用和資料中間加了一個代理層，分片邏輯統一維護在中介軟體服務中。比如 Mycat、360 的 Atlas、網易的 DDB 等，都是這種架構的實現。

其他調優策略

伺服器語句超時處理

在 MySQL 8.0 中可以設定伺服器語句超時的限制，單位可以達到毫秒級別 。當中斷的執行語句超過設定的毫秒數後，伺服器將終止查詢影響不大的事務或連線，然後將錯誤報給客戶端。

設定伺服器語句超時的限制，可以透過設定系統變數MAX_EXECUTION_TIME來實現。預設情況下，MAX_EXECUTION_TIME 的值為 0，代表沒有時間限制。示例：

SET GLOBAL MAX_EXECUTION_TIME=2000;
SET SESSION MAX_EXECUTION_TIME=2000; # 指定該會話中SELECT語句的超時時間

建立全域性通用表空間

MySQL 8.0 使用CREATE TABLE SPACE語句來建立一個全域性通用表空間。全域性表空間可以被所有的資料庫的表共享，而且相比於獨享表空間，使用手動建立共享表空間，可以節約後設資料方面的記憶體。可以在建立表的時候，指定屬於哪個表空間，也可以對已有表進行表空間修改等。

下面建立名為 atguigu1 的共享表空間，SQL 語句如下：

mysql> CREATE TABLESPACE atguigu1 ADD datafile 'atguigu1.ibd' file_block_size=16k;
Query OK, 0 rows affected (0.02 sec)

指定表空間，SQL 語句如下：

mysql> CREATE TABLE test(id int, name varchar(18)) Engine=InnoDB DEFAULT charset utf8mb4 TABLESPACE atguigu1;
Query OK, 0 rows affected (0.02 sec)

也可以透過 ALTER TABLE 語句指定表空間，SQL 語句如下：

mysql> ALTER TABLE test TABLESPACE atguigu1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

如何刪除建立的共享表空間？因為是共享表空間，所以不能直接透過 drop table tbname 刪除，這樣操作並不能回收空間。當確定共享表空間的資料都沒用，並且依賴該表空間的表均已經刪除時，可以透過DROP TABLESPACE刪除共享表空間來釋放空間，如果依賴該共享表空間的表存在，就會刪除失敗。如下所示：

mysql> DROP TABLESPACE atguigu1;
ERROR 3120 (HY000): Tablespace `atguigu1` is not empty.

所以，應該首先刪除依賴該表空間的資料表，SQL 語句如下：

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.02 sec)

最後即可刪除表空間，SQL 語句如下：

mysql> DROP TABLESPACE atguigu1;
Query OK, 0 rows affected (0.02 sec)

MySQL 8.0 新特性：隱藏索引對調優的幫助

不可見索引的特性對於效能除錯非常有用。在 MySQL 8.0 中，索引可以被 "隱藏" 和 "顯示"。當一個索引被隱藏時，它不會被查詢最佳化器所使用。也就是說，管理員可以隱藏一個索引，然後觀察對資料庫的影響。如果資料庫效能有所下降，就說明這個索引是有用的，於是將其 "恢復顯示" 即可；如果資料庫效能看不出變化，就說明這個索引是多餘的，可以刪掉了。

需要注意的是當索引被隱藏時，它的內容仍然是和正常索引一樣實時更新的。如果一個索引需要長期被隱藏，那麼可以將其刪除，因為索引的存在會影響插入、更新和刪除的效能。

資料表中的主鍵不能被設定為invisible。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md

相關文章