MySql 如何处理复合索引

Question

CREATE TABLE campaigns (
  domain varchar(50) DEFAULT NULL,
  campaign_id varchar(50) DEFAULT NULL,
  node_id varchar(50) DEFAULT NULL,
  subscriber_id varchar(50) DEFAULT NULL,
  message varchar(21000) DEFAULT NULL,
  log_time datetime DEFAULT NULL,
  log_type varchar(50) DEFAULT NULL,
  campaign_name varchar(500) DEFAULT NULL
  KEY `campid_domain_logtype_logtime_subid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`),
  KEY `domain_logtype_logtime_index` (`domain`,`log_type`,`log_time`)
)

上面的是我们在 MySql 中的表的模式。一个广告系列可以有多个节点。

索引campid_domain_logtype_logtime_subid_index适用于特定广告系列的报告。

最近我们在此表中添加了node_id，现在我们需要报告 w.r.t 特定节点。

所以现在我计划更改我们的覆盖指数，如下所示以支持广告系列级别和节点级别报告

campid_nodeid_domain_logtype_logtime_subid_index .

特定广告系列报表的示例查询

SELECT log_type,
        count(DISTINCT subscriber_id) AS count,
        count(subscriber_id) AS total 
FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) 
where domain = 'aaa' AND campaign_id='12345' AND 
  log_type in ('EMAIL_SENT','EMAIL_OPENED','EMAIL_CLICKED')
  AND log_time BETWEEN CONVERT_TZ('2016-03-13 00:00:00','+01:30','+00:00') AND CONVERT_TZ('2016-04-13 23:59:59','+01:30','+00:00') 
GROUP BY log_type;

针对特定广告系列的特定节点 ID 的报告查询示例

SELECT 
   log_type,
   count(DISTINCT subscriber_id) AS count,
   count(subscriber_id) AS total 
FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) 
where domain='aaa' AND campaign_id='12345' AND 
      node_id = '56789' and 
      log_type in ('EMAIL_SENT','EMAIL_OPENED','EMAIL_CLICKED') 
      AND log_time BETWEEN CONVERT_TZ('2016-03-13 00:00:00','+01:30','+00:00') AND CONVERT_TZ('2016-04-13 23:59:59','+01:30','+00:00') 
GROUP BY log_type;

所以我的问题，这个新索引是否有效地满足了我们的两种查询，如果不能，请建议合适的索引。

更新：

表中的数据分布

1 个网域可以有多个广告系列，例如 20

个

1 活动可以有多个节点，例如 10

1 活动可以有多个日志类型，例如 50

1 广告系列可以有很多订阅者，例如 100,000

1 活动可以有很多日志时间，因为我们在存储日志时使用 mysql now((。

更新

谢谢

Answer 1

构建最佳索引的基本规则：

1. 以任何顺序包含带有"="的列;
2. 包括另一列，最好是一个范围。

因此，您需要INDEX(domain, campaign_id, log_time)进行第一次查询。 log_type妨碍了;不要包括它。

(已添加("新建"查询需要INDEX(campaign_id, node_id, domain, log_time)。

在这两种情况下，log_time都应该排在最后;其他列可以按任意顺序排列。 重新排列顺序可能有助于其他查询。 没有一个索引是两个查询的最佳索引。

然后，不要使用USE INDEX;它可能适得其反。

更多食谱提示。

更好的解决方案更复杂：构建和维护经常需要的各种计数器/总和的汇总表。 (可能使用 30 分钟的时间范围。 警告：COUNT(DISTINCT ...)无法在汇总表中处理。

为什么这些ID VARCHAR(50)，而不是INT UNSIGNED？ 您还将受益于规范化其他字段，例如 domain 。 log_type可以是 1 字节ENUM .

你真的需要所有列都是可为空的吗？

这些列的任何组合都是唯一的吗？ InnoDB真的很喜欢有一个PRIMARY KEY。

Answer 2

根据您的查询，您应该创建以下索引

KEY `campid_domain_logtype_logtime_subid_index` (
    `campaign_id`, 
    `domain`, 
    `log_time`, 
    `node_id`, 
    `subscriber_id`
);

campaign_id和log_time总是使用，node_id在某些情况下。

不确定是否应该离开domain和subscriber_id

不要在查询中看到所有这些。

log_type也是值得商榷的。我不认为它具有良好的选择性，只是浪费空间。

更新

根据我们在评论中的沟通

• 广告系列 ID 是主要列
• 域是第二个。尝试玩，也许你应该删除它
• 范围选择的日志时间
• 特定查询的节点 ID

如果您经常在查询中使用subscriber_id - 请添加它。

我会删除log_type。

作为选项，尝试创建以下索引：

 Campaign id, Log time, Node ID  (subscriber_id ?)