2024年08月04日玄貓（BlackCat）

MySQL 查詢與複製技術最佳化策略

本文探討 MySQL 查詢和複製技術的最佳化策略，涵蓋 JOIN 查詢、GROUP BY 與 ROLLUP、LIMIT 和 OFFSET 等查詢型別的最佳化技巧，以及 MySQL 複製技術的組態、運作原理和最佳實踐。文章提供具體的程式碼範例和詳細的內容解密，幫助讀者理解並應用這些技術，提升資料函式庫效能和系統

資料函式庫效能調校

MySQL 查詢最佳化複製技術效能調校資料函式倉管理

MySQL 資料函式庫效能的提升關鍵在於查詢和複製技術的最佳化。查詢最佳化方面，針對 JOIN 查詢，需確保 ON 或 USING 子句中的欄位已建立索引，並最佳化 GROUP BY 和 ORDER BY 表示式，同時留意 MySQL 版本升級後的相容性。對於 GROUP BY 與 ROLLUP，可考慮在應用程式中進行超級聚合以提升效率。而 LIMIT 和 OFFSET 的最佳化，特別是在分頁場景中，建議記住前一頁最後一筆資料的主鍵，避免使用大的 OFFSET 值。複製技術方面，MySQL 的內建複製功能是構建高效能和高用性應用的基礎。複製技術允許將資料同步到多個副本伺服器，實作讀寫分離、災難還原和資料倉儲等策略。理解複製的工作原理、組態選項和最佳實踐，例如 crash safe 組態、延遲複製和多執行緒複製，對於構建穩健的資料函式庫系統至關重要。

最佳化特定型別的查詢

在資料函式庫系統中，查詢最佳化是至關重要的。本章節將重點介紹如何最佳化不同型別的查詢，包括JOIN查詢、GROUP BY與ROLLUP、LIMIT和OFFSET等。

最佳化JOIN查詢

最佳化JOIN查詢是提升資料函式庫效能的關鍵步驟。以下是一些實用的技巧：

確保ON或USING子句中的欄位已建立索引：在進行表連線時，確保相關欄位有適當的索引可以大幅提高查詢效率。考慮到連線順序，僅在第二個表上建立索引即可，除非該索引對其他查詢也有幫助。
最佳化GROUP BY和ORDER BY表示式：盡量讓這些表示式只涉及單一表格的欄位，這樣MySQL就可以利用索引來進行排序或分組操作。
注意MySQL版本升級：不同版本的MySQL在連線語法、運算元優先順序等方面可能存在差異。升級MySQL版本時，需檢查原有的查詢是否仍能正常運作。

程式碼範例：最佳化JOIN查詢

-- 原始查詢
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

-- 最佳化後的查詢（使用延遲連線）
SELECT film.film_id, film.description
FROM sakila.film
INNER JOIN (
   SELECT film_id FROM sakila.film
   ORDER BY title LIMIT 50, 5
) AS lim USING(film_id);

內容解密：

原始查詢直接對sakila.film表進行排序並分頁，但當資料量大時效率較低。
最佳化後的查詢先在子查詢中使用索引對film_id進行排序和分頁。
主查詢再透過film_id與原表進行連線，取得所需的其他欄位（如description）。
這種「延遲連線」技術減少了需要掃描的資料量，提高了查詢效率。

最佳化GROUP BY與ROLLUP

使用WITH ROLLUP可以進行超級聚合運算，但有時效率不佳。可以透過檢查執行計畫，嘗試移除WITH ROLLUP來比較執行效率，或考慮在應用程式中進行超級聚合。

程式碼範例：最佳化GROUP BY與ROLLUP

-- 使用WITH ROLLUP的查詢
SELECT column1, SUM(column2) 
FROM table_name 
GROUP BY column1 WITH ROLLUP;

-- 在應用程式中進行超級聚合
SELECT column1, SUM(column2) 
FROM table_name 
GROUP BY column1;

內容解密：

使用WITH ROLLUP可以在資料函式庫層級進行超級聚合，但可能導致效能問題。
另一種方法是先取得分組結果，再在應用程式中進行匯總運算。
這種做法可以靈活地處理超級聚合需求，並可能提高整體效能。

最佳化LIMIT和OFFSET

對於包含LIMIT和OFFSET的查詢，特別是在分頁場景中，最佳化至關重要。

