MySQL存储引擎

JSON 2024-02-19 10:54:32 209

存储引擎(底层软件组件)是 MySQL 组件,它可以处理不同表类型的 SQL 操作,以存储和管理数据库中的信息。InnoDB 是最常用的通用存储引擎,从 MySQL 5.5 及更高版本开始它是默认引擎。MySQL 中有许多可用的存储引擎,它们用于不同的目的。

版本: MySQL 5.6

MySQL的存储引擎

 引擎  描述
InnoDB 这是 MySQL 5.5 及更高版本的默认存储引擎。它提供事务安全(符合 ACID)表,支持外键引用完整性约束。它支持提交、回滚和崩溃恢复功能来保护数据。它还支持行级锁定。在多用户环境中使用时,它的“一致的非锁定读取”可提高性能。它将数据存储在聚集索引中,从而减少基于主键的查询的 I/O。
MyISAM 该存储引擎管理非事务性表,提供高速存储和检索,支持全文搜索。
MEMORY 提供内存表,以前称为 HEAP。它将所有数据存储在 RAM 中,以便比将数据存储在磁盘上更快的访问速度。对于快速查找参考数据和其他相同数据很有用。
MERGE 将多个相似的 MyISAM 表分组为单个表,可以处理非事务性表(默认情况下包括在内)。
EXAMPLE 您可以使用此引擎创建表,但不能存储或获取数据。这样做的目的是教开发人员如何编写新的存储引擎。
ARCHIVE 用于存储大量数据,不支持索引。
 CSV 将数据以逗号分隔值格式存储在文本文件中。
 BLACKHOLE 接受要存储的数据,但始终返回空。
 FEDERATED 将数据存储在远程数据库中。

设置存储引擎

在 CREATE TABLE STATEMENT 中,您可以添加 ENGINE 表选项来提及存储引擎。请参阅以下 CREATE TABLE 语句,其中使用了不同的引擎:

CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

在MySQL 5.6中,默认引擎是InnoDB。如果您未在 ENGINE 选项中提及其他引擎名称,则使用默认存储引擎。您可以使用 --default-storage-engine 服务器启动选项(命令行格式)或通过在 my.cnf 配置文件中设置 default-storage-engine 选项来指定默认引擎。

您可以通过使用 set 命令设置 default_storage_engine 变量来设置当前会话的默认存储引擎。

SET default_storage_engine=ARCHIVE;

如果要将表从一种存储引擎转换为另一种存储引擎,请使用 ALTER TABLE 语句。请参阅以下声明:

ALTER TABLE table1 ENGINE = InnoDB;

为了存储新表的表和列定义,MySQL 总是创建一个 .frm 文件。根据存储引擎的不同,表的索引和数据可能存储在一个或多个其他文件中。服务器在存储引擎级别之上创建 .frm 文件。

MySQL:InnoDB 存储引擎

InnoDB是MySQL的一个平衡高可靠性和高性能的存储引擎。从 MySQL 5.5 及更高版本开始,它是默认存储引擎。

InnoDB存储引擎的特点:

 存储限制  64TB 事务  是否支持  锁定粒度  行    
 MVCC(多版本并发控制)  YES  地理空间数据类型支持  YES  地理空间索引支持  NO
 B 树索引  YES  T树索引  NO  哈希索引  NO
 全文搜索索引  YES  聚集索引  YES  数据缓存  YES
 索引缓存  YES  压缩数据  YES  加密数据  YES
 集群数据库支持  NO  复制支持      YES  外键支持  YES
 备份/时间点恢复  YES  查询缓存支持  YES  更新数据字典的统计信息  YES

InnoDB存储引擎的优点

InnoDB 在处理大数据量时具有最高性能。

其DML操作(添加、更新和删除数据)与ACID(原子、一致、隔离和持久)模型兼容,事务具有提交、回滚和崩溃恢复功能以保护用户数据。

行级锁定(锁放置在单个记录(行)上)系统提高了多用户并发性和性能。当事务提交或中止时,事务持有的所有 InnoDB 锁都会被释放。

