MySQL оптимизация размера (INDEX,ENUM)?

Question

[PigData]

Задача - максимально оптимизировать размер таблицы (скорость записи/чтения вторична) myisam, или innodb с повторяющимися данными без выноса данных в другие таблицы.

Допустим это статистика посещений и разрешения экрана мы можем перечислить в enum (+значение "другие"), но как быть например с IP которые заранее не известны, могут дублироваться десятки тысяч раз и явно выйдут за пределы лимитов поля enum. Какой подход тут будет наиболее эффективен - формат таблицы (myisam/innodb), вынос/нет в индексы, формат строк, сжатие?

Answer 1

[Dmitry Bay]

А вы не задумывались что ерунду придумываете c ENUM? сколько размеров экрана существует? только самых распространенных я сразу размеров 20 вспомню. А их может быть очень много.

Про ENUM очень просто -
https://dba.stackexchange.com/questions/6962/advan...

(myisam/innodb), - тоже давно уже решено - почти всегда нужно использовать innodb. так же статей много по этому поводу.

IP адрес можно в intege хранить IP->INTEGER есть функции, но учтите, что IP6 еще бывают.
Размер экрана - Varchar(9), чтобы записывать по маске 9999:9999

Индексы вам не нужны, по крайней мере из текущей задачи.

Comments

[PigData]

Таблица "статистика посещений" приведена для примера на самом деле там поля не разрешения/ip а varchar(255) с соответствующим размером данных. Интересует общий подход. Статьи по организации хранения/скорости работы myisam/innodb я читал, но некоторая информация 3-5 летней давности вероятно не так актуальна сейчас.

1. Enum - с помощью него я уменьшал таблицы раз в 10, причем например архивные в которые данные уже не добавляются, да и чтение происходит несколько раз в год. Ерунда?

2. У myisam есть (были) свои преимущества, но некоторые, как понимаю, в данный момент не так актуальны. Например раньше myisam была существенно быстрее innodb на чтение, но сейчас вроде innodb существенно сократила отставание.

3. Про индексы я спросил так как слышал что данные можно хранить только в индексах (innodb?) и это получается по сути безлимитный enum без предопределенных значений. Вот этот вопрос меня особо интересует.

[Dmitry Bay]

PigData, Читайте больше. литературы и статей полно.

1) Если данные повторяются сравнительно. то ваш enum ~ аналогичен дополнительной таблице с внешними ключами. Посмотрите ссылку в пункте 3. Ну и заодно прочтите - https://habr.com/ru/post/254773/ .

2) так читайте больше книг. Вот вам просто факт.
https://ruhighload.com/%D0%A1%D1%80%D0%B0%D0%B2%D0...

3) на бред смахивает. Не путайте индексы и вторичные ключи на соседние таблицы.
https://stackoverflow.com/questions/46566426/index... в ответе на вопрос обратите внимание на комментарии. там как раз упоминается обоснованность индексов в enum полях.

[PigData]

Dmitry Bay, Вероятно на любой вопрос здесь можно ответить "читайте больше литературы"? Зачем тогда этот сайт? Мне кажется чтобы каждый раз не изобретать велосипед.

1. Про вынос во внешние таблицы я знаю и сказал об этом, но если ТЗ оптимизировать данные без дополнительных таблиц (даже несколько обоснованное) буду делать так.

2. Нужно перекидывать базы между несколькими машинами, myisam тупо копируются файлы таблицы, с innodb это возможно? И это только один из кейсов.

3. Про индексы на enum поля я не спрашивал, интересует использования индекса подобно enum для хранения данных.

Информации в сети море, вопрос в ее актуальности. Например относительно недавнее появление в MySQL поля JSON позволило, в некоторых случаях, кардинально пересмотреть структуру баз и работу с ними.

[Dmitry Bay]

PigData,

"читайте больше литературы"? Зачем тогда этот сайт?

Потому как вы упомянули про кол-во экранов и кол-во IP в ENUM, у вас тут вопрос либо постебаться , либо вы действительно не ознакомились с вопросом хотя бы кратко, чтобы понимать, что вам тут не нужен ENUM.

2) за много лет практики ни разу не сталкивался с этим кейсом.

3) Там же в ссылке есть описание как хранятся данные в ENUM. Грубо говоря, если кол-во вариаций стремится к ~9...9 , то проще смотреть на другие варианты хранения данных.

А еще к примеру есть Redis, в котором ввести записи логов намного проще и выгоднее.

[PigData]

Dmitry Bay, пример со статистикой возможно не очень удачный, думал просто часто используемый. Попробую привести более реальный кейс.

Дают архивную таблицу myisam фирмы продающей 5 товаров, в 5 городов, 5 способов доставки и 5 способов оплаты. Все эти поля скажем varchar(25) и таблица с десятками-сотнями миллионов строк весит достаточно много. Задача оптимизировать размер таблицы без смены типа базы данных, скриптов с ней работающих, дополнительных таблиц - максимально быстро/дешево/безопасно. Загоняем все вышеозвученные поля в enum и таблица худеет в несколько раз. Но если мы хотим загнать в enum города/улицы доставки заказов упираемся в лимит 64 кб .frm файла. Ну и конечно минус enum предопределенные значения (для не архивных таблиц). Есть решение по уменьшению размера без дополнительных таблиц? Скорость выборки абсолютно не важна.

[Dmitry Bay]

Есть решение по уменьшению размера без дополнительных таблиц?

Оторвать руки человеку, который спроектировал это? То что вы говорите, для меня в 2005 году страшно звучало. А на дворе уже 2019 год.

[Dmitry Bay]

PigData, а если без шуток, то зависит конечно от определенной задачи.

Я бы смотрел в сторону функций и виртуальных таблиц в mysql.
Т.е. изначальную переименовываем, делаем нормализацию.
А виртуальная через функции забирает нам необходимую информацию .

[Dmitry Bay]

PigData, еще решение - если база архивная и к ней не нужен доступ - экспортируете в sql, сжимаете архиватором.

[PigData]

Dmitry Bay,

Оторвать руки человеку, который спроектировал это? То что вы говорите, для меня в 2005 году страшно звучало. А на дворе уже 2019 год.

Есть и обратная сторона медали - мелкие базы без перспективы роста в которых нагородили десятки таблиц и сам черт там ногу сломит. В 2005 году может это было и нормально, но в 2019 есть например тот же тип JSON который может заменить несколько дополнительных таблиц + выросли серверные мощности (подешевели), выросли скорости интернета и возможности клиентских машин, например можно один раз отдать большой объем данных клиенту в виде json и производить такие операции как сортировка/фильтрация/поиск у него в браузере сократив количество sql запросов в десятки раз.

Answer 2

[ivankomolin]

Для оптимизации данных по ip адресам, их необходимо хранить в поле типа VARBINARY

VARBINARY(16) для IPv6
VARBINARY(4) для IPv4

Пример преобразований

mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'FDFE0000000000005A55CAFFFEFA9089'
mysql> SELECT HEX(INET6_ATON('10.0.5.9'));
        -> '0A000509'

Точно не помню, но кажется появились эти функции только в mysql 5.6