mysqlプライマリ・キー・インデックスと非プライマリ・キー・インデックスの違い
2986 ワード
一、前言
プライマリ・キーは論理キー、インデックスは物理キーです.プライマリ・キーは実際には存在せず、インデックスは実際にデータベースに存在します.インデックスは本当にファイルを生成します.データは本当にファイルを生成します.redo logレコードの内容:物理ログ、「あるデータページで何を変更したか」、リサイクル.bin logレコードの内容:論理ログ、文の元の論理「ID=1,2」、追加使用.プライマリ・キーはファイルを生成しません.プライマリ・キーの検索はIDインデックス・ツリーに依存します.
二、データの概念
データにはデータページの概念があり、mysqlにもメモリの概念があります.mysqlクエリーの場合、まず所在するデータページがメモリにあるかどうかを検出し、存在する場合、クエリーは直接戻ります.存在しない場合は、ディスクからメモリにロードし、結果を返します.
更新操作では、データがメモリに存在しない場合は、ディスクからメモリにロードし、cpuを変更してからメモリに値を入れ、redo logログテーブルに更新します.
三、索引の原理
1.B+ツリーインデックス
B+ツリーのすべてのキーワードは、リーフノードのチェーンテーブル(稠密インデックス)に表示され、チェーンテーブルのキーワードは秩序化されています.非リーフノードはインデックスとしてのみ機能します(疎インデックス).文がselect*from T where ID=500、すなわちプライマリ・キー・クエリ方式の場合、IDというB+ツリーを検索するだけです.文がselect*from T where k=5、すなわち通常のインデックスクエリ方式である場合、まずkインデックスツリーを検索し、IDの値が500で、IDインデックスツリーに1回検索する必要があります.このプロセスはテーブルと呼ばれます.つまり、プライマリ・キー以外のインデックスに基づいたクエリは、インデックス・ツリーを複数スキャンする必要があります.したがって、アプリケーションではできるだけプライマリ・キー・クエリーを使用する必要があります.
2.1つのデータページがいっぱいになって、B+Treeアルゴリズムに従って、新しいデータページを追加して、ページ分裂と呼ばれて、性能が低下します.空間利用率は約50%低下した.隣接する2つのデータ・ページの使用率が低い場合、システムはデータ・ページのマージを行い、マージのプロセスは分裂プロセスの逆プロセスです.
3.パフォーマンスとストレージスペースの面から考えると、プライマリ・キーの増加はより合理的な選択であることが多い.
4.プライマリ・キーの長さが小さいほど、通常のインデックスのリーフ・ノードが小さくなり、通常のインデックスのスペースも小さくなります.したがって、自己増加プライマリ・キーが適切である.
四、索引の上書き
このクエリKのインデックスは、まずプライマリ・キーを検索し、次にプライマリ・キーのインデックスを検索し、テーブルに戻って具体的な情報を取得する.select ID from T where k between 3 and 5この場合、IDの値を調べるだけで、IDの値はkインデックスツリーにあるので、クエリー結果を直接提供することができ、テーブルに戻る必要はありません.インデックスを上書きすると、ツリーの検索回数が減少し、クエリーのパフォーマンスが大幅に向上するため、インデックスを上書きすることは一般的なパフォーマンス最適化手段です.
五、最左接頭辞の原則
インデックスは(a,b)で、クエリー条件bは結合インデックスを使用できませんが、クエリー条件aはインデックスを使用できます.%%最初はインデックスは使用されません.
六、索引に関する問題
歴史的な理由で、このテーブルはa、bを結合するプライマリ・キーを必要とします.それは、フィールドcに単独でインデックスを作成すると、すでに3つのフィールドが含まれていることを意味します.なぜ「ca」「cb」という2つのインデックスを作成するのですか.select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1; 質問:この同僚の説明は正しいですか.削除できるインデックスはどれですか?
答え:(1)プライマリ・キーa,bのクラスタ・インデックスの組織順序はorder by a,bに相当します.つまり、まずaでソートし、bでソートし、cは無秩序です.–a-|–b-|–c-|–d--1 2 3 d 1 3 2 d 1 4 3 d 2 1 3 d 2 2 2 2 d 2 3 4 d(2)インデックスcaの組織は、cを先に押してソートし、aを押してソートし、プライマリ・キーを記録します.–c-|–a-|–プライマリ・キー部分b-(ここではabではなくbのみ)2 1 3 2 2 2 3 1 3 1 4 3 2 4 4 2 2 3 3上のこのインデックスcaはインデックスcのデータとそっくりであることに注意してください.
(3)インデックスcbの組織は、まずcでソートし、bでソートし、同時にプライマリ・キー–c--|–b-|-プライマリ・キー部分a-(同上)2 2 2 2 2 3 1 3 1 2 3 2 3 3 3 4 3 2を記録する
だから結論はcaは取り除くことができて、cbは保留します.
プライマリ・キーは論理キー、インデックスは物理キーです.プライマリ・キーは実際には存在せず、インデックスは実際にデータベースに存在します.インデックスは本当にファイルを生成します.データは本当にファイルを生成します.redo logレコードの内容:物理ログ、「あるデータページで何を変更したか」、リサイクル.bin logレコードの内容:論理ログ、文の元の論理「ID=1,2」、追加使用.プライマリ・キーはファイルを生成しません.プライマリ・キーの検索はIDインデックス・ツリーに依存します.
二、データの概念
データにはデータページの概念があり、mysqlにもメモリの概念があります.mysqlクエリーの場合、まず所在するデータページがメモリにあるかどうかを検出し、存在する場合、クエリーは直接戻ります.存在しない場合は、ディスクからメモリにロードし、結果を返します.
更新操作では、データがメモリに存在しない場合は、ディスクからメモリにロードし、cpuを変更してからメモリに値を入れ、redo logログテーブルに更新します.
三、索引の原理
1.B+ツリーインデックス
B+ツリーのすべてのキーワードは、リーフノードのチェーンテーブル(稠密インデックス)に表示され、チェーンテーブルのキーワードは秩序化されています.非リーフノードはインデックスとしてのみ機能します(疎インデックス).文がselect*from T where ID=500、すなわちプライマリ・キー・クエリ方式の場合、IDというB+ツリーを検索するだけです.文がselect*from T where k=5、すなわち通常のインデックスクエリ方式である場合、まずkインデックスツリーを検索し、IDの値が500で、IDインデックスツリーに1回検索する必要があります.このプロセスはテーブルと呼ばれます.つまり、プライマリ・キー以外のインデックスに基づいたクエリは、インデックス・ツリーを複数スキャンする必要があります.したがって、アプリケーションではできるだけプライマリ・キー・クエリーを使用する必要があります.
2.1つのデータページがいっぱいになって、B+Treeアルゴリズムに従って、新しいデータページを追加して、ページ分裂と呼ばれて、性能が低下します.空間利用率は約50%低下した.隣接する2つのデータ・ページの使用率が低い場合、システムはデータ・ページのマージを行い、マージのプロセスは分裂プロセスの逆プロセスです.
3.パフォーマンスとストレージスペースの面から考えると、プライマリ・キーの増加はより合理的な選択であることが多い.
4.プライマリ・キーの長さが小さいほど、通常のインデックスのリーフ・ノードが小さくなり、通常のインデックスのスペースも小さくなります.したがって、自己増加プライマリ・キーが適切である.
四、索引の上書き
create table T
( ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),
(200,2,'bb'),
(300,3,'cc'),
(500,5,'ee'),
(600,6,'ff'),
(700,7,'gg');
select * from T where k between 3 and 5 このクエリKのインデックスは、まずプライマリ・キーを検索し、次にプライマリ・キーのインデックスを検索し、テーブルに戻って具体的な情報を取得する.select ID from T where k between 3 and 5この場合、IDの値を調べるだけで、IDの値はkインデックスツリーにあるので、クエリー結果を直接提供することができ、テーブルに戻る必要はありません.インデックスを上書きすると、ツリーの検索回数が減少し、クエリーのパフォーマンスが大幅に向上するため、インデックスを上書きすることは一般的なパフォーマンス最適化手段です.
五、最左接頭辞の原則
インデックスは(a,b)で、クエリー条件bは結合インデックスを使用できませんが、クエリー条件aはインデックスを使用できます.%%最初はインデックスは使用されません.
六、索引に関する問題
CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;歴史的な理由で、このテーブルはa、bを結合するプライマリ・キーを必要とします.それは、フィールドcに単独でインデックスを作成すると、すでに3つのフィールドが含まれていることを意味します.なぜ「ca」「cb」という2つのインデックスを作成するのですか.select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1; 質問:この同僚の説明は正しいですか.削除できるインデックスはどれですか?
答え:(1)プライマリ・キーa,bのクラスタ・インデックスの組織順序はorder by a,bに相当します.つまり、まずaでソートし、bでソートし、cは無秩序です.–a-|–b-|–c-|–d--1 2 3 d 1 3 2 d 1 4 3 d 2 1 3 d 2 2 2 2 d 2 3 4 d(2)インデックスcaの組織は、cを先に押してソートし、aを押してソートし、プライマリ・キーを記録します.–c-|–a-|–プライマリ・キー部分b-(ここではabではなくbのみ)2 1 3 2 2 2 3 1 3 1 4 3 2 4 4 2 2 3 3上のこのインデックスcaはインデックスcのデータとそっくりであることに注意してください.
(3)インデックスcbの組織は、まずcでソートし、bでソートし、同時にプライマリ・キー–c--|–b-|-プライマリ・キー部分a-(同上)2 2 2 2 2 3 1 3 1 2 3 2 3 3 3 4 3 2を記録する
だから結論はcaは取り除くことができて、cbは保留します.