这是学习笔记的第 2152 篇文章
今天有一个同事通过即时通讯工具找我,说需要做一个数据变更操作,我一看需求很简单,是新增了一个列,需要创建相关的索引。
对于SQL自动化上线,目前算是到了收获的时段,从近期的工单情况来看,很多业务需求都从平台化的工单操作转向了自动化单据,按照最新的数据统计结果,假设有150个工单,那么100个左右都是自动化流程完成的,占比近70%。
这个工单的操作是目前自动化不支持的,因为需求是删除已有的索引,然后添加新的索引字段。
当我看到问题的时候,我感觉到一种异常,但是又实在说不清楚,所以准备当面沟通下。
表结构信息如下:
CREATE TABLE `data_stat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`day` int(8) NOT NULL DEFAULT '0',
`kind` varchar(10) NOT NULL DEFAULT '',
`netid` varchar(3) NOT NULL DEFAULT '',
`item` varchar(10) NOT NULL DEFAULT '' ,
`value` varchar(20) NOT NULL DEFAULT '',
`room` varchar(10) NOT NULL DEFAULT 'null' ,
PRIMARY KEY (`id`),
KEY `idx_day_netid` (`day`,`kind`,`netid`,`item`)
) ENGINE=InnoDB AUTO_INCREMENT=55158 DEFAULT CHARSET=utf8 ;
大体的业务含义是对每一天的登录数据进行统计,原本是3个维度(kind,netid,item),现在多了一个维度(room)。
举个小例子,数据可以这样描述:
在2019-01-01(day)这一天用户通过手机(kind)登录了网站,使用的是5G手机(netid),连接的就近站点(room)是北京,在线时长(item)为5分钟(value)。
此时我需要了解的是业务的查询模型,即通常都有哪些场景的查询,而一旦这个room新字段在复合索引中,而如果条件不满足,则这个索引列不会被用到,其实效果更糟。
而通过沟通,我惊奇的发现业务对于这个表的使用是有问题的。他说如果不添加索引字段room,业务就写入不了数据了。
这个大大超出了我的预期,大家可以仔细看下这条SQL,按照我刚刚描述的场景,是否能够理解。
经过沟通,理解了这个业务场景,总算是明白了为什么业务写入不了数据。
可以使用如下的两条数据描述来说明:
第1条记录:
2019-01-01(day)用户通过手机(kind)登录了网站,使用的是5G手机(netid),连接的就近站点(room)是北京,在线时长(item)为5分钟(value)
第2条记录:
2019-01-01(day)用户通过手机(kind)登录了网站,使用的是5G手机(netid),连接的就近站点(room)是北京,在线时长(item)为15分钟(value)
在这种情况下,因为字段(day,kind,netid,item)是唯一性索引,那么第2条记录对应的数据是无法写入的。
所以按照这种设计,如果后续还有新的字段,那索引就需要横向扩展了,所以对于这个问题,我提出了改进建议。
索引确实需要重建,根据业务反馈的查询场景,其实添加非唯一性索引(`day`,`netid`,`room`)已经足够覆盖目前的查询,而更有意义的是:数据写入不会因为索引设计不合理/新增业务字段而导致数据无法写入。
所以在明确了需求之后,帮业务同学重建了索引,这个问题的处理就告一段落。
这个问题带给我的总结就是: