php – 在分页表中显示大量数据而不会严重影响DB
|
当前的实现是一个具有多个连接和临时表的复杂查询,但是对我的
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,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秒)时导致同样长的等待时间,即使它只是直接从产品表. >是否可以调整现有查询以提高效率？ 我感谢您提供的任何输入. 编辑： 现有查询： 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,... 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,tz.timezone) datetime,...
FROM (SELECT line_name,...
FROM (SELECT line_id,...
FROM table_product_stereos WHERE company_id=#
UNION
SELECT line_id,...
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,...
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,...
FROM table_product_stereos
WHERE company_id=#
ORDER BY assembly_datetime DESC
LIMIT 100
) AS st
UNION ALL
SELECT * FROM (
SELECT line_id,...
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多个产品表.您现在拥有的设置不灵活,无法轻松扩展.如果使用主产品表构建模式,并且可能使用某些属性表来构建特定于产品的信息,那么两年后您会发现自己更快乐.认真.请考虑进行更改. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |