存儲(chǔ)引擎

什么是數(shù)據(jù)庫(kù)存儲(chǔ)引擎？

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

如何查看引擎？

--如何查看數(shù)據(jù)庫(kù)支持的引擎show engines;​--查看當(dāng)前數(shù)據(jù)的引擎：show create table 表名G​--查看當(dāng)前庫(kù)所有表的引擎：show table statusG

建表時(shí)指定引擎

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

修改表的引擎

alter table 表名 engine=’引擎名稱(chēng)’;

修改默認(rèn)引擎

vi /etc/my.cnf （配置文件地址根據(jù)安裝情況） [mysqld]下面 default-storage-engine=MyIsAM 記得保存后重啟服務(wù)

MyISAM 與 InnoDB 的區(qū)別

MyISAM：支持全文索引（full text）；不支持事務(wù);表級(jí)鎖;保存表的具體行數(shù);奔潰恢復(fù)不好。

Innodb：支持事務(wù)；以前的版本是不支持全文索引，但在5.6之后的版本就開(kāi)始支持這個(gè)功能了；行級(jí)鎖（并非絕對(duì)，當(dāng)執(zhí)行sql語(yǔ)句時(shí)不能確定范圍時(shí)，也會(huì)進(jìn)行鎖全表，例如： update table set id=3 where name like ’a%’;）；不保存表的具體行數(shù)；奔潰恢復(fù)好。

什么時(shí)候選擇什么引擎比較好

MyISAM：

不需要用到事務(wù)的時(shí)候 做很多 count 計(jì)算

InnoDB：

可靠性要求高的，或者要求支持事務(wù) 想要用到外鍵約束的時(shí)候（MyISAM建立的外鍵是無(wú)效的）​

推薦用 InnoDB

索引

什么是索引？

索引是一個(gè)單獨(dú)的，存儲(chǔ)在磁盤(pán)中上的數(shù)據(jù)庫(kù)結(jié)構(gòu)，它們包含著對(duì)數(shù)據(jù)表里的所有記錄的引用指針。使用索引可以快速的找出在某列或多列中有特定值的行。

索引的優(yōu)點(diǎn)：

通過(guò)創(chuàng)建唯一索引，來(lái)保證數(shù)據(jù)庫(kù)表中的每一行數(shù)據(jù)的唯一性。 可以加快數(shù)據(jù)的檢索速度。 可以保證表數(shù)據(jù)的完整性與準(zhǔn)確性

索引的缺點(diǎn)：

索引需要占用物理空間。 對(duì)表中的數(shù)據(jù)進(jìn)行改動(dòng)時(shí)，索引也需要跟著動(dòng)態(tài)維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。

索引的常見(jiàn)類(lèi)型：

index：普通索引 unique：唯一索引 primary key：主鍵索引 foreign key：外鍵索引 fulltext: 全文索引 組合索引

普通索引與唯一索引

什么是普通索引？

普通索引（index）顧名思義就是各類(lèi)索引中最為普通的索引，主要任務(wù)就是提高查詢(xún)速度。其特點(diǎn)是允許出現(xiàn)相同的索引內(nèi)容，允許空（null）值

什么是唯一索引？

唯一索引：（unique）顧名思義就是不可以出現(xiàn)相同的索引內(nèi)容，但是可以為空（null）值

如何創(chuàng)建普通索引或者唯一索引？

--創(chuàng)建表的時(shí)候創(chuàng)建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;--直接為表添加索引--語(yǔ)法：alter table 表名 add index 索引名稱(chēng) (字段名稱(chēng));--注意：假如沒(méi)有指定索引名稱(chēng)時(shí)，會(huì)以默認(rèn)的字段名為索引名稱(chēng)alter table test add unique unique_username (username);​--直接創(chuàng)建索引--語(yǔ)法：create index 索引 on 表名 (字段名);create index index_createtime on test (createtime);

查看索引

--語(yǔ)法：show index from 表名Gshow index from testG

如何刪除索引

--語(yǔ)法：drop index 索引名稱(chēng) on 表名;drop index unique_username on test;--語(yǔ)法：alter table 表名 drop index 索引名;alter table test drop index createtime;

主鍵索引

什么是主鍵索引？

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

主鍵：主鍵是表的某一列，這一列的值是用來(lái)標(biāo)志表中的每一行數(shù)據(jù)的。注意：每一張表只能擁有一個(gè)主鍵

創(chuàng)建主鍵：

--1）創(chuàng)建表的時(shí)候創(chuàng)建--2）直接為表添加主鍵索引--語(yǔ)法：alter table 表名 add primary key (字段名);alter table test add primary key (id);

刪除主鍵：

--語(yǔ)法：alter table 表名 drop primary key;alter table test drop primary key;

注意：在有自增的情況下，必須先刪除自增，才可以刪除主鍵

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

全文索引

什么是全文索引？

全文索引是將存儲(chǔ)在數(shù)據(jù)庫(kù)中的文章或者句子等任意內(nèi)容信息查找出來(lái)的索引，單位是詞。全文索引也是目前搜索引擎使用的一種關(guān)鍵技術(shù)。指定為 fulltext

