假设我有一个仅追加的表:
CREATE TABLE IF NOT EXISTS `states` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`person_id` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
);
在name
上有一个索引,在person_id
上有另一个索引(person_id
是对另一个表的fkey引用(。
对于每个名称,我们存储一个到给定日期范围的person_id
的映射。从name
->person_id
的映射是多对一的(这是一个人为的例子,但可以将其视为存储一个人如何更改姓名(。我们从不想删除历史记录,因此在更改映射时,我们会插入一个新条目。给定名称的最后一个条目是真理的来源。我们最终想在数据集上问两种不同类型的问题,对此我有一些一般性的问题。
- 给定名称/名称列表的当前映射是什么
如果只有一个名称,最直接的查询是:
select * from states where name = 'name' ORDER BY `id` DESC LIMIT 1;
如果有不止一个名字,我能想出的最好办法就是:
select * from states as a
left join states as b on a.name = b.name and a.id < b.id
where isnull(b.id);
这实际上是批量查询的最佳方式吗?对于一批1,第二个查询会比第一个查询差多少?使用explain,我可以看出我们最终执行的是两个索引查找,而不是1。考虑到我们非常关心这种单独查找的性能,我的直觉是根据查询的名称数量运行不同的查询。不过,我更希望有一种方法可以遵从mysql的优化器。有没有一种方法可以编写这个查询,让mysql知道该为我做什么?
- 映射到
person_id
/person_id
列表的当前映射是什么
我查询的方式是:
select * from states as a
left join states as b on a.name = b.name and a.id < b.id
where isnull(b.id) and person_id in person_id_list
不过,我有点担心小列表的性能,因为我对mysql如何工作的理解有限。使用explain,我知道mysql通过a上的索引按person_id进行过滤,然后再按isnul(b.id(进行过滤。但它是在联接之前还是在联接之后进行过滤?我们会不会浪费很多时间加入这两张桌子?一般来说,我该怎么想?
(1(中的代码是"groupwise max",但执行效率非常低。(按照我添加的标签进行更多讨论。(
我可以建议你有两张桌子吗;一个只是附加的,就像你一样。让我们把这个表称为History
。然后有另一个名为Current
的表。添加新条目时,将INSERT
替换为History
,但将其替换为Current
。
如果您确实采用了这种方法,请考虑您在这两个表中可能存在的差异。PRIMARY KEY
肯定会有所不同;其他索引可能不同,甚至某些列也可能不同。