MySQL 存儲引擎知識講解

本文主要給大家介紹MySQL 存儲引擎知識講解，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續(xù)在億速云行業(yè)資訊里面關(guān)注我的更新文章的。

1、MySQL鍵值(限制如何給字段賦值)
1）
索引:類似與“書的目錄”樹型目錄
缺點:減慢寫的速度（insert update delete）
占用物理存儲空間
優(yōu)點:加快查詢速度
2）
使用索引
使用規(guī)則
一個表中可以由多個index字段
字段的值允許由重復(fù)，切可以賦NULL
經(jīng)常把做查詢條件的字段設(shè)置為index字段
index字段的key標(biāo)志是MUL

查看索引
desc 表名；

show index for 表名；
#查看索引詳細(xì)信息

創(chuàng)建索引
create table 表名(
字段 類型，
index(索引名)；
#建表時創(chuàng)建索引
#默認(rèn)和字段名同名

-> create table t21(
-> name char(10),
-> age int,
-> sex enum('boy','girl'),
-> index(name),
-> index(sex));

#####################################################################

create index 索引名 on 表名(字段);
#已有表創(chuàng)建索引
#索引名可以和字段名相同
#默認(rèn)使用的索引類型:BRREE（二叉樹）

create index name on t3(name)

drop index 索引名 on 表名；
#刪除索引

###################################################################
主鍵 parimary key

使用規(guī)則
一個表中只能有一個 parimary key 字段
對應(yīng)的字段值不允許有重復(fù)，且不允許賦值
如果有多個字段都作為 parimary key ，稱為復(fù)合主鍵，必須一個創(chuàng)建。
主健字段的KEY標(biāo)志死PRI
通常與 auto_increment 連用
經(jīng)常把表中能夠唯一標(biāo)識記錄的字段位主鍵
1）
-> create table t22(            -> create table t22(
-> name char(10),               -> name char(10)primary key ,
-> age int,                 -> age int,
-> likes set('a','b','c'),      -> likes set('a','b','c')0;
-> primary key(name));
#創(chuàng)建表時添加主鍵

2)
alter table 表名 add primary key(字段)；
#已有表添加主鍵

3）
alter table 表名 drop primary key;
#刪除主鍵

##########################################################################
復(fù)合主鍵
多個字段一起做主鍵，字段值無法同時重復(fù)。

1）
-> create table 表名(
-> cip char(15),
-> port smallint,
-> status enum('allow','deny') default 'deny',
-> primary key(cip,port));
#創(chuàng)建復(fù)合主鍵，必須一起創(chuàng)建

2）
alter table 表名 drop primary key;
#刪除復(fù)合主鍵，必須同時刪除

3）
alter table t23 add primary key(cip,port);
#已有表創(chuàng)建復(fù)合主鍵

#############################################################################
主鍵與 auto_increment 連用，讓字段的最大值自動增長 +1 ,并且位數(shù)值類型

1）
-> create table t24(
-> id int(2) zerofill primary key auto_increment,
-> name char(10),
-> class char(4),
-> index(name));
#創(chuàng)建表

insert into  t24(name,class) values('tom','1709');
#添加信息 name class ，此時表中 id 字段自動 +1 。

2）
alter table t24  modify id int(2) unsigned zerofill not null;
alter table t24 drop  primary key;
#刪除主鍵要先把 aotu_increment

############################################################################
unique唯一索引
一個表中可以有多個unique字段
對應(yīng)的字段值不允許有重復(fù)
unique字段的key標(biāo)志是uni
unique字段的值允許為null，當(dāng)將其修改為不允許為null，則此字段限制與主鍵相同

############################################################################
外鍵：foreign key
作用：
限制給字段賦值的。
值必須在指定表中指定字段值的范圍內(nèi)選擇。
條件：
表的存儲引擎必須是innodb
字段類型要一致
被參照字段必須要是索引類型的一種（primary key)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
1）
foreign key(字段) references 被參考表(字段)
on update cascade on delete cascade
#使用方法，同步更新同步刪除

練習(xí)：
參考表
mysql> create table jfb(
-> id int(2) primary key auto_increment,
-> name char(10),
-> pay float(7,2)
-> )engine=innodb;

insert into jfb(name,pay) values('bob',20000),('lucy',15000);
+-------+----------+-----------------+
| id | name | pay      |
+-------+----------+-----------------+
|  1 | bob  | 20000.00 |
|  2 | lucy | 15000.00 |
+-------+----------+-----------------+

