MySql ストレージ エンジンとインデックスに関する知識のまとめ

ストレージエンジン

データベース ストレージ エンジンとは何ですか?

データベース エンジンは、データベースの基盤となるソフトウェア コンポーネントです。ストレージ エンジンによって、ストレージ メカニズム、インデックス作成手法、ロック レベルなどの機能が異なります。異なるデータベース エンジンを使用することで、特定の機能を実現できます。

エンジンを表示するにはどうすればいいですか?

--データベースでサポートされているエンジンを表示する方法 show engines;
​
--現在のデータ エンジンを表示します。
表示 テーブルの作成 テーブル名\G
​
--現在のライブラリ内のすべてのテーブルのエンジンを表示します。
テーブルステータスを表示_G

テーブルを作成するときにエンジンを指定する

テーブル yingqin を作成します (id int、name varchar(20)) engine='InnoDB';

テーブル変更エンジン

alter table テーブル名 engine = 'エンジン名';

デフォルトのエンジンを変更する

1. vi /etc/my.cnf (設定ファイルのアドレスはインストール状況によって異なります)
2. [mysqld] 以下
3. デフォルトのストレージエンジン=MyIsAM
4. 保存後は必ずサービスを再起動するようにしてください
   

MyISAM と InnoDB の違い

MyISAM: 全文インデックス (全文) をサポートします。トランザクションはサポートされません。テーブル レベルのロックがあり、テーブル内の特定の行数を保存します。クラッシュ リカバリは不十分です。

Innodb: トランザクションをサポートします。以前のバージョンではフルテキスト インデックスはサポートされていませんでしたが、バージョン 5.6 以降ではこの機能がサポートされるようになりました。行レベルのロック (絶対的ではありません。SQL ステートメントの実行時に範囲を決定できない場合は、テーブル全体がロックされます。例: update table set id=3 where name like 'a%';)。テーブル内の特定の行数は保存されません。クラッシュ リカバリが良好です。

どのエンジンが優れているかを選択するタイミング

マイISAM:

• 取引が不要な場合
• たくさんのカウント計算をする
  

InnoDB:

• 高い信頼性要件またはサポートサービス
• 外部キー制約を使用したい場合（MyISAMで作成された外部キーは無効）
  

InnoDBが推奨されます

索引

インデックスとは何ですか?

インデックスは、ディスク上に保存される個別のデータベース構造であり、テーブル内のすべてのレコードへの参照ポインターが含まれています。インデックスを使用すると、1 つ以上の列に特定の値を持つ行をすばやく見つけることができます。

インデックスの利点:

• 一意のインデックスを作成することにより、データベース テーブル内の各データ行の一意性が保証されます。
• データの取得を高速化できます。
• テーブルデータの整合性と正確性を確保できる
  

インデックス作成の欠点:

• インデックスには物理的なスペースが必要です。
• テーブル内のデータが変更されると、インデックスも動的に維持する必要があり、データ維持の速度が低下します。

一般的なインデックスの種類:

• インデックス: 通常のインデックス
• ユニーク: ユニークなインデックス
• 主キー: 主キーインデックス
• 外部キー: 外部キーインデックス
• 全文: 全文インデックス
• 総合指数
  

通常のインデックスとユニークインデックス

通常のインデックスとは何ですか?

名前が示すように、共通インデックスはすべての種類のインデックスの中で最も一般的なインデックスです。その主な役割は、クエリ速度を向上させることです。同じインデックス内容が許可され、null 値が許可されるのが特徴です。

ユニークインデックスとは何ですか?

ユニークインデックス: 名前が示すように、同じインデックスコンテンツは出現できませんが、空（null）値になることがあります。

通常のインデックスまたは一意のインデックスを作成するにはどうすればよいですか?

--テーブルテストの作成（
  id int(7) ゼロフィル auto_increment nullではない、
  ユーザー名varchar(20)、
  サーバ番号varchar(30)、
  パスワードvarchar(20)、
  作成時刻 日付時刻、
  ユニーク (ID)
)デフォルト文字セット=utf8;