InnoDB 表在磁盘上排列数据以根据主键优化查询。

InnoDB 支持 FOREIGN KEY 约束来维护数据完整性。因此,插入、更新和删除都会受到检查,以确保它们不会导致不同表之间的不一致。

可以在同一语句中将 InnoDB 表与其他 MySQL 存储引擎的表混合使用。例如,您可以使用联接操作将 InnoDB 和 MEMORY 表中的数据合并到单个查询中。

创建 InnoDB 表:

使用 CREATE TABLE 语句创建 InnoDB 表,无需任何特殊子句。从 MySQL 5.5 开始,它是默认的 MySQL 存储引擎。在 MySQL 5.6 中,发出不带 ENGINE= 子句的 CREATE TABLE 语句会创建一个 InnoDB 表。这是一个例子:

mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1));
Query OK, 0 rows affected (1.11 sec)
mysql> DESC table1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col1  | int(11)  | NO   | PRI | 0       |       |
| col2  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.21 sec)

以下 SHOW TABLE STATUS 语句显示表的属性(属于“tutorial”数据库)。

mysql> SHOW TABLE STATUS FROM tutorial;
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | |
| table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.07 sec)

在 InnoDB 中处理 AUTO_INCRMENT:

InnoDB 提供了一种方法,可以提高 SQL 语句的可伸缩性和性能,这些语句将行插入到具有 AUTO_INCRMENT 列的表中。要将 AUTO_INCRMENT 机制与 InnoDB 表一起使用,必须将 AUTO_INCRMENT 列(示例中的 col1)定义为索引的一部分。请参阅以下示例:

mysql> CREATE TABLE table1 (col1 INT(10) NOT NULL AUTO_INCREMENT, 
col2 CHAR(30), PRIMARY KEY (col1));
Query OK, 0 rows affected (0.50 sec)

处理 InnoDB 中的外键约束:

MySQL 支持外键(允许您跨表交叉引用相关数据)和外键约束(有助于保持分散数据的一致性)。InnoDB 表的外键定义遵循以下条件:

InnoDB 允许外键引用任何索引列或列组。但是,在引用的表中,必须有一个索引,其中引用的列以相同的顺序列为第一列。

InnoDB 目前不支持具有用户定义分区的表的外键。这意味着用户分区的 InnoDB 表不能包含外键引用或外键引用的列。

InnoDB 允许外键约束引用非唯一键。这是标准 SQL 的 InnoDB 扩展。

限制:InnoDB 表:

一个表中最多允许有 1017 列(在 MySQL 5.6.9 中从早期的限制 1000 提高)。

一个表中最多允许有 64 个二级索引。二级索引是一种 InnoDB 索引,表示表列的子集。

默认情况下,单列索引的索引键最大可达 767 字节。相同的长度限制适用于任何索引键前缀。

InnoDB内部最大键长度为3500字节,但MySQL本身将其限制为3072字节(多列索引中的组合索引键)。

对于 16KB 的默认页大小,除可变长度列(VARBINARY、VARCHAR、BLOB 和 TEXT)之外的最大行长度约为 8000 字节。

InnoDB 内部支持大于 65,535 字节的行大小,但 MySQL 本身对所有列的组合大小施加了 65,535 的行大小限制。

最大表空间大小为 40 亿数据库页 (64TB),最小表空间大小略大于 10MB。

MySQL:MyISAM 存储引擎

MyISAM 存储引擎基于较旧的 ISAM 存储引擎(现已不可用),但具有许多有用的扩展。

MyISAM存储引擎的特点:

存储限制256TB事务是否支持锁粒度
MVCC(多版本并发控制)NO地理空间数据类型支持YES
地理空间索引支持YES
B 树索引YES
T树索引NO
哈希索引NO
全文搜索索引YES
聚集索引NO
数据缓存NO
索引缓存YES
压缩数据YES
加密数据YES
集群数据库支持NO复制支持YES
外键支持NO
备份/时间点恢复YES
查询缓存支持YES
更新数据字典的统计信息YES

