MySQL インデックスの種類と利点と欠点

1インデックスのデータ型を選択します

MySQL は多くのデータ型をサポートしており、データを保存するために適切なデータ型を選択することは、パフォーマンスに大きな影響を与えます。一般的に、いくつかのガイドラインに従うことができます:

(1) 通常、データ型が小さいほど優れています: 通常、データ型が小さいほど、必要なディスク、メモリ、CPU キャッシュのスペースが少なくなり、処理が速くなります。

(2) 単純なデータ型の方が優れています: 文字列の比較がより複雑であるため、整数データは文字よりも処理オーバーヘッドが少なくなります。 MySQL では、時刻を格納するには文字列の代わりに組み込みの日付と時刻のデータ型を使用し、IP アドレスを格納するには整数データ型を使用する必要があります。

(3) NULL を避けるようにしてください。NULL を格納する場合を除き、列は NOT NULL として指定する必要があります。 MySQL では、null 値を含むカラムはインデックス、インデックス統計、比較操作を複雑にするため、クエリを最適化することが困難です。 null 値は 0、特別な値、または空の文字列に置き換える必要があります。

2
インデックスの種類

インデックスは、サーバー層ではなく、ストレージ エンジンに実装されます。したがって、各ストレージ エンジンのインデックスは必ずしも完全に同じであるとは限りません。また、すべてのストレージ エンジンがすべてのインデックス タイプをサポートしているわけではありません

(1) 通常のインデックス

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

インデックスの作成

CREATE INDEX IndexName ON mytable(username(length)); CHAR、VARCHAR 型の場合、長さはフィールドの実際の長さより小さくてもかまいません。 TEXT型、長さを指定する必要があります。以下同様です。

テーブル構造を変更する

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) 一意のインデックス

インデックス列の値は一意である必要があるが、NULL 値が許可される点を除いて、前の通常のインデックスと似ています。複合インデックスの場合、列値の組み合わせは一意である必要があります。以下の作成方法があります:

インデックスの作成

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) 主キーインデックス

null 値を許可しない特殊な一意のインデックスです。通常、主キーインデックスはテーブル作成時に同時に作成されます:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

(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 複合インデックスの「左端のプレフィックス」の結果です。簡単に理解すると、組み合わせは左端から開始するだけです。 (これは面接の質問の 1 つであり、当時は正しく答えるべきでした) これら 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="郑州"

3

インデックスを作成するタイミング

インデックスの作成方法を学習しましたが、どのような状況でインデックスを作成する必要があるでしょうか?一般的に、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='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

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

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

4

インデックスの欠点

上記ではインデックスを使用する利点について説明しましたが、インデックスを過度に使用すると乱用が発生します。したがって、インデックスには欠点もあります:

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

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

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

5

インデックス使用時の注意点

インデックスを使用する場合、次のようなヒントと注意事項があります:

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

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

使用短索引

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

索引列排序

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

like语句操作

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

不要在列上进行运算

select * from users where YEAR(adddate)<2015; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2015-01-01’;

不使用NOT IN和<>操作

以上就是MySQL索引类型与优缺点的内容，更多相关内容请关注PHP中文网（www.php.cn）！