--テーブルに直接インデックスを追加します--構文: alter table table name add index index name (field name);
--注: インデックス名が指定されていない場合は、デフォルトのフィールド名がインデックス名として使用されます。alter table test add unique unique_username (username);
​
-- インデックスを直接作成します -- 構文: create index index on table name (field name);
test(createtime) にインデックス index_createtime を作成します。

インデックスを表示

--構文: テーブル名からインデックスを表示\G
test_G からインデックスを表示

インデックスを削除する方法

--構文: テーブル名のインデックス インデックス名を削除します。
テストでインデックス unique_username を削除します。
--構文: alter table table name drop index index name;
テーブルを変更してテストし、インデックスを削除してcreatetimeを実行します。

主キーインデックス

主キーインデックスとは何ですか?

主キーにインデックスを追加すると、主キー インデックスが作成されます。これは、null 値を許可しない特別な一意のインデックスですが、一意のインデックス (unique) では null 値が許可されます。 「PRIMARY KEY」として指定

主キー: 主キーはテーブル内の列であり、この列の値はテーブル内の各データ行をマークするために使用されます。注意: 各テーブルには主キーを 1 つだけ設定できます。

主キーを作成します。

--1) テーブルの作成時に作成します --2) テーブルに直接主キー インデックスを追加します --構文: alter table table name add primary key (field name);
テーブルテストを変更して主キー (ID) を追加します。

主キーを削除するには:

--構文: alter table table name drop primary key;
テーブルテストを変更して主キーを削除します。

注: 自動増分がある場合は、主キーを削除する前に自動増分を削除する必要があります。

​--自動増分を削除:
alter table test change id id int(7) unsigned zerofill not null;

全文索引

全文インデックスとは何ですか?

全文索引とは、データベースに格納されている記事や文章などの任意のコンテンツ情報を検索するための索引であり、索引の単位は単語です。全文インデックスも、現在検索エンジンで使用されている重要な技術です。全文を指定

--練習用テーブルの SQL を作成します。
テーブル作成コマンド（
  id int(5) 符号なし主キー auto_increment,
  名前varchar(10),
  命令varchar(60)
)エンジン=MyISAM;

--データ挿入SQL:
コマンドに値を挿入します('1','ls','ディレクトリの内容を一覧表示');
コマンドに値を挿入します('2','wc','各ファイルの改行数、単語数、バイト数を出力します');
コマンドに値を挿入します('3','cut','ファイルの各行からセクションを削除します');
コマンド値に挿入します('4','sort','テキスト ファイルの行をソート');
コマンド値に挿入('5','find','ディレクトリ階層内のファイルを検索');
コマンドに値を挿入します('6','cp','ファイルまたはフォルダーをコピーします');
コマンド値に挿入します('7','top','Linuxプロセスの表示');
コマンドに値を挿入します('8','mv','ファイル名を変更して移動');
コマンドに値('9'、'ストップワード'、'is、not、me、yes、no ...')を挿入します。

全文インデックスを追加します。

--1) テーブルの作成時にフルテキスト インデックスを作成します --2) alter alter table コマンド add fulltext(instruction); を通じて追加します

全文インデックスを使用する:

--構文: select * from table name where match (フィールド名) against ('検索コンテンツ');
select * from command where match(instruction) against ('sections');

一致度を確認してください:

select * from command where match(instruction) against ('directory');

ストップワード:

非常に頻繁に出現する単語は、全文インデックスを無効にします。

ブールモードの場合:

ブールモード: 全文検索モードをブール全文検索として指定することを意味します（これは単に検索方法として理解できます）。

--構文: select * from table name where match (フィールド名) against (ブールモードでの '検索コンテンツ');
select * from command where match(instruction) against ('direct*' in boolean mode);

注意: ワイルドカード * を使用する場合、ワイルドカードは単語の前にではなく、単語の後にのみ配置できます。

全文インデックスを削除するには:

alter table コマンド、drop index 命令。

注意すべき点のまとめ:

1. 一般に、フルテキスト インデックスを作成するためのフィールド データ型は、char、varchar、および text です。その他のフィールドタイプは許可されません。
2. 全文インデックスでは、頻繁に使用される単語はインデックス化されません。例えば、is、no、not、you、me、yes、これらはストップワードと呼ばれます
3. 英語で検索する場合は大文字と小文字を無視します

