我会简单地在 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

在 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实现自己的物化视图