Абсолютный кошмар устаревшей базы данных MySQL
Таблица 1: Все, включая кухонную раковину. Даты в неправильном формате (последний год, поэтому вы не можете отсортировать по этому столбцу), числа, сохраненные как VARCHAR, полные адреса в столбце «улица», имя и фамилия в столбце имени, город в столбце фамилии, неполные адреса, строки, которые обновлять предыдущие строки, перемещая данные из одного поля в другое на основе некоторого набора правил, которые менялись с годами, повторяющихся записей, неполных записей, записей мусора ... вы называете это ... ну и, конечно, не TIMESTAMP или PRIMARY КЛЮЧЕВОЙ столбец в поле зрения.
Таблица 2: Всякая надежда на нормализацию исчезла после того, как открыли этого ребенка. У нас есть строка для каждой записи И обновление строк в первой таблице. Так что дубликаты вроде "завтра не наступит" (на 800 МБ) и столбцы вроде Phone1 Phone2 Phone3 Phone4 ... Phone15 (они не называются телефоном. Я использую это для иллюстрации) Внешний ключ ... ну, предположим. Есть три кандидата в зависимости от того, какие данные были в строке в table1.
Таблица 3: Может ли быть хуже. О, да. Внешний ключ представляет собой комбинацию столбцов типа VARCHAR, состоящую из дефисов, точек, цифр и букв! Если он не обеспечивает совпадения (чего часто не происходит), то должен использоваться второй столбец с аналогичным кодом продукта. Столбцы с именами, имеющими НЕТ корреляции с данными в них и обязательным Phone1 Phone2 Phone3 Phone4 ... Phone 15. Есть столбцы, дублированные из Table1, а не столбец TIMESTAMP или PRIMARY KEY в поле зрения.
Таблица 4: работа была описана как незавершенная и может быть изменена в любой момент. Это по существу аналогично остальным.
Приблизительно к 1 метру строк это БОЛЬШОЙ беспорядок. К счастью, это не моя большая проблема. К сожалению, мне приходится вытаскивать из него композитную запись для каждого «покупателя».
Первоначально я разработал четырехэтапный перевод таблицы 1, добавив ПЕРВИЧНЫЙ КЛЮЧ и преобразовав все даты в сортируемый формат. Затем еще пара шагов запросов, которые возвращали отфильтрованные данные, пока я не получил Table1, где я мог бы использовать его для извлечения из других таблиц для формирования композиции. После недель работы я сократил это до одного шага, используя некоторые уловки. Итак, теперь я могу указать своим приложением на беспорядок и вытащить красивую чистую таблицу составных данных. К счастью, для моих целей мне нужен только один из телефонных номеров, поэтому нормализация моей таблицы не является проблемой.
Однако именно здесь начинается настоящая задача, потому что каждый день сотни сотрудников добавляют / обновляют / удаляют эту базу данных способами, которые вы не хотите себе представить, и каждую ночь я должен извлекать новые строки.
Поскольку существующие строки в любой из таблиц могут быть изменены, и поскольку столбцов TIMESTAMP ON UPDATE нет, мне придется обратиться к журналам, чтобы узнать, что произошло. Конечно, это предполагает наличие двоичного журнала, которого нет!
Представление концепта пошло вниз, как свинцовый шар. С таким же успехом я мог бы сказать им, что их детям предстоит сделать экспериментальную операцию. Они не совсем высокотехнологичные ... на случай, если вы еще не собрались ...
Ситуация немного деликатная, так как у них есть ценная информация, которая очень нужна моей компании. Меня послали высшее руководство крупной корпорации (вы знаете, какие они есть), чтобы «это произошло».
Я не могу придумать другого способа обработки ночных обновлений, кроме анализа файла журнала bin с помощью еще одного приложения, чтобы выяснить, что они сделали с этой базой данных в течение дня, а затем соответствующим образом составить мою таблицу. Мне действительно нужно только взглянуть на их таблицу1, чтобы понять, что делать с моей таблицей. В других таблицах просто есть поля для удаления записи. (Использование MASTER SLAVE не поможет, потому что у меня будет дубликат беспорядка.)
Альтернативой является создание уникального хеша для каждой строки их table1 и построение хеш-таблицы. Затем я каждую ночь просматривал ВСЮ базу данных, проверяя, совпадают ли хеши. Если они этого не делают, я бы прочитал эту запись и проверил, существует ли она в моей базе данных, если это так, я бы обновил ее в своей базе данных, если это не так, то это новая запись, и я бы ВСТАВЬ ее. Это некрасиво и не быстро, но анализ двоичного файла журнала тоже не очень приятен.
Я написал это, чтобы помочь разобраться в проблеме. часто рассказывая об этом кому-то другому, это помогает прояснить проблему, делая решение более очевидным. В этом случае у меня просто больше болит голова!
Будем признательны за ваши мысли.
Ответы 4
Разве вы не можете использовать существующий код, который обращается к этой базе данных и адаптировать ее к вашим потребностям? Конечно, код должен быть ужасным, но мог бы обрабатывает структуру базы данных за вас, не так ли? Надеюсь, тогда вы могли бы сосредоточиться на выполнении своей работы вместо того, чтобы играть в археолога.
вы могли бы использовать инструмент maatkit mk-table-sync для синхронизации промежуточной базы данных (в конце концов, ваша база данных очень мала). Это будет «дублировать беспорядок»
Затем вы можете написать что-нибудь, что после синхронизации будет выполнять различные запросы для создания набора более разумных таблиц, о которых вы затем можете сообщить.
Я полагаю, что это можно делать ежедневно без проблем с производительностью.
Выполнение всего этого с другого сервера не повлияет на исходную базу данных.
Единственная проблема, которую я вижу, - это то, что у некоторых таблиц нет первичных ключей.
Я не являюсь специалистом по MySQL, поэтому это выходит из левого поля.
Но я думаю, что файлы журналов могут быть ответом.
К счастью, вам действительно нужно знать только 2 вещи из журнала.
Вам нужен идентификатор записи / строки и операция.
В большинстве БД, и я предполагаю, что MySQL, в каждой строке есть неявный столбец, например rowid или recordid, или что-то еще. Это внутренний номер строки, используемый базой данных. Это ваш «бесплатный» первичный ключ.
Далее вам потребуется операция. В частности, будь то операция вставки, обновления или удаления строки.
Вы объединяете всю эту информацию во времени, а затем просматриваете ее.
Для каждой вставки / обновления вы выбираете строку из исходной БД и вставляете / обновляете эту строку в целевой БД. Если это удаление, вы удаляете строку.
Вам наплевать на значения полей, они просто не важны. Сделайте весь ряд.
Надеюсь, вам не придется «разбирать» двоичные файлы журналов, MySQL уже должен иметь для этого процедуры, вам просто нужно найти и выяснить, как их использовать (может даже быть какая-то удобная утилита «дампа журнала», которую вы могли бы использовать ).
Это позволяет сделать систему довольно простой, и это должно зависеть только от вашей реальной активности в течение дня, а не от общего размера БД. Наконец, позже вы сможете оптимизировать его, сделав «умнее». Например, возможно, они вставляют строку, затем обновляют ее, а затем удаляют. Вы бы знали, что можете просто полностью игнорировать эту строку в воспроизведении.
Очевидно, что для того, чтобы на самом деле читать файлы журнала, требуется немного мистических знаний, но остальное должно быть простым. Я хотел бы думать, что файлы журнала также имеют временные метки, чтобы вы могли знать, что нужно работать со строками «с сегодняшнего дня» или с любым диапазоном дат, который вам нужен.
Файлы журналов (двоичные журналы) тоже были моей первой мыслью. Если бы вы знали, как они поступают, вы бы вздрогнули. Для каждой строки есть много записей в журнале по мере добавления и изменения частей. Это просто ОГРОМНО! А пока я остановился на подходе Hash. С некоторой умной подкачкой файловой памяти это довольно быстро.