У нас есть многопользовательское приложение (вроде MailChimp, Constant Contact). У каждого из наших клиентов есть свой список контактов (от 5 до 100 000 контактов). Все хранится в одной БОЛЬШОЙ базе данных (в настоящее время 25 ГБ). С момента выпуска нашего продукта у нас есть следующая история данных.

5 лет истории данных: - пользователи / клиенты (200+) - контакты (40 миллионов записей) - кампании - campaign_deliveries (73.843.764 записей) - campaign_queue (8 миллионов в настоящее время)

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

СХЕМА

Таблица контактов

--------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| contact_id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| client_id           | int(10) unsigned | YES  |     | NULL    |                | 
| name                | varchar(60)      | YES  |     | NULL    |                | 
| mail                | varchar(60)      | YES  | MUL | NULL    |                | 
| verified            | int(1)           | YES  |     | 0       |                | 
| owner               | int(10) unsigned | NO   | MUL | 0       |                | 
| date_created        | date             | YES  | MUL | NULL    |                |  
| geolocation         | varchar(100)     | YES  |     | NULL    |                | 
| ip                  | varchar(20)      | YES  | MUL | NULL    |                | 
+---------------------+------------------+------+-----+---------+----------------+  
PRIMARY KEY  (`contact_id`),
  UNIQUE KEY `owner` (`owner`,`mail`),
  KEY `contacts_index_mail` (`mail`),
  KEY `index_contacts_date_created` (`date_created`),
  KEY `index_contacts_ip` (`ip`),

Таблица campaign_deliveries

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(11)          | NO   | PRI | NULL    | auto_increment | 
| newsletter_id | int(10) unsigned | NO   | MUL | 0       |                | 
| contact_id    | int(10) unsigned | NO   | MUL | 0       |                | 
| sent_date     | date             | YES  | MUL | NULL    |                | 
| sent_time     | time             | YES  | MUL | NULL    |                | 
| smtp_server   | varchar(20)      | YES  |     | NULL    |                | 
| owner         | int(5)           | YES  | MUL | NULL    |                | 
| ip            | varchar(20)      | YES  | MUL | NULL    |                | 
+---------------+------------------+------+-----+---------+----------------+
INDEXES
PRIMARY KEY  (`id`),
  UNIQUE KEY `newsletter_id` (`newsletter_id`,`contact_id`),
  KEY `newsletter_delivery_FKIndex1` (`newsletter_id`),
  KEY `newsletter_delivery_FKIndex2` (`contact_id`),
  KEY `newsletter_delivery_owner` (`owner`),
  KEY `index_nd_sent_date` (`sent_date`),
  KEY `index_nd_sent_time` (`sent_time`),
  KEY `index_ip` (`ip`)

Таблица campaign_queue

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| queue_id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| newsletter_id | int(10) unsigned | NO   | MUL | 0       |                | 
| owner         | int(10) unsigned | NO   | MUL | 0       |                | 
| date_to_send  | date             | YES  |     | NULL    |                | 
| contact_id    | int(11)          | NO   | MUL | NULL    |                | 
| date_created  | date             | YES  |     | NULL    |                | 
+---------------+------------------+------+-----+---------+----------------+
INDEXES
PRIMARY KEY  (`queue_id`),
  UNIQUE KEY `newsletter_id` (`newsletter_id`,`contact_id`),
  KEY `newsletter_queue_index1` (`newsletter_id`),
  KEY `newsletter_queue_index4` (`owner`),
  KEY `newsletter_queue_index5` (`newsletter_id`),
  KEY `contacts_contact_id` (`contact_id`)

ЖУРНАЛ медленных запросов --------------------------------------------

Query_time: 350 Lock_time: 1 Rows_sent: 1 Rows_examined: 971004

ВЫБЕРИТЕ COUNT (*) как общее ИЗ contacts ГДЕ (contacts.owner = 70 И contacts.verified = 1);

Query_time: 235 Lock_time: 1 Rows_sent: 1 Rows_examined: 4455209

ВЫБЕРИТЕ COUNT (*) как общее ИЗ contacts ГДЕ (contacts.owner = 2);

ОБЪЯСНИТЕ

ОБЪЯСНИТЬ ВЫБРАТЬ СЧЁТ (*) как итого ОТ contacts ГДЕ (contacts.owner = 112 И contacts.verified = 1);

+----+-------------+----------+------+---------------+-------+---------+-------+--------+-------------+
| id | select_type | table    | type | possible_keys | key   | key_len | ref   | rows   | Extra       |
+----+-------------+----------+------+---------------+-------+---------+-------+--------+-------------+
|  1 | SIMPLE      | contacts | ref  | owner         | owner | 4       | const | 644817 | Using where | 
+----+-------------+----------+------+---------------+-------+---------+-------+--------+-------------+

Как мы можем его оптимизировать? Запросы должны выполняться не более 30 секунд? Можем ли мы оптимизировать его и хранить все данные в одной БОЛЬШОЙ базе данных или нам следует изменить структуру приложения и установить одну базу данных для каждого пользователя?

РЕДАКТИРОВАТЬ индексы таблицы, добавленные ниже схемы

Спасибо