MySQL でのインデックスの追加と削除に関連する操作

MySQL でのインデックスの追加と削除に関連する操作

1. インデックスの役割

一般的なアプリケーション システムでは、読み取りと書き込みの比率は約 10:1 であり、挿入操作と一般的な更新操作でパフォーマンス上の問題が発生することはほとんどありません。最も一般的で最も問題となる操作は、一部の複雑なクエリ操作であるため、クエリ ステートメントの最適化が最優先事項であることは明らかです。

データ量やアクセス数が多くない場合、MySQL のアクセスは非常に高速であり、インデックスを追加するかどうかはアクセスにほとんど影響しません。ただし、データ量やアクセス数が急増すると、MySQL の速度が低下したり、クラッシュしたりすることがあります。この場合、SQL の最適化を検討する必要があります。データベースに適切かつ適切なインデックスを確立することは、MySQL を最適化するための重要な手段です。

インデックスの目的は、クエリの効率を向上させることです。これは辞書に例えることができます。「mysql」という単語を検索する場合は、文字 m を見つけ、次に文字 y を下から下まで見つけ、次に sql の残りの部分を見つける必要があります。索引がない場合、必要な単語を見つけるためにすべての単語を調べる必要がある場合があります。辞書以外にも、駅の電車の時刻表や本のカタログなど、生活のあらゆるところに索引の例が見られます。それらの原理は同じで、取得したいデータの範囲を継続的に絞り込むことで最終的な目的の結果をフィルタリングし、同時にランダムなイベントを連続したイベントに変換することです。つまり、常に同じ検索方法を通じてデータをロックします。

インデックスを作成するときは、SQL クエリで使用される列を考慮し、それらの列に対して 1 つ以上のインデックスを作成する必要があります。実際、インデックスは、主キーまたはインデックス フィールドと、各レコードを実際のテーブルにポイントするポインターを格納するテーブルでもあります。インデックスはデータベース ユーザーには見えず、クエリを高速化するためにのみ使用されます。データベース検索エンジンはインデックスを使用してレコードをすばやく見つけます。

インデックス付きのテーブルでは、INSERT ステートメントと UPDATE ステートメントの実行に時間がかかりますが、SELECT ステートメントはより高速に実行されます。これは、挿入または更新を実行するときに、データベースでもインデックス値を挿入または更新する必要があるためです。

2. インデックスの作成と削除

インデックスの種類:

  • UNIQUE(ユニークインデックス):同じ値は出現できず、NULL値は許可されます
  • INDEX(通常のインデックス):同じインデックスコンテンツを表示できます
  • PROMARY KEY(主キーインデックス):重複した値は許可されません
  • 全文インデックス: 値内の単語をターゲットにするために使用できますが、効率は十分ではありません。
  • 複合インデックス:本質的には、複数のフィールドが1つのインデックスに組み込まれ、列の値の組み合わせは一意である必要があります。

(1)ALTER TABLE文を使用して、

テーブルの作成後に追加する場合に使用します。

ALTER TABLE テーブル名 ADD インデックスタイプ (ユニーク、主キー、フルテキスト、インデックス) [インデックス名] (フィールド名)
//通常のインデックス alter table table_name add index index_name (column_list);
// ユニークインデックス alter table table_name add unique (column_list);
//主キー インデックス alter table table_name add primary key (column_list);

ALTER TABLE を使用すると、通常のインデックス、UNIQUE インデックス、PRIMARY KEY インデックスの 3 つのインデックス形式を作成できます。table_name はインデックスを追加するテーブルの名前、column_list はインデックスを作成する列を示します。複数の列がある場合は、列はカンマで区切られます。インデックス名 index_name はオプションです。デフォルトでは、MySQL は最初のインデックス列に基づいて名前を割り当てます。さらに、ALTER TABLE を使用すると、1 つのステートメントで複数のテーブルを変更できるため、複数のインデックスを同時に作成できます。

(2)CREATE INDEX文を使用してテーブルにインデックスを追加する

CREATE INDEX は、テーブルに共通インデックスまたは UNIQUE インデックスを追加するために使用でき、テーブルの構築時にインデックスを作成するために使用できます。

CREATE INDEX index_name ON table_name(username(length));

