对于那些不熟悉WP的DBA来说,它有一个面向列的结构(就像许多为大多数用例设计的开源结构一样),随着行数的增加,它的性能呈指数级下降。
我知道许多OpenSource CMS是为了覆盖大多数不执行的用例而设计的。这就是他们使用面向列的数据库设计的原因。
我们用ElasticSearch替换了许多查询,但Mysql仍然是我们的主要数据存储,我想为了数据一致性,仍然需要一些SQL查询。
如果我们转移到我们自己的Mysql表结构,我们将失去丰富和广泛使用的WPneneneba API的所有好处。(主要是WP_query())
我们做得不正确是不是很明显,比如简单的查询优化或相对的简单DBA任务,这些任务可以帮助我们达到2000万行,而不是数十亿行。
以下是一些慢速查询的示例。
EXPLAIN SELECT COUNT(*) FROM wp_posts JOIN (SELECT `post_id` FROM wp_postmeta WHERE `meta_key` = 'et_payment_package' AND `meta_value` IN (246)) as metas ON `ID` = `post_id` WHERE `post_author` = 25492 AND `post_status` = 'publish';
+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 110310 | |
| 1 | PRIMARY | wp_posts | eq_ref | PRIMARY,post_author | PRIMARY | 8 | metas.post_id | 1 | Using where |
| 2 | DERIVED | wp_postmeta | ref | meta_key,meta_value | meta_key | 203 | | 217442 | Using where |
+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
3 rows in set (5.27 sec)
另一个查询:
Query_time: 23.991255 Lock_time: 0.000174 Rows_sent: 6 Rows_examined: 402008
SELECT wp_users.ID,wp_users.user_login,wp_users.display_name FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( ( wp_usermeta.meta_key = 'wp_user_level' AND CAST(wp_usermeta.meta_value AS CHAR) != '0' )) ORDER BY display_name ASC;
+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+
| 1 | SIMPLE | wp_users | ALL | PRIMARY | NULL | NULL | NULL | 127267 | Using filesort |
| 1 | SIMPLE | wp_usermeta | ref | user_id,meta_key | meta_key | 111 | const,tdm_wp_cl.wp_users.ID | 1 | Using where |
+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+
这个代码由@Strawberry提供,运行速度非常快。有人知道这是否可以使用标准WP_query API完成吗?
EXPLAIN SELECT COUNT(*) FROM wp_posts p JOIN wp_postmeta m ON p.ID = m.post_id WHERE p.post_author = 25492 AND p.post_status = 'publish' And m.meta_key = 'et_payment_package' AND m.meta_value IN (246);
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
| 1 | SIMPLE | p | ref | PRIMARY,post_author | post_author | 8 | const | 12 | Using where |
| 1 | SIMPLE | m | ref | meta_key,post_id,meta_value | post_id | 8 | tdm_wp_cl.p.ID | 3 | Using where |
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
2 rows in set (0.01 sec)
存在优化。例如,在第一个查询中有一个不必要的子查询:
SELECT COUNT(*)
FROM wp_posts p
JOIN wp_postmeta m
ON p.ID = m.post_id
WHERE p.post_author = 25492
AND p.post_status = 'publish'
And m.meta_key = 'et_payment_package'
AND m.meta_value IN (246)
同样,我怀疑CAST函数禁止使用索引
SELECT u.ID
, u.user_login
, u.display_name
FROM wp_users u
JOIN wp_usermeta m
ON u.ID = m.user_id
WHERE m.meta_key = 'wp_user_level'
AND m.meta_value != 0
ORDER
BY display_name ASC;