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

インデックスを使用して数千万のデータを持つ 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をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • この記事では、インデックスなしでMySQL結合を最適化する方法を説明します。
  • MySQL のインデックスの原理とクエリの最適化の詳細な説明
  • MySQL データの最適化 - 多層インデックス
  • MySQL インデックスの最適化: ページング探索の詳細な紹介
  • MySQLのインデックス選択と最適化の詳細な説明
  • MySQL パフォーマンス最適化インデックス プッシュダウン
  • MySQLの最適化とインデックス分析

<<:  検証コードケースのjs実装

>>:  CSS で左上の三角形を作成するいくつかの方法の詳細な説明

推薦する

Linuxのlocateコマンドの使い方

01. コマンドの概要実際には、locate コマンドは find -name の別の書き方ですが、...

Douyin ロゴを作成する手順の CSS3 分析

「Tik Tok」も大人気で、ユーザー数は7億人と言われています。今日は、注目を集めるためにTikT...

Vueテクノロジーに基づく再帰コンポーネントの実装方法

説明するこの記事では、Vue テクノロジーに基づいて再帰コンポーネントを実装する方法を紹介します。 ...

JSX を使用してコンポーネント パーサー開発を構築する例

目次JSX環境の構築プロジェクトの設定NPMを初期化するwebpackをインストールするBabelを...

要素の円弧モーションを実現する CSS3 サンプルコード

CSS を使用して要素の円弧の動きを制御する方法CSS3 の新しい属性 transfrom トランジ...

ローカルで起動したときに Vue プロジェクトがクッキーを保持できない問題を解決する

vueプロジェクトをパッケージ化してサーバーにデプロイし、正常にログインできるが、ローカルで起動する...

Dockerコンテナの紹介

Dockerの概要Docker はオープンソースのソフトウェア展開ソリューションです。 Docker...

WeChatミニプログラムでのマップの正しい使用例

目次序文1. 準備2. 実際の戦闘2.1 ミニプログラムの権限を設定する2.2 カプセル化ツールの機...

Linux カーネルの探究: Kconfig の秘密

Linux 構成/ビルド システムがどのように機能するかを深く理解します。 Linux カーネル構成...

写真とテキストによる MySQL 8.0.11 インストール チュートリアル

インターネット上には多くのチュートリアルがありますが、基本的には同じです。ただし、細かい原因でソフト...

MySQLで関連テーブルを削除する実用的な方法

MySQL データベースでは、テーブルが互いに関連付けられた後は、それらを任意に削除することはできま...

HTML の ReadOnly と Enabled の違い

ReadOnly 属性を持つ TextBox は、クライアント上で次のマークアップとして表示されます...

Javascriptのtry catchの2つの機能についてお話しましょう

プログラムは上から下へ順番に実行され、いくつかの制御文によって実行経路を変更することができます。制御...