MySQL存儲(chǔ)引擎怎么理解

這篇文章主要講解了“MySQL存儲(chǔ)引擎怎么理解”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL存儲(chǔ)引擎怎么理解”吧！

今天發(fā)現(xiàn)了一個(gè)神奇的參數(shù)：-site:xxxx.net

一、存儲(chǔ)引擎的選擇（表類型）

1、存儲(chǔ)引擎的介紹

與到多數(shù)關(guān)系型數(shù)據(jù)庫(kù)的區(qū)別在于MySQL有一個(gè)存儲(chǔ)引擎的概念，針對(duì)不同的存儲(chǔ)需求可以選擇最合適的存儲(chǔ)引擎。MySQL中的插件式的存儲(chǔ)引擎是其一大特色，用戶可以根據(jù)應(yīng)用的需求選擇如何存儲(chǔ)、是否索引，是否使用事務(wù)。嘿嘿，你也可以根據(jù)業(yè)務(wù)環(huán)境去適配最適合自己業(yè)務(wù)的存儲(chǔ)引擎。

Oracle從中嗅到了商機(jī)，收購(gòu)了MySQL，從此有了企業(yè)版（商業(yè)支持）。社區(qū)版依舊可以免費(fèi)下載。另一大魅力也是因?yàn)殚_(kāi)源，社區(qū)高度活躍，人人都可貢獻(xiàn)。接下來(lái)介紹幾種使用比較多的存儲(chǔ)引擎，存儲(chǔ)引擎并無(wú)優(yōu)劣之分，有的只是誰(shuí)更適合對(duì)應(yīng)的生產(chǎn)業(yè)務(wù)環(huán)境。

MySQL5.0中支持的存儲(chǔ)引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之后的默認(rèn)存儲(chǔ)引擎）、PERFORMANCE_SCHEMA（非常規(guī)存儲(chǔ)數(shù)據(jù)引擎）。下面給出MySQL與MariaDB支持的存儲(chǔ)器引擎的對(duì)比，可以看出MariaDB新增了Aria引擎：

查看存儲(chǔ)引擎

通過(guò)MySQL登錄自帶的字符界面輸入show engines\G;或者使用支持MySQL查詢的工具SQLyog、phpMyAdmin、MySQL workbench等查詢支持的引擎，這里只展示部分喲：

[test@cnwangk ~]$ mysql -uroot -p
Enter password: 
mysql> show engines\G;*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO
          XA: NO
  Savepoints: NO*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES     Comment: MyISAM storage engineTransactions: NO
          XA: NO
  Savepoints: NO*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO
          XA: NO
  Savepoints: NO*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES
          XA: YES
  Savepoints: YES9 rows in set (0.00 sec)

作用描述：

• Engine：引擎名稱（描述）；

• Support：當(dāng)前版本數(shù)據(jù)庫(kù)是否支持該存儲(chǔ)引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，個(gè)人字面上翻譯這段話：支持事務(wù)、行級(jí)別鎖和外鍵；

• Comment：對(duì)該存儲(chǔ)引擎的詳情描述，比如描述該引擎否支持事務(wù)和外鍵；

• Transactions：對(duì)該存儲(chǔ)引擎是否支持事務(wù)的描述，YES：支持、NO：不支持；

• XA：是否滿足XA規(guī)范。XA規(guī)范是開(kāi)放群組關(guān)于分布式事務(wù)處理(DTP)的規(guī)范。YES：支持、NO：不支持；

• Savepoints：字面意思是保存點(diǎn)，對(duì)事物控制是否支持，YES：支持、NO：不支持。

小聲嗶嗶，如果你能閱讀明白官方的一些英文文檔，這將有助于你對(duì)MySQL存儲(chǔ)引擎的進(jìn)一步理解，養(yǎng)成閱讀源碼或者文檔的能力。

順帶的提一下MySQL的妹妹MariaDB。在MySQL的復(fù)刻版本MariaDB中10.2之前使用的自帶的新引擎Aria，在MariaDB10.2之后使用的默認(rèn)存儲(chǔ)引擎也是InnoDB，足以看出InnoDB存儲(chǔ)引擎的優(yōu)秀之處。MariaDB的API和協(xié)議兼容MySQL，另外又添加了一些功能，以支持本地的非阻塞操作和進(jìn)度報(bào)告。這意味著，所有使用MySQL的連接器、程序庫(kù)和應(yīng)用程序也將可以在MariaDB下工作。在此基礎(chǔ)上，由于擔(dān)心甲骨文MySQL的一個(gè)更加封閉的軟件項(xiàng)目，F(xiàn)edora等Linux發(fā)行版已經(jīng)在最新版本中以MariaDB取代MySQL，維基媒體基金會(huì)的服務(wù)器同樣也使用MariaDB取代了MySQL。

主要需要了解的幾種存儲(chǔ)引擎：

• MyISAM

• InnoDB

• MEMORY

• MERGE

下面將著重介紹我最近看書(shū)認(rèn)識(shí)的幾種常用的存儲(chǔ)引擎，對(duì)比各個(gè)存儲(chǔ)引擎之間的區(qū)別，幫助我們理解不同存儲(chǔ)引擎的使用方式。更多詳情可以參考MySQL的官方文檔。

2、部分存儲(chǔ)引擎的特性

InnoDB存儲(chǔ)引擎在MySQL5.6版本開(kāi)始支持全文索引。在MySQL5.7推出了虛擬列，MySQL8.0新特性加入了函數(shù)索引支持。

2.1、MyISAM存儲(chǔ)引擎

MyISAM是MySQL5.5之前默認(rèn)的存儲(chǔ)引擎。MyISAM不支持事務(wù)、不支持外鍵。優(yōu)勢(shì)在于訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有特殊要求或者以select和insert為主的應(yīng)用基本上可以使用MyISAM作為存儲(chǔ)引擎創(chuàng)建表。我們先弄個(gè)例子出來(lái)演示，事先準(zhǔn)備了一張數(shù)據(jù)千萬(wàn)級(jí)別的表，看看這個(gè)存儲(chǔ)引擎的特性：

我已經(jīng)創(chuàng)建好了數(shù)據(jù)庫(kù)為test，在test中分別創(chuàng)建了兩張表test和tolove。test表在創(chuàng)建的時(shí)候指定默認(rèn)存儲(chǔ)引擎為MyISAM，tolove表指定存儲(chǔ)引擎為InnoDB。
使用MyISAM存儲(chǔ)引擎創(chuàng)建的表tolove，查詢存儲(chǔ)有1kw數(shù)據(jù)的表tolove。

tips：你可以使用use test，切換到test數(shù)據(jù)庫(kù)，就不用像我這樣查詢tolove表去指定test數(shù)據(jù)庫(kù)了喲！

