インデックスを使用して数千万のデータを持つ MySQL のクエリ速度を最適化する

1. インデックスの役割

一般的に言えば、インデックスは本の目次に相当します。条件に基づいてクエリを実行する場合、インデックスがない場合、テーブル全体をスキャンする必要があります。データ量が少ない場合は問題ありませんが、データ量が100万、さらには数千万を超えると、クエリSQLの実行に数十秒以上かかることがよくあります。5秒を超えると、すでに耐えられません。

問題がソフトウェアで解決できる場合、ハードウェアでは解決できません。結局のところ、ハードウェアコードの拡張は高価であり、費用対効果が低すぎます。低コストで効果的な解決策は、インデックスを適切に追加することです。インデックスを適切に使用すると、クエリ速度が数千倍も向上し、驚くべき効果が得られます。

(ii) MySQL インデックスの種類:

MySQL インデックスには、主キー インデックス、通常のインデックス、一意のインデックス、全文インデックス、クラスター化インデックス (複数列インデックス) の 5 種類があります。

ユニーク インデックスとフルテキスト インデックスはほとんど使用されません。主に、主キー インデックス、共通インデックス、クラスター化インデックスに焦点を当てます。

1) 主キー インデックス: 主キー インデックスは、主キーに追加されるインデックスです。主キーを設定すると、MySQL は自動的に主キー インデックスを作成します。

2) 通常のインデックス: 主キー以外の列に作成されるインデックス。

3) クラスター化インデックス: 複数の列に作成されるインデックス。

(III) インデックス構文:

テーブルのインデックスを表示します: テーブル名からインデックスを表示します。

通常のインデックスを作成します: alter table テーブル名 add index インデックス名 (インデックスを追加する列)

クラスター化インデックスを作成します: alter table table name add index index name (indexed column 1, indexed column 2)

テーブルのインデックスを削除します: drop index index name on table name;

（IV）パフォーマンステスト

テスト環境: ブロガーの作業用デスクトップ

プロセッサ: Intel Core i5-4460 3.2GHz;

メモリ 8G;

64 ビット Windows。

1: テストテーブルを作成する

`test_user` が存在する場合はテーブルを削除します。
テーブル `test_user` を作成します (
 `id` bigint(20) 主キーがnullでない AUTO_INCREMENT、
 `username` varchar(11) デフォルト NULL,
 `性別` varchar(2) デフォルト NULL,
 `password` varchar(100) デフォルト NULL
)ENGINE=MyISAM デフォルト文字セット=utf8;

ストレージ エンジンには MyISAM を使用します。このエンジンにはトランザクションがなく、挿入速度が非常に速いため、数千万のテスト データをすばやく挿入できます。データを挿入した後、ストレージ タイプを InnoDB に変更します。

2: ストアドプロシージャを使用して1000万件のレコードを挿入する

プロシージャ myproc() を作成する 
始める 
num int を宣言します。 
num=1 を設定します。 
num <= 10000000 の場合 
test_user(ユーザー名、性別、パスワード) に値 (数値、'confidential'、パスワード (数値)) を挿入します。 
num=num+1 を設定します。
終了しながら;
 終わり

myproc() を呼び出す;

MyISAM エンジンを使用したため、1,000 万件のレコードを挿入するのに 246 秒しかかかりませんでした。InnoDB エンジンの場合は、数時間かかります。

次に、ストレージ エンジンを InnDB に戻します。次のコマンドを使用します: alter table test_user engine=InnoDB; このコマンドの実行には約 5 分かかりますので、しばらくお待ちください。

ヒント: これはテストです。実稼働環境でストレージ エンジンを勝手に変更しないでください。alter table 操作により、テーブル全体がロックされます。注意して使用してください。 2 つ目: MyISAM エンジンにはトランザクションがなく、データをメモリに書き込んでから定期的にディスクにフラッシュするだけなので、突然の停電が発生するとデータが失われます。 InnDB エンジンはデータをログに書き込み、定期的にディスクにフラッシュするため、突然の停電を心配する必要はありません。したがって、InnDB が実際の運用で使用できる場合は、InnDB を使用する必要があります。

3: SQLテスト

id=999999 の test_user から id、ユーザー名、性別、パスワードを選択します。

所要時間: 0.114秒。

テーブルを作成するときに id を主キーとして設定したため、この SQL を実行するときに主キー インデックスが使用され、クエリ速度が非常に速くなりました。

select id,username,gender,password from test_user where username='9000000' を実行してみましょう。
所要時間: 4.613秒。

ユーザー名列に通常のインデックスを追加します。

ALTER TABLE `test_user` ADD INDEX index_name(username);

このプロセスには約 54.028 秒かかります。インデックス構築プロセスではテーブル全体をスキャンし、インデックスを 1 つずつ構築するため、当然ながら時間がかかります。

次に、次を実行します: selectid,username,gender,password from test_user where username='9000000'
所要時間: 0.043秒。

次にユーザー名とパスワードを使用してクエリに参加します

test_user から ID、ユーザー名、性別、パスワードを選択します。ユーザー名は '9000000'、パスワードは '*3A70E147E88D99888804E4D472410EFD9CD890AE' です。

ユーザー名列にインデックスを追加しましたが、パスワード列にはインデックスがありません。インデックスを使用してパスワードをフィルタリングすると、テーブル全体がスキャンされます。

クエリ速度がすぐに低下しました。

所要時間: 4.492秒。

SQL にフィルタリング条件の列が複数ある場合は、クエリの複数の列にインデックスを追加して集計インデックスを形成する必要があります。

クラスター化インデックスを追加します: ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
次に以下を実行します:

所要時間: 0.001秒。

冒頭で述べたように、ソフトウェアレベルの最適化とは、インデックスを適度に追加し、実行速度の遅い SQL を最適化することです。この 2 つは互いに補完し合い、必要不可欠です。インデックスを追加した後もクエリが遅い場合は、SQL の問題であるかどうかを検討し、SQL を最適化する必要があります。

ヒント:

1: インデックスを追加しても、次の状況では完全なテーブルスキャンが必要になる場合があります。

インデックス列は引用符のない文字列です。

インデックス列は where 条件の後には表示されません。

インデックス列が先頭に表示されません。

2: 関連付けられたクエリがインデックスを使用しない状況としては、次のようなものが考えられます。

関連付けられたテーブルの文字セットが異なります。

関連フィールドの文字セットが異なります。

ストレージ エンジンが異なります。

フィールドの長さが異なります。

これで、インデックスに基づいてMySQL数千万データのクエリ速度を最適化する方法についての記事は終わりです。MySQL数千万インデックス最適化クエリの関連コンテンツについては、123WORDPRESS.COMの以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。