Search the Community

Если подключить в 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 = <<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 = <<

Если подключить в 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 = <<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 = <<