Медленная работы выборки при большом количестве постов от несколько 10-ков тысяч

(@xrayboy)

ЗАДАЧА:
Столкнулся с такой проблемой, что при количестве постов 20 000+ выборка занимала по 30 секунд при выборе каждой страницы. То есть база данных постоянно выбирает все записи, чтобы выбрать из всех какое-то количество (штук на страницу). Компьютер слабоват для глобальных масштабов, но в планах довести до 7 000 000+ постов. Обычно место на жёстких дисках не проблема.
ВОПРОС: Если возможность оптимизировать выборки?
Моё РЕШЕНИЕ (действующий в проекте MySQL): (возможно стоит добавить это решение в релиз WP)
Поэтому решение выбрал такое, что при поступлении выборки через функцию get_posts () (wp-includes/class-wp-query.php) нужно «перехватить» стандартный запрос, модифицировать функцию запроса и выдать уже построенный «список».
Для хранения «списков» сортинга в базе данных были сотворены таблицы:
CREATE TABLEwp_сорт` (
номер_сорта int(10) unsigned NOT NULL AUTO_INCREMENT,
значение_сорта text NOT NULL,
время_сорта datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’,
период_сорта int(10) unsigned NOT NULL,
мд5_сорта varchar(32) NOT NULL,
записей_сорта int(10) unsigned NOT NULL,
вызов_сорта datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’,
PRIMARY KEY (номер_сорта),
UNIQUE KEY инд_мд5_сорта (мд5_сорта) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;`
CREATE TABLEwp_сортировка` (
номер_поста bigint(20) unsigned NOT NULL,
номер_страница int(10) unsigned NOT NULL DEFAULT ‘0’,
номер_на_странице tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
номер_сорта int(10) unsigned NOT NULL DEFAULT ‘1’,
UNIQUE KEY уник (номер_страница,номер_на_странице,номер_сорта) USING BTREE,
KEY внешка_поста (номер_поста) USING BTREE,
KEY внешка_сорта (номер_сорта),
CONSTRAINT внешка_поста FOREIGN KEY (номер_поста) REFERENCES wp_posts (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT внешка_сорта FOREIGN KEY (номер_сорта) REFERENCES wp_сорт (номер_сорта) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`
Модификация файла wp-includes/class-wp-query.php в функции get_posts ():
где то 2513 строка найти:

