mysql 分表原理_MYSQL 分表原理（转）

简介:

引用MySQL官方文档中的一段话:MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合."相同"意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩.

例子:

mysql>

show

engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

|

Engine             | Support | Comment

| Transactions | XA   | Savepoints

|

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

|

MEMORY             | YES     | Hash

based, stored in memory, useful for temporary tables      | NO

| NO   | NO

|

| MRG_MYISAM         | YES     |

Collection of identical MyISAM tables

| 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

|

| CSV                |

YES     | CSV storage engine

| NO           | NO

| NO         |

| PERFORMANCE_SCHEMA |

YES     | Performance Schema

| NO           | NO

| NO         |

| ARCHIVE

| YES     | Archive storage engine

| NO

| NO   | NO

|

| FEDERATED          | NO

| Federated MySQL storage engine

|

NULL         | NULL | NULL

|

| InnoDB             | DEFAULT |

Supports transactions, row-level locking, and foreign keys     | YES

| YES  | YES

|

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9

rows in set (0.00 sec)

mysql> create table test1 (id int

not null auto_increment,name varchar(10) default null ,primary key (id))

engine=myisam auto_increment=1;

Query OK, 0 rows affected (0.01

sec)

mysql> create table test2 (id int not null

auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam

auto_increment=1;

Query OK, 0 rows affected (0.00

sec)

mysql> INSERT INTO `test1` (`name`)

VALUES(‘beijing1‘);

Query OK, 1 row affected (0.00

sec)

mysql> INSERT INTO `test2` (`name`)

VALUES(‘beijing2‘);

Query OK, 1 row affected (0.00

sec)

mysql> create table test (id int not null

auto_increment,name varchar(10) default null ,index(id)) engine=mrg_myisam

union=(test1,test2) insert_method=last auto_increment=1;

Query

OK, 0 rows affected (0.03 sec)

mysql> select id,name from

test;

+----+----------+

| id | name

|

+----+----------+

|  1 |

beijing1 |

|  1 | beijing2

|

+----+----------+

2 rows in set (0.00

sec)

mysql> INSERT INTO `test` (`name`)

VALUES(‘beijing3‘);

Query OK, 1 row affected (0.00

sec)

mysql> select id,name from test;

+----+----------+

| id | name

|

+----+----------+

|  1 | beijing1

|

|  1 | beijing2 |

|  2 | beijing3

|

+----+----------+

3 rows in set (0.00

sec)

mysql> select id, name from test2

->

;

+----+----------+

| id | name

|

+----+----------+

|  1 | beijing2

|

|  2 | beijing3

|

+----+----------+

2 rows in set (0.00

sec)

mysql> system ls -l

/mysql/data/test

total 164

-rw-rw---- 1 mysql

mysql  8586 Feb  2 16:40 test1.frm

-rw-rw---- 1 mysql

mysql    20 Feb  2 16:40 test1.MYD

-rw-rw---- 1

mysql mysql  2048 Feb  2 16:40 test1.MYI

-rw-rw---- 1

mysql mysql  8586 Feb  2 16:40 test2.frm

-rw-rw---- 1

mysql mysql    40 Feb  2 16:44

test2.MYD

-rw-rw---- 1 mysql mysql  2048 Feb  2 16:44

test2.MYI

-rw-rw---- 1 mysql mysql  8586 Feb  2 16:43

test.frm

-rw-rw---- 1 mysql mysql    32 Feb  2

16:43

test.MRG

在这里需要注意建立MRG_MYISAM需要必须指定一个UNION=(list-of-tables)子句,它说明你要把哪些表当作一个表来用.另外一个重要的参数INSERT_METHOD,此参数INSERT_METHOD

= NO 表示该表不能做任何写入操作只作为查询使用,INSERT_METHOD =

LAST表示插入到最后的一张表里面.

例子:

mysql> show create

table test\G

*************************** 1. row

***************************

Table:

test

Create Table: CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10)

DEFAULT NULL,

KEY `id` (`id`)

)

ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST

UNION=(`test1`,`test2`)

1 row in set (0.00

sec)

mysql> CREATE TABLE `test` (`id` int(11) NOT NULL

AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,KEY `id` (`id`))ENGINE=MRG_MyISAM

DEFAULT CHARSET=utf8 INSERT_METHOD=NO UNION=(`test1`,`test2`);

Query OK, 0 rows affected

(0.00 sec)

mysql> select * from

test;

+----+----------+

| id | name

|

+----+----------+

|  1 | beijing1

|

|  1 | beijing2 |

|  2 | beijing3

|

+----+----------+

3 rows in set (0.00

sec)

mysql> INSERT INTO `test` (`name`)

VALUES(‘beijing4‘);

ERROR 1036 (HY000): Table ‘test‘ is read

only

当你需要在现有MRG_MYISAM添加新表的时候可以这样做

mysql>

create table test3 (id int not null auto_increment,name varchar(10) default null

,primary key (id)) engine=myisam auto_increment=1;

Query

OK, 0 rows affected (0.02 sec)

mysql> alter table test

engine=mrg_myisam union=(test1,test2,test3 ) insert_method=last;

Query OK, 0 rows affected

(0.01 sec)

Records: 0  Duplicates: 0  Warnings:

0

mysql> select * from

test;

+----+----------+

| id | name

|

+----+----------+

|  1 | beijing1

|

|  1 | beijing2 |

|  2 | beijing3

|

+----+----------+

3 rows in set (0.00

sec)

mysql> INSERT INTO `test` (`name`)

VALUES(‘beijing4‘);

Query OK, 1 row affected (0.00

sec)

mysql> select * from test;

+----+----------+

| id | name

|

+----+----------+

|  1 | beijing1

|

|  1 | beijing2 |

|  2 | beijing3

|

|  3 | beijing4

|

+----+----------+

4 rows in set (0.00

sec)

mysql> select * from test3;

+----+----------+

| id | name

|

+----+----------+

|  3 |

beijing4 |

+----+----------+

1 row in set (0.00

sec)

OK,完成了.

对于日常来说MRG_MYISAM分表优点主要有以下几点:

(1)对于日志类应用的表.比如,你可以把每月的数据放进分离的表中,用myisampack压缩这些表,创建一个MERGE表来把它们当作一个表来使用非常方便快捷.

(2)在查询速度上应该更优,对于一些表可以分割大的只读表,放进不同磁盘上的单个表中.基于这个建立一个MERGE表会比单纯一个大表速度应该会快不少.

(3)超过操作系统的文件尺寸限制,每个MyISAM表都受制于这个限制,但是MRG_MYSIAM则不会.

缺点:

MRG_MYISAM不支持全文索引还有其它一些MyISAM功能,当然可以在底表创建,但是仍然不能利用全文索引在MRG表上搜索.建立全文会报如下错误

ERROR

1214 (HY000): The used table type doesn‘t support FULLTEXT indexes