MySQL [(none)]> select count(*) from test.tolove;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.000 sec)

再看演示使用InnoDB存儲(chǔ)引擎創(chuàng)建的表test，同樣為了演示，事先隨機(jī)生成了1kw條數(shù)據(jù)。

MySQL [(none)]> select count(*) from test.test;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.080 sec)

進(jìn)行對(duì)比同樣存儲(chǔ)1kw條數(shù)據(jù)的表，使用MyISAM作為存儲(chǔ)引擎查詢速度堪稱光速1 row in set (0.000 sec)，使用InnoDB存儲(chǔ)引擎查詢速度稍遜一籌1 row in set (3.080 sec)。

MyISAM在磁盤中存儲(chǔ)的文件：

每個(gè)MyISAM在磁盤上存儲(chǔ)成3個(gè)文件，其文件名和表名都相同，擴(kuò)展名分別是：

• .frm：存儲(chǔ)表定義；

• .MYD：MYData，存儲(chǔ)數(shù)據(jù)；

• .MYI：MYindex，存儲(chǔ)索引。

數(shù)據(jù)文件和索引文件可以存放在不同的目錄，平均分布IO，獲得更快的速度，提升性能。需要指定索引文件和數(shù)據(jù)文件存儲(chǔ)的路徑，創(chuàng)建表時(shí)通過(guò)DATA DIRECTORY和INDEX DIRECTORY參數(shù)指定，表明不同MyISAM表的索引文件和數(shù)據(jù)文件可以存放在不同的路徑下。當(dāng)然，需要給予該路徑的訪問(wèn)權(quán)限。

MyISAM損壞處理方式 ：

MyISAM類型的表可能會(huì)損壞，原因多種多樣。損壞后的表有可能不能被訪問(wèn)，會(huì)提示需要修復(fù)或者訪問(wèn)后提示返回錯(cuò)誤結(jié)果。MyISAM類型的表，可以通過(guò)提供的修復(fù)工具執(zhí)行CHECK TABLE語(yǔ)句檢查MyISAM表的健康程度，使用REPAIR TABLE語(yǔ)句修復(fù)一個(gè)損壞的表。表?yè)p壞可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)異常重新啟動(dòng)，需要盡快修復(fù)并確定原因好做應(yīng)對(duì)策略。

使用MyISAM存儲(chǔ)引擎的表支持3種不同的存儲(chǔ)格式，如下：

• 靜態(tài)表，固定長(zhǎng)度；

• 動(dòng)態(tài)表

• 壓縮表

靜態(tài)表是MyISAM存儲(chǔ)引擎的默認(rèn)存儲(chǔ)格式，字段長(zhǎng)度是定長(zhǎng)，記錄都是固定長(zhǎng)度。優(yōu)勢(shì)在于存儲(chǔ)迅速、容易緩存、出現(xiàn)故障易恢復(fù)；缺點(diǎn)是相對(duì)耗存儲(chǔ)空間。需要注意的是：如需保存內(nèi)容后面的空格，默認(rèn)返回結(jié)果會(huì)去掉后面的空格。

動(dòng)態(tài)表包含變長(zhǎng)字段，記錄不是固定長(zhǎng)度，存儲(chǔ)優(yōu)勢(shì)：占用空間相對(duì)較小、但頻繁刪除和更新記錄會(huì)產(chǎn)生碎片。這時(shí)，需要定期執(zhí)行optimize table語(yǔ)句或者myisamchk -r命令來(lái)改善性能，出現(xiàn)故障恢復(fù)相對(duì)較難。

壓縮表由mysiampack工具創(chuàng)建，占用磁盤空間很小。因?yàn)槊總€(gè)記錄是被單獨(dú)壓縮，所以訪問(wèn)開(kāi)始非常小。

梳理一下MyISAM存儲(chǔ)引擎的要點(diǎn)，如下圖1-2-2-1所示：

順帶安利一波，前段時(shí)間發(fā)現(xiàn)WPS也能夠制作精美的思維導(dǎo)圖，并且支持一鍵導(dǎo)入到doc文件中。普通用戶最多可存儲(chǔ)150個(gè)文件。之前也用過(guò)XMind、processon、gitmind等等，現(xiàn)在使用WPS更方便了。

2.2、InnoDB存儲(chǔ)引擎

優(yōu)點(diǎn)與缺點(diǎn)：InnoDB存儲(chǔ)引擎提供了具有提交（commit）、回滾（rollback）和崩潰恢復(fù)能力的事務(wù)安全。但對(duì)比MyISAM存儲(chǔ)引擎，InnoDB寫(xiě)的處理效率相對(duì)差一些，并且會(huì)占用更多的磁盤空間保留數(shù)據(jù)和索引。下圖是我存儲(chǔ)了1kw條數(shù)據(jù)的表，并且使用的是InnoDB存儲(chǔ)引擎。student01表同樣使用了InnoDB存儲(chǔ)引擎，存儲(chǔ)數(shù)據(jù)為100w條。從下圖可以看出存儲(chǔ)數(shù)據(jù)索引在.ibd文件中、表結(jié)構(gòu)則存在.frm文件中。

2.2.1、自動(dòng)增長(zhǎng)列

InnoDB表的自動(dòng)增長(zhǎng)列可以手工插入，但插入的值為空或者0，則實(shí)際插入的將是自動(dòng)自動(dòng)增長(zhǎng)后的值。

本來(lái)想繼續(xù)使用bols那張表作為演示的，思來(lái)想去還是正經(jīng)一點(diǎn)。為了演示，我又新增了一張表為autoincre_test，表示id設(shè)置為主鍵且自增長(zhǎng)，存儲(chǔ)引擎選擇InnoDB。然后插入了3條數(shù)據(jù)進(jìn)行演示。查詢當(dāng)前線程最后插入數(shù)據(jù)的記錄使用值：

MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb;
Query OK, 0 rows affected (0.018 sec)

MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.007 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL [test]> select * from autoincre_test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.000 sec)

select last_insert_id();
MySQL [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.000 sec)

tips：可以通過(guò)alter table table_name=n;語(yǔ)句強(qiáng)制設(shè)置自動(dòng)增長(zhǎng)列的初始值，默認(rèn)從1開(kāi)始，但該強(qiáng)制的默認(rèn)值是保留在內(nèi)存中的，如果使用該值之前數(shù)據(jù)庫(kù)重新啟動(dòng)，強(qiáng)制默認(rèn)值則會(huì)丟失，就需要重新設(shè)置，畢竟使用內(nèi)存沒(méi)有加載到磁盤中。

