在MySQL中缓存/重新使用子查询 [英] Cache/Re-Use a Subquery in MySQL

问题描述

我有一个非常复杂的MySQL查询，该查询包含三次使用同一子查询. MySQL实际上会运行子查询3次吗? (这是很昂贵的.)如果是这样，我是否有办法告诉MySQL保存或缓存结果，所以它不会这样做?我可以将数据保存在一个大数组中，然后将其重新馈送到MySQL，但我不想那样将其移出并移回数据库中.

I have a very complex MySQL query that includes use of the same subquery three times. Will MySQL actually run the subquery three times? (It's an expensive one.) If so, is there a way for me to tell MySQL to save or cache the results so it won't do that? I could save the data in a large array then re-feed it to MySQL, but I'd rather not move it out and back into the database like that.

这是出现三次的子查询:

This is the subquery that appears three times:

SELECT id FROM programs 
WHERE submitter_id=32 AND id in (
    SELECT id FROM programs 
    WHERE feed_id=2478 AND id in (
        SELECT program_id FROM playlist_program_map 
        WHERE playlist_id=181)))

以下是完整查询的示例，其中显示了该查询:

And here's an example of the full query in which the query appears:

SELECT object_id, programs.created AS created, 
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
FROM comments_programs USE INDEX (text) 
LEFT JOIN programs ON programs.id=object_id 
WHERE object_id IN (
    SELECT id FROM programs 
    WHERE 1 AND id IN (
        SELECT id FROM programs 
        WHERE submitter_id=32 AND id in (
            SELECT id FROM programs 
            WHERE feed_id=2478 AND id in (
                SELECT program_id FROM playlist_program_map 
                WHERE playlist_id=181)))) 
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0)

UNION (

SELECT object_id, programs.created AS created, 
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
FROM descriptions_programs USE INDEX (text) 
LEFT JOIN programs ON programs.id=object_id 
WHERE object_id IN (
    SELECT id FROM programs 
    WHERE 1 AND id IN (
        SELECT id FROM programs 
        WHERE submitter_id=32 AND id in (
            SELECT id FROM programs 
            WHERE feed_id=2478 AND id in (
                SELECT program_id FROM playlist_program_map 
                WHERE playlist_id=181)))) 
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0 AND current=1 ) 

UNION (

SELECT object_id, programs.created AS created, 
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
FROM titles_programs USE INDEX (text) 
LEFT JOIN programs ON programs.id=object_id 
WHERE object_id IN (
    SELECT id FROM programs 
    WHERE 1 AND id IN (
        SELECT id FROM programs 
        WHERE submitter_id=32 AND id in (
            SELECT id FROM programs 
            WHERE feed_id=2478 AND id in (
                SELECT program_id FROM playlist_program_map 
                WHERE playlist_id=181)))) 
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0 AND current=1;

推荐答案

看看EXPLAIN EXTENDED怎么说.

如果显示DEPENDENT SUBQUERY或UNCACHEABLE SUBQUERY，则每次使用时都会重新评估.

If it says DEPENDENT SUBQUERY or UNCACHEABLE SUBQUERY, then it will be reevaluated each time it's used.

如果子查询使用会话变量或者是相关子查询，则会发生这种情况.

This happens if the subquery uses session variables or is a correlated subquery.

如果没有，它很可能会被缓存.

If it doesn't, it most probably will be cached.

如果您的情况子查询不会被缓存，则会在每个UNION设置的集合中对其进行重新评估.

If your case the subquery will not be cached, it will be reevaluated in each UNION'ed set.

但是，子查询似乎太复杂了.您为什么不只使用:

You subquery, though, seems to be too complicated. Why don't you just use:

SELECT id
FROM   playlist_program_map ppm, programs p
WHERE  ppm.playlist_id = 181
       AND p.id = ppm.program_id
       AND submitter_id = 32
       AND feed_id = 2478

如果您在playlist_program_map (playlist_id)上有索引，则此查询应该像超级按钮一样工作.

If you have an index on playlist_program_map (playlist_id), this query should work like a charm.

您能再告诉我两件事吗?

Could you please tell me two more things:

1. playlist_program_map中有多少行，其中有多少DISTINCT playlist_id个值?
   • programs中有多少行，对有多少DISTINCT submitter_id, feed_id对?

1. How many rows are there in playlist_program_map and how many DISTINCT playlist_id values are there?
   • How many rows are there in programs and how many DISTINCT submitter_id, feed_id pairs are there?

根据您的评论，我可以得出结论，每个playlist平均有 10 programs，每个(submitter, feed)对有 200 programs.这意味着您在playlist_program_map上的索引比在(submitter, feed)上的索引更具选择性，并且playlist_program_map必须在连接中处于领先地位.

From your comment I can conclude that there are 10 programs per playlist in average, and 200 programs per (submitter, feed) pair. This means your index on playlist_program_map is more selective than the one on (submitter, feed), and playlist_program_map must be leading in the join.

鉴于您需要加入 2,000,000 个中的 10 个程序，因此您对全文索引的选择似乎也不是那么有选择性.

The fulltext index in your case also doesn't seem to be very selective, given that you need to join 10 programs out of 2,000,000.

您最好尝试以下操作:

SELECT object_id, programs.created AS created
FROM   playlist_program_map ppm, programs p, comments_programs cp
WHERE  ppm.playlist_id = 181
       AND p.id = ppm.program_id
       AND p.submitter_id = 32
       AND p.feed_id = 2478
       AND cp.object_id = p.id
       AND cp.text REGEXP 'excellent'

，然后对所有三个表重复此操作.

, and repeat this for all three tables.

这篇关于在MySQL中缓存/重新使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！