Ускорение запросов в базу данных с PDO и итераторами

Когда вы обозреваете множество кода, вы часто удивляетесь, почему что-то было написано так как было. Особенно когда делаются дорогие запросы в базу данных, я все еще вижу вещи, которые могут и должны быть улучшены.

Разработка без фреймворка

Когда работа идет с фреймворком, в основном эти запросы в базу данных оптимизированы для разработчика и сложная логика абстрагируется для улучшения и оптимизации получения и использования данных. Но потом разработчикам нужно создать что-то без фреймворка и это заканчивается использованием базового PHP не оптимальным способом.


$pdo = new \PDO(
    $config['db']['dsn'],
    $config['db']['username'],
    $config['db']['password']
);

$sql = 'SELECT * FROM `gen_contact` ORDER BY `contact_modified` DESC';

$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll(\PDO::FETCH_OBJ);

echo 'Getting the contacts that changed the last 3 months' . PHP_EOL;
foreach ($data as $row) {
    $dt = new \DateTime('2015-04-01 00:00:00');
    if ($dt->format('Y-m-d') . '00:00:00' < $row->contact_modified) {
        echo sprintf(
            '%s (%s)| modified %s',
            $row->contact_name,
            $row->contact_email,
            $row->contact_modified
        ) . PHP_EOL;
    }
}

$pdo = new \PDO(

$config['db']['dsn'],

$config['db']['username'],

$config['db']['password']

);

$sql = 'SELECT * FROM `gen_contact` ORDER BY `contact_modified` DESC';

$stmt = $pdo->prepare($sql);

$stmt->execute();

$data = $stmt->fetchAll(\PDO::FETCH_OBJ);

echo 'Getting the contacts that changed the last 3 months' . PHP_EOL;