通過(guò)上面的演示，你會(huì)發(fā)現(xiàn)插入記錄是0或者空時(shí)，實(shí)際插入的將是自動(dòng)增長(zhǎng)后的值。通過(guò)last_insert_id()函數(shù)可以查詢當(dāng)前線程最后插入數(shù)據(jù)的記錄使用值。如果一次插入多條記錄，則返回的是第一條記錄使用的自動(dòng)增長(zhǎng)值，這里就不演示插入多條數(shù)據(jù)了。記住一點(diǎn)，可以使用last_insert_id()去查詢id記錄值。

對(duì)于InnoDB表，自動(dòng)增長(zhǎng)列必須是索引。如果是組合索引，也必須是組合索引的第一列。但對(duì)于MyISAM表，自動(dòng)增長(zhǎng)列可以是組合索引的其它列。這樣插入記錄后，自動(dòng)增長(zhǎng)列是按照組合索引的前面幾列排序后遞增的。你可以創(chuàng)建一張表指定MyISAM存儲(chǔ)引擎，然后將兩列字段組合索引進(jìn)行測(cè)試驗(yàn)證。

2.2.2、外鍵約束

在MySQL中，目前支持外鍵約束的存儲(chǔ)引擎只有InnoDB。創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引。子表創(chuàng)建外鍵的時(shí)候，也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。下面將通過(guò)實(shí)例進(jìn)行講解。可以從MySQL官網(wǎng)下載示例數(shù)據(jù)庫(kù)world和sakila進(jìn)行參考。

• city表，F(xiàn)OREIGN KEY (CountryCode) REFERENCES country (Code)

• country表

• countrylanguage表，F(xiàn)OREIGN KEY (CountryCode) REFERENCES country (Code)

通過(guò)MySQL workbench或者Navicat逆向生成物理模型進(jìn)行參考，更加直觀。插一句，在MySQL的官網(wǎng)同樣有一個(gè)sakila數(shù)據(jù)庫(kù)是關(guān)于演員電影的，也提供了sakila的ERR物理模型圖，這句話做了超鏈接，可以直接訪問(wèn)。給出我之前逆向生成的world數(shù)據(jù)庫(kù)的物理模型：

在創(chuàng)建索引時(shí)，可以指定在刪除、更新父表時(shí)，對(duì)子表進(jìn)行的相應(yīng)操作包含：

• restrict

• cascade

• set null和no action

其中restrict和no action相同，restrict限制在子表有關(guān)聯(lián)記錄的情況下父表無(wú)法更新；cascade表示在父表更新或刪除的時(shí)候，級(jí)聯(lián)更新或者刪除子表對(duì)應(yīng)記錄；set null表示在父表更新或刪除的時(shí)候，子表的對(duì)應(yīng)字段被set null。選擇cascade以及set null時(shí)需要謹(jǐn)慎操作，有可能導(dǎo)致數(shù)據(jù)丟失。

在導(dǎo)入多個(gè)表的數(shù)據(jù)時(shí)，如果忽略表之前的導(dǎo)入順序，可以暫時(shí)關(guān)閉外鍵檢查；同樣執(zhí)行l(wèi)oad data和alter table時(shí)也可以暫時(shí)關(guān)閉外鍵檢查加快處理的速度，提升效率。關(guān)閉外鍵檢查的命令為：

set foreign_key_checks=0;

執(zhí)行完導(dǎo)入數(shù)據(jù)或者修改表的操作后，通過(guò)開(kāi)啟外鍵檢查命令改回來(lái)：

set foreign_key_checks=1;

對(duì)于InnoDB類型的表，外鍵信息可以通過(guò)show create table或者show table status查看。比如查找world數(shù)據(jù)庫(kù)中的city表：

MySQL [sakila]> show table status like 'city'\G

關(guān)于外鍵約束就提這么多，沒(méi)有演示創(chuàng)建以及刪除，因?yàn)橘N太多的SQL語(yǔ)句太占篇幅了?？梢缘組ySQL官網(wǎng)下載world和sakila數(shù)據(jù)庫(kù)進(jìn)行測(cè)試。

2.2.3、存儲(chǔ)方式

InnoDB存儲(chǔ)表和索引有兩種方式：

• 共享表空間存儲(chǔ)

• 多表空間存儲(chǔ)

使用共享表空間存儲(chǔ)，這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個(gè)文件。在開(kāi)頭介紹InnoDB存儲(chǔ)引擎時(shí)也提到過(guò)文件存儲(chǔ)位置。

使用多表空間存儲(chǔ)，這種方式創(chuàng)建的表的表結(jié)構(gòu)仍然保存在.frm文件中，但每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.ibd文件中。如果是個(gè)分區(qū)表，則每個(gè)分區(qū)對(duì)應(yīng)單獨(dú)的.ibd文件，文件名為表名+分區(qū)名?？梢栽趧?chuàng)建分區(qū)的時(shí)候指定每個(gè)分區(qū)的數(shù)據(jù)文件位置，以此來(lái)平均分布磁盤的IO，達(dá)到緩解磁盤壓力的目的。如下是在Windows下使用InnoDB存儲(chǔ)了海量數(shù)據(jù)的文件：

使用多表空間存儲(chǔ)需要設(shè)置參數(shù)innodb_file_per_table，重啟數(shù)據(jù)庫(kù)服務(wù)器才能生效喲。多表空間的參數(shù)生效后，只對(duì)新建的表生效。多表空間的數(shù)據(jù)文件無(wú)大小限制，無(wú)需設(shè)置初始大小，也不需設(shè)置文件的最大限制與擴(kuò)展大小等參數(shù)。使用多表空間存儲(chǔ)優(yōu)勢(shì)在于方便單表備份和恢復(fù)操作。雖然不能直接復(fù)制.frm和.ibd文件達(dá)到目的，但可以使用如下命令操作：

alter table table_name discard tablespace;alter table table_name import tablespace;

將備份恢復(fù)到數(shù)據(jù)庫(kù)中，單表備份，只能恢復(fù)到原來(lái)所在的數(shù)據(jù)庫(kù)中，無(wú)法恢復(fù)到其它數(shù)據(jù)庫(kù)中。如過(guò)需要將單表恢復(fù)至其它目標(biāo)數(shù)據(jù)庫(kù)中，則需要通過(guò)mysqldump和mysqlimport來(lái)實(shí)現(xiàn)。

注意：即便多表存儲(chǔ)更有優(yōu)勢(shì)，但是共享表存儲(chǔ)空間依舊是必須的，InnoDB將內(nèi)部數(shù)據(jù)字典和在線重做日志存在這個(gè)文件中。

梳理一下InnoDB存儲(chǔ)引擎的要點(diǎn)，如下圖1-2-2-2所示：

關(guān)于InnoDB存儲(chǔ)引擎就介紹到此處了，更多詳情可以參考MySQL的官方文檔。是不是發(fā)現(xiàn)了我只在MyISAM和InnoDB存儲(chǔ)引擎做了總結(jié)的思維導(dǎo)圖。沒(méi)錯(cuò)，只做了這兩個(gè)，因?yàn)檫@倆最常用。至于為啥是粉色背景，因?yàn)槔戏蛴幸活w少女心！

