A-A+
WordPress 10w+数据时, 解决SQL_CALC_FOUND_ROWS查询使网站变慢问题
WordPress在查询post列表时,默认会同时把文章数量也查询出来,
使用这种方式的有:get_posts 、query_posts和WP_Query。
get_posts在4.6.1+已经不用SQL_CALC_FOUND_ROWS,但是query_posts和WP_Query还是会用,所以还须优化。
具体语句如下:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' ) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 SELECT FOUND_ROWS()
这在网站数据量小的时候,不会引起什么问题,
但是当post数量到10w+的时候,这个就是一条必现的慢查询,
首页、分类、标签、搜索页面,只要用到这几个函数,就都会使用SQL_CALC_FOUND_ROWS这个方式。
那么,如何解决?
禁用掉SQL_CALC_FOUND_ROWS用法,用一种更加高效的方式,
这里我们用EXPLAIN方式,为什么用EXPLAIN而不是count(*)?
具体代码如下,放在functions.php文件:
if (!function_exists('maizi_set_no_found_rows')) { /** * 设置WP_Query的 'no_found_rows' 属性为true,禁用SQL_CALC_FOUND_ROWS * * @param WP_Query $wp_query WP_Query实例 * @return void */ function maizi_set_no_found_rows(\WP_Query $wp_query) { $wp_query->set('no_found_rows', true); } } add_filter('pre_get_posts', 'maizi_set_no_found_rows', 10, 1); if (!function_exists('maizi_set_found_posts')) { /** * 使用 EXPLAIN 方式重构 */ function maizi_set_found_posts($clauses, \WP_Query $wp_query) { // Don't proceed if it's a singular page. if ($wp_query->is_singular()) { return $clauses; } global $wpdb; $where = isset($clauses['where']) ? $clauses['where'] : ''; $join = isset($clauses['join']) ? $clauses['join'] : ''; $distinct = isset($clauses['distinct']) ? $clauses['distinct'] : ''; $wp_query->found_posts = (int)$wpdb->get_row("EXPLAIN SELECT $distinct * FROM {$wpdb->posts} $join WHERE 1=1 $where")->rows; $posts_per_page = (!empty($wp_query->query_vars['posts_per_page']) ? absint($wp_query->query_vars['posts_per_page']) : absint(get_option('posts_per_page'))); $wp_query->max_num_pages = ceil($wp_query->found_posts / $posts_per_page); return $clauses; } } add_filter('posts_clauses', 'maizi_set_found_posts', 10, 2);
下面是用ChatGPT给添加的带注释的版本:
if (!function_exists('maizi_set_no_found_rows')) { /** * 设置 WP_Query 的 'no_found_rows' 属性为 true,禁用 SQL_CALC_FOUND_ROWS * * @param WP_Query $wp_query WP_Query 实例 * @return void */ function maizi_set_no_found_rows(\WP_Query $wp_query) { $wp_query->set('no_found_rows', true); } } // 确保函数定义后再添加过滤器 add_filter('pre_get_posts', 'maizi_set_no_found_rows', 10, 1); if (!function_exists('maizi_set_found_posts')) { /** * 使用 EXPLAIN 方式重构 found_posts 的计算逻辑 * * @param array $clauses SQL 查询片段 * @param WP_Query $wp_query WP_Query 实例 * @return array 修改后的 SQL 查询片段 */ function maizi_set_found_posts($clauses, \WP_Query $wp_query) { // 如果是单篇文章页面,不进行处理 if ($wp_query->is_singular()) { return $clauses; } global $wpdb; // 获取 SQL 查询片段 $where = isset($clauses['where']) ? $clauses['where'] : ''; $join = isset($clauses['join']) ? $clauses['join'] : ''; $distinct = isset($clauses['distinct']) ? $clauses['distinct'] : ''; // 使用 EXPLAIN 查询获取行数 $wp_query->found_posts = (int)$wpdb->get_row("EXPLAIN SELECT $distinct * FROM {$wpdb->posts} $join WHERE 1=1 $where")->rows; // 计算每页显示的文章数 $posts_per_page = (!empty($wp_query->query_vars['posts_per_page']) ? absint($wp_query->query_vars['posts_per_page']) : absint(get_option('posts_per_page'))); // 计算最大页数 $wp_query->max_num_pages = ceil($wp_query->found_posts / $posts_per_page); return $clauses; } } // 确保函数定义后再添加过滤器 add_filter('posts_clauses', 'maizi_set_found_posts', 10, 2);
我又找到一个第二种实现EXPLAIN的代码,但和上面的略微不同,哪位大神能解惑呢?来自:https://www.banzhuti.com/sql-calc-found-rows-slow-query-optimization.html
//优化数据库慢查询 if ( ! function_exists( 'banzhuti_set_no_found_rows' ) ) : /** * 设置WP_Query的 'no_found_rows' 属性为true,禁用SQL_CALC_FOUND_ROWS * 更多优化教程-搬主题www.banzhuti.com * @param WP_Query $wp_query The WP_Query instance. * @return void */ function banzhuti_set_no_found_rows( \WP_Query $wp_query ) { $wp_query->set( 'no_found_rows', true ); } endif; add_filter( 'pre_get_posts', 'banzhuti_set_no_found_rows', 10, 1 ); if ( ! function_exists( 'banzhuti_set_found_posts' ) ) : /** * Workout the pagination values. * * 为这个wp_query构建和设置分页结果 * */ function banzhuti_set_found_posts( $clauses, \WP_Query $wp_query ) { // Don't proceed if it's a singular page. if ( $wp_query->is_singular() ) { return $clauses; } global $wpdb; $where = isset( $clauses[ 'where' ] ) ? $clauses[ 'where' ] : ''; $join = isset( $clauses[ 'join' ] ) ? $clauses[ 'join' ] : ''; $distinct = isset( $clauses[ 'distinct' ] ) ? $clauses[ 'distinct' ] : ''; // 构建并运行查询。将结果设为'found_posts' // 在我们要运行的主要查询上设置参数. $wp_query->found_posts = $wpdb->get_var( "SELECT $distinct COUNT(*) FROM {$wpdb->posts} $join WHERE 1=1 $where" ); // 计算出每页应该有多少文章 $posts_per_page = ( ! empty( $wp_query->query_vars['posts_per_page'] ) ? absint( $wp_query->query_vars['posts_per_page'] ) : absint( get_option( 'posts_per_page' ) ) ); // 设置max_num_pages(最大页数). $wp_query->max_num_pages = ceil( $wp_query->found_posts / $posts_per_page ); // Return the $clauses so the main query can run. return $clauses; } endif; add_filter( 'posts_clauses', 'banzhuti_set_found_posts', 10, 2 );
如果你要用COUNT语句,就用下面的代码:
<?PHP /** * Plugin Name: Fix WordPress Slow Queries * Description: Fix WordPress Slow Queries * Author: Mahdi Akrami * Version: 1.0.0 */ class FIX_WP_SLOW_QUERY { public static function init () { /** * WP_Query */ add_filter ( 'found_posts_query', [ __CLASS__, 'add_found_rows_query' ], 999, 2 ); add_filter ( 'posts_request_ids', [ __CLASS__, 'remove_found_rows_query' ], 999 ); add_filter ( 'posts_pre_query', function ( $posts, \WP_Query $query ) { $query->request = self::remove_found_rows_query ( $query->request ); return $posts; }, 999, 2 ); add_filter ( 'posts_clauses', function ( $clauses, \WP_Query $wp_query ) { $wp_query->fw_clauses = $clauses; return $clauses; }, 999, 2 ); } public static function remove_found_rows_query ( $sql ) { return str_replace ( ' SQL_CALC_FOUND_ROWS ', '', $sql ); } public static function add_found_rows_query ( $sql, WP_Query $query ) { global $wpdb; $distinct = $query->fw_clauses['distinct'] ?? ''; $join = $query->fw_clauses['join'] ?? ''; $where = $query->fw_clauses['where'] ?? ''; $groupby = $query->fw_clauses['groupby'] ?? ''; $count = 'COUNT (*)'; if ( ! empty ( $groupby ) ) { $count = "COUNT ( distinct $groupby )"; } return " SELECT $distinct $count FROM {$wpdb->posts} $join WHERE 1=1 $where "; } } FIX_WP_SLOW_QUERY::init ();
这样就可以了,记得清除缓存。