mysql存储引擎(二)_MySQL

mysql存储引擎（二）

mysql存储引擎二 memory merge berkeleydb存储引擎

MEMORY

mysql> create table memory_table( id int primary key, name varchar(20) )engine=memory;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into memory_table(id,name) values(2,'frank');
Query OK, 1 row affected (0.00 sec)
mysql> select * from memory_table;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | frankstar |
|  2 | frank     |
+----+-----------+
2 rows in set (0.00 sec)
mysql> show table status like 'memory_table' \G;
*************************** 1. row ***************************
           Name: memory_table
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 66
    Data_length: 127008
Max_data_length: 12582900
   Index_length: 126992
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-05-09 22:23:47
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show index from memory_table \G;
*************************** 1. row ***************************
        Table: memory_table
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: NULL
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified

MERGE

如下：

mysql> create table myisam_table1(
    -> id int primary key,
    -> data datetime
    -> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)
create table myisam_table2( id int primary key, data datetime )engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table table1_merge_table2(
    -> id int primary key,
    -> data datetime
    -> )engine=merge union=(myisam_table1,myisam_table2) insert_method=first;
Query OK, 0 rows affected (0.01 sec)

<code class="hljs haml">向2个字表分别添加数据，如下：

mysql> insert into myisam_table1 values(1,'2016-5-7');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myisam_table1 values(2,'2016-5-6');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myisam_table2 values(1,'2016-5-7');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myisam_table2 values(2,'2016-5-6');
Query OK, 1 row affected (0.00 sec)

<code class="hljs haml"><code class="hljs cs">查询merge表，如下：

mysql> select * from table1_merge_table2;
+----+---------------------+
| id | data                |
+----+---------------------+
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
+----+---------------------+
4 rows in set (0.01 sec)

<code class="hljs haml"><code class="hljs cs"><code class="hljs asciidoc">向merge表中添加一条数据，如下：

mysql> insert into table1_merge_table2 values(3,'2016-5-8');
Query OK, 1 row affected (0.00 sec)
mysql> select * from table1_merge_table2;
+----+---------------------+
| id | data                |
+----+---------------------+
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
|  3 | 2016-05-08 00:00:00 |
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)

mysql> select * from myisam_table1;
+----+---------------------+
| id | data                |
+----+---------------------+
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
|  3 | 2016-05-08 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)
mysql> select * from myisam_table2;
+----+---------------------+
| id | data                |
+----+---------------------+
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
+----+---------------------+
2 rows in set (0.00 sec)

<code class="hljs haml"><code class="hljs cs"><code class="hljs asciidoc"><code class="hljs asciidoc">BerkeleyDB存储引擎

<code class="hljs haml"><code class="hljs cs"><code class="hljs asciidoc"><code class="hljs asciidoc">简称BDB，创建该类型的表时，会有2个数据文件，一个.frm文件存储表元数据，另一个.db文件存储数据和索引文件，类似innodb。它的实现事务安全有redo日志。在每次启动的时候，都会做一次检查操作，将所有的redo日志清空。它和Memory引擎一样，都是页级锁定。