MySql 存儲引擎和索引相關知識總結

存儲引擎
 

什麼是數據庫存儲引擎？

數據庫引擎是數據庫底層軟件組件，不同的存儲引擎提供不同的存儲機制，索引技巧，鎖定水平等功能，使用不同的數據庫引擎，可以獲得特定的功能

如何查看引擎？

  --如何查看數據庫支持的引擎  show engines;    --查看當前數據的引擎：  show create table 表名G    --查看當前庫所有表的引擎：  show table statusG

建表時指定引擎

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

修改表的引擎

  alter table 表名 engine='引擎名稱';

修改默認引擎

1. vi /etc/my.cnf   （配置文件地址根據安裝情況）

2. [mysqld]下面

3. default-storage-engine=MyIsAM

4. 記得保存後重啟服務
   

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 testG

如何刪除索引

  --語法：drop index 索引名稱 on 表名;  drop index unique_username on test;  --語法：alter table 表名 drop index 索引名;  alter table test drop index createtime;

主鍵索引

什麼是主鍵索引？

把主鍵添加索引就是主鍵索引，它是一種特殊的唯一索引，不允許有空值，而唯一索引（unique是允許為空值的）。指定為“PRIMARY KEY”

主鍵：主鍵是表的某一列，這一列的值是用來標誌表中的每一行數據的。注意：每一張表只能擁有一個主鍵

創建主鍵：

  --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;

注意點總結：

1. 一般情況下創建全文索引的字段數據類型為 char、varchar、text 。其它字段類型不可以

2. 全文索引不針對非常頻繁的詞做索引。比如 is，no，not，you，me，yes 這些，我們稱之為停止詞

3. 對英文檢索時忽略大小寫

外鍵約束
 

什麼是外鍵？

外鍵就是作用於兩個表數據之間的鏈接的一列或多列，用來保證表與表之間的數據的完整性和準確性。

添加外鍵約束：

  --語法：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

注意點總結：

1. 倆個表，主鍵跟外鍵的字段類型一定要相同

2. 要使用外鍵約束表的引擎一定得是 InnoDB 引擎，MyISAM 是不起作用的

3. 在幹掉外鍵索引之前必須先把外鍵約束刪除，才能刪除索引

聯合索引
 

什麼是聯合索引？

聯合索引又稱組合索引或者複合索引，是建立在倆列或者多列以上的索引。

創建聯合索引

  --語法：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;

為什麼要使用聯合索引，而不使用多個單列索引？

聯合索引的效率遠遠高於單列索引。假如創建了三個單列索引，並且查詢條件中也存在這三列，但是 MySQL 只會選擇最優的列索引，而不會三個索引都用上

聯合索引的最左原則

以上面的索引為例，查詢條件中必須有 username，才會去使用這個索引，否則不會去使用該索引

注意點總結：

• 索引並非越多越好，過多的索引會增加數據的維護速度還有磁盤空間的浪費。

• 當表的數據量很大的時候，可以考慮建立索引。

• 表中經常查數據的字段，可以考慮建立索引。

• 想要保證表中數據的唯一性，可以考慮建立唯一索引。

• 想要保證倆張表中的數據的完整性跟準確性，可以考慮建立外鍵約束。

• 經常對多列數據進行查詢時，可以考慮建立聯合索引。