主页 / dba / 问题 / 160984
Accepted
Vitaly Karasik DevOps
Asked: 2017-01-14 02:43:26 +0800 CST

MySQL - 为什么特定查询不使用缓存？

  • 772

我的客户在 Ubuntu16.04 上运行 MySQL 5.7.16 作为 Wordpress 站点的后端。大量繁重、糟糕的 SQL 语句。通过启用 MySQL 缓存，我能够显着改善情况，慢查询日志现在几乎是空的。但是 - 某些查询不使用缓存，我不明白为什么。SQL 使用两个非常静态的表，所以它不是缓存失效。知道我该如何调试吗？

TIA，维塔利

1) SHOW STATUS LIKE "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1700     |
| Qcache_free_memory      | 49555584 |
| Qcache_hits             | 17960465 |
| Qcache_inserts          | 6928571  |
| Qcache_lowmem_prunes    | 1850967  |
| Qcache_not_cached       | 924283   |
| Qcache_queries_in_cache | 13303    |
| Qcache_total_blocks     | 28441    |
+-------------------------+----------+

2）SQL示例：

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
  LEFT JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id )  
  LEFT JOIN wp_postmeta AS mt1 
    ON (wp_posts.ID = mt1.post_id 
    AND mt1.meta_key = '_pagefrog_fbia_status' ) 
WHERE 1=1  
  AND (     ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '1' )   
       OR     mt1.post_id IS NULL   
       OR   ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '' ) ) 
  AND wp_posts.post_type = 'post' 
  AND ((wp_posts.post_status = 'publish')) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

附加物

SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |    
+-----------------------------+----------+
mysql query-cache

3 个回答

  1. Best Answer
    • 当任一表被修改时， QC 中的所有条目都将从该表的 QC 中删除（修剪）。
    • 如果查询的结果集太大，则不会进入 QC。（请提供SHOW VARIABLES LIKE 'query_cache*';

    你有这个吗wp_postmeta: PRIMARY KEY(post_ID, meta_key)？如果你可以切换到那个，当不使用 QC 时性能应该会提高。如果你遇到麻烦，让我们看看SHOW CREATE TABLE wp_postmeta；在优化该模式时，某些 WP 版本真的很蹩脚。

    你需要GROUP BY吗？ JOIN...GROUP BY经常遭受使此类查询变慢的爆炸-内爆综合症。可能有解决方法。

    你会“分页”吗？如果是这样，使用OFFSET效率低下。

    摆脱SQL_CALC_FOUND_ROWS将加快查询速度。

    这是一个令人困惑的查询，它试图做什么？

    指数

    PRIMARY KEY (meta_id),         -- useless and slows things down
KEY post_id (post_id),         -- inadequate
KEY meta_key (meta_key(191))   -- prefixing is next to useless

    改成这样

    Change `meta_key` from VARCHAR(255) to VARCHAR(191)
PRIMARY KEY (post_id, meta_key)
no other indexes unless you ever search for meta_key without post_id

    笔记：

    • 您可能没有很长的密钥，因此更改为 191 可能是安全的。您可以运行SELECT MAX(CHAR_LENGTH(meta_key)) FROM ..以查看当前最大值是多少。
    • 如果你不能缩小到 191，那么尝试升级到 5.6.3 / 5.5.14，这允许你增加限制（需要一些努力），或者 5.7.7，它默认允许。
    • “复合”键通常比单列键更好，尤其是在这种情况下。
    • 将主要访问模式作为 PK 可以使其更快。
    • 这些改进可能对特定的扭曲查询没有帮助，但会以其他方式帮助表。

    （多年来我一直在抱怨 WP。我很乐意为他们提供建议。）

    • 1

  2. 感谢 Rick 提出了许多有趣的想法。是的 - “摆脱 SQL_CALC_FOUND_ROWS 将加快查询速度。” 回答我的问题。似乎使用 SQL_CALC_FOUND_ROWS 缓存应该更大以保存中间结果。

    • 1

  3. query_cache_min_res_unit = 512在你的.cfg/.ini将允许更多的结果在 QC 中。您可能有许多小于 4,096 结果大小的结果，这意味着您仍然为最小的查询保留了 4,096，减少了query_cache_size(4096-512)=3584 的容量，而这些容量仍然可用于其他结果。

    • 0

相关问题