--創(chuàng)建練習(xí)表的sql：create table command ( id int(5) unsigned primary key auto_increment, name varchar(10), instruction varchar(60))engine=MyISAM;--插入數(shù)據(jù)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’,’復(fù)制文件或者文件夾’);insert into command values(’7’,’top’,’display Linux processes’);insert into command values(’8’,’mv’,’修改文件名，移動(dòng)’);insert into command values(’9’,’停止詞’,’is,not,me,yes,no ...’);

添加全文索引：

--1）創(chuàng)建表的時(shí)候創(chuàng)建全文索引--2）通過(guò)alter添加alter table command add fulltext(instruction);

使用全文索引：

--語(yǔ)法：select * from 表名 where match (字段名) against (’檢索內(nèi)容’);select * from command where match(instruction) against (’sections’);

查看匹配度：

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

停止詞：

出現(xiàn)頻率很高的詞，將會(huì)使全文索引失效。

in boolean mode 模式：

in boolean mode：意思是指定全文檢索模式為布爾全文檢索（簡(jiǎn)單可以理解為是檢索方式）

--語(yǔ)法：select * from 表名 where match (字段名) against (’檢索內(nèi)容’ in boolean mode);select * from command where match(instruction) against (’direct*’ in boolean mode);

注意點(diǎn)：使用通配符*時(shí)，只能放在詞的后邊，不能放前邊。

刪除全文索引：

alter table command drop index instruction;

注意點(diǎn)總結(jié)：

一般情況下創(chuàng)建全文索引的字段數(shù)據(jù)類(lèi)型為 char、varchar、text 。其它字段類(lèi)型不可以 全文索引不針對(duì)非常頻繁的詞做索引。比如 is，no，not，you，me，yes 這些，我們稱(chēng)之為停止詞 對(duì)英文檢索時(shí)忽略大小寫(xiě)

外鍵約束

什么是外鍵？

外鍵就是作用于兩個(gè)表數(shù)據(jù)之間的鏈接的一列或多列，用來(lái)保證表與表之間的數(shù)據(jù)的完整性和準(zhǔn)確性。

添加外鍵約束：

--語(yǔ)法：foreign key (字段名) references 關(guān)聯(lián)的表名(關(guān)聯(lián)表的字段名)--​注意：主鍵跟外鍵的字段類(lèi)型一定要相同--create table 的方法：CREATE TABLE `employee` ( `empno` int(11) NOT NULL COMMENT ’雇員編號(hào)’, `ename` varchar(50) DEFAULT NULL COMMENT ’雇員姓名’, `job` varchar(30) DEFAULT NULL, `mgr` int(11) DEFAULT NULL COMMENT ’雇員上級(jí)編號(hào)’, `hiredate` date DEFAULT NULL COMMENT ’雇傭日期’, `sal` decimal(7,2) DEFAULT NULL COMMENT ’薪資’, `deptnu` int(11) DEFAULT NULL COMMENT ’部門(mén)編號(hào)’, 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 constraintmysql> mysql> alter table employee drop foreign key employee_ibfk_1;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> mysql> alter table employee drop index deptnu;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0

注意點(diǎn)總結(jié)：

倆個(gè)表，主鍵跟外鍵的字段類(lèi)型一定要相同 要使用外鍵約束表的引擎一定得是 InnoDB 引擎，MyISAM 是不起作用的 在干掉外鍵索引之前必須先把外鍵約束刪除，才能刪除索引

聯(lián)合索引

什么是聯(lián)合索引？

聯(lián)合索引又稱(chēng)組合索引或者復(fù)合索引，是建立在倆列或者多列以上的索引。

創(chuàng)建聯(lián)合索引

--語(yǔ)法：alter table 表名 add index(字段1,字段2,字段3);alter table test add index(username,servnumber,password);

刪除聯(lián)合索引

--語(yǔ)法：alter table test drop index 索引名;alter table test drop index username;

為什么要使用聯(lián)合索引，而不使用多個(gè)單列索引？

聯(lián)合索引的效率遠(yuǎn)遠(yuǎn)高于單列索引。假如創(chuàng)建了三個(gè)單列索引，并且查詢(xún)條件中也存在這三列，但是 MySQL 只會(huì)選擇最優(yōu)的列索引，而不會(huì)三個(gè)索引都用上

聯(lián)合索引的最左原則

以上面的索引為例，查詢(xún)條件中必須有 username，才會(huì)去使用這個(gè)索引，否則不會(huì)去使用該索引

注意點(diǎn)總結(jié)：

索引并非越多越好，過(guò)多的索引會(huì)增加數(shù)據(jù)的維護(hù)速度還有磁盤(pán)空間的浪費(fèi)。 當(dāng)表的數(shù)據(jù)量很大的時(shí)候，可以考慮建立索引。 表中經(jīng)常查數(shù)據(jù)的字段，可以考慮建立索引。 想要保證表中數(shù)據(jù)的唯一性，可以考慮建立唯一索引。 想要保證倆張表中的數(shù)據(jù)的完整性跟準(zhǔn)確性，可以考慮建立外鍵約束。 經(jīng)常對(duì)多列數(shù)據(jù)進(jìn)行查詢(xún)時(shí)，可以考慮建立聯(lián)合索引。

以上就是MySql 存儲(chǔ)引擎和索引相關(guān)知識(shí)總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySql 存儲(chǔ)引擎和索引的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！