CHAR または VARCHAR 型の場合、長さはフィールドの実際の長さより短くてもかまいません。BLOB または TEXT 型の場合は、長さを指定する必要があります。

//create ではこれら 2 つのインデックスのみを追加できます。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name、column_list は、ALTER TABLE ステートメントの場合と同じ意味を持ちます。インデックス名はオプションではありません。また、CREATE INDEX ステートメントを使用して PRIMARY KEY インデックスを作成することはできません。

(3)インデックスを削除する

インデックスの削除は、ALTER TABLE または DROP INDEX ステートメントを使用して実行できます。 DROP INDEX は、次の形式で ALTER TABLE 内の単一のステートメントとして処理できます。

table_name のインデックス index_name を削除します。
テーブル table_name を変更し、インデックス index_name を削除します。
テーブル table_name を変更し、主キーを削除します。

前の 2 つのステートメントでは、table_name 内のインデックス index_name が削除されます。最後のステートメントでは、PRIMARY KEY インデックスを削除するためにのみ使用されます。テーブルには 1 つの PRIMARY KEY インデックスしか設定できないため、インデックス名を指定する必要はありません。 PRIMARY KEY インデックスが作成されていないが、テーブルに 1 つ以上の UNIQUE インデックスがある場合、MySQL は最初の UNIQUE インデックスを削除します。

テーブルから列を削除すると、インデックスに影響します。複数の列を持つインデックスの場合、列の 1 つを削除すると、その列もインデックスから削除されます。インデックスを構成するすべての列を削除すると、インデックス全体が削除されます。

(4)複合索引とプレフィックス索引

ここで指摘しておくべきことは、複合インデックスとプレフィックス インデックスはインデックスの種類ではなく、インデックス作成手法の名前であるということです。わかりやすく説明するために、次のようなデモ テーブルを作成します。

テーブル USER_DEMO を作成する
(
 ID int not null auto_increment コメント '主キー',
 LOGIN_NAME varchar(100) NULLでないコメント 'ログイン名',
 PASSWORD varchar(100) NULLでないコメント 'パスワード',
 CITY varchar(30) NULLでないコメント 'City',
 AGE int NULLでないコメント '年齢',
 SEX int not null コメント '性別 (0: 女性 1: 男性)',
 主キー (ID)
);

MySQL の効率をさらに高めるには、複合インデックスの作成、つまり LOGIN_NAME、CITY、AGE を 1 つのインデックスに組み込むことを検討してください。

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

テーブルを作成するとき、LOGIN_NAME の長さは 100 です。名前の長さは通常 16 を超えないため、ここでは 16 が使用されます。これにより、インデックス クエリが高速化され、インデックス ファイルのサイズが削減され、INSERT および UPDATE の更新速度が向上します。

LOGIN_NAME、CITY、AGE のそれぞれに単一列インデックスを作成して、テーブルに 3 つの単一列インデックスがある場合、クエリの効率は結合インデックスの場合とは大きく異なり、結合インデックスよりもはるかに低くなる可能性があります。現時点では 3 つのインデックスがありますが、MySQL は最も効率的と思われる単一列のインデックスのみを使用できます。他の 2 つは使用されない、つまり、依然として完全なテーブル スキャン プロセスであることを意味します。

このような複合インデックスを確立することは、それぞれ次の 3 つの複合インデックスを確立することと同等です。

ログイン名、都市、年齢
ログイン名、都市
ログイン名

CITY、AGE などの結合インデックスがないのはなぜですか?これは、MySQL 複合インデックスの「左端のプレフィックス」の結果によるものです。簡単に理解すると、組み合わせは左端の列から始まり、これら 3 つの列を含むすべてのクエリがこの結合インデックスを使用するわけではないということです。つまり、name_city_age(LOGIN_NAME(16),CITY,AGE) は左から右にインデックス付けされます。左端のインデックスがない場合、MySQL はインデックス クエリを実行しません。

インデックス列が長すぎると、この列にインデックスを作成するときに大きなインデックス ファイルが生成され、操作が不便になります。プレフィックス インデックスを使用してインデックスを作成できます。プレフィックス インデックスは適切なポイントで制御する必要があり、黄金値 0.31 内で制御できます (この値より大きい場合は作成できます)。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- この値が 0.31 より大きい場合は、プレフィックス インデックスを作成し、Distinct を使用して重複を削除できます。 ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- プレフィックス インデックス SQL を追加し、10 で name のインデックスを作成します。これにより、インデックス ファイルのサイズが縮小され、インデックス クエリが高速化されます。

3. インデックスの使用と注意事項

EXPLAIN は、開発者が SQL の問題を分析するのに役立ちます。EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。これにより、より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述できるようになります。

これを使用するには、select ステートメントの前に Explain を追加するだけです。

select * from user where id=1; について説明します。

インデックスを使用しない次の SQL ステートメントは避けるようにしてください。

SELECT `sname` FROM `stu` W​​HERE `age`+10=30;-- インデックス列はすべて計算に関係するため、インデックスは使用されません。SELECT `sname` FROM `stu` W​​HERE LEFT(`date`,4) <1990; -- 関数演算が使用されるため、インデックスは使用されません。原理は上記と同じです。SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- インデックスを使用します。SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- インデックスを使用しないでください。-- 正規表現ではインデックスを使用しないので、簡単に理解できるはずです。では、SQL で regexp キーワードがわかりにくいのはなぜでしょうか。-- 数字との文字列比較ではインデックスを使用しません。
テーブル `a` を作成します (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- インデックスを使用します。 EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- インデックスを使用しません。 select * from dept where dname='xxx' or loc='xx' or deptno=45 -- 条件に or がある場合、条件にインデックスがあっても使用されません。つまり、使用されるすべてのフィールドにインデックスを付ける必要があります。可能であれば、またはキーワードの使用を避けることをお勧めします。MySQL がテーブル全体のスキャンの方がインデックスよりも高速であると判断すると、インデックスは使用されません。

インデックスには多くの利点がありますが、インデックスを過度に使用すると逆の問題が発生する可能性があります。インデックスには欠点もあります。

  • インデックスによりクエリ速度は大幅に向上しますが、INSERT、UPDATE、DELETE などのテーブル更新の速度も低下します。テーブルを更新すると、MySQL はデータを保存するだけでなく、インデックス ファイルも保存します。
  • インデックスを構築すると、インデックス ファイル用のディスク領域が消費されます。通常、この問題は深刻ではありませんが、大きなテーブルに複数の結合インデックスを作成すると、インデックス ファイルが非常に広くなります。

インデックス作成は、効率を向上させる方法の 1 つにすぎません。MySQL に大量のデータが含まれるテーブルがある場合は、最適なインデックスを作成する方法やクエリ ステートメントを最適化する方法を時間をかけて調査する必要があります。

インデックスを使用する際のヒントがいくつかあります。

1. インデックスにはNULLの列は含まれません

列に NULL 値が含まれている限り、その列はインデックスに含まれません。複合インデックス内の 1 つの列に NULL 値が含まれている限り、その列はこの複合インデックスに対して無効になります。

2. 短いインデックスを使用する

リスト列にインデックスを付ける場合、可能であればプレフィックスの長さを指定する必要があります。たとえば、char(255) 列があり、最初の 10 文字または 20 文字の範囲内でほとんどの値が一意である場合は、列全体にインデックスを付けないでください。短いインデックスを使用すると、クエリ速度が向上するだけでなく、ディスク領域と I/O 操作も節約できます。

3. インデックス列の並べ替え

MySQL クエリは 1 つのインデックスのみを使用するため、where 句でインデックスがすでに使用されている場合、order by 句の列ではインデックスは使用されません。したがって、データベースのデフォルトのソートで要件を満たすことができる場合は、ソート操作を使用しないでください。複数の列のソートを含めないようにしてください。必要な場合は、これらの列の複合インデックスを作成するのが最適です。

4.Like文の操作

一般的に言えば、like 操作の使用は推奨されません。使用する必要がある場合は、正しい使用方法に注意してください。 '%aaa%' のようにインデックスは使用されませんが、 'aaa%' のようにインデックスを使用できます。

5. 列に対して操作を行わない

6. NOT IN、<>、! は使用しないでください。 = 演算ですが、<、<=、=、>、>=、BETWEEN、IN ではインデックスを使用できます。

7. 頻繁に選択されるフィールドにインデックスを作成する必要があります。

これらの列がほとんど使用されない場合は、インデックスの有無によってクエリ速度に大きな変化が生じないためです。逆に、インデックスを追加すると、システムのメンテナンス速度が低下し、必要なスペースが増加します。

8. インデックスは、比較的一意の値を持つフィールドに作成する必要があります。

9. テキスト、イメージ、ビット データ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に多いか、値が非常に少ないためです。

10. where および join に表示される列にはインデックスを付ける必要があります。

11. where クエリ条件に不等号がある場合 (where column != ...)、MySQL はインデックスを使用できません。

12. where 句のクエリ条件で関数が使用されている場合 (where DAY(column)=… など)、MySQL はインデックスを使用できません。

13. 結合操作(複数のテーブルからデータを抽出する必要がある場合)では、MySQL は主キーと外部キーのデータ型が同じ場合にのみインデックスを使用できます。そうでない場合は、インデックスが確立されていても使用されません。

以下は他のネットユーザーからのコメントです

以下のように表示されます。

ALTER TABLE xxxxx ADD INDEX `tid` (`tid`) USING BTREE;
xxxx の uid インデックスを削除します。
xxxx からのインデックスを表示

MySQL でインデックスを追加および削除する上記の操作は、すべて私が皆さんと共有したいものです。これが皆さんの参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQLにインデックスを追加しても効果がないいくつかの状況について簡単に説明します。

<<:  Element-ui の組み込み 2 つのリモート検索 (ファジークエリ) の使用方法の説明

>>:  LinuxサーバーにGRUBをインストールする手順

推薦する

MySQL の自動増分 ID (主キー) が不足した場合の解決策

MySQL で使用される自動インクリメント ID には多くの種類があり、各自動インクリメント ID ...

TomcatはXMLを解析し、リフレクションを通じてオブジェクトを作成します。

次のサンプル コードでは、Tomcat が XML を解析し、リフレクションを通じてオブジェクトを作...

階段効果を実現するためのWeChatアプレットカスタムメニューナビゲーション

設計意図ページを開発する際には、ページ上のナビゲーション メニューをクリックしたときにページを対応す...

MySQL でストアド プロシージャを作成し、ループでレコードを追加する方法

この記事では、例を使用して、MySQL でストアド プロシージャを作成し、ループでレコードを追加する...

nginx+lua を使用した単一マシンでの何万もの同時接続の実装

nginx は弊社で最もよく使用されるサーバーで、コンテンツ配信やリバース プロキシによく使用されま...

DBeaver を MySQL バージョン 8 以降に接続し、起こりうる問題を解決する方法の詳細な説明

データベース MySQL バージョン 8.0.18 DBeaver.exeをダウンロードするダウンロ...

MySQL でデータを削除してもテーブル ファイルのサイズが変更されないのはなぜですか?

長期間稼働しているデータベースの場合、テーブルがストレージ領域を占有しすぎるという問題がよく発生しま...

Dockerを使用してLaravelおよびVueプロジェクトの開発環境を構築する詳細な説明

この記事では、Docker で構築された Laravel および Vue プロジェクトの開発環境を紹...

HTMLデータ送信投稿_PowerNode Java Academy

HTTP/1.1 プロトコルで指定されている HTTP リクエスト メソッドには、OPTIONS、...

Ubuntu 18.04 (物理マシン) で OpenWRT 開発環境を構成する方法

1. 仮想マシン(物理マシン)をインストールする仮想マシンまたは物理マシンにインストールできます。 ...

Web フォームの入力要素の高度な使用例 11 選

1. ボタンが押されたときに点線のボックスをキャンセルする<br />入力に属性値hid...

HTMLハイパーリンクタグAのTARGET属性の詳細な説明

ハイパーリンク <a> タグはリンク ポイントを表します。これは英語の単語「anchor...

CSS で「プラス記号」効果を実装するためのサンプルコード

以下に示すプラス記号の効果を実現するには: この効果を実現するには、div 要素だけが必要です。 b...

jsとcssのブロッキング問題の詳細な分析

目次DOMContentLoadedとロードjs ブロッキングとは何ですか? CSS ブロッキングと...

Windows 10 で MySQL 8.0.12 の解凍バージョンをインストールして構成する方法 (グラフィック チュートリアル付き)

この記事では、MySQL 8.0.12 の解凍版のインストールと設定方法を記録し、皆様と共有します。...