2.3、MEMORY存儲(chǔ)引擎

MEMORY存儲(chǔ)引擎使用存在與內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表。每個(gè)MEMORY表只對(duì)應(yīng)一個(gè)磁盤文件，格式是.frm。MEMORY類型的表訪問(wèn)速度極快，存在內(nèi)存中當(dāng)然快。這就是Redis為什么這么快？不僅??？還能持久？咱回到正題，MEMORY存在內(nèi)存中并默認(rèn)使用hash索引，一旦服務(wù)關(guān)閉，表中數(shù)據(jù)會(huì)丟失。創(chuàng)建一張名為GIRLS的表指定存儲(chǔ)引擎為MEMORY，注意了在UNIX和Linux操作系統(tǒng)下，是對(duì)字段和表名大小是寫(xiě)敏感的，關(guān)鍵字不影響。

CREATE TABLE GIRLS (
  ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL,
  CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

還記得在介紹存儲(chǔ)引擎做的那會(huì)張表格嗎，有介紹到MEMORY支持B TREE索引。雖然MEMORY默認(rèn)使用的索引是hash索引，但是你可以手動(dòng)指定索引類型。例如默認(rèn)手動(dòng)指定使用關(guān)鍵字USING HASH：

-- 創(chuàng)建索引指定索引類型為hash。create index mem_hash USING HASH on GIRLS(ID);-- 查詢索引類型，簡(jiǎn)化了一下，只展示了部分參數(shù)。mysql> SHOW TABLE STATUS LIKE 'GIRLS'\G*************************** 1. row ***************************
           Name: GIRLS         Engine: MEMORY
        Version: 10
     Row_format: Fixed1 row in set (0.00 sec)

雖然MEMORY容易丟失數(shù)據(jù)，但是在啟動(dòng)MySQL服務(wù)的時(shí)候，我們可以使用**–init-file選項(xiàng)，將insert into … select或者load data infile**這樣的語(yǔ)句存放在這個(gè)指定的文件中，就可以在服務(wù)啟動(dòng)時(shí)從持久穩(wěn)固的數(shù)據(jù)源裝載表。

服務(wù)器需要提供足夠的內(nèi)存來(lái)維持所有在同一時(shí)間使用的MEMORY表，當(dāng)不在需要MEMORY表內(nèi)容之時(shí)，釋放被MEMORY表使用的內(nèi)存。仔細(xì)思考一下，如果內(nèi)存用了不釋放那將有多可怕。此時(shí)可以執(zhí)行delete form 或truncate table亦或完整地刪除整個(gè)表，使用drop table。這里提一點(diǎn)，在Oracle中也同樣支持truncate，使用truncate的好處在于不用再去考慮回滾（rollback），效率更高。使用truncate需要在命令模式下使用，其它客戶端工具可能不支持。

每個(gè)MEMORY表中存放的數(shù)據(jù)量大小，受max_heap_table_size系統(tǒng)變量約束，初始值為16MB，可以根據(jù)需求調(diào)整。通過(guò)max_rows可以指定表的最大行數(shù)。

MEMORY存儲(chǔ)引擎最大特色是快，主要用于內(nèi)容變化不頻繁的代碼表，或者是為了做統(tǒng)計(jì)提供的中間表，效率更高。使用MEMORY時(shí)需謹(jǐn)慎，萬(wàn)一忘了這廝重啟數(shù)據(jù)就沒(méi)了就尷尬了。所以在使用時(shí)，考慮好重啟服務(wù)器后如何取得數(shù)據(jù)。

關(guān)于MEMORY存儲(chǔ)引擎就介紹到這里，大部分都是些理論知識(shí)，更多的需要自己去實(shí)踐測(cè)試。

2.4、MERGE存儲(chǔ)引擎

MERGE存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)果完全相同，MERGE表本身沒(méi)有數(shù)據(jù)，對(duì)MERGE類型的表可以進(jìn)行查詢、更新、刪除操作，實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行操作的。對(duì)于MERGE類型表的插入操作，通過(guò)insert_method子句定義插入的，可以有3個(gè)不同的值，使用first或last插入操作對(duì)應(yīng)開(kāi)始與最后一個(gè)表上。如果不定義這個(gè)子句，或者定義為NO，表示不能對(duì)MERGE表進(jìn)行操作。

對(duì)MERGE表進(jìn)行DROP操作，只是對(duì)MERGE的定義進(jìn)行刪除，對(duì)內(nèi)部表沒(méi)有任何影響。MERGE表上保留兩個(gè)文件，文件名以表的名字開(kāi)始，分別為：

• .frm文件存儲(chǔ)表定義；

• .mrg文件包含組合表的信息，包含表組成、插入數(shù)據(jù)依據(jù)。

可以通過(guò)修改.mrg文件來(lái)修改表，但修改后需要使用flush tables刷新。測(cè)試可以先創(chuàng)建兩張存儲(chǔ)引擎為MyISAM的表，再建一張存儲(chǔ)引擎為MERGE存儲(chǔ)引擎的表。如下所示創(chuàng)建demo為總表指定引擎為MERGE，demo01和demo02為分表：

CREATE TABLE `merge_demo` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MERGE UNION=(merge_demo01,merge_demo02)  INSERT_METHOD=LAST DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

CREATE TABLE `merge_demo01` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin  
CREATE TABLE `merge_demo02` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

通過(guò)插入數(shù)據(jù)驗(yàn)證MERGE確實(shí)是一個(gè)MyISAM的組合，就是這么神奇。如下所示，只對(duì)demo01和demo02進(jìn)行插入：

INSERT INTO study.`merge_demo01` VALUES(1,'demo01');
INSERT INTO study.`merge_demo02` VALUES(1,'demo02');
mysql [study]> select * from merge_demo;
+----+--------+
| ID | NAME   |
+----+--------+
|  1 | demo01 |
|  1 | demo02 |
+----+--------+
2 rows in set (0.000 sec)

插入完數(shù)據(jù)，分別查看demo01和demo02各只有一條數(shù)據(jù)，總表可以看到倆分表的全部數(shù)據(jù)。關(guān)鍵是指定了insert_method=last。MERGE表和分區(qū)表的區(qū)別，MERGE并不能智能地將記錄插入到對(duì)應(yīng)表中，而分區(qū)表可以做到。通常我們使用MERGE表來(lái)透明的對(duì)多個(gè)表進(jìn)行查詢和更新操作?？梢宰约涸谙旅鏈y(cè)試總表插入數(shù)據(jù)，看分表的情況，我這里就不貼代碼了。

