数千万データを持つ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 マスター スレーブ レプリケーションを実装するためのサンプル コード

推薦する

ウェブデザインの概要

<br />1998年に最初の個人ページが誕生してから2008年の今日まで、デザイン業界...

LinuxシステムにDockerをインストールするプロセス

このブログでは、Docker をインストールするプロセスを簡単な手順で説明します。Docker のイ...

CSSカウンター関連属性の学習の詳細な説明

CSS カウンター属性はほぼすべてのブラウザ (IE8 を含む) でサポートされていますが、あまり使...

Mysql クラシック高レベル/コマンドライン操作 (クイック) (推奨)

サーバーとデータベースの構築方法を学ぶ必要があるため、最近は SQL 言語を独学で学び始めました。デ...

Nest.js パラメータ検証とカスタム戻りデータ形式の詳細な説明

0x0 パラメータ検証Nest.jsでは、パラメータ検証業務のほとんどをパイプライン方式で実装してい...

MySQLインデックスに関する詳細を共有する

数日前、同僚からMySQLのインデックスについて質問を受けました。大体わかっているのですが、まだ練習...

URLパラメータに基づくNginx転送

使用シナリオ:ジャンプ パスは、傍受された URL に応じて動的に構成する必要があります。これは、イ...

Javascript イベントキャプチャとバブリングメソッドの詳細な説明

目次1. イベント処理モデル1. イベントバブリング(1)3つのdiv要素にイベントをバインドする(...

Linux環境でグラフデータベースneo4jを構築する方法の説明

Neo4j (Nosql の 1 つ) は、高性能なグラフ データベース (分散をサポートしていませ...

el-table のテーブルを最適化するために仮想リストを使用する方法についての簡単な説明

目次序文解決具体的な実装満たすべき前提条件質問序文テーブルをよく使用します。データ量が多い場合は直接...

Docker を使って LEMP 環境を素早く構築する方法の例

LEMP(Linux + Nginx + MySQL + PHP)は、基本的に今日のWeb開発者にと...

TypeScript 名前空間のマージの説明

目次同じ名前の名前空間をマージする名前空間とその他の種類のマージ同じ名前の名前空間とクラスをマージす...

Linux環境でのDockerインストールチュートリアル

1. 設置環境Dockerは次のCentOSバージョンをサポートしていますCentOS 6.5 (6...

JavaScriptは4桁のランダム検証コードの生成を実装します

この記事の例では、JavaScriptで4桁のランダムな検証コードを生成する具体的なコードを参考まで...