MySQL存儲引擎怎么理解

蝸牛 互聯網技術資訊 2022-03-05 151 0

這篇文章主要講解了“MySQL存儲引擎怎么理解”,文中的講解內容簡單清晰,易于學習與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學習“MySQL存儲引擎怎么理解”吧!

MySQL存儲引擎怎么理解  mysql 第1張

MySQL存儲引擎怎么理解  mysql 第2張

今天發現了一個神奇的參數-site:xxxx.net

一、存儲引擎的選擇(表類型)

1、存儲引擎的介紹

與到多數關系型數據庫的區別在于MySQL有一個存儲引擎的概念,針對不同的存儲需求可以選擇最合適的存儲引擎。MySQL中的插件式的存儲引擎是其一大特色,用戶可以根據應用的需求選擇如何存儲、是否索引,是否使用事務。嘿嘿,你也可以根據業務環境去適配最適合自己業務的存儲引擎。

Oracle從中嗅到了商機,收購了MySQL,從此有了企業版(商業支持)。社區版依舊可以免費下載。另一大魅力也是因為開源,社區高度活躍,人人都可貢獻。接下來介紹幾種使用比較多的存儲引擎,存儲引擎并無優劣之分,有的只是誰更適合對應的生產業務環境。

MySQL5.0中支持的存儲引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB(MySQL5.5以及MariaDB10.2之后的默認存儲引擎)、PERFORMANCE_SCHEMA(非常規存儲數據引擎)。下面給出MySQL與MariaDB支持的存儲器引擎的對比,可以看出MariaDB新增了Aria引擎:

MySQL存儲引擎怎么理解  mysql 第3張

查看存儲引擎

通過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:當前版本數據庫是否支持該存儲引擎,YES:支持、NO:不支持;Supports transactions, row-level locking, and foreign keys,個人字面上翻譯這段話:支持事務、行級別鎖和外鍵;

  • Comment:對該存儲引擎的詳情描述,比如描述該引擎否支持事務和外鍵;

  • Transactions:對該存儲引擎是否支持事務的描述,YES:支持、NO:不支持;

  • XA:是否滿足XA規范。XA規范是開放群組關于分布式事務處理(DTP)的規范。YES:支持、NO:不支持;

  • Savepoints:字面意思是保存點,對事物控制是否支持,YES:支持、NO:不支持。

小聲嗶嗶,如果你能閱讀明白官方的一些英文文檔,這將有助于你對MySQL存儲引擎的進一步理解,養成閱讀源碼或者文檔的能力。

順帶的提一下MySQL的妹妹MariaDB。在MySQL的復刻版本MariaDB中10.2之前使用的自帶的新引擎Aria,在MariaDB10.2之后使用的默認存儲引擎也是InnoDB,足以看出InnoDB存儲引擎的優秀之處。MariaDB的API和協議兼容MySQL,另外又添加了一些功能,以支持本地的非阻塞操作和進度報告。這意味著,所有使用MySQL的連接器、程序庫和應用程序也將可以在MariaDB下工作。在此基礎上,由于擔心甲骨文MySQL的一個更加封閉的軟件項目,Fedora等Linux發行版已經在最新版本中以MariaDB取代MySQL,維基媒體基金會的服務器同樣也使用MariaDB取代了MySQL。

主要需要了解的幾種存儲引擎

  • MyISAM

  • InnoDB

  • MEMORY

  • MERGE

下面將著重介紹我最近看書認識的幾種常用的存儲引擎,對比各個存儲引擎之間的區別,幫助我們理解不同存儲引擎的使用方式。更多詳情可以參考MySQL的官方文檔。

2、部分存儲引擎的特性

存儲引擎/支持特性 存儲限制 事務安全 鎖機制 B樹索引 哈希索引 全文索引 集群索引 數據緩存 索引緩存 數據可壓縮 空間使用 內存使用 批量插入速度 外鍵支持
MyISAM
表鎖 支持
支持

支持 支持
InnoDB 64TB 支持 行鎖 支持
支持(5.6) 支持 支持 支持
支持
MEMORY
表鎖 支持 支持

支持 支持
N/A 中等
MERGE 沒有
表鎖 支持



支持

NDB
行鎖 支持


支持 支持

InnoDB存儲引擎在MySQL5.6版本開始支持全文索引。在MySQL5.7推出了虛擬列,MySQL8.0新特性加入了函數索引支持。