關(guān)于MySQL自帶的幾款常用存儲(chǔ)引擎就介紹到此，感興趣的可以私下測(cè)試驗(yàn)證，更多參考請(qǐng)到官網(wǎng)獲取API或者DOC文檔。

除了MySQL自帶的一些存儲(chǔ)引擎之外，常見(jiàn)優(yōu)秀的第三方存儲(chǔ)引擎有TokuDB，一款開(kāi)源的高性能存儲(chǔ)引擎，適用于MySQL和MariaDB。更多詳情可以去TokuDB官網(wǎng)了解喲。

2.5、修改表的存儲(chǔ)引擎

創(chuàng)建新表時(shí)，如果不指定存儲(chǔ)引擎，系統(tǒng)會(huì)使用默認(rèn)存儲(chǔ)引擎。在MySQL5.5之前默認(rèn)的存儲(chǔ)引擎為MyISAM，在MySQL5.5之后默認(rèn)的存儲(chǔ)引擎為InnoDB。如果想修改默認(rèn)存儲(chǔ)引擎，可以通過(guò)配置文件指定default-table-type的參數(shù)。關(guān)于存儲(chǔ)引擎的查看，在上面介紹存儲(chǔ)引擎的時(shí)候已經(jīng)有說(shuō)明了。

方法一：建表即指定當(dāng)前表的存儲(chǔ)引擎

在創(chuàng)建tolove表的時(shí)候就指定存儲(chǔ)引擎，例如指定存儲(chǔ)引擎為MyISAM，默認(rèn)編碼為utf8：

-- Create TableCREATE TABLE `tolove` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,`GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL,`CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

測(cè)試生成的數(shù)據(jù)量比較大，隨機(jī)生成了1千萬(wàn)條數(shù)據(jù)。查詢（select）業(yè)務(wù)相對(duì)較多，在建表的時(shí)候就指定默認(rèn)存儲(chǔ)引擎MyISAM，統(tǒng)計(jì)（count）的效率很高。以我的渣渣電腦，使用INNODB存儲(chǔ)引擎，統(tǒng)計(jì)一次需要2~3秒左右。在上面講到MYISAM的時(shí)候，已經(jīng)將查詢時(shí)間進(jìn)行過(guò)對(duì)比。

方法二：使用alter table修改當(dāng)前表的存儲(chǔ)引擎

修改創(chuàng)建的tolove表為MYISAM引擎進(jìn)行測(cè)試。

-- 修改創(chuàng)建的tolove表為MYISAM引擎進(jìn)行測(cè)試ALTER TABLE test.`tolove` ENGINE=MYISAM;

修改test表的存儲(chǔ)引擎為INNODB進(jìn)行測(cè)試。

-- 修改表的存儲(chǔ)引擎為INNODB進(jìn)行測(cè)試ALTER TABLE test.`test` ENGINE=INNODB;

SHOW CREATE TABLE查詢表的存儲(chǔ)引擎，分別查詢test表和tolove表，在講存儲(chǔ)引擎為MyISAM的時(shí)候，有演示過(guò)喲！

SHOW CREATE TABLE test.`test`;SHOW CREATE TABLE test.`tolove`;

如果在工具中無(wú)法看全，可以導(dǎo)出成xml、csv、html等查詢，以下是我查詢出自己創(chuàng)建表時(shí)設(shè)置的存儲(chǔ)引擎為InnoDB：

-- 顯示出我創(chuàng)建的test表的SQL語(yǔ)句存儲(chǔ)引擎為InnoDB
CREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `STU_NAME` varchar(50) NOT NULL, `SCORE` int(11) NOT NULL, `CREATETIME` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8
-- 顯示出我創(chuàng)建的tolove表的SQL語(yǔ)句，存儲(chǔ)引擎為MyISAM
CREATE TABLE `tolove` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL, `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL, `CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

存儲(chǔ)引擎的修改就介紹這么多，看到我的自增長(zhǎng)列（AUTO_INCREMENT）ID到了20000001，之前隨機(jī)生成過(guò)一次1kw條數(shù)據(jù)喲！有一部分解釋說(shuō)明我寫(xiě)在了代碼塊中，看起來(lái)更加舒服。

3、存儲(chǔ)引擎的選擇

在選擇合適的存儲(chǔ)引擎時(shí)，應(yīng)根據(jù)應(yīng)用特點(diǎn)選擇合適的存儲(chǔ)引擎。對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，你可以選擇多種存儲(chǔ)引擎滿足不同的應(yīng)用場(chǎng)景需求。如何選擇合適的存儲(chǔ)引擎呢？存儲(chǔ)引擎的選擇真的很重要嗎？

確實(shí)應(yīng)該好好思考，在并不復(fù)雜的應(yīng)用場(chǎng)景下，可能MyISAM存儲(chǔ)引擎就能滿足日常開(kāi)銷?；蛟S在另外一種場(chǎng)景之下InnoDB才是最佳選擇，綜合性能更好，滿足更多需求。

MyISAM是MySQL的默認(rèn)的插件式存儲(chǔ)引擎，是MySQL在5.5之前的默認(rèn)存儲(chǔ)引擎。如果應(yīng)用以讀和插入操作居多，只有很少的更新和刪除操作，對(duì)事務(wù)完整性、并發(fā)性沒(méi)有很高的需求，此時(shí)首選是MyISAM存儲(chǔ)引擎。在web和數(shù)據(jù)倉(cāng)庫(kù)最常用的存儲(chǔ)引擎之一。

InnoDB用于事務(wù)處理應(yīng)用程序，并且支持外鍵。是MySQL在5.5之后的默認(rèn)存儲(chǔ)引擎，同樣也是MariaDB在10.2之后的默認(rèn)存儲(chǔ)引擎，足見(jiàn)InnoDB的優(yōu)秀之處。如果應(yīng)用對(duì)事務(wù)完整性有較高的要求，在并發(fā)情況下要求數(shù)據(jù)高度一致性。數(shù)據(jù)除了插入和查詢以外，還包括很多的更新和刪除操作，那么InnoDB應(yīng)該是比較合適的存儲(chǔ)引擎選擇。InnoDB除了有效地降低由于刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交（commit）、回滾（rollback）。對(duì)類似計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)，InnoDB也是合適的選擇。插點(diǎn)題外話，本人在工作中使用Oracle數(shù)據(jù)庫(kù)也有一段時(shí)間，Oracle的事務(wù)確實(shí)很強(qiáng)大，處理大數(shù)據(jù)壓力很強(qiáng)。

