Говнокод #14412 — SQL — Говнокод.ру

SQL / Говнокод #14412

−138
1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
```
ALTER TABLE `test` ENGINE MyISAM;
  SELECT COUNT(*) FROM `test`;
  ALTER IGNORE TABLE `test` ADD UNIQUE INDEX `dupidx` (`col1`, `col2`, ...);
  SELECT COUNT(*) FROM `test`;
  ALTER TABLE `test` DROP INDEX `dupidx`;
ALTER TABLE `test` ENGINE InnoDB;
```
На Говнокод не тянет. Но идея мне кажется говнистой. Задача удалить все записи с дубликатами значений в полях.

Запостил: Vasiliy, 24 Января 2014

Tweet
Комментарии (58) RSS
- guest 24.01.2014 19:05 # 0
  
  Нечто подобное - применение уникального индекса для удаления дубликатов - мне встречалось книге одного из именитых мэтров SQL.
  Так что, сама идея нормальная. А вот конкретная реализация может попахивать (я не работал с упомянутой ДБ).
  Ответить
  - bormand 24.01.2014 20:44 # +1
    
    > Так что, сама идея нормальная.
    Да не особо она нормальная. Адекватный и предсказуемый результат будет только если этот уникальный индекс строить по всем подряд колонкам. В остальных случаях это какой-то корейский рандом.
    Ответить
