在 MERGE存储引擎的简单测试 – MySQL邯郸学步 一文中,有一个Union SELECT, 极其消耗时间, 仔细分析会发现,使用的搜索条件对应字段是TIMESTAMP, 索引几乎没用. 那么我们能不能绕开时间字段,使用作为主键的自增字段object_id 呢? 就单纯示例上这种情况,是可以的. 请看下面的存储过程:
1 DELIMITER $$
2
3 DROP PROCEDURE IF EXISTS `testMERGE`.`unionSelect`$$
4 CREATE PROCEDURE `testMERGE`.`unionSelect` (IN startTime CHAR(16), IN endTime CHAR(16))
5 BEGIN
6 /* 表test1结果集的总数，结果集开始ID， 结束ID */
7 DECLARE t1_count BIGINT DEFAULT 0;
8 DECLARE t1_startId BIGINT DEFAULT 0;
9 DECLARE t1_endId BIGINT DEFAULT 0;
10 DECLARE t2_count BIGINT DEFAULT 0;
11 DECLARE t2_startId BIGINT DEFAULT 0;
12 DECLARE t2_endId BIGINT DEFAULT 0;
13
14 /* 先确定两个结果集的开始、结束ID，判断小集合; */
15 SELECT object_id INTO t1_startId FROM t1 WHERE date_format(updated, ‘%Y-%m-%d’) >= startTime ORDER BY object_id LIMIT 1;
16 SELECT object_id INTO t1_endId FROM t1 WHERE date_format(updated, ‘%Y-%m-%d’) <= endTime ORDER BY object_id DESC LIMIT 1;
17 SET t1_count = t1_endId – t1_startId;
18
19 SELECT object_id INTO t2_startId FROM t2 WHERE date_format(updated, ‘%Y-%m-%d’) >= startTime ORDER BY object_id LIMIT 1;
20 SELECT object_id INTO t2_endId FROM t2 WHERE date_format(updated, ‘%Y-%m-%d’) <= endTime ORDER BY object_id DESC LIMIT 1;
21 SET t2_count = t2_endId – t2_startId;
22
23 /* 小集合驱动大集合; 利用主键object_id作为搜索条件！ */
24 IF t1_count < t2_count THEN
25 SELECT * FROM t1 WHERE object_id BETWEEN t1_startId AND (t1_endId+1)
26 UNION ALL SELECT * FROM t2 WHERE object_id BETWEEN t2_startId AND (t2_endId+1)
27 ORDER BY updated DESC LIMIT 0,50;
28 ELSE
29 SELECT * FROM t2 WHERE object_id BETWEEN t2_startId AND (t2_endId+1)
30 UNION ALL SELECT * FROM t1 WHERE object_id BETWEEN t1_startId AND (t1_endId+1)
31 ORDER BY updated DESC LIMIT 0,50;
32 END IF;
33
34 END$$
35
36 DELIMITER ;
mysql> call unionSelect(’2009-10-01′, ’2009-10-31′);
——->
Query OK, 0 rows affected (0.97 sec)
多次执行，执行时间为维持在1s左右; 执行时间是原来的一半不到;
LD不是DBA，不过开发中消耗资源比较大的SELECT语句，尽量使用索引，尤其是唯一索引。
最新评论