Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > PHP: Базы Данных > Большой IN


Автор: Onis 16.5.2012, 17:50
Доброго времени суток Винград
Сейчас занимаюсь оптимизацией одного проекта, и нарвался на довольно сомнительный момент.
Есть скрипт импортирующий из файла имейлы в табличку mysql, каждые 100 итераций он обращается к базе чтобы проверить какие из импортируемых имейлов уже имеют дубликаты в таблице (данные нужны для статистики), делает он это так:
Код

mysql_query('SELECT email FROM queue WHERE email IN ("'.implode('","', array_keys($queue)).'")');

$queue Содержит последние 100 имейлов, а в таблице queue их уже несколько миллионов. Выходит за один проход скрипт может сделать порядка 100 обращений к базе данных с условием IN на 100 элементов.
Как лучше оптимизировать этот момент? Я думал скачать в начале скрипта все имейлы из очереди и сравнивать массивы на стороне php, но оправдает ли себя загрузка такого объемного количества данных? Есть ли другие способы, или стоит этот момент оставить без изменений?

Табличка в innoDB, и сейчас всё переписываю на PDO.

Автор: Fortop 16.5.2012, 18:21
Результаты профайлинга выполнения этого вызова функции приведите.
Плюс explain для самого запроса.

Тогда можно будет о чем-то говорить.

Автор: Onis 17.5.2012, 10:14
Ok, пардон, думал уже есть стандартные методики для избежания подобных выборок. Я сейчас переписываю код у себя на локальной машине, когда будет доступ к продукт серверу и базе, выложу тесты.

Автор: ksnk 17.5.2012, 10:48
Цитата(Onis @  16.5.2012,  17:50 Найти цитируемый пост)
Есть скрипт импортирующий из файла имейлы в табличку mysql

А сделать email уникальным ключем не проще? Тогда можно вставлять insert... on duplicate key update..., чтобы не наталкиваться на ошибки, в крайнем случае просто их игнорировать при вставке.

Автор: Onis 17.5.2012, 11:21
ksnk, 
Это уже сделано, я специально дописал то что эти данные нужны для статистики. Правда перед инсертом дубликаты всё равно убираются
На данный момент в коде это выглядит примерно так:
Код

...
//$this->stack; массив входящих имейлов, обычно за одну итерацию обрабатываем ~100 имейлов

$unsubscribed  = $this->selectAlreadyUnsubscribed($this->stack); // внутри происходить запрос SELECT email FROM unsubscribe WHERE email IN ("'.implode('","', $stack).'")
$bounced  = $this->selectAlreadyBounced($this->stack); //SELECT email FROM bounce WHERE email IN ("'.implode('","', $stack).'")
$dublicate = $this->selectAlreadyInQueue($this->stack); //SELECT email FROM queue WHERE email IN ("'.implode('","', $stack).'")
$this->udpateStatistic(compact($unsubscribed, $bounced, $dublicate));
$this->addToQueue(array_diff($this->stack, $unsubscribed, $bounced, $dublicate)); // INSERT INTO queue ...
...

Ну вот меня мучают сомнения что этот момент сделан оптимально.

Автор: Aliance 17.5.2012, 11:49
Не проще ли выгрузить email`ы в массив вида:
Код

$emails = array('[email protected]', '[email protected]');

а дальше вместо запроса проверять наличие так:
Код

if (in_array($email, $emails))
    print 'Email '.$email.' exists in DB!';

Автор: Onis 17.5.2012, 13:19
Цитата(Aliance @  17.5.2012,  10:49 Найти цитируемый пост)
Не проще ли выгрузить email`ы в массив вида:

Собственно это была часть моего вопроса, у меня вообще была мысль загрузить массивы $unsubscribed,  $bounced и $dublicate в мемкэш и считывать их с базы только если их нет в кэше. Но дело в том что только за последние несколько месяцев каждый из этих массивов уже составляет по несколько миллионов имейлов. Вот и думаю оправдает ли себя в данном случае хранение и манипуляция с многомиллионными массивами.
Был бы у меня сейчас доступ к продукт серверам и данным я бы просто методом перебора подобрал бы оптимальный вариант, но пока в моем распоряжении только скрипт который уже нужно оптимизировать. Вот решил у умных людей спросить.

Автор: Onis 17.5.2012, 14:02
Ладно, решил создать синглтон который будет выступать в роли хранилища и при вызове будет пытаться подгрузить себя с memcache, в нем реализую Lazy Initialization с погрузкой этих массивов. Надеюсь это будет правильное решение. 

Автор: Aliance 17.5.2012, 14:49
Проведите замеры скорости и того, и того решения и сравните. А заодно выложите результаты сюда, всем интересно  smile 

Автор: Onis 17.5.2012, 15:00
Пардон если кто-то прочел превое не отредактированное сообщение, я после теста кэша не вынес кэширование в деструктор. Дурья моя бошка smile еще раз извините.
Вот адекватный  предварительный тест загрузки с mysql и memcahe.
Draft storage class:
Код

