MySQL インデックスのエクスペリエンスの概要

mysql|インデックス

データベース テーブルでは、インデックスを使用するとクエリ速度が大幅に向上します。一 testIndex テーブルを作成した場合:
Create Table TestIndex (I_Testid int Not Null, VC_Name Varchar (16) Not null);

vc_Name="erquan" のレコードを検索する場合
SELECT * FROM testIndex WHERE vc_Name=' erquan';
、vc_Name にインデックスが確立されている場合、MySql はスキャンせずに正確にレコードを見つけることができます。逆に、MySql はすべてのレコードをスキャンします。つまり、1,000 回クエリが実行されます~~インデックスによりクエリ速度が 100 倍向上します。

1. インデックスは単一列インデックスと複合インデックスに分類されます
単一列インデックス: つまり、インデックスには 1 つの列のみが含まれ、テーブルには複数の単一列インデックスを含めることができますが、これは複合インデックスではありません。 。
組み合わせインデックス: つまり、1 つのインデックスに複数の列が含まれます。

2番目に、インデックスの種類を紹介します

1. 通常のインデックス。
これは最も基本的なインデックスであり、制限はありません。以下の作成メソッドがあります:
(1) インデックスの作成: CHAR、VARCHAR 型の場合、長さはフィールドの実際の長さより小さくても構いません。およびTEXT型の場合は長さを指定する必要があります。以下同様です。
(2) テーブル構造を変更する: ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))
(3) テーブル作成時に直接指定する: CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns) (長さ)) ;

2. 一意のインデックス。
インデックス列の値が一意である必要があることを除いて、前の「通常のインデックス」と似ていますが、結合されたインデックスの場合は null 値が許可されます。 、列の値の組み合わせは一意である必要があります:
(1) インデックスの作成: CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
(2) テーブル構造の変更: ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length) )
(3) テーブル作成時に直接指定: CREATE TABLE tableName ([...], UNIQUE [indexName] (tableColumns(length));

3. 主キーインデックス
特別な一意のインデックスです。NULL 値が許可されます。通常、テーブルの作成時に主キー インデックスが作成されます: CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(16) NOT NULL, PRIMARY KEY(i_testID) )); もちろん、ALTER コマンドも使用できます
覚えておいてください: テーブルには 1 つの主キーしか持てません

4. フルテキスト インデックス
MySQL はバージョン 3.23 からフルテキスト インデックスとフルテキスト検索をサポートします。 23. ここでは説明しません、はは~~

インデックスを削除する構文: DROP INDEX Index_name ON tableName

3. 単一列インデックスと結合インデックス

2 つを明確に比較するために、別のテーブルを作成します:
CREATE TABLE myIndex (i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );

これら 10,000 件のレコードの中には、 vc_Name="erquan" の 5 つのレコードが上に 7 つ、下に 8 つ分散されています。これらは都市、年齢、学校の組み合わせにすぎません。それぞれが異なります。
次の T-SQL を見てみましょう:
SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='Zhengzhou' AND i_Age=25;

まず、単一列インデックスの構築を検討してください:
vc_Name 列にインデックスを作成します。 。 T-SQL を実行するとき、MYSQL はすぐに vc_Name=erquan の 5 つのレコードをターゲットにし、それらを取り出して中間結果セットに入れます。この結果セットでは、まず vc_City が「鄭州」に等しくないレコードを除外し、次に i_Age が 25 に等しくないレコードを除外し、最後に条件を満たす唯一のレコードを除外します。

vc_Name にインデックスが確立されましたが、MYSQL はクエリ時にテーブル全体をスキャンする必要がなくなり、効率が向上しましたが、要件にはまだ一定の距離があります。同様に、vc_City と i_Age で確立された単一列インデックスの効率も同様です。

