MySQL常用存储引擎之CSV

CSV存储引擎可以将CSV文件作为mysql表来处理,存储格式就是普通的CSV文件。如果把数据存储在myisam和Innodb中,存储数据的文件是不能直接查看的,因为这两种存储引擎都是以二进制文件存储的。而CSV是以文本方式存储的,CSV是不支持索引的,查找的时候要进行全表扫描。

文件系统存储特点:

  • 数据以文本方式存储在文件中(Innodb则是二进制)
  • .CSV文件存储表内容
  • .CSM文件存储表的元数据如表状态和数据量
  • .frm文件存储表结构信息

CSV存储引擎特点:

  • 以CSV格式进行数据存储(逗号隔开,引号)
  • 所有的列必须都是不能为NULL的
  • 不支持索引,所以CSV不适合大表,不适合在线处理类型的应用
  • 可以对数据文件直接编辑,因为CSV存储的是文本内容
  • 不支持事务,不支持保存点,不支持XA事务(两阶段提交)

接下来我们创建一张使用CSV存储引擎的表,但是不指定 not null 看看会发生什么:

mysql> create table mycsv(id int,c1 varchar(10),c2 char(20)) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
mysql> 

可以看到,不指定 not null 就会报错,所以需要指定所有的列为 not null :

mysql> create table  mycsv(id  int not null ,c1 varchar(10) not null,c2 char(20) not null) engine=csv;
Query OK, 0 rows affected (0.01 sec)
mysql>

然后我们向表中插入一些数据:

