WordPress MySQL查询优化BuddyPress成员类型

我正在制作一个已经拥有超过1000万用户的自定义应用程序。

我必须创建一个自定义查询，从特定的成员类型中提取用户。

问题是，如果我只想看到20个用户并进行分页，则需要大量时间从数据库中获取数据，并且由于查询非常繁重，通常会出现严重错误。

如果我使用BP_User_Query(Buddypress类)，也会发生同样的情况，因为我的代码是基于它的。

最大的问题是Buddypressmember_type已经被记录在WP术语中，并且通过一个复杂的关系到达。

这是我的查询:

SELECT 
wp_users.*,
t_ex.name AS member_type,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='first_name' AND wp_usermeta.user_id=wp_users.ID ) AS first_name,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='last_name' AND wp_usermeta.user_id=wp_users.ID ) AS last_name,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='nickname' AND wp_usermeta.user_id=wp_users.ID ) AS nickname,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='description' AND wp_usermeta.user_id=wp_users.ID ) AS description,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='last_update' AND wp_usermeta.user_id=wp_users.ID ) AS last_update,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='wp_capabilities' AND wp_usermeta.user_id=wp_users.ID ) AS caps,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='rich_editing' AND wp_usermeta.user_id=wp_users.ID ) AS rich_editing
FROM
wp_users
LEFT JOIN wp_term_relationships tr_ex ON tr_ex.object_id = wp_users.ID
LEFT JOIN wp_term_taxonomy tt_ex ON tt_ex.term_taxonomy_id = tr_ex.term_taxonomy_id
LEFT JOIN wp_terms t_ex ON t_ex.term_id = tt_ex.term_id
WHERE
1=1
AND
tt_ex.taxonomy = 'bp_member_type'
AND
t_ex.name = 'platformuser'
GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC
LIMIT 0, 20

有没有更好的解决方案，我可以得到想要的结果，而不需要这么困难的数据库。

我要查找的参数位于t_ex.name，在我的情况下是'platformuser'

我认为问题在于分组和顺序:

...
GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC
...

但不知道为什么。我也需要那个

一些可以使用的东西

首先总之，1000万用户对于WordPress来说是一个非常大的数字。你已经知道了。

第二，请考虑重写您的查询，以执行所谓的"延迟连接"。您有臭名昭著的性能反模式SELECT many_long_rows ... ORDER BY something LIMIT small_number。这迫使MySQL对整个混乱的数据进行排序，只丢弃其中的一小部分。

建议重写:从一个子查询开始获取您感兴趣的wp_users.ID值。从您的示例中，子查询如下:

SELECT wp_users.ID
FROM wp_users
LEFT JOIN wp_term_relationships tr_ex ON tr_ex.object_id = wp_users.ID
LEFT JOIN wp_term_taxonomy tt_ex ON tt_ex.term_taxonomy_id = tr_ex.term_taxonomy_id
LEFT JOIN wp_terms t_ex ON t_ex.term_id = tt_ex.term_id
WHERE 1=1
AND tt_ex.taxonomy = 'bp_member_type'
AND t_ex.name = 'platformuser'
GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC
LIMIT 0, 20

这需要对更少的数据进行排序…只有ID值。所以在MySQL中它应该更快，占用更少的内存。

然后在主查询中使用该子查询来处理您关心的20个用户。

SELECT 
wp_users.*,
'platformuser' AS member_type,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='first_name' AND wp_usermeta.user_id=wp_users.ID ) AS first_name,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='last_name' AND wp_usermeta.user_id=wp_users.ID ) AS last_name,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='nickname' AND wp_usermeta.user_id=wp_users.ID ) AS nickname,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='description' AND wp_usermeta.user_id=wp_users.ID ) AS description,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='last_update' AND wp_usermeta.user_id=wp_users.ID ) AS last_update,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='wp_capabilities' AND wp_usermeta.user_id=wp_users.ID ) AS caps,
( SELECT meta_value FROM wp_usermeta WHERE wp_usermeta.meta_key='rich_editing' AND wp_usermeta.user_id=wp_users.ID ) AS rich_editing
FROM
wp_users
WHERE wp_users.ID IN (
SELECT wp_users.ID
FROM wp_users
LEFT JOIN wp_term_relationships tr_ex ON tr_ex.object_id = wp_users.ID
LEFT JOIN wp_term_taxonomy tt_ex ON tt_ex.term_taxonomy_id = tr_ex.term_taxonomy_id
LEFT JOIN wp_terms t_ex ON t_ex.term_id = tt_ex.term_id
WHERE 1=1
AND tt_ex.taxonomy = 'bp_member_type'
AND t_ex.name = 'platformuser'
GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC
LIMIT 0, 20
)     
GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC
LIMIT 0, 20

查找20个用户的元数据不会花很长时间。

3WordPress的wp_users表，由https://ma.tt和company定义，在display_name列上没有索引。因此，MySQL避免巨大排序的索引技巧不起作用。你可以修复这个

使用ORDER BY wp_users.user_nicename代替display_name，或者

在display_name列上创建如下索引。

ALTER TABLE wp_users ADD KEY display_name (display_name);

第四如果您运行的是旧版本的MySQL(8之前)或MariaDB(10.3之前)，请升级它。并将表转换为使用InnoDB和DYNAMIC行格式。你的1000万用户正在挑战极限。为什么要使用过时的软件呢?

(如果您执行了上述操作，那么就不那么重要了)wp_usermeta上的索引对于您所执行的查找类型并不是最优的。这个MySQL语句序列将重新索引表，使查找速度更快。这可能需要一些时间。

ALTER TABLE wp_usermeta ADD UNIQUE KEY umeta_id (umeta_id);
ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (user_id, meta_key, umeta_id);
ALTER TABLE wp_usermeta DROP KEY user_id;
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key, user_id);

@RickJames和我已经发布了一个(免费的开源)WordPress插件来处理InnoDB转换和驯鹿索引，或者你当然可以自己做。您可以将它与WP-CLI一起使用，以避免超时。

我认为下一个版本的插件也应该添加display_name索引到wp_users，以涵盖您的情况。(我听说其他人也有类似的问题。)

OJones说的话，加上

避免排序:

GROUP BY wp_users.ID
ORDER BY wp_users.display_name ASC

——比;

GROUP BY wp_users.display_name ,    wp_users.ID
ORDER BY wp_users.display_name ASC, wp_users.ID ASC

(通过使这两行本质上相等，可以避免额外的排序)

相关内容

最新更新

热门标签：