每个 MyISAM 表都以三个文件的形式存储在磁盘上。

.frm 文件存储表格式。

数据文件的扩展名为 .MYD (MYData)。

索引文件的扩展名为 .MYI (MYIndex)。

创建 MyISAM 表:

使用 CREATE TABLE 语句使用 ENGINE 子句创建 MyISAM 表。从MySQL 5.6开始,需要使用ENGINE子句来指定MyISAM存储引擎,因为InnoDB是默认引擎。

例子:

mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.19 sec)
mysql> SHOW TABLE STATUS FROM tutorial;
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | |
| table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.07 sec)

以上SHOW TABLE STATUS 语句显示表的属性(属于“tutorial”数据库)。

MyISAM表的主要特点:

支持大文件的文件系统和操作系统支持最大 63 位文件长度的大文件。

MyISAM 表中允许有(2 32 ) 2 (1.844E+19) 行。

最多允许 64 个索引,每个索引最多允许 16 个列。

最大密钥长度为 1000 字节。

支持每个表一个 AUTO_INCREMENT 列的内部处理。

您可以将数据文件和索引文件放在不同物理设备上的不同目录中,以使用 DATA DIRECTORY 和 INDEX DIRECTORY 表选项来创建 TABLE 以获得更快的速度

BLOB 和 TEXT 列可以建立索引。

索引列中允许使用 NULL 值。每个密钥需要 0 到 1 个字节。

每个字符列可以有不同的字符集。

支持真正的 VARCHAR 类型;VARCHAR 列以存储在一个或两个字节中的长度开始。

具有 VARCHAR 列的表可能具有固定或动态的行长度。

表中 VARCHAR 和 CHAR 列的长度总和最多可达 64KB。

任意长度UNIQUE约束。

损坏的 MyISAM 表:

MyISAM 表格式非常可靠,但在某些情况下,如果发生以下任何事件,您可能会得到损坏的表:

mysqld(称为 MySQL 服务器)进程在写入过程中被终止。

硬件故障。

计算机发生意外关闭。

使用外部程序修改表

MySQL 或 MyISAM 代码中的软件错误。

MySQL:MEMORY存储引擎

MEMORY 存储引擎创建存储在内存中的表。由于数据可能因硬件或电源问题而崩溃,因此您只能将这些表用作临时工作区或只读缓存,用于从其他表中拉取数据。当 MySQL 服务器停止或重新启动时,MEMORY 表中的数据将丢失。

MEMORY存储引擎的特点:

存储限制内存事务是否支持锁定粒度
MVCCNO地理空间数据类型支持NO地理空间索引支持NO
B 树索引YES
T树索引NO
哈希索引YES
全文搜索索引NO
聚集索引NO
数据缓存NO
索引缓存NO
压缩数据NO
加密数据YES
集群数据库支持NO
复制支持YES外键支持NO
备份/时间点恢复YES
查询缓存支持YES
更新数据字典的统计信息YES

创建 MEMORY 表:

使用 CREATE TABLE 语句通过 ENGINE 子句创建 MEMORY 表。从MySQL 5.6开始,需要使用ENGINE子句来指定MEMORY存储引擎,因为InnoDB是默认引擎。以下示例显示如何创建和使用 MEMORY 表:

mysql> SELECT * FROM hr.departments;
+---------------+----------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME      | MANAGER_ID | LOCATION_ID |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |          0 |        1700 |
|- - - - - - - -|- - - - - - - - - - - |- - - - - - |- - - - - - -|
|- - - - - - - -|- - - - - - - - - - - |- - - - - - |- - - - - - -| 
+---------------+----------------------+------------+-------------+
27 rows in set (0.01 sec)

