ホームページ > 記事 > データベース > MySQL のキーとインデックスの詳細な紹介

MySQL のキーとインデックスの詳細な紹介

PHP中文网オリジナル: 2017-06-20 14:10:3410638ブラウズ

1. 概要

1. 基本概念

(1) キーには、データの一意性と整合性を制限するために使用される 2 つの機能があります。インデックス作成機能は、インデックスを作成し、クエリ速度を最適化するために使用されます。インデックスと同じ機能があります。

(2) 通常のキー: 制約効果はありませんが、このキーにインデックスが確立されます。

(3) 主キー: 主キー; テーブルには、単一の主キー (1 つの列のみを含む) と複合主キー (結合主キーとも呼ばれ、以下を含むことができる) に分割される主キーを持つことができます。複数の列）; ストレージの主キーを指定して標準化することができ、同時にこのキーにインデックスが確立されます。主キーは必須ではありませんが、強くお勧めします [主キーを使用するためのいくつかの良い習慣: 変更なし、再利用なし]

(4) 固有キー: 同時にデータの一意性を標準化します。このキーにインデックスが確立されます。

(5) 外部キー: 外部キー。データの参照整合性を標準化すると同時に、このキーにインデックスが確立されます。

(6) インデックス: キーの役割の次元。キーワードのキーを置き換えることもあります。

2. 主キーと一意キー

(1) 類似点: 一意性制約

(2) 相違点

1) 異なる開始点/関数: 前者はデータ行の一意の識別子であり、後者は使用のみデータの重複を避けます。
2) 前者の 1 つ以上の列はすべて null でない必要があります。列の 1 つが null の場合、主キーを追加すると、その列の null 許容の性質が変わります。戻る。後者の列は null にすることもできます。
3) テーブルは主キーを 1 つだけ持つことができ、複数の一意のキーを持つことができます。 [テーブルに主キーがないことはありますか? ? ? 】
4) ユニークキーに対応する列には、null を複数回挿入できます (これも一種の繰り返しですが)。これはインデックスの原則、つまりインデックスの null の処理によって決まります。

2. 構文

1. 作成時に追加 - フィールドレベル

(1) 通常キー: create table t (id int not null key)

(2) 主キー: create table t (id int not null 主キー); この 2 つは同じ機能を持ちます。つまり、キーを指定すると主キーも指定され、テーブル内で 1 回だけ指定できます (結合主キーとして複数回指定することはできません)。 )

(3) ユニークキー: create table t (id int not null unique key);

(4) 外部キー: 機能しないはずです

(5) インデックス: すべてのキーをインデックスに転置することはできません

2. 作成時に追加 - テーブルレベル

(1) 通常キー: フィールドレベルの指定とは異なり、ここでの通常キーは主キーが指定されていない場合でも主キーと同じではなくなります。キーを主キーとして使用します。

create table t(id int, key (id)); id を使用する他のキー (外部キーなど) がある場合は、名前がない場合は他のキーを使用します。一度に複数の列をキーとして指定した場合、最初の列名がキー名として使用されます。

create table t(id int, key kismet(id)); キーの名前を指定します

constraint: 結局のところ、通常のキーには制約効果がありません

( 2) 主キーは実行できますが、名前は機能しません

create table t(id int,constraint kismet Primary key(id)) は実行できますが、名前は機能しません

(3) unique key

create table t(id int, unique key (id)); キー名として使用されるのは最初の列のみです
。

create table t(id int, unique key kismet(id)); キーの名前を指定します

create table t(id int,constraint kismet unique key(id));キーの名前

(4) 外部キー [私の意見では、いわゆる 2 つの A キーの作成は 2 つの論理レベル、つまりデータ整合性制約とインデックスの最適化です]

create table t(id int, 外部キー(dage_id)references dage(id)); 実行可能、実行結果自動命名外部キーと自動命名通常キーが作成されます。

create table t(id int, external key kismet(dage_id)references dage(id)); を実行すると、自動的に名前が付けられた外部キーと kismet という名前の通常キーが作成されます。

create table t(id int,constraint kismet external key(dage_id)references dage(id)); を実行でき、実行結果は kismet という名前の外部キーと kismet という名前の通常キーを作成します。

(5) インデックス: キー内のキーと一意のキー (テーブルレベル) をインデックスに置き換えることができ、効果は同じです。

3. 作成後

(1) キーを追加します。例: alter table t add Primary key(id);

(2) キーを削除、削除、主キーは alter table t 他のキーを使用します。名前を使用して削除するだけです。キーの削除と列の削除の違いに注意してください。

4. 情報の表示: show create table table_name; キー属性、ストレージエンジン、文字セット、パーティションステータスなど、テーブルのさまざまな属性を表示できます。

3. 外部キー

1. 2 つのテーブルを関連付け、データの一貫性を確保し、いくつかのカスケード操作を実装できます

2. 外部キーをサポートするストレージエンジン: InnoDB、メモリ検証がサポートされています。、その他は未確認です。

3. 完全な構文

(1) [CONSTRAINT シンボル] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | SET NULL | NO ACTION SET DEFAULT |

