大家知道wp_postmeta表应该是WordPress网站中数据库中最大的表，如果不是，就有问题。WordPress数据库读取 wp_actionscheduler_actions 和 wp_actionscheduler_logs 表要花大量的时间，特别是一些大型的WordPress网站，文章超过十几万甚至百万的，加载速度都很慢很卡了。这里搬主题就分享一下为MySQL表添加高性能keys，加快WordPress运行速度的教程。WordPress数据库表分析首次安装 WordPress 时，会在数据库中创建表，例如 wp_postmeta 表：CREATE TABLE wp_postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT '0',
meta_key varchar(255) DEFAULT NULL,
meta_value longtext,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;问题出在哪里？AUTO_INCREMENT 会降低大多数查询的速度，因为必须在二级索引中查找 auto_inc ID，然后再在数据中查找所需的实际 ID。BIGINT 和 AUTO_INCREMENT 多次一举meta_key或meta_value永远不会为空解决方案：CREATE TABLE wp_postmeta (
post_id BIGINT UNSIGNED NOT NULL,
meta_key VARCHAR(255) NOT NULL,
meta_value LONGTEXT NOT NULL,
PRIMARY KEY(post_id, meta_key),
INDEX(meta_key)
) ENGINE=InnoDB;在现有表上，我们可以添加与 WordPress 最常见查询相匹配的高性能键：wp_postmetaALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);这将创建新的高性能Keys：表标准 WP keys新keyswp_postmetameta_id: primary keypost_idmeta_keypost_id, meta_key, meta_id: primary keymeta_id: unique keymeta_key, post_id其他主要表也可以这样做：wp_postsALTER TABLE wp_posts DROP KEY type_status_date;
ALTER TABLE wp_posts ADD KEY type_status_date (post_type, post_status, post_date, post_author, ID);
ALTER TABLE wp_posts DROP KEY post_author;
ALTER TABLE wp_posts ADD KEY post_author (post_author, post_type, post_status, post_date, ID);表标准 WP keys新keyswp_postspost_type, post_status, post_date,IDpost_authorpost_type,post_status,post_date,post_author,IDpost_author,post_type,post_status,post_date,IDwp_optionsALTER TABLE wp_options ADD UNIQUE KEY option_id (option_id);
ALTER TABLE wp_options DROP PRIMARY KEY;
ALTER TABLE wp_options ADD PRIMARY KEY (autoload, option_id);
ALTER TABLE wp_options DROP KEY autoload;表标准 WP keys新keyswp_optionsoption_id: primary keyoption_name: unique keyautoloadautoload, option_id: primary keyoption_name: unique_keyoption_id: unique keywp_usermetaALTER 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);表标准 WP keys新keyswp_usermetaumeta_id: primary keyuser_idmeta_keyuser_id, meta_key, umeta_id: primary keyumeta_id: unique keymeta_key, user_idwp_termmetaALTER TABLE wp_termmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_termmeta DROP PRIMARY KEY;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (term_id, meta_key, meta_id);
ALTER TABLE wp_termmeta DROP KEY term_id;
ALTER TABLE wp_termmeta DROP KEY meta_key;
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key, term_id);表标准 WP keys新keyswp_termmetameta_id: primary keyterm_idmeta_keyterm_id, meta_key, meta_id: primary keymeta_id: unique keymeta_key, term_id这里提到的模式更改在 Index WP MySQL For Speed WordPress 插件中自动进行。插件免费下载地址：https://wordpress.org/plugins/index-wp-mysql-for-speed/当然，本站也分享了汉化中文版的下载，方便国人使用。【Index WP MySQL For Speed汉化中文版|WordPress数据库优化索引添加高性能Keys插件】
THE END