2.1、MyISAM存儲引擎

MyISAM是MySQL5.5之前默認的存儲引擎。MyISAM不支持事務、不支持外鍵。優勢在于訪問速度快,對事務完整性沒有特殊要求或者以select和insert為主的應用基本上可以使用MyISAM作為存儲引擎創建表。我們先弄個例子出來演示,事先準備了一張數據千萬級別的表,看看這個存儲引擎的特性:

我已經創建好了數據庫為test,在test中分別創建了兩張表test和tolove。test表在創建的時候指定默認存儲引擎為MyISAM,tolove表指定存儲引擎為InnoDB。
使用MyISAM存儲引擎創建的表tolove,查詢存儲有1kw數據的表tolove。

tips:你可以使用use test,切換到test數據庫,就不用像我這樣查詢tolove表去指定test數據庫了喲!

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

再看演示使用InnoDB存儲引擎創建的表test,同樣為了演示,事先隨機生成了1kw條數據。

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

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

MyISAM在磁盤中存儲的文件

每個MyISAM在磁盤上存儲成3個文件,其文件名和表名都相同,擴展名分別是:

  • .frm:存儲表定義;

  • .MYD:MYData,存儲數據;

  • .MYI:MYindex,存儲索引。

MySQL存儲引擎怎么理解  mysql 第4張

數據文件和索引文件可以存放在不同的目錄,平均分布IO,獲得更快的速度,提升性能。需要指定索引文件和數據文件存儲的路徑,創建表時通過DATA DIRECTORY和INDEX DIRECTORY參數指定,表明不同MyISAM表的索引文件和數據文件可以存放在不同的路徑下。當然,需要給予該路徑的訪問權限。

MyISAM損壞處理方式

MyISAM類型的表可能會損壞,原因多種多樣。損壞后的表有可能不能被訪問,會提示需要修復或者訪問后提示返回錯誤結果。MyISAM類型的表,可以通過提供的修復工具執行CHECK TABLE語句檢查MyISAM表的健康程度,使用REPAIR TABLE語句修復一個損壞的表。表損壞可能會導致數據庫異常重新啟動,需要盡快修復并確定原因好做應對策略。

MySQL存儲引擎怎么理解  mysql 第5張

使用MyISAM存儲引擎的表支持3種不同的存儲格式,如下:

  • 靜態表,固定長度;

  • 動態表

  • 壓縮表

靜態表是MyISAM存儲引擎的默認存儲格式,字段長度是定長,記錄都是固定長度。優勢在于存儲迅速、容易緩存、出現故障易恢復;缺點是相對耗存儲空間。需要注意的是如需保存內容后面的空格,默認返回結果會去掉后面的空格。

動態表包含變長字段,記錄不是固定長度,存儲優勢:占用空間相對較小、但頻繁刪除和更新記錄會產生碎片。這時,需要定期執行optimize table語句或者myisamchk -r命令來改善性能,出現故障恢復相對較難。

壓縮表mysiampack工具創建,占用磁盤空間很小。因為每個記錄是被單獨壓縮,所以訪問開始非常小。

梳理一下MyISAM存儲引擎的要點,如下圖1-2-2-1所示:

MySQL存儲引擎怎么理解  mysql 第6張

順帶安利一波,前段時間發現WPS也能夠制作精美的思維導圖,并且支持一鍵導入到doc文件中。普通用戶最多可存儲150個文件。之前也用過XMind、processon、gitmind等等,現在使用WPS更方便了。

2.2、InnoDB存儲引擎

優點與缺點:InnoDB存儲引擎提供了具有提交(commit)、回滾(rollback)和崩潰恢復能力的事務安全。但對比MyISAM存儲引擎,InnoDB寫的處理效率相對差一些,并且會占用更多的磁盤空間保留數據和索引。下圖是我存儲了1kw條數據的表,并且使用的是InnoDB存儲引擎。student01表同樣使用了InnoDB存儲引擎,存儲數據為100w條。從下圖可以看出存儲數據索引在.ibd文件中、表結構則存在.frm文件中。

MySQL存儲引擎怎么理解  mysql 第7張

2.2.1、自動增長列

InnoDB表的自動增長列可以手工插入,但插入的值為空或者0,則實際插入的將是自動自動增長后的值。