(3) ) CONSTRAINT シンボルはキーの名前を指定します。指定しない場合、キーは自動的に生成されます

(4) パラメータは設定できます:

(外部キーを制限します)。外観の変更、デフォルト)

CASCADE (外部キーの変更に従う)

SET NULL (NULL 値を設定)

SET DEFAULT (デフォルト値を設定)

NO ACTION (アクションなし)

4. 例
(1) テーブルを作成し、外部キーを設定し、データを挿入します

CREATE TABLE `dage` ( span>

 `id` int(11) NOT NULL auto_increment、
 `name` varchar(32) デフォルト ''、 span>
 主キー (`id`)
);

CREATE TABLE `xiaodi` ( span>CREATE TABLE `dage` (

 `id` int(11) NOT NULL auto_increment,

 `name` varchar(32) default '',

 PRIMARY KEY (`id`)

);

CREATE TABLE `xiaodi` (

 `id` int(11) NOT NULL auto_increment,

 `dage_id` int(11) default NULL,

 `name` varchar(32) default '',

 PRIMARY KEY (`id`),

 KEY `dage_id` (`dage_id`),
 `id` int(11) NOT NULL auto_increment、
 `dage_id` int(11) default NULL、 🎜
 `名前` varchar(32) デフォルト '', 🎜
 主キー (`id`)、 🎜🎜<code class=" language-sql"> KEY `dage_id` (`dage_id`),スパン>コード>🎜<div class="L1"> <code class=" language-sql"> 制約 `xiaodi_ibfk_1` 外部キー ( `dage_id`) 参考文献 `dage` (`id`) CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)

);

insert into dage(name) values('铜锣湾');

insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');

（2）如果在还有小弟的情况下删除大哥，结果如下

[SQL] delete from dage where id=1;

[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

（3）如果想在没有建立大哥的情况下，强行插入小弟，结果如下

[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

（4）修改事件触发设置

show create table xiaodi;#查看键名称
);

insert into dage(name) 値('铜锣湾');スパン>🎜
挿入into xiaodi(dage_id,名前)値(1,'铜锣湾_小弟A');🎜🎜🎜
（2）如果在还有小弟的情况下删除大哥、结果如下🎜🎜
[SQL] 削除 から dage where id=1; span>🎜🎜[エラー] 1451 - できません 削除 または 親行を更新: 外部キー制約が失敗します (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY ="pun">(`dage_id`) 参考文献 `dage` (`id`)) スパン> 🎜🎜🎜
（3）如果想在没建立大哥的情况下，强行插入小弟，結果如下🎜🎜

[SQL] 挿入を xiaodi( dage_id,名前) 値(2,'旺角_小弟A'); スパン>🎜🎜[エラー] 1452 - できません 子行を追加または 更新: 外部キー制約が失敗しました (`sample`.`xiaodi` , 制約 `xiaodi_ibfk_1` 外部キー (`dage_id `) 参照 `dage` (`dage` ="str">`id`)) スパン>🎜🎜🎜（4）修正イベント触発信設置🎜🎜

show create table xiaodi;#查看键名🎜
alter table xiaodi drop foreign key xiaodi_ibfk_1;

alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

（5）如果在还有小弟的情况下删除大哥：大哥和大哥对应的小弟一起被删除；如果想在没有建立大哥的情况下，强行插入小弟，结果并不变，即失败。

四、索引【参考：】

1、索引入门

（1）作用：索引对查询的速度有着至关重要的影响。如果没有索引，查询将对整个表进行扫描；如果有索引，查询只对索引进行。由于数据库的数据不在内存中，每次查询都需要将数据由硬盘调入内存，IO将浪费大量时间。考虑到索引比数据小的多，使用索引可以大幅提高查询速度；尤其是在数据量大时。

（2）索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。目前最常用的存储引擎是InnoDB。

2、选择索引的数据类型：MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则【（1）（2）条不适用于哈希索引】：

（1）越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

（2）简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。注意，对于索引，能用整型，就不要用字符串，尤其是在数据量大的时候；整型的一个弊端是，与客户端的配合可能需要一些额外的工作(尤其是大整型)，但是对效率几乎没有影响。

（3）尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

3、B-tree索引：结果为B-tree（平衡二叉树）

（1）概述：索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

（2）示例：其索引包含表中每一行的last_name、first_name和dob列。

CREATE TABLE People (

 last_name varchar(50) not null,

 first_name varchar(50) not null,

 dob date not null,

 gender enum('m', 'f') not null,

 key(last_name, first_name, dob) key(last_name, first_name, dob)

);

（3）匹配方式：既可以查找，也可以order by【结果是排序的，因此搜索很快】

1）匹配全值：对索引中的所有列都指定具体的值。

2）匹配最左前缀：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。

3）匹配列前缀：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。

4）匹配值的范围查询：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。

5）匹配部分精确而其它部分进行范围匹配：可以利用索引查找last name为Allen，而first name以字母K开始的人。

6）仅对索引进行查询：如果查询的列都位于索引中，则不需要读取元组的值。

7）如果索引字段为A+B，查询A+C时，会使用A索引吗->会，使用explain可以证实

（4）限制

1）查询必须从索引的最左边的列开始。

2）不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。

3）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

4、Hash索引

（1）概述

1）Hash索引通过哈希函数计算Hash值进行检索，可以查到要查数据的行指针，从而定位数据。

2）Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

3）Memory存储引擎支持非唯一hash索引，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。

（2）限制

1）由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。

2）不能使用hash索引排序。

3）Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

4）Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

（3）示例

CREATE TABLE testhash (

 fname VARCHAR(50) NOT NULL,

 lname VARCHAR(50) NOT NULL,

 KEY USING HASH(fname)

)ENGINE=MEMORY;
); span>