外部キー制約

外部キーとは何ですか?

外部キーは、テーブル間のデータの整合性と正確性を確保するために、2 つのテーブル内のデータ間のリンクとして機能する 1 つ以上の列です。

外部キー制約を追加します。

--構文: 外部キー (フィールド名) は、関連付けられたテーブル名 (関連付けられたテーブルのフィールド名) を参照します。
--​注: 主キーと外部キーのフィールド タイプは同じである必要があります -- テーブル作成方法:
テーブル「従業員」を作成します（
 `empno` int(11) NOT NULL COMMENT '従業員番号',
 `ename` varchar(50) DEFAULT NULL COMMENT '従業員名',
 `job` varchar(30) デフォルト NULL,
 `mgr` int(11) DEFAULT NULL COMMENT '従業員上司番号',
 `hiredate` 日付 デフォルト NULL コメント 'hired date',
 `sal` 小数点(7,2) デフォルト NULL コメント '給与',
 `deptnu` int(11) DEFAULT NULL COMMENT '部門番号',
 主キー (`empno`)、
 外部キー (deptnu) は dept(deptnu) を参照します。
)ENGINE=InnoDB デフォルト文字セット=utf8;

--テーブル変更メソッド:
テーブル employee を変更し、外部キー (deptnu) を追加して、dept(deptnu) を参照します。

外部キー制約を削除するには:

注意: 外部キー インデックスを削除する前に、まず外部キー制約を削除する必要があります。

mysql> テーブル employee を変更し、インデックス deptnu を削除します。
エラー 1553 (HY000): インデックス 'deptnu' を削除できません: 外部キー制約で必要です
マイSQL> 
mysql> テーブル employee を変更し、外部キー employee_ibfk_1 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
レコード: 0 重複: 0 警告: 0

マイSQL> 
mysql> テーブル employee を変更し、インデックス deptnu を削除します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
レコード: 0 重複: 0 警告: 0

注意すべき点のまとめ:

1. 2つのテーブルの主キーと外部キーのフィールドタイプは同じである必要があります
2. 外部キー制約を使用するためのエンジンは InnoDB である必要があります。MyISAM は動作しません。
3. 外部キー インデックスを削除する前に、まず外部キー制約を削除する必要があります。

共同インデックス

ジョイントインデックスとは何ですか?

結合インデックス (複合インデックスまたは複合インデックスとも呼ばれる) は、2 つ以上の列に基づくインデックスです。

共同インデックスの作成

--構文: alter table table name add index (field 1, field 2, field 3);
alter table test add index(username,servnumber,password);

ジョイントインデックスの削除

--構文: alter table test drop index index name;
テーブルを変更してテストし、インデックスを削除してユーザー名を取得します。

複数の単一列インデックスではなく結合インデックスを使用するのはなぜですか?

結合インデックスの効率は、単一列インデックスよりもはるかに高くなります。 3 つの単一列インデックスが作成され、クエリ条件にもこれらの 3 つの列が含まれている場合、MySQL は最適な列インデックスのみを選択し、3 つのインデックスすべてを使用することはありません。

ジョイントインデックスの左端の原理

上記のインデックスを例にとると、このインデックスを使用するにはクエリ条件にユーザー名が含まれている必要があり、含まれていない場合は使用されません。

注意すべき点のまとめ:

• インデックスは多ければ多いほど良いです。インデックスが多すぎると、データのメンテナンス速度が上がり、ディスク領域が無駄になります。
• テーブル内のデータ量が多い場合は、インデックスの作成を検討できます。
• データが頻繁にクエリされるテーブル内のフィールドについては、インデックスの作成を検討できます。
• テーブル内のデータの一意性を保証するには、一意のインデックスを作成することを検討してください。
• 2 つのテーブル内のデータの整合性と正確性を確保したい場合は、外部キー制約を確立することを検討してください。
• 複数のデータ列を頻繁にクエリする場合は、結合インデックスの作成を検討できます。

上記はMySqlストレージエンジンとインデックスに関する知識のまとめの詳細な内容です。MySqlストレージエンジンとインデックスの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。