mysql> CREATE TABLE test7 ENGINE = MEMORY SELECT * FROM hr.departments;
Query OK, 27 rows affected (0.06 sec)
Records: 27  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test7 WHERE LOCATION_ID>1700;
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME  | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
|            20 | Marketing        |        201 |        1800 |
|            40 | Human Resources  |        203 |        2400 |
|            70 | Public Relations |        204 |        2700 |
|            80 | Sales            |        145 |        2500 |
+---------------+------------------+------------+-------------+
4 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM tutorial;
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |              1 | 2014-02-14 13:16:16 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| table2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 26740122787512319 |         1024 |         0 |           NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL       | utf8_general_ci |     NULL |                |         |
| test7  | MEMORY |      10 | Fixed      |   27 |             39 |       59400 |          16357770 |            0 |         0 |           NULL | 2014-02-17 11:06:46 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
3 rows in set (0.00 sec)

删除 MEMORY 表:

mysql> DROP TABLE TEST7;
Query OK, 0 rows affected (0.00 sec)

索引:MEMORY存储引擎支持HASH和BTREE索引。添加 USING 子句后,您可以为给定索引指定其中之一。请参阅以下示例:

CREATE TABLE test 

(id INT, INDEX USING HASH (id)) 

 ENGINE = MEMORY; 

CREATE TABLE test 

(id INT, INDEX USING BTREE (id)) 

 ENGINE = MEMORY;

何时使用 MEMORY 存储引擎:

涉及瞬态、非关键数据的操作,例如会话管理或缓存。

内存存储可实现快速访问和低延迟。数据量可以完全容纳在内存中,而不会导致操作系统换出虚拟内存页面。

默认情况下,单列索引的索引键最大可达 767 字节。相同的长度限制适用于任何索引键前缀。

InnoDB内部最大键长度为3500字节,但MySQL本身将其限制为3072字节(多列索引中的组合索引键)。

对于 16KB 的默认页大小,除可变长度列(VARBINARY、VARCHAR、BLOB 和 TEXT)之外的最大行长度约为 8000 字节。

InnoDB 内部支持大于 65,535 字节的行大小,但 MySQL 本身对所有列的组合大小施加了 65,535 的行大小限制。

最大表空间大小为 40 亿数据库页 (64TB),最小表空间大小略大于 10MB。

MySQL:MERGE 存储引擎

MERGE存储引擎(也称为MRG_MyISAM)是相同MyISAM表(具有相同顺序的相同列和索引信息)的集合,可以用作单个表。您必须对映射到 MERGE 表的 MyISAM 表具有 SELECT、DELETE 和 UPDATE 权限。

创建 MERGE 表:

要创建 MERGE 表,必须在 CREAE TABLE 语句中指定 UNION=(list-of-tables) 选项(指示要使用哪些 MyISAM 表)。下面的示例首先我们创建了三个包含两行的表,然后使用 MERGE 存储引擎将其合并为一个表:

mysql> CREATE TABLE tabl1 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE tabl2 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE tabl3 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO tabl1 (class, student_name) VALUES ('V','Steven'), ('V', 'Neena');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tabl2 (class, student_name) VALUES ('VI','Lex'), ('VI', 'Alexander');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tabl3 (class, student_name) VALUES ('VII','Bruce'), ('VII', 'David');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE allclass (rollno INT NOT NULL, class CHAR(5), student_name CHAR(40)) ENGINE = MERGE UNION = (tabl1, tabl2, tabl3) INSERT_METHOD = LAST;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from allclass;
+--------+-------+--------------+
| rollno | class | student_name |
+--------+-------+--------------+
|      1 | V     | Steven       |
|      2 | V     | Neena        |
|      1 | VI    | Lex          |
|      2 | VI    | Alexander    |
|      1 | VII   | Bruce        |
|      2 | VII   | David        |
+--------+-------+--------------+
6 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM tutorial;
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| allclass | MRG_MYISAM |      10 | Fixed      |    6 |            140 |         840 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| tabl1    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl2    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl3    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL       | utf8_general_ci |     NULL |                |         |
| table1   | InnoDB     |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |              1 | 2014-02-14 13:16:16 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| table2   | MyISAM     |      10 | Fixed      |    0 |              0 |           0 | 26740122787512319 |         1024 |         0 |           NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL       | utf8_general_ci |     NULL |                |         |
| test7    | MEMORY     |      10 | Fixed      |   27 |             39 |       59400 |          16357770 |            0 |         0 |           NULL | 2014-02-17 11:06:46 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
7 rows in set (0.16 sec)