本來想繼續使用bols那張表作為演示的,思來想去還是正經一點。為了演示,我又新增了一張表為autoincre_test,表示id設置為主鍵且自增長,存儲引擎選擇InnoDB。然后插入了3條數據進行演示。查詢當前線程最后插入數據的記錄使用值:

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:可以通過alter table table_name=n;語句強制設置自動增長列的初始值,默認從1開始,但該強制的默認值是保留在內存中的,如果使用該值之前數據庫重新啟動,強制默認值則會丟失,就需要重新設置,畢竟使用內存沒有加載到磁盤中。

通過上面的演示,你會發現插入記錄是0或者空時,實際插入的將是自動增長后的值。通過last_insert_id()函數可以查詢當前線程最后插入數據的記錄使用值。如果一次插入多條記錄,則返回的是第一條記錄使用的自動增長值,這里就不演示插入多條數據了。記住一點,可以使用last_insert_id()去查詢id記錄值。

對于InnoDB表,自動增長列必須是索引。如果是組合索引,也必須是組合索引的第一列。但對于MyISAM表,自動增長列可以是組合索引的其它列。這樣插入記錄后,自動增長列是按照組合索引的前面幾列排序后遞增的。你可以創建一張表指定MyISAM存儲引擎,然后將兩列字段組合索引進行測試驗證。

2.2.2、外鍵約束

MySQL中,目前支持外鍵約束的存儲引擎只有InnoDB。創建外鍵的時候,要求父表必須有對應的索引。子表創建外鍵的時候,也會自動創建對應的索引。下面將通過實例進行講解??梢詮腗ySQL官網下載示例數據庫world和sakila進行參考。

  • city表,FOREIGN KEY (CountryCode) REFERENCES country (Code)

  • country表

  • countrylanguage表,FOREIGN KEY (CountryCode) REFERENCES country (Code)

通過MySQL workbench或者Navicat逆向生成物理模型進行參考,更加直觀。插一句,在MySQL的官網同樣有一個sakila數據庫是關于演員電影的,也提供了sakila的ERR物理模型圖,這句話做了超鏈接,可以直接訪問。給出我之前逆向生成的world數據庫的物理模型:

MySQL存儲引擎怎么理解  mysql 第8張

在創建索引時,可以指定在刪除、更新父表時,對子表進行的相應操作包含:

  • restrict

  • cascade

  • set null和no action

其中restrictno action相同,restrict限制在子表有關聯記錄的情況下父表無法更新;cascade表示在父表更新或刪除的時候,級聯更新或者刪除子表對應記錄;set null表示在父表更新或刪除的時候,子表的對應字段被set null。選擇cascade以及set null時需要謹慎操作,有可能導致數據丟失。

在導入多個表的數據時,如果忽略表之前的導入順序,可以暫時關閉外鍵檢查;同樣執行load data和alter table時也可以暫時關閉外鍵檢查加快處理的速度,提升效率。關閉外鍵檢查的命令為:

set?foreign_key_checks=0;

執行完導入數據或者修改表的操作后,通過開啟外鍵檢查命令改回來:

set?foreign_key_checks=1;

對于InnoDB類型的表,外鍵信息可以通過show create table或者show table status查看。比如查找world數據庫中的city表:

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

關于外鍵約束就提這么多,沒有演示創建以及刪除,因為貼太多的SQL語句太占篇幅了??梢缘組ySQL官網下載world和sakila數據庫進行測試。

2.2.3、存儲方式

InnoDB存儲表和索引有兩種方式:

  • 共享表空間存儲

  • 多表空間存儲

使用共享表空間存儲,這種方式創建的表的表結構保存在.frm文件中,數據和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中,可以是多個文件。在開頭介紹InnoDB存儲引擎時也提到過文件存儲位置。

使用多表空間存儲,這種方式創建的表的表結構仍然保存在.frm文件中,但每個表的數據和索引單獨保存在.ibd文件中。如果是個分區表,則每個分區對應單獨的.ibd文件,文件名為表名+分區名??梢栽趧摻ǚ謪^的時候指定每個分區的數據文件位置,以此來平均分布磁盤的IO,達到緩解磁盤壓力的目的。如下是在Windows下使用InnoDB存儲了海量數據的文件:

MySQL存儲引擎怎么理解  mysql 第9張

