<p>我有一个基于WooCommerce会员的网站，使用了Teams for WooCommerce会员扩展。尽管有庞大的用户群体（> 70,000用户），95%的时间它表现得非常好。我们每天接待约10,000个访问，响应时间在一秒以下。</p> <p>在用户需要完成培训的旺季期间，由于并发未缓存请求的负载，网站可能会崩溃。</p> <p>使用Query Monitor工具，有一个查询引起了注意：0.0375秒（平均值小于0.0050秒）。这个查询检查以下内容：</p> <ol> <li><p>当前用户是否是具有活跃订阅的团队的成员？</p> </li> <li><p>他们是否有权限查看此文章？</p> </li> </ol> <p>以下是查询本身：</p> <pre class="brush:php;toolbar:false;">SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta _teams_pm ON wp_posts.ID = _teams_pm.post_id AND _teams_pm.meta_key = '_member_id' AND _teams_pm.meta_value = 2 LEFT JOIN wp_usermeta _teams_um ON _teams_um.user_id = _teams_pm.meta_value AND _teams_um.meta_key = CONCAT( '_wc_memberships_for_teams_team_', wp_posts.ID, '_role' ) WHERE 1=1 AND ((wp_posts.post_type = 'wc_memberships_team' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private'))) AND ( _teams_um.meta_value IN('manager', 'member') OR wp_posts.post_author = 2 ) ORDER BY wp_posts.post_date DESC</pre> <p>它被以下调用：</p> <pre class="brush:php;toolbar:false;">"WP_Query->get_posts() wp-includes/class-wp-query.php:3111 WP_Query->query() wp-includes/class-wp-query.php:3542 WP_Query->__construct() wp-includes/class-wp-query.php:3653 SkyVergeWMTTeams_Handler->get_teams() wp-content/plugins/woocommerce-memberships-for-teams/src/Teams_Handler.php:446 wc_memberships_for_teams_get_teams() wp-content/plugins/woocommerce-memberships-for-teams/src/Functions/Teams.php:100 ctz_membership_get_user_team_id() wp-content/plugins/core-functionality/temp/wc_teams.php:603"</pre> <p>有人有任何关于如何加快此查询速度的索引的想法吗？我对SQL不够熟悉，不知道最佳索引位置在哪里。</p> <p>我们预计周一会有一个大的峰值，所以我希望在本周末网站比较空闲的时候能有所准备。</p> <p>提前感谢！</p> <p>编辑：explain的输出，前缀和数据库名称已匿名化：</p> <pre class="brush:php;toolbar:false;">+----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+ | 1 | SIMPLE | wp_posts | NULL | range | type_status_date,post_author | type_status_date | 164 | NULL | 5556 | 100.00 | Using index condition; Using filesort | | 1 | SIMPLE | _teams_pm | NULL | ref | PRIMARY,meta_key,meta_value | PRIMARY | 1030 | dbname.wp_posts.ID,const | 1 | 100.00 | Using where | | 1 | SIMPLE | _teams_um | NULL | ref | PRIMARY,meta_key | PRIMARY | 1030 | dbname._teams_pm.meta_value,func | 1 | 100.00 | Using where | +----+-------------+---------------+------------+-------+------------------------------+------------------+---------+----------------------------------------------+------+----------+---------------------------------------+ 3 rows in set, 4 warnings (0.00 sec)</pre> <p><br /></p>

INDEX(post_type, post_status, post_author, ID, post_date)

AND ( _teams_um.meta_value    IN('manager', 'member')
      OR wp_posts.post_author = 2 )