- r - 以节省内存的方式增长 data.frame
- ruby-on-rails - ruby/ruby on rails 内存泄漏检测
- android - 无法解析导入android.support.v7.app
- UNIX 域套接字与共享内存(映射文件)
当前的实现是一个具有多个连接和临时表的复杂查询,但对我的 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 条件执行 UNION 查询 WHERE line_id IN (<results from previous query>)
.这将消除对连接和临时表的需要,我可以在 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,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+,所以它会快得多。
最后将此查询包含在您的外部查询 Material 中。加入table_manufacture_line
信息,并修正时区。
如果您更早地进行索引和 ORDER BY ... LIMIT
操作,这个查询应该会变得非常快。
您问题中的评论对话框向我表明您可能有多种产品类型,而不仅仅是两种,并且您的分页显示有复杂的选择标准。对大量行使用 UNION ALL
会严重影响性能:它将多个索引表转换为一个内部行列表,根本无法有效地进行搜索。
您真的应该考虑将两种产品数据放在一个表中,而不必 UNION ALL
多个产品表。您现在拥有的设置不灵活,不会轻易扩展。如果您使用主产品表和一些产品特定信息的属性表来构建您的模式,那么两年后您会发现自己会更快乐。严重地。请考虑进行更改。
关于php - 在不严重影响数据库的情况下在分页表中显示大量数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26169567/
我的问题是如何在 python 中创建一个简单的数据库。我的例子是: User = { 'Name' : {'Firstname', 'Lastname'}, 'Address' : {'Street
我需要创建一个与远程数据库链接的应用程序! mysql 是最好的解决方案吗? Sqlite 是唯一的本地解决方案吗? 我使用下面的方法,我想知道它是否是最好的方法! NSString *evento
给定两台 MySQL 服务器,一台本地,一台远程。两者都有一个包含表 bohica 的数据库 foobar。本地服务器定义了用户 'myadmin'@'%' 和 'myadmin'@'localhos
我有以下灵活的搜索查询 Select {vt:code},{vt:productcode},{vw:code},{vw:productcode} from {abcd AS vt JOIN wxyz
好吧,我的电脑开始运行有点缓慢,所以我重置了 Windows,保留了我的文件。因为我的大脑还没有打开,所以我忘记事先备份我的 MySQL 数据库。我仍然拥有所有原始文件,因此我实际上仍然拥有数据库,但
如何将我的 Access 数据库 (.accdb) 转换为 SQLite 数据库 (.sqlite)? 请,任何帮助将不胜感激。 最佳答案 1)如果要转换 db 的结构,则应使用任何 DB 建模工具:
系统检查发现了一些问题: 警告:?:(mysql.W002)未为数据库连接“默认”设置 MySQL 严格模式 提示:MySQL 的严格模式通过将警告升级为错误来修复 MySQL 中的许多数据完整性问题
系统检查发现了一些问题: 警告:?:(mysql.W002)未为数据库连接“默认”设置 MySQL 严格模式 提示:MySQL 的严格模式通过将警告升级为错误来修复 MySQL 中的许多数据完整性问题
我想在相同的 phonegap 应用程序中使用 android 数据库。 更多说明: 我创建了 phonegap 应用程序,但 phonegap 应用程序不支持服务,所以我们已经在 java 中为 a
Time Tracker function clock() { var mytime = new Date(); var seconds
我需要在现有项目上实现一些事件的显示。我无法更改数据库结构。 在我的 Controller 中,我(从 ajax 请求)传递了一个时间戳,并且我需要显示之前的 8 个事件。因此,如果时间戳是(转换后)
我有一个可以收集和显示各种测量值的产品(不会详细介绍)。正如人们所期望的那样,显示部分是一个数据库+建立在其之上的网站(使用 Symfony)。 但是,我们可能还会创建一个 API 来向第三方公开数据
我们将 SQL Server 从 Azure VM 迁移到 Azure SQL 数据库。 Azure VM 为 DS2_V2、2 核、7GB RAM、最大 6400 IOPS Azure SQL 数据
我正在开发一个使用 MongoDB 数据库的程序,但我想问在通过 Java 执行 SQL 时是否可以使用内部数据库进行测试,例如 H2? 最佳答案 你可以尝试使用Testcontainers Test
已关闭。此问题不符合Stack Overflow guidelines 。目前不接受答案。 已关闭 9 年前。 此问题似乎与 a specific programming problem, a sof
我正在尝试使用 MSI 身份验证(无需用户名和密码)从 Azure 机器学习服务连接 Azure SQL 数据库。 我正在尝试在 Azure 机器学习服务上建立机器学习模型,目的是我需要数据,这就是我
我在我的 MySQL 数据库中使用这个查询来查找 my_column 不为空的所有行: SELECT * FROM my_table WHERE my_column != ""; 不幸的是,许多行在
我有那个基地:http://sqlfiddle.com/#!2/e5a24/2这是 WordPress 默认模式的简写。我已经删除了该示例不需要的字段。 如您所见,我的结果是“类别 1”的两倍。我喜欢
我有一张这样的 table : mysql> select * from users; +--------+----------+------------+-----------+ | userid
我有表: CREATE TABLE IF NOT EXISTS `category` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL
我是一名优秀的程序员,十分优秀!