时间戳

首页 > 解决方案 > 如何优化选择 300.000 行的 MySQLl 代码 +

php - 如何优化选择 300.000 行的 MySQLl 代码 +

问题描述

我需要为包含 300k+ 记录的表制作 XML 文件。

代码大约需要 3~4 秒才能完成（这可以接受吗？）。

再加上从 MySQL 检索数据，大约需要 32 秒才能完成（这可以接受吗？）：

询问

SELECT `id`, `join_at` 
FROM girls g 
WHERE g.del_flg = 0 
ORDER BY g.join_at, g.id ASC

如果我从 navicat mysql 端运行这个单个查询，它仍然需要大约 20 秒。

我尝试了什么：

  1. 起初，由于“内存耗尽”错误（php.ini - memory_limit = 128M），选择查询不起作用

  2. 之后我改为memory_limit-1。但是我看到很多人说memory_limit改成-1不好

那么在以下情况下如何优化select对 300k+ 记录的查询：

  1. 仅使用 PHP、sql、DOMDocument 代码

  2. 使用 #1 中的选项与数据库中的索引列相结合

  3. 还有什么你知道的...

带有 SQL 查询的 PHP 代码：

public function getInfo() {
    MySQL::connect();
    try {
        $select = 'SELECT `id`, `join_at`';
        $sql = ' FROM girls g';
        $sql .= ' WHERE g.del_flg = 0';
        $sql .= ' ORDER BY g.join_at, g.id ASC';
        $sql = sprintf($sql, $this->table);
        MySQL::$sth = MySQL::$pdo->prepare($select . $sql);
        MySQL::$sth->execute();
        while($rows = MySQL::$sth->fetch(\PDO::FETCH_ASSOC)) {
            $values[] = array('id' => $rows['id'], 'join_at' => $rows['join_at']);
        }
        // $rows = MySQL::$sth->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $e) {
        return null;
    }
    return $values;
}

我发现那ORDER BY g.join_at, g.id ASC部分会影响执行时间。当我删除它并使用 PHP 进行排序时，执行时间从总共约 50 秒减少到约 5 秒。

另一件事是，如果我设置memory_limit为 128M，它会导致“内存耗尽”错误（512M 可以工作）。这个问题还有其他解决方案吗？

以下是我目前在桌子上的索引：

指数

标签： phpmysqlbulk

解决方案

排序最好在数据库端完成。但是您需要定义正确的索引。在你的情况下order by是昂贵的。尽管您有一个del_flg可以防止表扫描的索引，但它不能从产生所需的输出顺序中受益。

所以我建议改变del_flg你必须包含更多字段的索引：

DROP INDEX del_flg ON girls;
CREATE INDEX del_flg ON girls (del_flg, join_at, id);

如果这不能提高执行时间，则创建一个不包括的索引del_flag：

CREATE INDEX join_at ON girls (join_at, id);

推荐阅读