Início / user-222739

Federico Baù's questions

Martin Hope
Federico Baù
Asked: 2024-06-04 21:49:07 +0800 CST
  • 6

Cenário

Imagine que temos um tableusuário e um itemusuário. Essas 2 tabelas possuem uma tabela associativa chamada user_itempara definir um many to manyrelacionamento.

  • Começamos 100 itemregistros

  • Temos 500 milhões userde registros.

  • Portanto devemos gerar 50_000_000_000 user_item(50 bilhões)

  • Poderíamos potencialmente ter ainda mais

  • Não será fácil fragmentar nem particionar porque isso tornará qualquer outra operação mais lenta (caso contrário, precisaremos verificar tudo)

  • Assuma como padrão de consulta ( INSERT, SELECT, UPDATE) padrões m2m básicos/típicos (que podem ser encontrados em qualquer tutorial ou exemplo

Pergunta

Qual é o melhor design ou solução conhecida para lidar com bilhões de relacionamentos Muitos para Muitos em um banco de dados, independentemente de um esquema?

Esquema

Imagine este esquema simples

CREATE DATABASE IF NOT EXISTS `playground` CHARACTER SET = latin1;
USE playground;

CREATE TABLE IF NOT EXISTS `user`
(
    `id`   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user__name_fk` (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

CREATE TABLE IF NOT EXISTS `item`
(
    `id`   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user__name` (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

CREATE TABLE IF NOT EXISTS `user_item`
(
    `user_id` BIGINT UNSIGNED NOT NULL,
    `item_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`, `item_id`),
    INDEX `user_item__item` (`item_id`),

    FOREIGN KEY `user_id_fk` (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
    FOREIGN KEY `item_id_fk` (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

-- create some default items
INSERT INTO `item` (`name`) VALUES ('item_1'), ('item_2'), ('item_3'), ('item_4'), ('item_5'), ('item_6'), ('item_7'), ('item_8'), ('item_9'), ('item_10');
-- create some users
INSERT INTO `user` (`name`) VALUES ('user_1'), ('user_2'), ('user_3'), ('user_4'), ('user_5'), ('user_6'), ('user_7'), ('user_8'), ('user_9'), ('user_10');
INSERT INTO `user_item` (`user_id`, `item_id`) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);

Mais informações

Não estou perguntando como usar o relacionamento muitos para muitos no MySQL, eu sei disso. Estou perguntando qual é a solução mais conhecida para um problema de escala, ou seja, quando o número de registros relacionados está crescendo exponencialmente em uma escala tão grande.

Além disso, intencionalmente não adicionei nenhum padrão de consulta ( INSERT, SELECT, UPDATE) porque é irrelevante . Suponha o padrão M2M mais típico. Não quero perder o foco na questão real que é sobre escalabilidade e enorme quantidade de dados.

Deve haver algum truque ou alguma solução alternativa conhecida, certo? Também estou considerando um banco de dados NoSQL, então a resposta pode incluir qualquer coisa não relacionada ao MySQL (ou qualquer banco de dados SQL),

Sinto que este deveria ser um problema comum que muitas grandes empresas enfrentarão e, portanto, deveria haver uma solução comum (ou poucas). A causa raiz desse problema é que, embora o MySQL seja ótimo para criar relacionamentos, ele aumentará exponencialmente a tabela associativa m2m .

Os 500 milhões x 100 == 50 bilhões são apenas um exemplo. Mas poderia teoricamente acontecer.

Esclarecimento

  • Deixei a consulta de propósito porque você pode assumir a mais fácil.
  • Tenho certeza que se eu der alguns exemplos, começarei a aparecer a otimização na consulta específica, essa não é a questão
  • Estou fazendo uma pergunta de alto nível e, se não houver uma solução real conhecida, um não explicando o porquê seria suficiente (presumindo que esteja correto)

Aqui está um exemplo de uma consulta simples de muitos para muitos.

SELECT user.*, item.* FROM user
LEFT JOIN user_item ON user.id = user_item.user_id
LEFT JOIN item ON item.id = user_item.item_id
WHERE user.name = 'user_1';

Perguntas semelhantes, mas não iguais

mysql
Martin Hope
Federico Baù
Asked: 2023-01-03 04:13:12 +0800 CST
  • 9

Problema

Tendo um banco de dados existente, precisamos adicionar a a columna table, sendo esta coluna do tipo int, existe uma maneira de definir o valor inicial padrão como resultado de uma consulta no banco de dados?

Apenas para dar um pouco mais de contexto do problema. Este valor 'padrão' seria usado apenas inicialmente, quando a coluna for adicionada, então podemos alterar a nova coluna novamente dando um padrão normal (exemplo default 0)

Fazendo isso, você seria capaz de inicializar um valor em um banco de dados existente, sem a necessidade de uma migração (provavelmente através de um script fazendo um loop no existente, objectscontando quantos itens eles têm e dando seu valor inicial.

Exemplo:

esquema

create table object
(
    object_id       int auto_increment   primary key,
    name            varchar(120)         not null
);
create table item
(
    item_id       varchar(63)         not null,
    object_id int                     not null,
    primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
      ("item1", 1),
      ("item2", 1),
      ("item3", 1),
      ("item4", 1);

Podemos a quantidade total de item para cada objeto desta forma

-- Counting item of object
SELECT COUNT(*) AS total, o.object_id , o.name
    FROM item AS i
    JOIN object o on i.object_id = o.object_id
    GROUP BY o.object_id ORDER BY total DESC LIMIT 50;
total
4

estou procurando algo assim

ALTER TABLE object ADD COLUMN item_count INT DEFAULT (
    SELECT COUNT(*) AS total
    FROM item AS i
    JOIN object o on i.object_id = o.object_id
    GROUP BY o.object_id ORDER BY total DESC LIMIT 50

    );

Se esse recurso não existe, diga-me, não seria uma boa ideia tê-lo :)?

mysql
Martin Hope
Federico Baù
Asked: 2022-12-28 09:08:51 +0800 CST
  • 5

Problema

Existe uma maneira de contar automaticamente as tabelas relacionadas (um para muitos), onde a contagem aumentará / diminuirá automaticamente dependendo se adicionarmos ou removermos um item relacionado.

Então, obviamente, eu poderia apenas fazer um COUNT, mas por motivos de desempenho, custa milhões de registros e são consultas muitas e muitas vezes. Como solução, em vez de contar todas as vezes, eu realmente criaria um contador onde adicionar 1 ao adicionar um novo item relacionado ou remover 1 ao excluir ou deferir outro item

Para fazer isso, posso apenas criar outra tabela que sirva como contador e consultar essa tabela sem contar.

Existe uma maneira melhor, de preferência, que seja automática?

Exemplo

esquema

create table object
(
    object_id       int auto_increment   primary key,
    name            varchar(120)         not null
);


create table item
(
    item_id       varchar(63)         not null,
    object_id int                     not null,
    primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
      ("item1", 1),
      ("item2", 1),
      ("item3", 1),
      ("item4", 1);

O objeto "hello" possui 4 itens:

select count(*) from item where object_id = 1;
-- ouput: 4

No entanto, descobri que, como solução alternativa, posso criar um contador ( que está usando Python de back-end ) que cada vez que você faz uma operação CRUD , o contador é atualizado. Por exemplo:

Esquema de contador

create table item_counter
(
    counter bigint                  NOT NULL DEFAULT 0,
    object_id int                   NOT NULL  primary key
)

Então agora o ORM no poderia faríamos algo assim (novamente, será manipulado em Python, mas não importa é apenas como exemplo:

-- create object AND item_counter
insert into object (name) VALUES ("hello");
-- create in the same time a counter 
insert into item_counter (object_id) VALUES ((SELECT object_id FROM object where name = "hello"));
-- create items
insert into item (item_id, object_id) VALUES  ("item1", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item2", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item3", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item4", 2);
update item_counter set counter = counter + 1 where object_id = 2;
-- select the counter instead
select counter from item_counter where object_id = 2;

Que se fosse em python ficaria assim


# pseudo ORM code 
class ItemORM:

    def save(self, item_id, object_id):
        self.orm.save(item_id, object_id)
        counter = self.orm.get_counter(object_id)
        counter.add()

Então, existe uma maneira melhor, especialmente, algo que o MySQL possa fazer automaticamente?

Mais Contexto

Caso você queira saber por quê, digamos que estou trabalhando em uma grande base de código, com muitos códigos legados e dependências de API usados ​​por clientes externos e atualmente não há uma contra-implementação , mas é apenas uma ideia minha para superá-la. Portanto, alterar grande parte do código é muito arriscado; em vez disso, ajustar as tabelas MYSQL pode ser uma solução melhor.

mysql