程式碼範例：最佳化LIMIT和OFFSET

-- 原始查詢
SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

-- 下一頁查詢（記住最後一筆rental_id）
SELECT * FROM sakila.rental 
WHERE rental_id < 16030 
ORDER BY rental_id DESC LIMIT 20;

內容解密：

當使用大的OFFSET值時，MySQL需要生成並丟棄大量不需要的行，效率低下。
一種最佳化方法是記住前一頁的最後一筆資料的主鍵（如rental_id），並在下一頁查詢中使用該值作為過濾條件。
這種方法避免了使用大的OFFSET，大大提高了分頁查詢的效率。

MySQL 查詢最佳化與複製技術

查詢最佳化技術深度解析

在 MySQL 資料函式庫的應用中，查詢最佳化是提升效能的關鍵步驟。最佳化查詢需要深入瞭解資料函式庫結構、索引設計以及查詢邏輯之間的相互作用。最佳化查詢的核心目標是減少回應時間，理解查詢執行的過程對於分析時間消耗至關重要。

查詢最佳化涉及停止不必要的工作、減少操作次數以及加快執行速度三個主要導向。實際操作中，可能需要手動調整查詢條件，例如將 WHERE、LIMIT 和 ORDER BY 等條件「下推」到 UNION 中的每個 SELECT 陳述式，以提高效率。

使用 UNION ALL 取代 UNION 可以避免 MySQL 在臨時表中進行重複資料消除的額外開銷。即使用 UNION ALL，MySQL 仍然會建立臨時表來存放結果，因此理解查詢執行過程對於最佳化至關重要。

MySQL 複製技術詳解

MySQL 的內建複製功能是構建高效能應用的基礎，透過「擴充套件」架構實作大規模應用。複製技術允許將一個伺服器的資料同步到多個副本伺服器上，這不僅適用於高效能應用，也是實作高用性、擴充套件性、災難還原和資料倉儲等策略的根本。

複製技術的核心挑戰與解決方案

複製技術主要解決的是在相同拓撲結構中的資料函式庫例項之間保持資料同步的問題。源伺服器將修改資料或資料結構的事件寫入日誌，副本伺服器讀取這些事件並重放，從而實作資料同步。然而，這是一個非同步過程，副本資料與源資料之間可能存在延遲。

MySQL 的複製大多數情況下是向後相容的，但舊版本的伺服器可能無法作為新版本的副本，因為它們可能無法理解新功能或 SQL 語法。建議在升級前測試複製設定，以確保相容性。

複製技術的常見應用場景

資料分佈：MySQL 複製可用於在不同地理位置維護資料副本，適用於跨資料中心或雲區域的場景。
讀取流量擴充套件：透過將讀取查詢分散到多個副本伺服器，可以有效擴充套件讀取密集型應用。
備份：複製技術可用於協助備份，但需要注意的是，副本並不能取代備份。

內容解密：

上述 SQL 陳述式使用 UNION ALL 將 table1 和 table2 的查詢結果合併。與使用 UNION 不同，UNION ALL 不會對結果進行重複資料消除，因此效率更高。這種寫法適用於需要保留重複資料的場景。透過減少不必要的重複資料檢查，可以顯著提高查詢效能。

MySQL 複製技術的應用與運作原理

MySQL 的複製技術（replication）是一種將資料從一個 MySQL 伺服器（來源伺服器）同步到另一個或多個 MySQL 伺服器（複製伺服器）的技術。這種技術在許多場景中非常有用，例如：

分析與報表

使用專門的複製伺服器來處理分析/報表查詢（OLAP），可以有效地隔離這些查詢對外部客戶請求的影響。複製技術為這種隔離提供了基礎。

高用性和容錯移轉

複製可以幫助避免 MySQL 成為應用程式的單點故障。一個好的容錯移轉系統結合複製，可以顯著減少停機時間。

測試 MySQL 升級

通常會建立一個複製伺服器，升級其 MySQL 版本，並測試查詢是否按預期工作，然後再升級所有例項。

複製的工作原理

在深入瞭解複製的設定細節之前，讓我們先快速瞭解 MySQL 如何複製資料。這裡我們介紹最簡單的複製拓撲結構：單一來源和單一複製伺服器。

總的來說，複製是一個簡單的三步驟過程：

