Какой префикс использовать при индексировании строкового столбца SecureRandom

Это просто случайное число, верно? Не шифрование.

Использовать ли нет префикс; хотя это немного уменьшит размер индекса, во многих случаях это сделает использование индекса недействительным. Правда, 22 байта длиннее, чем 8-символьная строка или 4-байтовая INT. Но обратная сторона отказа от использования индекса еще хуже.

Значение по умолчанию 16 (22) достаточно, чтобы сделать случайную строку достаточно уникальной, чтобы избежать случайных столкновений.

Не говорите VARCHAR(255), если максимальное значение равно 22. Говорите CHAR(22), если длина фиксированная, или VARCHAR(22), если вы разрешаете пользователю выбирать длину до 16.

Скажите CHARACTER SET ascii COLLATE ascii_bin для столбца. Это позволит избежать (1) накладных расходов utf8 и (2) ошибки свертывания регистра.

Если вы собираетесь иметь индекс для миллиарда таких элементов, то возникнут серьезные проблемы с производительностью, как обсуждалось в здесь (хотя и в другом контексте). Миллион строк, вероятно, не проблема - это зависит от того, когда индекс станет больше, чем может быть кэшировано в ОЗУ в пуле буферов.

(Если я правильно помню формулу, для 8 символов, как вы описываете, будет один шанс на 300 КБ, что индекс с 300 000 записей будет включать дубликат. Но это не проблема.)

Результаты

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

В результате этого эксперимента произошли два изменения:

1. Я уменьшил размер столбца с 255 до 22 (также предложено @Rick James). Подробнее см. в вопросе, почему данные никогда не превышают 22 символов.
2. Я добавил индекс столбца с префиксом из 8 символов. Кодировка — utf8mb4, поэтому размер ключа не будет прямо пропорционален количеству символов в префиксе.

Сведения об эксперименте

Я протестировал префиксы длиной 1, 2, 4, 8 и 16 символов и использовал EXPLAIN, чтобы увидеть, что сделает поиск в столбце. Я также тестировал без индекса в качестве базовой линии. Мой эксперимент был основан на наборе из 100 тыс. записей. Я мог бы протестировать большее количество записей, но для моих целей не было необходимости в большей точности.

Вот некоторые моменты эксперимента:

• Без индекса запрос в столбце SecureRandom выполняет сканирование таблицы.
• С префиксом в 1 символ MySQL должен сканировать примерно 3200 записей по 100 тыс.
• С двухсимвольным префиксом MySQL должен просмотреть 79 записей.
• С префиксом из 4 символов MySQL сразу получает точную запись, не выполняя поиск в таблице.

Помимо 4-значного префикса, с набором данных 100 000 и исходя из характеристик SecureRandom.urlsafe_base64, я не могу быть более точным. Основываясь на текущем размере таблицы в производственной среде и приблизительном представлении о темпах ее роста, мы решили, что префикс из 8 символов сослужит нам хорошую службу в будущем, экономя при этом дисковое пространство и память.

Альтернативы

Благодаря этому исследованию я также узнал о функции избирательности индексов в MySQL. Это позволило бы, используя один и тот же выборочный набор из 100 000 записей, протестировать количество записей, извлеченных для каждой длины префикса, без необходимости выполнять миграции для добавления/удаления различных индексов перед каждым тестом. Например, чтобы проверить селективность 4-символьного префикса:

SELECT count(*) AS count, LEFT(uuid_column, 4) AS prefix
  FROM string_prefix_test GROUP BY count DESC LIMIT 10;

Более подробную информацию о селективности индекса можно найти в выпуске Высокая производительность MySQL 2012 года на стр. 160.

Будущая работа

Далее я собираюсь изучить префиксы для индексов столбцов varchar с данными, которые не обладают характеристиками уникальности SecureRandom.urlsafe_base64. Это будет сложнее только потому, что сложнее получить репрезентативный набор данных.