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

Если подключить в 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 раза выиграли в скорости.