Есть ли способы оптимизировать большую таблицу с разреженными данными?
Суть следующая, есть таблица с 200 млн строк (char32, char32, char32, char64)
Первые три поля - uid, последний - текстовый id
Выборка по всем полям, длина ключа - 127
Суть в том, что выборка по этой таблице осуществляется через where exists по 13 тысячам строк.
Размер индекса с таким ключам сами понимаете какой. Никаких аналогов hstore, jsonb в mysql нет. Частичных индексов, секционирования тоже. Всю голову издумал как можно вывернуться.
Вячеслав Успенский, а теперь прикиньте, если бы вы 16-байтные идентификаторы хранили не в CHAR(32), который занимает 32 байта, а в BINARY(16), то смогли бы сэкономить по 3*16=48 байт на каждой записи. Плюс экономия на размере индекса. Косвенным бонусом будет более высокая скорость отработки запросов. Это замечание общего характера. Что касается, сути мне не понятна ваша проблема. Запрос долго отрабатывает или что?
Вячеслав Успенский, в 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 байта на символ?
1. Создайте индексы для каждого поля.
2. Оптимизируйте запрос так, чтобы подстроки искались последними и используйте limit при выборке данных.
И покажите пример запроса...
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, пробовал, ,будет еще хуже, поскольку в таблице связей есть дубли. Я думаю что проблема в очень большой длине ключа, а так же в том что мы заставляем mysql пересмотреть 13000*600 строк.
Это все бы переписать к чертовой матери естественно, но поскольку такой опции просто нет на данный момент, я прикидываю чем можно помочь пациенту. В том же PostgreSQL я бы уже конвертнул все строки в uuid, 64 символьное поле в enum, построил бы частичные индексы и полегчало бы существенно. Работа с binary(16) в mysql потребует переписывать все запросы
xmoonlight, limit 1 это через подзапрос? будет еще хуже. Exists это и есть аналог limit 1, условно говоря. Все эти варианты я уже рассматривал, как бы я ни старался - выигрыш незначительный. Этот запрос вообще шевелится только потому, что его limit 21 останавливает в большинстве случаев, если это не происходит то привет время выполнения от 8 до 60 секунд (в лучшем случае).
Вячеслав Успенский, нет. Без подзапросов. Только для одной таблицы сделать, чтобы получить нужную строку.
Можно же сделать однократную привязку конкретного юзера к строкам, чтобы обходиться без left_join?
xmoonlight, индекс? это очевидно. Кстати есть ссылочка где почитать касательно быстродействия индексов? Сама структура b-tree при составном индексе на это не так чтобы намекает, не первый раз мне вопрос такого порядка задают. Где то была статья у меня раньше где все доступно объяснялось, очень удобнокидать ее было, никак нагуглить немогу
Вячеслав Успенский, пример: ищем два совпадения: 1 7:
1 5
1 6
1 7
2 7
2 5
Начинать поиск нужно со 2-го столбца.
Вначале, получаем:
1 7
2 7
И уже здесь, ищем 1 в первом столбце.
Наоборот - будет медленнее.
xmoonlight, вот только b-tree это не плоский список, там это выглядит чуть иначе. И я спрашивал не для себя а чтобы линковать народу, вместо объяснений.
Если ключ содержится в корне, он найден. Иначе определяем интервал и идём к соответствующему потомку. Повторяем.
Основной недостаток В-деревьев состоит в отсутствии для них эффективных средств выборки данных (т.е. метода обхода дерева), упорядоченных по отличному от выбранного ключа.
Вячеслав Успенский, проще сделать миграцию записей в новую структуру.
А вот уже её - сделать по-нормальному.
И потом, можно даже, триггер для конвертирования в текущей сделать при инсерте.
Т.е. сразу будет новая структура наполняться без изменения кода приложухи.
xmoonlight, а толку, если все селекты переписывать. Был бы пг можно было бы преобразовать все char(32) в uuid, char(64) в enum, развернуть индексы + сделать их частичными на критичных запросах. А уж если радикально менять то вместо таблицы подсунуть вьюшку и обложить ее instead of триггерами. Да даже денормализовать через hstore или jsonb
xmoonlight, ну сам ради развлечения опенсорсное решение, да еще допиленное, я точно переписывать не буду. А что мешает фирме, которая этим пользуется... Вы знаете, иногда для осознания нужны годы и годы ,чтобы решиться.
А по теме: я к тому же не могу придумать нормальной схемы данных под индивидуальный доступ к объектам в системе основанной на uuid ключах в mysql.
xmoonlight, ну по идее и базу бы сменить, я сам много лет работал с mysql, наразвлекался с ее оптимизатором по самое не хочу, но после oracle,postgresql я mysql за базу уже не считаю.
Вячеслав Успенский, проблема в том, что у тебя знаний меньше, чем понтов. А результата - нет.
Пересмотри оптимизацию со стороны связей всех полей БД и создай промежуточные таблицы при необходимости.
Bavashi, такое решение почти всегда бестолковое, за очень редким случаем, когда у оптимизатора таки встают на место мозги из-за него.
ЗЫ благодарю, я в курсе, я тут довольно редко появляюсь, но регулярно встречаю этого товарища. Собственно столько комментов, а нового я для себя почерпнул ровно ноль.
Вячеслав Успенский, разве не видно, что этот человек умышленно везде пишет про меня гадости?
Ладно, видимо, он поможет лучше меня.
Карма хабра - у всех отрицательная, кроме своих.
Можете зайти на хабр и убедиться.
Здесь - рейтинг свой. Нажмите на ник или смотрите топ10.
Всего доброго.
