MySqlストレージエンジンとインデックス

ストレージエンジン
データベース・ストレージ・エンジンとは?
データベースエンジンはデータベースの下層ソフトウェアコンポーネントであり、異なるストレージエンジンは異なるストレージメカニズム、インデックステクニック、ロックレベルなどの機能を提供し、異なるデータベースエンジンを使用して、特定の機能を得ることができる.
エンジンの表示方法

--            
show engines;
​
--         ：
show create table   \G
​
--           ：
show table status\G

テーブル作成時のエンジンの指定

create table yingqin (id int,name varchar(20)) engine='InnoDB';

テーブルのエンジンの変更

alter table    engine='    ';

デフォルトエンジンの変更

vi/etc/my.cnf(プロファイルアドレスはインストール状況による)

[mysqld]下

default-storage-engine=MyIsAM

保存後にサービスを再開することを覚えています

MyISAMとInnoDBの違い
MyISAM:全文索引(full text)をサポートする;トランザクションはサポートされていません.表レベルのロック;テーブルの特定のロー数を保存します.潰れて回復が悪い.
Innodb:トランザクションのサポート;以前のバージョンでは全文インデックスはサポートされていませんでしたが、5.6以降のバージョンではこの機能がサポートされ始めました.行レベルのロック(絶対ではなく、sql文を実行するときに範囲を特定できない場合、update table set id=3 where name like'a%';など、全テーブルをロックします.テーブルの特定のロー数を保存しません.潰れて回復する.
いつどんなエンジンを選べばいいですか
MyISAM:

トランザクションを使用する必要がない場合

多くのcount計算

InnoDB:

信頼性の要件が高い、またはトランザクションのサポートが要求される

外部キー制約を使用したい場合(MyISAMで確立された外部キーは無効)

推奨用InnoDB
 
索引
インデックスとは?
インデックスは、データ・テーブル内のすべてのレコードに対する参照ポインタを含むディスクに格納されたデータベース構造です.インデックスを使用すると、カラムまたは複数のカラムに特定の値があるローをすばやく見つけることができます.
インデックスの利点:

は、一意のインデックスを作成することによって、データベース・テーブル内の各行のデータの一意性を保証する.

は、データの取得速度を速めることができます.

は、テーブルデータの完全性と正確性を保証することができる

.
インデックスの欠点:

インデックスには物理的なスペースが必要です.

表中のデータを変更する場合、インデックスも動的にメンテナンスする必要があり、データのメンテナンス速度を低下させる.

索引の一般的なタイプ:

index:一般インデックス

unique:ユニークインデックス

primary key:プライマリ・キー・インデックス

foreign key:外部キーインデックス

fulltext:全文インデックス

コンポジットインデックス

 
通常のインデックスとユニークなインデックス
一般インデックスとは?
一般インデックス(index)は、その名の通り、各インデックスの中で最も一般的なインデックスであり、主なタスクはクエリーの速度を高めることです.同じインデックスコンテンツを許可し、null値を許可するのが特徴です.
ユニークインデックスとは?
ユニークインデックス:(unique)名前の通り、同じインデックスコンテンツは表示できませんが、null値を空にすることができます.
通常のインデックスまたは一意のインデックスを作成するにはどうすればいいですか?

--        
create table test (
    id int(7) zerofill auto_increment not null,
    username varchar(20),
    servnumber varchar(30),
    password varchar(20),
    createtime datetime,
    unique (id)
)DEFAULT CHARSET=utf8;

--        
--  ：alter table    add index      (    );
--  ：           ，             
alter table test add unique unique_username (username);
​
--      
--  ：create index    on    (   );
create index index_createtime on test (createtime);

索引の表示

--  ：show index from   \G
show index from test\G

索引の削除方法

--  ：drop index      on   ;
drop index unique_username on test;
--  ：alter table    drop index    ;
alter table test drop index createtime;

 
プライマリ・キー索引
プライマリ・キー・インデックスとは?
プライマリ・キーにインデックスを追加することは、特殊な一意のインデックスであり、空の値は許可されず、一意のインデックス(uniqueは空の値を許可されます).「PRIMARY KEY」に指定
≪プライマリ・キー|Primary Key|emdw≫:プライマリ・キーはテーブルの列で、この列の値はテーブル内の各行のデータを示すために使用されます.注:各テーブルにはプライマリ・キーが1つしかありません
プライマリ・キーの作成:

--1）        


--2）          
--  ：alter table    add primary key (   );
alter table test add primary key (id);

プライマリ・キーの削除:

--  ：alter table    drop primary key;
alter table test drop primary key;

注意:プライマリ・キーを削除するには、自己増加がある場合は、まず自己増加を削除する必要があります.

​--    ：
alter table test change id id int(7) unsigned zerofill not null;

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

--      sql：
create table command (
    id int(5) unsigned primary key  auto_increment,
    name varchar(10),
    instruction varchar(60)
)engine=MyISAM;

--    sql：
insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','         ');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','     ，  ');
insert into command values('9','   ','is,not,me,yes,no ...');

