中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

MySQL常用存儲引擎功能與用法詳解

發布時間:2020-10-08 18:48:09 來源:腳本之家 閱讀:159 作者:編程人,在天涯 欄目:MySQL數據庫

本文實例講述了MySQL常用存儲引擎功能與用法。分享給大家供大家參考,具體如下:

MySQL存儲引擎主要有兩大類:

1. 事務安全表:InnoDB、BDB。

2. 非事務安全表:MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。

MySQL默認的存儲引擎是MyISAM(5.7版本中默認為InnoDB)。

配置文件中設置默認存儲引擎的參數:default-table-type。

查詢當前數據庫支持的存儲引擎:

show engines;
show variables like 'have%';

查看當前的默認存儲引擎:

show variables like '%table_type%';

創建新表時指定存儲引擎:

create table(...) engine=MyISAM;

下面詳細介紹4個比較常用的存儲引擎:MyISAM、InnoDB、MEMORY和MERGE。

一、MyISAM

1. 數據文件:

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

(1).frm:存儲數據表結構定義。

(2).MYD:存儲表數據。

(3).MYI:存儲表索引。

其中,數據文件和索引文件可以放置在不同的目錄,平均分布IO,獲得更快的速度。指定索引文件和數據文件的路徑,需要在創建表的時候通過data directory和index directory語句指定。(文件路徑需要是絕對路徑并且具有訪問的權限)

MyISAM類型的表可能會損壞,原因可能是多種多樣的,損壞后的表可能不能訪問,會提示需要修復或者訪問后返回錯誤的結果。可以使用check table語句來檢查MyISAM表的健康,并用repair table語句修復已經損壞的MyISAM表。

 2. 存儲格式:

(1)靜態表(默認):字段都是非變長的(每個記錄都是固定長度的)。存儲非常迅速、容易緩存,出現故障容易恢復;占用空間通常比動態表多。

(2)動態表:占用的空間相對較少,但是頻繁的更新刪除記錄會產生碎片,需要定期執行optimize table或myisamchk -r命令來改善性能,而且出現故障的時候恢復比較困難。

(3)壓縮表:使用myisampack工具創建,占用非常小的磁盤空間。因為每個記錄是被單獨壓縮的,所以只有非常小的訪問開支。

靜態表的數據在存儲的時候會按照列的寬度定義補足空格,在返回數據給應用之前去掉這些空格。如果需要保存的內容后面本來就有空格,在返回結果的時候也會被去掉。(其實是數據類型char的行為,動態表中若有這個數據類型也同樣會有這個問題)

(靜態表和動態表是根據正使用的列的類型自動選擇的。)

 3. 優劣勢:

(1)優勢:訪問的速度快。

(2)不支持事務、也不支持外鍵。

 4. 適用情況:

如果應用是以讀操作和插入操作為主,只有很少的更新和刪除操作,并且對事務的完整性、并發性要求不是很高,那么選擇這個存儲引擎是非常適合的。MyISAM 是在Web、數據倉庫和其他應用環境下最常使用的存儲引擎之一。

二、InnoDB

1. 存儲方式:

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

(1)使用共享表空間存儲:這種方式創建的表結構保存在.frm文件中,數據和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中,可以是多個文件。

(2)使用多表空間存儲:這種方式創建的表結構仍然保存在.frm文件中,但是每個表的數據和索引單獨保存在.idb文件中。如果是個分區表,則每個分區對應單獨的.idb文件,文件名是“表名+分區名”,可以在創建分區的時候指定每個分區的數據文件的位置,以此來將表的IO均勻分布在多個磁盤上。

要使用多表空間的存儲方式,需要設置參數innodb_file_per_table并重啟服務器后才可以生效,而且只對新建的表生效。多表空間的數據文件沒有大小限制,不需要設置初始大小,也不需要設置文件的最大限制、擴展大小等參數。即使在多表空間的存儲方式下,共享表空間仍然是必須的,InnoDB把內部數據詞典和工作日志放在這個文件中,所以備份使用多表空間特性的表時直接復制.idb文件是不行的,可以通過命令將數據備份恢復到數據庫中:

ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;

但是這樣只能恢復到表原來所在數據庫中,如果需要恢復到其他數據庫則需要通過mysqldump和mysqlimport來實現。

2. 數據文件:

InnoDB的數據文件由表的存儲方式決定。

(1)共享表空間文件:由參數innodb_data_home_dir和innodb_data_file_path定義,用于存放數據詞典和日志等。

(2).frm:存放表結構定義。

(3).idb:使用多表空間存儲方式時,用于存放表數據和索引,若使用共享表空間存儲則無此文件。

