MySQL常用存儲引擎

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表
  • 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ù)
  • 存儲引擎的特有特性

不要混合使用存儲引擎

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末魏铅,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子坚芜,更是在濱河造成了極大的恐慌览芳,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,042評論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件鸿竖,死亡現(xiàn)場離奇詭異沧竟,居然都是意外死亡铸敏,警方通過查閱死者的電腦和手機,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,996評論 2 384
  • 文/潘曉璐 我一進店門屯仗,熙熙樓的掌柜王于貴愁眉苦臉地迎上來搞坝,“玉大人,你說我怎么就攤上這事魁袜∽椋” “怎么了?”我有些...
    開封第一講書人閱讀 156,674評論 0 345
  • 文/不壞的土叔 我叫張陵峰弹,是天一觀的道長店量。 經(jīng)常有香客問我,道長鞠呈,這世上最難降的妖魔是什么融师? 我笑而不...
    開封第一講書人閱讀 56,340評論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮蚁吝,結(jié)果婚禮上旱爆,老公的妹妹穿的比我還像新娘。我一直安慰自己窘茁,他們只是感情好怀伦,可當(dāng)我...
    茶點故事閱讀 65,404評論 5 384
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著山林,像睡著了一般房待。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上驼抹,一...
    開封第一講書人閱讀 49,749評論 1 289
  • 那天桑孩,我揣著相機與錄音,去河邊找鬼框冀。 笑死流椒,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的明也。 我是一名探鬼主播镣隶,決...
    沈念sama閱讀 38,902評論 3 405
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼诡右!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起轻猖,我...
    開封第一講書人閱讀 37,662評論 0 266
  • 序言:老撾萬榮一對情侶失蹤帆吻,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后咙边,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體猜煮,經(jīng)...
    沈念sama閱讀 44,110評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡次员,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,451評論 2 325
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了王带。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片淑蔚。...
    茶點故事閱讀 38,577評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖愕撰,靈堂內(nèi)的尸體忽然破棺而出刹衫,到底是詐尸還是另有隱情,我是刑警寧澤搞挣,帶...
    沈念sama閱讀 34,258評論 4 328
  • 正文 年R本政府宣布带迟,位于F島的核電站,受9級特大地震影響囱桨,放射性物質(zhì)發(fā)生泄漏仓犬。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,848評論 3 312
  • 文/蒙蒙 一舍肠、第九天 我趴在偏房一處隱蔽的房頂上張望搀继。 院中可真熱鬧,春花似錦翠语、人聲如沸叽躯。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,726評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽险毁。三九已至,卻和暖如春们童,著一層夾襖步出監(jiān)牢的瞬間畔况,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,952評論 1 264
  • 我被黑心中介騙來泰國打工慧库, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留跷跪,地道東北人。 一個月前我還...
    沈念sama閱讀 46,271評論 2 360
  • 正文 我出身青樓齐板,卻偏偏與公主長得像吵瞻,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子甘磨,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,452評論 2 348

推薦閱讀更多精彩內(nèi)容