MySQL MVCC を使用してデータベース設計を最適化し、アプリケーションのパフォーマンスを向上させる-mysql チュートリアル-php.cn

MySQL MVCC を使用してデータベース設計を最適化し、アプリケーションのパフォーマンスを向上させる

WBOY

リリース： 2023-09-08 08:34:04

オリジナル

1317 人が閲覧しました

使用MySQL MVCC 优化数据库设计，提高应用性能

MySQL MVCC を使用してデータベース設計を最適化し、アプリケーションのパフォーマンスを向上させる

要約: 今日のインターネットアプリケーションでは、データベースのパフォーマンスはシステムの安定した動作と応答時間にとって非常に重要です。。最も一般的に使用されているリレーショナルデータベース管理システムの 1 つである MySQL は、マルチバージョン同時実行制御 (MVCC) を使用して、データベース設計時の同時実行パフォーマンスとデータの一貫性を向上させます。この記事では、MVCC の基本原理と MySQL でのその実装を紹介し、データベース設計を最適化する例をいくつか示します。

MVCC の基本原則
マルチバージョン同時実行制御 (MVCC) は、トランザクションの分離レベルを実装するために使用されるテクノロジです。データベース内の複数のトランザクション間のスナップショットを保存することで同時実行制御を実現します。各トランザクションは、トランザクションの開始前にコミットされたすべてのデータを含む個別のスナップショットを参照できます。

MVCC の基本原則は、各データ行をバージョンチェーンとしてマークすることによってスナップショットを作成および管理することです。トランザクションが開始されると、新しいスナップショットが作成され、現在のタイムスタンプがトランザクションに関連付けられます。その後、トランザクションは、他の同時トランザクションからの干渉を受けることなく、スナップショット内のデータを読み取り、変更できるようになります。

MySQL での MVCC 実装
MySQL は、ロールバックポインター (rollpointer) とバージョン番号 (version) という 2 つの重要なレコードフィールドを使用して MVCC を実装します。ロールバックポインタは、最初に挿入および変更されたデータ行を指し、元に戻すログを記録することによって即時回復が実現されます。バージョン番号は増加するカウンターであり、新しいトランザクションによってデータが変更されるたびに、バージョン番号が増加します。

読み取り操作中、MySQL は読み取りトランザクションのタイムスタンプに基づいて可視性を決定します。データのバージョン番号が現在のトランザクションのタイムスタンプ以上の場合、データは表示されます。それ以外の場合は、UNDO ログから古いバージョンのデータを取得する必要があります。

書き込み操作中に、MySQL は新しいバージョンのデータ行を作成し、新しいバージョンのデータを新しいバージョンチェーンに書き込み、古いバージョンのデータを UNDO ログに移動します。この利点は、同時実行状況において、異なるトランザクションが競合することなく古いバージョンと新しいバージョンのデータを同時に読み取ることができることです。

データベース設計の最適化例
(1) 適切なデータ型を使用する
適切なデータ型を使用すると、ストレージ容量の使用量が削減され、データの読み取りと書き込みの効率が向上します。最も単純でコンパクトなデータ型を選択し、過度に長い文字や不要なデータ型の使用を避けるようにしてください。

たとえば、フィールドにブール値のみを格納する必要がある場合は、BOOL 型の代わりに TINYINT(1) を使用できます。これは、TINYINT(1) が占有する記憶領域は 1 バイトだけであるためです。

(2) インデックスの合理的な使用
インデックスはクエリの効率を向上させる重要な方法ですが、インデックスが多すぎる、または不合理であると、書き込み操作のパフォーマンスが低下します。インデックスを設計するときは、実際のクエリ要件とデータ量に基づいて、適切なフィールドとインデックスタイプを選択する必要があります。

たとえば、頻繁に範囲クエリが実行されるフィールドの場合、クエリの効率を向上させるために、複数列インデックスの使用またはカバーインデックスの使用を検討できます。

(3) バッチ操作とトランザクション制御
バッチ操作により、IO 回数が削減され、データ処理の効率が大幅に向上します。大量の挿入、更新、および削除操作の場合は、バッチ操作ステートメント (INSERT INTO ... VALUES ... など) を使用して、複数のデータを一度に処理できます。

同時に、トランザクションを合理的に使用することで、データの一貫性と整合性を確保できます。同時実行性の高いシナリオでは、適切なトランザクション分離レベルと合理的なトランザクション制御を使用することで、データの競合や競合を回避できます。

(4) パーティショニングとサブテーブル
パーティショニングとサブテーブルは、大きなテーブルのパフォーマンスの問題を解決する効果的な手段です。大きなテーブルを複数の小さなテーブルに分割することで、データを異なるディスクに保存できるため、1 つのテーブル内のデータ量が削減され、クエリの効率が向上します。

たとえば、時間範囲によるクエリのシナリオでは、1 年間のデータを月ごとに異なるパーティションテーブルに分割し、各パーティションテーブルにはその月のデータのみを含めることができます。

コード例:

-- 创建表
CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(50) NOT NULL,
  `email` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_username` (`username`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `user` (`username`, `password`, `email`) VALUES
('user1', 'password1', 'user1@example.com'),
('user2', 'password2', 'user2@example.com'),
('user3', 'password3', 'user3@example.com');


-- 查询数据
SELECT * FROM `user` WHERE `username` = 'user1';

-- 更新数据
UPDATE `user` SET `password` = 'newpassword' WHERE `username` = 'user1';

-- 删除数据
DELETE FROM `user` WHERE `username` = 'user1';

ログイン後にコピー

結論: MySQL MVCC を使用すると、データベース設計を最適化し、アプリケーションのパフォーマンスを向上させることができます。適切なデータ型、インデックスの合理的な使用、バッチ操作とトランザクション制御、パーティショニングとテーブルの分割により、IO 操作が効果的に削減され、クエリ効率が向上し、同時実行の競合が軽減され、システム全体のパフォーマンスと安定性が向上します。

参考資料:

MySQL 5.7 リファレンスマニュアル - 14.2.5 InnoDB 行形式と MVCC の詳細な紹介 (https://dev.mysql.com/doc/refman/5.7/) en/innodb-row-format-and-mvcc.html)
ハイパフォーマンス MySQL、第 3 版 (https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/)

以上がMySQL MVCC を使用してデータベース設計を最適化し、アプリケーションのパフォーマンスを向上させるの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。