外鍵表
mysql> create table xsb(
-> num int(2),
-> name char(10),
-> class char(9),
-> foreign key(num) references jfb(id)
-> on update cascade
-> on delete cascade
-> )engine=innodb;
#num字段參考jfb(id)

insert into xsb values(1,'bob','nsd1709'),(2,'lucy','nsd1709');
#將xsb寫入數(shù)據(jù)
+----------+----------+----------------+
| num  | name | class   |
+----------+-----------+---------------+
|    1 | bob  | nsd1709 |
|    2 | lucy | nsd1709 |
+----------+----------+----------------+

insert into xsb values(3,'tom','nsd1709');
#因為參考表jfb(id)沒有3，所以無法創(chuàng)建

insert into xsb values(2,'jerry','nsd1709');
+----------+------------+----------------+
| num  | name  | class   |
+----------+------------+----------------+
|    1 | bob   | nsd1709 |
|    2 | lucy  | nsd1709 |
|    2 | jerry | nsd1709 |
+----------+------------+----------------+

參考表jfb(id)只有兩條數(shù)據(jù)，但是還是添加成功因為
參數(shù) 2 存在所以添加成功，但是參考表只有兩條數(shù)據(jù)
為了避免這種情況我們要讓 xsb(num) 也具有唯一性
在上面添加主鍵

delete from xsb where name='jerry';
#刪除重復(fù)數(shù)據(jù)

alter table xsb add primary key(num);
#為 xsb(num) 設(shè)置主鍵

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  1 | bob  | 20000.00 |        |    1 | bob  | nsd1709 |
|  2 | lucy | 15000.00 |        |    2 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

update jfb set id=8 where id=2;
#修改 jfb id=2 改為 id=8

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  1 | bob  | 20000.00 |        |    1 | bob  | nsd1709 |
|  8 | lucy | 15000.00 |        |    8 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

delete from jfb where id=1;
#刪除 jfb id 為1的數(shù)據(jù)

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  8 | lucy | 15000.00 |        |    8 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

delete from jfb;
error:被參考的表不能隨便被刪除的

刪除外鍵
show create table 表名    
#外鍵名稱自動生成。
alter table 表名 drop foreign key 外鍵名
#刪除外鍵名字

###########################################################################
存儲引擎
MySQL 數(shù)據(jù)庫服務(wù)軟件自帶的程序。
不同的存儲引擎有不同的功能和數(shù)據(jù)存儲方式
是處理表的處理器
表名.frm 存放數(shù)據(jù)庫結(jié)構(gòu)

1）
show engines;
#查看數(shù)據(jù)庫服務(wù)支持的存儲引擎

2)
存儲引擎的特點
MyISAM：
表名.MYI  索引信息
表名.MYI  數(shù)據(jù)
表名.frm  表結(jié)構(gòu)
功能：
支持表級鎖:鎖一張表
不支持事務(wù)   事務(wù)回滾

InnoDB
表名.MYI  表結(jié)構(gòu)
表名.idb  數(shù)據(jù)+索引信息
功能：
支持行級鎖：只給當(dāng)前被訪問的行加鎖
支持事務(wù)   事務(wù)回滾

鎖類型：讀鎖、     寫鎖
select insert delete update

接收寫操作的表適合使用 InnoDB 存儲引擎
接收讀操作的表適合使用 MyISAM 存儲引擎

鎖粒度：表級鎖 行級鎖

事務(wù)：一次數(shù)據(jù)訪問從開始訪問到訪問結(jié)束的過程
事務(wù)回滾：一次數(shù)據(jù)訪問過程中任意一步操作錯誤，都會恢復(fù)所有操作
事務(wù)特性：一致性   原子性   隔離性

事務(wù)日志文件：記錄InnoDB存儲引擎的表執(zhí)行過的操作。

3)
在配置文件中寫入 /etc/my.cnf
default_storage_engine=存儲引擎名
#修改默認(rèn)引擎

alter table 表名 engine=存儲引擎名
#修改表引擎

create table 表名（。。。。）engine=innodb;
#設(shè)置表的存儲引擎

因當(dāng)在表中沒有存儲數(shù)據(jù)時設(shè)置。

###########################################################################################
MySQL 服務(wù)體系結(jié)構(gòu)：（8個功能模塊）
連接池
sql接口
分析器：分析命令語法
優(yōu)化器：以最小消耗執(zhí)行命令
查詢緩存：存放曾經(jīng)查找過的數(shù)據(jù)
存儲引擎
文件系統(tǒng)：硬盤
管理工具：裝包后提供的命令

看了以上關(guān)于MySQL 存儲引擎知識講解，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時售前售后，隨時幫您解答問題的。