MySQL常用存儲(chǔ)引擎

MySQL常用存儲(chǔ)引擎之MyISAM

MySQL5.5之前版本默認(rèn)存儲(chǔ)引擎

臨時(shí)表

在排序、分組等操作中，當(dāng)數(shù)量超過(guò)一定的大小之后，由查詢優(yōu)化器建立的臨時(shí)表。

MyISAM存儲(chǔ)引擎表由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ā)性與鎖級(jí)別（表級(jí)鎖）
  • 表?yè)p壞修復(fù)
    • check table tablename
    • repair table tablename
  • MyISAM表支持的索引類型
  • MyISAM表支持?jǐn)?shù)據(jù)壓縮
    命令行：myisampack
限制
  • 版本<MySQL5.0時(shí)默認(rèn)表大小為4G
    如存儲(chǔ)大表則修改MAX_Rows和AVG_ROW_LENGTH
  • 版本>MySQL5.0時(shí)默認(rèn)支持為256TB
適用場(chǎng)景：
  • 非事務(wù)型應(yīng)用
  • 只讀類應(yīng)用
  • 空間類應(yīng)用

MySQL常用存儲(chǔ)引擎之Innodb

MySQL5.5及之后版本默認(rèn)存儲(chǔ)引擎
Innodb使用表空間進(jìn)行 數(shù)據(jù)存儲(chǔ)
innodb_file_per_table
ON：獨(dú)立表空間：tablename.ibd
OFF：系統(tǒng)表空間：ibdataX

show variables like 'innodb_file_per_table';

set global innodb_file_per_table=off;

系統(tǒng)表空間和獨(dú)立表空間要如何選擇

比較：
  • 系統(tǒng)表空間無(wú)法簡(jiǎn)單的收縮文件大小
  • 獨(dú)立表空間可以通過(guò)optimize table命令收縮系統(tǒng)文件
  • 系統(tǒng)表空間會(huì)產(chǎn)生IO瓶頸
  • 獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)
建議：
  • 對(duì)Innodb使用獨(dú)立表空間（MySQL5.6之后默認(rèn)采用對(duì)表空間）

表轉(zhuǎn)移的步驟

把原來(lái)存在于系統(tǒng)表空間中的表轉(zhuǎn)移到獨(dú)立表空間中的方法

步驟：
1、使用mysqldump導(dǎo)出所有數(shù)據(jù)庫(kù)表數(shù)據(jù)
2、停止MySQL服務(wù)，修改參數(shù)，并刪除Innodb相關(guān)文件
3、重啟MySQL服務(wù)，重建Innodb系統(tǒng)表空間
4、重新導(dǎo)入數(shù)據(jù)

Innodb存儲(chǔ)引擎的特性（1）

系統(tǒng)表空間和獨(dú)立表空間要如何選擇

  • Innodb 數(shù)據(jù)字典信息
  • Undo 回滾段
Innodb存儲(chǔ)引擎的特性
  • Innodb是一種事務(wù)性存儲(chǔ)引擎
  • 完全支持事務(wù)的ACID特性
  • Redo Log 和 Undo Log
show variables like 'innodb_log_buffer_size';
show variables like 'innodb_log_files_in_group';
  • Innodb支持行級(jí)鎖
  • 行級(jí)鎖可以最大程度的支持并發(fā)
  • 行級(jí)鎖是由存儲(chǔ)引擎層實(shí)現(xiàn)的
什么是鎖
  • 鎖對(duì)主要作用是管理共享資源的并發(fā)訪問(wèn)
  • 鎖用于實(shí)現(xiàn)事務(wù)的隔離性
鎖的類型
  • 共享鎖（也稱讀鎖）
  • 獨(dú)占鎖（也稱寫(xiě)鎖）
寫(xiě)鎖 讀鎖
寫(xiě)鎖 不兼容 不兼容
讀鎖 不兼容 兼容
鎖的粒度
  • 表級(jí)鎖
lock table tablename write;
unlock tables;
  • 行級(jí)鎖
