MySQL存储过程用于从相同表中搜索

Question

开发者们，我正在研究如何从具有相同结构的许多表中快速选择行。由于数据量太大，不能将其存放在一个表中，因此每个表包含一个月的数据。 例如log_2011_01，log_2011_02等。这些表都有一个日期时间列created_at。

我需要创建一个存储过程，它必须包含2个日期时间参数以搜索这些日期之间的数据和其他一些内容。

现在我看到一种实现方式：存储过程必须计算两个日期之间的表集合并生成用于选择数据的union_all sql语句。我认为这会很耗费资源，而且不适合高负载，对吗？还有其他的可能性吗？谢谢。

- Fivell

---

听起来你应该使用分区表而不是每个月单独创建表。你是否知道并考虑过按日期进行范围分区？ - searlea

@Fivell：只是好奇，"太多" 是多少？ - ypercubeᵀᴹ

@ypercube：我不知道现在有多少，可能是5-10 GB，但大小每年会增长约10倍。 - Fivell

好的，每月5-10 GB相当多。 - ypercubeᵀᴹ

@searlea：由于其存在一些限制，分区表无法使用。 - Fivell

Answer 1

同意其他评论，但我尝试通过SP帮助您。这个SP构建了一个可以使用预处理语句执行的查询。

-- SP definition:

DELIMITER $$

CREATE PROCEDURE iterateMonths(IN fromDate DATE, IN toDate   DATE)
BEGIN
  DECLARE tempDate DATE;
  DECLARE query    VARCHAR(1000); -- Query string, length might be increased

  SET tempDate = fromDate - INTERVAL (dayofmonth(fromDate) - 1) DAY;

  WHILE tempDate <= toDate
  DO

    IF query IS NOT NULL THEN
      SET query = concat(query, '\r\nUNION ALL\r\n');
    ELSE
      SET query = '';
    END IF;

    SET query = concat(query, 'SELECT * FROM log_', DATE_FORMAT(tempDate, '%Y_%m'));
    SET tempDate = tempDate + INTERVAL 1 MONTH;
  END WHILE;

  SELECT query; -- Output generated query
END
$$

DELIMITER ;

-- Call the SP

SET @fromDate = '2010-07-29';
SET @toDate = '2011-08-29';
CALL iterateMonths(@fromDate, @toDate);

-- Output:

SELECT * FROM log_2010_07
UNION ALL
SELECT * FROM log_2010_08
UNION ALL
SELECT * FROM log_2010_09
UNION ALL
SELECT * FROM log_2010_10
UNION ALL
SELECT * FROM log_2010_11
UNION ALL
SELECT * FROM log_2010_12
UNION ALL
SELECT * FROM log_2011_01
UNION ALL
SELECT * FROM log_2011_02
UNION ALL
SELECT * FROM log_2011_03
UNION ALL
SELECT * FROM log_2011_04
UNION ALL
SELECT * FROM log_2011_05
UNION ALL
SELECT * FROM log_2011_06
UNION ALL
SELECT * FROM log_2011_07
UNION ALL
SELECT * FROM log_2011_08

- Devart

Answer 2

这里没有替代存储过程的选择。考虑到您的表结构，将会有高负载。

- Ravindra S