使用多表空間存儲需要設置參數innodb_file_per_table,重啟數據庫服務器才能生效喲。多表空間的參數生效后,只對新建的表生效。多表空間的數據文件無大小限制,無需設置初始大小,也不需設置文件的最大限制與擴展大小等參數。使用多表空間存儲優勢在于方便單表備份和恢復操作。雖然不能直接復制.frm和.ibd文件達到目的,但可以使用如下命令操作:

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

將備份恢復到數據庫中,單表備份,只能恢復到原來所在的數據庫中,無法恢復到其它數據庫中。如過需要將單表恢復至其它目標數據庫中,則需要通過mysqldump和mysqlimport來實現。

注意:即便多表存儲更有優勢,但是共享表存儲空間依舊是必須的,InnoDB將內部數據字典和在線重做日志存在這個文件中。

梳理一下InnoDB存儲引擎的要點,如下圖1-2-2-2所示:

MySQL存儲引擎怎么理解  mysql 第10張

關于InnoDB存儲引擎就介紹到此處了,更多詳情可以參考MySQL的官方文檔。是不是發現了我只在MyISAM和InnoDB存儲引擎做了總結的思維導圖。沒錯,只做了這兩個,因為這倆最常用。至于為啥是粉色背景,因為老夫有一顆少女心!

2.3、MEMORY存儲引擎

MEMORY存儲引擎使用存在與內存中的內容來創建表。每個MEMORY表只對應一個磁盤文件,格式是.frm。MEMORY類型的表訪問速度極快,存在內存中當然快。這就是Redis為什么這么快?不僅???還能持久?咱回到正題,MEMORY存在內存中并默認使用hash索引,一旦服務關閉,表中數據會丟失。創建一張名為GIRLS的表指定存儲引擎為MEMORY,注意了在UNIX和Linux操作系統下,是對字段和表名大小是寫敏感的,關鍵字不影響。

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;

還記得在介紹存儲引擎做的那會張表格嗎,有介紹到MEMORY支持B TREE索引。雖然MEMORY默認使用的索引是hash索引,但是你可以手動指定索引類型。例如默認手動指定使用關鍵字USING HASH

--?創建索引指定索引類型為hash。create?index?mem_hash?USING?HASH?on?GIRLS(ID);--?查詢索引類型,簡化了一下,只展示了部分參數。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容易丟失數據,但是在啟動MySQL服務的時候,我們可以使用**–init-file選項,將insert into … select或者load data infile**這樣的語句存放在這個指定的文件中,就可以在服務啟動時從持久穩固的數據源裝載表。

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

每個MEMORY表中存放的數據量大小,受max_heap_table_size系統變量約束,初始值為16MB,可以根據需求調整。通過max_rows可以指定表的最大行數。

MEMORY存儲引擎最大特色是快,主要用于內容變化不頻繁的代碼表,或者是為了做統計提供的中間表,效率更高。使用MEMORY時需謹慎,萬一忘了這廝重啟數據就沒了就尷尬了。所以在使用時,考慮好重啟服務器后如何取得數據。

關于MEMORY存儲引擎就介紹到這里,大部分都是些理論知識,更多的需要自己去實踐測試。

2.4、MERGE存儲引擎

MERGE存儲引擎是一組MyISAM表的組合,這些MyISAM表必須結果完全相同,MERGE表本身沒有數據,對MERGE類型的表可以進行查詢、更新、刪除操作,實際上是對內部的MyISAM表進行操作的。對于MERGE類型表的插入操作,通過insert_method子句定義插入的,可以有3個不同的值,使用first或last插入操作對應開始與最后一個表上。如果不定義這個子句,或者定義為NO,表示不能對MERGE表進行操作。

對MERGE表進行DROP操作,只是對MERGE的定義進行刪除,對內部表沒有任何影響。MERGE表上保留兩個文件,文件名以表的名字開始,分別為:

  • .frm文件存儲表定義;

  • .mrg文件包含組合表的信息,包含表組成、插入數據依據。

可以通過修改.mrg文件來修改表,但修改后需要使用flush tables刷新。測試可以先創建兩張存儲引擎為MyISAM的表,再建一張存儲引擎為MERGE存儲引擎的表。如下所示創建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

MySQL存儲引擎怎么理解  mysql 第11張

通過插入數據驗證MERGE確實是一個MyISAM的組合,就是這么神奇。如下所示,只對demo01和demo02進行插入:

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)

插入完數據,分別查看demo01和demo02各只有一條數據,總表可以看到倆分表的全部數據。關鍵是指定了insert_method=last。MERGE表和分區表的區別,MERGE并不能智能地將記錄插入到對應表中,而分區表可以做到。通常我們使用MERGE表來透明的對多個表進行查詢和更新操作??梢宰约涸谙旅鏈y試總表插入數據,看分表的情況,我這里就不貼代碼了。

關于MySQL自帶的幾款常用存儲引擎就介紹到此,感興趣的可以私下測試驗證,更多參考請到官網獲取API或者DOC文檔。

除了MySQL自帶的一些存儲引擎之外,常見優秀的第三方存儲引擎有TokuDB,一款開源的高性能存儲引擎,適用于MySQL和MariaDB。更多詳情可以去TokuDB官網了解喲。

2.5、修改表的存儲引擎

創建新表時,如果不指定存儲引擎,系統會使用默認存儲引擎。在MySQL5.5之前默認的存儲引擎為MyISAM,在MySQL5.5之后默認的存儲引擎為InnoDB。如果想修改默認存儲引擎,可以通過配置文件指定default-table-type的參數。關于存儲引擎的查看,在上面介紹存儲引擎的時候已經有說明了。

方法一:建表即指定當前表的存儲引擎

在創建tolove表的時候就指定存儲引擎,例如指定存儲引擎為MyISAM,默認編碼為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

測試生成的數據量比較大,隨機生成了1千萬條數據。查詢(select)業務相對較多,在建表的時候就指定默認存儲引擎MyISAM,統計(count)的效率很高。以我的渣渣電腦,使用INNODB存儲引擎,統計一次需要2~3秒左右。在上面講到MYISAM的時候,已經將查詢時間進行過對比。

方法二:使用alter table修改當前表的存儲引擎

修改創建的tolove表為MYISAM引擎進行測試。

--?修改創建的tolove表為MYISAM引擎進行測試ALTER?TABLE?test.`tolove`?ENGINE=MYISAM;

修改test表的存儲引擎為INNODB進行測試。

--?修改表的存儲引擎為INNODB進行測試ALTER?TABLE?test.`test`?ENGINE=INNODB;

SHOW CREATE TABLE查詢表的存儲引擎,分別查詢test表和tolove表,在講存儲引擎為MyISAM的時候,有演示過喲!

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

如果在工具中無法看全,可以導出成xml、csv、html等查詢,以下是我查詢出自己創建表時設置的存儲引擎為InnoDB

MySQL存儲引擎怎么理解  mysql 第12張

--?顯示出我創建的test表的SQL語句存儲引擎為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
--?顯示出我創建的tolove表的SQL語句,存儲引擎為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

存儲引擎的修改就介紹這么多,看到我的自增長列(AUTO_INCREMENT)ID到了20000001,之前隨機生成過一次1kw條數據喲!有一部分解釋說明我寫在了代碼塊中,看起來更加舒服。

3、存儲引擎的選擇

在選擇合適的存儲引擎時,應根據應用特點選擇合適的存儲引擎。對于復雜的應用系統,你可以選擇多種存儲引擎滿足不同的應用場景需求。如何選擇合適的存儲引擎呢?存儲引擎的選擇真的很重要嗎?

確實應該好好思考,在并不復雜的應用場景下,可能MyISAM存儲引擎就能滿足日常開銷?;蛟S在另外一種場景之下InnoDB才是最佳選擇,綜合性能更好,滿足更多需求。

MyISAM是MySQL的默認的插件式存儲引擎,是MySQL在5.5之前的默認存儲引擎。如果應用以讀和插入操作居多,只有很少的更新和刪除操作,對事務完整性、并發性沒有很高的需求,此時首選是MyISAM存儲引擎。在web和數據倉庫最常用的存儲引擎之一。

InnoDB用于事務處理應用程序,并且支持外鍵。是MySQL在5.5之后的默認存儲引擎,同樣也是MariaDB在10.2之后的默認存儲引擎,足見InnoDB的優秀之處。如果應用對事務完整性有較高的要求,在并發情況下要求數據高度一致性。數據除了插入和查詢以外,還包括很多的更新和刪除操作,那么InnoDB應該是比較合適的存儲引擎選擇。InnoDB除了有效地降低由于刪除和更新導致的鎖定,還可以確保事務的完整提交(commit)、回滾(rollback)。對類似計費系統或者財務系統等對數據準確性要求比較高的系統,InnoDB也是合適的選擇。插點題外話,本人在工作中使用Oracle數據庫也有一段時間,Oracle的事務確實很強大,處理大數據壓力很強。