安全问题:如果用户有权访问 MyISAM 表(例如 t1),则该用户可以创建访问 t1 的 MERGE 表 m1。但是,如果管理员撤销了用户对t1的权限,则用户可以通过m1继续访问t1的数据。

MySQL:CSV 存储引擎

CSV 存储引擎使用逗号分隔值格式将数据存储在文本文件中,并且 CSV 存储引擎始终编译到 MySQL 服务器中。创建 CSV 表时,服务器会在数据库目录中创建表格式文件(扩展名为 .frm)和数据文件(扩展名为 .csv)。.frm 和 .csv 文件名均以表名开头。数据文件是纯文本文件,存储引擎以逗号分隔值格式保存数据。以下示例显示如何创建和使用 CSV 表:

示例输出:

mysql> CREATE TABLE color (slno INT NOT NULL, cname CHAR(30) NOT NULL,
ccode CHAR(6) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO color VALUES(1, 'IndianRed', 'CD5C5C'),
(2, 'LightCoral', 'F08080'), (3, 'Salmon', 'FA8072');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * from color;
+------+------------+--------+
| slno | cname      | ccode  |
+------+------------+--------+
|    1 | IndianRed  | CD5C5C |
|    2 | LightCoral | F08080 |
|    3 | Salmon     | FA8072 |
+------+------------+--------+
3 rows in set (0.00 sec)

您可以通过 Microsoft Excel 或 StarSuite Calc 等电子表格应用程序读取、修改“color.CSV”文件。

CSV 限制:

不支持索引。

不支持分区。

CSV 表中的所有列都必须具有 NOT NULL 属性。

以下 SHOW TABLE STATUS 语句显示表的属性(属于“tutorial”数据库)。

mysql> SHOW TABLE STATUS FROM tutorial;
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| allclass | MRG_MYISAM |      10 | Fixed      |    6 |            140 |         840 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| color    | CSV        |      10 | Fixed      |    3 |              0 |           0 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| tabl1    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl2    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl3    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL       | utf8_general_ci |     NULL |                |         |
| table1   | InnoDB     |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |              1 | 2014-02-14 13:16:16 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| table2   | MyISAM     |      10 | Fixed      |    0 |              0 |           0 | 26740122787512319 |         1024 |         0 |           NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL       | utf8_general_ci |     NULL |                |         |
| test7    | MEMORY     |      10 | Fixed      |   27 |             39 |       59400 |          16357770 |            0 |         0 |           NULL | 2014-02-17 11:06:46 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
8 rows in set (0.00 sec)

MySQL:ARCHIVE存储引擎

ARCHIVE 存储引擎用于在非常小的占用空间中存储大量未索引的数据。该存储引擎包含在 MySQL 二进制发行版中。要启用此存储引擎(如果您从源代码构建 MySQL),请使用 -DWITH_ARCHIVE_STORAGE_ENGINE 选项调用 CMake。创建 ARCHIVE 表时,服务器会在数据库目录中创建一个表格式文件(扩展名为 .frm)。

ARCHIVE存储引擎的特点:

存储限制事务是否支持锁粒度
MVCCNO地理空间数据类型支持YES
地理空间索引支持NO
B 树索引NO
T树索引NO
哈希索引NO
全文搜索索引NO
聚集索引NO
数据缓存NO
索引缓存NO
压缩数据YES
加密数据YES
集群数据库支持NO
复制支持YES
外键支持NO
备份/时间点恢复YES查询缓存支持YES
更新数据字典的统计信息YES

ARCHIVE存储引擎支持

1.插入并选择。

2.ORDER BY操作

3.BLOB列

4.AUTO_INCRMENT 列属性。AUTO_INCRMENT 列可以有唯一索引或非唯一索引。

5.CREATE TABLE 语句中的 AUTO_INCRMENT 表选项

ARCHIVE存储引擎不支持

删除、替换或更新

将小于当前最大列值的值插入到 AUTO_INCREMENT 列中。

ARCHIVE 存储引擎:存储和检索

ARCHIVE 引擎使用 zlib 无损数据压缩

行在插入时被压缩。

检索时,行按需解压缩;没有行缓存。

MySQL:EXAMPLE存储引擎

EXAMPLE存储引擎是一个存根引擎,它不执行任何操作,而是作为 MySQL 源代码中的示例来阐明如何开始编写新的存储引擎。要检查 EXAMPLE 引擎的源代码,请查看 MySQL 源代码发行版的 storage/example 目录。当您创建示例表时:

服务器在数据库目录中创建一个表格式文件(.frm 扩展名)。

没有创建其他文件

没有数据可以存储到表中。

检索返回空结果。

不支持索引。

要在从源代码构建 MySQL 的情况下启用示例存储引擎,请使用 -DWITH_EXAMPLE_STORAGE_ENGINE 选项调用 CMake。

MySQL:BLACKHOLE 存储引擎

BLACKHOLE存储引擎充当“黑洞”,接受数据但返回空结果。要启用 BLACKHOLE 存储引擎(如果是从源构建 MySQL),请使用 -DWITH_BLACKHOLE_STORAGE_ENGINE 选项调用 CMake。创建 BLACKHOLE 表时,服务器会在数据库目录中创建一个表格式文件 (.frm)。BLACKHOLE存储引擎支持各种索引。这是一个例子:

mysql> CREATE TABLE test10 (slno INT, message CHAR(40)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO test10 VALUES(1, 'message1'), (2, 'message2');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test10;
Empty set (0.03 sec)

以下 SHOW TABLE STATUS 语句显示表的属性(属于“tutorial”数据库)

mysql> SHOW TABLE STATUS FROM tutorial;
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| allclass | MRG_MYISAM |      10 | Fixed      |    6 |            140 |         840 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| color    | CSV        |      10 | Fixed      |    2 |              0 |           0 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| tabl1    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl2    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL       | utf8_general_ci |     NULL |                |         |
| tabl3    | MyISAM     |      10 | Fixed      |    2 |            140 |         280 | 39406496739491839 |         2048 |         0 |              3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL       | utf8_general_ci |     NULL |                |         |
| table1   | InnoDB     |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |              1 | 2014-02-14 13:16:16 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| table2   | MyISAM     |      10 | Fixed      |    0 |              0 |           0 | 26740122787512319 |         1024 |         0 |           NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL       | utf8_general_ci |     NULL |                |         |
| test10   | BLACKHOLE  |      10 | Fixed      |    0 |              0 |           0 |                 0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| test7    | MEMORY     |      10 | Fixed      |    0 |             39 |           0 |          16357770 |            0 |         0 |           NULL | 2014-02-19 11:42:17 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
9 rows in set (1.05 sec)

MySQL:FEDERATED存储引擎

FEDERATED存储引擎用于从远程MySQL数据库访问数据,而不使用复制或集群技术。查询本地 FEDERATED 表会自动从远程(联合)表中提取数据。本地表中不存储任何数据。要包含 FEDERATED 存储引擎(如果是从源构建 MySQL),请使用 -DWITH_FEDERATED_STORAGE_ ENGINE 选项调用 CMake。

要启用 FEDERATED(默认情况下在运行的服务器中未启用),您必须使用 --federated 选项启动 MySQL 服务器二进制文件。要检查 FEDERATED 引擎的源代码,请查看 MySQL 源代码发行版的 storage/ federated 目录。

创建 FEDERATED 表

您可以通过以下方式创建 FEDERATED 表:

使用 CONNECTION

使用 CREATE SERVER

使用 CONNECTION:要使用此方法,您必须在 CREATE TABLE 语句中的引擎类型之后指定 CONNECTION 字符串。请参阅以下示例:

CREATE TABLE federated_table (
    roll_no     INT(3) NOT NULL AUTO_INCREMENT,
    stu_name   VARCHAR(42) NOT NULL DEFAULT '',
    total_marks  INT(5) NOT NULL DEFAULT '0',
    PRIMARY KEY  (roll_no),
    INDEX stu_name (stu_name),
    INDEX total_marks (total_marks)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://feduser@remote_host:9306/federated/test10_table';

连接字符串的格式如下:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

说明:

方案:公认的连接协议。目前仅支持 mysql 作为方案值。

连接的用户名必须已在远程服务器上创建,并且具有适当的权限才能在远程表上执行所需的操作,例如 SELECT、INSERT、UPDATE 等。

user_name 的密码。(选修的)

host_name:远程服务器的主机名或 IP 地址。

port_num:远程服务器的端口号(默认:3306)。(选修的)

db_name:保存远程表的数据库的名称。

tbl_name:远程表的名称。

使用 CREATE SERVER:要使用此方法,必须在 CREATE TABLE 语句中的引擎类型后面指定 CONNECTION 字符串。请参见以下示例:

CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

创建新的 FEDERATED 表时,在连接字符串中使用 server_name。

InnoDB和MyISAM的区别

特征InnoDBMyISAM
存储限制64TB256TB
事务YESNO
锁粒度
MVCCYESNO
地理空间数据类型支持YES
YES
地理空间索引支持NOYES
B 树索引YES
YES
T树索引NO
NO
哈希索引 NONO
全文搜索索引YES
YES
聚集索引YES
NO
数据缓存YES
NO
索引缓存YES
YES
压缩数据YES
YES
加密数据YES
YES
集群数据库支持NONO
复制支持YES
YES
外键支持YES
NO
备份/时间点恢复YES
YES
查询缓存支持YES
YES
更新数据字典的统计信息YES
YES


版权所属:SO JSON在线解析

原文地址:https://www.sojson.com/blog/500.html

转载时必须以链接形式注明原始出处及本声明。

本文主题:

如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。

相关文章
MySQL无法存储Emoji表情,IPhone表情问题 。
MySQL数据库存储过程字符集问题,乱码的解决办法
Elasticsearch Date类型,时间存储相关说明。
Java 集成阿里云消息队列,日志消息存储
SpringBoot 集成Spring-data-redis,redis对象序列化存储
使用七牛云存储实现图片API,自动删除图片方案合集
阿里云DNS 解析讲解,SEO配置搜索引擎线路解析
又拍云(Upyun)CDN、云存储刷新链接缓存,API实例讲解
两招提升硬盘存储数据的写入效率
又拍云(Upyun)刷新CDN,云存储缓存 —JavaAPI
最新文章
XML内部实体和外部实体 131
Java面向对象编程概念 101
PHP回显语句 91
Linux—文件树 116
C语言while循环和do while循环 131
Python元组剖析 200
MySQL触发器教程 296
sql使用布尔运算符和关系运算符 241
C语言的变量和常量 296
PHP变量剖析 198
最热文章
最新MyEclipse8.5注册码,有效期到2020年 (已经更新) 681970
苹果电脑Mac怎么恢复出厂系统?苹果系统怎么重装系统? 674712
免费天气API,全国天气 JSON API接口,可以获取五天的天气预报 602004
免费天气API,天气JSON API,不限次数获取十五天的天气预报 576723
Jackson 时间格式化,时间注解 @JsonFormat 用法、时差问题说明 552904
我为什么要选择RabbitMQ ,RabbitMQ简介,各种MQ选型对比 509353
Elasticsearch教程(四) elasticsearch head 插件安装和使用 479956
Jackson 美化输出JSON,优雅的输出JSON数据,格式化输出JSON数据... ... 264463
Java 信任所有SSL证书,HTTPS请求抛错,忽略证书请求完美解决 244246
Elasticsearch教程(一),全程直播(小白级别) 225520
支付扫码

所有赞助/开支都讲公开明细,用于网站维护:赞助名单查看

查看我的收藏

正在加载... ...