MySQL Advanced 12 - インデックス

インデックス作成は高速検索の鍵です。 MySQL インデックスの確立は、MySQL を効率的に運用するために非常に重要です。ここでは、一般的な MySQL インデックス タイプをいくつか紹介します

データベース テーブルでは、フィールドにインデックスを付けるとクエリ速度が大幅に向上します。 mytable テーブルを作成するとします。

コードは次のとおりです。

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );

10,000 レコードをランダムに挿入します。そのうちの 1 つは、5555、管理者です。

username="admin" のレコード

SELECT * FROM mytable WHERE username='admin';

を検索するとき、ユーザー名にインデックスが確立されていれば、MySQL はスキャンせずに正確にレコードを見つけることができます。逆に、MySQL はすべてのレコードをスキャンします。つまり、10,000 レコードがクエリされます。

インデックスは、単一列インデックスと結合インデックスに分かれています。単一列インデックスは、インデックスに 1 つの列のみが含まれることを意味します。テーブルには複数の単一列インデックスを含めることができますが、これは結合されたインデックスではありません。結合インデックス。つまり、1 つのインデックスに複数の列が含まれます。

MySQL インデックス タイプには以下が含まれます:

1. 通常のインデックス

これは最も基本的なインデックスであり、制限はありません。以下の作成メソッドがあります:

1. インデックスを作成します

コードは次のとおりです:

CREATE INDEX indexName ON mytable(username(length));

CHAR、VARCHAR 型の場合、BLOB および TEXT の場合、長さはフィールドの実際の長さより小さくてもかまいません。タイプ、長さを指定する必要があります。以下も同様です。

2. テーブル構造を変更します

コードは次のとおりです:

ALTER mytable ADD INDEX [indexName] ON (username(length)) CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length)));

インデックスを削除します:

DROP INDEX [indexName] ON mytable;

2. ユニークなインデックス

違いは、インデックスの値です。列は一意である必要がありますが、値を空にすることもできます。複合インデックスの場合、列値の組み合わせは一意である必要があります。以下の作成方法があります:

コードは次のとおりです:

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;

テーブル構造を変更します

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

テーブル作成時に直接指定します

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));

3. 主キーインデックス

ではない特殊な一意のインデックスです。空の値でも構いません。通常、主キー インデックスはテーブルの作成時に同時に作成されます:

コードは次のとおりです:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));

もちろん、ALTER コマンドを使用することもできます。テーブルには主キーを 1 つだけ持つことができることに注意してください。

4. 複合インデックス

単一列インデックスと複合インデックスを明確に比較するには、テーブルに複数のフィールドを追加します:

コードは次のとおりです:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,   age INT NOT NULL);

MySQL の効率をさらに引き出すために、複合インデックスの構築を検討する必要がある。つまり、名前、都市、年齢をインデックスに構築します:

コードは次のとおりです:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

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

テーブルに 3 つの単一列インデックスがあるように、ユーザー名、都市、年齢にそれぞれ単一列インデックスを作成すると、クエリ効率は上記の結合インデックスとは大きく異なり、結合インデックスよりもはるかに低くなります。 。現時点では 3 つのインデックスがありますが、MySQL は最も効率的であると思われる単一列インデックスのみを使用できます。

このような結合インデックスを確立することは、実際には、次の 3 つの結合インデックス セットを確立することと同じです:

usernname、city、age usernname、city usernname なぜ city や age のような結合インデックスがないのですか?これは、MySQL 複合インデックスの「左端のプレフィックス」の結果です。簡単に理解すると、組み合わせは左端から開始するだけです。これら 3 つの列を含むクエリだけがこの結合インデックスを使用するだけでなく、次の SQL もこの結合インデックスを使用します:

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin";

以下のものは使用されません:

コードは次のとおりです:

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州";

5.インデックス

インデックスの作成方法を学習しました。では、どのような状況でインデックスを作成する必要があるのでしょうか?一般に、WHERE および JOIN に出現するカラムにはインデックスを付ける必要がありますが、MySQL では <、<=、=、>、>=、BETWEEN、IN のインデックスのみが使用され、場合によっては LIKE でインデックスが使用されるため、これが完全に当てはまるわけではありません。索引。例:

コードは次のとおりです:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

このとき、mytableテーブルのuserameもJOIN句に出現するため、インデックスする必要があります。

インデックスを作成する必要があるのは特定の LIKE のみであると先ほど述べました。 MySQL はワイルドカード文字 % と _ で始まるクエリを作成するときにインデックスを使用しないためです。たとえば、次の文はインデックスを使用します:

コードは次のとおりです:

SELECT * FROM mytable WHERE username like'admin%';

次の文はインデックスを使用しません:

コードは次のとおりです:

SELECT * FROM mytable WHEREt Name like'%admin';

因此，在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

七、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

代码如下:

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

代码如下:

select * from users where adddate<‘2007-01-01&#39;;

6.不使用NOT IN和<>操作

 以上就是MySQL高级十二——索引的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！