MySQL存储引擎
存储引擎(底层软件组件)是 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存储引擎的特点:
存储限制 | 内存 | 事务 | 是否支持 | 锁定粒度 | 表 |
MVCC | NO | 地理空间数据类型支持 | 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存储引擎的特点:
存储限制 | 无 | 事务 | 是否支持 | 锁粒度 | 表 |
MVCC | NO | 地理空间数据类型支持 | 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的区别
特征 | InnoDB | MyISAM |
---|---|---|
存储限制 | 64TB | 256TB |
事务 | YES | NO |
锁粒度 | 行 | 表 |
MVCC | YES | NO |
地理空间数据类型支持 | YES | YES |
地理空间索引支持 | NO | YES |
B 树索引 | YES | YES |
T树索引 | NO | NO |
哈希索引 | NO | NO |
全文搜索索引 | YES | YES |
聚集索引 | YES | NO |
数据缓存 | YES | NO |
索引缓存 | YES | YES |
压缩数据 | YES | YES |
加密数据 | YES | YES |
集群数据库支持 | NO | NO |
复制支持 | YES | YES |
外键支持 | YES | NO |
备份/时间点恢复 | YES | YES |
查询缓存支持 | YES | YES |
更新数据字典的统计信息 | YES | YES |
版权所属:SO JSON在线解析
原文地址:https://www.sojson.com/blog/500.html
转载时必须以链接形式注明原始出处及本声明。
如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。