全文索引を追加:

--1）            

--2）  alter  
alter table command add fulltext(instruction);

全文索引の使用:

--  ：select * from    where match (   ) against ('    ');
select * from command where match(instruction) against ('sections');

整合性の表示:

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

ストップワード:
頻度の高い語は、全文インデックスを失効させます.
in boolean modeモード:
in boolean mode:全文検索モードをブール全文検索と指定することを意味する(簡単には検索方式と理解できる)

--  ：select * from    where match (   ) against ('    ' in boolean mode);
select * from command where match(instruction) against ('direct*' in boolean mode);

注意点:ワイルドカード*を使用する場合、単語の後ろにしか置けず、前に置けません.
全文索引を削除:

alter table command drop index instruction;

注意点まとめ:

一般的に全文インデックスを作成するフィールドデータ型はchar、varchar、textである.その他のフィールドタイプは

できません.

全文インデックスは、非常に頻繁な語に対してインデックスを作成しません.例えばis,no,not,you,me,yesなど,我々は停止語

と呼ぶ.

英語検索時に大文字と小文字を無視する

 
外部キーコンストレイント
外部キーとは?
外部キーは、2つのテーブル・データ間のリンクに作用する1つまたは複数のカラムであり、テーブルとテーブル間のデータの完全性と正確性を保証します.
外部キーコンストレイントを追加するには:

--  ：foreign key (   ) references      (       )
--​  ：               

--create table    ：
CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '    ',
  `ename` varchar(50) DEFAULT NULL COMMENT '    ',
  `job` varchar(30) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL COMMENT '      ',
  `hiredate` date DEFAULT NULL COMMENT '    ',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '  ',
  `deptnu` int(11) DEFAULT NULL COMMENT '    ',
  PRIMARY KEY (`empno`),
 foreign key (deptnu) references dept(deptnu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--alter table   ：
alter table employee add foreign key (deptnu) references dept(deptnu);

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

mysql> alter table employee drop index deptnu;
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
mysql> 
mysql> alter table employee drop foreign key employee_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意点まとめ:

の2つのテーブルで、プライマリ・キーと外部キーのフィールド・タイプは必ず同じ

でなければなりません.

外部キー制約テーブルを使用するエンジンは必ずInnoDBエンジンで、MyISAMは機能しない

です.

外部キーインデックスを削除する前に、

を削除するには、外部キー制約を削除する必要があります.
 
結合インデックス
連合インデックスとは?
コンビネーションインデックスは、コンビネーションインデックスまたは複合インデックスとも呼ばれ、2つ以上のカラム以上に構築されたインデックスです.
結合インデックスの作成

--  ：alter table    add index(  1,  2,  3);
alter table test add index(username,servnumber,password);

結合インデックスの削除

--  ：alter table test drop index    ;
alter table test drop index username;

複数の単一カラムインデックスではなく、統合インデックスを使用する理由
統合インデックスの効率は、単一カラムインデックスよりもはるかに高い.3つの単列インデックスが作成され、クエリー条件にも3つの列が存在する場合、MySQLは3つのインデックスではなく最適な列インデックスのみを選択します.
連合インデックスの最左原則
上記のインデックスを例にとると、クエリ条件にusernameがある必要があります.そうしないと、インデックスは使用されません.
注意点まとめ:

インデックスは多ければ多いほど良いわけではありません.インデックスが多すぎると、データのメンテナンス速度が増加し、ディスク領域の無駄になります.

テーブルのデータ量が大きい場合、インデックスの作成を検討できます.

テーブルでデータを頻繁に調べるフィールドで、インデックスの作成を検討できます.

は、テーブル内のデータの一意性を保証するために、一意のインデックスを確立することを考慮することができる.

は、2つのテーブルのデータの完全性と正確性を保証するために、外部キー制約の確立を考慮することができる.

複数のカラムデータを頻繁にクエリーする場合は、統合インデックスの作成を検討できます.