mysql
一、MySQL 之 InnoDB 總體介紹
InnoDB 是 MySQL 中一個(gè)平衡了 高可用 和 搞性能 的通用存儲(chǔ)引擎鳄逾。在 MySQL 8.0 中稻轨，除非通過 my.cnf 配置了默認(rèn)的存儲(chǔ)引擎，或者在 CREATE TABLE 語(yǔ)句中顯式的通過 ENGINE=xxx 指定了別的存儲(chǔ)引擎雕凹，InnoDB 將作為 MySQL 的默認(rèn)存儲(chǔ)引擎殴俱。
1. InnoDB 的明顯優(yōu)勢(shì)
- 在
InnoDB的事務(wù)中，DML(Data Manipulation Language)滿足事務(wù)的 ACID 原則枚抵，支持事務(wù)的提交(commit)和回滾(rollback)功能线欲，同時(shí)基于災(zāi)難恢復(fù)(crash-recovery)的能力確保了用戶數(shù)據(jù)的安全性。 - 更小粒度的
行鎖和 Oracle 風(fēng)格的一致性讀(consistent read)保證了多用戶訪問時(shí)的并發(fā)能力汽摹，提高了整體性能李丰。 -
InnoDB在磁盤上的數(shù)據(jù)存儲(chǔ)方式，對(duì)主鍵(primary keys)查詢做了優(yōu)化逼泣。在每一個(gè)包含主鍵索引趴泌，準(zhǔn)確地說是聚簇索引(clustered index)，的InnoDB表中拉庶，聚簇索引以一種使得主鍵查找 IO 次數(shù)最少的方式在磁盤上存放數(shù)據(jù)嗜憔。 -
InnoDB支持外鍵(foreign key)限制，進(jìn)一步保證了數(shù)據(jù)的完整性(integrity)砍的。外鍵限制痹筛，在增、刪廓鞠、改時(shí)會(huì)驗(yàn)證多張表中的數(shù)據(jù)關(guān)聯(lián)帚稠，能夠避免不一致的數(shù)據(jù)產(chǎn)生。
2. InnoDB 具體特性
| 特性 | 是否支持 |
|---|---|
B-Tree 索引 |
支持 |
server層的備份和事實(shí)恢復(fù) (point-in-time recovery)
|
支持 |
| 數(shù)據(jù)庫(kù)集群 | 不支持 |
聚簇索引(clustered index)
|
支持 |
| 壓縮數(shù)據(jù) | 支持 |
| 數(shù)據(jù)緩存 | 支持 |
| server層的數(shù)據(jù)加密 | 支持 |
| 外鍵限制 | 支持 |
全文索引 (fulltext index)
|
5.6后支持 |
空間索引 (Geospatial index)
|
5.7后支持 |
| Hash索引 | 不支持 |
| 索引緩存 | 支持 |
| 鎖粒度 | 行 |
MVCC(Multi-Version Concurrency Controll)多版本并發(fā)控制 |
支持 |
server層的復(fù)制(replication)
|
支持 |
| 存儲(chǔ)限制 | 64TB |
T-Tree 索引 |
不支持 |
| 事務(wù) | 支持 |
| 數(shù)據(jù)字典的更新統(tǒng)計(jì) | 支持 |
二床佳、表使用 InnoDB 的好處
表使用 InnoDB 存儲(chǔ)引擎的好處有：
- 不管服務(wù)器由于軟件滋早、硬件問題宕機(jī)，無論當(dāng)時(shí) MySQL 上正在執(zhí)行什么任務(wù)砌们，你只需要重新啟動(dòng) MySQL杆麸，
InnoDB的災(zāi)難恢復(fù)(crash-recovery)能力會(huì)自動(dòng)幫你：把宕機(jī)時(shí)已提交的變更恢復(fù)回來搁进，把未提交的變更舍棄掉，繼續(xù)后續(xù)的工作昔头，只需要重新啟動(dòng)就可以了饼问。 -
InnoDB會(huì)維護(hù)一個(gè)自己的內(nèi)存緩存buffer pool，把訪問過的數(shù)據(jù)和索引緩存到內(nèi)存中揭斧，而經(jīng)常訪問的數(shù)據(jù)是直接從內(nèi)存中獲取的莱革。這個(gè)內(nèi)存buffer pool緩存緩存了很多數(shù)據(jù)，進(jìn)一步提升InnoDB的處理速度讹开。在專門做數(shù)據(jù)庫(kù)的服務(wù)器上盅视，甚至經(jīng)常會(huì)將80%的內(nèi)存分配給這個(gè)buffer pool。 - 如果你將相關(guān)的數(shù)據(jù)分開存儲(chǔ)到了多張表中旦万，
InnoDB中的外鍵(foreign key)限制將使你能保證數(shù)據(jù)的完整性(integrity)闹击。- 更新或刪除主表中的數(shù)據(jù)，子表的更新和刪除是由外鍵限制自動(dòng)完成的成艘。
- 當(dāng)直接插入子表時(shí)赏半，外鍵限制會(huì)自動(dòng)檢查主表的主鍵是否存在，直接剔除不完整的插入淆两。
- 當(dāng)磁盤除破、內(nèi)存中的數(shù)據(jù)被破壞時(shí)，
InnoDB的checksum機(jī)制琼腔，會(huì)提醒你數(shù)據(jù)的不完整性瑰枫。 - 當(dāng)你在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，如果給每張表都設(shè)置了一個(gè)合適的主鍵
(primary keys)丹莲，那么涉及到這些主鍵(primary keys)的操作都會(huì)自動(dòng)優(yōu)化光坝，對(duì)于使用了主鍵(primary keys)的whereorder bygroup byjoin都將非常快甥材。 -
change buffering機(jī)制會(huì)自動(dòng)優(yōu)化插入盯另、更新、刪除操作洲赵。InnoDB不僅允許對(duì)同一個(gè)表進(jìn)行并發(fā)讀寫訪問鸳惯，還緩存更改后的數(shù)據(jù)以優(yōu)化磁盤I/O。 - 性能的提升不僅僅是支持大表的重查詢
(long-running queries)叠萍，當(dāng)頻繁的從一個(gè)表獲取相同數(shù)據(jù)時(shí)芝发，自適應(yīng) hash 索引(Adaptive Hash Index)將會(huì)緩存這樣的查詢結(jié)果，使得查詢非晨凉龋快辅鲸，就像使用了 hash 索引的表一樣。 - 支持多表和索引的壓縮
- 使得創(chuàng)建腹殿、銷毀索引對(duì)性能和可用性上的影響變得很小
- 對(duì)于清空一個(gè)單文件表空間
(file-per-table tablespace)來說独悴，變得更快例书。使得磁盤空間釋放出來給操作系統(tǒng)用，而不是僅僅從 MySQL 的InnoDB表空間中釋放出來刻炒，給InnoDB表用决采。 - 使用了動(dòng)態(tài)行結(jié)構(gòu)
(dynamic row format)，這讓InnoDB對(duì)blob和text字段的存儲(chǔ)和訪問上更高效坟奥。 - 可以通過查詢
INFORMATION_SCHEMA表织狐，對(duì)InnoDB內(nèi)部的工作進(jìn)行監(jiān)控。 - 可以通過查詢
Performance Schema表筏勒，對(duì)InnoDB的性能細(xì)節(jié)進(jìn)行監(jiān)控。 - 可以自由地在一個(gè)語(yǔ)句中混合
(mix)使用InnoDB和其他存儲(chǔ)引擎旺嬉。例如：可以在一條語(yǔ)句中使用join合并InnoDB和memory存儲(chǔ)引擎的數(shù)據(jù)管行。 -
InnoDB在處理大量數(shù)據(jù)的 CPU 運(yùn)算上有很好的性能。 - 即使在一個(gè)文件最大 2GB 的操作系統(tǒng)上邪媳，
InnoDB的表可以存儲(chǔ)大量數(shù)據(jù)捐顷。
三、InnoDB 表的最佳實(shí)踐
InnoDB 表的最佳實(shí)踐包含：
- 為每個(gè)存在大量查詢的表指定一個(gè)主鍵
(primary keys)雨效，或者選擇一個(gè)自增類型的字段作為主鍵(primary keys)迅涮。 - 基于每個(gè)表的 id 字段使用
join操作。出于性能考慮徽龟，在 join 的字段上增加外鍵(foreign key)限制叮姑，并且將每個(gè)表中的這個(gè)字段設(shè)置為同樣的數(shù)據(jù)類型。增加外鍵(foreign key)限制保證了不同表中的關(guān)聯(lián)字段可以使用索引据悔，同時(shí)使得刪除和更新這些字段能影響到所有（有外鍵(foreign key)限制的）表传透，還能保證子表和主表的數(shù)據(jù)完整性瞒渠。 - 關(guān)閉事務(wù)的自動(dòng)提交
(autocommit)玖详。每秒鐘提交數(shù)百個(gè)事務(wù)將限制InnoDB的性能抖韩。 - 將大量的 DML
(Data Manipulation Language)語(yǔ)句傻咖，分組放到多個(gè)事務(wù)中纺棺，使用start transaction和commit包起來获黔。雖然你不想太過頻繁的提交事務(wù)拒逮，但你肯定也不想看到硕噩，運(yùn)行了幾個(gè)小時(shí)的insert語(yǔ)句骇径，最后沒有提交躯肌。 - 不要使用
lock table語(yǔ)句。InnoDB在不犧牲可用性和高性能的前提下破衔，支持多個(gè)會(huì)話(session)同時(shí)對(duì)同一個(gè)表的并發(fā)讀寫羡榴。為了排他性(exclusive)地訪問一些行的寫入操作，可以使用select ... for update語(yǔ)句运敢，單獨(dú)只鎖定你需要的行校仑。 - 開啟
innodb_file_per_table選項(xiàng)或者使用普通表空間(general tablespace)忠售，使得每張表的數(shù)據(jù)和索引分開存儲(chǔ)在不同的文件中，而不是使用系統(tǒng)表空間(system tablespace)迄沫。innodb_file_per_table默認(rèn)是開啟的稻扬。 - 評(píng)估是否你的數(shù)據(jù)、訪問方式能從
InnoDB的表羊瘩、頁(yè)(page)的壓縮功能中獲益泰佳。InnoDB在不犧牲讀寫能力的前提下，支持壓縮功能尘吗。 - 如果在
create table時(shí)使用engine=xxx有問題的話逝她，可以在啟動(dòng) MySQL 服務(wù)時(shí)，通過--sql_mode=NO_ENGINE_SUBSTITUTION選項(xiàng)禁止創(chuàng)建其他存儲(chǔ)引擎的表睬捶。
四黔宛、確認(rèn) InnoDB 是否是默認(rèn)的存儲(chǔ)引擎
可以通過 show engines; 或者 SELECT * FROM INFORMATION_SCHEMA.ENGINES; 來查看 InnoDB 是否是默認(rèn)的存儲(chǔ)引擎。注意 InnoDB 那行的 default 字樣擒贸。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> select * from INFORMATION_SCHEMA.ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
五臀晃、找個(gè)表測(cè)試一下 InnoDB
- 如果你沒有把
InnoDB作為默認(rèn)的存儲(chǔ)引擎，可以通過啟動(dòng)參數(shù)中增加--default-storage-engine=InnoDB或者在my.cnf的[mysqld]下增加default-storage-engine=innodb的方式介劫，來檢驗(yàn)下你的數(shù)據(jù)服務(wù)徽惋、應(yīng)用是否正常。 - 如果你的應(yīng)用有依賴其他存儲(chǔ)引擎的某個(gè)特性座韵，將得到一個(gè)
add the ENGINE=other_engine_name clause to the CREATE TABLE statement to avoid the error.的錯(cuò)誤险绘。 - 如果你想看一下某個(gè)表在
InnoDB存儲(chǔ)引擎下的具體表，可以通過ALTER TABLE table_name ENGINE=InnoDB;的方式修改指定表使用的存儲(chǔ)引擎誉碴。 - 如果你不想破壞原表隆圆、或者影響正在運(yùn)行的應(yīng)用，可以通過
CREATE TABLE table_xxx_innodb (...) ENGINE=InnoDB AS SELECT * FROM table_xxx;的方式翔烁，制作一個(gè)備份表渺氧。 - 當(dāng)你的數(shù)據(jù)服務(wù)是一個(gè)集群時(shí)，要特別注意
master和slaves的版本差異蹬屹。
