mysql進(jìn)階系列：mysql存儲(chǔ)引擎

本文mysql實(shí)驗(yàn)版本 : 5.7.21

基礎(chǔ)架構(gòu)篇了解到執(zhí)行器執(zhí)行這個(gè)執(zhí)行計(jì)劃，通過調(diào)用存儲(chǔ)引擎的API來操作數(shù)據(jù)。

mysql提供了一系列存儲(chǔ)引擎的API，所有的存儲(chǔ)引擎都要符合API要求，因此可以實(shí)現(xiàn)這種插件式的存儲(chǔ)引擎，可以根據(jù)不同的需求選擇合適的存儲(chǔ)引擎（就像握推杠鈴一樣，可以按需選擇不同大小的杠鈴片，嗯對(duì)的）。

存儲(chǔ)引擎是針對(duì)表的而不是庫，對(duì)于同一個(gè)庫不同的表可以使用不同的存儲(chǔ)引擎。

常見的存儲(chǔ)引擎有 MyISAM，InnoDB，Memory

查看當(dāng)前數(shù)據(jù)支持的存儲(chǔ)引擎：

mysql-2-engines.png
  1. 在新建表的時(shí)候可以選擇存儲(chǔ)引擎
CREATE TABLE  'user'  (
 'id' bigint(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE = MyISAM

ENGINE = MyISAM 代表這個(gè)表的存儲(chǔ)引擎是MyISAM 。

  1. 查看表相關(guān)信息，例如mysql庫中的user表
  • 使用show table status 查看表信息(不限版本)

需要先切換到對(duì)應(yīng)的數(shù)據(jù)庫下再執(zhí)行此命令

mysql> use mysql;
Database changed
mysql> show table status like 'user' \G ;
*************************** 1. row ***************************
 Name: user
 Engine: MyISAM
 Version: 10
 Row_format: Dynamic
 Rows: 3
 Avg_row_length: 128
 Data_length: 384
Max_data_length: 281474976710655
 Index_length: 4096
 Data_free: 0
 Auto_increment: NULL
 Create_time: 2018-06-11 09:51:16
 Update_time: 2018-06-11 09:53:08
 Check_time: NULL
 Collation: utf8_bin
 Checksum: NULL
 Create_options: 
 Comment: Users and global privileges
1 row in set (0.00 sec)
  • 還可以使用information_schema查看表信息(mysql5.0以后的版本支持)

會(huì)查出實(shí)例中所有庫中的表信息，但是可以指定TABLE_SCHEMA查詢指定庫的表

mysql> select * from information_schema.tables where table_name = 'user' and TABLE_SCHEMA='mysql' \G;

簡單介紹小輸出字段的含義：

Name: 表名。

Engine: 存儲(chǔ)引擎。

Version：版本，默認(rèn)10。

Row_format: 行的格式。

Rows: 表中的行數(shù)，對(duì)應(yīng)MyISAM和其他一些存儲(chǔ)引擎，該值是精確的; 而InnoDB該值是估計(jì)的。

Avg_row_length: 平均每行包含的字節(jié)數(shù)。

Data_length: 表數(shù)據(jù)的大小(字節(jié))。

Max_data_length: 表數(shù)據(jù)的最大容量(和存儲(chǔ)引擎有關(guān))。

Index_length: 索引的大小(字節(jié))。

Data_free: 對(duì)于MyISAM表，表示已經(jīng)分配但是沒有使用的空間。

Auto_increment: 下一個(gè)auto_increment值。

Create_time: 表的創(chuàng)建時(shí)間。

Update_time: 表數(shù)據(jù)最后修改時(shí)間。

Check_time: 使用check table命令或者myisamchk工具最后一次檢查表的時(shí)間。

Collation: 表的默認(rèn)字符集和字符列排序規(guī)則。

Checksum: 如果啟用保存的是整個(gè)表的實(shí)時(shí)校驗(yàn)和。

Create_options: 創(chuàng)建表是指定的其他選項(xiàng)。

Comment: 包含其他額外信息

1. InnoDB

InnoDB是mysql5.5.x開始默認(rèn)的事務(wù)型引擎，也是使用最廣泛的存儲(chǔ)引擎。被設(shè)計(jì)用來處理大量短期事務(wù)的。

InnoDB所有的表都保存在同一個(gè)數(shù)據(jù)文件中（也可能是多個(gè)文件，或者是獨(dú)立的表空間文件），表的大小只受限于操作系統(tǒng)文件的大小。表的結(jié)構(gòu)定義存在.frm后綴文件中，數(shù)據(jù)和索引集中存放在.idb后綴文件中。因?yàn)楸頂?shù)據(jù)和索引是在同一個(gè)文件，InnoDB的索引是聚簇索引。

