mysql - 在 InnoDB 表上使用 ZF2 分页器的 Count() 的糟糕性能

Question

我正尝试在一些大型(在没有搜索过滤器的最坏情况下大约 1000 万)记录集上使用 ZF2 分页器。我的表是 InnoDB 格式的，据我所知，它并没有作为元数据的一部分保留一个明确的计数。

我意识到我可以扩展 Zend\Paginator\Adapter\DbSelect 类并实现我自己的 count() 方法，该方法使用我手动存储在另一个表中的计数数据，但我不确定如何存储所有的计数可能进行的搜索排列。

默认ZF2 DbSelect adapter使用此方法:

<?php
public function count()
{
    if ($this->rowCount !== null) {
        return $this->rowCount;
    }

    $select = clone $this->select;
    $select->reset(Select::LIMIT);
    $select->reset(Select::OFFSET);
    $select->reset(Select::ORDER);

    $countSelect = new Select;
    $countSelect->columns(array('c' => new Expression('COUNT(1)')));
    $countSelect->from(array('original_select' => $select));

    $statement = $this->sql->prepareStatementForSqlObject($countSelect);
    $result    = $statement->execute();
    $row       = $result->current();

    $this->rowCount = $row['c'];

    return $this->rowCount;
}
?>

这是方法为我生成的一个非常简单的示例查询:

SELECT
    COUNT(1) AS `c`
FROM
    (
        SELECT
            `contacts`.`id` AS `id`,
            `contacts`.`firstname` AS `firstname`,
            `contacts`.`middlename` AS `middlename`,
            `contacts`.`lastname` AS `lastname`,
            `contacts`.`gender` AS `gender`
        FROM
            `contacts`
        WHERE
            `contacts`.`trash` = '0'
    ) AS `original_select`

我不确定 MyISAM 表的性能如何，但这对我来说失败了，因为它占用了它所在的 Amazon RDS(25GB，db.m1.small)实例上的所有可用空间运行。作为比较，仅运行内部(原始)查询，它在 100 秒内完成(当然不好)并返回 739 万条记录。

这是来自内部查询的 EXPLAIN(计数上的 EXPLAIN 也因 RDS 服务器上的磁盘空间而失效):

+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+| id | select_type | table    | type | possible_keys | key   | key_len | ref   | rows    | Extra |+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+| 1  | SIMPLE      | contacts | ref  | trash         | trash | 1       | const | 3441317 |       |+----+-------------+----------+------+---------------+-------+---------+-------+---------+-------+1 rows in set (0.04 sec)

是否可以做些什么来更好地调整它？ ZF2 Paginator 处理计数的方式是否与 InnoDB 做事的方式不兼容？如果我们允许对数据库中的大多数字段进行搜索，其他人将如何处理所有可能查询的缓存计数？

提前致谢...

Answer 1

您不需要从原始查询中进行选择 - 这会占用您的内存/磁盘空间!

SELECT count( 1 ) AS `c`
FROM (
    SELECT 1
    FROM `contacts`
    WHERE `trash` = 0
) AS `original_select`

除此之外:

• 假设 trash 只是一个 bool 值，将其设为 bool 值不可空列并搜索 int 或 bool 值是 true/false

  ALTER TABLE `contacts` CHANGE `trash` `trash` TINYINT( 1 ) NOT NULL 
  

• 一定要索引垃圾列

  ALTER TABLE `contacts` ADD INDEX `TRASH` ( `trash` )
  

更进一步:

• 大型结果集的分页不一定需要精确计数:假设我们每页显示 100 个条目，我们不需要 100000 个单页 N 按钮。而是使用您的偏移量和限制来计算页面，并且只显示单个按钮，例如上一页/下 10 页并将其与一些“显示下一页/上一页”按钮组合。

• 当您需要“转到最后一页”的可能性时，为什么不使用类似 DESC 命令的方式来实现类似的效果。

• 真的会有人对您的 1000 万行进行分页吗？也许提供高级过滤器来帮助用户找到他需要的东西。