mysql> insert into mycsv values ( 1,'aaa','bbb'),(2,'ccc','ddd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mycsv;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.01 sec)

我们查看一下文件系统里生成的文件以及mycsv.CSV文件的内容:

[root@01server /data/mysql/test_database]# ls mycsv.*
mycsv.CSM  mycsv.CSV  mycsv.frm
[root@01server /data/mysql/test_database]# cat mycsv.CSV
1,"aaa","bbb"
2,"ccc","ddd"
[root@01server /data/mysql/test_database]# 

从cat出来的内容可以看到,该文件的内容是文本格式的,我们来追加一行数据到该文件的末尾

[root@01server /data/mysql/test_database]# echo '3,"eee","fff"' >> mycsv.CSV

然后回到数据库中查看mycsv表的内容,会发现我们追加到文件的末尾的数据已经添加到了mycsv表中:

mysql> select * from mycsv;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
|  3 | eee | fff |
+----+-----+-----+
3 rows in set (0.00 sec)

之前提到了CSV是不支持索引的,我们来看看如果增加索引会发生什么:

mysql> create index idx_id on mycsv(id);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed
mysql>

可以看到报错了,证明的确是不支持索引的

CSV存储引擎的适用场景:

适合做为数据交换的中间表,能够在服务器运行的时候,拷贝和拷出文件,可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下,就能够在数据库中打开和使用。同样,如果将数据写入到CSV文件数据表中,其它web程序也可以迅速读取到数据。


MySQL常用存储引擎之Archive

从archive单词的解释我们大概可以明白这个存储引擎的用途,这个存储引擎基本上用于数据归档;它的压缩比非常的高,存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合,由于它不支持索引同时也不能缓存索引和数据,所以它不适合作为并发访问表的存储引擎。Archivec存储引擎使用行锁来实现高并发插入操作,但是它不支持事务,其设计目标只是提供高速的插入和压缩功能。

文件系统存储特点:

  • 以zlib对表数据进行压缩,磁盘I/O更少
  • 数据存储在ARZ为后缀的文件中

Archiv存储引擎的特点:

  • 只支持insert、replace和select操作,但不支持update和delete操作
  • archive支持行级锁和缓冲区,可以实现高并发的插入
  • archive存储引擎支持blob、text等大字段类型
  • archive支持自增列,但是不支持往自增列插入一个小于当前最大的值的值
  • 只允许在自增ID列上加索引,同时自增列可以不是唯一索引

存储特点:

往archive表插入的数据会经过压缩,archive使用zlib进行数据压缩,archive支持optimize table、 check table操作。

一个insert语句仅仅往压缩缓存中插入数据,插入的数据在压缩缓存中被锁定,当select操作时会触发压缩缓存中的数据进行刷新。insert delay除外。

对于一个bulk insert操作只有当它完全执行完才能看到记录,除非在同一时刻还有其它的inserts操作,在这种情况下可以看到部分记录,select从不刷新bulk insert除非在它加载时存在一般的Insert操作。

检索特点:

对于检索请求返回的行不会压缩,且不会进行数据缓存;一个select查询会执行完整的表扫描;当一个select查询发生时它查找当前表所有有效的行,select执行一致性读操作,注意,过多的select查询语句会导致压缩插入性能变的恶化,除非使用bulk insert或delay insert,可以使用OPTIMIZE TABLE 或REPAIR TABLE来获取更好的压缩,可以使用SHOW TABLES STATUS查看ARCHIVE表的记录行。

相同数量级下,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。

Archive表的性能是否可能超过MyISAM?答案是肯定的。根据MySQL工程师的资料,当表内的数据达到1.5GB这个量级,CPU又比较快的时候,Archive表的执行性能就会超越MyISAM表。因为这个时候,CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。

较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你需要把数据从一台MySQL服务器转移到另一台的时候,Archive表可以方便地移植到新的MySQL环境,你只需将保存Archive表的底层文件复制过去就可以了。

接下来我们创建一张使用Archive存储引擎的表:

mysql> create table myarchive( id int auto_increment not null , c1 varchar(10),c2 char(10), primary key(id)) engine = archive;
Query OK, 0 rows affected (0.00 sec)

然后查看文件系统,会发现存在两个文件,.ARZ文件存储表内容,.frm文件则存储表结构:

[root@01server /data/mysql/test_database]# ls myarchive.*
myarchive.ARZ  myarchive.frm
[root@01server /data/mysql/test_database]# 

回到数据库中,我们往数据表里插入一些数据:

mysql> insert into myarchive (c1,c2) values ('aa','bb'),('cc','dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from myarchive;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aa   | bb   |
|  2 | cc   | dd   |
+----+------+------+
2 rows in set (0.01 sec)

mysql> 

如上文所说,虽然我们可以进行插入和查询操作,但是删除和更新操作是不支持的:

mysql> delete from myarchive where id = 1;
ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option
mysql> update myarchive  set c1='aaaa' where id =1;
ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option
mysql> 

也无法在非自增键上创建索引:

mysql> create index idx_c1 on myarchive(c1);
ERROR 1069 (42000): Too many keys specified; max 1 keys allowed
mysql> 

使用场景:

日志和数据采集类应用(不支持OLTP)


MySQL常用存储引擎之Memory

MEMORY是MySQL中一类特殊的存储引擎,称HEAP存储引擎,所以数据保存在内存中(服务器重启则表的数据丢失,但是表结构是保留的,表结构保存在磁盘文件中,而表的内容是存储在内存中)。基本上使用Memory存储引擎的出发点是速度,为得到最快的响应时间。因为其基于内存中的特性,这类表的处理速度会非常快,但是,其数据易丢失,生命周期短。基于其这个缺陷,选择Memory存储引擎时需要特别小心。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm类型。该文件中只存储表的结构。而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了,可以释放内存,甚至删除不需要的表。MEMORY默认使用哈希索引,速度比使用B型树索引快。当然如果你想用B型树索引,可以在创建索引时指定。

Memory存储引擎通常很少用到,至少我是没有用到过。因为Memory表的所有数据都是存储在内存上的,如果内存出现异常会影响到数据的完整性。如果重启机器或者关机,表中的所有数据都将消失,因此,基于Memory存储引擎的表的生命周期都比较短,一般都是一次性的。

Memory表的大小是受到限制的,表的大小主要取决于2个参数,分别是max_rowsmax_heap_table_size。其中,max_rows可以在创建表时指定,max_heap_table_size的大小默认为16MB,可以按需要进行扩大。

Memory存储引擎的功能特点总结:

  • 支持HASH索引(等值查询快)和BTree索引(范围查找快),默认为HASH
  • 所有字段都为固定长度,就算是使用varchar也会被转换成char类型,varchar(10) -> char(10)
  • 不支持BLOG和TEXT等大字段
  • Memory存储引擎使用的是表级锁,不是行级锁
  • Memory表的最大大小由max_heap_table_size参数决定(默认16M,但是对存在的表修改是无效的)

接下来我们创建一张使用Memory存储引擎并且使用TEXT字段的表,看看会不会报错:

mysql> create table mymemory (id int,c1 varchar(10),c2 char(10),c3 text ) engine = memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns
mysql> 

从报错的信息中可以看到,明确说明了不支持BLOG和TEXT字段。那么我们就来创建一张正常的Memory表吧:

mysql> create table mymemory (id int,c1 varchar(10),c2 char(10) ) engine = memory;
Query OK, 0 rows affected (0.00 sec)

然后我们进入文件系统,可以看到只有一个保存表结构的文件,并没有用于保存数据的文件:

[root@01server /data/mysql/test_database]# ls mymemory.*
mymemory.frm
[root@01server /data/mysql/test_database]#

以上我们提到了Memory引擎默认使用HASH索引,但也可以指定创建BTree索引,我们来创建两个索引看看:

mysql> create index idx_c1 on mymemory(c1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_c2 using btree on mymemory(c2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

查看一下当前索引类型,可以看到存在两个索引,一个为默认的HASH,一个是指定的BTree:

mysql> show index from mymemory \G
*************************** 1. row ***************************
        Table: mymemory
   Non_unique: 1
     Key_name: idx_c1
 Seq_in_index: 1
  Column_name: c1
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: mymemory
   Non_unique: 1
     Key_name: idx_c2
 Seq_in_index: 1
  Column_name: c2
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

mysql> 

我们再查看一下表的状态:

mysql> show table status like 'mymemory'\G
*************************** 1. row ***************************
           Name: mymemory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed  # 固定长度
           Rows: 0
 Avg_row_length: 26
    Data_length: 0
Max_data_length: 4793490
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-10-10 15:45:18
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql>

Memory存储引擎表和临时表的区别:

临时表分两类:系统为了优化查询所生成的临时表和使用 create temporary table 语句建立的临时表。无论哪种表,只有当前session是可见的。而Memory表是所有线程都可以使用的,所以Memory表并不属于临时表。

系统使用临时表又分为两类:超过内存限制时使用MyISAM临时表,未超过限制时则会使用Memory表。

使用场景:

  • 用于查找或者是映射表,例如邮编和地区的对应表
  • 用于保存数据分析中产生的中间表
  • 用于缓存周期性聚合数据的结果表

注意一点是:Memory数据易丢失,所以要求存储的数据都是可再生的


MySQL常用存储引擎之Federated

mysql 提供了一个类似Oracle中的数据库链接(DBLINK)功能的存储引擎--Federated,使得可以不使用replication或cluster技术,直接远程服务器主机的数据表。当我们创建一个以Federated为存储引擎的表时,服务器在数据库目录只创建一个表定义文件。文件由表的名字开始,并有一个frm扩展名。无其它文件被创建,因为实际的数据在一个远程数据库上。这不同于为本地表工作的存储引擎的方式。

Federated 存储引擎允许访问远程MySQL数据库中的数据,Federated 仅支持表级别的远程访问。本地的Federated表中不存储数据,访问本地表时,会自动从远程表中获取数据。因为使用Federated 存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地。

Federated存储引擎特点总结:

  • 提供了访问远程MySQL服务器上表的方法(连接)
  • 本地不存储数据,数据全部放到远程服务器上
  • 本地需要保存表结构(frm文件)和远程服务器的连接信息

实现原理:

通过创建存储引擎为Federated 的表来实现远程共享服务器表数据。Federated:能够将多个分离(不在同一台服务器上的机器)的MySQL服务器链接起来,从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

Federated 存储引擎架构图:
除Innodb和MyISAM外MySQL还有哪些存储引擎

  1. 本地服务器 Federated 存储引擎的表只存放表的.frm结构文件
  2. 远程服务器 存放了.frm和数据文件
  3. 增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。
  4. 远程数据表的存储引擎为MySQL支持的存储引擎,如MyISAM、InnoDB等

Federated 存储引擎的性能并不是很好,而且可以使用复制的方式来实现 Federated 的功能,所以目前的mysql版本默认是不开启的,可以使用如下语句查看是否开启了Federated:

show engines;

执行结果如下, FEDERATED 中Support状态NO表明引擎未开启:
除Innodb和MyISAM外MySQL还有哪些存储引擎

需要在MySQL服务的配置中增加federated参数来开启 [ /etc/my.cnf ] ,如下:

[mysqld]
federated

配置好后重启 MySQL 再次查看,FEDERATED 中Support状态成YES表明引擎开启成功:
除Innodb和MyISAM外MySQL还有哪些存储引擎

由于我这里只有一台机器,所以将在本地进行模拟,首先创建如下两个数据库:

mysql> create database local;
Query OK, 1 row affected (0.00 sec)

mysql> create database remote;
Query OK, 1 row affected (0.00 sec)

mysql> 

在remote库中,创建一张表格以及插入相应的测试数据,还需要创建一个用户,如下:

mysql> create table remote_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('ddd','fff');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> grant select,update,delete,insert on remote.remote_fed to fred_link@'127.0.0.1' identified by '123456';  -- 创建一个用户
Query OK, 0 rows affected (0.00 sec)

mysql> 

完成以上操作后,就可以使用以下语句进行连接了:

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

- user_name 远程mysql的用户名
- password 远程mysql的密码
- host_name 远程mysql的主机地址(ip)
- port_num 远程mysql的端口后
- db_name 需要连接的数据库
- tbl_name 需要映射的表格

进入local库,创建与remote.remote_fed一样的表结构的表,不同的是需要选择federated存储引擎,并且加上连接信息,如下:

mysql> use local;
Database changed
mysql> create table local_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id))engine=federated connection='mysql://fred_link:123456@127.0.0.1:3306/remote/remote_fed';
Query OK, 0 rows affected (0.01 sec)

mysql>

然后我们来select这张表的数据,看看能否正常查找出remote.remote_fed表里的数据,如下:

mysql> select * from local.local_fed;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
|  3 | ddd | fff |
+----+-----+-----+
3 rows in set (0.00 sec)

mysql> 

可以看到,显示的是remote.remote_fed表里的数据,我们再来测试一下delete语句,看看删除local.local_fed表里的数据后,remote.remote_fed表是否会跟着删除:

mysql> delete from local.local_fed where id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> 

切换到remote库,查看remote.remote_fed表里的数据,可以看到,id为3的数据已经被删除了:

mysql> use remote;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from remote.remote_fed;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.00 sec)

mysql> 

上文中提到本地不会存储远程表的数据,这一点我们可以通过查看文件系统来证实,如下可以看到只存在一个用于存储表结构的.frm文件:

[root@01server /data/mysql]# ls local/
db.opt  local_fed.frm
[root@01server /data/mysql]#

使用场景:

由于其性能问题,不适用于生产环境中,只能用于偶尔的统计分析及手动查询