MySQL Partition 后续

上一篇文章写到了怎样为 MySQL 的大表分区,这一篇写关于自动化修剪分区的相关内容。

Using storage procedure to prune partitions

MySQL partition 有关于存储过程和变量的限制:

Prohibited constructs. The following constructs are not permitted in partitioning expressions:
Stored procedures, stored functions, UDFs, or plugins.
Declared variables or user variables.

但是,这并不意味着无法在 partition 相关的语句中使用变量,有一个方法可以绕过这个限制: 使用 concat 函数拼接命令。例如:

1
2
3
4
5
6
7
8
SET @sql = CONCAT('ALTER TABLE tbl_data REORGANIZE PARTITION future INTO (PARTITION from'
, CAST(DATE_FORMAT(newpart_date, '%Y%m%d') as char(8))
, ' values less than('
, CAST(UNIX_TIMESTAMP(newpart_date) as char(10))
, '), PARTITION future VALUES LESS THAN (MAXVALUE));');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

与上面分区相匹配的存储过程可以写成这个样子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
DELIMITER $$

DROP PROCEDURE IF EXISTS `UpdatePartitions` $$
CREATE PROCEDURE `UpdatePartitions` ()
BEGIN

DECLARE maxpart_timestamp varchar(10);
DECLARE min_partition varchar(10);
DECLARE minpart_timestamp varchar(10);
-- DECLARE droppart_sql date;
DECLARE newpart_date date;
-- DECLARE newpart_sql varchar(500);

SELECT MIN(PARTITION_DESCRIPTION)
INTO min_partition
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tbl_data' AND TABLE_SCHEMA='dbName';

-- first, deal with pruning old partitions
SELECT FROM_UNIXTIME(min_partition);
WHILE ((FROM_UNIXTIME(min_partition) + INTERVAL 3 DAY) < CURDATE() && min_partition != 'MAXVALUE')
DO

SELECT FROM_UNIXTIME(min_partition);
-- optionally, do something here to deal with the parition you're dropping, e.g.
-- copy the data into an archive table

SELECT MIN(PARTITION_DESCRIPTION)
INTO minpart_timestamp
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tbl_data' AND TABLE_SCHEMA='dbName';

SET @sql := CONCAT('ALTER TABLE tbl_data DROP PARTITION from'
, CAST(DATE_FORMAT(FROM_UNIXTIME(minpart_timestamp), '%Y%m%d') as char(8))
, ';');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT MIN(PARTITION_DESCRIPTION)
INTO min_partition
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tbl_data' AND TABLE_SCHEMA='dbName';

END WHILE;

SELECT MAX(PARTITION_DESCRIPTION)
INTO maxpart_timestamp
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tbl_data' AND
TABLE_SCHEMA='dbName' AND PARTITION_DESCRIPTION != 'MAXVALUE';

IF maxpart_timestamp IS NULL THEN SET maxpart_timestamp = UNIX_TIMESTAMP(CURDATE());
END IF;
-- create enough partitions for at least the next week
WHILE (DATE_FORMAT(FROM_UNIXTIME(maxpart_timestamp), '%Y%m%d') < CURDATE() + INTERVAL 2 DAY)
DO

SET newpart_date := FROM_UNIXTIME(maxpart_timestamp) + INTERVAL 1 DAY;
SET @sql := CONCAT('ALTER TABLE tbl_data REORGANIZE PARTITION future INTO (PARTITION from'
, CAST(DATE_FORMAT(newpart_date, '%Y%m%d') as char(8))
, ' values less than('
, CAST(UNIX_TIMESTAMP(newpart_date) as char(10))
, '), PARTITION future VALUES LESS THAN (MAXVALUE));');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT MAX(PARTITION_DESCRIPTION)
INTO maxpart_timestamp
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tbl_data' AND TABLE_SCHEMA='dbName' AND PARTITION_DESCRIPTION != 'MAXVALUE';

END WHILE;

END $$

DELIMITER ;

修改分区的存储过程就这样可以搞定了,再自动化一点,需要使用 MySQL Scheduler Events:

1
2
3
4
CREATE EVENT `prune_data`
ON SCHEDULE EVERY 1 DAY STARTS date_add(date(curdate() - 1),interval 2 HOUR)
DO
CALL UpdatePartitions();

每天2点,MySQL 会自动调用 UpdatePartitions 完成 Partition 的修整。

  1. 在使用 JDBC 执行 SQL 时,不需要使用 DELIMITER
  2. 使用 JDBC 执行多个语句时,报错的原因有可能是因为 connection 的 allowMultiQuery 未开启:
1
url="jdbc:mysql://localhost/glyndwr?autoReconnect=true&amp;allowMultiQueries=true"

Sample code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
session.doWork(
new Work() {
@Override
public void execute(Connection connection) throws SQLException {
PreparedStatement pStmt = null;
try {
connection.setAutoCommit(false);
pStmt = connection.prepareStatement(createProceduerSQL);
pStmt.executeUpdate();
connection.commit();
logger.info("create procedure commited.");
} catch (Exception e) {
logger.error("create procedure raise exception" + e);
connection.rollback();
} finally {
if (pStmt != null ) {
pStmt.close();
}
}
}
}
);

Query Optimize

之前有提到过 Partition 并不能解决所有性能问题,那么问题来了,什么样的查询可以使用 Partition 进行优化呢?官方文档给出了解答:

The optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:

  1. ~partition_column = constant~
  2. ~partition_column IN (constant1, constant2, …, constantN)~

In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning. See the examples later in this section.

而且同样的优化适用于使用时间进行分区的表:

Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.5, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function.

注: ~UNIX_TIMESTAMP()~ 也适用。参考 Reference 6。

查询 partition:

1
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='table_name' AND TABLE_SCHEMA='database_name';

查询存储过程

1
show procedure status like '%procedure%'

查询 event

1
show events

References

  1. Can MySQL create new partitions from the event scheduler
  2. Using the Event Scheduler
  3. Can I execute multiple queries separated by semicolon with MySQL Connector/J?
  4. How to start a transaction in JDBC?
  5. Multiple queries executed in java in single statement
  6. Partition Pruning