foreach ($data as $row) {

$dt = new \DateTime('2015-04-01 00:00:00');

if ($dt->format('Y-m-d') . '00:00:00' < $row->contact_modified) {

echo sprintf(

'%s (%s)| modified %s',

$row->contact_name,

$row->contact_email,

$row->contact_modified

) . PHP_EOL;

}

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

Вышепиведенный код не повторно используем, поэтому когда вам понадобится похожая функциональность, вы упретесь в дублирование существующего кода.
Даже если вы получаете объект используя $stmt->fetchAll(\PDO::FETCH_OBJ); , вы по прежнему сталкиваетесь с проблемой, что вы используете массив объектов, что потребляет слишком много памяти когда выгружается множество данных.
Фильтрация осуществляется в рамках подпрограммы, что так же ознаает, что если вы имеете другие условия фильтрации, вам нужно модифицировать существующую логику, делая ее сложной для поддержки и расширяя функционал.

Итераторы

Большинство современных фреймворков используют Итераторы в их выгрузках данных потому, что они быстрые и повторно используемы. Но так же они предоставляют другие итераторы для фильтрации и модификации полученных результатов. Создание приложения без фреймворка все еще дает вам вариант использовать итераторы, т.к. они часть PHP начиная с версии 5.0.0 Beta 2.

И так давайте предположим, что вы продолжаете использовать PDO для получения ваших данных, мы можем выбирать между двумя вариантами:

Использовать PDOStatement::fetchAll() для получения всех данных в одно действие
Использовать PDOSTatement::fetch() для получение одной записи за итерацию

Даже если первый вариант выглядит очень заманчивым, я предпочитаю использовать второй вариант, т.к. он позволяет мне создать единственный Итератор для выполнения загрузки для себя, без ограничения себя параметрами, нужными для выполнения запроса (и таким образом делает это повторно используемым для любых видов выгрузок).


<?php

/**
 * Class DbRowIterator
 *
 * File: Iterator/DbRowIterator.php
 */
class DbRowIterator implements Iterator
{
    /** @var \PDOStatement $pdoStatement The PDO Statement to execute */
    protected $pdoStatement;
    /** @var int $key The cursor pointer */
    protected $key;
    /** @var  bool|\stdClass The resultset for a single row */
    protected $result;
    /** @var  bool $valid Flag indicating there's a valid resource or not */
    protected $valid;

    public function __construct(\PDOStatement $PDOStatement)
    {
        $this->pdoStatement = $PDOStatement;
    }

    /**
     * @inheritDoc
     */
    public function current()
    {
        return $this->result;
    }

    /**
     * @inheritDoc
     */
    public function next()
    {
        $this->key++;
        $this->result = $this->pdoStatement->fetch(
            \PDO::FETCH_OBJ, 
            \PDO::FETCH_ORI_ABS, 
            $this->key
        );
        if (false === $this->result) {
            $this->valid = false;
            return null;
        }
    }

    /**
     * @inheritDoc
     */
    public function key()
    {
        return $this->key;
    }

    /**
     * @inheritDoc
     */
    public function valid()
    {
        return $this->valid;
    }

    /**
     * @inheritDoc
     */
    public function rewind()
    {
        $this->key = 0;
    }
}

<?php

/**

* Class DbRowIterator

* File: Iterator/DbRowIterator.php

class DbRowIterator implements Iterator

{

/** @var \PDOStatement $pdoStatement The PDO Statement to execute */

protected $pdoStatement;

/** @var int $key The cursor pointer */

protected $key;

/** @var bool|\stdClass The resultset for a single row */

protected $result;

/** @var bool $valid Flag indicating there's a valid resource or not */

protected $valid;

public function __construct(\PDOStatement $PDOStatement)

{

$this->pdoStatement = $PDOStatement;

}

/**

* @inheritDoc

public function current()

{

return $this->result;

}

/**

* @inheritDoc

public function next()

{

$this->key++;

$this->result = $this->pdoStatement->fetch(

\PDO::FETCH_OBJ,

\PDO::FETCH_ORI_ABS,

$this->key

);

if (false === $this->result) {

$this->valid = false;

return null;

}

/**

* @inheritDoc

public function key()

{

return $this->key;

}

/**

* @inheritDoc

public function valid()

{

return $this->valid;

}

/**

* @inheritDoc

public function rewind()

{

$this->key = 0;

}

Вышеприведенный Итератор всего лишь реализует PHP Iterator interface, но в нашем примере этого более чем достаточно для достижения нашей цели.

Как вы можете видеть, мы реализовали логику для получения данных в “следующем” цикле, т.к. это наша прямая последовательность получения. Обратите внимание на второй и третий аргументы выражения PDOSTatement::fetch() : вторым аргументом мы можем контроллировать курсор во время нашего получения данных, третий аргумент для позиционирования курсора для этого получения данных, которое было сделано прокручиваемым за пределами Итератора.


<?php
class LastPeriodIterator extends FilterIterator
{
    protected $period;

    public function __construct(\Iterator $iterator, $period = 'last week')
    {
        parent::__construct($iterator);
        $this->period = $period;
    }
    public function accept()
    {
        if (!$this->getInnerIterator()->valid()) {
            return false;
        }
        $row = $this->getInnerIterator()->current();
        $dt = new \DateTime($this->period);
        if ($dt->format('Y-m-d') . '00:00:00' < $row->contact_modified) {
            return true;
        }
        return false;
    }
}

<?php

class LastPeriodIterator extends FilterIterator

{

protected $period;

public function __construct(\Iterator $iterator, $period = 'last week')

{

parent::__construct($iterator);

$this->period = $period;

}

public function accept()

{

if (!$this->getInnerIterator()->valid()) {

return false;

}

$row = $this->getInnerIterator()->current();

$dt = new \DateTime($this->period);

if ($dt->format('Y-m-d') . '00:00:00' < $row->contact_modified) {

return true;

}

return false;

}

Для фильтрации наших данных мы можем теперь расширить SPL FilterIterator, который позволит нам присоединять нашу фильтрацию мнгновенно к нашему DbRowIterator, делая его расширяемым и повторно используемым немедленно.

Изменение нашего изначального кода получения данных внутри кода, который будет использовать оба наших Итератора, теперь очень просто:


$pdo = new \PDO(
    $config['db']['dsn'],
    $config['db']['username'],
    $config['db']['password']
);

$sql = 'SELECT * FROM `gen_contact` ORDER BY `contact_modified` DESC';
$stmt = $pdo->prepare($sql, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]);
$stmt->execute();

$data = new DbRowIterator($stmt);
echo 'Getting the contacts that changed the last 3 months' . PHP_EOL;
$lastPeriod = new LastPeriodIterator($data, '2015-04-01 00:00:00');
foreach ($lastPeriod as $row) {
    echo sprintf(
        '%s (%s)| modified %s',
        $row->contact_name,
        $row->contact_email,
        $row->contact_modified
    ) . PHP_EOL;
}

$pdo = new \PDO(

$config['db']['dsn'],

$config['db']['username'],

$config['db']['password']

);

$sql = 'SELECT * FROM `gen_contact` ORDER BY `contact_modified` DESC';

$stmt = $pdo->prepare($sql, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]);

$stmt->execute();

$data = new DbRowIterator($stmt);

echo 'Getting the contacts that changed the last 3 months' . PHP_EOL;

$lastPeriod = new LastPeriodIterator($data, '2015-04-01 00:00:00');

foreach ($lastPeriod as $row) {

echo sprintf(

'%s (%s)| modified %s',

$row->contact_name,

$row->contact_email,

$row->contact_modified

) . PHP_EOL;

}

Пожалуйста обратите внимание на $pdo->prepare($sql, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]); , т.к. теперь мы должны убедиться, что курсор получения данных теперь прокручиваем, т.е. мы можем использовать запись через управление записями.

Измерения производительности

Я знаю, что все эти требования немного “лишняя” работа и вы можете удивляться, почему вы должны инвестировать в эту “большую работу” , если цикл foreach работает хорошо. Позвольте мне показать вам, используя измерение производительности между двумя:

Foreach цикл

Время получения данных для 63992 из 250000 записей: 2.14 секунды
Время обработки данных для 63992 из 250000 записей: 7.11 секунд
Общее время выполнения для 63992 из 250000 записей: 9.25 секунд
Потребление памяти для 63992 из 250000 записей: 217.75 МБ

Iterator цикл

Время получения данных для 63992 из 250000 записей: 0.92 секунды
Время обработки данных для 63992 из 250000 записей: 5.57 секунд
Общее время выполнения для 63992 из 250000 записей: 6.49 секунд
Потребление памяти для 63992 из 250000 записей: 0.25 МБ

Результат измерения

Получение данных быстрее с Итераторами
Обработка данных быстрее с Итераторами
Потребление памяти крайне лучше с Итераторами

Измерения проводились с MySQL 5.5.43 и PHP 5.5.26 на Ubuntu 12.04 LTS (виртуальной машине). Другие версии PHP, MySQL и OS могут дать вам другие результаты. 250000 записей сгенерировано используя fzaninotto/Faker.

Заключение

Использование простых итераторов в вашем PHP коде может увеличить скорость получения данных и обработку, но наиболее значимо то, что эти измерения показывают вам, что Итераторы сберегают тонну памяти.

Замечание

Итераторы более эффективны для обработки больших объемов данных. Для малых объемов даных (приблизительно до 5000 записей) итераторы могут быть даже медленнее, чем использование массивов, но вы все еще будете в выигрыше по памяти.

Оригинал на английском:

http://www.dragonbe.com/2015/07/speeding-up-database-calls-with-pdo-and.html

Это мой перевод. Если вы заметили ошибки или считаете, что какие-то части можно перевести лучше – пишите в комментарии.

Космонавт в лодке

Личный блог Олега Абражаева о программировании, технологиях и о жизни

Ускорение запросов в базу данных с PDO и итераторами

Разработка без фреймворка

Итераторы

Измерения производительности

Foreach цикл

Iterator цикл

Результат измерения

Заключение

Замечание

Разработка без фреймворка

Итераторы

Измерения производительности

Foreach цикл

Iterator цикл

Результат измерения

Заключение

Замечание

Похожие посты: