MySql多列索引的工作原理

Question

我们有一个表MySql，下面是模式

CREATE TABLE campaigns (
  domain varchar(50) ,
  campaign_id bigint(12) ,
  log_time datetime ,
  log_type int,
  node_id bigint(12) 
)

表简介

一个域可以有多个活动，一个活动可以有多个节点。

有1.5亿行的表。唯一的域是40k。

我希望在此表上创建一个复合索引，以获得活动级别和节点级别的报告。

假设我创建的复合索引如下所示

KEY campid_domain_nodeid_logtime (`campaign_id`,`domain`,`node_id`,`log_time`)

它是否完全满足以下查询，这意味着在活动级别和节点级别

竞选级别报告

select count(*) from campaigns 
where domain = 'aaa' and campaign_id = '1235' 
and log_time between '2016-01-01 00:00:00' and '2016-02-02 00:00:00' 

节点级报告

select count(*) from campaigns
       where domain = 'aaa' and campaign_id = '1235' and node_id = '2345' and  log_time between '2016-01-01 00:00:00' and '2016-02-02 00:00:00

谢谢

Answer 1

您可以将索引视为具有快速查找功能的订单列表。如果您有一个包含A、B、C、D字段的复合索引，则该列表将在A上排序，然后对于A与C、D相同的行进行排序。

A1 | B1 | C1 | D1 | -> pointer to row
A1 | B1 | C1 | D2 | -> pointer to row
A1 | B1 | C2 | D1 | -> pointer to row
A1 | B1 | C2 | D2 | -> pointer to row
A1 | B2 | C1 | D1 | -> pointer to row
...
A2 | B1 | C1 | D1 | -> pointer to row
A2 | B1 | C1 | D2 | -> pointer to row

查询优化器将检查您的查询。如果你的查询要求A，B，C，D，一切都很好。查询的顺序对于一个好的数据库来说并不重要，所以您也可以编写查询where D and C and B and A。

如果您的查询只要求A，那么一切都很好，因为具有相同A的所有行都是一个接一个的。

如果您的查询只要求D，则索引是无用的。具有相同D但不同A的行分布在整个列表中。

如果您的查询要求A，B，D，就像您的竞选级别的报告，那么这个索引是有用的。它可以用来加速A和B的查找，但是由于缺少C，所以需要对所有数据进行迭代。

您可以定义多个索引。当然，缺点是每增加一个索引都会使写入速度变慢，并且需要硬盘上的一些空间。

Answer 2

不，复合索引不会帮助您以这种格式列出的2个查询中的任何一个。where标准中的字段需要与索引中的字段顺序相同。

我还将通过将log_time移动到第三位置来更改索引中字段的顺序：

KEY campid_domain_nodeid_logtime (`campaign_id`,`domain`,`log_time`,`node_id`)

第一次查询更改campaign_id和域的顺序：

select count(*) from campaigns 
where campaign_id = '1235' and domain = 'aaa'
and log_time between '2016-01-01 00:00:00' and '2016-02-02 00:00:00' 

第二个查询更改campaign_id和域+ node_id和log_time的顺序：

select count(*) from campaigns
where  campaign_id = '1235' and domain = 'aaa'
   and  log_time between '2016-01-01 00:00:00' and '2016-02-02 00:00:00' 
   and node_id = '2345'

可以运行explain来验证索引的使用情况。如果您有任何与节点相关的查询，这些查询不会在log_time上筛选，那么这些查询只能是索引的usecampaign_id和域部分。

Answer 3

https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

上面的链接很好地说明了在多列索引过程中索引的顺序。

按以下顺序为列创建索引

域，campaign_id，节点，日志时间

并将节点级别的报告更改为

select count(*) from campaigns
where domain = 'aaa' and campaign_id = '1235'  
and  log_time between '2016-01-01 00:00:00' and '2016-02-02 00:00:00'
and node_id = '2345'