关键词:
【中文标题】如何简化/提高这个 MySQL 查询的性能?【英文标题】:How can I simplify/improve the performance of this MySQL query? 【发布时间】:2011-11-29 11:56:04 【问题描述】:我是 MySQL 的新手,多亏了这里更有经验的人的大力支持,我正在努力奋斗,同时在这个过程中学到了很多东西。
我有一个完全符合我要求的查询。但是,它对我来说看起来非常混乱,我确信必须有一种方法来简化它。
如何改进和优化此查询的性能?
非常感谢
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable b
LEFT JOIN (
SELECT COUNT(*) AS projects_count, a.songs_id
FROM $sTable2 a
GROUP BY a.songs_id
) bb ON bb.songs_id = b.songsID
LEFT JOIN (
SELECT AVG(rating) AS rating, COUNT(rating) AS ratings_count, c.songid
FROM $sTable3 c
GROUP BY c.songid
) bbb ON bbb.songid = b.songsID
LEFT JOIN (
SELECT c.songid, c.userid,
CASE WHEN EXISTS
(
SELECT songid
FROM $sTable3
WHERE songid = c.songid
) Then 'User Voted'
else
(
'Not Voted'
)
end
AS voted
FROM $sTable3 c
WHERE c.userid = $userid
GROUP BY c.songid
) bbbb ON bbbb.songid = b.songsID
编辑:这里是查询正在做什么的描述:-
我有三张桌子:
$sTable = 歌曲表(songid、mp3link、artifact、useruploadid 等等)
$sTable2 = 包含链接到它们的歌曲的项目表(projectid, songid、项目名称等)
$sTable3 = 歌曲评分表(songid、userid、评分)
所有这些数据都输出到 JSON 数组并显示在我的应用程序的表格中,以提供歌曲列表,并结合项目和收视率数据。
查询本身按以下顺序执行以下操作:-
-
从 $sTable 中收集所有行
在 songID 上加入 $sTable2 并计算此表中具有相同 songID 的行(项目)的数量
在 songID 上加入 $stable3 并计算此表中具有相同歌曲 ID 的“评分”列的平均值
此时它还会计算 $sTable3 中具有相同 songID 的总行数,以提供总票数。
最后,它对所有这些行执行检查,以查看 $userid(这是一个包含登录用户 ID 的变量)是否与 $sTable3 中每行的“userid”存储相匹配,以检查是否存在用户是否已经对给定的歌曲 ID 进行了投票。如果匹配,则返回“用户投票”,否则返回“未投票”。它将它作为一个单独的列输出到我的 JSON 数组中,然后我在我的应用程序中检查客户端并向其中添加一个类。
如果有人需要更多详细信息,请告诉我。谢谢大家。
编辑:
感谢 Aurimis 出色的第一次尝试,我正在接近一个更简单的解决方案。
这是我根据该建议尝试的代码。
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM
(SELECT
$sTable.songsID, COUNT(rating) AS ratings_count,
AVG(rating) AS ratings
FROM $sTable
LEFT JOIN $sTable2 ON $sTable.songsID = $sTable2.songs_id
LEFT JOIN $sTable3 ON $sTable.songsID = $sTable3.songid
GROUP BY $sTable.songsID) AS A
LEFT JOIN $sTable3 AS B ON A.songsID = B.songid AND B.userid = $userid
但是有几个问题。我不得不删除您答案的第一行,因为它导致了 500 内部服务器错误:
IF(B.userid = NULL, "Not voted", "User Voted") AS voted
显然现在“投票检查”功能已丢失。
此外,更重要的是,它不会返回我的数组中定义的所有列,只返回歌曲 ID。我的 JSON 在“字段列表”中返回未知列“歌曲名称”-如果我将其从我的 $aColumns 数组中删除,它当然会移至下一个。
我在脚本开头定义列,因为该数组用于过滤和汇总 JSON 编码的输出。这是$aColumns的定义:-
$aColumns = array( 'songsID', 'song_name', 'artist_band_name', 'author', 'song_artwork', 'song_file', 'genre', 'song_description', 'uploaded_time', 'emotion', 'tempo', 'user', 'happiness', 'instruments', 'similar_artists', 'play_count', 'projects_count', 'rating', 'ratings_count', 'voted');
为了快速测试查询的其余部分,我修改了子查询中的第一行以选择 $sTable.* 而不是 $sTable.songsID(记住 $sTable 是歌曲表)
然后...查询显然有效,但当然性能很差。但仅返回 5000 首歌曲测试数据集中的 24 首歌曲。因此,我将您的第一个“JOIN”更改为“LEFT JOIN”,以便返回所有 5000 首歌曲。为了澄清查询,需要返回歌曲表中的所有行,但要返回每首歌曲的项目和评分表中的各种额外数据。
所以...我们正在到达那里,我确信这是一个更好的方法,它只需要一些修改。到目前为止,感谢您对 Aurimis 的帮助。
【问题讨论】:
看来写查询的人对MySQL并不陌生 我自己通过“谷歌搜索”写的,在这里提出了各种问题,并阅读了很多。但是,我本质上将几个部分拼凑到一个“工作”查询中,以实现我想要的。尽管我了解查询的每个部分的作用及其工作原理,但我没有经验知道我是否以有效的方式执行此操作。我对其他语言的经验告诉我,我不是。因此,我欢迎更有经验的 MySQL 开发人员的建议。 :) 你能描述一下预期的结果应该是什么吗? 我刚刚更新了我的原始问题,以便为您提供更多详细信息。干杯。 您能否发布表的创建表语句。查看索引和数据类型会很有用。 【参考方案1】:SELECT SQL_CALC_FOUND_ROWS
songsID, song_name, artist_band_name, author, song_artwork, song_file,
genre, song_description, uploaded_time, emotion, tempo,
`user`, happiness, instruments, similar_artists, play_count,
projects_count,
rating, ratings_count,
IF(user_ratings_count, 'User Voted', 'Not Voted') as voted
FROM (
SELECT
sp.songsID, projects_count,
AVG(rating) as rating,
COUNT(rating) AS ratings_count,
COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count
FROM (
SELECT songsID, COUNT(*) as projects_count
FROM $sTable s
LEFT JOIN $sTable2 p ON s.songsID = p.songs_id
GROUP BY songsID) as sp
LEFT JOIN $sTable3 r ON sp.songsID = r.songid
GROUP BY sp.songsID) as spr
JOIN $sTable s USING (songsID);
您将需要以下索引:
(songs_id)在 $sTable2 上 $sTable3 上的组合(歌曲 ID、评分、用户 ID)查询背后的想法:
子查询使用 INT 操作,以便子查询的结果可以轻松放入内存中 左连接单独分组以减少笛卡尔积 用户投票计入与其他评分相同的子查询中,以避免昂贵的相关子查询 所有其他信息都在最终加入时检索【讨论】:
感谢 newtover 的建议。直到周末之后,我才能检查您的解决方案。快速浏览一下,我觉得一切都很好,我会在星期一告诉你。 :-) 太棒了...它运行良好。就其执行而言,它更容易理解并且无限优雅。我还没有进行任何性能测试,但从最初的印象来看,它肯定不会更糟,使用您的查询将使任何未来的调整更容易管理,因为它在结构上也更优越。非常感谢 newtover,一个非常好的答案!【参考方案2】:让我根据您的描述而不是查询来尝试。我将使用Songs
表示Table1
,Projects
表示Table2
和Ratings
表示Table3
- 为了清楚起见。
SELECT
/* [column list again] */,
IF(B.userid = NULL, "Not voted", "Voted") as voted
FROM
(SELECT
Songs.SongID, count(rating) as total_votes,
avg(rating) as average_rating /*[,.. other columns as you need them] */
FROM Songs
JOIN Projects ON Songs.SongID = Projects.SongID
LEFT JOIN Ratings ON Songs.SongID = Ratings.SongID
GROUP BY Songs.SongID) as A
LEFT JOIN Ratings as B ON A.SongID = B.SongID AND B.userid = ? /* your user id */
如您所见,您可以在一个相对简单的查询中获取所有歌曲信息(只需使用 Group by 和 count() / avg() 函数)。要获取歌曲是否被特定用户评分的信息,需要一个子查询——您可以在其中执行 LEFT JOIN,如果用户 ID 为空——您知道他没有投票。
现在,我没有深入研究您的查询,因为它看起来确实很复杂。可能是我错过了什么 - 如果是这种情况,请更新描述,我可以再试一次:)
【讨论】:
这看起来很棒 Aurimas,非常感谢您的建议。再过一个小时左右,我就无法测试这个了。我会告诉你进展如何。 感谢 Aurimas... 自从尝试您的建议以来,我已经用我的发现更新了我的问题。干杯。 :)如何提高 Django 管理员搜索相关字段(MySQL)中的查询性能
】如何提高Django管理员搜索相关字段(MySQL)中的查询性能【英文标题】:HowtoimprovedqueryperformanceinDjangoadminsearchonrelatedfields(MySQL)【发布时间】:2012-03-2900:20:24【问题描述】:在Django中我有这个:models.pyclassBook(models.Model):isbn=models.... 查看详情
如何提高MYSQL查询的性能?
】如何提高MYSQL查询的性能?【英文标题】:HowtoimprovetheperformanceofMYSQLquery?【发布时间】:2013-03-2100:32:58【问题描述】:我目前正在将数据馈送上传到我的数据库中。由于存在超过1千万条记录,我的数据库中被转储了。我需要改... 查看详情
如何提高选择查询的性能
】如何提高选择查询的性能【英文标题】:howtoimproveperformanceofselectquery【发布时间】:2014-06-0317:30:17【问题描述】:我使用mysql在该表中创建了具有45列和总共30000个数据的表我必须从这个表中检索所有数据,但这需要很长时间,... 查看详情
如何强制连接顺序以提高 MYSQL 中的查询性能?
】如何强制连接顺序以提高MYSQL中的查询性能?【英文标题】:HowtoforcetheorderofthejoinstoimprovequeryperformanceinMYSQL?【发布时间】:2020-06-1116:51:09【问题描述】:我有一条sql语句SELECTcount(*)Fromtable1innerjointable2oncondition1..innerjointableNoncondi... 查看详情
如何提高这个慢查询的性能
】如何提高这个慢查询的性能【英文标题】:HowcanIimprovetheperformanceofthisslowquery【发布时间】:2014-07-1705:45:04【问题描述】:我有以下查询,大约需要10分钟。我需要这个更快。有什么想法可以调整此查询吗?r_pos_transaction_head表... 查看详情
如何提高这个 MySQL 查询的速度?
】如何提高这个MySQL查询的速度?【英文标题】:HowcanIimprovethespeedofthisMySQLquery?【发布时间】:2013-10-0715:34:58【问题描述】:这是查询:SELECTu.uidasUID,fuo.uidasFUO_UID,fo.pridasFO_NAMEFROMusersuLEFTOUTERJOINfirstpoint_users_organisationsfuoON(u.uid=fuo. 查看详情
如何提高查询性能?
...方法,互联网公司常用的SQL编写规范,以及在实际情况中如何优化数据库访问等内容,知识脑图如下所示。 MySQL查询优化器SELECT执行过程那么如何提高MySQL的查询性能呢?首先你需要了解查询优化器处理SQL的全过程。以SE... 查看详情
如何提高这个mysql查询的速度
】如何提高这个mysql查询的速度【英文标题】:Howtoimprovethismysqlqueryinspeed【发布时间】:2010-07-0221:26:08【问题描述】:这里是SELECTtbl_rls.*,(SELECTcount(*)FROM`comments`WHERE`post_id`=`tbl_rls`.`id`)AS`t_comments`FROM`tbl_rls`WHERE1=1AND`status`<> 查看详情
MYSQL 在使用 OR 时提高查询性能
】MYSQL在使用OR时提高查询性能【英文标题】:MYSQLimprovequeryperformancewhenusingOR【发布时间】:2014-11-0519:34:26【问题描述】:我有一个非常简单的MYSQL表,有2列,我运行这个查询:SELECT*FROMtableWHERE(col1=\'123\'ANDcol2=\'456\')OR(col1=\'456\'AND... 查看详情
如何提高mysql查询速度
在已有的MySQL服务器之上使用ApacheSpark(无需将数据导出到Spark或者Hadoop平台上),这样至少可以提升10倍的查询性能。使用多个MySQL服务器(复制或者PerconaXtraDBCluster)可以让我们在某些查询上得到额外的性能提升。你也可以使用... 查看详情
提高mysql查询的性能
】提高mysql查询的性能【英文标题】:Improveperformanceofmysqlquery【发布时间】:2013-11-1421:57:20【问题描述】:我有一个mysqlsqlselect需要很长时间才能返回数据。表格╔════════════════╗╔══════════... 查看详情
如何提高此视图/查询的性能?
】如何提高此视图/查询的性能?【英文标题】:HowtoincreasetheporformanceofthisView/Query?【发布时间】:2021-02-1503:27:48【问题描述】:我有一个MysqlView,它运行大约9秒。我真的需要提高这个视图的性能。这是我的代码:SELECT`tad`.`TRIP_AS... 查看详情
如何提高 PostgreSQL LIKE %text% 查询性能
】如何提高PostgreSQLLIKE%text%查询性能【英文标题】:HowtoimprovePostgreSQLLIKE%text%queryperformance【发布时间】:2020-11-2512:04:31【问题描述】:我有3个表:请求、步骤和有效负载。每个请求有N个步骤(所以是一对多),每个步骤有1个有... 查看详情
如何优化 mysql 查询
】如何优化mysql查询【英文标题】:howtooptimizethemysqlQuery【发布时间】:2016-12-1013:15:13【问题描述】:如何提高这个mysql查询的性能SELECT\'\'ASsharedid,hubber_posts.userIDASpostowner,hubber_posts.*,\'\'ASsharedby,hubber_posts.userIDASuserID,hubber_posts.post 查看详情
如何提高数据库查询的性能?(代码片段)
...dView。我试图加入2个SQL句子,但失败了。另外,我不知道如何简化Stock等的检查。我试图将所有信息输入到数组然后将其作为DataGridViewSource传递,而不是逐行插入,但对搜索词没有影响。conn.Open();MySqlTransactiontrans=conn.BeginTransaction... 查看详情
如何在不改变db结构的情况下提高Mysql数据库性能
】如何在不改变db结构的情况下提高Mysql数据库性能【英文标题】:HowtoimproveMysqldatabaseperformancewithoutchangingthedbstructure【发布时间】:2014-12-2601:52:43【问题描述】:我有一个已经在使用的数据库,我必须提高使用该数据库的系统的... 查看详情
提高这个慢查询的性能
】提高这个慢查询的性能【英文标题】:improvetheperformanceofthisslowquery【发布时间】:2015-07-2309:34:27【问题描述】:我想调整这个查询以获得更好的性能。查询:SELECT`DeviceRawUsage`.`duration`,`DeviceRawUsage`.`current`FROM`epowerg`.`device_raw_usag... 查看详情
提高不同的查询性能
...rformance【发布时间】:2019-12-1511:51:40【问题描述】:知道如何改进这个查询执行吗?(也许有一些预聚合)?SELECTp.segment,country,count(distinctuserid)frompixel_data_optpWHEREcountryin(\'US\')andsegmentisnotnullGROUPBYp.s 查看详情