MEMORY存儲(chǔ)引擎將所有的數(shù)據(jù)存在RAM中，在需要快速定位記錄和其它類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問(wèn)。MEMORY的缺陷在于對(duì)表的大小有限制，太大的表無(wú)法緩存在內(nèi)存中，其次是要確保表的數(shù)據(jù)可以恢復(fù)，數(shù)據(jù)庫(kù)異常重啟后表的數(shù)據(jù)是可恢復(fù)的。MEMORY表通常用于更新不太頻繁的小表，快速定位訪問(wèn)結(jié)果。

MERGE用于將一組等同的MyISAM存儲(chǔ)引擎的表以邏輯方式組合在一起，并作為一個(gè)對(duì)象應(yīng)用它們。MERGE表的優(yōu)點(diǎn)在于可以突破對(duì)單個(gè)MyISAM表大小的限制，并通過(guò)將不同的表分布在多個(gè)磁盤上，改善MERGE表的訪問(wèn)效率。對(duì)數(shù)據(jù)局倉(cāng)庫(kù)等VLDB環(huán)境很適合。

最后，關(guān)于存儲(chǔ)引擎的選擇都是根據(jù)別人實(shí)際經(jīng)驗(yàn)去總結(jié)的。并不是一定契合你的應(yīng)用場(chǎng)景，最終需要用戶對(duì)各自應(yīng)用進(jìn)行測(cè)試，通過(guò)測(cè)試來(lái)獲取最合適的結(jié)果。就像我開(kāi)始列舉的示例，數(shù)據(jù)量很龐大，對(duì)查詢和插入業(yè)務(wù)比較頻繁，我就開(kāi)始對(duì)MyISAM存儲(chǔ)引擎進(jìn)行測(cè)試，確實(shí)比較符合我的應(yīng)用場(chǎng)景。

關(guān)于存儲(chǔ)引擎的選擇，總結(jié)簡(jiǎn)化如下圖1-3：

4、表的優(yōu)化（碎片整理）

在開(kāi)始介紹存MyISAM和InnoDB儲(chǔ)引擎的時(shí)候，我也展示過(guò)存儲(chǔ)大量數(shù)據(jù)所占的磁盤空間。使用OPTIMIZE TABLE來(lái)優(yōu)化test數(shù)據(jù)庫(kù)下的test表，優(yōu)化之前，這張表占據(jù)磁盤空間大概在824M；通過(guò)優(yōu)化之后，有明顯的改善，系統(tǒng)回收了沒(méi)有利用的空間，test表所耗磁盤空間明顯下降，優(yōu)化之后只有456M。這里就不貼磁盤所占空間的截圖了。

OPTIMIZE TABLE test.`test`;

優(yōu)化之后，統(tǒng)計(jì)（count）數(shù)據(jù)效率也有所提升，大概在2.5sec左右：

mysql [test]> select count(*) from test; -- 使用的是innodb存儲(chǔ)引擎測(cè)試
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.468 sec)

優(yōu)化之前，統(tǒng)計(jì)數(shù)據(jù)大概在3.080 sec。經(jīng)過(guò)對(duì)比，效率提升是可觀的。

你也可以使用explain執(zhí)行計(jì)劃對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化。關(guān)于MySQL優(yōu)化方面的知識(shí)，并不是本文的重點(diǎn)，就不做過(guò)多描述。

二、索引設(shè)計(jì)與使用

1、索引簡(jiǎn)介

在涉及到MySQL的面試當(dāng)中，會(huì)提到最左前綴索引，都被玩成梗了。

MySQL所有列類型都可以被索引，對(duì)相關(guān)列合理的使用索引是提高查詢（select）操作性能的最佳方法。根據(jù)引擎可以定義每張表的最大索引數(shù)和最大索引長(zhǎng)度，MySQL的每種存儲(chǔ)引擎（MyISAM、InnoDB等等）對(duì)每張表至少支持16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)。大多數(shù)存儲(chǔ)引擎有更高的限制。

MyISAM和InnoDB存儲(chǔ)引擎默認(rèn)創(chuàng)建的表都是BTREE索引。在MySQL8.0之前是不只支持函數(shù)索引的，MySQL5.7推出了虛擬列功能，在MySQL8.0開(kāi)始支持函數(shù)索引，也是8.0版本的新特性之一。

MySQL支持前綴索引，對(duì)索引字段的前N個(gè)字符創(chuàng)建索引，前綴索引長(zhǎng)度和存儲(chǔ)引擎有關(guān)。有很多人經(jīng)常會(huì)問(wèn)到，MySQL支持全文索引嗎？我的回答是：支持。MySQL5.6之前MyISAM存儲(chǔ)引擎支持全文索引（FULLTEXT），5.6之后InnoDB開(kāi)始支持全文索引。

為test表創(chuàng)建10個(gè)字節(jié)的前綴索引，創(chuàng)建索引的語(yǔ)法如下：

CREATE INDEX girl_name ON table_name(test(10));

同樣可以使用alter table語(yǔ)句去新增索引，給girl表的字段girl_name新增一個(gè)索引：

ALTER TABLE test.`girl` ADD INDEX idx_girlname(girl_name);

對(duì)于使用索引的驗(yàn)證可以使用explain執(zhí)行計(jì)劃去判斷。關(guān)于索引的簡(jiǎn)述就介紹這么多，更多基礎(chǔ)知識(shí)可以參考官方文檔或者權(quán)威書(shū)籍。

2、設(shè)計(jì)索引原則

索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則。有助于提升索引的使用效率。

搜索的索引列，不一定是所要選擇的列。最合適的索引列，往往是出現(xiàn)在where子句中的列，或者是連接子句中指定的列，而不是出現(xiàn)在select后選擇列表中的列。

使用唯一索引?？紤]某列中值的分布，索引列的基數(shù)越大，索引效果越好。

使用短索引。如果對(duì)字符串列進(jìn)行索引，應(yīng)指定一個(gè)前綴長(zhǎng)度。比如char(100)，思考一下，重復(fù)度的問(wèn)題。是全部索引來(lái)的快，還是對(duì)部分字符進(jìn)行索引更優(yōu)？

利用最左前綴。在創(chuàng)建一個(gè)N列的索引時(shí)，實(shí)際上是創(chuàng)建了MySQL可利用的N個(gè)索引。多列索引可以起幾個(gè)索引的作用，利用索引中最左邊的列表來(lái)匹配行。這樣的列集稱為最左前綴。都快被涉及到MySQL的面試玩成梗了，哈哈。

注意不要過(guò)度使用索引。不要以為使用索引好處多多，就在所有的列上全部使用索引，過(guò)度使用索引反而會(huì)適得其反。每個(gè)額外的索引會(huì)占用磁盤空間，對(duì)磁盤寫(xiě)操作性能造成損耗。在重構(gòu)的時(shí)候，索引也得更新，造成不必要的時(shí)間浪費(fèi)。

InnoDB存儲(chǔ)引擎的表。對(duì)于使用InnoDB存儲(chǔ)引擎的表，記錄默認(rèn)按一定的順序保存。有如下幾種情況：

