php – 在分页表中显示大量数据而不会严重影响DB

2022-09-05 18:13:32

当前的实现是一个具有多个连接和临时表的复杂查询,但是对我的MySQL施加了太大的压力,并且加载表需要30秒. PHP通过JavaScript Ajax调用检索数据并显示在网页上.以下是涉及的表格：

Table: table_companies
Columns: company_id, ...

Table: table_manufacture_line
Columns: line_id, line_name, ...

Table: table_product_stereo
Columns: product_id, line_id, company_id, assembly_datetime, serial_number, ...

Table: table_product_television
Columns: product_id, line_id, company_id, assembly_datetime, serial_number, warranty_expiry, ...

单个公司可以在两个产品表之间分配10万个项目.产品表由line_name联合和过滤,然后按assembly_datetime排序,并根据分页进行限制.日期时间值也依赖于时区,这将作为查询的一部分应用(另一个JOIN临时表). line_name也是返回列之一.

我在考虑从产品联合查询中拆分line_name过滤器.本质上,我确定与过滤器对应的行的ID,然后使用WHERE条件WHERE line_id IN(<以前的查询结果>)执行UNION查询.这将减少对连接和临时表的需求,我可以在PHP中将line_name应用于line_id和时区修改,但我不确定这是解决问题的最佳方法.

我也看过可能使用Redis,但是大量的单个产品在通过PHP将所有数据推送到Redis(20-30秒)时导致同样长的等待时间,即使它只是直接从产品表.

>是否可以调整现有查询以提高效率？
>我可以将一些处理推送到PHP以减少SQL服务器上的负载吗？ Redis怎么样？
>有没有办法更好地构建表格？
>您会建议哪些其他解决方案？

我感谢您提供的任何输入.

编辑：

现有查询：

SELECT line_name,CONVERT_TZ(datetime,'UTC',timezone) datetime,... FROM (SELECT line_name,datetime,... FROM ((SELECT line_id,assembly_datetime datetime,... FROM table_product_stereos WHERE company_id=# ) UNION (SELECT line_id,assembly_datetime datetime,... FROM table_product_televisions WHERE company_id=# )) AS union_products INNER JOIN table_manufacture_line USING (line_id)) AS products INNER JOIN (SELECT timezone FROM table_companies WHERE company_id=# ) AS tz ORDER BY datetime DESC LIMIT 0,100

这里的格式是为了一些可读性.

SELECT line_name,CONVERT_TZ(datetime,'UTC',tz.timezone) datetime,... 
  FROM (SELECT line_name,datetime,... 
          FROM (SELECT line_id,assembly_datetime datetime,... 
                    FROM table_product_stereos WHERE company_id=# 

                 UNION 
                SELECT line_id,assembly_datetime datetime,... 
                  FROM table_product_televisions 
                 WHERE company_id=# 
               ) AS union_products 
         INNER JOIN table_manufacture_line USING (line_id)
        ) AS products 
INNER JOIN (SELECT timezone 
            FROM table_companies 
            WHERE company_id=# 
            ) AS tz 
ORDER BY datetime DESC LIMIT 0,100

ID已编入索引;主键是每列的第一个键.

解决方法:

让我们从其组成部分构建此查询,以查看我们可以优化的内容.

观察：您从两个大型产品表的并集中获取100个最新行.

因此,让我们首先尝试优化从产品表中提取东西的子查询.这是其中之一.

              SELECT line_id,assembly_datetime datetime,... 
                FROM table_product_stereos 
               WHERE company_id=#

但是看,你只需要这里有100个最新的条目.所以,让我们补充一下

               ORDER BY assembly_datetime DESC
               LIMIT 100

这个查询.此外,您应该在此表上放置一个复合索引,如下所示.这将允许索引满足WHERE和ORDER BY查找.

 CREATE INDEX id_date ON table_product_stereos (company_id, assembly_datetime)

所有相同的注意事项都适用于table_product_televisions中的查询.按时间排序,将其限制为100,并将其编入索引.

如果需要应用其他选择条件,可以将它们放在这些内部查询中.例如,在评论中,您提到了基于子字符串搜索的选择.您可以按如下方式执行此操作

              SELECT t.line_id,t.assembly_datetime datetime,... 
                FROM table_product_stereos AS t
                JOIN table_manufacture_line AS m   ON m.line_id = t.line_id 
                                                  AND m.line_name LIKE '%test'
               WHERE company_id=#
               ORDER BY assembly_datetime DESC
               LIMIT 100

接下来,您将使用UNION将这两个查询结果集合并为一个. UNION具有消除重复的功能,这非常耗时. (你知道你没有重复项,但MySQL却没有.)请改用UNION ALL.

把这一切放在一起,最里面的子查询就变成了这个.我们必须包装子查询,因为SQL在同一查询级别被UNION和ORDER BY子句混淆.

           SELECT * FROM (
              SELECT line_id,assembly_datetime datetime,... 
                FROM table_product_stereos 
               WHERE company_id=#
               ORDER BY assembly_datetime DESC 
               LIMIT 100
                         ) AS st
           UNION ALL 
           SELECT * FROM (
             SELECT line_id,assembly_datetime datetime,... 
               FROM table_product_televisions 
              WHERE company_id=#
              ORDER BY assembly_datetime DESC 
              LIMIT 100
                         ) AS tv

这会让你200行.它应该很快得到那些行.

保证200行足以在您执行外部ORDER BY … LIMIT操作后为您提供100个最新项目.但是这个操作只需要处理200行,而不是100K,所以它会快得多.

最后在您的外部查询材料中包装此查询.加入table_manufacture_line信息,并修复时区.

如果您之前执行索引和ORDER BY … LIMIT操作,则此查询应该变得非常快.

问题中的注释对话框向我表明您可能有多种产品类型,而不仅仅是两种,并且您的分页显示具有复杂的选择条件.在大量行上使用UNION ALL可以实现性能：它将多个索引表转换为无法有效搜索的内部行列表.

您真的应该考虑将两种产品数据放在一个表中,而不是UNION ALL多个产品表.您现在拥有的设置不灵活,无法轻松扩展.如果使用主产品表构建模式,并且可能使用某些属性表来构建特定于产品的信息,那么两年后您会发现自己更快乐.认真.请考虑进行更改.

码农公寓

相关文章