3. 外鍵約束:

InnoDB是MySQL唯一支持外鍵約束的引擎。外鍵約束可以讓數據庫自己通過外鍵保證數據的完整性和一致性,但是引入外鍵會使速度和性能下降。在創建外鍵的時候,要求父表必須有對應的索引,子表在創建外鍵的時候也會自動創建對應的索引。

外鍵約束使用示例:

CREATE TABLE `dep` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` smallint(6) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_fk_dep_id` (`dep_id`),
 CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

KEY :定義索引約束名稱。

CONSTRAINT:定義外鍵約束名稱。(在數據庫中應是唯一的,若不指定系統會自動生成一個約束名)

ON:指定父表操作對子表的影響(不定義默認采用restrict)。

Restrictno action:在子表有相關記錄的情況下父表不能更新或刪除。

Cascade:在父表更新或刪除時,同時更新或刪除子表對應的記錄。

Set null:在父表更新或刪除的時候,子表的對應字段被設置為null。

當某個表被其他表創建了外鍵參照,那么這個表的對應索引或者主鍵禁止被刪除。在導入多個表的數據時,如果需要忽略表的導入順序,可以暫時關閉外鍵的檢查;在執行load data和alter table操作的時候,也可以通過暫時關閉外鍵約束來加快處理的速度。

關閉命令:

set foreign_key_checks=0;

開啟命令:

set foreign_key_checks=1;

4. 優劣勢:

(1)優勢:提供了具有提交、回滾和崩潰恢復能力的事務安全。

(2)劣勢:相比MyISAM,InnoDB寫的處理效率差一些,并且會占用更多的磁盤空間以保留數據和索引。

5. 適用情況:

如果應用對事務的完整性有比較高的要求,在并發條件下要求數據的一致性,數據操作除了插入和查詢以外,還包括很多的更新、刪除操作,那么InnoDB 存儲引擎應該是比較合適的選擇。InnoDB 存儲引擎除了有效地降低由于刪除和更新導致的鎖定,還可以確保事務的完整提交和回滾,對于類似計費系統或者財務系統等對數據準確性要求比較高的系統,InnoDB 都是合適的選擇。

三、MEMORY

1. 數據文件:

每個MEMORY表只對應一個.frm磁盤文件,用于存儲表的結構定義,表數據存放在內存中。默認使用HASH索引,而不是BTREE索引。

2. 優劣勢:

(1)優勢:訪問速度非常快,因為數據是存在內存中的。

(2)劣勢:一旦服務關閉,表中的數據就會丟失;對表的大小有限制。

3. 適用情況:

Memory存儲引擎主要用在那些內容變化不頻繁的代碼表,或者作為統計操作的中間結果表,便于高效地對中間結果進行分析并得到最終的統計結果。

四、MERGE

1. 引擎原理:

Merge存儲引擎是一組MyISAM表的組合,這些MyISAM表必須結構完全相同,merge表本身并沒有數據,對merge類型的表可以進行查詢、更新、刪除的操作,這些操作實際上是對內部的實際的MyISAM表進行的。

通過insert_method子句定義merge表的插入操作:使用first或last可以使插入操作被相應地作用在第一或最后一個表上,不定義或定義為No表示不能對這個merge表進行插入操作。對merge表進行drop操作只是刪除了merge的定義,對內部的表沒有任何影響。

2. 數據文件:

(1).frm:存儲表定義。

(2).MRG:存儲組合表的信息,包括merge表由哪些表組成、插入新數據時的依據。可以通過修改.mrg文件來修改merge表,但是修改后要通過flush tables刷新。

3. 使用示例:

CREATE TABLE `m1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

4. 適用情況:

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

更多關于MySQL相關內容感興趣的讀者可查看本站專題:《MySQL存儲過程技巧大全》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》及《MySQL數據庫鎖相關技巧匯總》

希望本文所述對大家MySQL數據庫計有所幫助。

向AI問一下細節

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

AI

德清县| 太仆寺旗| 广灵县| 密山市| 上林县| 朝阳市| 南溪县| 额尔古纳市| 洛宁县| 华容县| 溆浦县| 绵阳市| 成都市| 德阳市| 潢川县| 陆良县| 岳池县| 湘乡市| 郁南县| 萍乡市| 长海县| 鸡泽县| 大姚县| 鹿邑县| 财经| 张掖市| 宜宾县| 阜宁县| 赞皇县| 都匀市| 辽宁省| 喜德县| 阿瓦提县| 垦利县| 罗甸县| 大港区| 广丰县| 武鸣县| 甘孜县| 临高县| 长治市|