如何优化选择300.000行+的SQl代码

I need to make XML files for a table that contains 300k+ records.

The code takes around 3~4s to finish (Is this acceptable?).

Add to that the data retrieval from MySQL, which takes around ~32s to finish (Is this acceptable?):

Query

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

If I run this single query from the navicat mysql side it still takes around ~20s.

What I tried:

  1. At first, the select query did not work because of a "memory exhausted" error (php.ini - memory_limit = 128M)

  2. After that I changed memory_limit to -1. But I see that many people tell it's bad to change memory_limit into -1

So how to optimize the select query for 300k+ records in case of:

  1. using PHP, sql, DOMDocument code only

  2. Use options from #1 combined with an indexed column in the database

  3. anything else that you know ...

PHP code with SQL query:

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;
}

I found out that ORDER BY g.join_at, g.id ASC part impacts the execution time. When I remove it, and use PHP instead for sorting, the execution time decreases from ~50s total to ~5s.

One more thing is that if I set memory_limit to 128M it leads to a "memory exhausted" error (512M will work). Is there any other solution for this problem?

Here are the indexes I currently have on the table:

Index

douwei1408
douwei1408 是的我在问题部分更新了
一年多之前 回复
dph6308
dph6308 你能提供你在桌子上的索引的定义吗?
一年多之前 回复
douzhong6480
douzhong6480 我担心我不能在这里发布信息。非常抱歉:)
一年多之前 回复
dpvmtdu364462
dpvmtdu364462 这不是这个网站的工作方式。答案应该就是这样。问题属于问题部分,而不是答案部分。如果您提供Progman在您的问题中询问的信息将会很好:它是相关的。
一年多之前 回复
dst8922
dst8922 请编辑您的问题以包含SQL查询EXPLAINSELECT的结果....还包括您的表的完整CREATETABLE语句以及您的问题。你桌子上有指数吗?你能在列del_flg和join_at上添加索引吗?
一年多之前 回复
dongmale0656
dongmale0656 你能解释一下,因为我不擅长sql
一年多之前 回复
dtlc84438
dtlc84438 啊。我这样做了一次getInfo()这样的$Data=(new\App\Database\Girls\XML())->getInfo();
一年多之前 回复
duanqiang2977
duanqiang2977 依赖于,如果在循环中调用getInfo(),则prepare语句会提供更好的性能。看看这里:stackoverflow.com/questions/14166798/...
一年多之前 回复
douan4106
douan4106 不做准备()。只需调用query(),因为你没有绑定任何东西
一年多之前 回复

1个回答



排序最好在数据库端完成。 但是您需要定义正确的索引。 在您的情况下, by </ code>的代码很昂贵。 虽然你有 del_flg </ code>的索引可能会阻止表扫描,但是它不能从中产生所需的输出顺序。</ p>

所以我建议改变 索引 del_flg </ code>你必须包含更多字段:</ p>

  DROP INDEX del_flg ON girls; 
CREATE INDEX del_flg ON girls(del_flg,join_at,id) ;
</ code> </ pre>

如果这不会缩短执行时间,则创建一个不包含 del_flag </ code>的索引:</ p>

  CREATE INDEX join_at ON girls(join_at,id); 
</ code> </ pre>
</ div>

展开原文

原文

Sorting is better done on the database side. But you need to define the proper indexes. The order by is expensive in your case. Although you have an index on del_flg which could prevent a table scan, it cannot be benefited from to produce the desired output order.

So I would suggest to alter the index del_flg you have to include more fields:

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

If that does not improve the execution time, then create an index that does not include del_flag:

CREATE INDEX join_at ON girls (join_at, id);

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