mysql - 加快SQL查询速度，已经有效

Question

我有这个查询

SELECT distinct(season) as seasons,
        s.name as seriename,
        c.content_digest,
        c.sub_category_id,
        c.source_url,
        c.synopsis,
        c.short_synopsis,
        c.title,
        c.duration,
        c.subtitle_url,
        c.logo_url,
        c.provider_id,
        c.event_date,
        c.start_time,
        c.live,
        c.seriesid as cSerieId,
        c.episode,
        c.season,
        c.content_id FROM content c
        INNER JOIN provider ON provider.id = c.provider_id
        LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
        WHERE live = '0' AND c.version >= provider.version
        ORDER BY seriename DESC, title
        DESC
        LIMIT 0, 18446744073709551615;

已对其进行索引并进行了一定程度的优化，以快速交付结果(它在 0.3 秒内交付了 44000 行)。由于某种未知的原因，我的齿轮磨损了，速度不再是 0.3 秒，而是 2.5 秒。

这是上面 SQL 的解释

1   SIMPLE  provider    index   PRIMARY,provider_id_idx,provider_version_idx    provider_version_idx    5       16  Using index; Using temporary; Using filesort

1   SIMPLE  c   ref c_providerid_idx,c_live_idx,c_version_idx   c_providerid_idx    5   provider.id 3179    Using where

1   SIMPLE  s   ref seriesid_idx    seriesid_idx    97  c.seriesid  1   

据我了解，它正在使用其索引，但仍然是 2.5 秒..我用 google 搜索了查询缓存，激活了它，尝试调整它，但仍然需要 2.5 秒。

有什么建议吗？如果您需要，我会发布更多信息...

最终结果

SELECT s.name as seriename, 
            c.season as seasons,
            c.content_digest,
            c.sub_category_id,
            c.source_url,
            c.synopsis,
            c.short_synopsis,
            c.title,
            c.duration,
            c.subtitle_url,
            c.logo_url,
            c.provider_id,
            c.event_date,
            c.start_time,
            c.live,
            c.seriesid as cSerieId,
            c.episode,
            c.season,
            c.content_id FROM content c
            INNER JOIN provider ON provider.id = c.provider_id
            LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
            WHERE live = '0' AND c.version >= provider.version
            GROUP BY (CASE WHEN ( seriename IS NOT NULL )
            THEN seriename ELSE title END) 
            ORDER BY seriename DESC, title DESC
            LIMIT 0, 50

Answer 1

根据服务器的负载和数据集的大小，请求的时间可能会有所不同。

我不确定，但有时将 c.version >=provider.version 与 join 一起使用而不是在 WHERE 中使用会有所不同。请参阅下面的完整查询。让我知道 EXPLAIN 结果。

SELECT distinct(season) as seasons,
    s.name as seriename,
    c.content_digest,
    c.sub_category_id,
    c.source_url,
    c.synopsis,
    c.short_synopsis,
    c.title,
    c.duration,
    c.subtitle_url,
    c.logo_url,
    c.provider_id,
    c.event_date,
    c.start_time,
    c.live,
    c.seriesid as cSerieId,
    c.episode,
    c.season,
    c.content_id FROM content c
    INNER JOIN provider ON provider.id = c.provider_id 
    AND c.version >= provider.version
    LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
    WHERE live = '0' 
    ORDER BY seriename DESC, title
    DESC
    LIMIT 0, 18446744073709551615;

还在 content 表中创建 (version, live, id) 列的复合(多列)索引。这是创建多列索引的查询:更改表内容添加索引idxc_vr_lv_id(版本，实时，id)；