主頁 > 知識庫 > MySql 存儲引擎和索引相關(guān)知識總結(jié)

MySql 存儲引擎和索引相關(guān)知識總結(jié)

熱門標簽:怎么更改高德地圖標注 上海市三維地圖標注 電話機器人是電腦呼號嗎 400電話到哪辦理優(yōu)惠 博樂電銷機器人 機器人打電銷電話 云南大數(shù)據(jù)外呼系統(tǒng) 南寧外呼系統(tǒng)招商 鄭州網(wǎng)絡(luò)外呼系統(tǒng)價錢

存儲引擎

什么是數(shù)據(jù)庫存儲引擎?

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

如何查看引擎?

--如何查看數(shù)據(jù)庫支持的引擎
show engines;
​
--查看當前數(shù)據(jù)的引擎:
show create table 表名\G
​
--查看當前庫所有表的引擎:
show table status\G

建表時指定引擎

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

修改表的引擎

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

修改默認引擎

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

MyISAM 與 InnoDB 的區(qū)別

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

Innodb:支持事務(wù);以前的版本是不支持全文索引,但在5.6之后的版本就開始支持這個功能了;行級鎖(并非絕對,當執(zhí)行sql語句時不能確定范圍時,也會進行鎖全表,例如: update table set id=3 where name like 'a%';);不保存表的具體行數(shù);奔潰恢復(fù)好。

什么時候選擇什么引擎比較好

MyISAM:

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

InnoDB:

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

推薦用 InnoDB

索引

什么是索引?

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

索引的優(yōu)點:

  • 通過創(chuàng)建唯一索引,來保證數(shù)據(jù)庫表中的每一行數(shù)據(jù)的唯一性。
  • 可以加快數(shù)據(jù)的檢索速度。
  • 可以保證表數(shù)據(jù)的完整性與準確性

索引的缺點:

  • 索引需要占用物理空間。
  • 對表中的數(shù)據(jù)進行改動時,索引也需要跟著動態(tài)維護,降低了數(shù)據(jù)的維護速度。

索引的常見類型:

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

普通索引與唯一索引

什么是普通索引?

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

什么是唯一索引?

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

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

--創(chuàng)建表的時候創(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;

--直接為表添加索引
--語法:alter table 表名 add index 索引名稱 (字段名稱);
--注意:假如沒有指定索引名稱時,會以默認的字段名為索引名稱
alter table test add unique unique_username (username);
​
--直接創(chuàng)建索引
--語法: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”

主鍵:主鍵是表的某一列,這一列的值是用來標志表中的每一行數(shù)據(jù)的。注意:每一張表只能擁有一個主鍵

創(chuàng)建主鍵:

--1)創(chuàng)建表的時候創(chuàng)建

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

全文索引

什么是全文索引?

全文索引是將存儲在數(shù)據(jù)庫中的文章或者句子等任意內(nè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','修改文件名,移動');
insert into command values('9','停止詞','is,not,me,yes,no ...');

添加全文索引:

--1)創(chuàng)建表的時候創(chuàng)建全文索引

--2)通過alter添加
alter table command add fulltext(instruction);

使用全文索引:

--語法: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)頻率很高的詞,將會使全文索引失效。

in boolean mode 模式:

in boolean mode:意思是指定全文檢索模式為布爾全文檢索(簡單可以理解為是檢索方式)

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

注意點:使用通配符*時,只能放在詞的后邊,不能放前邊。

刪除全文索引:

alter table command drop index instruction;

注意點總結(jié):

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

外鍵約束

什么是外鍵?

外鍵就是作用于兩個表數(shù)據(jù)之間的鏈接的一列或多列,用來保證表與表之間的數(shù)據(jù)的完整性和準確性。

添加外鍵約束:

--語法:foreign key (字段名) references 關(guān)聯(lián)的表名(關(guān)聯(lián)表的字段名)
--​注意:主鍵跟外鍵的字段類型一定要相同

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

注意點總結(jié):

  1. 倆個表,主鍵跟外鍵的字段類型一定要相同
  2. 要使用外鍵約束表的引擎一定得是 InnoDB 引擎,MyISAM 是不起作用的
  3. 在干掉外鍵索引之前必須先把外鍵約束刪除,才能刪除索引

聯(lián)合索引

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

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

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

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

刪除聯(lián)合索引

--語法:alter table test drop index 索引名;
alter table test drop index username;

為什么要使用聯(lián)合索引,而不使用多個單列索引?

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

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

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

注意點總結(jié):

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

以上就是MySql 存儲引擎和索引相關(guān)知識總結(jié)的詳細內(nèi)容,更多關(guān)于MySql 存儲引擎和索引的資料請關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • 詳解MySQL InnoDB存儲引擎的內(nèi)存管理
  • 關(guān)于MySQL Memory存儲引擎的相關(guān)知識
  • 詳解mysql中的存儲引擎
  • 簡述MySQL InnoDB存儲引擎
  • MySQL MyISAM默認存儲引擎實現(xiàn)原理
  • MySQL 學(xué)習(xí)總結(jié) 之 初步了解 InnoDB 存儲引擎的架構(gòu)設(shè)計
  • 淺談MySQL中四種常用存儲引擎
  • MySQL高級學(xué)習(xí)筆記(三):Mysql邏輯架構(gòu)介紹、mysql存儲引擎詳解
  • MySQL存儲引擎InnoDB的配置與使用的講解
  • MySQL存儲引擎MyISAM與InnoDB區(qū)別總結(jié)整理
  • MySQL 常見存儲引擎的優(yōu)劣

標簽:澳門 益陽 白銀 恩施 杭州 定西 秦皇島 寧夏

巨人網(wǎng)絡(luò)通訊聲明:本文標題《MySql 存儲引擎和索引相關(guān)知識總結(jié)》,本文關(guān)鍵詞  MySql,存儲,引擎,和,索引,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySql 存儲引擎和索引相關(guān)知識總結(jié)》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySql 存儲引擎和索引相關(guān)知識總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章