php - 使用子查询和Having优化SQL

Question

目前，我们正在使用自定义 CI 库从数据库中作为数据库记录存在的文档生成 PDF 文件。

每个文档都与具有一对多关系的内容(==行)相关。每行都有一个数字(字段:row_span)来指示在 PDF 中打印后将使用多少行。

对于构建的每个 PDF 页面，仅使用子查询选择该页面所需的行:

$where = $this->docType."_id = ".$this->data['doc']->id." AND visible = 1";
$sql = "SELECT *,

    (SELECT
    sum(row_span) FROM app_".$this->docType."_rows X
    WHERE X.position <= O.position 
    AND ".$where." 
    ORDER BY position ASC) 'span_total'

    FROM app_".$this->docType."_rows O 
    WHERE ".$where."
    HAVING span_total > ".(($i-1)*$this->maxRows)." AND span_total <= ".($i*$this->maxRows)." ORDER BY O.position ASC ";

$rows = $rows->query($sql);

在代码中，$i 是页码，$this->maxRows 是从文档模板记录中加载的，它指示 PDF 模板有多少可用行。

因此，当 SQL 呈现时，ID 为 834 的订单的第 1 页可能如下所示:

SELECT `app_order_rows`.*, 
(SELECT SUM(`app_order_rows_subquery`.`row_span`) AS row_span
    FROM `app_order_rows` `app_order_rows_subquery`
    WHERE `app_order_rows_subquery`.`position` <= 'app_order_rows.position'
    AND `app_order_rows_subquery`.`order_id` =  834
    AND `app_order_rows_subquery`.`visible` =  1
    ORDER BY `app_order_rows_subquery`.`position` asc) AS span_total 
FROM (`app_order_rows`) 
WHERE `app_order_rows`.`order_id` =  834
AND `app_order_rows`.`visible` =  1
HAVING span_total >  0
AND span_total <=  45
ORDER BY `app_order_rows`.`position` asc

使用 EXPLAIN 运行此命令会得到以下输出:

+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+
| id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |   |
+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+
| 1  | PRIMARY     | app_order_rows          | ALL  | NULL          | NULL | NULL    | NULL | 1809 | Using where; Using filesort | 1 |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-----------------------------+---+
| 2  | SUBQUERY    | app_order_rows_subquery | ALL  | NULL          | NULL | NULL    | NULL | 1809 | Using where                 | 2 |
+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+

这很有效，但是...当我们有大量订单或发票时，它会使文档变得非常慢。这可能是由于子查询造成的。

有人知道如何在没有子查询的情况下进行相同的选择吗？也许我们将不得不采用一种全新的方法来选择行并构建 PDF。我们欢迎您提出建议^^

提前致谢

---------------------------- 编辑 --------------- ---------------

创建索引后的解释:

+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+
| id | select_type | table                   | type  | possible_keys | key        | key_len | ref   | rows | Extra       |   |
+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+
| 1  | PRIMARY     | app_order_rows          | ref   | index_main    | index_main | 5       | const | 9    | Using where | 1 |
+----+-------------+-------------------------+-------+---------------+------------+---------+-------+------+-------------+---+
| 2  | SUBQUERY    | app_order_rows_subquery | range | index_main    | index_main | 10      | NULL  | 1    | Using where | 2 |
+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+

Answer 1

正如您在评论中确认的那样，这些表没有索引。

直接的解决方案是:

create index index_main on app_order_rows (order_id, position);