大多数时候,LD都看似很闲,所以看书也无目的性。最近看关于MySQL的东西，碰到MERGE这个存储引擎，做个小测试。
我其实只想知道一样东西，用UNION就可以合并两个查询，视图也能合并两张表数据，那么MERGE在合并表的数据后是否提供优化？
LD使用的MySQL是ArchLinux软件库中的，版本5.1.39, 配置文件默认，不做任何内存参数优化(因为使用的数据是真实的，而实际服务器上，运维也使用默认设置);
这两个表2个月前因为表字段的不同，执行一个union all的select语句要 260s 之久; 经过一番优化，已经缩短为2~3s, 但随着数据的增长，执行时间也在增加。LD一直在找解决方案，这么少的数据，还没达到100W，如果不能从SQL优化角度着手，只能从Linux系统和MySQL服务的配置想办法了。 废话不多说，Let’s Go。
一、原数据在InnoDB上的表现
1.1 第一张表 t1 , InnoDB 存储引擎, 50W条数据;
mysql> show create table t1;
+————+——————————-+
| Table | Create Table |
+————+——————————-+
| t1 | CREATE TABLE `t1` (
`object_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL COMMENT ‘内容’,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (`object_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘测试表1′ |
+————+——————————-+
mysql> SELECT count(*) FROM t1;
+———-+
| count(*) |
+———-+
| 543360 |
+———-+
1 row in set (3.29 sec)
1.2第二张表 t2 , InnoDB 存储引擎, 12W条数据;
mysql> show create table t2;
+————+——————————-+
| Table | Create Table |
+————+——————————-+
| t2 | CREATE TABLE `t2` (
`object_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL COMMENT ‘内容’,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (`object_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘测试表1′ |
+————+——————————-+
mysql> SELECT count(*) FROM t2;
+———-+
| count(*) |
+———-+
| 129315 |
+———-+
1 row in set (0.64 sec)
1.3对两张表进行UNION之后的结果
mysql> SELECT count(*) AS t2_sum FROM t2 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ;
+——–+
| t2_sum |
+——–+
| 56926 |
+——–+
1 row in set (0.44 sec)
mysql> SELECT count(*) AS t1_sum FROM t1 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ;
+——–+
| t1_sum |
+——–+
| 234110 |
+——–+
1 row in set (1.82 sec)
mysql> SELECT * FROM t2 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ UNION ALL SELECT * FROM t1 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ORDER BY updated DESC LIMIT 0,50;
+———–+———+
| … 略 … |
+———–+———+
50 rows in set (3.42 sec)
# 多执行几次，时间在2.6 ~ 3.5 之间徘徊
二、把表类型改为MyISAM，再执行Select union
2.1修改数据表类型 站长教学网 eduyo.com
mysql> ALTER TABLE t1 type = MyISAM;
Query OK, 543360 rows affected, 1 warning (4.68 sec)
Records: 543360 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t2 type = MyISAM;
Query OK, 129315 rows affected, 1 warning (1.10 sec)
Records: 129315 Duplicates: 0 Warnings: 0
2.2 执行SELECT UNION
mysql> SELECT * FROM t2 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ UNION ALL SELECT * FROM t1 WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ORDER BY updated DESC LIMIT 0,50;
+———–+———+
| … 略 … |
+———–+———+
50 rows in set (2.11 sec)
# 多执行几次，时间在2.0 ~ 2.2 之间徘徊
三、使用MERGE合并数据表
3.1创建MERGE数据引擎的表 t3_merge
mysql> CREATE TABLE `t3_merge` (
-> `object_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `content` TEXT NOT NULL COMMENT ‘内容’,
-> `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘更新时间’,
-> PRIMARY KEY (`object_id`)
-> ) ENGINE=MERGE DEFAULT CHARSET=utf8 COMMENT=‘测试MERGE表3′
3.2执行SELECT UNION
mysql> SELECT * FROM t3_merge WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ORDER BY updated DESC LIMIT 0,50;
+———–+———+
| … 略 … |
+———–+———+
50 rows in set (2.37 sec)
# 多执行几次，时间在1.62 ~ 1.78 之间徘徊
四、测试视图性能
mysql> CREATE VIEW t4_view AS SELECT object_id, content, updated FROM t2UNION ALL SELECT object_id, content, updated FROM t1;
Query OK, 0 rows affected (0.10 sec)
mysql> SELECT * FROM t4_view WHERE updated BETWEEN ’2009-10-01′ AND ’2009-10-31′ ORDER BY updated DESC LIMIT 0,50;
+———–+———+
| … 略 … |
+———–+———+
50 rows in set (4.22 sec)
# 多执行几次，时间在3.3 ~ 3.5 之间徘徊 站长教学网 eduyo.com
五、结论
仅从最终平均执行时间上看，同时查询两张表的数据，merge胜出。 然而为了使用事务，真实数据表使用的是InnoDB，和第一步相同,而MERGE只支持MyISAM的联合。在这种情况中，如果数据表使用MyISAM，配合MERGE还是可取的一种方式(这里不说优化，因为是否真的优化还得再考量);
很多人认为MySQL会对视图有所优化，但实际使用中，你会发现：复杂的情况下使用视图，会托慢整个系统; 不如自己手工优化SQL语句，这时SQL语句也许会很复杂，但省下的性能开销，还是值得的。
(责任编辑：ken)