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

Question

[Вячеслав Успенский]

Суть следующая, есть таблица с 200 млн строк (char32, char32, char32, char64)
Первые три поля - uid, последний - текстовый id
Выборка по всем полям, длина ключа - 127

Суть в том, что выборка по этой таблице осуществляется через where exists по 13 тысячам строк.
Размер индекса с таким ключам сами понимаете какой. Никаких аналогов hstore, jsonb в mysql нет. Частичных индексов, секционирования тоже. Всю голову издумал как можно вывернуться.

Comments

[Иван Мельников]

Правильно ли я понял, что в полях CHAR(32) вы храните числа?

[Вячеслав Успенский]

Иван Мельников, UID, 16битный идентификатор. Не совсем я, я то сам в ужасе.

[Иван Мельников]

Вячеслав Успенский, а теперь прикиньте, если бы вы 16-байтные идентификаторы хранили не в CHAR(32), который занимает 32 байта, а в BINARY(16), то смогли бы сэкономить по 3*16=48 байт на каждой записи. Плюс экономия на размере индекса. Косвенным бонусом будет более высокая скорость отработки запросов. Это замечание общего характера. Что касается, сути мне не понятна ваша проблема. Запрос долго отрабатывает или что?

[Вячеслав Успенский]

Иван Мельников, уж поверьте я в курсе, 16 байт на каждом из трех полей плюс 63 байта на четвертом (меняем на enum), но писал сей ужас не я)

Да, работает очень долго, на некоторых конфигурациях - кошмарно долго.

ЗЫ кстати в utf8 базе разве 32 байта на char(32)?

[Иван Мельников]

Вячеслав Успенский, в utf8 русские буквы занимают 2 байта, а ASCII-символы -- 1 байт. Поэтому первые 3 поля весят по 32 байта, а вот 4-е поле может весить и все 128 байт, в зависимости от кол-ва русских букв.
Скиньте проблемный запрс.

[Вячеслав Успенский]

select * 
from tasks_table tt
left join users_table as ut
  on ut.user_uid=tt.user_uid
left join user_groups_table as gt
  on gt.group_uid=ut.group_uid
where tt.responsible_id=? or
  exists (select 1
             from my_dumb_table as dt
             where dt.record_uid=tt.record_uid and
                        dt.group_uid=gt.group_uid and 
                        dt.dumb_text = 'some_dumb_text')

Только в оригинале джоины под экзистом.

Сколько весят символы я знаю, но разве mysql выделяет не по 2 байта на символ?

Answer 1

[xmoonlight]

1. Создайте индексы для каждого поля.
2. Оптимизируйте запрос так, чтобы подстроки искались последними и используйте limit при выборке данных.
И покажите пример запроса...

Comments

[Вячеслав Успенский]

1. А индексы есть
2. Нету там никаких подстрок

select * 
from tasks_table tt
left join users_table as ut
  on ut.user_uid=tt.user_uid
left join user_groups_table as gt
  on gt.group_uid=ut.group_uid
where tt.responsible_id=? or
  exists (select 1
             from my_dumb_table as dt
             where dt.record_uid=tt.record_uid and
                        dt.group_uid=gt.group_uid and 
                        dt.dumb_text = 'some_dumb_text')

Примерно так

[Вячеслав Успенский]

На самом деле там условие чуть более сложное но смысл именно такой.
Explain скажет 13к строк под общую выборку, по 1 строке в каждом джоине, 600 строк в materialized подзапросе с длиной ключа в 127

[xmoonlight]

Вячеслав Успенский, думаю, что скобки в exists нужно превратить в доп.колонку для tasks_table.

[Вячеслав Успенский]

xmoonlight, пробовал, ,будет еще хуже, поскольку в таблице связей есть дубли. Я думаю что проблема в очень большой длине ключа, а так же в том что мы заставляем mysql пересмотреть 13000*600 строк.

Это все бы переписать к чертовой матери естественно, но поскольку такой опции просто нет на данный момент, я прикидываю чем можно помочь пациенту. В том же PostgreSQL я бы уже конвертнул все строки в uuid, 64 символьное поле в enum, построил бы частичные индексы и полегчало бы существенно. Работа с binary(16) в mysql потребует переписывать все запросы

[xmoonlight]

Вячеслав Успенский, искать, начиная с наиболее уникальных данных без пересечений и с LIMIT 1 - не вариант?

[Вячеслав Успенский]

xmoonlight, limit 1 это через подзапрос? будет еще хуже. Exists это и есть аналог limit 1, условно говоря. Все эти варианты я уже рассматривал, как бы я ни старался - выигрыш незначительный. Этот запрос вообще шевелится только потому, что его limit 21 останавливает в большинстве случаев, если это не происходит то привет время выполнения от 8 до 60 секунд (в лучшем случае).

[xmoonlight]

Вячеслав Успенский, нет. Без подзапросов. Только для одной таблицы сделать, чтобы получить нужную строку.
Можно же сделать однократную привязку конкретного юзера к строкам, чтобы обходиться без left_join?

[Вячеслав Успенский]

Лентюй, разве?

План запроса показать могу, но только без имен таблиц и индексов.
тынц

[Вячеслав Успенский]

