[[271516]]

一.存儲引擎簡介

• 1、文件系統(tǒng)：
• 1.1 操作系統(tǒng)組織和存取數(shù)據(jù)的一種機(jī)制。
• 1.2 文件系統(tǒng)是一種軟件。
• 2、文件系統(tǒng)類型：ext2 3 4 ，xfs 數(shù)據(jù)
• 2.1 不管使用什么文件系統(tǒng)，數(shù)據(jù)內(nèi)容不會變化
• 2.2 不同的是，存儲空間、大小、速度。
• 3、MySQL引擎：
• 3.1 可以理解為，MySQL的“文件系統(tǒng)”，只不過功能更加強(qiáng)大。
• 4、MySQL引擎功能：
• 4.1 除了可以提供基本的存取功能，還有更多功能事務(wù)功能、鎖定、備份和恢復(fù)、優(yōu)化以及特殊功能

總之，存儲引擎的各項(xiàng)特性就是為了保障數(shù)據(jù)庫的安全和性能設(shè)計(jì)結(jié)構(gòu)。

二.MySQL自帶的存儲引擎類型

MySQL 提供以下存儲引擎:

• 01)InnoDB
• 02)MyISAM
• 03)MEMORY
• 04)ARCHIVE
• 05)FEDERATED
• 06)EXAMPLE
• 07)BLACKHOLE
• 08)MERGE
• 09)NDBCLUSTER
• 10)CSV

還可以使用第三方存儲引擎:

• 01)MySQL當(dāng)中插件式的存儲引擎類型
• 02)MySQL的兩個(gè)分支
• 03)perconaDB
• 04)mariaDB

#查看當(dāng)前MySQL支持的存儲引擎類型 
mysql> show engines 
#查看innodb的表有哪些 
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb'; 
#查看myisam的表有哪些 
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam'; 

1、innodb和myisam的區(qū)別

物理上的區(qū)別：

#進(jìn)入mysql目錄 
[root@db01~l]# cd /application/mysql/data/mysql 
#查看所有user的文件 
[root@db01 mysql]# ll user.* 
-rw-rw---- 1 mysql mysql 10684 Mar 6 2017 user.frm 
-rw-rw---- 1 mysql mysql 960 Aug 14 01:15 user.MYD 
-rw-rw---- 1 mysql mysql 2048 Aug 14 01:15 user.MYI 
#進(jìn)入word目錄 
[root@db01 world]# cd /application/mysql/data/world/ 
#查看所有city的文件 
[root@db01 world]# ll city.* 
-rw-rw---- 1 mysql mysql 8710 Aug 14 16:23 city.frm 
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd 

2.innodb存儲引擎的簡介

在MySQL5.5版本之后，默認(rèn)的存儲引擎，提供高可靠性和高性能。

優(yōu)點(diǎn):

• 01)事務(wù)安全(遵從 ACID)
• 02)MVCC(Multi-Versioning Concurrency Control，多版本并發(fā)控制)
• 03)InnoDB 行級別鎖定
• 04)Oracle 樣式一致非鎖定讀取
• 05)表數(shù)據(jù)進(jìn)行整理來優(yōu)化基于主鍵的查詢
• 06)支持外鍵引用完整性約束
• 07)大型數(shù)據(jù)卷上的最大性能
• 08)將對表的查詢與不同存儲引擎混合
• 09)出現(xiàn)故障后快速自動恢復(fù)
• 10)用于在內(nèi)存中緩存數(shù)據(jù)和索引的緩沖區(qū)池

innodb核心特性

重點(diǎn):

• MVCC
• 事務(wù)
• 行級鎖
• 熱備份
• Crash Safe Recovery(自動故障恢復(fù))

3.查看存儲引擎

1)使用 SELECT 確認(rèn)會話存儲引擎

#查詢默認(rèn)存儲引擎 
SELECT @@default_storage_engine; 

2)使用 SHOW 確認(rèn)每個(gè)表的存儲引擎

#查看表的存儲引擎 
SHOW CREATE TABLE City\G 
SHOW TABLE STATUS LIKE 'CountryLanguage'\G 

3)使用 INFORMATION_SCHEMA 確認(rèn)每個(gè)表的存儲引擎

#查看表的存儲引擎 
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'City'AND TABLE_SCHEMA = 'world'\G 

4.存儲引擎的設(shè)置

1)在啟動配置文件中設(shè)置服務(wù)器存儲引擎

#在配置文件的[mysqld]標(biāo)簽下添加 
[mysqld] 
default-storage-engine=<Storage Engine> 

2)使用 SET 命令為當(dāng)前客戶機(jī)會話設(shè)置

#在MySQL命令行中臨時(shí)設(shè)置 
SET @@storage_engine=<Storage Engine> 

(3)在 CREATE TABLE 語句指定

#建表的時(shí)候指定存儲引擎 
CREATE TABLE t (i INT) ENGINE = <Storage Engine>; 

 三.真實(shí)企業(yè)案例

項(xiàng)目背景：

公司原有的架構(gòu)：一個(gè)展示型的網(wǎng)站，LAMT，MySQL5.1.77版本(MYISAM)，50M數(shù)據(jù)量。

小問題不斷：

1、表級鎖：對表中任意一行數(shù)據(jù)修改類操作時(shí)，整個(gè)表都會鎖定，對其他行的操作都不能同時(shí)進(jìn)行。

2、不支持故障自動恢復(fù)(CSR)：當(dāng)斷電時(shí)有可能會出現(xiàn)數(shù)據(jù)損壞或丟失的問題。

如何解決：

1、提建議將現(xiàn)有的MYISAM引擎替換為Innodb，將版本替換為5.6.38

1)如果使用MYISAM會產(chǎn)生”小問題”，性能安全不能得到保證，使用innodb可以解決這個(gè)問題。

2)5.1.77版本對于innodb引擎支持不夠完善，5.6.38版本對innodb支持非常完善了。

2、實(shí)施過程和注意要素

1)備份生產(chǎn)庫數(shù)據(jù)(mysqldump)

[root@db01 ~]# mysqldump -uroot -p123 -A --triggers -R --master-data=2 >/tmp/full.sql 

2)準(zhǔn)備一個(gè)5.6.38版本的新數(shù)據(jù)庫

3)對備份數(shù)據(jù)進(jìn)行處理(將engine字段替換)

[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql 

4)將修改后的備份恢復(fù)到新庫

5)應(yīng)用測試環(huán)境連接新庫，測試所有功能

6)停應(yīng)用，將備份之后的生產(chǎn)庫發(fā)生的新變化，補(bǔ)償?shù)叫聨?/p>

7)應(yīng)用割接到新數(shù)據(jù)庫