if ( $split_the_query ) {
    // First get the IDs and then fill in the objects

и вставить после:

                $bMod = isset( $q[ "posts_per_page" ] ) && $q[ "posts_per_page" ] > 0 && !isset( $q[ 'search_terms' ] );
                if ( $bMod ) {
                    $мЗначение = [
                        "join"           => $join,
                        "where"          => $where,
                        "groupby"        => $groupby,
                        "orderby"        => $orderby,
                        "posts_per_page" => $q[ "posts_per_page" ]
                    ];
                    $сЗначение = serialize ( $мЗначение );
                    $сМд5 = md5 ( $сЗначение );
                    $aR = $wpdb->get_results ( "SELECT <code>номер_сорта</code>, <code>записей_сорта</code> FROM <code>wp_сорт</code> 
                    WHERE 
                    <code>мд5_сорта</code>='{$сМд5}'", ARRAY_A );
                    $sTime = date ( "Y-m-d H:i:s", strtotime ( "now" ) + 14400 );
                    if ( !$aR ) {
                        $stable = "wp_сорт";
                        $sColumName = "номер_сорта";
                        // можно бнз этого, но красивее в базе
                        $iN = $wpdb->get_var ( 'SELECT t1.' . $sColumName . '+1
              FROM ' . $stable . ' AS t1
              LEFT JOIN ' . $stable . ' AS t2
              ON t1.' . $sColumName . '+1 = t2.' . $sColumName . '
              WHERE t2.' . $sColumName . ' IS NULL
              ORDER BY t1.' . $sColumName . '
              LIMIT 1' );
                        $wpdb->insert (
                            "wp_сорт",
                            [
                                "номер_сорта"    => $iN,
                                "значение_сорта" => $сЗначение,
                                "период_сорта"   => 86400, // Обнова раз в сутки если нет запроса сорта
                                "мд5_сорта"      => $сМд5,
                                "вызов_сорта"    => $sTime
                            ],
                            [
                                '%d',
                                '%s',
                                '%d',
                                '%s',
                                '%s'
                            ]
                        );
                        $чНомерСорта = $wpdb->insert_id;
                        $iPerPage = $q[ "posts_per_page" ] - 1;
                        $iQ = $wpdb->query (
                            "SET @iVar = -1;" );
                        $iQ = $wpdb->query (
                            "SET @iVar2 = 0;" );
                        $sQ = "INSERT INTO <code>wp_сортировка</code>
                        SELECT SQL_CALC_FOUND_ROWS @iVar3:=wp_posts.ID,
                            IF(@iVar={$iPerPage},@iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 , @iVar2)) t,
                            IF(@iVar<" . $iPerPage . ",@iVar:=@iVar+1,@iVar:=0) t1,
                            {$чНомерСорта}
                        FROM wp_posts {$join}
                        WHERE 1=1 {$where} {$groupby} {$orderby}
                        ON DUPLICATE KEY UPDATE <code>номер_поста</code>=@iVar3;";
                        $wpdb->query ( $sQ );
                        $q[ "iRows" ] = $iRows = $wpdb->get_var ( "SELECT FOUND_ROWS()" );
                        $wpdb->update ( "wp_сорт", [
                            "записей_сорта" => $iRows, "время_сорта" => $sTime
                        ],
                            [ "номер_сорта" => $чНомерСорта ] );
                    } else {
                        $чНомерСорта = $aR[ 0 ][ "номер_сорта" ];
                        $wpdb->update ( "wp_сорт", [ "вызов_сорта" => $sTime ],
                            [ "номер_сорта" => $чНомерСорта ] );
                        $q[ "iRows" ] = $iRows = $aR [ 0 ][ "записей_сорта" ];
                    }
                    if ( isset ( $q[ "paged" ] ) && $q[ "paged" ] )
                        $iPaged = $q[ "paged" ] - 1;
                    else
                        $iPaged = 0;
                    $this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM 
                    {$wpdb->posts} $join WHERE 
                {$wpdb->posts}.ID IN(SELECT <code>номер_поста</code> FROM <code>wp_сортировка</code> WHERE <code>номер_страница</code>={$iPaged} 
                AND <code>номер_сорта</code>={$чНомерСорта}) GROUP BY {$wpdb->posts}.ID";
                    //echo 0;
                } else {
                    $this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 
                    1=1 $where $groupby $orderby $limits";
                }

Чтобы отдать количество записей, то в функции set_found_posts ( $q, $limits ) изменяем c:
$this->found_posts = $wpdb->get_var ( apply_filters_ref_array ( 'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );
на:

            if ( isset( $q[ "iRows" ] ) )
                $this->found_posts = $q[ "iRows" ];
            else
                $this->found_posts = $wpdb->get_var ( apply_filters_ref_array ( 'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );

в cron нужно добавить разово при инсталляции, например, плагина задание:
wp_schedule_event ( time (), 'wp_wc_updater_cron_interval', 'xray_sorting_event' );
на событие:
add_action ( 'xray_sorting_event', 'xray_sorting_event_func' );
и, соответственно, сама функция:

/**
 * Функция для cron событий
 */
function xray_sorting_event_func () {
    global $wpdb;
    set_time_limit ( 600 );
    echo '<pre>xray_sorting';
    $aR = $wpdb->get_results ( "SELECT * FROM <code>wp_сорт</code> WHERE (NOW()>DATE_ADD(<code>время_сорта</code>,INTERVAL <code>период_сорта</code> 
    SECOND) OR <code>время_сорта</code><<code>вызов_сорта</code>) AND <code>номер_сорта</code>>0", ARRAY_A );
    foreach ( $aR as $aV ) {
        $aЗначение = unserialize ( $aV[ "значение_сорта" ] );
        $iPerPage = $aЗначение[ "posts_per_page" ] - 1;
        $iQ = $wpdb->query (
            "SET @iVar = -1;" );
        $iQ = $wpdb->query (
            "SET @iVar2 = 0;" );
        $sQ = "INSERT LOW_PRIORITY INTO <code>wp_сортировка</code>
        SELECT SQL_CALC_FOUND_ROWS @iVar3:=wp_posts.ID,
            IF(@iVar={$iPerPage},@iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 , @iVar2)) t,
            IF(@iVar<" . $iPerPage . ",@iVar:=@iVar+1,@iVar:=0) t1,
            {$aV["номер_сорта"]}
        FROM wp_posts {$aЗначение["join"]}
        WHERE 1=1 {$aЗначение["where"]} {$aЗначение["groupby"]} {$aЗначение["orderby"]}
        ON DUPLICATE KEY UPDATE <code>номер_поста</code>=@iVar3;";
        $iQ = $wpdb->query ( $sQ );
        if ( !$wpdb->last_error == "" ) {
            echo "\nсорт {$iQ} " . $aV[ "номер_сорта" ] . ": провал: " . htmlspecialchars ( $sQ ) . " " . $wpdb->last_error
                . ";";
        } else {
            echo "\nсорт {$iQ} " . $aV[ "номер_сорта" ] . ": норма;";
        }
        $iRows = $wpdb->get_var ( "SELECT FOUND_ROWS()" );
        echo $wpdb->update ( "wp_сорт", [
            "записей_сорта" => $iRows, "время_сорта" => date ( "Y-m-d H:i:s", strtotime
                ( "now" ) + 14400 )
        ],
            [
                "номер_сорта" => $aV[ "номер_сорта" ]
            ] );
    }
    # Стираем старые записи сортировок 604800 сек - это 7 суток
    $wpdb->query ( "DELETE FROM <code>wp_сорт</code> WHERE <code>вызов_сорта</code><" . date ( "Y-m-d H:i:s", strtotime
            ( "now" ) - 604800 ) . "  AND <code>номер_сорта</code>>0" );
}

Так как CRON WP не работает, то крон пользую Linux`а подобной командой, которую запускает каждую минуту:
/usr/bin/flock -xn /var/lock/cron.php -c 'cd /home/wp/www/;php -q /home/wp/www/wp-cron.php' >/dev/null 2>&1
flock пользую для исключения повторного запуска, без окончания предыдущего.
Чтобы крон работал без границы WP, то нужно коментировать в файле wp-cron.php строки:

//if ( $doing_cron_transient != $doing_wp_cron )
	//return;

Ещё раз повторю, что это рабочая модель. Может требует напильника. Но все ошибки, которые могут быть в коде(запятые, пробелы ещё что) это проблемы копи-паста.

Просмотр 3 ответов — с 1 по 3 (всего 3)

Модератор Yui
(@fierevere)

永子

7 лет, 5 месяцев назад

мало кто работает с сайтами с таким количеством записей
ну и опять же тут форум поддержки, свое предложение лучше изложите на https://core.trac.wordpress.org/ в виде тикета

Flector
(@flector)

7 лет, 5 месяцев назад

memcached для базы данных не пробовали?

Модератор Sergey Biryukov
(@sergeybiryukov)

Live and Learn

7 лет, 5 месяцев назад

То есть база данных постоянно выбирает все записи, чтобы выбрать из всех какое-то количество (штук на страницу).

Стоит убедиться, что это происходит именно в стандартном запросе — без плагинов и с одной из штатных тем оформления.

Любой запрос, если что, можно перед его выполнением изменить с помощью фильтра query или фильтров в WP_Query::get_posts() — возможно, удастся найти решение без правки файлов движка.

Просмотр 3 ответов — с 1 по 3 (всего 3)

Тема «Медленная работы выборки при большом количестве постов от несколько 10-ков тысяч» закрыта для новых ответов.

Медленная работы выборки при большом количестве постов от несколько 10-ков тысяч

Метки

Темы

Самые популярные темы

Темы без ответов

Без вопроса

Решённые

Нерешённые

Все темы