Лентюй, все 3 материализованных таблицы это как раз блок exists, джион по группам на самом деле там, запамятовал

[Вячеслав Успенский]

xmoonlight, каким образом?

[xmoonlight]

Вячеслав Успенский, создать доп.колонки рядом и делать селект начиная с пользователя, чтобы сразу уменьшить объём результата для "водопадной" выборки:

select ... from .. t1, (select ... from ...) as t2 where t1.col=t2.col

[Вячеслав Успенский]

xmoonlight, надо попробовать, обычно такие конструкции очень далеки от оптимальных. Хотя очень интересно что сделает оптимизатор mysql в этом случае

[xmoonlight]

Вячеслав Успенский, порядок: от самого уникального к менее уникальному!
Не забудьте!

Отпишитесь потом: что получилось...

[Вячеслав Успенский]

xmoonlight, индекс? это очевидно. Кстати есть ссылочка где почитать касательно быстродействия индексов? Сама структура b-tree при составном индексе на это не так чтобы намекает, не первый раз мне вопрос такого порядка задают. Где то была статья у меня раньше где все доступно объяснялось, очень удобнокидать ее было, никак нагуглить немогу

[xmoonlight]

Вячеслав Успенский, пример: ищем два совпадения: 1 7:
1 5
1 6
1 7
2 7
2 5
Начинать поиск нужно со 2-го столбца.
Вначале, получаем:
1 7
2 7
И уже здесь, ищем 1 в первом столбце.
Наоборот - будет медленнее.

[Вячеслав Успенский]

xmoonlight, вот только b-tree это не плоский список, там это выглядит чуть иначе. И я спрашивал не для себя а чтобы линковать народу, вместо объяснений.

[xmoonlight]

Вячеслав Успенский,

Если ключ содержится в корне, он найден. Иначе определяем интервал и идём к соответствующему потомку. Повторяем.

Основной недостаток В-деревьев состоит в отсутствии для них эффективных средств выборки данных (т.е. метода обхода дерева), упорядоченных по отличному от выбранного ключа.

Поэтому я сразу написал пример.

[Вячеслав Успенский]

xmoonlight, кстати по идее для uid btree подходит ну совсем хреново. Надо глянуть не завезли ли в mysql чего более подходящего

[xmoonlight]

Вячеслав Успенский, проще сделать миграцию записей в новую структуру.
А вот уже её - сделать по-нормальному.
И потом, можно даже, триггер для конвертирования в текущей сделать при инсерте.
Т.е. сразу будет новая структура наполняться без изменения кода приложухи.

[Вячеслав Успенский]

xmoonlight, а толку, если все селекты переписывать. Был бы пг можно было бы преобразовать все char(32) в uuid, char(64) в enum, развернуть индексы + сделать их частичными на критичных запросах. А уж если радикально менять то вместо таблицы подсунуть вьюшку и обложить ее instead of триггерами. Да даже денормализовать через hstore или jsonb

[xmoonlight]

Вячеслав Успенский, а нормально сделать, переписав код приложения и создав новую нормальную структуру бд, что мешает?

[Вячеслав Успенский]

xmoonlight, ну сам ради развлечения опенсорсное решение, да еще допиленное, я точно переписывать не буду. А что мешает фирме, которая этим пользуется... Вы знаете, иногда для осознания нужны годы и годы ,чтобы решиться.

А по теме: я к тому же не могу придумать нормальной схемы данных под индивидуальный доступ к объектам в системе основанной на uuid ключах в mysql.

[Вячеслав Успенский]

xmoonlight, ну по идее и базу бы сменить, я сам много лет работал с mysql, наразвлекался с ее оптимизатором по самое не хочу, но после oracle,postgresql я mysql за базу уже не считаю.

[xmoonlight]

Вячеслав Успенский,

А по теме: я к тому же не могу придумать нормальной схемы данных под индивидуальный доступ к объектам в системе основанной на uuid ключах в mysql.

Это обсуждение не для тостера. Скорее - это фриланс с teamviewer и доступом к этой БД и к приложению.

[xmoonlight]

Вячеслав Успенский, проблема в том, что у тебя знаний меньше, чем понтов. А результата - нет.
Пересмотри оптимизацию со стороны связей всех полей БД и создай промежуточные таблицы при необходимости.

[Вячеслав Успенский]

xmoonlight, вы - точно ничем помочь не сможете даже если сильно захотите, никаких знаний я от вас не увидел, все на уровне шаманства.

UPD ой еще и хамим, уважаемый скройтесь нахрен с этой платформы пожалуйста.

[Вячеслав Успенский]

Bavashi, такое решение почти всегда бестолковое, за очень редким случаем, когда у оптимизатора таки встают на место мозги из-за него.

ЗЫ благодарю, я в курсе, я тут довольно редко появляюсь, но регулярно встречаю этого товарища. Собственно столько комментов, а нового я для себя почерпнул ровно ноль.

[xmoonlight]

Вячеслав Успенский, разве не видно, что этот человек умышленно везде пишет про меня гадости?
Ладно, видимо, он поможет лучше меня.
Карма хабра - у всех отрицательная, кроме своих.
Можете зайти на хабр и убедиться.
Здесь - рейтинг свой. Нажмите на ник или смотрите топ10.
Всего доброго.