MySQL 存儲引擎知識講解

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

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

查看索引
desc 表名；

show index for 表名；
#查看索引詳細信息

創建索引
create table 表名(
字段 類型，
index(索引名)；
#建表時創建索引
#默認和字段名同名

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

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

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

create index name on t3(name)

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

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

使用規則
一個表中只能有一個 parimary key 字段
對應的字段值不允許有重復，且不允許賦值
如果有多個字段都作為 parimary key ，稱為復合主鍵，必須一個創建。
主健字段的KEY標志死PRI
通常與 auto_increment 連用
經常把表中能夠唯一標識記錄的字段位主鍵
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));
#創建表時添加主鍵

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

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

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

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

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

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

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

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

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字段
對應的字段值不允許有重復
unique字段的key標志是uni
unique字段的值允許為null，當將其修改為不允許為null，則此字段限制與主鍵相同

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

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

練習：
參考表
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寫入數據
+----------+----------+----------------+
| num  | name | class   |
+----------+-----------+---------------+
|    1 | bob  | nsd1709 |
|    2 | lucy | nsd1709 |
+----------+----------+----------------+

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

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

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

delete from xsb where name='jerry';
#刪除重復數據

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

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的數據

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 數據庫服務軟件自帶的程序。
不同的存儲引擎有不同的功能和數據存儲方式
是處理表的處理器
表名.frm 存放數據庫結構

1）
show engines;
#查看數據庫服務支持的存儲引擎

2)
存儲引擎的特點
MyISAM：
表名.MYI  索引信息
表名.MYI  數據
表名.frm  表結構
功能：
支持表級鎖:鎖一張表
不支持事務   事務回滾

InnoDB
表名.MYI  表結構
表名.idb  數據+索引信息
功能：
支持行級鎖：只給當前被訪問的行加鎖
支持事務   事務回滾

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

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

鎖粒度：表級鎖 行級鎖

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

事務日志文件：記錄InnoDB存儲引擎的表執行過的操作。

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

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

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

因當在表中沒有存儲數據時設置。

###########################################################################################
MySQL 服務體系結構：（8個功能模塊）
連接池
sql接口
分析器：分析命令語法
優化器：以最小消耗執行命令
查詢緩存：存放曾經查找過的數據
存儲引擎
文件系統：硬盤
管理工具：裝包后提供的命令

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