• 如果有明確定義的主鍵，則遵循主鍵順序保存；

• 在沒(méi)有主鍵，但有唯一索引的情況下，會(huì)遵循唯一索引順序保存；

• 既沒(méi)有主鍵又沒(méi)有唯一索引，表中會(huì)自動(dòng)生成一個(gè)內(nèi)部列，并遵循這個(gè)列的順序保存。

以上就是對(duì)索引設(shè)計(jì)原則的簡(jiǎn)單介紹。

3、B-TREE與HASH索引

使用這些索引時(shí)，應(yīng)該考慮索引是否當(dāng)前使用條件下生效！在使用MEMORY存儲(chǔ)引擎的表中可以選擇使用HASH索引或者B-TREE索引，兩種不同的索引有其各自適用的范圍。

HASH索引。只用于這類關(guān)系操作符：=、<=>的操作比較，優(yōu)化器不能使用HASH索引來(lái)加速order by操作。MySQL不能確定在兩個(gè)值之間大約有多少行。

B-TREE索引。對(duì)于B-TREE索引，使用>、<、>=、<=、BETWEEN、!=或者<>、亦或是使用like ‘condition’。其中’condition’不以通配符開(kāi)始的操作符時(shí)，都可以使用相關(guān)列上的索引。

關(guān)于索引就介紹到這里。合理的使用索引將有助于提升效率，但并不是使用的索引越多越好。

三、數(shù)據(jù)類型選擇

• 字符串類型char與varchar

• 浮點(diǎn)數(shù)和定點(diǎn)數(shù)

• 日期類型

工作中，個(gè)人使用經(jīng)驗(yàn)。Oracle里面使用BLOB存儲(chǔ)大字段比較頻繁，TEXT相對(duì)少見(jiàn)，使用VARCHAR2類型比較多。但在MySQL中是不支持VARCHAR2類型的。

1、CHAR與VARCHAR

char和varchar類型類似，用于存儲(chǔ)字符串，但它們保存和檢索的方式不同。char類型屬于固定長(zhǎng)度（定長(zhǎng)）類型的字符串，varchar屬于可變長(zhǎng)度的字符串類型。在MySQL的嚴(yán)格模式中，使用的char和varchar，超過(guò)列長(zhǎng)度的值不會(huì)被保存，并且出現(xiàn)錯(cuò)誤提示。

char優(yōu)缺點(diǎn)。char是固定長(zhǎng)度，處理速度比varchar要快，但缺點(diǎn)是浪費(fèi)存儲(chǔ)空間，沒(méi)有varchar那么靈活。varchar。隨著MySQL的不斷升級(jí)，varchar類型也在不斷優(yōu)化，性能也在提升，被用于更多的應(yīng)用中。

MyISAM存儲(chǔ)引擎：建議使用固定長(zhǎng)度的數(shù)據(jù)列代替可變長(zhǎng)度的數(shù)據(jù)列。

InnoDB存儲(chǔ)引擎：建議使用VARCHAR類型。

MEMORY存儲(chǔ)引擎：使用固定長(zhǎng)度數(shù)據(jù)類型存儲(chǔ)。

2、TEXT與BLOB

一般情況，存儲(chǔ)少量的字符串時(shí)，會(huì)選擇char和varchar類型。而在保存較大文本時(shí)，通常選擇TEXT或者BLOB大字段，二者之間的區(qū)別在于BLOB能存二進(jìn)制數(shù)據(jù)，比如：照片，TEXT類型只能存字符數(shù)據(jù)。這也是為什么我在開(kāi)始的時(shí)候提及到個(gè)人工作中見(jiàn)到BLOB類型相對(duì)較多。TEXT和BLOB還包括不同類型：

• TEXT、LONGTEXT、MEDIUMINT、MEDIUMTEXT、TINYTEXT；

• BLOB、LONGBLOB、MEDIUMBLOB、TINYBLOB。

區(qū)別在于存儲(chǔ)文本長(zhǎng)度和字節(jié)不同。

需要注意的點(diǎn)：

• BLOB和TEXT值會(huì)引起一些性能問(wèn)題，尤其是執(zhí)行大量刪除操作時(shí)；

• 可以使用合成索引提高大字段的查詢性能；

• 在不必要的時(shí)候避免檢索大字段；

• 將BLOB和TEXT分離到不同的表中。

3、浮點(diǎn)數(shù)與定點(diǎn)數(shù)

浮點(diǎn)類型一般用于表示含有小數(shù)部分的值。列舉一些示例：

• double類型：用于浮點(diǎn)數(shù)（雙精度）；

• decimal類型：MySQL中表示定點(diǎn)數(shù)；

• float類型：用于浮點(diǎn)數(shù)（單精度）。

學(xué)過(guò)Java語(yǔ)言的同學(xué)，對(duì)這些浮點(diǎn)類型并不陌生吧。

注意點(diǎn)：浮點(diǎn)數(shù)存在誤差問(wèn)題，對(duì)精度比較敏感的數(shù)據(jù)，避免對(duì)浮點(diǎn)類型做比較。

4、日期類型

談到日期類型，又讓我想起了7年前學(xué)Java語(yǔ)言的時(shí)候，會(huì)寫(xiě)一個(gè)工具類（Utils.java），將常用的處理日期的方法寫(xiě)進(jìn)去然后調(diào)用。經(jīng)常用到的一個(gè)方法（SimpleDateFormat），對(duì)時(shí)間戳進(jìn)行轉(zhuǎn)換格式化。

MySQL中常用的日期類型有：

• DATE

• DATETIME

• TIME

• TIMESTAMP

如果需要記錄年月日時(shí)分秒，并且記錄的年份比較久遠(yuǎn)，最好用DATETIME，而不要使用TIMESTAMP時(shí)間戳。TIMESTAMP表示的范圍比DATETIME短得多。

四、字符集（字符編碼）設(shè)置

從本質(zhì)上來(lái)說(shuō)，計(jì)算機(jī)只能是被二進(jìn)制代碼（010101）。因此，不論是計(jì)算機(jī)程序還是處理的數(shù)據(jù)，最終都會(huì)轉(zhuǎn)換成二進(jìn)制代碼，計(jì)算機(jī)才能識(shí)別。為了讓計(jì)算機(jī)不僅能做科學(xué)計(jì)算，也能處理文字信息，于是計(jì)算機(jī)字符集誕生了。

