Mysql 使用索引进行查询优化

查询在不同的阶段进行处理.首先处理的子句是FROM子句，然后是WHERE子句，然后是SELECT子句.那些相依子查询意味着，在处理FROM和WHERE子句之后，您将为其中each row个结果运行单独的新子查询.在你的情况下，你做的是那个的四倍.

您通常可以对此进行修改，以将这些查询从SELECT子句移到FROM子句中.

以您已有的一列，即serials列为例，我认为您可能希望将其移到FROM子句中，如下所示

SELECT p.ID                                                                                  AS order_id
     , DATE(p.post_date)                                                                     AS order_date
     , SUBSTR(comment_content, 17)                                                           AS csr
     , SUBSTR(p.post_status, 4)                                                              AS order_status
     , UCASE(CONCAT((SELECT wp_postmeta.meta_value
                     FROM wp_postmeta
                     WHERE meta_key = '_billing_first_name' and wp_postmeta.post_id = p.ID), ' ',
                    (SELECT wp_postmeta.meta_value
                     FROM wp_postmeta
                     WHERE meta_key = '_billing_last_name' and wp_postmeta.post_id = p.ID))) AS customer
     , (SELECT GROUP_CONCAT(DISTINCT order_item_name ORDER BY order_item_name ASC SEPARATOR ', ')
        FROM wp_woocommerce_order_items
        WHERE order_id = p.ID
          AND order_item_type = 'line_item'
        GROUP BY order_id)                                                                   AS products
     , serials_sub.serials
FROM wp_posts AS p
         INNER JOIN wp_comments AS c ON p.ID = c.comment_post_ID
         INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
         LEFT JOIN (
            SELECT p.ID as post_id, GROUP_CONCAT(CONCAT(cs.serial_number, '', cs.serial_feature_code)) AS serials
            FROM wp_custom_serial cs
            JOIN wp_posts AS p ON cs.order_id = p.ID
            WHERE p.post_type = 'shop_order'
             AND comment_content LIKE 'Order placed by%'
            GROUP BY cs.order_id
         ) as serials_sub ON serials_sub.post_id = p.ID
WHERE p.post_type = 'shop_order'
  AND comment_content LIKE 'Order placed by%'
GROUP BY p.ID
ORDER BY SUBSTR(comment_content, 17) ASC, p.post_date DESC;

这里的不同之处在于，不是对每一行执行单独的查询，而是在初始的FROM子句中使用单个子查询.因此，虽然看起来可能更笨拙，但实际上这会给您带来更好的性能.

对其他子查询遵循此模式，我认为将解决您的问题.

如果感兴趣，这里有关于解释的文档.

https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html个

我推荐这本书High Performance MySQL.