我有一个用于存储统计数据的表格。目前,在一天结束时填充了大约 1000 万行,然后复制到每日统计信息表并删除。因此,我不能拥有自动递增的主键。
这是表结构:
CREATE TABLE `stats` (
`shop_id` int(11) NOT NULL,
`title` varchar(255) CHARACTER SET latin1 NOT NULL,
`created` datetime NOT NULL,
`mobile` tinyint(1) NOT NULL DEFAULT '0',
`click` tinyint(1) NOT NULL DEFAULT '0',
`conversion` tinyint(1) NOT NULL DEFAULT '0',
`ip` varchar(20) CHARACTER SET latin1 NOT NULL,
KEY `shop_id` (`shop_id`,`created`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
我有一个键shop_id, created, ip
但我不确定应该使用哪些列来创建最佳索引以进一步提高查找速度?
下面的查询在没有键的情况下大约需要 12 秒,使用上面的索引大约需要 1.5 秒:
SELECT DATE(CONVERT_TZ(`created`, 'UTC', 'Australia/Brisbane')) AS `date`, COUNT(*) AS `views`
FROM `stats`
WHERE `created` <= '2017-07-18 09:59:59'
AND `shop_id` = '17515021'
AND `click` != 1
AND `conversion` != 1
GROUP BY DATE(CONVERT_TZ(`created`, 'UTC', 'Australia/Brisbane'))
ORDER BY DATE(CONVERT_TZ(`created`, 'UTC', 'Australia/Brisbane'));
- 如果没有保证唯一的列(或列的组合(,则有一个
AUTO_INCREMENT id
。 不用担心截断/删除。 (但是,如果 id 未重置,则可能需要使用BIGINT
,而不是INT UNSIGNED
以避免溢出。 - 不要使用
id
作为主键,而是使用PRIMARY KEY(shop_id, created, id), INDEX(id)
。 - 这种非常规的PK将以两种方式帮助提高性能,同时具有独特性(由于添加了
id
(。INDEX(id)
是让AUTO_INCREMENT
开心。 (您是每小时还是每天DELETE
是一个单独的问题。 - 基于每小时(或分钟(构建摘要表。 它将包含这样的计数 - 400K/小时或7K/分钟。 每小时(或每分钟(增加一次,这样您就不必在一天结束时完成所有工作。
- 汇总表还可以按点击和/或转化进行过滤。或者,如果您需要它们,它可以保留两者。
- 如果点击/转化只有两种状态(0和1(,不要说
!= 1
,比如说= 0
;优化器在=
方面比在!=
方面要好得多。 - 如果他们 2 状态并且您更改为
=
,那么这变得可行并且更好:INDEX(shop_id, click, conversion, created)
-created
必须是最后一个。
汇总 - 到摘要表时不要打扰 TZ;稍后应用转换。
- 更好的是,不要使用DATETIME,使用时间戳,这样你就不需要转换(假设你已经正确设置了TZ(。
毕竟,如果您仍有问题,请重新开始讨论问题;可能会有进一步的调整。
在 where 子句中,首先使用该列,它将返回一小组结果,依此类推,并按相同的顺序创建索引。
你有WHERE created <= '2017-07-18 09:59:59'
AND shop_id = '17515021'
AND click != 1
AND conversion != 1
如果创建将返回与其他 3 列相比的少量集合,那么您很好,否则您在 where 子句中的第一列,然后根据相同的解释选择第二列并根据您的 where 子句创建索引。
如果您认为顺序很好,请创建一个索引
KEY created_shopid_click_conversion (created,shop_id, click, conversion);
.