來源伺服器將資料變更記錄到其二進位制日誌中作為“二進位制日誌事件”。
複製伺服器將來源伺服器的二進位制日誌事件複製到其本地的中繼日誌。
複製伺服器重放中繼日誌中的事件，將變更應用到其本地資料。

圖示說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖示說明

rectangle "二進位制日誌事件" as node1
rectangle "寫入中繼日誌" as node2
rectangle "應用變更" as node3

node1 --> node2
node2 --> node3

@enduml

此圖示展示了 MySQL 複製的基本工作原理。

複製格式選擇

MySQL 為複製提供了三種不同的二進位制日誌格式：根據陳述式（statement-based）、根據行（row-based）和混合（mixed）。這些格式由 binlog_format 組態引數控制，它決定了資料如何寫入二進位制日誌。

根據陳述式的複製

記錄在來源伺服器上更改資料的查詢。當複製伺服器讀取事件並執行它時，它重新執行來源伺服器執行的 SQL 查詢。這種格式的主要優點是簡單且緊湊。但是，它通常難以處理非確定性查詢。

根據行的複製

將表示行變更的事件寫入二進位制日誌。這種方法具有確定性，可以精確地檢視哪些行被更改以及更改後的值是什麼。缺點是寫入每個受影響行的事件可能會顯著增加二進位制日誌的大小。

混合模式

嘗試結合根據陳述式和根據行的優點，預設使用根據陳述式的格式，只有在需要時才切換到根據行。然而，這種模式可能會導致二進位制日誌中的事件不可預測。

我們的建議是堅持使用根據行的複製，除非暫時需要使用根據陳述式的複製。根據行提供了最安全的資料複製方法。

全域交易識別碼（GTIDs）

在 MySQL 5.6 之前，複製伺服器必須跟蹤它從來源伺服器讀取的二進位制日誌檔案和位置。為了簡化這個過程，MySQL 引入了全域交易識別碼（GTIDs）。每個來源伺服器提交的交易都被分配了一個唯一的識別碼，這個識別碼是 server_uuid 和遞增的交易號的組合。當交易被寫入二進位制日誌時，GTID 也會被寫入。複製伺服器使用 GTID 來記錄已完成的交易，從而簡化了重新連線和還原的過程。

GTID 的優點

簡化了複製伺服器的還原和重新連線過程。
避免了由於錯誤地重新連線到錯誤的位置而導致的重複或遺漏事件。

總之，MySQL 的複製技術透過提供多種複製格式和 GTID 等功能，為資料的高用性、隔離和分析提供了強大的支援。選擇合適的複製策略對於確保資料的一致性和系統的可靠性至關重要。

MySQL複製的進階組態與最佳實踐

在MySQL複製的組態中，有幾個重要的設定可以大幅提升複製的穩定性和資料的安全性。首先，我們來討論如何讓複製變得更加「crash safe」，也就是在發生故障時能夠更好地還原。

使複製更為 Crash Safe 的組態

為了最小化複製中斷的可能性，建議進行以下組態：

1. `innodb_flush_log_at_trx_commit = 1`

這個設定確保了每筆交易的日誌都會被寫入並同步到磁碟上。這是完全符合ACID規範的設定，能夠最大程度地保護資料。因為二進位制日誌事件會先被提交，然後交易才會被提交並重新整理到磁碟。將此設定為1會增加磁碟寫入操作，但能夠確保資料的永續性。

2. `sync_binlog = 1`

這個變數控制MySQL同步二進位制日誌資料到磁碟的頻率。將此值設為1意味著在每筆交易之前都會進行同步。這樣可以防止在伺服器當機時丟失交易。同樣，這也會增加磁碟寫入操作。

3. `relay_log_info_repository = TABLE`

過去，MySQL複製依賴磁碟上的檔案來追蹤複製位置。這意味著複製完成的交易需要作為第二步同步到磁碟。如果在交易提交和同步之間發生當機，磁碟上的檔案就會有錯誤的檔案和位置資訊。現在，這些資訊已經被移到MySQL內的InnoDB表格中，使得複製可以在同一個交易中更新交易和中繼日誌資訊，從而建立一個原子操作，有助於當機還原。

4. `relay_log_recovery = ON`

簡而言之，當檢測到當機時，relay_log_recovery會丟棄所有本地中繼日誌，並從來源伺服器取得缺失的資料。這確保了在當機中可能發生的任何損壞或不完整的中繼日誌都是可還原的。這個設定也消除了使用sync_relay_log的必要，因為在發生當機時，中繼日誌會被刪除，因此不需要額外的操作來同步它們到磁碟。

