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

  • ЗАДАЧА:
    Столкнулся с такой проблемой, что при количестве постов 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)

    ゆい

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

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

    Модератор Sergey Biryukov

    (@sergeybiryukov)

    Live and Learn

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

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

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

Просмотр 3 ответов — с 1 по 3 (всего 3)
  • Тема «Медленная работы выборки при большом количестве постов от несколько 10-ков тысяч» закрыта для новых ответов.