代码之家 › 专栏 › 技术社区 › Moon

为什么极限0,1比极限0,17慢

performance sql mysql

Moon · 技术社区 · 4 年前

我正试图分析为什么下面的查询速度较慢 LIMIT 0,1 比 LIMIT 0,100

我添加了 SQL_NO_CACHE 用于测试目的。

查询

 SELECT 
  SQL_NO_CACHE  SQL_CALC_FOUND_ROWS wp_posts.*, 
  low_stock_amount_meta.meta_value AS low_stock_amount
FROM 
  wp_posts 
  LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup ON wp_posts.ID = wc_product_meta_lookup.product_id 
  LEFT JOIN wp_postmeta AS low_stock_amount_meta ON wp_posts.ID = low_stock_amount_meta.post_id 
  AND low_stock_amount_meta.meta_key = '_low_stock_amount' 
WHERE 
  1 = 1 
  AND wp_posts.post_type IN ('product', 'product_variation') 
  AND (
    (wp_posts.post_status = 'publish')
  ) 
  AND wc_product_meta_lookup.stock_quantity IS NOT NULL 
  AND wc_product_meta_lookup.stock_status IN('instock', 'outofstock') 
  AND (
    (
      low_stock_amount_meta.meta_value > '' 
      AND wc_product_meta_lookup.stock_quantity <= CAST(
        low_stock_amount_meta.meta_value AS SIGNED
      )
    ) 
    OR (
      (
        low_stock_amount_meta.meta_value IS NULL 
        OR low_stock_amount_meta.meta_value <= ''
      ) 
      AND wc_product_meta_lookup.stock_quantity <= 2
    )
  ) 

ORDER BY 
  wp_posts.ID DESC 
LIMIT 
  0, 1

解释显示完全相同的输出

1   SIMPLE  wp_posts    index   PRIMARY,type_status_date    PRIMARY 8   NULL    27071   Using where
1   SIMPLE  low_stock_amount_meta   ref post_id,meta_key    meta_key    767 const   1   Using where
1   SIMPLE  wc_product_meta_lookup  eq_ref  PRIMARY,stock_status,stock_quantity,product_id  PRIMARY 8   woocommerce-admin.wp_posts.ID   1   Using where

平均查询时间为350ms 极限0,1

平均查询时间为7ms 极限0100

从开始,查询性能变得更快 LIMIT 0,17

我在order by子句中添加了另一列,如下所示 question ,但这会触发 Using filesort 在解释输出中

Order by wp_posts.post_date, wp_posts.ID desc

1   SIMPLE  wp_posts    ALL PRIMARY,type_status_date    NULL    NULL    NULL    27071   Using where; Using filesort
1   SIMPLE  low_stock_amount_meta   ref post_id,meta_key    meta_key    767 const   1   Using where
1   SIMPLE  wc_product_meta_lookup  eq_ref  PRIMARY,stock_status,stock_quantity,product_id  PRIMARY 8   woocommerce-admin.wp_posts.ID   1   Using where

有没有办法在不改变指数的情况下解决这个问题?为什么会发生这种情况?

同样有趣的是,查询时间从 极限0,17 。我不知道为什么17在这里是个神奇的数字。

更新1:我刚刚尝试添加 FORCE INDEX(PRIMARY) 现在 极限0100 具有与相同的性能 极限0,1 smh

0 回复 | 直到 4 年前

Rick James diyism 4 年前

wp_postmeta 索引草率;这会减慢大多数涉及它的查询的速度。

O.Jones和我做了一个 WordPress plugin 以改进postmeta的索引。我们检测到各种各样的东西,比如InnoDB存储引擎的Barracuda版本和其他MySQL奥秘的存在,并做正确的事情。

这个也许加快所有三个平均值。它可能会改变 EXPLAINs .

Rick James diyism 4 年前

正在分析此查询。我承认我不理解从极限1到极限17的性能变化。尽管如此,你商店的顾客(或经理)面临的问题是极限1的速度慢。所以让我们来解决这个问题。

这个 question you linked 用于postgreSQL,而不是MySQL。postgreSQL有一种更复杂的方法来处理ORDERBY。。。LIMIT 1比MySQL要多。而且,解决这个问题的方法是为所需的查找添加适当的复合索引。

在我看来,您查询的目的是找到库存不足或缺货的WooCommerce产品 wp_posts.ID

LEFT加入 wp_wc_product_meta_lookup 表应该是,而且是,直截了当的:上面提到的ON条件列是它的主键。该表基本上是WooCommerce对存储在中的stock_quantity等数值的物化视图 wp_postmeta 。中的数值 wp_postmeta 无法建立索引,因为该表将它们存储为文本字符串。是啊我知道。

LEFT JOIN之间 wp_posts 和 wp_postmeta 遵循非常常见的ON条件模式 ON posts.ID = meta.post_id AND meta.meta_key = 'constant' 。这种ON条件因WordPress的标准索引支持不力而臭名昭著。或多或少,里克和我的全部目的 Index WP MySQL For Speed plugin 是在中提供良好的复合指数 wp_postmeta 解决那个问题。

为什么呢 This is the DDL 它运行以添加索引。为此,最重要的一行是:(还有更多内容,请阅读链接文章。)

ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

这两个索引支持查询中的ON条件模式。我非常确信,将这些键添加到postmeta将使您的查询更可预测,性能更快。

如果 ORDER BY post.ID DESC 是一个非常常见的用例,可以为此添加索引。

您可以尝试重构查询(如果您可以控制其源),以推迟从 wp_posts 桌子这样地。

SELECT wp_posts.*, postid.low_stock_amount
 FROM (
   wp_posts.ID, low_stock_amount_meta.meta_value AS low_stock_amount
FROM 
  wp_posts 
  LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup ON wp_posts.ID = wc_product_meta_lookup.product_id 
  LEFT JOIN wp_postmeta AS low_stock_amount_meta ON wp_posts.ID = low_stock_amount_meta.post_id 
  AND low_stock_amount_meta.meta_key = '_low_stock_amount' 
WHERE 
  1 = 1 
  AND wp_posts.post_type IN ('product', 'product_variation') 
  AND (
    (wp_posts.post_status = 'publish')
  ) 
  AND wc_product_meta_lookup.stock_quantity IS NOT NULL 
  AND wc_product_meta_lookup.stock_status IN('instock', 'outofstock') 
  AND (
    (
      low_stock_amount_meta.meta_value > '' 
      AND wc_product_meta_lookup.stock_quantity <= CAST(
        low_stock_amount_meta.meta_value AS SIGNED
      )
    ) 
    OR (
      (
        low_stock_amount_meta.meta_value IS NULL 
        OR low_stock_amount_meta.meta_value <= ''
      ) 
      AND wc_product_meta_lookup.stock_quantity <= 2
    )
  ) 

ORDER BY 
  wp_posts.ID DESC 
LIMIT 
  0, 1
) postid
LEFT JOIN wp_posts ON wp_posts.ID = postid.ID

此重构使您的复杂查询仅排序 wp_posts。身份证件 值,然后在获得适当的值后检索posts数据。很多WordPress核心代码都做类似的事情:在一个查询中检索帖子ID值的列表,然后在第二个查询中获取帖子数据。

顺便说一句,MySQL 8忽略了 SQL_NO_CACHE .