1、查看當前數(shù)據(jù)庫支出的存儲引擎
方法1：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
mysql> show engines \G;*************************** 1. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO*************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO*************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO XA: NO Savepoints: NO*************************** 5. row *************************** Engine: MyISAM Support: DEFAULT Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO*************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO*************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO*************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO*************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL9 rows in set (0.00 sec)ERROR:No query specified |
方法2：
（Value 顯示為“DISABLED”的記錄表示支持該存儲引擎，但是數(shù)據(jù)庫啟動的時候被禁用。）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> show variables like 'have%';+------------------------+----------+| Variable_name | Value |+------------------------+----------+| have_compress | YES || have_crypt | NO || have_dynamic_loading | YES || have_geometry | YES || have_openssl | DISABLED || have_profiling | YES || have_query_cache | YES || have_rtree_keys | YES || have_ssl | DISABLED || have_statement_timeout | YES || have_symlink | YES |+------------------------+----------+11 rows in set, 1 warning (0.00 sec) |
2、ENGINE={存儲引起類型} 創(chuàng)建表的時候，設置存儲引擎
|
1
2
3
4
5
6
7
8
9
10
|
mysql> create table a( -> i bigint(20) not null auto_increment, -> primary key (i) -> ) engine=myisam default charset=gbk;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 3Current database: testQuery OK, 0 rows affected (1.33 sec) |
3、alter able tablename engine={存儲引起類型} 修改表為其他存儲引擎
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> alter table a engine=innodb;Query OK, 0 rows affected (1.70 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table a \G;*************************** 1. row *************************** Table: aCreate Table: CREATE TABLE `a` ( `i` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`)) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.14 sec) |
3.1 常用存儲引擎的對比
| 特點 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
| 存儲限制 | 有 | 64TB | 有 | 沒有 | 有 |
| 事務安全 | 支持 | ||||
| 鎖機制 | 表鎖 | 行鎖 | 表鎖 | 表鎖 | 表鎖 |
| B 樹索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 數(shù)據(jù)緩存 | 支持 | 支持 | 支持 | ||
| 索引緩存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 數(shù)據(jù)可壓縮 | 支持 | ||||
| 空間使用 | 低 | 高 | N/A | 低 | 低 |
| 內存使用 | 低 | 高 | 中等 | 低 | 高 |
| 批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外鍵 | 支持 |
3.2 常用存儲引擎學習（MyISAM、InnoDB、MEMORY 和 MERGE）
MyISAM：
默認的MySQL存儲引擎，不支持事務和外鍵
優(yōu)點：訪問速度快
每個MyISAM在磁盤上存儲成3個文件，其文件名和表名都相同。擴展名分別是：
.frm (存儲表定義)
.MYD (MYData，存儲數(shù)據(jù))
.MYI (MYIndex，存儲索引)
(數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分布 IO，獲得更快的速度。)
InnoDB:
處理效率較差，占用較多的空間用來保留數(shù)據(jù)和索引
優(yōu)點：具有提交、回滾、奔潰恢復能力的事務安全、唯一支持外鍵的存儲引擎
自動增長列：InnoDB 表的自動增長列可以手工插入，但是插入的值如果是空或者 0，則實際插入的將是自動增長后的值
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql> create table autoincre_demo( -> i smallint not null auto_increment, -> name varchar(10),primary key(i) -> )engine=innodb;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 5Current database: testQuery OK, 0 rows affected (1.19 sec)mysql> insert into autoincre_demo values(1,"121"),(0,"dddf"),(null,"fdf");Query OK, 3 rows affected (0.59 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from autoincre_demo;+---+------+| i | name |+---+------+| 1 | 121 || 2 | dddf || 3 | fdf |+---+------+3 rows in set (0.00 sec) |
alter table tabename auto_increment=n 設置自動增長列的初始值（此值默認從1開始）
可以使用 LAST_INSERT_ID()查詢當前線程最后插入記錄使用的值。如果一次插入了多條記錄，那么返回的是第一條記錄使用的自動增長值。
下面的例子演示了使用 LAST_INSERT_ID()的情況：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> insert into autoincre_demo(name) values('3');Query OK, 1 row affected (0.36 sec)mysql> select LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+| 15 |+------------------+1 row in set (0.00 sec)mysql> insert into autoincre_demo(name) values('3'),('6'),('323'),('21');Query OK, 4 rows affected (0.09 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+| 16 |+------------------+1 row in set (0.00 sec) |
外鍵約束：
在創(chuàng)建外鍵的時候，要求父表必須有對應的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuàng)建對應的索引。
下面是樣例數(shù)據(jù)庫中的兩個表，country 表是父表，country_id 為主鍵索引，city 表是子表，country_id 字段對 country 表的 country_id 有外鍵。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> create table country( -> country_id smallint unsigned not null auto_increment, -> country varchar(50) not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, -> primary key(country_id) -> )engine=innodb default charset=utf8;Query OK, 0 rows affected (0.86 sec)mysql> CREATE TABLE city ( -> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> city VARCHAR(50) NOT NULL, -> country_id SMALLINT UNSIGNED NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (city_id), -> KEY idx_fk_country_id (country_id), -> CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON -> DELETE RESTRICT ON UPDATE CASCADE -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (3.22 sec) |
在創(chuàng)建索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包 RESTRICT、CASCADE、SET NULL 和 NO ACTION
-
RESTRICT和NO ACTION相同，是指限制在子表有關聯(lián)記錄的情況下父表不能更新 -
CASCADE表示父表在更新或者刪除時，更新或者刪除子表對應記錄； -
SET NULL則表示父表在更新或者刪除的時候，子表的對應字段被SET NULL。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
mysql> select * from country;+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 1 | AAA | 2021-06-16 15:09:22 |+------------+---------+---------------------+1 row in set (0.00 sec)mysql> select * from city;+---------+------+------------+---------------------+| city_id | city | country_id | last_update |+---------+------+------------+---------------------+| 10 | bb | 1 | 2021-06-16 15:11:45 |+---------+------+------------+---------------------+1 row in set (0.00 sec)mysql> delete from country where country_id = 1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)mysql> update country set country_id = 10000 where country_id = 1;Query OK, 1 row affected (0.62 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from country;+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 10000 | AAA | 2021-06-16 15:13:35 |+------------+---------+---------------------+1 row in set (0.00 sec)mysql> select * from city -> ;+---------+------+------------+---------------------+| city_id | city | country_id | last_update |+---------+------+------------+---------------------+| 10 | bb | 10000 | 2021-06-16 15:11:45 |+---------+------+------------+---------------------+1 row in set (0.00 sec) |
在導入多個表的數(shù)據(jù)時，如果需要忽略表之前的導入順序，可以暫時關閉外鍵的檢查；同樣，在執(zhí)行 LOAD DATA 和 ALTER TABLE 操作的時候，可以通過暫時關閉外鍵約束來加快處理的速度，關閉的命令是“SET FOREIGN_KEY_CHECKS = 0;”，執(zhí)行完成之后，通過執(zhí)行“SETFOREIGN_KEY_CHECKS = 1;”語句改回原狀態(tài)。
查看表外鍵信息：show create table 或者 show table status 命令
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> show table status like 'city' \G;*************************** 1. row *************************** Name: city Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1 Avg_row_length: 16384 Data_length: 16384Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 11 Create_time: 2021-06-16 15:02:17 Update_time: 2021-06-16 15:13:35 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:1 row in set (0.43 sec)ERROR:No query specified |
存儲方式：
-
（1）、使用共享表空間存儲：表的表結構保存在.frm文件中，數(shù)據(jù)+索引存在
innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個文件 -
（2）、使用多表空間存儲：表的表結構也保存在.frm文件中，數(shù)據(jù)+索引單獨存在.
ibd中；如果是分區(qū)表，則每個分區(qū)對應單獨的.ibd文件，文件名是：“表名+分區(qū)名”，可以在創(chuàng)建分區(qū)的時候指定每個分區(qū)的數(shù)據(jù)文件的位置，以此來將表的 IO 均勻分布在多個磁盤上
MEMORY：
使用存在內存中的內容來創(chuàng)建表
每個 MEMORY 表只實際對應一個磁盤文件，格式是.frm。
優(yōu)點：訪問速度快（數(shù)據(jù)存儲在內存中），并且默認使用HASH索引，服務關閉則數(shù)據(jù)丟失
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
mysql> CREATE TABLE tab_memory ENGINE=MEMORY -> SELECT city_id,city,country_id -> FROM city GROUP BY city_id;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 12Current database: testQuery OK, 1 row affected (0.62 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from tab_memory;+---------+------+------------+| city_id | city | country_id |+---------+------+------------+| 10 | bb | 10000 |+---------+------+------------+1 row in set (0.00 sec)mysql> show table status like 'tab_memory' \G*************************** 1. row *************************** Name: tab_memory Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 155 Data_length: 520320Max_data_length: 65011650 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-06-16 15:28:58 Update_time: NULL Check_time: NULL Collation: utf8_unicode_ci Checksum: NULL Create_options: Comment:1 row in set (0.00 sec) |
給表創(chuàng)建索引的時候可以指定索引類型是HASH或是BTREE
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
mysql> create index mem_hash using hash on tab_memory(city_id);ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 13Current database: testQuery OK, 1 row affected (0.63 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show index from tab_memory \G;*************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: NULL Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: HASH Comment:Index_comment:1 row in set (0.32 sec)ERROR:No query specifiedmysql> drop index mem_hash on tab_memory;Query OK, 1 row affected (0.31 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> create index mem_hash using btree on tab_memory(city_id);Query OK, 1 row affected (0.16 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show index from tab_memory \G;*************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:Index_comment:1 row in set (0.00 sec)ERROR:No query specified |
MERGE：
此存儲殷勤是一組MyISAM表的組合
對 MERGE 類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內部的實際的 MyISAM 表進行的。
對于 MERGE 類型表的插入操作，是通過INSERT_METHOD 子句定義插入的表，可以有 3 個不同的值，使用 FIRST 或 LAST 值使得插入操作被相應地作用在第一或最后一個表上，不定義這個子句或者定義為 NO，表示不能對這個 MERGE 表執(zhí)行插入操作。
可以對 MERGE 表進行 DROP 操作，這個操作只是刪除 MERGE 的定義，對內部的表沒有任何的影響。
存儲文件：一個.frm 文件存儲表定義，另一個.MRG 文件包含組合表的信息，包括 MERGE 表由哪些表組成、插入新的數(shù)據(jù)時的依據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> create table payment_2020( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> KEY idx_fk_country_id (country_id) -> )engine=myisam;Query OK, 0 rows affected (0.25 sec)mysql> create table payment_2021( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> KEY idx_fk_country_id (country_id) -> )engine=myisam;Query OK, 0 rows affected (0.54 sec)mysql> CREATE TABLE payment_all( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> INDEX(country_id) -> )engine=merge union=(payment_2020,payment_2021) INSERT_METHOD=LAST;Query OK, 0 rows affected (0.47 sec) |
分別向2020和2021中插入數(shù)據(jù)，并查詢
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
mysql> insert into payment_2020 values(1,'2020-06-01',100000),(2,'2020-06-15',150000);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into payment_2021 values(1,'2021-04-20',35000),(2,'2021-06-15',220000);Query OK, 2 rows affected (0.03 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from payment_2020;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2020-06-01 00:00:00 | 100000.00 || 2 | 2020-06-15 00:00:00 | 150000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> select * from payment_2021;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2021-04-20 00:00:00 | 35000.00 || 2 | 2021-06-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> select * from payment_all;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2020-06-01 00:00:00 | 100000.00 || 2 | 2020-06-15 00:00:00 | 150000.00 || 1 | 2021-04-20 00:00:00 | 35000.00 || 2 | 2021-06-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+4 rows in set (0.00 sec) |
可以發(fā)現(xiàn)，payment_all 表中的數(shù)據(jù)是 payment_2020 和 payment_2021 表的記錄合并后的結果集
下面向 MERGE 表插入一條記錄，由于 MERGE 表的定義是 INSERT_METHOD=LAST，就會向最后一個表中插入記錄，所以雖然這里插入的記錄是 2006 年的，但仍然會寫到 payment_2021表中。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
mysql> insert into payment_all values(3,'2020-03-30',12333131);Query OK, 1 row affected (0.31 sec)mysql> select * from payment_all;+------------+---------------------+-------------+| country_id | payment_date | amount |+------------+---------------------+-------------+| 1 | 2020-06-01 00:00:00 | 100000.00 || 2 | 2020-06-15 00:00:00 | 150000.00 || 1 | 2021-04-20 00:00:00 | 35000.00 || 2 | 2021-06-15 00:00:00 | 220000.00 || 3 | 2020-03-30 00:00:00 | 12333131.00 |+------------+---------------------+-------------+5 rows in set (0.00 sec)mysql> select * from payment_2021;+------------+---------------------+-------------+| country_id | payment_date | amount |+------------+---------------------+-------------+| 1 | 2021-04-20 00:00:00 | 35000.00 || 2 | 2021-06-15 00:00:00 | 220000.00 || 3 | 2020-03-30 00:00:00 | 12333131.00 |+------------+---------------------+-------------+3 rows in set (0.00 sec)mysql> select * from payment_2020;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2020-06-01 00:00:00 | 100000.00 || 2 | 2020-06-15 00:00:00 | 150000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec) |
到此這篇關于MySQL表類型 存儲引擎 的選擇的文章就介紹到這了,更多相關MySQL表類型 存儲引擎內容請搜索服務器之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/19930521zhang/p/14756425.html
