Como usar TRUNCATE/DROP TABLE mais rápido - PolarDB: Central de Documentação do Alibaba Cloud

O PolarDB for MySQL otimiza as operações TRUNCATE TABLE e DROP TABLE substituindo o mecanismo de varredura completa do buffer pool do MySQL Community Edition por uma busca de páginas baseada em space-ID. Essa mudança elimina o gargalo da varredura completa, reduz o tempo de execução de DDL em até 99% e aumenta a estabilidade em clusters com grande quantidade de memória ou sob altas cargas.

Como funciona

No MySQL Community Edition, os comandos TRUNCATE TABLE e DROP TABLE acionam uma varredura completa no buffer pool para localizar e remover todas as páginas de dados da tabela de destino. Em clusters com buffer pools grandes (dezenas ou centenas de GB), essa varredura pode levar vários segundos ou mais e bloquear outras operações.

O PolarDB for MySQL evita essa varredura completa identificando e removendo diretamente apenas as páginas associadas ao tablespace da tabela de destino, usando uma busca baseada em space-ID. Isso torna as operações TRUNCATE TABLE e DROP TABLE quase instantâneas, independentemente do tamanho do buffer pool.

Pré-requisitos

Seu cluster PolarDB for MySQL deve executar uma das seguintes versões:

MySQL 5.7, versão de revisão 5.7.1.0.19 ou posterior.
MySQL 8.0.1, versão de revisão 8.0.1.1.50 ou posterior.
MySQL 8.0.2, versão de revisão 8.0.2.2.30.1 ou posterior.

Nota

Para verificar a versão de revisão do seu cluster, consulte o Guia de versões do PolarDB for MySQL.

Ativar o recurso

Defina o parâmetro loose_innodb_flush_pages_using_space_id para ativar ou desativar este recurso. Para obter instruções sobre como configurar parâmetros, consulte Definir parâmetros de cluster e parâmetros de nó.

Parâmetro	Nível	Descrição
loose_innodb_flush_pages_using_space_id	Global	Controla o recurso de TRUNCATE/DROP TABLE mais rápido. Valores válidos: ON (ativar) e OFF (desativar).

Benchmarks de desempenho

Ativar o recurso melhora significativamente o desempenho do TRUNCATE TABLE em todas as especificações de cluster. A melhoria é mais evidente em clusters com buffer pools maiores: em um cluster de 64 núcleos e 512 GB, o TRUNCATE TABLE em uma tabela com 2 milhões de linhas é concluído em 0,11 segundos, em vez de 9,48 segundos, representando uma redução de 98,84%. Mesmo na menor configuração testada (4 núcleos, 16 GB), o recurso proporciona uma melhoria de 65-87%.

Os resultados a seguir foram medidos no MySQL 5.7.

Resultados

Tabela t1 (8.192 linhas)

Especificações do cluster	Buffer pool (GB)	ON (segundos)	OFF (segundos)	Melhoria
64 núcleos, 512 GB	374	0,01	5,2	99,81%
32 núcleos, 256 GB	192	0,02	2,45	99,18%
16 núcleos, 128 GB	96	0,01	1,73	99,42%
8 núcleos, 64 GB	42	0,01	0,73	98,63%
4 núcleos, 32 GB	24	0,02	0,45	95,56%
4 núcleos, 16 GB	12	0,03	0,23	86,96%

Tabela t2 (2.097.152 linhas)

Especificações do cluster	Buffer pool (GB)	ON (segundos)	OFF (segundos)	Melhoria
64 núcleos, 512 GB	374	0,11	9,48	98,84%
32 núcleos, 256 GB	192	0,1	2,65	96,23%
16 núcleos, 128 GB	96	0,12	1,86	93,55%
8 núcleos, 64 GB	42	0,12	0,79	84,81%
4 núcleos, 32 GB	24	0,13	0,53	75,47%
4 núcleos, 16 GB	12	0,12	0,35	65,71%

Método de teste

Preencha os buffer pools em clusters com diferentes especificações. Isso garante que o buffer pool esteja cheio e que seu tamanho afete diretamente o tempo da operação TRUNCATE TABLE.

ip=<cluster_endpoint>
user=<database_account>
psw=<database_password>
port=<database_port>
MYSQL="mysql -h $ip -P $port -u$user -p$psw -vvv -e"
$MYSQL "create database test;"
$MYSQL "use test;create table if not exists t3 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
$MYSQL "use test;insert into t3 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
for ((i=1; i<=32; i+=1))
do
   $MYSQL --host=$ip -p$psw --port=$port -u$user  -vvv -e "use test;insert into t3 select * from t3;"
done
$MYSQL  "use test; select count(*) from t3;"

Crie duas tabelas de teste, t1 e t2. Insira 8.192 linhas de dados em t1 e 2.097.152 linhas de dados em t2.

# Create the t1 table and insert 8,192 rows of data.
row_num=13
ip=<cluster_endpoint>
user=<database_account>
psw=<database_password>
port=<database_port>
MYSQL="mysql -h $ip -P $port -u$user -p$psw -vvv -e"
$MYSQL "use test;create table if not exists t1 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
$MYSQL "use test;insert into t1 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
for ((i=1; i<=$row_num; i+=1))
do
   $MYSQL "use test;insert into t1 select * from t1;"
done

# Create the t2 table and insert 2,097,152 rows of data.
row_num=21
ip=<cluster_endpoint>
user=<database_account>
psw=<database_password>
port=<database_port>
MYSQL="mysql -h $ip -P $port -u$user -p$psw -vvv -e"
$MYSQL "use test;create table if not exists t2 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
$MYSQL "use test;insert into t2 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
for ((i=1; i<=$row_num; i+=1))
do
   $MYSQL "use test;insert into t2 select * from t2;"
done

Em clusters com diferentes especificações, execute a operação TRUNCATE TABLE nas tabelas t1 e t2 com o recurso ativado e desativado.

Fale conosco

Se você tiver dúvidas sobre operações DDL, entre em contato com o suporte técnico.