Деперсонализация базы MySQL. Интересная техника

В компании, где я работаю, мы используем деперсонализированную базу с Production-a. Ее суммарный объем на данный момент около 30 ГБ. Обфускация ruby скриптом занимала около 6 часов. Ускорение обработки можно добиться, если переписать это все в хранимую процедуру (stored procedure). Но у нас в проекте они запрещены… Увы и ах.

Тогда я задался вопросом: можно ли ускорить процесс по максимуму, деперсонализировать всю базу (или хотя бы полностью одну таблицу) используя только один оператор update? Проблема в том, что некоторые поля д.б. уникальными, а некоторые случайными значениями из списка.


Оказалось можно. Немного подумав, пришло решение с помощью пользовательских переменных, генератора псевдослучайных чисел и оператора case.

Ниже немного кода и пояснения:

Пусть есть таблица users с полями:
first_name
gender
last_name
address_1
address_2
home_phone
birthdate
ssn
password

После обфускации должно быть:
first_name одно из: женское — Patricia, Taylor, Susan, Lisa, Linda, Sandra, Carol, Debra, Teresa, Rebecca, Diana, Veronika, Helen, Alexandra, Svetlana, Elona, Marina, Mila, Olga, Vasilisa, Marta
мужское — David, John, Robert, Steven, William, Mark, Thomas, Michael, Richard, Kevin, Donald, Andrew, Ruslan, Eugene, Sergey, Alexandr, Yura, Ivan, Daniel
gender: без изменений
last_name: Johnson, Anderson, Reed, Erickson, Frank, Lucas, Jenkins, Watson, Morgan, Kim, Kovalinen, Konovalov, Tereshko, Urchik, Kuleshov, Kisliakov, Areshnik, Pekar, Matroskin, Gallagher
address_1: 123 Main Street
address_2: если в оригинале ничего нет, то после обфускации должен быть NULL, в противном случае адрес 123 Main Street
home_phone: если в оригинале ничего нет, то после обфускации должен быть NULL, в противном случае телефон [phone]
birthdate: оставить год рождения тем же, а дату и месяц изменить
ssn: случайное уникальное для каждой строки значение из девяти символов, начинающееся с '30'
password: NULL

Решение:

SET @rand = 276821; 
UPDATE users
  SET first_name=
        CASE WHEN gender='F' || gender='f' THEN ELT(FLOOR(1+RAND() * 21), 'Patricia', 'Taylor', 'Susan', 'Lisa', 'Linda', 'Sandra', 'Carol', 'Debra', 'Teresa', 'Rebecca', 'Diana', 'Veronika', 'Helen', 'Alexandra', 'Svetlana', 'Elona', 'Marina', 'Mila', 'Olga', 'Vasilisa', 'Marta')
                                           ELSE ELT(FLOOR(1+RAND() * 19), 'David', 'John', 'Robert', 'Steven', 'William', 'Mark', 'Thomas', 'Michael', 'Richard', 'Kevin', 'Donald', 'Andrew', 'Ruslan', 'Eugene', 'Sergey', 'Alexandr', 'Yura', 'Ivan', 'Daniel')
        END,
      last_name = ELT(FLOOR(1+RAND() * 20), 'Johnson', 'Anderson', 'Reed', 'Erickson', 'Frank', 'Lucas', 'Jenkins', 'Watson', 'Morgan', 'Kim', 'Kovalinen', 'Konovalov', 'Tereshko', 'Urchik', 'Kuleshov', 'Kisliakov', 'Areshnik', 'Pekar', 'Matroskin', 'Gallagher'),
      address_1 = '123 Main Street',
      address_2 =
        CASE WHEN address_2 is not null THEN 'Apt. 14'
                                        ELSE null
        END,
      home_phone =
        CASE WHEN home_phone is not null THEN '111-111-2222'
                                         ELSE null
        END,
      birthdate =
        CASE WHEN birthdate is not null THEN MAKEDATE(YEAR(birthdate),FLOOR(RAND()*365))
                                        ELSE null
        END,
      ssn =
        CASE WHEN ((@rand + 609673) % 1048576 <> 0) && ((@rand + 609673) % 1048576 <> 1000000)
               THEN CONCAT('30',LPAD(@rand:=(@rand + 609673) % 1048576,7,'0'))
             ELSE CONCAT('30',LPAD(@rand:=(@rand + 609673*2) % 1048576,7,'0'))
        END,
      password = null;

Как видно из кода, выбрать случайное значение из списка можно с помощью ELT(FLOOR(1+RAND() * 21),...), где FLOOR(1+RAND() * 21) выбор случайного значение в диапазоне от 1 до 21. ELT — выбирает соответствующую стоку с указанным индексом.

CASE помогает выбирать отдельно женские и мужские имена в зависимости от пола. Таких функций управления ходом выполнения, которые можно использовать в отдельном операторе четыре CASE, IF, IFNULL(), NULLIF().

Из того, что стоит еще упомянуть — это генератор случайного уникального значения. В качестве начального значения генератора было выбрано простое число (276821) и записано в пользовательскую переменную rand. Следующее значение устанавливается непосредственно в операторе CASE: rand:=( rand + 609673*2) % 1048576. Выражение CONCAT('30',LPAD( rand, 7, '0')) формирует окончательный вид значения rand.

Заключение:
Чего мы добились таким способом деперсонализируя базу?
1) Скорость выполнения сократилась с 6 часов до 4х минут.
2) Не используются хранимые процедуры.
3) Понятная (не сложная) логика работы и весь код собран в одном месте.