Как удалить критерии соответствия строк из огромной таблицы MySQL?

Question

Я использую базу данных MySQL в одном из моих приложений, в котором размер таблицы составляет около 10 ГБ. Я хочу удалить строки из этой таблицы, соответствующие критериям. Запрос на удаление имеет вид:

Delete from table_name
where (a,b,c) in ((1,2,3),(1,5,6));

Существует составной индекс по полям (a,b,c). Запрос занимает несколько минут, чтобы удалить строки, тогда как я хочу, чтобы операция выполнялась в миллисекундах или нескольких секундах. Как мне этого добиться? Будет ли отдельный индекс в полях быстрее, чем индекс из нескольких столбцов?

Я имею в виду, если запрос выглядит так:

Delete from table_name
where a = 1 and (b,c) in ((2,3),(5,6));

Это даст более быстрые результаты?

Answer 1

Если у вас есть отдельный набор значений для (a,b,c), вы можете разбить таблицу по этим значениям. Разделение столбцов списка. После того, как таблица была разбита на разделы, "удаление" вместо этого приведет к удалению разделов и будет очень быстрым.

MySQL 5.7 поддерживает разделение LIST COLUMNS. Это вариант секционирования LIST, который позволяет использовать несколько столбцов в качестве ключей разделения и для столбцов типов данных, отличных от целочисленных типов, которые будут использоваться в качестве столбцов разделения;

Пример таблицы из документации:

CREATE TABLE customers_1 (
   first_name VARCHAR(25),
   last_name VARCHAR(25),
   street_1 VARCHAR(30),
   street_2 VARCHAR(30),
   city VARCHAR(15),
   renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
   PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
   PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
   PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
   PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

Я не смог найти пример в документации с использованием нескольких столбцов в качестве ключа раздела, но, как и следовало ожидать, он будет выглядеть так...

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(first_name,last_name) (
    PARTITION me_partition VALUES IN(('Alden','W'),('Aldino','W')),
    PARTITION you_partition VALUES IN(('Pooja','Gupta'),('PJ','Gupta'))
);

Для разделения таблицы вы можете использовать немного модных штанов динамического sql.

SET SESSION group_concat_max_len = 1000000000;
SET @i = 0;
SELECT CONCAT('ALTER TABLE customers_2
PARTITION BY LIST COLUMNS(first_name,last_name) (
    ',GROUP_CONCAT('PARTITION partition_',@i:=@i+1,' VALUES 
IN((''',d.first_name,''',''',d.last_name,'''))' SEPARATOR 
',\n\t'),'
);')
FROM (
    SELECT DISTINCT first_name, last_name
    FROM customers_2
) d;

Это построить следующий оператор alter

ALTER TABLE customers_2
PARTITION BY LIST COLUMNS(first_name,last_name) (
    PARTITION partition_1 VALUES IN(('Alden','W')),
    PARTITION partition_2 VALUES IN(('Jon','Smith')),
    PARTITION partition_3 VALUES IN(('Other','Name')),
    ...
);