我们正在使用MySql作为数据库

以下查询在mysql表（约2500万条记录）上运行。我在这里粘贴了两个查询，查询运行太慢，我想知道更好的复合索引是否可以改善这种情况。

关于最佳综合指数是什么的任何想法？

并建议我这些查询是否需要复合索引

第一查询

    EXPLAIN SELECT log_type,
       count(DISTINCT subscriber_id) AS distinct_count,
       count(*) as total_count
FROM stats.campaign_logs
WHERE domain = 'xxx'
  AND campaign_id='12345'
  AND log_type IN ('EMAIL_SENT', 'EMAIL_CLICKED', 'EMAIL_OPENED', 'UNSUBSCRIBED')
  AND log_time BETWEEN CONVERT_TZ('2015-02-12 00:00:00','+05:30','+00:00')
                   AND CONVERT_TZ('2015-02-19 23:59:58','+05:30','+00:00')
GROUP BY log_type

上述查询的解释

+----+-------------+---------------+-------------+--------------------------------------------------------------+--------------------------------+---------+------+-------+------------------------------------------------------------------------------+
| id | select_type | table         | type        | possible_keys                                                | key                            | key_len | ref  | rows  | Extra                                                                        |
+----+-------------+---------------+-------------+--------------------------------------------------------------+--------------------------------+---------+------+-------+------------------------------------------------------------------------------+
|  1 | SIMPLE      | campaign_logs | index_merge | campaign_id_index,domain_index,log_type_index,log_time_index | campaign_id_index,domain_index | 153,153 | NULL | 35683 | Using intersect(campaign_id_index,domain_index); Using where; Using filesort |
+----+-------------+---------------+-------------+--------------------------------------------------------------+--------------------------------+---------+------+-------+------------------------------------------------------------------------------+

第二查询

SELECT campaign_id
     , subscriber_id
     , campaign_name
     , log_time
     , log_type
     , message
     , UNIX_TIMESTAMP(log_time) AS time 
  FROM campaign_logs 
 WHERE domain = 'xxx'  
   AND log_type = 'EMAIL_OPENED'  
 ORDER  
    BY log_time DESC 
 LIMIT 20;

上述查询的解释

+----+-------------+---------------+-------------+-----------------------------+-----------------------------+---------+------+--------+---------------------------------------------------------------------------+
| id | select_type | table         | type        | possible_keys               | key                         | key_len | ref  | rows   | Extra                                                                     |
+----+-------------+---------------+-------------+-----------------------------+-----------------------------+---------+------+--------+---------------------------------------------------------------------------+
|  1 | SIMPLE      | campaign_logs | index_merge | domain_index,log_type_index | domain_index,log_type_index | 153,153 | NULL | 118392 | Using intersect(domain_index,log_type_index); Using where; Using filesort |
+----+-------------+---------------+-------------+-----------------------------+-----------------------------+---------+------+--------+---------------------------------------------------------------------------+

第三查询

EXPLAIN SELECT *, UNIX_TIMESTAMP(log_time) AS time FROM stats.campaign_logs WHERE domain = 'xxx' AND log_type <> 'EMAIL_SLEEP' AND  subscriber_id = '123' ORDER BY log_time DESC LIMIT 100

上述查询的解释

+----+-------------+---------------+------+-------------------------------------------------+---------------------+---------+-------+------+-----------------------------+
| id | select_type | table         | type | possible_keys                                   | key                 | key_len | ref   | rows | Extra                       |
+----+-------------+---------------+------+-------------------------------------------------+---------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | campaign_logs | ref  | subscriber_id_index,domain_index,log_type_index | subscriber_id_index | 153     | const |   35 | Using where; Using filesort |
+----+-------------+---------------+------+-------------------------------------------------+---------------------+---------+-------+------+-----------------------------+

如果您需要其他详细信息，我可以在此处提供

更新（2016 / April / 22）
：现在，我们想在现有表中再添加一列，即节点ID。一个广告活动可以有多个节点。无论我们在广告系列中生成什么报告，我们现在也都需要在各个节点上的那些报告。

例如

SELECT log_type,
           count(DISTINCT subscriber_id) AS distinct_count,
           count(*) as total_count
    FROM stats.campaign_logs
    WHERE domain = 'xxx',
      AND campaign_id='12345',
      AND node_id = '34567',
      AND log_type IN ('EMAIL_SENT', 'EMAIL_CLICKED', 'EMAIL_OPENED', 'UNSUBSCRIBED')
      AND log_time BETWEEN CONVERT_TZ('2015-02-12 00:00:00','+05:30','+00:00')
                       AND CONVERT_TZ('2015-02-19 23:59:58','+05:30','+00:00')
    GROUP BY log_type

CREATE TABLE `camp_logs` (
  `domain` varchar(50) DEFAULT NULL,
  `campaign_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,
  `level` varchar(50) DEFAULT NULL,
  `campaign_name` varchar(500) DEFAULT NULL,
  KEY `subscriber_id_index` (`subscriber_id`),
  KEY `log_type_index` (`log_type`),
  KEY `log_time_index` (`log_time`),
  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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

SIZE问题 。

由于我们有两个复合索引，因此索引文件迅速增加。以下是该表格的当前统计信息。数据大小：30 GB索引大小：35 GB

对于有关node_id的报告，我们要更新现有的复合索引

从

KEY `campid_domain_logtype_logtime_subid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`),

到

KEY `campid_domain_logtype_logtime_subid_nodeid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`,`node_id`)

您能否为广告系列和节点级别的报告建议合适的复合索引。

谢谢