我们有多用户应用程序(如 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 contacts
WHERE ( contacts
. owner
= 70 AND contacts
. verified
= 1 );
查询时间:235 锁定时间:1 发送行数:1 检查行数:4455209
SELECT COUNT(*) 作为总计 FROM contacts
WHERE ( contacts
. owner
= 2 );
解释
解释选择 COUNT(*) 作为总计 FROM contacts
WHERE(contacts
. owner
= 112 AND contacts
. verified
= 1);
+----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+ | id | select_type | 表 | 类型 | possible_keys | 键 | key_len | ref | 行 | 额外 | +----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+ | 1 | 简单 | 联系人 | ref | 所有者 | 所有者 | 4 | const | 644817 | 使用 where | +----+-------------+-------+------+-------+-----------+---------+--------+-------+--------+-------------+
我们如何优化它?查询执行时间不应超过 30 秒?我们可以优化它并将所有数据保存在一个大型数据库中吗?还是我们应该更改应用程序的结构并为每个用户设置一个数据库?
编辑表索引添加到架构下方
谢谢