(3) マッチング方法: 検索または並べ替えが可能です [結果がソートされているため、検索が高速です]

1) 完全に一致value : インデックス内のすべての列に特定の値を指定します。

2) 一番左の接頭辞と一致します: インデックスを使用して、姓が Allen である人を見つけることができます。インデックスの最初の列を使用するだけです。

🎜🎜🎜🎜3) 列の接頭辞の一致: たとえば、インデックスを使用して、姓が J で始まる人を検索できます。これでは、インデックスの列 1 のみが使用されます。 🎜🎜🎜🎜🎜🎜4) 一致する値の範囲クエリ: インデックスを使用すると、インデックスの最初の列のみを使用して、姓が Allen と Barrymore の間にある人を検索できます。 🎜🎜🎜🎜🎜🎜5) 一致部分は正確で、他の部分は範囲一致です。インデックスを使用して、姓が Allen で、名前が文字 K で始まる人を検索できます。 🎜🎜🎜🎜🎜🎜6) インデックスに対してのみクエリを実行する: クエリされた列がすべてインデックス内にある場合、タプルの値を読み取る必要はありません。 🎜🎜🎜🎜7) インデックスフィールドが A+B の場合、A+C をクエリするときに A インデックスが使用されますか -> はい、explain🎜
(4) 制限

🎜🎜🎜1) クエリはインデックスの左端の列から開始する必要があります。 🎜🎜🎜🎜🎜🎜2) インデックス列をスキップすることはできません。たとえば、インデックスを使用して、姓がスミスで特定の日に生まれた人を検索することはできません。 🎜🎜🎜🎜🎜🎜3) ストレージエンジンは、インデックス内の範囲条件の右側の列を使用できません。たとえば、クエリステートメントが WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23' の場合、LIKE は範囲クエリであるため、クエリはインデックス内の最初の 2 列のみを使用します。。 🎜🎜🎜
🎜🎜 🎜🎜4. ハッシュインデックス🎜🎜🎜 (1) 概要 🎜🎜🎜
🎜🎜🎜1) ハッシュインデックスは、ハッシュ関数を通じてハッシュ値を計算し、次のように求めることができます。確認したい内容データを配置するためのデータの行ポインタ。 🎜🎜🎜🎜🎜🎜2) ハッシュ値は、列のデータ型に依存しません。TINYINT 列のインデックスは、長い文字列列のインデックスと同じくらい大きくなります。 🎜🎜🎜🎜🎜🎜3) メモリストレージエンジンは、一意でないハッシュインデックスをサポートします。複数の値が同じハッシュコードを持つ場合、インデックスは同じハッシュテーブルエントリへのリンクリスト内の行ポインターを保存します。 🎜🎜🎜

(2) 制限

🎜🎜🎜1) インデックスにはハッシュコードとレコードポインタのみが含まれるため、MySQL はインデックスを使用してレコードを読み取ることを避けることができません。ただし、メモリ内のレコードへのアクセスは非常に高速であり、パフォーマンスにはあまり影響しません。 🎜🎜🎜🎜🎜🎜2) ハッシュインデックスソートは使用できません。 🎜🎜🎜🎜🎜🎜3) ハッシュ値はインデックス値全体を通じて計算されるため、ハッシュインデックスはキーの部分一致をサポートしません。 🎜🎜🎜🎜🎜🎜4) ハッシュインデックスは、=、IN()、<=> の使用などの等価比較のみをサポートします。 WHERE 価格>100 の場合、クエリは高速化されません。 🎜🎜🎜
🎜🎜(3) 例🎜🎜🎜
🎜CREATE TABLE testhash (🎜
 fname VARCHAR(50) NOT NULL、 🎜
 lname VARCHAR(50) NOT NULL,🎜
 ハッシュを使用するキー(fname)🎜
)エンジン=メモリ;🎜🎜🎜🎜🎜 🎜🎜5. その他のインデックス🎜🎜🎜 (1) スペース(R ツリー) インデックス: MyISAM は、主に GEOMETRY などの地理空間データ型に使用される空間インデックスをサポートします。 🎜🎜 (2) 全文インデックス: 全文インデックスは MyISAM の特殊なインデックスタイプで、主に全文検索に使用されます。 🎜🎜🎜🎜🎜🎜🎜

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

声明：

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

前の記事：CSVインポートデータをmysqlインスタンスに共有する次の記事：CSVインポートデータをmysqlインスタンスに共有する

続きを見る