sql - 如何优化此 MYSQL JOIN 查询?

我在处理 query optimization 时遇到了困难,我相信目前正在使用的那个可以改进很多。

我有 4 个 tables;

Artist (14,930 rows)
artist_id artist_name artist_slug artist_country
1 Elle elle 5
Country (85 rows)
country_id country_name country_slug
1 USA usa
Song (61,119 rows)
song_id song_title song_artist_id song_plays
1 Kitty kat 1 38291
2 Donkey 1 8291
Lyrics (61,119 rows)
lyrics_id lyrics_song_id lyrics_body
1 1 Kitty Kat lyrics
2 2 Donkey lyrics

我正在加入 tables 使用此查询获取单个艺术家的数据加上、国家、歌曲总数、艺术家在所有歌曲上播放的总和以及歌词总数。

SELECT /*+ MAX_EXECUTION_TIME(2000) */ 
artist.*,
country.*,
COUNT(song.song_id) AS TotalSongs,
COUNT(lyrics.lyrics_id) AS TotalLyrics,
SUM(song.song_plays) AS TotalPlays
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
LEFT JOIN song ON artist.artist_id = song.song_artist_id
LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id 
WHERE artist_status = 'enabled' AND artist.artist_slug = '$slug'
GROUP BY artist.artist_id LIMIT 0, 1

如何改进它以更快地执行并使用更少的资源?

提前致谢。

回答1

我会简单地在 select 子句中使用子查询进行聚合:

SELECT artist.*, country.*, (
    SELECT COUNT(*)
    FROM song
    WHERE song.song_artist_id = artist.artist_id
) AS total_songs, (
    SELECT SUM(song_plays)
    FROM song
    WHERE song.song_artist_id = artist.artist_id
) AS total_plays, (
    SELECT COUNT(*)
    FROM song
    JOIN lyrics ON song.song_id = lyrics.lyrics_song_id
    WHERE song.song_artist_id = artist.artist_id
) AS total_lyrics
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
WHERE artist_status = 'enabled'
AND artist.artist_slug = :slug

在 MySQL 8 或更高版本中,您可以使用横向连接:

SELECT artist.*, country.*, lj.*
FROM (artist LEFT JOIN country ON artist.artist_country_id = country.country_id), LATERAL (
    SELECT COUNT(song.song_id) total_songs, SUM(song_plays) total_plays, COUNT(lyrics.lyrics_id) total_lyrics
    FROM song
    LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id
    WHERE song.song_artist_id = artist.artist_id
) AS lj
WHERE artist_status = 'enabled'
AND artist.artist_slug = :slug

回答2

在 tables 上创建所有索引,列如下:

CREATE INDEX index_name1 ON artist (artist_country_id ); 
CREATE INDEX index_name2 ON country (country_id); 
CREATE INDEX index_name3 ON artist (artist_id ); 
CREATE INDEX index_name4 ON song (song_artist_id); 
CREATE INDEX index_name5 ON artist (artist_status );

并创建一个这样的视图:

CREATE VIEW customerPayments
AS 
SELECT /*+ MAX_EXECUTION_TIME(2000) */ 
artist.*,
country.*,
COUNT(song.song_id) AS TotalSongs,
COUNT(lyrics.lyrics_id) AS TotalLyrics,
SUM(song.song_plays) AS TotalPlays
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
LEFT JOIN song ON artist.artist_id = song.song_artist_id
LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id 
WHERE artist_status = 'enabled' AND artist.artist_slug = '$slug'
GROUP BY artist.artist_id LIMIT 0, 1;

视图是存储在 database 目录中的命名查询

查看并没有物理地store 数据。当您针对视图发出 SELECT 语句时,MySQL 执行视图定义中指定的基础查询并返回结果集。因此,有时,视图被称为虚拟 table。

物化视图 (MV) 是查询的预计算(物化)结果。与简单的 VIEW 不同,物化视图的结果存储在某个地方,通常在 table 中。当需要立即响应并且物化视图所基于的查询需要很长时间才能产生结果时,使用物化视图。物化视图必须不时刷新。这取决于物化视图的刷新频率及其内容的实际程度。基本上,物化视图可以立即或延迟刷新,它可以完全刷新或到某个时间点。 MySQL 本身不提供物化视图。但是自己构建物化视图很容易。

由于物化视图基于缓存获取数据,因此它们比视图更快。 Mysql中没有物化视图的概念。但是你可以通过https://fromdual.com/mysql-materialized-views实现自己的物化视图

相似文章