- bormand 24.01.2014 20:24 # +4
  
  Блеать, mysql еще и грохает рандомную запись из совпавших во время построения unique index'а?!
  
  Ну все, теперь я точно никогда не буду с ней связываться.
  Ответить
  - WGH 24.01.2014 20:32 # +1
    
    Так IGNORE включает такое опасное поведение. Иначе будет аборт.
    Ответить
    - bormand 24.01.2014 20:34 # +2
      
      В InnoDB IGNORE не работает, и это хорошо :)
      
      UPD: Лол, так это баг! http://bugs.mysql.com/bug.php?id=40344
      Ответить
      
      inkanus-gray 25.01.2014 08:32 # +2
      
      Самое смешное, что в 2008-м году они обещали этот баг пофиксить в версии 6.0. Сон разума рождает чудовища...
      Ответить
      
      bormand 25.01.2014 10:30 # +1
      
      Но потом они опомнились и решили его никогда не фиксить?
      Ответить
      
      inkanus-gray 25.01.2014 10:48 # +6
      
      Нет, потом они опомнились и решили никогда не выпускать MySQL 6.0.
      Ответить
      
      inkanus-gray 25.01.2014 12:30 # +3
      
      P.S. Оказывается, на Рутрекере есть всё, даже MySQL 6.0. На официальном сайте нет, а на Рутрекере есть. Пираты совсем совесть потеряли.
      Ответить
      
      bormand 25.01.2014 12:36 # 0
      
      > Пираты совсем совесть потеряли.
      Ну дык опенсурс же, форкнули и дописали ;)
      Ответить
      
      inkanus-gray 25.01.2014 12:54 # 0
      
      Но зачем? Я не хочу видеть InnoDB с работающим IGNORE!!!
      Ответить
      
      Abbath 25.01.2014 17:28 # +3
      
      Версии 6 - прокляты. Perl, PHP, MySQL. Кресты это обошли, сразу с 03 на 11
      Ответить
      
      bormand 25.01.2014 17:31 # +6
      
      Borland C++ Builder 6.0
      Visual C++ 6.0
      Ответить
      
      Abbath 25.01.2014 17:33 # 0
      
      И где они сейчас?
      Ответить
      
      bormand 25.01.2014 17:35 # 0
      
      Юзаются, до сих пор ;)
      
      Как минимум знаю 2 конторы, где юзают BCB 6. А визуалку юзает Тарас.
      Ответить
      
      defecate-plusplus 25.01.2014 17:41 # +1
      
      когда речь идёт о 6й вижуалке, то ещё очень спорно, кто кого юзает.
      
      на самом деле всем известно, что Тарас обожает всё новенькое
      поэтому не надо грязи, он юзает 2003 студию
      Ответить
      
      Abbath 25.01.2014 17:53 # +3
      
      >новенькое
      >новенькое
      
      /fixed
      Ответить
      
      defecate-plusplus 25.01.2014 17:35 # +3
      
      DOS опять же
      Ответить
      
      bormand 25.01.2014 17:36 # +2
      
      И тоже, сука, никак не умрет, по крайней мере проги под него. Шестые версии или не выходят вообще, или их труп гальванизируют десятилетиями...
      
      P.S. Самый популярный FoxPro не шестой случаем был?
      Ответить
      
      1024-- 25.01.2014 17:43 # +2
      
      Windows 6.0 проклята. А по версии некоторых - и 6.2 вместе с ней
      Ответить
      
      Bart 30.01.2014 11:28 # 0
      
      > Версии 6 - прокляты
      На то они и шестерки, что не любят их...
      Ответить
  - bormand 24.01.2014 20:33 # +2
    
    Слава богу, что это говнорасширение IGNORE работает только в myisam, и в InnoDB не поддерживается.
    Ответить
    - Vasiliy 25.01.2014 02:58 # +2
      
      Именно поэтому первой строчкой ALTER TABLE `test` ENGINE MyISAM;
      а последней обратно ALTER TABLE `test` ENGINE InnoDB;
      Ответить
      
      inkanus-gray 25.01.2014 08:28 # +2
      
      Меня мучает вопрос, сколько времени будет тарахтеть, если скормить этому алгоритму, например, базу ФИАС.
      Ответить
      
      bormand 25.01.2014 10:12 # +2
      
      Сейчас скачаем да затестим ;)
      Ответить
      
      bormand 25.01.2014 10:31 # 0
      
      P.S. В мускуле можно замутить отдельный тейблспейс для InnoDB базы? А то у меня в корне всего гигов 6 свободно, а чистить влом.
      Ответить
      
      inkanus-gray 25.01.2014 10:51 # +1
      
      Есть вариант:
      1. С помощью innodb_data_home_dir создать новый корень InnoDB.
      2. Параметр конфига innodb_file_per_table раскидает таблицы по файлам, как в MyISAM.
      
      Ответить
      
      bormand 25.01.2014 12:15 # 0
      
      Спасибо, работает.
      
      P.S. Сраный фаерфокс не напомнил о недокачанном файле при выходе, и я качаю фиас заново ;(
      Ответить
      
      bormand 25.01.2014 17:32 # 0
      
      Кстати, база ФИАС - мелкая херня по сравнению с выгрузкой openstreetmap.
      Ответить
      
      bormand 25.01.2014 18:04 # +1
      
      Ну что, 30кк записей из таблицы HOUSE залиты (ФИАС что-то быстро растет, в старом снепшоте было 20кк). Запускаем алгоритм Василия ;)
      Ответить
      
      defecate-plusplus 25.01.2014 18:09 # +2
      
      > алгоритм Василия
      и в данном случае использование этого словосочетания столь же правомерно, как и "болезнь Альцгеймера" или "палочка Коха"
      Ответить
      
      bormand 25.01.2014 18:23 # +1
      
      mysql> ALTER TABLE `house` ENGINE MyISAM; Query OK, 29939779 rows affected (1 min 7.18 sec) Records: 29939779 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM `house`; +----------+ | COUNT(*) | +----------+ | 29939779 | +----------+ 1 row in set (0.01 sec) mysql> ALTER IGNORE TABLE `house` ADD UNIQUE INDEX `dupidx` (`aoguid`); Query OK, 29939779 rows affected (1 min 58.11 sec) Records: 29939779 Duplicates: 29276795 Warnings: 0 mysql> SELECT COUNT(*) FROM `house`; +----------+ | COUNT(*) | +----------+ | 662984 | +----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `house` DROP INDEX `dupidx`; Query OK, 662984 rows affected (0.50 sec) Records: 662984 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `house` ENGINE InnoDB; Query OK, 662984 rows affected (25.20 sec) Records: 662984 Duplicates: 0 Warnings: 0
      Шустрый алгоритм.
      Ответить
      
      defecate-plusplus 25.01.2014 18:30 # 0
      
      > шустрый
      > ~211 секунд
      а в сравнении с нормальным алгоритмом на нормальной субд?
      Ответить
      
      bormand 25.01.2014 18:51 # 0
      
      Мне сейчас лениво заливать эту хрень еще час в другую субд ;) Завтра попробую на слонёнке.
      
      > нормальным алгоритмом
      Что-то типа такого?
      delete from house using house ref where house.aoguid = ref.aoguid and house.updatedate < ref.updatedate;
      Ответить
      
      defecate-plusplus 25.01.2014 19:08 # +1
      
      странный запрос (как и непривычный синтаксис)
      что будет с постгресом, если внутренний запрос для одной исходной строки сделает много результирующих строк?
      Ответить
      
      bormand 25.01.2014 19:22 # 0
      
      > странный запрос
      Ну в данном случае он бессмысленный (равно как и применение алгоритма Василия к этой базе), но идея примерно такая: из записей с совпадающим id удалить старые. На маленькой тестовой табличке работает.
      
      > что будет с постгресом, если внутренний запрос для одной исходной строки сделает много результирующих строк?
      Если имелось в виду select (select ...) as a from ... или select ... from ... where (select ...) < 42, где внутренний (select...) вернул несколько строк - то зафейлится.
      Ответить
      
      bormand 25.01.2014 20:01 # 0
      
      Гугл подсказывает еще вот такой прием:
      insert into result select h1.* from house h1 left join house h2 on h1.aoguid = h2.aoguid and h1.udpatedate < h2.updatedate where h2.aoguid is null;
      Ответить
      
      wvxvw 25.01.2014 19:19 # 0
      
      Удалять - это вряд ли очень бысто будет... лучше копировать в другую таблицу, и потом переименовать.
      Ответить
      
      bormand 25.01.2014 19:33 # +2
      
      Да тут еще от задачи сильно зависит. Может быть эти старые записи вообще сгруппировать придется, сложив какие-нибудь суммы из них. Может быть придется выбирать какую из них грохнуть по какому-то условию, а часть вообще добивать руками. А может быть надо заодно поубивать что-то в других таблицах...
      
      А в виде "перегрохать рандомные записи с совпадающими полями" (что и делает ignore в mysql) оно мало кому нужно. Я даже не могу придумать применений, ну кроме удаления полностью совпадающих строк.
      Ответить
      
      defecate-plusplus 25.01.2014 19:56 # 0
      
      ынтерпрайз намекает, что применения бывают
      но, слава богу, не в mysql
      Ответить
      
      bormand 25.01.2014 20:03 # 0
      
      > применения бывают
      Например? Я просто в работе с базами не особо силен, поэтому и не могу придумать.
      Ответить
      
      defecate-plusplus 25.01.2014 20:12 # +2
      
      в прошлом году пришлось решать эту задачу
      на таблице в десятки миллионов записей
      
      в связи с багой closed source системы кое-какая статистика складировалась в эту самую таблицу с заметным дублированием каждой записи от 2 до 9 раз
      причем, каждый дубликат, понятное дело, получал свой уникальный первичный ключ - но все остальные колонки были идентичны
      
      пока заказчику не было дела до состояния статистики, это происходило незаметно и копилось месяцами, пока в один момент не бабахнуло
      
      пришлось разбираться в ситуации и исправлять прямо в базе
      вручную за весь исторический период, и автоматически джобом
      теперь еженощно за 3 предыдущих дня (с запасом) статистика перепроверяется и дубликаты аккуратно чистятся
      
      p.s. ну а в этом году надо бы уже поработать с производителем и посмотреть, что они там наисправляли по этой проблеме
      Ответить
      
      3.14159265 25.01.2014 23:15 # +3
      
      >>на таблице в десятки миллионов записей
      Идентичная ситуация была. Не один раз причем. Один раз какая-то сука дропнула констрейнт, а потом другая (м.б. та же) написала говно.
      Это практически неизбежная ситуация, как и то что каждый должен сделать update/delete без where.
      
      Одна большая транзакция залочила бы всю таблицу и надолго. Шансов отработать - ноль. Сделал скрипт, который брал небольшими батчами, искал и удолял. За ночь всё отработало. Десятки миллионов в принципе немного.
      Зато потом запросы на таблице стали летать!
      
      >>теперь еженощно за 3 предыдущих дня (с запасом) статистика перепроверяется и дубликаты аккуратно чистятся
      А не проще ли констрейнт повесить? Кто не пишет if exists () update <...> else insert <...> - тот сам виноват.
      Ответить
      
      defecate-plusplus 25.01.2014 23:21 # 0
      
      констрейнт будет означать неисправимую ошибку бд для активной высоконагруженной системы, которую разработал не ты и => исправить её в их говнокоде ты не можешь
      
      эффект будет непредсказуем, вплоть то до того, что она перестанет любую статистику собирать или вообще перестанет работать
      
      зачем мне такой праздник, у меня и так головной боли хватает
      Ответить
      
      defecate-plusplus 25.01.2014 23:26 # +5
      
      > if exists () update <...> else insert <...>
      ну merge же
      не пугай меня на ночь своими конструкциями
      Ответить
      
      3.14159265 25.01.2014 23:40 # 0
      
      >>ну merge же
      Мне показалось речь шла о каком-то жутком легаси.
      
      >>исправить её в их говнокоде ты не можешь
      Ну тогда костыль по планировщику, да - единственный выход.
      Ответить
      
      eth0 26.01.2014 17:51 # +2
      
      > merge
      Жаль, что его в постгрес никак не впилят, вельми полезная штука.
      Ответить
      
      bormand 26.01.2014 09:01 # 0
      
      fias=> insert into result select h1.* from house h1 left join house h2 on h1.aoguid = h2.aoguid and h1.houseid < h2.houseid where h2.aoguid is null; INSERT 0 662984 Time: 142904,246 ms fias=> select count(*) from result; count -------- 662984 (1 row) Time: 259,014 ms
      143 секунды.
      
      Если есть какие-то предложения - можно затестить, базу я пока не грохнул.
      Ответить
      
      defecate-plusplus 26.01.2014 11:08 # +2
      
      погоди, засовывать в другую таблицу результат - неспортивно
      
      или в майскл при смене джвижка примерно то же самое происходит?
      Ответить
      
      bormand 26.01.2014 11:10 # 0
      
      > или в майскл при смене джвижка примерно то же самое происходит?
      Да, там при смене движка оно копируется в другой файл.
      
      Сейчас копию базы запилю, чтобы не жалко было, и опробую на ней что-нибудь с delete.
      Ответить
      
      defecate-plusplus 26.01.2014 11:24 # +1
      
      ну если так, то с delete будет заметно дольше
      
      тебе же надо пару десятков М записей пометить, сегмент отката набить
      не. совсем другую цифру получишь
      Ответить
      
      bormand 26.01.2014 11:50 # +1
      
      Общество защиты баз данных меня побьет за жестокое обращение со слоненком:
      fias=> delete from house where exists(select houseid from house h2 where house.aoguid = h2.aoguid and house.houseid < h2.houseid); DELETE 29276795 Time: 1465177,637 ms fias=> select count(*) from house; count -------- 662984 (1 row)
      Ответить
      
      bormand 26.01.2014 11:55 # 0
      
      И еще 200 секунд слоник бегал с пылесосом:
      fias=> VACUUM house; VACUUM Time: 198992,379 ms
      Ответить
      
      eth0 26.01.2014 17:52 # +2
      
      Пропылесосим дом;
      Ответить
      
      bormand 26.01.2014 11:57 # 0
      
      > пришлось разбираться в ситуации и исправлять прямо в базе
      > вручную за весь исторический период
      А ты эту операцию делал через delete или переписывание в новую таблицу?
      Ответить
      
      defecate-plusplus 26.01.2014 12:05 # +1
      
      через delete
      
      во-первых, там не 95% подлежало удалению, а около половины
      
      ну и я вспомнил, что речь именно там шла не о десятках М, а о единицах
      
      во-вторых, это все делалось на живой системе, на таблице с зависимостями и индексами, и взять на ходу дропать и переименовывать таблицы - точно не вариант
      
      ну и в-третьих, там вполне производительный сервак, напрягся только на минуту, я гораздо дольше вылизывал этот запрос, чтобы ничего лишнего не угробить
      Ответить
      
      bormand 26.01.2014 13:49 # +1
      
      Ну вот сейчас попробовал - напихал в таблицу 4кк записей, повторяющихся по 2 раза, и поудалял повторы самым тупым и брутальным способом:
      fias=> insert into test(id, field1, field2) select id, md5((id / 2) :: varchar(32)), md5((id/2 + 1) :: varchar(32)) from generate_series(1, 4000000) as id; INSERT 0 4000000 Time: 21861,741 ms fias=> delete from test where exists(select id from test t2 where test.field1 = t2.field1 and test.field2 = t2.field2 and test.id < t2.id); DELETE 1999999 Time: 55293,478 ms fias=> select count(*) from test; count --------- 2000001 (1 row) Time: 3709,793 ms
      За минуту управилось. Т.е. постгрес не такой уж и медленный ;)
      Ответить
- guest 25.01.2014 21:40 # −1
  
  twttr.receiveCount({"count":0,"url":"htt p:\/\/govnokod.ru\/14407\/"});
  Ответить
Добавить комментарий
Ошибка компиляции комментария:

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

А не использовать ли нам bbcode?

[b]жирный[/b] — жирный

[i]курсив[/i] — курсив

[u]подчеркнутый[/u] — подчеркнутый

[s]перечеркнутый[/s] — перечеркнутый

[blink]мигающий[/blink] — мигающий

[color=red]цвет[/color] — цвет (подробнее)

[size=20]размер[/size] — размер (подробнее)

[code=<language>]some code[/code] (подробнее)

Проверочный код: *

Говнокод: по колено в коде.

SQL / Говнокод #14412

Комментарии (58) RSS

Добавить комментарий