MySQL常用存儲引擎之MyISAM
MySQL5.5之前版本默認存儲引擎
臨時表
在排序翻翩、分組等操作中，當(dāng)數(shù)量超過一定的大小之后慢宗，由查詢優(yōu)化器建立的臨時表耗啦。
MyISAM存儲引擎表由MYD和MYI組成
CREATE TABLE `myIsam`(
`id` int(11) DEFAULT NULL,
`c1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
ls -l
myIsam.frm
myIsam.MYD
myIsam.MYI
特性
- 并發(fā)性與鎖級別（表級鎖）
- 表損壞修復(fù)
- check table tablename
- repair table tablename
- MyISAM表支持的索引類型
- MyISAM表支持數(shù)據(jù)壓縮
命令行：myisampack
限制
- 版本<MySQL5.0時默認表大小為4G
如存儲大表則修改MAX_Rows和AVG_ROW_LENGTH - 版本>MySQL5.0時默認支持為256TB
適用場景：
- 非事務(wù)型應(yīng)用
- 只讀類應(yīng)用
- 空間類應(yīng)用
MySQL常用存儲引擎之Innodb
MySQL5.5及之后版本默認存儲引擎
Innodb使用表空間進行 數(shù)據(jù)存儲
innodb_file_per_table
ON：獨立表空間：tablename.ibd
OFF：系統(tǒng)表空間：ibdataX
show variables like 'innodb_file_per_table';
set global innodb_file_per_table=off;
系統(tǒng)表空間和獨立表空間要如何選擇
比較：
- 系統(tǒng)表空間無法簡單的收縮文件大小
- 獨立表空間可以通過optimize table命令收縮系統(tǒng)文件
- 系統(tǒng)表空間會產(chǎn)生IO瓶頸
- 獨立表空間可以同時向多個文件刷新數(shù)據(jù)
建議：
- 對Innodb使用獨立表空間（MySQL5.6之后默認采用對表空間）
表轉(zhuǎn)移的步驟
把原來存在于系統(tǒng)表空間中的表轉(zhuǎn)移到獨立表空間中的方法
步驟：
1、使用mysqldump導(dǎo)出所有數(shù)據(jù)庫表數(shù)據(jù)
2床绪、停止MySQL服務(wù)客情，修改參數(shù)，并刪除Innodb相關(guān)文件
3癞己、重啟MySQL服務(wù)膀斋，重建Innodb系統(tǒng)表空間
4、重新導(dǎo)入數(shù)據(jù)
Innodb存儲引擎的特性（1）
系統(tǒng)表空間和獨立表空間要如何選擇
- Innodb 數(shù)據(jù)字典信息
- Undo 回滾段
Innodb存儲引擎的特性
- Innodb是一種事務(wù)性存儲引擎
- 完全支持事務(wù)的ACID特性
- Redo Log 和 Undo Log
show variables like 'innodb_log_buffer_size';
show variables like 'innodb_log_files_in_group';
- Innodb支持行級鎖
- 行級鎖可以最大程度的支持并發(fā)
- 行級鎖是由存儲引擎層實現(xiàn)的
什么是鎖
- 鎖對主要作用是管理共享資源的并發(fā)訪問
- 鎖用于實現(xiàn)事務(wù)的隔離性
鎖的類型
- 共享鎖（也稱讀鎖）
- 獨占鎖（也稱寫鎖）
| 寫鎖 | 讀鎖 | |
|---|---|---|
| 寫鎖 | 不兼容 | 不兼容 |
| 讀鎖 | 不兼容 | 兼容 |
鎖的粒度
- 表級鎖
lock table tablename write;
unlock tables;
- 行級鎖
阻塞和死鎖
- 什么是阻塞
不同鎖的兼容性的關(guān)系痹雅，在有些時刻仰担，一個事務(wù)中的鎖需要等到另一個事務(wù)中的鎖釋放他所占用的資源，這就形成了阻塞绩社。 - 什么是死鎖
死鎖是指兩個或兩個以上的事務(wù)在執(zhí)行過程中摔蓝，相互占用著對方等待的資源而產(chǎn)生的異常。
Innodb狀態(tài)檢查
show engine innodb status;
適用場景
- Innodb適合于大多數(shù)OLTP應(yīng)用
MySQL常用存儲引擎之CSV
文件系統(tǒng)存儲特點
數(shù)據(jù)以文本方式存儲在文件中
.CSV文件存儲表內(nèi)容
.CSM文件存儲表的元數(shù)據(jù)如表狀態(tài)和數(shù)據(jù)量
.frm文件存儲表結(jié)構(gòu)信息
特點
- 以CSV格式進行數(shù)據(jù)存儲
- 所有列必須都是不能為NULL的
- 不支持索引
不適合大表愉耙，不適合在線處理 - 可以對數(shù)據(jù)文件直接編輯
保存文本文件內(nèi)容
適用場景
適合作為數(shù)據(jù)交換的中間表
電子表格->CSV文件->MySQL數(shù)據(jù)目錄
數(shù)據(jù)->CSV文件->其他Web程序
MySQL常用存儲引擎之Archive
文件系統(tǒng)存儲特點
- 以zlib對表數(shù)據(jù)進行壓縮贮尉，磁盤I/O更少
- 數(shù)據(jù)存儲在ARZ為后綴的文件中
Archive存儲引擎的特點
- 只支持insert 和select 操作
- 只允許在自增ID列上加索引
使用場景
日志和數(shù)據(jù)采集器應(yīng)用
MySQL常用存儲引擎之Memory
文件系統(tǒng)存儲特點
也稱HEAP存儲引擎，所以數(shù)據(jù)保存在內(nèi)存中
功能特點
- 支持HASH索引和BTree索引
- 所有字段都為固定長度 varchar(10)=char(10)
- 不支持BLOG和TEXT等大字段
- Memory存儲引擎使用表級鎖
- 最大大小由max_heap_table_size參數(shù)決定
容易混淆的概念
Memory存儲引擎
VS
臨時表
- 系統(tǒng)使用臨時表
- 超過限制使用Myisam臨時表
-未超限制使用Memory表
- 超過限制使用Myisam臨時表
- create temporary table 建立的臨時表
使用場景
- 用于查找或者是映射表朴沿，例如郵編和地區(qū)的對應(yīng)表
- 用于保存數(shù)據(jù)分析中產(chǎn)生的中間表
- 用于緩存周期性聚合數(shù)據(jù)的結(jié)果表
Memory數(shù)據(jù)易丟失猜谚，所以要求數(shù)據(jù)可再生
MySQL常用存儲引擎之Federated
特點
- 提供了訪問遠程MySQL服務(wù)器上表的方法
- 本地不存儲數(shù)據(jù)，數(shù)據(jù)全部放到遠程服務(wù)器上
- 本地需要保存表結(jié)構(gòu)和遠程服務(wù)器的連接信息
如何使用
默認禁止赌渣，啟用需要在啟動時增加federated參數(shù)
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
show engines;
federated=1
create database local;
create database remote;
use remote;
create table remote_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '',primery key(id))engine=innodb;
show create table remote_fed\G
insert into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('ddd','fff');
grant select,update,insert,delete on remote.remote_fed to fred_link@'127.0.0.1' identified by '123456';
use local;
create table remote_fed(id int(11) not null auto_increment, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id))engine=federated connection='mysql://fred_link:123456@127.0.0.1:3306/remote/remote_fed';
rename table remote_fed to local_fed;
select * from local.local_fed;
delete from local.local_fed where id=2;
use remote;
select * fro remote_fed;
使用場景
偶爾的統(tǒng)計分析及手工查詢
如何選擇存儲引擎
參考條件
- 事務(wù)
- 備份
- 崩潰恢復(fù)
- 存儲引擎的特有特性
不要混合使用存儲引擎
