数千万データを持つMySQLテーブルを最適化する実践記録

序文

まずここで説明させてください。インターネット上では、Alibaba では 500 万のデータを異なるデータベースとテーブルに分割する必要があると規定されていると言う人が多くいます。実はこの 500 万は固定値ではなく、MySQL の構成とマシンのハードウェアに関連しています。パフォーマンスを向上させるために、MySQL はテーブル インデックスをメモリにロードします。しかし、テーブル内のデータが一定量に達すると、メモリはこれらのインデックスを保存できなくなります。インデックスを保存できないと、ディスク IO しか実行できず、パフォーマンスが低下します。

実践的なチューニング

1000w のデータと 1 つの主キー インデックスのみを含むテーブルがあります。

テーブル `user` を作成します (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) デフォルト NULL コメント 'アカウント',
  `pwd` varchar(20) デフォルト NULL コメント 'パスワード',
  `addr` varchar(80) デフォルト NULL コメント 'アドレス',
  `tel` varchar(20) デフォルト NULL コメント '電話',
  `regtime` char(30) デフォルト NULL コメント '登録時刻',
  `age` int(11) デフォルト NULL コメント '年齢',
  主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000003 デフォルトCHARSET=utf8;

16 についてすべてを問い合わせます。かなり遅いですね。通常、eコマース プラットフォームなどのバックエンド システムがあり、これがユーザー テーブルです。バックエンド管理システムは通常、これらのユーザー情報を照会し、新しいユーザーを直接追加したり、ユーザーを削除したりするなどの操作を実行します。

ここでは2つの要件があります。1つはカウントを照会すること、もう1つはページごとに照会することです。

カウントにかかる時間とページングクエリにかかる時間をそれぞれテストしてみましょう。

select * from user limit 1, 10 //ほとんど使用されない select * from user limit 1000000, 10 //0.35秒
ユーザー制限 5000000, 10 から * を選択 //1.7 秒
ユーザー制限 9000000, 10 から * を選択 //2.8 秒
ユーザーからcount(1)を選択 //1.7秒

上記のクエリ時間から、ページングクエリの場合は、クエリするデータが増えるほど時間がかかり、クエリ数にも 1.7 秒かかることがわかります。これは明らかに当社の要件を満たしていません。したがって、ここで最適化する必要があります。まず、インデックスの最適化を試してみましょう。これは、主キー インデックスのみを使用した実行プランです。

テーブル `user` を変更し、インデックス `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`) を追加します。

上記の実行プランを見ると、type が all->index に変更され、sindex インデックスが使用されているにもかかわらず、クエリ速度は実際には変化していません。

実際、結合インデックスを作成するのは、完全なテーブルクエリではなく条件付きクエリを高速化するためです。

select * from user where uname='6.445329111484186' //3.5秒（ジョイントインデックスなし）
select * from user where uname='6.445329111484186' //0.003秒（ジョイントインデックス付き）

これが、共同インデックスを持つ場合と持たない場合の違いです

ここでは基本的に、インデックスの有無にかかわらず、完全なテーブル クエリを実行すると効率が非常に遅くなることが証明できます。

インデックス結果はもはや役に立たないので、他の解決策を探すしかありません。前回のMySQLインタビューで述べたように、countはテーブルに別々に保存できます。

CREATE TABLE `属性` (
  `id` int(11) NULLではない、
  `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'テーブル名',
  `formcount` int(11) NOT NULL COMMENT 'テーブルの合計データ',
  主キー (`id`)
) エンジン=InnoDB デフォルト文字セット=utf8 COLLATE=utf8_bin;

ここで言いたいのは、この種のテーブルは一般的にすべてをクエリするのではなく、1つだけをクエリするため、テーブルを作成するときにハッシュを作成できるということです。

select formcount from attribute where formname='user' //ほとんど使用されない

カウントの最適化が完了しました。上記の選択条件がある場合は、インデックスを作成してインデックスフィルタリングでクエリを実行できるため、カウントを読み取る必要はありません。

まあ、カウントは問題ありませんが、ページングクエリを最適化するにはどうすればよいでしょうか?ここではサブクエリを使用して最適化することができます

ユーザーから*を選択
id>=(ユーザー制限 9000000,1 から id を選択) 制限 10 //1.7 秒

実際、このようにサブクエリを記述して ID を決定することは、カバーインデックスを介してクエリを実行することになります。効率が大幅に向上します。ただし、ここでの私のテストは 1.7 秒です。以前、この側面を最適化したとき、クエリ時間はこれよりも短くなりました。データを生成して自分でテストすることもできます。

ただし、データの量が多すぎる場合は、es を使用するか、デフォルトの選択を行うことをお勧めします。カウントは個別にリストできます。

この時点で、数千万のデータに対するページングクエリの最適化が完了しました。

要約する

これで、MySQL 数千万データテーブル最適化に関するこの記事は終了です。MySQL 数千万データテーブル最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。