mysql查询与100个表的联合与100个单独的查询性能如何?

aemubtdh  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(580)

我有一个使用mysql数据库和myisam表的遗留项目,数据库的设计还远远不够完美。我遇到了n+1的问题,因为有实体表和实体数量,一些具有相似基本结构的子实体表和一些随机的附加列,其中一些id是实体表中记录的主键值。
当然,这绝对不是很好,但让我们假设这是我们的初始条件,不能在短期内改变。因此,我需要优化一个查询,其中我需要从实体表中选择一些记录,并从相关实体表中选择一些聚合数据。聚合将只使用所有子实体表中相似的列。最初,这是作为对实体表的单个查询来实现的,然后在一个循环中执行大量的查询,以访问相应的实体\一些\ id \子实体表。
我不能使用连接,因为每个实体都有一个单独的子实体表,所以使用联合可能有助于将查询数减少到2,其中第二个将使用联合来对子实体表进行子查询。
另一个需要注意的是,我需要做排序的所有东西之前,分页将被应用。
你能给我一些建议吗?在这种情况下,尝试与工会合作是值得的,还是两种情况下的表现都不好?或者你对如何处理这件事有更好的想法?

更新:

对实体表的查询很简单,如下所示:

SELECT col1, col2, col3 FROM entity WHERE ... LIMIT 10 OFFSET 0;

对实体\u some \u id \u子实体的查询如下所示:

SELECT count(id) total, min(start_date) started, max(completion_date) completed 
FROM entity_1234_subentity
ORDER BY started;

这里的entity\u 1234\u subentity是表名的示例。
使用联合可以看起来像:

SELECT count(id) total, min(start_date) started, max(completion_date) completed 
    FROM entity_1111_subentity
UNION
    (SELECT count(id) total, min(start_date) started, max(completion_date) completed 
    FROM entity_2222_subentity)
UNION
    (SELECT count(id) total, min(start_date) started, max(completion_date) completed 
    FROM entity_3333_subentity)
...
ORDER BY started
n3schb8v

n3schb8v1#

这是一个典型的设计,似乎是聪明的时候被创造出来,但结果是绝对不能扩展。。。我见过很多这样的项目。如果我找到了你,我会为搜索函数创建一个索引。
你可以的
a) 用户外部索引/搜索引擎solr或elasticsearch。
b) 在RDB中创建一个索引表,其中包含来自所有子表(如id、开始日期、完成日期)的循环信息,这些子表在每次子表更新时都会更新,或者,如果代码中有许多地方需要更改,则每小时/天都会按cronjob进行更新。

dy2hfwbg

dy2hfwbg2#

闻起来像是有人试图实现表继承,结果留下了一堆乱七八糟的东西。
您可以使用json和视图解决这个问题,可能比编写100个联合要快。
在事务中(或至少在副本上测试它)修改 entity 所以它可以保存子表中的所有信息。
将子表中的所有公共列添加到 entity .
添加一个json列来保存抓取的数据包。

alter table entity add column start_date datetime;
alter table entity add column completion_date datetime;
alter table entity add column data json;

如果您不喜欢json,可以使用传统的键/值表来存储额外的列,尽管这会失去一些灵活性,因为值必须是字符串。

create table entity_data (
  entity_id bigint not null,
  key varchar(255) not null,
  value varchar(255) not null
);

然后,对于每个子表。。。
用每个实体的子实体信息更新每个实体的信息。公共列将直接更新。其余的转换成json。

update entity e
inner join entity_123_subentity s on e.id = 123
set
  start_date = s.start_date,
  completion_date = s.completion_date,
  data = json_object(`extra1`, s.extra1, `extra2`, s.extra2)
where id = 123

完成并验证后,删除子表并用视图替换它。

drop entity_123_subentity;

create view entity_123_subentity
  (id, start_date, completion_date, extra1, extra2)
as
select
  id, start_date, completion_date, data->>'$.extra1', data->>'$.extra2'
from entity
where subid = 123;

重复,直到没有更多的子表。
新的查询可以高效地编写,而旧的查询在重写之前仍然可以工作。

相关问题