MySQLのパフォーマンスを最大化する読写分離（Read/Write Splitting）の実装手法

データベースの負荷分散において、最も一般的かつ効果的な手法の一つが「読写分離（リードレプリカの活用）」です。MySQLにおける読写分離は、更新処理（INSERT/UPDATE/DELETE）をマスター（主系）サーバーに、参照処理（SELECT）をスレーブ（従系）サーバーに振り分けることで、システム全体のプロセッシング能力を向上させるアーキテクチャです。

読写分離を導入する主な目的

  • スループットの向上： 読み取り専用のクエリを複数のスレーブに分散させることで、参照負荷の高いアプリケーションの応答速度を改善します。
  • 書き込み負荷の軽減： マスターサーバーを書き込み処理に専念させることで、ロック競合を減らし、トランザクションの安定性を高めます。
  • 高可用性の確保： マスター障害時にスレーブを昇格させる、あるいは参照処理を継続するといった冗長構成の基盤となります。

実装アプローチの比較

1. アプリケーション層での制御（コードベース）

アプリケーションコード内で、実行するクエリの内容に応じて接続先のデータソースを動的に切り替える手法です。

メリット： インフラ構成がシンプルに保て、特定のビジネスロジックに基づいた柔軟なルーティング（例：特定のユーザーだけ最新データを追うためにマスターを参照させる等）が容易です。
デメリット： 言語やフレームワークごとに実装が必要であり、データベース構成変更時のコード修正コストが発生します。

// Javaにおける抽象的なデータソースルーティングの例
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // 現在のコンテキストが読み取り専用か判定
        return TransactionContextHolder.isReadOnly() ? "slave" : "master";
    }
}

2. プロキシ・ミドルウェアの利用

アプリケーションとデータベースの間にプロキシサーバーを配置し、SQL文を解析して自動的に振り分けを行う手法です。ProxySQL、MySQL Router、ShardingSphereなどが代表的です。

メリット： アプリケーション側は単一の接続先を意識するだけで済み、構成変更が透過的になります。
デメリット： プロキシ自体が単一障害点（SPOF）にならないよう冗長化が必要であり、ネットワークホップが増えることによる僅かなレイテンシが発生します。

基盤となるレプリケーション設定

読写分離を実現するには、まずMySQLの非同期または準同期レプリケーションを構築する必要があります。

マスター側の設定 (my.cnf)

[mysqld]
server-id = 101
log-bin = mysql-bin
binlog-do-db = target_db
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

スレーブ側の設定 (my.cnf)

[mysqld]
server-id = 201
relay-log = relay-bin
read_only = 1

ProxySQLによるクエリルーティングの構築

ミドルウェアを利用した具体的なルーティング設定例を紹介します。ここでは、書き込み（Hostgroup 0）と読み取り（Hostgroup 1）を分離します。

-- サーバー定義の登録
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, 'master-db-host', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'slave-db-01', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'slave-db-02', 3306);

-- ルーティングルールの定義
-- 1. FOR UPDATE文などはマスターへ強制
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1);

-- 2. 通常のSELECT文はスレーブグループへ
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT.*', 1, 1);

-- 設定の反映
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

運用上の技術的課題と対策

レプリケーション遅延への対応

マスターでの更新がスレーブに反映されるまでには、ネットワークやIOの状態により僅かなタイムラグが生じます。更新直後のデータを参照する場合、古いデータを読み取ってしまう（イベント一貫性）リスクがあります。

  • 解決策1： 重要な処理（注文確定後の詳細表示など）では、SELECTであってもマスターに接続するヒント句（Hint）を使用する。
  • 解決策2： 準同期レプリケーション（Semisynchronous Replication）を採用し、データの整合性を高める。

トランザクションの整合性

トランザクション（BEGIN...COMMIT）内でのSELECTは、不整合を防ぐために原則としてマスターで行うべきです。多くのミドルウェアやフレームワークでは、トランザクション開始時に自動的にマスターへ接続を固定する機能が備わっています。

ヘルスチェックとフェイルオーバー

スレーブがダウンした際に、そのサーバーをルーティング対象から自動的に除外する仕組みが必要です。ProxySQLなどのミドルウェアは標準で死活監視機能を備えていますが、マスターがダウンした場合は、オーケストレーター（Orchestrator）等と連携して新しいマスターを選出し、ルーティング情報を書き換える運用設計が不可欠です。

タグ: MySQL database ReadWriteSplitting ProxySQL Replication

7月29日 01:35 投稿

ホットタグ

©2026 異端開発室