MEMORY存儲引擎將所有的數據存在RAM中,在需要快速定位記錄和其它類似數據的環境下,可提供極快的訪問。MEMORY的缺陷在于對表的大小有限制,太大的表無法緩存在內存中,其次是要確保表的數據可以恢復,數據庫異常重啟后表的數據是可恢復的。MEMORY表通常用于更新不太頻繁的小表,快速定位訪問結果。

MERGE用于將一組等同的MyISAM存儲引擎的表以邏輯方式組合在一起,并作為一個對象應用它們。MERGE表的優點在于可以突破對單個MyISAM表大小的限制,并通過將不同的表分布在多個磁盤上,改善MERGE表的訪問效率。對數據局倉庫等VLDB環境很適合。

最后,關于存儲引擎的選擇都是根據別人實際經驗去總結的。并不是一定契合你的應用場景,最終需要用戶對各自應用進行測試,通過測試來獲取最合適的結果。就像我開始列舉的示例,數據量很龐大,對查詢和插入業務比較頻繁,我就開始對MyISAM存儲引擎進行測試,確實比較符合我的應用場景。

關于存儲引擎的選擇,總結簡化如下圖1-3:

MySQL存儲引擎怎么理解  mysql 第13張

4、表的優化(碎片整理)

在開始介紹存MyISAM和InnoDB儲引擎的時候,我也展示過存儲大量數據所占的磁盤空間。使用OPTIMIZE TABLE來優化test數據庫下的test表,優化之前,這張表占據磁盤空間大概在824M;通過優化之后,有明顯的改善,系統回收了沒有利用的空間,test表所耗磁盤空間明顯下降,優化之后只有456M。這里就不貼磁盤所占空間的截圖了。

OPTIMIZE?TABLE?test.`test`;

優化之后,統計(count)數據效率也有所提升,大概在2.5sec左右:

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

優化之前,統計數據大概在3.080 sec。經過對比,效率提升是可觀的。

你也可以使用explain執行計劃對查詢語句進行優化。關于MySQL優化方面的知識,并不是本文的重點,就不做過多描述。

二、索引設計與使用

1、索引簡介

在涉及到MySQL的面試當中,會提到最左前綴索引,都被玩成梗了。

MySQL所有列類型都可以被索引,對相關列合理的使用索引是提高查詢(select)操作性能的最佳方法。根據引擎可以定義每張表的最大索引數和最大索引長度,MySQL的每種存儲引擎(MyISAM、InnoDB等等)對每張表至少支持16個索引,總索引長度至少為256字節。大多數存儲引擎有更高的限制。

MyISAM和InnoDB存儲引擎默認創建的表都是BTREE索引。在MySQL8.0之前是不只支持函數索引的,MySQL5.7推出了虛擬列功能,在MySQL8.0開始支持函數索引,也是8.0版本的新特性之一。

MySQL支持前綴索引,對索引字段的前N個字符創建索引,前綴索引長度和存儲引擎有關。有很多人經常會問到,MySQL支持全文索引嗎?我的回答是:支持。MySQL5.6之前MyISAM存儲引擎支持全文索引(FULLTEXT),5.6之后InnoDB開始支持全文索引。

為test表創建10個字節的前綴索引,創建索引的語法如下:

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

同樣可以使用alter table語句去新增索引,給girl表的字段girl_name新增一個索引:

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

對于使用索引的驗證可以使用explain執行計劃去判斷。關于索引的簡述就介紹這么多,更多基礎知識可以參考官方文檔或者權威書籍。

2、設計索引原則

索引的設計可以遵循一些已有的原則,創建索引的時候請盡量考慮符合這些原則。有助于提升索引的使用效率。

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

使用唯一索引??紤]某列中值的分布,索引列的基數越大,索引效果越好。

使用短索引。如果對字符串列進行索引,應指定一個前綴長度。比如char(100),思考一下,重復度的問題。是全部索引來的快,還是對部分字符進行索引更優?

利用最左前綴。在創建一個N列的索引時,實際上是創建了MySQL可利用的N個索引。多列索引可以起幾個索引的作用,利用索引中最左邊的列表來匹配行。這樣的列集稱為最左前綴。都快被涉及到MySQL的面試玩成梗了,哈哈。

