#mysql #performance
Вопрос:
У меня есть таблица MySQL (TABLE1) с 400 тысячами записей
CREATE TABLE `TABLE1` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) NOT NULL,
`UID` varchar(255) NOT NULL,
`USER_ID` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ukey1` (`VALUE`,`NAME`,`UID`),
UNIQUE KEY `ukey2` (`UID`,`NAME`,`VALUE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `TABLE2` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`UID` varchar(255) DEFAULT NULL,
`TABLE3ID` bigint(20) NOT NULL
PRIMARY KEY (`ID`),
KEY `FKEY` (`TABLE3ID`),
CONSTRAINT `FKEY` FOREIGN KEY (`TABLE3ID`) REFERENCES `TABLE3` (`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `TABLE3` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`TYPEID` bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Следующий запрос выполняется очень медленно, занимает несколько часов и, наконец, завершается ошибкой
delete from TABLE1 t1
inner join TABLE2 t2 on t1.UID=t2.UID
inner join TABLE3 t3 on t2.TABLE3ID=t3.ID
where t3.TYPEID in (234,3434) t1.USER_ID is not null and t1.USER_ID <> '12345';
Визуальное объяснение показывает следующее, и добавление индекса в UID не помогает. Как оптимизировать производительность этого запроса?
- Я попытался добавить индекс в таблицу 1.UID
- Преобразование в подзапрос
- Такой простой запрос, как
SELECT * FROM TABLE3 where UID="SOMEUID"получение данных, занимает более 800 мс
Комментарии:
1. Используйте
JOIN«а», а не «чемWHERE UID IN (subquery)«. Я обнаружил, что MySQL реализует последнее очень неэффективно.2. Пожалуйста, добавьте точную версию MySQL и подзапрос тоже. Спасибо
3. У вас есть какие-нибудь показатели на столе? Было бы полезно иметь индекс (UID); индекс (UID, USER_ID) может быть еще лучше.
4. @AleksG Индексы указаны в вопросе. Он сказал, что добавление индекса в UID не помогло.
5. В UID уже есть индекс, так как это префикс
ukey2индекса.
Ответ №1:
Измените его на СОЕДИНЕНИЕ.
DELETE t1
FROM TABLE1 AS t1
JOIN (SELECT uid FROM ...) AS t2 ON t1.uid = t2.uid
WHERE USER_ID is not null and USER_ID <> '12345';
Я обнаружил, что MySQL иногда реализует WHERE uid IN (subquery) очень плохо. Вместо того, чтобы получать все результаты подзапроса и искать их в индексе таблицы, он сканирует таблицу и выполняет подзапрос для каждой строки, а затем проверяет, есть ли uid в этом результате.
Комментарии:
1. Спасибо, На самом деле, это было ЗДОРОВО. Я опустил это для краткости. Обновил вопрос
2. Основной проблемой, по-видимому, является ТАБЛИЦА 1 с 300 тысячами записей, и в таблице есть столбец UID varchar(255)
Ответ №2:
Прежде всего сделайте резервную копию этой таблицы это первое правило для выполнения запросов на удаление, иначе вы можете ее испортить и принять все меры предосторожности, которые вы приняли до
( uid1,uid2,…uid45000)
Каково значение этих значений между скобками ? Вам нужно сравнить в списке все значения UID или некоторые из них?
потому что вы можете избежать размещения всех UID вручную, как это.
удалите из ТАБЛИЦЫ 1, где UID в (ВЫБЕРИТЕ T. UID ИЗ ТАБЛИЦЫ 1 как T, где T. UID не равен NULL, и ИДЕНТИФИКАТОР ПОЛЬЗОВАТЕЛЯ <> ‘12345’);
Прежде чем сделать это, пожалуйста, проверьте, что вы хотите между скобками, и сначала выполните команду в тестовой среде с фиктивными значениями
Примите во внимание, что у вас в таблице есть типы varchars в поле UIDS, и именно по этой причине эта операция занимает намного больше времени, чем если бы вы использовали целочисленные значения
Другой способ заключается в том, что вам нужно создать новую таблицу и поместить данные, которые вам нужно сохранить для старой таблицы, затем обрезать исходную таблицу и снова вставить те же значения новой таблицы в старую таблицу
Пожалуйста, прежде чем запускать решение, проверьте все свои ограничения у коллег по команде и проведите тест с фиктивными значениями
Ответ №3:
Я бы разделил ваш список фильтров uid на куски (100 по кускам или другим, нужно проверить) и повторил или многопоточно по нему