阻塞和死鎖
  • 什么是阻塞
    不同鎖的兼容性的關(guān)系，在有些時(shí)刻，一個(gè)事務(wù)中的鎖需要等到另一個(gè)事務(wù)中的鎖釋放他所占用的資源，這就形成了阻塞。
  • 什么是死鎖
    死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)在執(zhí)行過(guò)程中，相互占用著對(duì)方等待的資源而產(chǎn)生的異常。
Innodb狀態(tài)檢查
show engine innodb status;
適用場(chǎng)景
  • Innodb適合于大多數(shù)OLTP應(yīng)用

MySQL常用存儲(chǔ)引擎之CSV

文件系統(tǒng)存儲(chǔ)特點(diǎn)

數(shù)據(jù)以文本方式存儲(chǔ)在文件中
.CSV文件存儲(chǔ)表內(nèi)容
.CSM文件存儲(chǔ)表的元數(shù)據(jù)如表狀態(tài)和數(shù)據(jù)量
.frm文件存儲(chǔ)表結(jié)構(gòu)信息

特點(diǎn)
  • 以CSV格式進(jìn)行數(shù)據(jù)存儲(chǔ)
  • 所有列必須都是不能為NULL的
  • 不支持索引
    不適合大表，不適合在線處理
  • 可以對(duì)數(shù)據(jù)文件直接編輯
    保存文本文件內(nèi)容
適用場(chǎng)景

適合作為數(shù)據(jù)交換的中間表
電子表格->CSV文件->MySQL數(shù)據(jù)目錄
數(shù)據(jù)->CSV文件->其他Web程序

MySQL常用存儲(chǔ)引擎之Archive

文件系統(tǒng)存儲(chǔ)特點(diǎn)
  • 以zlib對(duì)表數(shù)據(jù)進(jìn)行壓縮，磁盤I/O更少
  • 數(shù)據(jù)存儲(chǔ)在ARZ為后綴的文件中
Archive存儲(chǔ)引擎的特點(diǎn)
  • 只支持insert 和select 操作
  • 只允許在自增ID列上加索引
使用場(chǎng)景

日志和數(shù)據(jù)采集器應(yīng)用

MySQL常用存儲(chǔ)引擎之Memory

文件系統(tǒng)存儲(chǔ)特點(diǎn)

也稱HEAP存儲(chǔ)引擎，所以數(shù)據(jù)保存在內(nèi)存中

功能特點(diǎn)
  • 支持HASH索引和BTree索引
  • 所有字段都為固定長(zhǎng)度 varchar(10)=char(10)
  • 不支持BLOG和TEXT等大字段
  • Memory存儲(chǔ)引擎使用表級(jí)鎖
  • 最大大小由max_heap_table_size參數(shù)決定
容易混淆的概念

Memory存儲(chǔ)引擎
VS
臨時(shí)表

  • 系統(tǒng)使用臨時(shí)表
    • 超過(guò)限制使用Myisam臨時(shí)表
      -未超限制使用Memory表
  • create temporary table 建立的臨時(shí)表
使用場(chǎng)景
  • 用于查找或者是映射表，例如郵編和地區(qū)的對(duì)應(yīng)表
  • 用于保存數(shù)據(jù)分析中產(chǎn)生的中間表
  • 用于緩存周期性聚合數(shù)據(jù)的結(jié)果表
    Memory數(shù)據(jù)易丟失，所以要求數(shù)據(jù)可再生

MySQL常用存儲(chǔ)引擎之Federated

特點(diǎn)
  • 提供了訪問(wèn)遠(yuǎn)程MySQL服務(wù)器上表的方法
  • 本地不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)全部放到遠(yuǎn)程服務(wù)器上
  • 本地需要保存表結(jié)構(gòu)和遠(yuǎn)程服務(wù)器的連接信息
如何使用

默認(rèn)禁止，啟用需要在啟動(dòng)時(shí)增加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;
使用場(chǎng)景

偶爾的統(tǒng)計(jì)分析及手工查詢

如何選擇存儲(chǔ)引擎

參考條件
  • 事務(wù)
  • 備份
  • 崩潰恢復(fù)
  • 存儲(chǔ)引擎的特有特性

不要混合使用存儲(chǔ)引擎

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容