Início / dba / Perguntas / 333718
Accepted
DSZ
Asked: 2023-12-04 20:28:08 +0800 CST

Tabelas temporárias e pools de conexões do MySQL

  • 772

Esta é mais uma pergunta do tipo "por favor, confirme/corrija meu entendimento".

Histórico Eu me conecto ao MySQL usando JDBC, o pool de conexões tem cerca de 250 conexões, a maioria das quais parece conectada persistentemente mesmo após o encerramento da consulta - todas usam a mesma conta de usuário. O procedimento armazenado para o qual desejo usar uma tabela temporária é chamado pelo código Java e executa uma transformação em uma coluna JSON para convertê-la em uma linha para uma tabela diferente. A transformação é relativamente cara, executada potencialmente em milhares de linhas, por isso não desejo realizar a transformação mais de uma vez. Para contornar isso, minha solução proposta é transformar os dados em uma tabela temporária, realizar minhas consultas estatísticas em torno dos dados agora transformados e, em seguida, inserir esse atacado na tabela de destino.

Pergunta Eu li que as tabelas temporárias estão vinculadas ao User . Estou certo ao teorizar que, como as conexões aparentemente nunca são encerradas e todas usam a mesma conta de usuário, a tabela temporária criada no procedimento seria compartilhada por todas as conexões e consultas (e assim os dados poderiam ser acessados ​​por outras conexões executando o mesmo procedimento ao mesmo tempo)?

Limitações O que eu gostaria de fazer é transmitir os dados originais ou realizar a transformação e transmitir esses dados de volta para Java. No entanto, estamos lidando com cerca de algumas centenas de megabytes de dados de cada vez e a transferência de dados de volta para Java parece bloquear o banco de dados até que a transmissão seja concluída. (Não sei por que, se alguém tiver uma recomendação para corrigir isso, por favor me avise)

Então, fico com a única outra solução que consegui pensar: não transmitir os dados de origem, mas realizar a transformação, coletar os dados agregados necessários e, em seguida, inserir os dados transformados no destino sem que eles saiam do banco de dados servidor.

desde já, obrigado

mysql

3 respostas

  1. Best Answer

    cerca de 250 conexões, a maioria das quais parecem persistentemente conectadas

    Esse é o propósito de um pool de conexões: manter as conexões abertas e permitir que os aplicativos as reutilizem, em vez de passar pelo caro processo de estabelecer novas conexões.

    De acordo com a documentação , "[uma] TEMPORARYtabela é visível apenas na sessão atual". Isso significa que não, uma tabela temporária criada por uma conexão não será compartilhada por todas as conexões. No entanto, quando outro aplicativo obtiver uma conexão do pool, ele verá todas as tabelas temporárias criadas até o momento na sessão correspondente a essa conexão.

    • 2

  2. As tabelas temporárias estão vinculadas a uma sessão específica, não a um usuário. No MySQL, você pode ter muitas sessões ativas simultaneamente com o mesmo usuário. Cada uma delas pode ter uma tabela temporária com o mesmo nome, mas nenhuma pode ver nenhuma das outras fora de sua própria sessão. Desta forma, é como uma variável local em uma função.

    As conexões são mantidas abertas pelo pool de conexões, mas os clientes subsequentes que adquirem uma determinada conexão não veem as tabelas temporárias deixadas por um cliente anterior.

    Quando um cliente solicita uma nova conexão de um pool de conexões, por padrão a implementação do pool redefine o estado da conexão. Isso significa que a sessão do MySQL limpa todos os dados relacionados à sessão, como transações, variáveis ​​de sessão, tabelas temporárias e instruções preparadas.

    https://github.com/mysql/mysql-connector-j/blob/release/8.x/src/main/user-impl/java/com/mysql/cj/jdbc/MysqlPooledConnection.java#L125-L127

    protected synchronized Connection getConnection(boolean resetServerState, boolean forXa) throws SQLException {
...
        if (resetServerState) {
            this.physicalConn.resetServerState();
        }

    Faz sentido que isso seja uma boa opção, pois caso contrário, um cliente que solicitasse uma conexão de um pool de conexões poderia visualizar os dados deixados por um cliente anterior dessa conexão. Isso criaria o risco de vazamento de dados privados de um cliente para outro.

    Todas as implementações de pool de conexões que conheço usam esse recurso de redefinição de conexão há anos. Acredito que alguns conectores não conseguiram fazer isso anos atrás, mas foram corrigidos. Se você ainda estiver usando um pool de conexões que não redefine a conexão, pare de usá-lo e mude para uma implementação mais moderna.

    • 1

  3. CREATE TEMPORARY TABLEnão é seguro para sua tarefa.

    Posso sugerir a criação de uma tabela 'permanente' para armazenar os dados processados. Em seguida, crie algum mecanismo, possivelmente por meio de outra tabela permanente, para dizer qual tabela possui quais dados foram processados.

    Algum tipo de soma de verificação ou hash pode ser usado para identificar exclusivamente o conjunto de dados. Esse poderia ser o PRIMARY KEYnúmero de dados que contêm os dados processados.

    • 0

relate perguntas