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

数千万データを持つ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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQLにおける遅いSQLの最適化の方向性について詳しく話しましょう
  • MySQL 選択最適化ソリューションに関する簡単な説明
  • MySQL の垂直テーブルを水平テーブルに変換する方法と最適化のチュートリアル
  • MySqlサブクエリINの実装と最適化
  • MySQLを素早く最適化する
  • MySQL データ挿入最適化メソッドconcurrent_insert
  • MySQL 最適化 query_cache_limit パラメータの説明
  • MySQL の最適化: 高品質の SQL 文を書く方法
  • MySQLクエリ最適化: 100万件のデータに対するテーブル最適化ソリューション
  • MYSQL の 10 の典型的な最適化ケースとシナリオ

<<:  シェアしたい絶妙なApple風無料アイコン素材18セット

>>:  Docker で MySQL マスター スレーブ レプリケーションを実装するためのサンプル コード

推薦する

WeChatアプレットシングルページアプリケーションルーティングを徹底的に理解するための10分

シングルページアプリケーションの特徴「前提:」Web ページには、クリックするとサイト内の他のページ...

Linux mpstat コマンドの使用方法の詳細な説明

1. mpstatコマンド1.1 コマンド形式 mpstat [ -A ] [ -u ] [ -V ...

LinuxでHomebrewを使用する正しい方法

多くの人が Linux Homebrew を使用しています。これをより良く使用するための 3 つのヒ...

Ubuntu のインストール グラフィック ドライバーと Cuda チュートリアル

目次1. 元のドライバーをアンインストールする2. 新しいグラフィックカードドライバーをダウンロード...

MySQL 8.0.15 バージョンのインストールチュートリアル Navicat.list への接続

落とし穴1. ネット上の多くのチュートリアルでは環境変数を設定するファイル名はmy.iniと書いてあ...

HTMLタグIDは変数にできる

<table id=" <%=var1%>">、var1...

js キャンバスはスライダー検証を実現します

この記事の例では、スライダー検証を実装するためのjsキャンバスの具体的なコードを参考までに共有してい...

一般的な Dockerfile コマンドの使用方法の紹介

目次01 CM 02 エントリーポイント03 ワークディレクトリ04 環境05 ユーザー06巻07 ...

Ubuntu 18.04 に Nvidia グラフィック カード ドライバーをインストールするチュートリアル (画像とテキスト付き)

0. 事前準備BIOS でセキュア ブートを無効にします。無効にしないと、サードパーティ ソースを...

MySQL 並列レプリケーションの簡単な分析

01 並列レプリケーションの概念MySQL のマスター スレーブ レプリケーション アーキテクチャで...

html 内の絶対パス URL と相対パス URL、サブディレクトリ、親ディレクトリ、ルート ディレクトリ

絶対 URL は、インターネット上の特定のファイルに必要なすべてのコンテンツを表すために使用されます...

Gitlab-ci を使用してリモート マシンに継続的にデプロイする方法 (詳細なチュートリアル)

簡単に言うと、今日は Gitlab-CI を使用してリモート サーバーに自動的にデプロイする方法につ...

JS での矢印関数と this の記述と理解

目次序文1. JSで関数を書く方法1. 通常の関数の書き方2. 矢印関数の書き方2. 通常の関数でこ...

モバイルブラウザが位置をサポートしない場合の解決策: 修正

具体的な方法は以下の通りです。 CSSコードコードをコピーコードは次のとおりです。 .wap_bot...

CSS で子 div の高さを親コンテナの残りのスペースに合わせる方法

1. フローティング方式を使用する効果画像: コードは次のとおりです: (.content の高さは...