Как оптимизировать SQL-запрос (или MySQL-таблицу) для ускорения его выполнения?

Question

[Леонид]

Имеется такая MySQL-таблица:

CREATE TABLE IF NOT EXISTS `mails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to` varchar(255) NOT NULL,
  `object` varchar(30) NOT NULL,
  `data` text NOT NULL,
  `temp_id` int(11) NOT NULL,
  `mailing_id` int(11) NOT NULL,
  `priority` tinyint(1) NOT NULL,
  `send_time` datetime NOT NULL,
  `error` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Таблица наполнена данными, сейчас порядка: 350 тыс. записей.

В цикле к данной таблице выполняется несколько тысяч запросов (3000-10000 штук) одного и того же вида:

SELECT `id`, `to` FROM `mails` WHERE (`to` LIKE '[e-mail]' OR `to` LIKE '[e-mail],%' OR `to` LIKE '%,[e-mail]' OR `to` LIKE '%,[e-mail],%') AND `mailing_id` = 172

Вопрос: как оптимизировать данный SQL-запрос (или саму MySQL-таблицу) для увеличения скорости его выполнения?


p.s.: запрос выполняется в Yii 1.x через конструктор запросов: Yii::app()->db->createCommand()->select("id, to")->from('{{mails}}')....

Answer 1

[romy4]

выполняйте не несколько тысяч запросов, а один. избавтесь от бесполезного цикла в первую. даже в нулевую очередь!

Comments

[Леонид]

Ни как не получится. Это задача функционала - там по другому никак.

[Максим Тимофеев]

Леонид: Уверен, что можно

Answer 2

[Rsa97]

Выносить адреса в отдельную таблицу и делать связь многие-ко-многим. Поиск через LIKE, если маска начинается с %, - очень затратная и не использующая индексы операция.

Comments

[Леонид]

Пока что это кардинальные меры, но наверное в будущем придется так и делать. Спасибо!

Answer 3

[Максим Тимофеев]

несколько тысяч запросов (3000-10000 штук) одного и того же вида

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

Comments

[Леонид]

Расскажите мне как это сделать. Имеется таблица с e-mail адресами (база рассылки). Имеется таблица mails - которую я тут описал в нёё мы записываем письма, которые потом будут скриптом медленно рассылаться. Так вот когда запускается процесс формирования писем для рассылки, система проходит по выбранным из базы рассылки адресам и формирует для них письма. Вот только адреса в базе рассылки могут повторяться, а формировать повторные письма для одного и того же email адрес нельзя. Потому я в процессе формирования перед каждым созданием письма проверяю, а не было ли ранее для данного email адреса уже сформировано письмо, чтобы не сделать дубль.

[Максим Тимофеев]

Леонид: 1. Делайте выборку всех 1 запросом с уникальным email. Потом перебирайте и отправляйте уже в php.

[Леонид]

Максим Тимофеев: проблема в том что емайл адреса записаны по несколько штук в одно поле через запятую.

[Максим Тимофеев]

Леонид: Меняйте архитектуру этого всего срочно. И делайте по человечески.

[Леонид]

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

[Максим Тимофеев]

Леонид: Можно пойти по неправильному пути. Кроном обходить старую базу и писать из нее данные в новую, а ее уже использовать для почтовика. Даже при такой странной схеме нагрузка будет меньше. Или как вариант все логику по выборке уникальных записей переложить на php. Но надо наверное тогда из базы брать частями данные, что бы не оперировать 200 мегабайтным массивом.

[Максим Тимофеев]

Можно сложнее, в бд создать функции для автоматического заполнения новой таблицы, при появлении новой записи в старой.

[Максим Тимофеев]

Хотя если это yii1, то за эту странную таблицу должна отвечать 1 модель и в ней в afterSave можно заполнять вторую таблицу

[Леонид]

Я попробую использовать промежуточную таблицу с тремя полями, в одном из которых будет email записан, посмотрим как это повлияет на скорость )

[Максим Тимофеев]

Леонид: если вместо 3000 запросов будет 1, отлично повлияет.

Answer 4

[Anton Spirin]

Создать составной индекс по полям `to` и 'mailing_id`.
Условие WHERE заменить на JOIN.
Ну, и обязательно "несколько тысяч запросов" заменить на один.

Comments

[Леонид]

Заменить несколько тысяч запросов на один - не представляется себе возможным. У меня две таблицы, по одной собираются E-mail'ы которые могут повторятся, потом в цикле создаются письма как раз в таблице mails и запрос который я написал проверяет, не было ли создано письмо для такого email уже, чтобы не создавать его повторно.

[Rsa97]

Леонид: А не проще сразу выбрать для каждого mailing_id список email'ов, на которые ещё не было отправлено письмо?

[Леонид]

А как если формирование писем это целый процесс, связанный с вытаскиванием определенного email'a из таблицы и создания для него письма.

Answer 5

[Леонид]

Изначально, не дописал один важный момент: таблица mails содержит в себе письма в разных статусах: в очереди, отправлено и т.д. Мой сумасшедший цикл выполняющий медленный запрос с LIKE'ами - неизбежен при таком функционале: у клиента есть рассылка писем по базе адресов, база адресов очень большая (более 100 тыс.) и главное что используется в разных местах огромной системы, ну и самое плохое: email'ы пишутся через запятую в одном поле и могут повторяться для разных записей из базы рассылки, потому я и использовал LIKE'и чтобы не отправить на один и тот же Email адрес более одного письма.

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

В рамках одного процесса формирования писем создается не более 10 тыс. писем. Потому я думаю пока лучшим временным решением будет создание промежуточной таблицы содержащий все email адреса для формирующихся писем по одному адресу в одной записи в одном поле. Таким образом, не нужно будет юзать LIKE'и, плюс сама таблица будет содержать максимум 3 поля и не содержать более 10 тыс. записей.

Всем спасибо за комментарии и ответы!
Если вдруг появятся еще свежие идеи по моей проблеме пожалуйста напишите здесь!

Answer 6

[stuk_alex]

зачем перебирать все варианты LIKE? Вполне достаточно одного оператора LIKE
SELECT `id`, `to` FROM `mails` WHERE `to` LIKE '[e-mail]%' AND `mailing_id` = 172
можно ли придумать обход в цикле немного по-другому, чтобы сократить количество итераций? мало данных, что означает mailing_id? можно ли объединить другими значениями и сделать mailing_id IN (...)?

Comments

[Леонид]

Затем что ваш вариант:
SELECT `id`, `to` FROM `mails` WHERE `to` LIKE '[e-mail]%' AND `mailing_id` = 172

для случая [e-mail] вернет положительный результат, а это ведь абсолютно два разных адреса.

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