关键词:
【中文标题】提高mysql查询的性能【英文标题】:Improve performance of mysql query 【发布时间】:2013-11-14 21:57:20 【问题描述】:我有一个 mysql sql select 需要很长时间才能返回数据。
表格
╔════════════════╗ ╔════════════════╗
║ ITEM ║ ║ Workspace ║
╠════════════════║ ╠════════════════║
║ id ║ ║ id ║
║ guid ║ ║ guid ║
║ workspace_id ║ ║ company_id ║
║ deleted ║ ║ deleted ║
╚════════════════╝ ╚════════════════╝
Indexes: id, guid Indexes: id, guid,
workspace_id company_id
╔════════════════╗ ╔════════════════════╗
║ COMPANY ║ ║ item_category_xref ║
╠════════════════║ ╠════════════════════║
║ id ║ ║ item_id ║
║ deleted ║ ║ category_id ║
╚════════════════╝ ╚════════════════════╝
Indexes: id Indexes: item_id, category_id
╔════════════════╗ ╔═════════════════════╗
║ item_image ║ ║ tracking_action ║
╠════════════════║ ╠═════════════════════║
║ item_id ║ ║ id ║
║ sequence ║ ║ guid ║
╚════════════════╝ ║ action ║
Indexes: ║ context ║
(item_id, sequence) ║ deleted ║
╚═════════════════════╝
SQL
SELECT
itm.id "item.id",
ws.id "workspace.id",
co.id "company.id",
((SELECT count(*) FROM item_category_xref icx
WHERE icx.item_id = itm.id
AND icx.featured = 1) > 0) "featured",
(SELECT COUNT(*) FROM tracking_action ta1
WHERE ta1.context = 'ITEM'
AND ta1.context_guid = itm.guid
AND ta1.action = 'VIEW') ta_view_count ,
(SELECT COUNT(*) FROM tracking_action ta2
WHERE ta2.context = 'ITEM'
AND ta2.context_guid = itm.guid
AND ta2.action = 'SEARCH_RESULT') ta_search_count
FROM item itm
JOIN workspace ws
ON itm.workspace_id = ws.id
AND ws.deleted != 1
JOIN company co
ON ws.company_id = co.id
AND co.deleted != 1
JOIN item_category_xref icx
ON itm.id = icx.item_id
AND icx.category_id = 1
LEFT JOIN item_image ii
ON itm.id = ii.item_id
AND ii.sequence = 1
WHERE itm.deleted != 1
HAVING featured > 0;
解释
这个查询是我努力减少和改进的结果。我已经从需要 180 秒的原始查询缩短到现在需要大约 20 秒但仍然不够的查询。
谁能为此查询提供性能改进?
我们正在搜索几百万行数据,因此每一点都会有所帮助。
【问题讨论】:
如果在你的select语句中注释掉三个子查询,它的运行速度有多快? 在此处发布说明语句的输出。 另外,item_category_xref 中有特色字段吗? 你想摆脱什么?在 item_category_xref 中设置了特色字段的所有项目、其查看次数和搜索次数? 你不能用 join 和 group 替换 select (*) from 吗? 【参考方案1】:在您的子查询中使用的许多字段不是索引的一部分。如果您打算在此类查询中大量使用它,请尝试创建与这些子查询对应的复合索引。您可能不需要所有这些(这取决于您的表有多大以及数据在其中的分布方式)。
另外,您没有指定 tracking_action 表的外观,但我看到您在那里使用文本字段(如果它是文本字段),如果该字段没有被索引,那么它也会减慢查询速度。
我会尝试创建(部分)以下复合索引:
item_category_xref - (item_id, featured) and (item_id, category_id)
tracking_action - (context_guid, context, action)
item_image - (item_id, sequence)
【讨论】:
【参考方案2】:我会将您的子查询移到整体查询中更合适的位置。任何你想要的结果,将他们的子查询加入到项目表中。您要比较的东西应该在 where 字段中。此外,您在查询中比较的任何内容都需要编制索引。显而易见的是delete
字段,但我将包括跟踪操作context_guid
和action
字段,可能作为复合索引。另外,我会确保在您的查询中正确引用 action
,因为它是保留字。
这将为您带来额外的好处,即能够分解每个子查询并单独测试它们以寻找性能命中。这将允许您隔离有问题的表或索引。
这是我粗略的凝视点,语法可能并不完美。
SELECT
itm.id "item.id",
ws.id "workspace.id",
co.id "company.id",
tav.ta_view_count,
tas.ta_search_count
FROM item itm
LEFT JOIN (SELECT ta1.context_guid, COUNT(*) as ta_view_count FROM tracking_action ta1 GROUP BY ta1.context_guid HAVING ta1.context_guid = 'ITEM' AND ta1.`action` = 'VIEW') tav ON tav.context_guid = itm.guid
LEFT JOIN (SELECT ta2.context_guid, COUNT(*) as ta_search_count FROM tracking_action ta2 GROUP BY ta2.context_guid HAVING ta2.context_guid = 'ITEM' AND ta2.`action` = 'SEARCH_RESULT') tas ON tas.context_guid = itm.guid
WHERE itm.deleted != 1 AND
itm.id IN (SELECT icx.item_id, COUNT(*) featured FROM item_category_xref icx GROUP BY icx.item_id HAVING featured > 0) AND
itm.id IN (SELECT company.id FROM company WHERE company.deleted != 1) AND
itm.id IN (SELECT workspace.id FROM workspace WHERE workspace.deleted != 1) AND
itm.id IN (SELECT item_image.id FROM item_image WHERE item_image.sequence != 1);
【讨论】:
复制并粘贴你所拥有的,它会返回一个错误。 “有子句”中的未知列“ta1.action” 您的 tracking_action 表中有哪些列?在原始查询中,它引用了一个操作列 如果 ta1.action 没有抛出错误,请删除显式引号并重试。 仍然出现同样的错误,但您的 cmets 和 sql 有帮助如何提高这个 MySQL 查询的性能?
】如何提高这个MySQL查询的性能?【英文标题】:HowcanIimprovetheperformanceofthisMySQLquery?【发布时间】:2010-10-1515:21:11【问题描述】:我有一个MySQL查询:SELECTDISTINCTc.id,c.company_name,cd.firstname,cd.surname,cis.descriptionASindustry_sectorFROM(clientsc)... 查看详情
如何简化/提高这个 MySQL 查询的性能?
】如何简化/提高这个MySQL查询的性能?【英文标题】:HowcanIsimplify/improvetheperformanceofthisMySQLquery?【发布时间】:2011-11-2911:56:04【问题描述】:我是MySQL的新手,多亏了这里更有经验的人的大力支持,我正在努力奋斗,同时在这个... 查看详情
使用 SUM 时提高 MySQL 查询性能
】使用SUM时提高MySQL查询性能【英文标题】:ImprovingMySQLqueryperformancewhenusingSUM【发布时间】:2011-12-2217:30:20【问题描述】:我有一个用户生成的内容表,其中包含用于状态的ENUM格式列。(Pending、Approved、Approved-auto或Rejected)为了... 查看详情
提高 MySQL 查询性能 - 可能的索引问题
】提高MySQL查询性能-可能的索引问题【英文标题】:ImprovingMySQLqueryperformance-possibleindexproblems【发布时间】:2015-02-0910:47:25【问题描述】:我见过很多类似我的问题,但在阅读完所有问题后,我感到很困惑。总而言之-我有一个查... 查看详情
如何强制连接顺序以提高 MYSQL 中的查询性能?
】如何强制连接顺序以提高MYSQL中的查询性能?【英文标题】:HowtoforcetheorderofthejoinstoimprovequeryperformanceinMYSQL?【发布时间】:2020-06-1116:51:09【问题描述】:我有一条sql语句SELECTcount(*)Fromtable1innerjointable2oncondition1..innerjointableNoncondi... 查看详情
需要帮助来提高 MYSQL 子查询性能
】需要帮助来提高MYSQL子查询性能【英文标题】:NeedhelptoimproveMYSQLSubQueryPerformance【发布时间】:2017-07-0705:04:37【问题描述】:我刚刚学习MYSQL,我有这样的MySql子查询:EXPLAINEXTENDEDSELECTbrand_name,stars,hh_stock,hh_stock_value,sales_monthly_1,s... 查看详情
提高查询性能
】提高查询性能【英文标题】:Improvequeryperformance【发布时间】:2013-08-0314:37:07【问题描述】:我需要从PostgreSQL数据库中读取和连接很多行(约500k)并将它们写入MySQL数据库。我的幼稚做法是这样的entrys=Entry.query.yield_per(500)forent... 查看详情
提高Mysql查询性能
】提高Mysql查询性能【英文标题】:ImproveMysqlqueryperformance【发布时间】:2014-08-1400:41:50【问题描述】:我有这张高分表CREATETABLEIFNOTEXISTS高分(盖int(11)NOTNULL,用户名varchar(50)NOTNULL,用户IDint(6)NOTNULL,得分int(16)NOTNULL,dateaddedtimestampNOTNULLd... 查看详情
MYSQL 在使用 OR 时提高查询性能
】MYSQL在使用OR时提高查询性能【英文标题】:MYSQLimprovequeryperformancewhenusingOR【发布时间】:2014-11-0519:34:26【问题描述】:我有一个非常简单的MYSQL表,有2列,我运行这个查询:SELECT*FROMtableWHERE(col1=\'123\'ANDcol2=\'456\')OR(col1=\'456\'AND... 查看详情
如何提高 Django 管理员搜索相关字段(MySQL)中的查询性能
】如何提高Django管理员搜索相关字段(MySQL)中的查询性能【英文标题】:HowtoimprovedqueryperformanceinDjangoadminsearchonrelatedfields(MySQL)【发布时间】:2012-03-2900:20:24【问题描述】:在Django中我有这个:models.pyclassBook(models.Model):isbn=models.... 查看详情
将数据从 EBS 移动到临时存储会提高 MySQL 查询性能吗?
】将数据从EBS移动到临时存储会提高MySQL查询性能吗?【英文标题】:WillmovingdatafromEBStoephemeralstorageimproveMySQLqueryperformance?【发布时间】:2012-04-2411:11:37【问题描述】:我正在使用EBS卷来存储MySQL数据。我在临时设备上也有足够的... 查看详情
如何提高查询性能?
...下所示。 MySQL查询优化器SELECT执行过程那么如何提高MySQL的查询性能呢?首先你需要了解查询优化器处理SQL的全过程。以SELECT的SQL的执行过程为例,如下图所示。 客户端发送一条SELECT查询给服务器;服务器先检... 查看详情
如何提高选择查询的性能
】如何提高选择查询的性能【英文标题】:howtoimproveperformanceofselectquery【发布时间】:2014-06-0317:30:17【问题描述】:我使用mysql在该表中创建了具有45列和总共30000个数据的表我必须从这个表中检索所有数据,但这需要很长时间,... 查看详情
使用 MySQL 视图来提高性能
】使用MySQL视图来提高性能【英文标题】:UsingMySQLviewstoincreaseperformance【发布时间】:2011-08-2316:01:15【问题描述】:假设我有一个带有deleted字段的products表。当产品被删除时,我只是将deleted设置为1,而不是删除实际记录。这样... 查看详情
提高mysql数据库性能
】提高mysql数据库性能【英文标题】:Improvingmysqldatabaseperformance【发布时间】:2013-10-1510:17:38【问题描述】:我的mysql数据库在数据库中的数据中包含2GB数据。当我加入3个表并执行查询时,执行起来会花费很多时间。谁能告诉我... 查看详情
如何在不改变db结构的情况下提高Mysql数据库性能
】如何在不改变db结构的情况下提高Mysql数据库性能【英文标题】:HowtoimproveMysqldatabaseperformancewithoutchangingthedbstructure【发布时间】:2014-12-2601:52:43【问题描述】:我有一个已经在使用的数据库,我必须提高使用该数据库的系统的... 查看详情
如何提高mysql查询速度
在已有的MySQL服务器之上使用ApacheSpark(无需将数据导出到Spark或者Hadoop平台上),这样至少可以提升10倍的查询性能。使用多个MySQL服务器(复制或者PerconaXtraDBCluster)可以让我们在某些查询上得到额外的性能提升。你也可以使用... 查看详情
mysql之性能指标查询
...N语句是一样的,并且分析结果也是一样的。语法:mysql中提高性能的最有效的方式:1,对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快了查询的速度。使用索引查询几种特殊情况: 查看详情