注意不要過度使用索引。不要以為使用索引好處多多,就在所有的列上全部使用索引,過度使用索引反而會適得其反。每個額外的索引會占用磁盤空間,對磁盤寫操作性能造成損耗。在重構的時候,索引也得更新,造成不必要的時間浪費。

InnoDB存儲引擎的表。對于使用InnoDB存儲引擎的表,記錄默認按一定的順序保存。有如下幾種情況:

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

  • 在沒有主鍵,但有唯一索引的情況下,會遵循唯一索引順序保存;

  • 既沒有主鍵又沒有唯一索引,表中會自動生成一個內部列,并遵循這個列的順序保存。

以上就是對索引設計原則的簡單介紹。

3、B-TREE與HASH索引

使用這些索引時,應該考慮索引是否當前使用條件下生效!在使用MEMORY存儲引擎的表中可以選擇使用HASH索引或者B-TREE索引,兩種不同的索引有其各自適用的范圍。

HASH索引。只用于這類關系操作符:=、<=>的操作比較,優化器不能使用HASH索引來加速order by操作。MySQL不能確定在兩個值之間大約有多少行。

B-TREE索引。對于B-TREE索引,使用>、<、>=、<=、BETWEEN、!=或者<>、亦或是使用like ‘condition’。其中’condition’不以通配符開始的操作符時,都可以使用相關列上的索引。

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

三、數據類型選擇

  • 字符串類型char與varchar

  • 浮點數和定點數

  • 日期類型

工作中,個人使用經驗。Oracle里面使用BLOB存儲大字段比較頻繁,TEXT相對少見,使用VARCHAR2類型比較多。但在MySQL中是不支持VARCHAR2類型的。

1、CHAR與VARCHAR

char和varchar類型類似,用于存儲字符串,但它們保存和檢索的方式不同。char類型屬于固定長度(定長)類型的字符串,varchar屬于可變長度的字符串類型。在MySQL的嚴格模式中,使用的char和varchar,超過列長度的值不會被保存,并且出現錯誤提示。

char優缺點。char是固定長度,處理速度比varchar要快,但缺點是浪費存儲空間,沒有varchar那么靈活。varchar。隨著MySQL的不斷升級,varchar類型也在不斷優化,性能也在提升,被用于更多的應用中。

MyISAM存儲引擎:建議使用固定長度的數據列代替可變長度的數據列。

InnoDB存儲引擎:建議使用VARCHAR類型。

MEMORY存儲引擎:使用固定長度數據類型存儲。

2、TEXT與BLOB

一般情況,存儲少量的字符串時,會選擇char和varchar類型。而在保存較大文本時,通常選擇TEXT或者BLOB大字段,二者之間的區別在于BLOB能存二進制數據,比如:照片,TEXT類型只能存字符數據。這也是為什么我在開始的時候提及到個人工作中見到BLOB類型相對較多。TEXT和BLOB還包括不同類型:

  • TEXT、LONGTEXT、MEDIUMINT、MEDIUMTEXT、TINYTEXT;

  • BLOB、LONGBLOB、MEDIUMBLOB、TINYBLOB。

區別在于存儲文本長度和字節不同。

需要注意的點

  • BLOB和TEXT值會引起一些性能問題,尤其是執行大量刪除操作時;

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

  • 在不必要的時候避免檢索大字段;

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

3、浮點數與定點數

浮點類型一般用于表示含有小數部分的值。列舉一些示例:

  • double類型:用于浮點數(雙精度);

  • decimal類型:MySQL中表示定點數;

  • float類型:用于浮點數(單精度)。

學過Java語言的同學,對這些浮點類型并不陌生吧。

注意點:浮點數存在誤差問題,對精度比較敏感的數據,避免對浮點類型做比較。

4、日期類型

談到日期類型,又讓我想起了7年前學Java語言的時候,會寫一個工具類(Utils.java),將常用的處理日期的方法寫進去然后調用。經常用到的一個方法(SimpleDateFormat),對時間戳進行轉換格式化。

MySQL中常用的日期類型有:

  • DATE

  • DATETIME

  • TIME

  • TIMESTAMP

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

四、字符集(字符編碼)設置

從本質上來說,計算機只能是被二進制代碼(010101)。因此,不論是計算機程序還是處理的數據,最終都會轉換成二進制代碼,計算機才能識別。為了讓計算機不僅能做科學計算,也能處理文字信息,于是計算機字符集誕生了。

