一、存儲引擎簡介
- 1.1 操作系統組織和存取數據的一種機制。
- 1.2 文件系統是一種軟件。
2.文件系統類型：ext2 3 4 ，xfs 數據
- 2.1 不管使用什么文件系統，數據內容不會變化
- 2.2 不同的是，存儲空間、大小、速度。
- 可以理解為，MySQL的“文件系統”，只不過功能更加強大。
4.MySQL引擎功能：
- 除了可以提供基本的存取功能，還有更多功能事務功能、鎖定、備份和恢復、優化以及特殊功能
總之，存儲引擎的各項特性就是為了保障數據庫的安全和性能設計結構。
二.MySQL自帶的存儲引擎類型
MySQL 提供以下存儲引擎:
①InnoDB
②MyISAM
③MEMORY
④ARCHIVE
⑤FEDERATED
⑥EXAMPLE
⑦BLACKHOLE
⑧MERGE
⑨NDBCLUSTER
⑩CSV
還可以使用第三方存儲引擎:
①MySQL當中插件式的存儲引擎類型
②MySQL的兩個分支
③perconaDB
④mariaDB
#查看當前MySQL支持的存儲引擎類型 mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) #查看innodb的表有哪些 mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb'; +--------------+----------------------+--------+ | table_schema | table_name | engine | +--------------+----------------------+--------+ | mysql | innodb_index_stats | InnoDB | | mysql | innodb_table_stats | InnoDB | | mysql | slave_master_info | InnoDB | | mysql | slave_relay_log_info | InnoDB | | mysql | slave_worker_info | InnoDB | +--------------+----------------------+--------+ 20 rows in set (0.03 sec) #查看myisam的表有哪些 mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam'; +--------------------+---------------------------+--------+ | table_schema | table_name | engine | +--------------------+---------------------------+--------+ | information_schema | COLUMNS | MyISAM | | information_schema | EVENTS | MyISAM | | mysql | help_category | MyISAM | | mysql | ndb_binlog_index | MyISAM | +--------------------+---------------------------+--------+ 33 rows in set (0.01 sec)
物理上的區別：
#進入mysql目錄 [root@db01~l]# cd /application/mysql/data/mysql #myisam [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/ #innodb [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核心特性
重點:3.查看存儲引擎
MVCC
事務
行級鎖
熱備份
Crash Safe Recovery（自動故障恢復）
1）使用 SELECT 確認會話存儲引擎
#查詢默認存儲引擎 mysql> SELECT @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
2）使用 SHOW 確認每個表的存儲引擎
#查看表的存儲引擎
mysql> show create table city\G
*************************** 1. row ***************************
...
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show table status like 'city'\G
*************************** 1. row ***************************
Name: city
Engine: InnoDB
...
3）使用 INFORMATION_SCHEMA 確認每個表的存儲引擎
#查看表的存儲引擎
mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='world'\G
*************************** 1. row ***************************
table_name: city
engine: InnoDB
1 row in set (0.00 sec)
- 4.存儲引擎的設置
1）在啟動配置文件中設置服務器存儲引擎
#在配置文件的[mysqld]標簽下添加/etc/my.cnf [mysqld] default-storage-engine=innodb
2）使用 SET 命令為當前客戶機會話設置
#在MySQL命令行中臨時設置 mysql> SET @@storage_engine=myisam; Query OK, 0 rows affected, 1 warning (0.00 sec) #查看 mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | MyISAM | +--------------------------+ 1 row in set (0.00 sec)
（3）在 CREATE TABLE 語句指定
#建表的時候指定存儲引擎 create table t (i INT) engine = <Storage Engine>; #如：建test1表，指定存儲引擎為myisam mysql> create table test1(id int) engine=myisam; Query OK, 0 rows affected (0.02 sec)
三.真實企業案例
項目背景：
公司原有的架構：一個展示型的網站，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 test]# mysql -uroot -p1 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | student4 | | tlbb2 | +----------------+ 2 rows in set (0.00 sec) #測試環境（先簡單創幾個存儲引擎為myisam） mysql> create table test1(id int) engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> create table test2(id int) engine=myisam;; Query OK, 0 rows affected (0.00 sec) mysql> create table test3(id int) engine=myisam;; Query OK, 0 rows affected (0.01 sec)
2）準備一個5.6.44版本的新數據庫
#先導庫，準備一個環境 [root@db01 test]# mysqldump -uroot -p1 -B test >/tmp/full.sql
3）對備份數據進行處理（將engine字段替換）
#方法一 [root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql #方法二 [root@db01 ~]# vim /tmp/full.sql :%s#MyISAM#InnoDB#g
4）將修改后的備份恢復到新庫
#方法一 [root@db01 test]# mysql -uroot -p123 -h 10.0.0.52 < /tmp/full.sql
5）應用測試環境連接新庫，測試所有功能
#連接 [root@db02 ~]# mysql -uroot -p123 #查看存儲引擎 mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
6）停應用，將備份之后的生產庫發生的新變化，補償到新庫
7）應用割接到新數據庫
項目結果：
*解決了”小問題” *
四.Innodb存儲引擎——表空間介紹
5.5版本以后出現共享表空間概念
表空間的管理模式的出現是為了數據庫的存儲更容易擴展
5.6版本中默認的是獨立表空間
1、共享表空間1）查看共享表空間
#物理查看 [root@db01 ~]# ll /application/mysql/data/ -rw-rw---- 1 mysql mysql 79691776 Aug 14 16:23 ibdata1 #命令行查看 mysql> show variables like '%path%'; +-----------------------+------------------------------------+ | Variable_name | Value | +-----------------------+------------------------------------+ | innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend | | ssl_capath | | | ssl_crlpath | | +-----------------------+------------------------------------+ 3 rows in set (0.00 sec) #查看大小 [root@db01 data]# du -sh ibdata1 76M ibdata1
5.6版本中默認存儲:
①系統數據
②undo
③臨時表
5.7版本中默認會將undo和臨時表獨立出來，5.6版本也可以獨立，只不過需要在初始化的時候進行配置
2）設置方法
#編輯配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
對于用戶自主創建的表，會采用此種模式，每個表由一個獨立的表空間進行管理
查看獨立表空間
#物理查看 [root@db01 ~]# ll /application/mysql/data/world/ -rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd #命令行查看 mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.01 sec)
企業案例
在沒有備份數據的情況下，突然斷電導致表損壞，打不開數據庫。
1）拷貝庫目錄到新庫中（先準備一個和原表結構一樣的環境）
[root@db01 data]# tar zcf world1.tgz world/ #傳到測試環境 [root@db01 data]# scp world1.tgz 172.16.1.52:/application/mysql/data/ #解壓 [root@db02 data]# tar xf world1.tgz
2）啟動新數據庫
[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
3）登陸數據庫查看r
#查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | world | +--------------------+ 5 rows in set (0.00 sec)
4）查詢表中數據
mysql> select * from city; ERROR 1146 (42S02): Table 'world.city' doesn't exist （表不存在）
5）找到以前的表結構在新庫中創建表
mysql> show create table world.city; #刪掉外鍵創建語句 CREATE TABLE `city1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`) #CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
6）刪除表空間文件
#刪除表空間文件 mysql> alter table city1 discard tablespace; #在表的物理結構查看 [root@db02 world]# ll total 1000 -rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city1.frm -rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city.frm -rw-rw---- 1 mysql mysql 589824 Nov 4 10:26 city.ibd
7）拷貝舊表空間文件
[root@db02 world1]# cp city.ibd city1.ibd
8）授權
[root@db01 world]# chown -R mysql.mysql *
9）導入表空間
#查看，會報錯 mysql> select * from city1; #導入表空間 mysql> alter table city_new import tablespace; #再次查看 mysql> select * from city1;
改表名
#改表名 mysql> alter table city1 rename city; #再次查看 mysql> show tables; +------------------+ | Tables_in_world1 | +------------------+ | city | | country | | countrylanguage | +------------------+ 3 rows in set (0.00 sec)
掃碼二維碼 獲取免費視頻學習資料
- 本文固定鏈接: http://www.wangchenghua.com/post/7307/
- 轉載請注明：轉載必須在正文中標注并保留原文鏈接
- 掃碼： 掃上方二維碼獲取免費視頻資料
查 看2022高級編程視頻教程免費獲取