數據庫：MySQL的存儲引擎到底是怎么回事？
一.存儲引擎簡介
- 1、文件系統：
- 1.1 操作系統組織和存取數據的一種機制。
- 1.2 文件系統是一種軟件。
- 2、文件系統類型：ext2 3 4 ，xfs 數據
- 2.1 不管使用什么文件系統，數據內容不會變化
- 2.2 不同的是，存儲空間、大小、速度。
- 3、MySQL引擎：
- 3.1 可以理解為，MySQL的“文件系統”，只不過功能更加強大。
- 4、MySQL引擎功能：
- 4.1 除了可以提供基本的存取功能，還有更多功能事務功能、鎖定、備份和恢復、優化以及特殊功能
總之，存儲引擎的各項特性就是為了保障數據庫的安全和性能設計結構。
二.MySQL自帶的存儲引擎類型
MySQL 提供以下存儲引擎:
- 01)InnoDB
- 02)MyISAM
- 03)MEMORY
- 04)ARCHIVE
- 05)FEDERATED
- 06)EXAMPLE
- 07)BLACKHOLE
- 08)MERGE
- 09)NDBCLUSTER
- 10)CSV
還可以使用第三方存儲引擎:
- 01)MySQL當中插件式的存儲引擎類型
- 02)MySQL的兩個分支
- 03)perconaDB
- 04)mariaDB
- #查看當前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的區別
物理上的區別：
- #進入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
- #進入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版本之后，默認的存儲引擎，提供高可靠性和高性能。
優點:
- 01)事務安全(遵從 ACID)
- 02)MVCC(Multi-Versioning Concurrency Control，多版本并發控制)
- 03)InnoDB 行級別鎖定
- 04)Oracle 樣式一致非鎖定讀取
- 05)表數據進行整理來優化基于主鍵的查詢
- 06)支持外鍵引用完整性約束
- 07)大型數據卷上的最大性能
- 08)將對表的查詢與不同存儲引擎混合
- 09)出現故障后快速自動恢復
- 10)用于在內存中緩存數據和索引的緩沖區池
innodb核心特性
重點:
- MVCC
- 事務
- 行級鎖
- 熱備份
- Crash Safe Recovery(自動故障恢復)
3.查看存儲引擎
1)使用 SELECT 確認會話存儲引擎
- #查詢默認存儲引擎
- SELECT @@default_storage_engine;
2)使用 SHOW 確認每個表的存儲引擎
- #查看表的存儲引擎
- SHOW CREATE TABLE City\G
- SHOW TABLE STATUS LIKE 'CountryLanguage'\G
3)使用 INFORMATION_SCHEMA 確認每個表的存儲引擎
- #查看表的存儲引擎
- SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'City'AND TABLE_SCHEMA = 'world'\G
4.存儲引擎的設置
1)在啟動配置文件中設置服務器存儲引擎
- #在配置文件的[mysqld]標簽下添加
- [mysqld]
- default-storage-engine=<Storage Engine>
2)使用 SET 命令為當前客戶機會話設置
- #在MySQL命令行中臨時設置
- SET @@storage_engine=<Storage Engine>
(3)在 CREATE TABLE 語句指定
- #建表的時候指定存儲引擎
- CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
三.真實企業案例
項目背景：
公司原有的架構：一個展示型的網站，LAMT，MySQL5.1.77版本(MYISAM)，50M數據量。
小問題不斷：
1、表級鎖：對表中任意一行數據修改類操作時，整個表都會鎖定，對其他行的操作都不能同時進行。
2、不支持故障自動恢復(CSR)：當斷電時有可能會出現數據損壞或丟失的問題。
如何解決：
1、提建議將現有的MYISAM引擎替換為Innodb，將版本替換為5.6.38
1)如果使用MYISAM會產生”小問題”，性能安全不能得到保證，使用innodb可以解決這個問題。
2)5.1.77版本對于innodb引擎支持不夠完善，5.6.38版本對innodb支持非常完善了。
2、實施過程和注意要素
1)備份生產庫數據(mysqldump)
- [root@db01 ~]# mysqldump -uroot -p123 -A --triggers -R --master-data=2 >/tmp/full.sql
2)準備一個5.6.38版本的新數據庫
3)對備份數據進行處理(將engine字段替換)
- [root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql
4)將修改后的備份恢復到新庫
5)應用測試環境連接新庫，測試所有功能
6)停應用，將備份之后的生產庫發生的新變化，補償到新庫
7)應用割接到新數據庫
