当前位置：首页 → 问答吧 → merge引擎的疑问

merge引擎的疑问

时间：2011-08-14

来源：互联网

最近看文档有提到merge引擎有这么个有点：查询时候能够访问更少的数据，只在目标子表进行查询。
这是不是意味着类似分区表的功能？

但是我实验好像不是这样的：

QUOTE:mysql> create table t1 (tp char(10),id int , name char(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (tp char(10),id int , name char(20)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 (tp char(10),id int , name char(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_merge (tp char(10),id int , name char(20)) engine=merge union=(t2,t3);
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

mysql> explain select * from t1 where tp='a' ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra    |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE    | t1 | ALL  | NULL       | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t_merge where tp='a' ;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra    |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE    | t_merge | ALL  | NULL       | NULL | NULL | NULL | 8 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table t1 add index (tp);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table t_merge add index (tp);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t2 add index (tp);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t3 add index (tp);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where tp='a';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra    |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE    | t1 | ref  | tp          | tp | 31    | const | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t_merge where tp='a';
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra    |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE    | t_merge | ref  | tp          | tp | 31    | const | 4 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

是我理解有误，还是实验有误，还是explain的rows不是实际查询的数据量？？？？？

[ 本帖最后由 linjia828 于 2011-8-14 14:06 编辑 ]

作者: linjia828 发布时间: 2011-08-14

另外为什么下面这个查询检索的数据量只有3呢？数据应该是4条才对。

QUOTE:mysql> select * from t1 where tp='a';
+------+------+------+
| tp | id | name |
+------+------+------+
| a | 1 | aaa  |
| a | 1 | bbb  |
| a | 2 | ccc  |
| a | 2 | ddd  |
+------+------+------+
4 rows in set (0.00 sec)

mysql> explain select * from t1 where tp='a';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra    |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE    | t1 | ref  | tp          | tp | 31    | const | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

作者: linjia828 发布时间: 2011-08-14

merge引擎的疑问

热门阅读

热门下载