<?php
namespace Queue;
require('config.class.php');
require('db.class.php');
require('qmc.class.php');
require('errorhandler.class.php');

class Storage{
    private $recipients = array();

    private function getUnsubscribed(){
        if(!isset($this->recipients['unsubscribed'])){
            $this->recipients['unsubscribed'] = Db::getInstance()->sel_all_unsubscribed()->fetchAll(\PDO::FETCH_COLUMN, 0);
        }
        return $this->recipients['unsubscribed'];
    }

    /***Magic***/
    public function __get($name){
        $method = 'get' . ucfirst($name);
        if(method_exists($this, $method))
            return $this->$method();
        ErrorHandler::trigger('Undefined property requested "' . $name . '"', __FILE__, __LINE__);
        return null;
    }
    public function __destruct(){
          echo 'To cache', "\n";
          QMC::getInstance()->add('add2queue_storage', self::$instance, MEMCACHE_COMPRESSED, 60*10);
    }
    /***Singleton stuff***/
    protected static $instance;
    private function __contruct(){}
    private function __clone(){}
    public static function getInstance(){
        if(!isset(self::$instance)){
            if(!(self::$instance = QMC::getInstance()->get('add2queue_storage'))){
                echo 'Initilized:', "\n";
                self::$instance = new self;
            }
            else
                echo 'From cache:', "\n";
        }
        return self::$instance;
    }
}
//var_dump(QMC::getInstance->get(123));
$start = microtime(true);
echo count(Storage::getInstance()->unsubscribed), ' recipients loaded', "\n";
$end =  microtime(true);
var_dump($end - $start);



Код

[ivan@*** classes]$ ../clear.php
Done.
[ivan@*** classes]$ php storage.class.php
Initilized:
38042 recipients loaded
float(0.038810014724731)
To cache
[ivan@*** classes]$ php storage.class.php
From cache:
38042 recipients loaded
float(0.029272079467773)
To cache
[ivan@*** classes]$ ../clear.php
Done.
[ivan@*** classes]$ php storage.class.php
Initilized:
38042 recipients loaded
float(0.043804168701172)
To cache
[ivan@*** classes]$ php storage.class.php
From cache:
38042 recipients loaded
float(0.029371976852417)
To cache
[ivan@*** classes]$



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

Автор: Fortop 17.5.2012, 20:27
Цитата(Aliance @  17.5.2012,  11:49 Найти цитируемый пост)
Не проще ли выгрузить email`ы в массив вида:

зависит от конкретной БД.
Если пару тысяч записей, то кладите в кеш или в банальный php файл, который при включенном опкодкеше будет подгружаться моментально.
При числе записей в несколько миллионов, БД до определенного момента предпочтительнее, потом при росте нагрузки вы всеравно вынесете это в память.

Автор: Onis 18.5.2012, 09:17
Fortop, 
Вы правы, сегодня удалось протестировать storage класс на продакшен сервере, и на данный момент загруженные данные занимают почти гигабайт оперативной памяти, а их объем будет быстро расти, так что я отказался от идеи выгрузки всех данных с базы.

Автор: Fortop 18.5.2012, 14:05
Цитата(Onis @  18.5.2012,  09:17 Найти цитируемый пост)
 и на данный момент загруженные данные занимают почти гигабайт оперативной памяти, а их объем будет быстро расти, так что я отказался от идеи выгрузки всех данных с базы. 

Ну это как бы не показатель smile у нас только на скриптовом сервере съедается до 100Гб  на пике smile


Onis, давайте вернемся все же к исходным баранам smile
Цитата(Fortop @  16.5.2012,  18:21 Найти цитируемый пост)
Результаты профайлинга выполнения этого вызова функции приведите.
Плюс explain для самого запроса.

Автор: Onis 22.5.2012, 18:15
Цитата(Fortop @  18.5.2012,  13:05 Найти цитируемый пост)
давайте вернемся все же к исходным баранам 

 smile 
Честно говоря, просто переписав тот код, и поправив логику я уменьшил время выполнения скрипта более чем в шесть (!) раз. Причем этот скрипт, писал мой тимлид. Дальше, с этим, возиться не собираюсь.
Я уже написал заявление об уходе из этой конторы, никому не пожелаю столкнуться с таким апофеозом непрофессионализма на уровне целой компании.

Автор: Fortop 23.5.2012, 06:29
Цитата(Onis @  22.5.2012,  18:15 Найти цитируемый пост)
Честно говоря, просто переписав тот код, и поправив логику я уменьшил время выполнения скрипта более чем в шесть (!) раз. Причем этот скрипт, писал мой тимлид. Дальше, с этим, возиться не собираюсь.

Это странный подход.

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

Сначала функционал - потом производительность (иногда даже ценой "переписать все")

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)