MySQL存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

• --default-storage-engine=type
• --default_tmp_storage_engine=type

• mysql> create table t1 (i int not null) engine = innodb;
• mysql> create table t2 (i int not null) engine = csv;
• mysql> create table t3 (i int not null) engine = memory;
• mysql> alter table t1 engine=myisam;
• mysql> show create table t1;

InnoDB

• innodb_*                   ----InnoDB引擎相关的参数都是以innodb_开头的
• innodb_data_home_dir
• innodb_log_group_home_dir
• innodb_log_file_size
• innodb_log_files_in_group
• innodb_file_per_table

MyISAM

• .frm .MYD .MYI               ----MyISAM的索引文件和数据文件是分开的（Innodb索引就是数据）
• Operating system independent
• mysql db                   ----自带的mysql schema是MyISAM引擎
• table level lock             ----表级锁
• key_buffer_size              ----MyISAM会将索引缓存在内存中，MyISAM只缓存索引内容，而                                                                     innodb不仅缓存索引还缓存数据。
生成压缩的、只读的MyISAM表，可以使用下面两个命名。

• myisamchk
• myisampack

myisampack压缩表的数据，然后再用myisamchk修复索引数据（因为压缩表数据之后，表的数据地址就变了，索引中数据地址的指向就错了。）

如果myisampack压缩完数据文件后，不执行myisamchk修复索引文件，查询的时候可能会报错：

ERROR 126(HY000):Incorrect key file for table 'xxxx.MYI';try to repair it;

[root@mysqlrep2 lucien]# ll -h t1.*
-rw-rw----. 1 mysql mysql 330M Nov  9 12:28 t1.MYD
-rw-rw----. 1 mysql mysql  77M Nov  9 12:28 t1.MYI
-rw-rw----. 1 mysql mysql 8.5K Nov  9 12:27 t1.frm
[root@mysqlrep2 lucien]# <span style="color:#ff0000;">/usr/local/mysql/bin/myisampack t1</span>
Compressing t1.MYD: (7864320 records)
- Calculating statistics
- Compressing file
59.77%     
Remember to run myisamchk -rq on compressed tables
[root@mysqlrep2 lucien]# 
[root@mysqlrep2 lucien]# 
[root@mysqlrep2 lucien]# <span style="color:#ff0000;">/usr/local/mysql/bin/myisamchk -rq t1</span>
- check record delete-chain
- recovering (with sort) MyISAM-table 't1'
Data records: 7864320
- Fixing index 1
[root@mysqlrep2 lucien]# 

Memory

• Heap
• .frm
• lost when restart
• --init-file
• fixed length
• not blob
• max_heap_table_size

CSV

• .frm .CSM .CSV
• not support indexing
• not partition
• not null              ----建表时所有字段必须not null，否则创建表不成功。（见下面代码）

关于.CSM文件，官方解释如下：

Creating a  CSV  table also creates a corresponding Metafile that stores the state of the table and the number of rows that exist in the table. The name of this file is the same as the name of the table with the extension  CSM .

mysql> create table t2(id int not null,name varchar(20),sex varchar(2),address varchar(100)) engine=CSV;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
mysql> create table t2(id int not null,name varchar(20) NOT NULL ,sex varchar(2) NOT NULL,address varchar(100) NOT NULL) engine=CSV;
Query OK, 0 rows affected (0.01 sec)

.CSV文件可以直接查看

[root@mysqlrep2 lucien]# cat t2.CSV 
31,"diyige","F","beijingshichaoyangqu"
32,"dierge","M","beijingshichaoyangqubeisihuan"
33,"disange","M","beijingshichaoyangqubeisihuandonglu"

当.CSV文件中的信息，比如行数，与.CSM中记录的不同时，会报错，提示表已损坏，需要恢复。

例如：t2表中原本有10行数据，编辑.CSV文件删除一行后，再次查询该表，提示该表已被标记为crashed，需要repair。

mysql> flush tables;
Query OK, 0 rows affected (0.18 sec)

mysql> select * from t2;
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired
mysql> check table t2;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| lucien.t2 | check | error    | Corrupt  |
+-----------+-------+----------+----------+
1 row in set (0.05 sec)

mysql> repair table t2;
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| lucien.t2 | repair | status   | OK       |
+-----------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.06 sec)

可以将外部.CSV文件中的数据导入到数据库中，例如：

load data infile ‘xxx.CSV’ into table table_name 

fields terminated by ','

enclosed by '"'

escaped by '\\';

ARCHIVE

• .frm .ARZ
• select/insert
• no index
• auto_increment primary key
• zlib

看名字就知道，一般用于类似于归档性质的表。采用压缩格式。没有索引。

Blackhole

----不存储具体数据，只有一个.frm文件，记录日志，基于语句。复制时用作日志分发等比较多。

----实用mysqlbinlog 命令可以查看binlog中记录的日志

• No Data
• binlog
• based-statement

mysql> create table t3(id int not null,name varchar(20),sex varchar(2),address varchar(100)) engine=blackhole;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 values(31,'diyige','F','beijingshichaoyangqu');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(32,'dierge','M','beijingshichaoyangqubeisihuan'); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
Empty set (0.00 sec)

[root@mysqlrep2 lucien]# ll t3*
-rw-rw----. 1 mysql mysql 8650 Nov  9 13:06 t3.frm
[root@mysqlrep2 lucien]# 

MRG_MYISAM

将相同结构，不同名字的MyISAM表MERGE起来

• MRG_MyISAM
• .frm .MRG
• engine=merge union=(a,b,c) insert_methon=xxx
• NO|FIRST|LAST

Federated

类似于oracle的dblink

Infobright

列式存储