延遲複製

在某些情況下，在您的拓撲結構中擁有一個延遲的複製伺服器是有優勢的。這種策略可以用來保持資料線上且執行，但始終落後於實時幾個小時或幾天。這是透過CHANGE REPLICATION SOURCE TO陳述式和SOURCE_DELAY選項來組態的。

延遲複製的優勢與挑戰

延遲複製可以在某些資料丟失的場景下提供更快的修復時間，例如意外地丟棄了一個表格。然而，它也為許多其他操作方面帶來了複雜性。如果您決定需要使用延遲複製，您還應該考慮如何正確地將這個延遲複製伺服器排除在成為來源節點候選者之外（如果您的寫入容錯移轉是自動化的，這一點尤為重要），如何監控複製，以及如何處理這個特殊的複製伺服器。

多執行緒複製

MySQL歷史上的一個挑戰是，雖然您可以在來源伺服器上平行寫入，但您的複製伺服器是單執行緒的。現代MySQL版本提供了多執行緒複製（見圖9-2），您可以在本地執行多個SQL應用程式執行緒來應用來自中繼日誌的變更。

多執行緒複製的模式

有多執行緒複製的兩種模式：DATABASE和LOGICAL_CLOCK。DATABASE選項使用多個執行緒來更新不同的資料函式庫；沒有兩個執行緒會同時更新同一個資料函式庫。如果您將資料分散在MySQL的多個資料函式庫中，並且一致且並發地更新它們，這種方法效果很好。另一個選項LOGICAL_CLOCK允許對同一個資料函式庫進行平行更新，只要它們是同一個二進位制日誌組提交的一部分。

什麼是二進位制日誌組提交？

為了最好地解釋這一點，我們將使用Morgan Tocker的一個很好的類別比：從A點到B點運送乘客的單一渡輪的例子。

在MySQL 5.0中，渡輪會從A點接上下一位乘客，並將他們運送到B點。A點和B點之間的旅程大約需要10分鐘的往返時間，因此在渡輪運輸途中可能會有多名新乘客到達。

#### 內容解密：

這段文字使用了一個渡輪運送乘客的例子來說明MySQL中的二進位制日誌組提交的概念。這種類別比有助於理解在MySQL的不同版本中，交易提交是如何被處理和最佳化的。在MySQL 5.0中，每個交易的提交就像渡輪每次只運送一位乘客，這種方式可能導致資源利用率不高。透過這種類別比，讀者可以更好地理解MySQL內部的工作原理以及最佳化策略的重要性。

MySQL複製技術深度解析

MySQL的複製技術是一種重要的資料同步機制，廣泛應用於分散式資料函式庫系統中。複製技術允許資料從一個主伺服器（source）同步到多個從伺服器（replica），確保資料的高用性和容錯能力。

多執行緒複製的最佳化

在MySQL 5.6及以後版本中，多執行緒複製（Multithreaded Replication）得到了顯著改進。這種機制透過平行處理事務，提高了複製的效率。我們可以將這個過程比喻為一艘渡輪（ferry）在兩個港口（point A和point B）之間運輸乘客（transactions）。

渡輪模型的運作原理

在MySQL 5.6中，渡輪會在每次傳回point A時，收集所有等待的乘客並將他們運送到point B。這種機制在實際應用中表現出更好的效能，因為它減少了往返次數，提高了整體的吞吐量。

人工延遲組態

MySQL 5.7及以後版本進一步最佳化了這個機制，引入了人工延遲（artificial delay）的組態變數：binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count。透過適當地設定這些引數，可以進一步提高複製的效率。

組態多執行緒複製

要啟用多執行緒複製，需要設定replica_parallel_workers為非零值。如果只操作一個資料函式庫，還需要將replica_parallel_type設定為LOGICAL_CLOCK。此外，為了確保事務的提交順序不被打亂，需要啟用replica_preserve_commit_order。

最佳化執行緒數量

確定最佳的replica_parallel_workers值有兩種方法：

粗略估計法：停止複製，測量不同執行緒數量下的複製延遲，直到找到最佳值。
精確測量法：透過Performance Schema監控每個應用執行緒的忙碌程度，從而確定最佳的平行度。

使用Performance Schema監控

首先，需要啟用相關的Performance Schema消費者和儀器：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE 'events_transactions%';

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'transaction';

然後，執行查詢以取得每個執行緒處理的事務數量：

SELECT 
    events_transactions_summary_by_thread_by_event_name.thread_id AS THREAD_ID,
    events_transactions_summary_by_thread_by_event_name.count_star AS COUNT_STAR
FROM 
    events_transactions_summary_by_thread_by_event_name
WHERE 
    events_transactions_summary_by_thread_by_event_name.thread_id IN (
        SELECT 
            replication_applier_status_by_worker.thread_id
        FROM 
            replication_applier_status_by_worker
    );

內容解密：

查詢目的：此查詢用於取得每個複製應用執行緒處理的事務數量，幫助我們評估平行度。
events_transactions_summary_by_thread_by_event_name表：該表儲存了按執行緒和事件名稱匯總的事務資料。
replication_applier_status_by_worker表：該表提供了複製應用工作執行緒的狀態資訊，包括其thread_id。
THREAD_ID欄位：表示執行緒ID，用於識別不同的複製應用執行緒。
COUNT_STAR欄位：表示每個執行緒處理的事務總數。
查詢邏輯：透過子查詢篩選出與複製應用工作執行緒相關的thread_id，並取得這些執行緒對應的事務處理數量。

半同步複製

半同步複製（Semisynchronous Replication）要求至少一個從伺服器確認接收到事務後，主伺服器才會提交事務。這增加了事務的延遲，但提高了資料的一致性。

運作機制

確認機制：從伺服器必須將接收到的事務寫入其relay log，並向主伺服器傳送確認訊息。
降級機制：如果在指定時間內沒有收到足夠的確認訊息，主伺服器會退回到非同步複製模式。

使用場景與限制

半同步複製不是為了防止資料丟失，而是作為更強壯的容錯移轉方案的一部分。由於它會在無法獲得確認時退回到非同步模式，因此不應完全依賴它來保證資料完整性。

複製過濾器

複製過濾器（Replication Filters）允許選擇性地複製部分資料。然而，這種做法可能會導致資料不一致，應謹慎使用。

二進位日誌過濾

透過設定binlog_do_db和binlog_ignore_db，可以控制哪些資料函式庫被記錄到二進位日誌中。然而，不當使用這些選項可能會導致資料丟失。

從伺服器過濾

從伺服器上的replicate_*選項允許過濾來自relay log的事件。然而，這種做法同樣需要謹慎，因為不當的過濾規則可能會導致資料不一致。

最佳化特定型別的查詢

最佳化JOIN查詢

程式碼範例：最佳化JOIN查詢

內容解密：

最佳化GROUP BY與ROLLUP

程式碼範例：最佳化GROUP BY與ROLLUP

內容解密：

最佳化LIMIT和OFFSET

程式碼範例：最佳化LIMIT和OFFSET

內容解密：

MySQL 查詢最佳化與複製技術

查詢最佳化技術深度解析

MySQL 複製技術詳解

複製技術的核心挑戰與解決方案

複製技術的常見應用場景

內容解密：

MySQL 複製技術的應用與運作原理

分析與報表

高用性和容錯移轉

測試 MySQL 升級

複製的工作原理

圖示說明

複製格式選擇

根據陳述式的複製

根據行的複製

混合模式

全域交易識別碼（GTIDs）

GTID 的優點

MySQL複製的進階組態與最佳實踐

使複製更為 Crash Safe 的組態

1. innodb_flush_log_at_trx_commit = 1

2. sync_binlog = 1

3. relay_log_info_repository = TABLE

4. relay_log_recovery = ON

延遲複製

延遲複製的優勢與挑戰

多執行緒複製

多執行緒複製的模式

什麼是二進位制日誌組提交？

#### 內容解密：

MySQL複製技術深度解析

多執行緒複製的最佳化

渡輪模型的運作原理

人工延遲組態

組態多執行緒複製

最佳化執行緒數量

使用Performance Schema監控

內容解密：

半同步複製

運作機制

使用場景與限制

複製過濾器

二進位日誌過濾

從伺服器過濾

1. `innodb_flush_log_at_trx_commit = 1`

2. `sync_binlog = 1`

3. `relay_log_info_repository = TABLE`

4. `relay_log_recovery = ON`