mysql存储引擎(二)

WBOY
Lepaskan: 2016-06-07 14:51:04
asal
1136 orang telah melayarinya

mysql存储引擎(二) mysql存储引擎二 MEMORY MERGE BerkeleyDB存储引擎 MEMORY MEMORY存储引擎通过采用内存中的内容来创建表。每个Memory表实际上和一个磁盘文件关联起来,文件名采用”表名.frm”的格式。Memory类型的表访问速度极快,因为数据源来自内存,

mysql存储引擎(二)


      • mysql存储引擎二
        • MEMORY
        • MERGE
        • BerkeleyDB存储引擎

MEMORY

MEMORY存储引擎通过采用内存中的内容来创建表。每个Memory表实际上和一个磁盘文件关联起来,文件名采用”表名.frm”的格式。Memory类型的表访问速度极快,因为数据源来自内存,所以数据库关闭时,内存中的数据就会发生丢失。默认使用Hash索引。

<code class=" hljs asciidoc">mysql> create table memory<span class="hljs-emphasis">_table( id int primary key, name varchar(20) )engine=memory;
Query OK, 0 rows affected (0.02 sec)

</span>mysql> insert into memory<span class="hljs-emphasis">_table(id,name) values(2,'frank');
Query OK, 1 row affected (0.00 sec)

</span><span class="hljs-header">mysql> select * from memory_table;
+----+-----------+</span>
<span class="hljs-header">| id | name      |
+----+-----------+</span>
|  1 | frankstar |
<span class="hljs-header">|  2 | frank     |
+----+-----------+</span>
2 rows in set (0.00 sec)

mysql> show table status like <span class="hljs-emphasis">'memory_table'</span> \G;
<span class="hljs-bullet">*************************** </span>1. row ***************************
<span class="hljs-code">           Name: memory_table</span>
<span class="hljs-code">         Engine: MEMORY</span>
<span class="hljs-code">        Version: 10</span>
<span class="hljs-code">     Row_format: Fixed</span>
<span class="hljs-code">           Rows: 2</span>
<span class="hljs-code"> Avg_row_length: 66</span>
<span class="hljs-code">    Data_length: 127008</span>
Max<span class="hljs-emphasis">_data_</span>length: 12582900
<span class="hljs-code">   Index_length: 126992</span>
<span class="hljs-code">      Data_free: 0</span>
<span class="hljs-code"> Auto_increment: NULL</span>
<span class="hljs-code">    Create_time: 2016-05-09 22:23:47</span>
<span class="hljs-code">    Update_time: NULL</span>
<span class="hljs-code">     Check_time: NULL</span>
<span class="hljs-code">      Collation: utf8_bin</span>
<span class="hljs-code">       Checksum: NULL</span>
<span class="hljs-code"> Create_options:</span>
<span class="hljs-code">        Comment:</span>
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from memory<span class="hljs-emphasis">_table \G;
*************************** 1. row ***************************
        Table: memory_</span>table
<span class="hljs-code">   Non_unique: 0</span>
<span class="hljs-code">     Key_name: PRIMARY</span>
<span class="hljs-code"> Seq_in_index: 1</span>
<span class="hljs-code">  Column_name: id</span>
<span class="hljs-code">    Collation: NULL</span>
<span class="hljs-code">  Cardinality: 2</span>
<span class="hljs-code">     Sub_part: NULL</span>
<span class="hljs-code">       Packed: NULL</span>
<span class="hljs-code">         Null:</span>
<span class="hljs-code">   Index_type: HASH</span>
<span class="hljs-code">      Comment:</span>
Index<span class="hljs-emphasis">_comment:
1 row in set (0.00 sec)

</span>ERROR:
No query specified
</code>
Salin selepas log masuk

memory表的内存储存在内存中,如果表的数据很大,那么服务器将会自动将其转换为磁盘表,阀值由temp_table_size系统变量来确定。每个memory表的容量由max_heap_table_size变量的值控制。默认16MB。
主要用于数据内容变化不频繁的代码表及访问速度要求较高、数据量不大的场合,同时需要考虑更新操作数据不回写入到磁盘文件中。