MySQL の効率をさらに引き出すためには、結合インデックスの構築を検討する必要があります。 vc_Name、vc_City、i_Age をインデックスに構築するだけです:
ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age); --テーブルの作成時の vc_Name の長さは 50 であることに注意してください。ここで 10 が使用されるのはなぜですか?通常、名前の長さは 10 を超えないため、これによりインデックス クエリが高速化され、インデックス ファイルのサイズが削減され、INSERT の更新速度が向上します。

T-SQL を実行するとき、MySQL は一意のレコードを見つけるためにレコードをスキャンする必要がありません。 ！

誰かが、vc_Name、vc_City、および i_Age にそれぞれ単一列インデックスを作成して、テーブルに 3 つの単一列インデックスがある場合、クエリは上記の結合インデックスと同じくらい効率的になるのではないかと尋ねたに違いありません。これはまったく異なり、結合インデックスよりもはるかに低いです~~ 現時点では 3 つのインデックスがありますが、MySQL は最も効率的であると思われる単一列インデックスのみを使用できます。このような組み合わせインデックスの確立は、実際には、
VC_Name、VC_CITY、I_AGE
VC_Name、VC_CITY
VC_Name
などの 3 つの組み合わせインデックスに相当します。 vc_City、i_Age などの複合インデックスがないのはなぜですか?これは、mysql の結合インデックスの「左端のプレフィックス」の結果です。簡単に理解すると、組み合わせは左端から開始するだけです。これら 3 つの列を含むクエリだけでなく、次の T-SQL でもこの結合インデックスが使用されます:
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="Zhengzhou"
SELECT * FROM myIndex WHREE vc_Name="erquan "
次のものは使用されません:
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_City="鄭州"

Four。インデックスを使用します
これで、次のことができるはずです。インデックスを作成して使用しますよね?しかし、どのような状況でインデックスを作成する必要があるのでしょうか?一般的に、WHERE および JOIN に出現するカラムにはインデックスを付ける必要がありますが、MySQL は 、>=、BETWEEN、IN、および場合によっては LIKE のみにインデックスを付けるため、これが完全に当てはまるわけではありません (後で説明します)。 ) インデックスを使用します。
SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='鄭州' の場合、myIndex テーブルの vc_City と i_Age にインデックスを付ける必要があります。 testIndex テーブルの vc_Name が JOIN 句に含まれるため、インデックスを作成する必要があります。

先ほど述べたように、特定の時間の「いいね」だけをインデックスに登録する必要があるのでしょうか?はい。ワイルドカード文字 % と _ で始まるクエリを作成する場合、MySQL はインデックスを使用せず、
SELECT * FROM myIndex WHERE vc_Name like'erquan%'
はインデックスを使用し、SELECT * FROM myIndex WHEREt vc_Name like'%erquan を使用するためです。 '
インデックスは使用されません。


5. インデックスの欠点

上で述べたインデックスには良い点がたくさんありますが、本当に伝説通りに優れているのでしょうか?もちろん欠点もあるでしょう。

1. インデックスによりクエリ速度は大幅に向上しますが、テーブルの INSERT、UPDATE、DELETE などのテーブル更新の速度も低下します。なぜなら、テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるからです

2. インデックスを作成すると、ディスク領域上のインデックス ファイルが占有されます。通常、この問題は深刻ではありませんが、大きなテーブルに複数の結合インデックスを作成すると、インデックス ファイルが急速に拡張します。


記事終わり:
ここまで言っておきますが、インデックスを使用してデータベースの実行効率を向上させたいだけです。ただし、インデックス作成は効率を向上させる 1 つの要素にすぎません。 MySQL に大きなデータ テーブルがある場合は、最適なインデックスを構築する方法やクエリ ステートメントを最適化する方法を研究するために時間を費やす必要があります。

上記に何か間違っていると思われる場合、異なる意見がある場合、または追加したいことがあれば、お気軽にコメントを残してください^_^。

参考: MYSQL 中国語マニュアル、MYSQL オンライン マニュアル、SQLSERVER オンライン シリーズ、PC163-IT ガイド

erquan
2003/2/25 PM Yu Zheng State