字符編碼（英語(yǔ)：Character encoding）、字集碼是把字符集中的字符編碼為指定集合中某一對(duì)象)（例如：比特模式、自然數(shù)序列、8位組或者電脈沖），以便文本在計(jì)算機(jī)中存儲(chǔ)和通過(guò)通信網(wǎng)絡(luò)的傳遞。常見(jiàn)的例子包括將拉丁字母表編碼成摩斯電碼和ASCII。其中，ASCII將字母、數(shù)字和其它符號(hào)編號(hào)，并用7比特的二進(jìn)制來(lái)表示這個(gè)整數(shù)。通常會(huì)額外使用一個(gè)擴(kuò)充的比特，以便于以1個(gè)字節(jié)的方式存儲(chǔ)。

在計(jì)算機(jī)技術(shù)發(fā)展的早期，如ASCII（1963年）和EBCDIC（1964年）這樣的字符集逐漸成為標(biāo)準(zhǔn)。但這些字符集的局限很快就變得明顯，于是人們開(kāi)發(fā)了許多方法來(lái)擴(kuò)展它們。對(duì)于支持包括東亞CJK字符家族在內(nèi)的寫(xiě)作系統(tǒng)的要求能支持更大量的字符，并且需要一種系統(tǒng)而不是臨時(shí)的方法實(shí)現(xiàn)這些字符的編碼。

引用自維基百科對(duì)字符編碼的介紹。

1、Unicode

Unicode是什么？是統(tǒng)一編碼，是計(jì)算機(jī)科學(xué)領(lǐng)域的業(yè)界標(biāo)準(zhǔn)。從最初的的1.0.0到目前最新的14.0版本，對(duì)應(yīng)ISO/IEC 10646-N:xxxx。說(shuō)一下UTF-8、UTF-16、UTF-16LE、UTF-32BE、UTF-32LE等等大家應(yīng)該很熟悉了。

2、常見(jiàn)字符集

常見(jiàn)的字符集：

• UTF-8：泛用性最廣泛；

• GBK：對(duì)中文支持非常友好，在GB2312基礎(chǔ)上進(jìn)行了擴(kuò)充；

• GB2312：對(duì)中文字符集支持，；

• GB18030：支持中文字符集，解決GBK強(qiáng)制力不夠的問(wèn)題。

3、MySQL支持的字符集

通過(guò)show character set;命令可以查看MySQL支持的字符集。我只展示部分：

mysql [test]> show character set;
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

或者你還可以使用DESC information_schema.CHARACTER_SETS查看所有字符集和字符集默認(rèn)的校對(duì)規(guī)則。

查看相關(guān)字符集校對(duì)規(guī)則，可以使用SHOW COLLATION配合 LIKE模糊搜索gbk字符集。

SHOW COLLATION LIKE 'gbk%';

MySQL字符集設(shè)置：默認(rèn)可以過(guò)配置文件設(shè)置character-set-server參數(shù)。

• Linux發(fā)行版中安裝一般在my.cnf中配置；

• Windows下在my.ini文件中配置

[mysqld]character-set-server=utf-8
character-set-server=gbk

額外再提一點(diǎn)，判斷字符集所占字節(jié)，可以使用函數(shù)LENGTH()：

SELECT LENGTH('中');

如果使用的是UTF-8編碼，默認(rèn)漢字是占用3個(gè)字節(jié)，使用GBK則占用2個(gè)字節(jié)。字符編碼就介紹到這里。

五、MySQL示例數(shù)據(jù)庫(kù)sakila

視圖、存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器。這里給出我自己隨機(jī)生成海量數(shù)據(jù)用到的函數(shù)和存儲(chǔ)過(guò)程。

1、函數(shù)

創(chuàng)建函數(shù)，使用DELIMITER聲明，使用CREATE FUNCTION創(chuàng)建函數(shù)，tolove表的創(chuàng)建在介紹存儲(chǔ)引擎過(guò)程中已經(jīng)有展示過(guò)。

/** 創(chuàng)建函數(shù) 生成學(xué)號(hào) **/DELIMITER $CREATE FUNCTION rand_number() RETURNS INTBEGIN
	DECLARE i INT DEFAULT 0;
	SET i= FLOOR(1+RAND()*100);
	RETURN i;END $DELIMITER $

創(chuàng)建函數(shù)：用于生成姓名隨機(jī)字符串

/** 創(chuàng)建函數(shù) 生成姓名隨機(jī)字符串 **/DELIMITER $CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255)BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;	END $DELIMITER $

2、存儲(chǔ)過(guò)程

創(chuàng)建存儲(chǔ)過(guò)程，使用CREATE PROCEDURE創(chuàng)建：

/** 創(chuàng)建存儲(chǔ)過(guò)程 **/DELIMITER $CREATE PROCEDURE insert_tolove(IN max_num INT(10))BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;
	WHILE i< max_num DO
	INSERT INTO test.`tolove`(ID,GIRL_NAME,GIRL_AGE,CUP_SIZE) VALUES(NULL,rand_name(5),rand_number(),NULL);
	SET i = i + 1;
	END WHILE;COMMIT;END $DELIMITER $

使用CALL調(diào)用存儲(chǔ)過(guò)程，隨機(jī)生成百萬(wàn)數(shù)據(jù)：

/** 調(diào)用存儲(chǔ)過(guò)程 **/CALL insert_tolove(100*10000);

刪除函數(shù)或者存儲(chǔ)過(guò)程，使用DROP關(guān)鍵字

-- 刪除函數(shù)rand_nameDROP FUNCTION rand_name;
-- 刪除存儲(chǔ)過(guò)程insert_toloveDROP PROCEDURE insert_tolove;

3、觸發(fā)器

創(chuàng)建觸發(fā)器使用CREATE TRIGGER，這里就引用sakila數(shù)據(jù)庫(kù)實(shí)例。如果存在，使用了判斷語(yǔ)句 IF EXISTS，然后刪除DROP TRIGGER已經(jīng)存在的觸發(fā)器。

DELIMITER $$USE `sakila`$$DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`$$CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `customer_create_date` BEFORE INSERT ON `customer` 
    FOR EACH ROW SET NEW.create_date = NOW();$$DELIMITER ;

4、sakila數(shù)據(jù)庫(kù)

在文中我反復(fù)提到了MySQL的示例數(shù)據(jù)庫(kù)sakila，是一個(gè)完整的學(xué)習(xí)MySQL的好例子。包含了視圖、存儲(chǔ)過(guò)程、函數(shù)和觸發(fā)器?？梢匀ySQL的官網(wǎng)獲取SQL腳本。

文末留一個(gè)神秘的參數(shù)，通過(guò)此種方式可以過(guò)濾你不想看到的內(nèi)容喲！無(wú)論在手機(jī)端或者PC端都可生效，親測(cè)可用。

xxxx(檢索的內(nèi)容) -site:xxxn.net
-- 或者
xxxx(檢索的內(nèi)容) -site:xxshu.com

感謝各位的閱讀，以上就是“MySQL存儲(chǔ)引擎怎么理解”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL存儲(chǔ)引擎怎么理解這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！