mysqlインデックスとは何ですか? mysql インデックスの関連知識の紹介

この記事では、mysql インデックスとは何ですか? mysql インデックスに関する関連知識の紹介は、必要な友人が参考になれば幸いです。

インデックスとは

インデックスは本の目次のようなものです

インデックスクイック検索に使用されます。インデックスを使用せずに、カラム内の特定の値を持つ行を見つけるには、MySQL は最初のレコードから始めて、関連する行が見つかるまでテーブル全体を読み取る必要があります。テーブルが大きくなるほど、時間がかかります。テーブル内のクエリ対象の列にインデックスがある場合、MySQL はすべてのデータを確認することなく、データ ファイルを検索する場所にすぐにアクセスできるため、時間を大幅に節約できます。

利点と欠点

利点

1. クエリを大幅に高速化します

欠点

1. データが増えると、インデックスの作成と維持に時間がかかります。

#2. インデックスは、データ内のデータを占有します。テーブルには最大オンライン設定もあります。多数のインデックスがある場合、テーブル内のデータを追加、削除、または変更すると、インデックス ファイルがデータ ファイルよりも早くオンライン値に到達する可能性があります。テーブルの場合、インデックスも必要です。 動的メンテナンスによりデータ メンテナンスの速度が低下します。

使用原則とシナリオ

1. インデックスの数は多いほど良いですが、状況によって異なります

2. 頻繁に更新されるテーブルには、インデックスをできるだけ少なくする必要があります。

3. クエリに頻繁に使用されるフィールドのインデックスを作成します。

4. フィールドにはインデックスを使用しないようにします。少量のデータの場合はすべてクエリ データに費やす時間はインデックス データを走査する時間よりも短く、インデックスには最適化効果がありません。

5 異なる値がほとんどないフィールドにはインデックスを使用しないでください。 、男性と女性の 2 つの異なる値のみを持つ性別フィールドなど。

インデックス分類

注: インデックスはストレージ エンジンに実装されています。つまり、異なるストレージ エンジンは異なるインデックスを使用します

MyISAM と InnoDBストレージ エンジン: BTREE インデックスのみをサポートします。つまり、BTREE がデフォルトで使用され、置き換えることはできません。

MEMORY/HEAP ストレージ エンジン: HASH インデックスと BTREE インデックスをサポート

1。インデックス

インデックスには 1 つの列のみが含まれますが、テーブルには複数の単一列インデックスが存在する場合があります。

1.1. 基本的なインデックス タイプ。 MySQL では、純粋にデータをより高速にクエリするためだけに、インデックスが定義されているカラムに重複値や null 値を挿入することを許可する制限はありません。

1.2. 一意のインデックス

インデックス列の値は一意である必要がありますが、NULL 値も許可されます

1.3.特別な一意のインデックスであり、null 値は許可されません。

2. 結合インデックス

テーブル内の複数のフィールドの組み合わせに対してのみ作成されるインデックスです。クエリ条件 インデックスは、これらのフィールドの左側のフィールドが使用される場合にのみ使用されます。結合インデックスを使用する場合は、最適な左プレフィックス ルール

3 に従います。

#フルテキスト インデックス。MyISAM エンジンでのみ使用でき、フルテキスト インデックスは CHAR、VARCHAR、および TEXT タイプのフィールドでのみ使用できます。全文インデックスとは、テキストの山の中で、特定のキーワード (たとえば、「あなたはペンがとても苦手で、二流の人です...) を通じて、そのフィールドが属するレコード行を見つけることができることを意味します。」大きな悪いペン、それは可能かもしれません レコードを見つけます

4. 空間インデックス

空間インデックスは、空間データ型のフィールドに対して確立されるインデックスです。 MySQL の空間データ型、 GEOMETRY 、 POINT、 LINESTRING、 POLYGON。空間インデックスを作成する場合は、SPATIAL キーワードを使用します。要件: エンジンは MyISAM であり、空間インデックスの作成に使用される列は NOT NULL として宣言される必要があります

インデックス メソッド

使用原則: 値が異なる場合が大きく、主に等しい値の検索 (=、<=>、in) に基づいており、値の差が比較的少ない場合は、ハッシュ インデックスの方が効率的な選択肢です。検索の複雑さは O(1) です。 、範囲検索は主に、範囲検索をサポートする B ツリーの方が良い選択です。

B ツリー インデックスB ツリー インデックスには範囲検索と接頭辞検索の機能があり、N 個のノードを持つ B ツリーの場合、レコードを取得する複雑さは O(LogN) です。 。二分探索と同等。

ハッシュ インデックス

ハッシュ インデックスは等しい検索のみを実行できますが、ハッシュ テーブルがどれほど大きくても、検索の複雑さは O(1) です。

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

作成

テーブル作成時に作成されます

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

例:

CREATE TABLE `NewTable` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (255) NOT NULL,
    `name` VARCHAR (255) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT 0,
    `address` VARCHAR (255) NULL,
    PRIMARY KEY (`id`), # 主键索引
    INDEX `name` (`name`) USING BTREE, # 普通索引
    UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引
    INDEX `u_n_a` (`username`,    `name`,`address`) USING BTREE # 组合索引
);

既存のテーブルの作成

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

例:

ALTER TABLE `test`
ADD PRIMARY KEY (`id`),  # 主键索引
ADD INDEX `name` (`name`) USING BTREE , # 普通索引
ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引
ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引

インデックスの削除

ALTER TABLE 表名 DROP INDEX 索引名。

例:

ALTER TABLE `test`
DROP PRIMARY KEY,
DROP INDEX `username`,
DROP INDEX `name`,
DROP INDEX `u_n_a`;

インデックスの更新

最初に削除してからビルド

ALTER TABLE `test`
DROP INDEX `username` ,
ADD UNIQUE INDEX `username1` (`username`) USING BTREE ,
DROP INDEX `name` ,
ADD INDEX `name2` (`name`) USING BTREE ,
DROP INDEX `u_n_a` ,
ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

インデックスの失敗状況

1 結合されたフィールドが最適に従っていません。左プレフィックス ルール

2。「%test」などのファジー クエリ。

# 索引生效
select * from `test` where `name` like "123";
# 索引生效
select * from `test` where `name` like "123%";
# 索引失效
select * from `test` where `name` like "%123";
# 索引失效
select * from `test` where `name` like "%123%";

3. 在索引列上做如下任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范围索引(>,<,between and)后，无法命中组合索引右边的列

构建索引

ALTER TABLE `test`
ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;

示例：

# 命中全部
select * from `test` where `sex` = 1 and `name` = 'a';
# 命中部分，sex命中，name失效
select * from `test` where `sex` > 1 and `name` = 'a';</p>
<p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p>
<p style="white-space: normal;">6. 字符串字段的值不加单引号（数字不报错，英文报错）索引失效</p>
<p>构建索引</p>
<pre class="brush:php;toolbar:false">ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 条件导致索引失效

构建索引

ALTER TABLE `test`
ADD INDEX `sex` (`sex`) USING BTREE ;
ADD INDEX `n_u` (`name`, `username`) USING BTREE ;

示例：

# 索引不生效
select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 
# 索引sex生效
select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )

附录

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

如下构建索引

ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

如下查询情况

# 命中部分
select * from `test` where `username` = 'aaa';
# 命中部分
select * from `test` where `username` = 'aa' and `address` = 'aaa';
# 全命中
select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a';
# 不命中，第一条件字段不是username
select * from `test` where  `address` = 'aaa';

以上がmysqlインデックスとは何ですか? mysql インデックスの関連知識の紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。