我有一个拥有170.000用户的大型Wordpress网站，每天都有很多页面浏览量。我只是根据一些评论调整了所有MySQL索引，但实际上在我的慢日志中，SELECT distinct wp_usermeta.meta_key FROM wp_usermeta；大约需要3秒。服务器硬件为：专用服务器，AMD Epyc 64核，128Gb DDR4，2x480 NVMe SSD。
DB服务器为MariaDB最新版本，配置为(仅innoDB表)：
innodb_buffer_pool_size = 64G
innodb_log_file_size = 16G
innodb_buffer_pool_instances = 16
innodb_io_capacity = 5000
max_binlog_size = 200M
max_connections = 250
wait_timeout = 28700
interactive_timeout = 28700
join_buffer_size = 128M
expire_logs_days = 3
skip-host-cache
skip-name-resolve
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
tmp_table_size = 256M
max_heap_table_size = 256M
table_definition_cache = 500
sort_buffer_size = 24M
key_buffer_size = 32M
performance_schema = on
也许有人有一些建议
在与每个用户关联的49个"值"中，有多少用于WHERE或ORDER BY？我怀疑只有少数。
以下是一种解决WP滥用"；实体属性值"；设计模式。
比方说，a、b、c对过滤和/或排序很有用。其他46个值被简单地保存以供以后显示。每个用户在usermeta中有4行，而不是49行。3行将用于a、b、c；其余部分为其余部分的JSON字符串。
然后让应用程序了解JSON并相应地编写代码。
此更改将需要重建wp_usermeta。每个用户46行将被聚集在一起，并重新排列为具有适度大的JSON字符串的单个元行(在meta_value中)。这可能不会使桌子缩小多少，但会使它使用起来更快。
为my.ini[mysqld]部分考虑的启用DEMAND查询缓存利用率的建议。
query_cache_min_res_unit=512 # from 4096 to enable higher density of results
query_cache_size=50M # from 1M to increase capacity
query_cache_limit=6M # from 1M target result for identified query is above 2M
query_cache_type=2 # from OFF to support DEMAND (SELECT SQL_CACHE ...)
net_buffer_length=96K # from 16K to reduce packet in/out count
在做出任何改变之前，我们可能应该在今天晚些时候谈谈SKYPE。几个小时后(3-4)，我会办理登机手续。
Analysis of GLOBAL STATUS and VARIABLES:
观察结果：
- 版本：10.6.5-MariaDB-1:10.6.5+maria ~牛眼日志
- 128 GB RAM
- 正常运行时间=1d 02:48:55
- 384 QPS
更重要的问题：
我看不到任何对解决您遇到的问题至关重要的项目
详细信息和其他观察结果：
( innodb_lru_scan_depth ) = 1,536--"；InnoDB：page_cleaner:1000ms预期循环占用"可以通过降低lru_scan_depth 来修复
( innodb_io_capacity_max ) = 10,000——紧急冲洗时，使用这么多IOP。--阅读可能缓慢或尖锐。
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 2,787,201 / 4145152 = 67.2%--缓冲池当前未使用的百分比--innodb_buffer_pool_size(现在是68719476736)是否大于所需大小？
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 22,248,669,184 / 65536M = 32.4%—数据占用缓冲池的百分比--一个小百分比可能表示buffer_pool过大。
( Innodb_log_writes ) = 5,298,275 / 96535 = 55 /sec
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 96,535 / 60 * 16384M / 6560327680 = 4,213——InnoDB日志循环之间的分钟数从5.6.8开始，InnoDB_log_file_size可以动态更改；我不知道MariaDB。请务必更改my.cnf。--(两次轮换之间60分钟的建议有点武断。)调整innodb_log_file_size(现在是17179869184)。(无法在AWS中更改。)
( Innodb_row_lock_waits ) = 83,931 / 96535 = 0.87 /sec—获取行锁时延迟的频率。--可能是由可以优化的复杂查询引起的。
( Innodb_row_lock_waits/Innodb_rows_inserted ) = 83,931/1560067 = 5.4%——必须等待一行的频率。
( innodb_flush_neighbors ) = 1——将块写入磁盘时的一个小优化。--对SSD驱动器使用0；1用于HDD。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF——是否记录所有死锁。--如果您受到死锁的困扰，请打开它。注意：如果您有很多死锁，这可能会向磁盘写入很多内容。
( join_buffer_size * Max_used_connections ) = (128M * 127) / 131072M = 12.4%-(一个考虑join_buffer_size大小的度量。)--join_buffer_size(现在是134217728)可能应该缩小以避免RAM用完。
( (Com_show_create_table + Com_show_fields) / Questions ) = (66 + 1370563) / 37103211 = 3.7%——Naughty框架——花费大量精力重新发现模式。--向第三方供应商投诉。
( local_infile ) = local_infile = ON--local_infile(现在为ON)=ON是潜在的安全问题
CCD_ 18——创建"；temp"；表作为复杂SELECT的一部分。
CCD_ 19——创建磁盘的频率"；temp"；表作为复杂SELECT的一部分--增加tmp_table_ size(现在为268435456)和max_heap_table_。当使用MEMORY而不是MyISAM时，请检查临时表的规则。也许轻微的架构或查询更改可以避免MyISAM。更好的索引和重新表述查询更有可能有所帮助。
( Created_tmp_disk_tables / Questions ) = 1,751,146 / 37103211 = 4.7%—磁盘tmp表上需要的查询数。--更好的索引/无斑点等
( Created_tmp_disk_tables / Created_tmp_tables ) = 1,751,146 / 2088713 = 83.8%—溢出到磁盘的临时表的百分比--可能会增加tmp_table_size(现在为268435456)和max_heap_table_size(目前为268435456])；提高指标；避免斑点等。
( Handler_read_rnd_next ) = 104,164,660,719 / 96535 = 1079035 /sec—如果有大量表扫描，则为高--密钥可能不足
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (1561842 + 4652536 + 13886 + 42) / 352 = 17,694——每次提交的语句数(假设所有InnoDB)--高：长期交易使各种事情变得紧张。
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (1561842 + 13886 + 0 + 42 + 4652536 + 794) / 96535 = 65 /sec—写入/秒--50次写入/秒+日志刷新可能会使HDD驱动器的I/O写入容量最大化
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 2208251 - 1415 ) / ( 2208251 + 1415 ) = 99.9%-你准备好的发言结束了吗？--添加关闭。
( Com_stmt_prepare - Com_stmt_close ) = 2,208,251 - 1415 = 2.21e+6——有多少准备好的语句尚未关闭。--CLOSE准备的报表
( Com_stmt_close / Com_stmt_prepare ) = 1,415 / 2208251 = 0.06%——Prepared语句应为Closed。--检查是否所有Prepared语句都是"；关闭"；。
( binlog_format ) = binlog_format = MIXED—语句/行/混合。--5.7(10.3)优选ROW
( Syncs ) = 5,727,396 / 96535 = 59 /sec—同步到binlog的磁盘。
( Com_change_db ) = 1,168,504 / 96535 = 12 /sec——可能来自USE语句。--考虑与DB连接，使用DB.tbl语法，消除虚假的USE语句等。
( Connections ) = 3,377,949 / 96535 = 35 /sec—连接--增加wait_timeout(现在为28700)；使用池？
( thread_cache_size / Max_used_connections ) = 250 / 127 = 196.9%--线程缓存大于可能的连接数没有任何好处。浪费空间是缺点。
( thread_pool_size ) = 64—"线程组"的数目。限制一次可以执行的踏板数量。可能不应该比CPU的数量大多少。--不要设置太高的CPU核心数量。
查询缓存已关闭一半。您应该将query_cache_type设置为OFF，将query_cachi_size设置为0。QC代码中有一个"错误"，除非你同时关闭这两个设置，否则会打开一些代码。
VSClasses.inc.256 eval('((1048576-1031304)/0)/4096')错误expr=[[((query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache)/query_cache_min_res_unit]]VSClasses.inc.256 eval错误('(1048576-1031304)/0/16384')expr=[[(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache/query_alloc_block_size]]VSClasses.inc.256 eval("0/0")错误expr=[[Innodb_dbwr_pages_writen/Innodb__pages_witten]]VSClasses.inc.256 eval错误("0/(0+0+0)")expr=[[Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached)]]VSClasses.inc.256 eval("0/0")错误expr=[[Qcache_lowmem_prunes/Qcache_inserts]]
异常小：
Innodb_adaptive_hash_non_hash_searches = 0
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 6 /HR
Innodb_data_written = 0
Innodb_dblwr_pages_written = 0
Innodb_master_thread_active_loops = 13
Innodb_mem_adaptive_hash = 0
Innodb_pages_written = 0
Memory_used = 0.04%
Memory_used_initial = 15.7MB
异常大：
Aria_pagecache_reads = 18 /sec
Aria_pagecache_write_requests = 1180 /sec
Com_show_fields = 14 /sec
Com_stmt_prepare = 23 /sec
Handler_discover = 3 /HR
Handler_read_next = 1805396 /sec
Handler_read_next / Handler_read_key = 121
Innodb_buffer_pool_pages_dirty = 77,929
Innodb_buffer_pool_pages_free = 2.79e+6
Innodb_buffer_pool_pages_total = 4.15e+6
Innodb_checkpoint_age = 2.3e+9
Innodb_log_writes / Innodb_log_write_requests = 6636.2%
Innodb_os_log_fsyncs = 55 /sec
Innodb_rows_read = 2894484 /sec
Open_streams = 4
Opened_views = 0.058 /sec
Performance_schema_file_instances_lost = 6
Rows_read = 2887256 /sec
Select_full_range_join = 0.4 /sec
Select_full_range_join / Com_select = 0.18%
Slaves_connected = 0.037 /HR
Threads_cached = 113
performance_schema_max_statement_classes = 222
异常字符串：
Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
character_set_system = utf8mb3
disconnect_on_expired_password = OFF
innodb_fast_shutdown = 1
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
old_mode = UTF8_IS_UTF8MB3
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
sql_slave_skip_counter = 0
您的服务器配置很好。如果有什么不同的话，那就是它为你的网站做了过度的准备。
如果我正确理解您的评论，那么您已经多次出现这种令人不快的SELECT distinct wp_usermeta.meta_key FROM wp_usermeta查询。这个查询似乎生成了一个172K行的结果集。然而，您说每个用户在wp_usermeta中都有完全合理的49行数。
因此，正如@RickJames所指出的，看起来每个用户都以某种方式获得了自己独特的wp_usermeta.meta_key值。WordPress核心不会这样做，也永远不会那样做。这些wp_whatevermeta表的目的是具有有限数量的密钥。此外，经常重新运行该特定查询是非常低效的。除了一些全局用户列表之外，该查询还有什么用途？因此一个插件肯定与此有关。如果可以获得查询监视器(https://wordpress.org/plugins/query-monitor/)插件工作时，它会告诉你是什么软件生成了有问题的查询。
没有WHERE子句的查询没有什么神奇之处。像COUNT(*)和DISTINCT这样的SELECT通常需要扫描整个表或索引来生成结果，因此当表很大时，查询需要很长时间。如果您在wp_usermeta.meta_key上有一个索引，并且它索引的是整列，而不是191前缀，那么有问题的查询应该进行相对便宜的松散索引扫描。但是，它仍然必须进行索引扫描。
然后，试试我和Rick James组合的插件。https://wordpress.org/plugins/index-wp-mysql-for-speed/它制作了有用的索引，还内置了一些诊断工具