MERGE

它实际上是一组myisam表的组合,将一组结构相同的MyISAM表组合在一起,MERGE表本身没有数据,对于该类型表的插入操作,是通过INSERT_METHOD定义完成的,取值为LAST或者为FIRST,FIRST意味着数据增加到组合表中的第一个myisam表中,同理LAST意味着添加到最后一个表中。所以MERGE表的文件有2个,一个是.frm文件,用于存放数据,还有一个MRG文件,用于存放MERGE表的名称,包括其组成表。

如下:

<code class=" hljs haml">mysql> create table myisam_table1(
    -<span class="ruby">> id int primary key,
</span>    -<span class="ruby">> data datetime
</span>    -<span class="ruby">> )engine=myisam;
</span>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(
    -<span class="ruby">> id int primary key,
</span>    -<span class="ruby">> data datetime
</span>    -<span class="ruby">> )engine=merge union=(myisam_table1,myisam_table2) insert_method=first;
</span>Query OK, 0 rows affected (0.01 sec)</code>
Salin selepas log masuk

向2个字表分别添加数据,如下:

<code class=" hljs cs">mysql> insert <span class="hljs-keyword">into</span> myisam_table1 values(<span class="hljs-number">1</span>,<span class="hljs-string">'2016-5-7'</span>);
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.00</span> sec)

mysql> insert <span class="hljs-keyword">into</span> myisam_table1 values(<span class="hljs-number">2</span>,<span class="hljs-string">'2016-5-6'</span>);
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.00</span> sec)

mysql> insert <span class="hljs-keyword">into</span> myisam_table2 values(<span class="hljs-number">1</span>,<span class="hljs-string">'2016-5-7'</span>);
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.00</span> sec)

mysql> insert <span class="hljs-keyword">into</span> myisam_table2 values(<span class="hljs-number">2</span>,<span class="hljs-string">'2016-5-6'</span>);
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.00</span> sec)
</code>
Salin selepas log masuk

查询merge表,如下:

<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from table1_merge_table2;
+----+---------------------+</span>
<span class="hljs-header">| id | data                |
+----+---------------------+</span>
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
|  1 | 2016-05-07 00:00:00 |
<span class="hljs-header">|  2 | 2016-05-06 00:00:00 |
+----+---------------------+</span>
4 rows in set (0.01 sec)</code>
Salin selepas log masuk

向merge表中添加一条数据,如下:

<code class=" hljs asciidoc">mysql> insert into table1<span class="hljs-emphasis">_merge_</span>table2 values(3,<span class="hljs-emphasis">'2016-5-8'</span>);
Query OK, 1 row affected (0.00 sec)

<span class="hljs-header">mysql> select * from table1_merge_table2;
+----+---------------------+</span>
<span class="hljs-header">| id | data                |
+----+---------------------+</span>
|  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 |
<span class="hljs-header">|  2 | 2016-05-06 00:00:00 |
+----+---------------------+</span>
5 rows in set (0.00 sec)


<span class="hljs-header">mysql> select * from myisam_table1;
+----+---------------------+</span>
<span class="hljs-header">| id | data                |
+----+---------------------+</span>
|  1 | 2016-05-07 00:00:00 |
|  2 | 2016-05-06 00:00:00 |
<span class="hljs-header">|  3 | 2016-05-08 00:00:00 |
+----+---------------------+</span>
3 rows in set (0.00 sec)

<span class="hljs-header">mysql> select * from myisam_table2;
+----+---------------------+</span>
<span class="hljs-header">| id | data                |
+----+---------------------+</span>
|  1 | 2016-05-07 00:00:00 |
<span class="hljs-header">|  2 | 2016-05-06 00:00:00 |
+----+---------------------+</span>
2 rows in set (0.00 sec)</code>
Salin selepas log masuk

INSERT_METHOD的指定起作用了,如果没有指定,那么当试图往Merge表中insert数据时,都会发生错误。通常使用merge表来透明的对多个表进行查询和更新。

BerkeleyDB存储引擎

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

Label berkaitan:
sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan