代码之家 › 专栏 › 技术社区 › Air akonsu

在按LIST(n)分区的表中,如何在没有索引的情况下快速选择MAX(n)?

database-partitioning mysql

1

Air akonsu · 技术社区 · 10 年前

我创建了一个MyISAM表,该表根据 year 列,每年都有自己的分区,例如:

CREATE TABLE t (
  id INT UNSIGNED AUTO_INCREMENT
, year SMALLINT(4) UNSIGNED
, ...
, PRIMARY KEY (id, year)
) ENGINE=MyISAM
PARTITION BY LIST (year) (
  PARTITION p0 VALUES IN (2000)
, PARTITION p1 VALUES IN (2001)
, ...
);

我想回答的问题很简单;什么是最大的价值 年 在桌子上?换句话说:

SELECT MAX(year) FROM t;

从表定义来看,似乎应该有一个在恒定时间内运行的查询执行计划没有重新排序PK或在上创建新索引 年 数据库需要做的就是找到定义为具有最大值的分区,然后检查该分区中是否存在任何行;如果没有,请检查下一个最大的,依此类推。不幸的是,优化器选择进行全表扫描。

我可以轻松查询 INFORMATION_SCHEMA 找到的最大值 年 其中存在定义的分区:

SELECT MAX(PARTITION_DESCRIPTION)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't';

然而,这只是告诉我表上定义的分区,而不是表中存在的行;如果某些分区可能是空的,它不一定会给我 MAX(year) 。我尝试添加 EXISTS 条件:

SELECT MAX(PARTITION_DESCRIPTION)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't'
    AND EXISTS(SELECT 1 FROM fact_registration 
               WHERE year = PARTITION_DESCRIPTION);

这是可行的,但所需的时间与整个表扫描的时间一样长。我还尝试在子查询中进行分区选择,但语法不接受用户变量或引用:

SELECT MAX(PARTITION_DESCRIPTION)
FROM
    INFORMATION_SCHEMA.PARTITIONS AS P
WHERE
    TABLE_NAME = 'fact_registration'
        AND EXISTS(SELECT 1 FROM fact_registration 
                   PARTITION(P.PARTITION_DESCRIPTION));  # syntax error, unexpected '.'

那么,我如何消除表扫描并找到 精确的 桌子上的最大值?

1 回复 | 直到 10 年前

1

2

Air akonsu 10 年前

事实证明,答案很简单,只要仔细看看 the columns in the INFORMATION_SCHEMA.PARTITIONS table :

TABLE_ROWS :分区中的表行数。

对于已分区 InnoDB 表中给出的行计数 表格_行 列仅是SQL中使用的估计值并且可能不总是精确的。

所以,为了 特别是MyISAM引擎 ,我们可以找到 MAX(year) 而无需通过使用以下查询进行表扫描或任何附加索引:

SELECT MAX(PARTITION_DESCRIPTION) AS maxyear
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_NAME = 'fact_registration'
        AND TABLE_ROWS > 0;

注意,这只在某些情况下有效 -如果一个分区中有多个值,分区中有行的事实并不一定意味着存在一个或另一个值;从那时起 PARTITION_DESCRIPTION 是一个 longtext 列中,您提供的整数值将存储为逗号分隔的字符串。