☰

架构

tag-icon • tag-icon mysql performance-tuning database-performance
架构

我们有多用户应用程序（如 MailChimp、Constant Contact）。我们的每个客户都有自己的联系人列表（从 5 个到 100,000 个联系人）。所有内容都存储在一个大型数据库中（目前为 25G）。自我们发布产品以来，我们拥有以下数据历史记录。

5 年的数据历史： - 用户 / 客户（200+） - 联系人（4000 万条记录） - 活动 - campaign_deliveries（73,843,764 条记录） - campaign_queue（目前 800 万条）

随着用户越来越多，表记录也越来越多，我们的系统/Web 应用程序变得越来越慢。某些查询执行时间太长。

架构

表格联系人

--------------------+------------------+------+-----+---------+----------------+
| 字段 | 类型 | Null | 键 | 默认 | 额外 |
+---------------------+------------------+------+-----+---------+----------------+
| contact_id | int（10）无符号|否|PRI |NULL |自动增量|
| client_id | int（10）无符号|是|| NULL ||
| 名称 | varchar(60) | 是 | | NULL | |
| 邮件 | varchar(60) | 是 | MUL | NULL | |
| 已验证 | int(1) | 是 | | 0 | |
| 所有者 | int(10) 无符号 | NO | MUL | 0 | |
| 创建日期 | 日期 | 是 | MUL | NULL | |  
| 地理位置 | varchar(100) | 是 | | NULL | |
| ip | varchar (20) | 是 | MUL | NULL | |
+---------------------+------------------+------+-----+---------+----------------+  
主键 (`contact_id`),
  唯一键 `owner` (`owner`,`mail`),
  键 `contacts_index_mail` (`mail`),
  键 `index_contacts_date_created` (`date_created`),
  键 `index_contacts_ip` (`ip`),

表 campaign_deliveries

+---------------+------------------+------+-----+---------+----------------+
| 字段 | 类型 | Null | 键 | 默认 | 额外 |
+---------------+------------------+------+-----+---------+----------------+
| id | int(11) | 否 | PRI | NULL | 自动增量|
| newsletter_id | int（10）无符号|否|MUL | 0 ||
| contact_id | int(10) 无符号 | 否 | MUL | 0 | |
| 发送日期 | 日期 | 是 | MUL | NULL | |
| sent_time | 时间 | 是 | MUL | NULL | |
| smtp_server | varchar(20) | 是 | | NULL | |
| 所有者 | int(5) | YES | MUL | NULL | |
| ip | varchar (20) | 是 | MUL | NULL | |
+---------------+------------------+------+-----+---------+----------------+
索引
主键 (`id`),
  唯一键 `newsletter_id` (`newsletter_id`,`contact_id`),
  键 `newsletter_delivery_FKIndex1` (`newsletter_id`),
  键 `newsletter_delivery_FKIndex2` (`contact_id`),
  键 `newsletter_delivery_owner` (`所有者`),
  键 `index_nd_sent_date` (`sent_date`),
  键 `index_nd_sent_time` (`sent_time`)，
  键 `index_ip` (`ip`)

表 campaign_queue

+---------------+------------------+------+-----+---------+----------------+
| 字段 | 类型 | Null | 键 | 默认 | 额外 |
+---------------+------------------+------+-----+---------+----------------+
|queue_id|int（10）无符号|否|PRI|NULL|自动增量|
| newsletter_id | int（10）无符号|否|MUL | 0 ||
| 所有者 | int(10) 无符号 | NO | MUL | 0 | |
| date_to_send | 日期 | 是 | | NULL | |
| contact_id | int(11) | 否 | MUL | NULL | |
| 创建日期 | 日期 | 是 | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
索引
主键 (`queue_id`)，
  唯一键 `newsletter_id` (`newsletter_id`,`contact_id`),
  键 `newsletter_queue_index1` (`newsletter_id`),
  键 `newsletter_queue_index4` (`所有者`)，
  键 `newsletter_queue_index5` (`newsletter_id`),
  键 `contacts_contact_id` (`contact_id`)

慢速查询日志--------------------------------------------

查询时间：350 锁定时间：1 发送行数：1 检查行数：971004

SELECT COUNT(*) 作为总计 FROM contactsWHERE ( contacts. owner= 70 AND contacts. verified= 1 );

查询时间：235 锁定时间：1 发送行数：1 检查行数：4455209

SELECT COUNT(*) 作为总计 FROM contactsWHERE ( contacts. owner= 2 );

解释

解释选择 COUNT(*) 作为总计 FROM contactsWHERE（contacts. owner= 112 AND contacts. verified= 1）；

+----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+
| id | select_type | 表 | 类型 | possible_keys | 键 | key_len | ref | 行 | 额外 |
+----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+
| 1 | 简单 | 联系人 | ref | 所有者 | 所有者 | 4 | const | 644817 | 使用 where |
+----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+

我们如何优化它？查询执行时间不应超过 30 秒？我们可以优化它并将所有数据保存在一个大型数据库中吗？还是我们应该更改应用程序的结构并为每个用户设置一个数据库？

编辑表索引添加到架构下方

谢谢

答案1

你有什么索引？你应该在 contacts.owner 上有一个索引，在这种情况下查询应该非常快。创建索引

此外，提供查询计划也会很有用。解释

相关内容