InnoDB采用MVCC支持高并發(fā)，并且實(shí)現(xiàn)了四種標(biāo)準(zhǔn)的隔離級(jí)別(讀未提交，讀已提交，可重復(fù)讀，可串行化)，其默認(rèn)級(jí)別是REPEATABLE-READ(可重復(fù)讀)，并且通過間隙鎖(next-key locking)策略防止幻讀的出現(xiàn)。間隙鎖不僅僅鎖定查詢涉及的行，還會(huì)對(duì)索引中的間隙行進(jìn)行鎖定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的，聚簇索引對(duì)主鍵的查詢有很高的性能。但是InnoDB的非主鍵索引中必須包含主鍵列，所以如果主鍵列很大的話，非主鍵索引也會(huì)很大。如果一張表的索引較多，主鍵應(yīng)該盡可能的小。關(guān)于索引，后面會(huì)詳細(xì)講解。

InnoDB的內(nèi)部優(yōu)化，包括磁盤預(yù)讀(從磁盤讀取數(shù)據(jù)時(shí)采用可預(yù)測性讀取)，自適應(yīng)哈希(自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作)以及能夠加速插入操作的插入緩沖區(qū)。

2. MyISAM

在mysql5.1及之前的版本，MyISAM是默認(rèn)的存儲(chǔ)引擎。提供了大量的特性，包括全文索引，壓縮，空間函數(shù)等，但是不支持事務(wù)和行級(jí)鎖，而且有一個(gè)嚴(yán)重的問題是奔潰后無法安全恢復(fù)。

MyISAM的數(shù)據(jù)表存儲(chǔ)在磁盤上是3個(gè)文件，表結(jié)構(gòu)定義存在.frm后綴文件中，表數(shù)據(jù)存儲(chǔ)在.MYD后綴文件中，表索引存儲(chǔ)在.MYI后綴文件中。表數(shù)據(jù)和表索引在不同的文件中，所以MyISAM索引是非聚簇索引。而且MyISAM可以存儲(chǔ)表數(shù)據(jù)的總行數(shù)。

MyISAM表支持?jǐn)?shù)據(jù)壓縮，對(duì)于表創(chuàng)建后并導(dǎo)入數(shù)據(jù)以后，不需要修改操作，可以采用MyISAM壓縮表。壓縮命令：myisampack，壓縮表可以極大的減少磁盤空間占用，因此也可以減少磁盤I/O，提高查詢性能。而且壓縮表中的數(shù)據(jù)是單行壓縮，所以單行讀取是不需要解壓整個(gè)表。

3. Memory

Memory存儲(chǔ)引擎的數(shù)據(jù)是存放在內(nèi)存中的，所以如果服務(wù)器重啟會(huì)導(dǎo)致數(shù)據(jù)丟失，但是表結(jié)構(gòu)還是存在的表結(jié)構(gòu)是以 .frm 后綴的文件中。

Memory默認(rèn)hash索引，因此查詢非?？?。Memory表是表級(jí)鎖，因此并發(fā)寫入的性能較低。不支持BLOB或TEXT類型的列，并且每行的長度都是固定的，所以即使指定了varchar列實(shí)際存儲(chǔ)也會(huì)轉(zhuǎn)換成char，會(huì)導(dǎo)致內(nèi)存浪費(fèi)。

如果mysql查詢過程中需要使用臨時(shí)表來保存中間結(jié)果，內(nèi)部使用的臨時(shí)表就是Memory表，如果中間結(jié)果太大超出Memory表的限制或者含有BLOB或TEXT字段，那么臨時(shí)表會(huì)轉(zhuǎn)換成MyISAM表。

上面介紹了三種，你如何選擇存儲(chǔ)引擎呢：

  • 事務(wù) ：目前只有Innodb能完美的支持事務(wù)。

  • 備份 ：只有Innodb有免費(fèi)的在線熱備方案，mysqldump不算在線熱備的方案，它需要對(duì)數(shù)據(jù)加鎖。

  • 崩潰恢復(fù)：myisam表由于系統(tǒng)崩潰導(dǎo)致數(shù)據(jù)損壞的概率比Innodb高跟很多，而且恢復(fù)速度也沒有innodb快。

  • 特有的特性：如需要聚簇索引，那就需要選擇innodb存儲(chǔ)引擎，有的需要使用地理空間搜索，那就選擇myisam 。

mysql的存儲(chǔ)引擎有很多，這里主要介紹了以上3中，其中InnoDB是現(xiàn)在使用最廣泛也是默認(rèn)的存儲(chǔ)引擎，如果沒有特殊需求使用默認(rèn)的即可，也就是InnoDB。后面有文章詳解InnoDB.

歡迎關(guān)注公眾號(hào)：紀(jì)先生筆記

?著作權(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)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

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

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