字符編碼(英語:Character encoding)、字集碼是把字符集中的字符編碼為指定集合中某一對象)(例如:比特模式、自然數序列、8位組或者電脈沖),以便文本在計算機中存儲和通過通信網絡的傳遞。常見的例子包括將拉丁字母表編碼成摩斯電碼和ASCII。其中,ASCII將字母、數字和其它符號編號,并用7比特的二進制來表示這個整數。通常會額外使用一個擴充的比特,以便于以1個字節的方式存儲。

在計算機技術發展的早期,如ASCII(1963年)和EBCDIC(1964年)這樣的字符集逐漸成為標準。但這些字符集的局限很快就變得明顯,于是人們開發了許多方法來擴展它們。對于支持包括東亞CJK字符家族在內的寫作系統的要求能支持更大量的字符,并且需要一種系統而不是臨時的方法實現這些字符的編碼。

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

1、Unicode

Unicode是什么?是統一編碼,是計算機科學領域的業界標準。從最初的的1.0.0到目前最新的14.0版本,對應ISO/IEC 10646-N:xxxx。說一下UTF-8、UTF-16、UTF-16LE、UTF-32BE、UTF-32LE等等大家應該很熟悉了。

2、常見字符集

常見的字符集:

  • UTF-8:泛用性最廣泛;

  • GBK:對中文支持非常友好,在GB2312基礎上進行了擴充;

  • GB2312:對中文字符集支持,;

  • GB18030:支持中文字符集,解決GBK強制力不夠的問題。

3、MySQL支持的字符集

通過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查看所有字符集和字符集默認的校對規則。

查看相關字符集校對規則,可以使用SHOW COLLATION配合 LIKE模糊搜索gbk字符集。

SHOW?COLLATION?LIKE?'gbk%';

MySQL字符集設置:默認可以過配置文件設置character-set-server參數。

  • Linux發行版中安裝一般在my.cnf中配置;

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

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

額外再提一點,判斷字符集所占字節,可以使用函數LENGTH():

SELECT?LENGTH('中');

如果使用的是UTF-8編碼,默認漢字是占用3個字節,使用GBK則占用2個字節。字符編碼就介紹到這里。

五、MySQL示例數據庫sakila

視圖、存儲過程、函數、觸發器。這里給出我自己隨機生成海量數據用到的函數和存儲過程。

1、函數

創建函數,使用DELIMITER聲明,使用CREATE FUNCTION創建函數,tolove表的創建在介紹存儲引擎過程中已經有展示過。

/**?創建函數?生成學號?**/DELIMITER?$CREATE?FUNCTION?rand_number()?RETURNS?INTBEGIN
	DECLARE?i?INT?DEFAULT?0;
	SET?i=?FLOOR(1+RAND()*100);
	RETURN?i;END?$DELIMITER?$

創建函數:用于生成姓名隨機字符串

/**?創建函數?生成姓名隨機字符串?**/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、存儲過程

創建存儲過程,使用CREATE PROCEDURE創建:

/**?創建存儲過程?**/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調用存儲過程,隨機生成百萬數據:

/**?調用存儲過程?**/CALL?insert_tolove(100*10000);

刪除函數或者存儲過程,使用DROP關鍵字

--?刪除函數rand_nameDROP?FUNCTION?rand_name;
--?刪除存儲過程insert_toloveDROP?PROCEDURE?insert_tolove;

3、觸發器

創建觸發器使用CREATE TRIGGER,這里就引用sakila數據庫實例。如果存在,使用了判斷語句 IF EXISTS,然后刪除DROP TRIGGER已經存在的觸發器。

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數據庫

在文中我反復提到了MySQL的示例數據庫sakila,是一個完整的學習MySQL的好例子。包含了視圖、存儲過程、函數和觸發器??梢匀ySQL的官網獲取SQL腳本。

MySQL存儲引擎怎么理解  mysql 第14張

文末留一個神秘的參數,通過此種方式可以過濾你不想看到的內容喲!無論在手機端或者PC端都可生效,親測可用。

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

感謝各位的閱讀,以上就是“MySQL存儲引擎怎么理解”的內容了,經過本文的學習后,相信大家對MySQL存儲引擎怎么理解這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是蝸牛博客,小編將為大家推送更多相關知識點的文章,歡迎關注!

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:niceseo99@gmail.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

評論

日本韩欧美一级A片在线观看