Jump to content
SpravkaCRM.ru - Ваш справочник по CRM
  • Sign in to follow this  

    Ускоряем модуль E-mail в SuiteCRM


    SpravkaCRM.ru

    Если подключить в SuiteCRM почту, то можно достаточно удобно ее прикреплять в историю общения с тем или иным клиентом, создавать на основании пришедшего письма обращения и прочие замечательные плюшки. Но если подключить в SuiteCRM почту с большим количеством спама, то весь этот спам радостно будет забивать базу данных SuiteCRM, пока не настанет критический момент, когда с модулем Email станет некомфортно работать. У одного из клиентов он настал на пороге примерно в 1млн записей в таблице `emails`.

    При разборе "тормозов" при работе с почтой в SuiteCRM выяснилось, что слабым местом является именно MySQL (процесс с базой в htop вылетал в 100 и более процентов), а SHOW FULL PROCESSLIST, выполненный в этот момент, показывал нам запрос вроде такого:

    SELECT COUNT(*) c FROM emails
    JOIN emails_text ON emails.id = emails_text.email_id
    WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft','test') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
        SELECT `email_id` FROM `emails_tmp_8118`
    )

    SELECT COUNT(*) c FROM emails
    JOIN emails_text ON emails.id = emails_text.email_id
    WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
    SELECT eear.email_id FROM emails_email_addr_rel eear
    JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = 'eadf4b41-ab90-9e98-38be-4ff16857726d' AND eabr.bean_module = 'Users'
    WHERE eear.deleted=0
    )

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

    SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails
    JOIN emails_text ON emails.id = emails_text.email_id
    WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
    SELECT eear.email_id FROM emails_email_addr_rel eear
    JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = 'eadf4b41-ab90-9e98-38be-4ff16857726d' AND eabr.bean_module = 'Users'
    WHERE eear.deleted=0
    )

    Время чистого выполнения этих запросов в базе данных составляло порядка 28 секунд на каждый запрос. Я сначало пытался понавесить индексов на каждое поле, используемое в этих SQL-запросах, но результата это не дало никакого: все необходимые индексы уже и так были в базе данных, а новые (например, на поле `emails`.`type`) прироста в скорости не выявили.

    Основной проблемой в этих запросах я считал присутствие в операторе WHERE выборки из другого подзапроса, в котором еще и JOIN используется. Как показала практика на некоторых других проектах, в  случаях, когда используются внутренние сложные подзапросы, весьма значимого ускорения можно достичь путем упрощения этих подзапросов: мы должны избавиться от всех этих JOIN в подзапросе + желательно избавиться от WHERE. То есть чтобы выборка из максимально упрощенного подзапроса. Добиться этого можно путем "засовывания" результата работы подзапроса в отдельную ВРЕМЕННУЮ таблицу. А основной запрос уже будет обращаться к этой временной таблице. Тоесть для нашего случая мы должны сначало создать временную таблицу с едиственным полем `email_id`, потом наполнить эту временную таблицу данными, полученными в результате запроса 

    SELECT eear.email_id FROM emails_email_addr_rel eear
    JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = 'eadf4b41-ab90-9e98-38be-4ff16857726d' AND eabr.bean_module = 'Users'
    WHERE eear.deleted=0

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

    По реализации в SuiteCRM на примере одной из функций:

    Находим такой вот файл: include/SugarFolders/SugarFolders.php и в нем фунцию generateArchiveFolderQuery:

        protected function generateArchiveFolderQuery()
       {
          global $current_user;
           $q = <<<ENDQ
    SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails
    JOIN emails_text on emails.id = emails_text.email_id
    WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
    SELECT eear.email_id FROM emails_email_addr_rel eear
    JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = '{$current_user->id}' AND eabr.bean_module = 'Users'
    WHERE eear.deleted=0
    )
    ENDQ;
            return $q;
       }

    и заменяем эту функцию на такую:

     

    protected function generateArchiveFolderQuery()
       {
          global $current_user;
          global $db;
    
          // Название временной таблицы
          $tmp_table_name = 'emails_tmp_' . mt_rand(1000,10000);
    
          // Создаем временную таблицу
          $sql = "
                CREATE TEMPORARY TABLE IF NOT EXISTS `{$tmp_table_name}` (
                  `email_id` VARCHAR(36) NOT NULL DEFAULT '',
                  PRIMARY KEY (`email_id`)
                ) ENGINE=MYISAM DEFAULT CHARSET=utf8
                ";
          $db->query($sql, true);
    
          // Наполняем временную таблицу данными
          $sql = "
                INSERT INTO
                   `{$tmp_table_name}`
                SELECT DISTINCT
                   eear.email_id
                FROM emails_email_addr_rel eear
                JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = '{$current_user->id}' AND eabr.bean_module = 'Users'
                WHERE eear.deleted=0
          ";
          $db->query($sql, true);
    
           // Формируем основной запрос
          $q = <<<ENDQ
    SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails
    JOIN emails_text on emails.id = emails_text.email_id
    WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft','test') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
       SELECT `email_id` FROM `{$tmp_table_name}`
    )
    ENDQ;
            return $q;
       }
    

    Прирост в производительности для конкретно этого взятого случая составил: с 28 секунд на запрос спустились до 8 секунд, что примерно в 4 раза выиграли в скорости. 

    Sign in to follow this  


    User Feedback

    Recommended Comments

    There are no comments to display.



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×