Селекты из таблицы на 5 млн строк. Если разбить её на 100 таблиц — будет выигрыш в производительности?

Question

[Александр]

Приветствую, коллеги!

Посоветуйте, есть таблица с товарами (InnoDB на 5 млн строк). Индексы добавлены. Но на сервере всего 4Гб памяти.

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

Есть ли смысл раскидать товары на 50-100 таблиц (для каждой категории свою таблицу)? В итоге получатся таблицы по 30-100 тыс строк, не больше. Таким образом можно радикально улучшить производительность?

Или, все же самый правильный вариант - это добавить памяти гигабайт до 16 и наслаждаться результатом? :)

Спасибо!

Comments

[RidgeA]

а если надо выбрать из нескольких категорий?

Искусственно разбивать таблицу, ИМХО, решение так себе.

В mysql есть Partitioning - может это поможет...

[Arris]

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

[landergate]

Александр
Не забудьте отметить решением тот ответ, который смог вам помочь. Это мотивирует участников продолжать отвечать на ваши вопросы. =)

Answer 1

[ThunderCat]

Поддерживаю longclaps, вы что, алиекспресс админите? откуда в таблице товаров 5М строк, вы что, спички поштучно учитываете? Это номер раз, второе - если фильтры такие забубенные - делайте в 2-3 запроса через бэк, что у вас там, пых, нода, джанга, ограничивайте по уму количество товаров в первой выборке по самому быстрому индексу и самой ограничивающей выборке, например все товары выбранной категории, далее филтр только на результат выборки по типу id in(1,2,3,4). И все же скажите, не мучьте, ОТКУДА СТОЛЬКО ТОВАРОВ БЛИН, я ж не засну теперь...

Comments

[Arris]

ОТКУДА СТОЛЬКО ТОВАРОВ БЛИН

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

[Arris]

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

[Stalker_RED]

Если мне не изменяет память, в базе данных одной небольшой сети по торговле бытовой техникой (типа мвидео, но помельче масштабом, десятка три магазинов всего) было несколько десятков тысяч видов товаров. Не едениц товара, а именно записей типа "Hynix 1 GB DDR2 800 MHz (HYMP112U64CP8-S6)" плюс почти к каждому несколько строк описания, несколько картинок и табличка с характеристиками, по которым поиск и фильтры работают.
Что там творилось в 1С с товарами я и представить боюсь. И 5 млн записей - это не много для базы данных. Но уже надо понимать как оптимизировать запросы, конечно же.

[ThunderCat]

Arris: 5к товаров, пусть по 10 атрибутов на товар, в целом 50к, вообще мизер, на 2 порядка меньше чем у ТК. Даже если товаров 50К все еще на порядок выше заявленной цифры. Вывод - или что-то здесь не так, или мы действительно советы чуваку с таобао даем.
Stalker_RED:

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

Это да, и как мы видим вот прям тут - еще и таблица должна быть в приличном состоянии, индексы, все дела, мусора поменьше, поменьше магии и побольше здравого смысла. Что при разгребании чужого кода часто упирается в легаси. Так что все еще жду ответа - что там наколбасено на 5 лимонов, и можно ли это все покоцать на пару порядков.

[Fortop]

ThunderCat: каждый товар с разным набором атрибутов может учитываться под отдельным артикулом.
Так что какая-то из таблиц действительно может быть миллионной.

[ThunderCat]

Fortop: йес, это не исключает изначальной мысли что 90% заполнения - мусор. И это реально должно быть МНОГО товаров и ДОФИГА атрибутов. учитывая что часть атрибутов все же разнесена (видимо) в разные таблицы:

У меня сейчас классический нормализованый вариант из трех таблиц: item, brand, item_brand. И такие наборы для каждого фильтра - цвет, бренд... Как эти связи можно по другому хранить?

[Fortop]

ThunderCat:
Возможно.
Вряд ли у автора Wallmart.

Но причины тормозов скорее всего не в мусоре, а в индексах или самих запросах. 5млн для 4Гб вполне терпимо.
И 30 секунд на запрос... Это странно.

[Dark_Dante]

Это еще что, у меня тут на серваке одна база есть, 147 млн записей - номенклатура автозапчастей

[ThunderCat]

Dark_Dante: Вроде TecDoc имеет около 50М кодов запчастей, это наиболее известный и объемный (ИМХО) каталог. Но там своя база, не "влоб" прикрученная.

[Dark_Dante]

ThunderCat: Кодов то да, а там же еще таблицы соотношений кодов к группам, групп к моделям и вот это вот все.

[ThunderCat]

Dark_Dante: пропустил на автомате база -> таблица )

[Arris]

ThunderCat: 10 атрибутов одного типа, 10 атрибутов другого, 20 атрибутов третьего. И они перемножаются. Впрочем там чертте что может быть понакручено.

[PQR]

Приходится делать много запросов к ней с джойнами других больших таблиц

Проблема в этих JOIN'ах, покажите нам EXPLAIN запроса

[Vilintritenmert]

Табличка на 20 кк записей, загрузка страницы до 300 мс.

Очень помогли, нормализация, композитние ключики.

Answer 2

[laxikodeje]

5 миллионов записей для современных СУБД - это ерунда.
Было бы 5 миллиардов - можно было бы так заморачиваться, как вы хотите.

Дело в том, что вы не правильно организовали хранение фильтров.
Это действительно простой метод, но SQL в нормализованной форме не подходит для фильтров товаров по категориям товаров и интернет-магазине.
Нужна или ДЕНОРМАЛИЗОВАННАЯ форма с дублированием.
Или вообще БД другого типа. Я лично под такую задачу фильтрации предпочитаю Tarantool, но, полагаю, годится и Solr

Или, все же самый правильный вариант - это добавить памяти гигабайт до 16 и наслаждаться результатом? :)

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

Comments

[Александр]

Можете чуть подробнее пояснить про денормализованную форму для фильтров? У меня сейчас классический нормализованый вариант из трех таблиц: item, brand, item_brand. И такие наборы для каждого фильтра - цвет, бренд... Как эти связи можно по другому хранить?

[laxikodeje]

Александр: меньше таблиц - это и будет денормализованная в данном случае.

самый крайний вариант выглядит так:
1 таблица с товаром

Название товара|Значение фильтра № 1|Значение фильтра №2|Значение фильтра №3

Или в вашем случае так

Название товара|Название бренда

Или так
Название товара|ИД бренда
вторая таблица
ИД бренда|Название бренда

В вашем случае непонятно зачем нужно 3 таблицы
item, brand, item_brand
Эти 3 таблицы предполагают что один товар может относится к нескольким брендам одновременно. Зачем это?

[Макс Жук]

А что если вместо денормализации использовать Представления?

[laxikodeje]

Макс Жук: зачем?

для производительности?
тогда нужно "материализованные представления".
но что-то сомневаюсь, что овчинка выделки стоит.

Answer 3

[longclaps]

Что там делает таблица на 5млн строк? Отнести нахер в архивную таблицу всё, что 10 лет как мхом позарасло - это осмысленое действие. Уверен, 90% говна улетит по самым мягким критериям.
100 актуальных таблиц - это гораздо больший ад чем то, что есть.

Answer 4

[Дмитрий Дарт]

Нет, выигрыша вы не получите, потому что в MySQL есть ещё время на открытие таблицы некоторое, и там доп. логику придётся писать с условиями из какой таблицы выбирать. Надо денормализовать данные хотя бы немного, сделать EXPLAIN, посмотреть, бывает MySQL сходит с ума и не использует правильные индексы.
5 млн. это не очень много, уверен всё можно привести в норму.
P.S. Сам работал с таблицей 3,5 млн. записей на сервере с 2 Гб памяти, всё отлично, дольше 0,1 секунды запросов нет, хотя и это многовато имхо.

Answer 5

[landergate]

Прироста не будет.
Но давайте посмотрим на проблему под другим углом:

5кк строк - это ничтожное кол-во. Если у вас происходят селекты по 30 секунд, значит либо у вас не выставлены правильные индексы на те колонки, которые вы селектите, либо вы совершаете LIKE, начинающиеся с %. Индексы не используются при запросах LIKE %...%. Только при LIKE ...%

Ещё одна возможная причина - это хранилище. Посмотрите, не упирается ли всё в этот момент в диски. Если окажется, что в момент тяжёлых запросов у вас высокий iowait, попробуйте мигрировать на площадку с SSD.

Answer 6

[Василий Назаров]

1. Возможно, тут более правильно использовать noSQL.
2. Даже если SQL (= РСУБД) - нужно избавляться от JOIN-ов.
Но! ! Уровень JOIN-ов =1 может оказаться вполне приемлем, если вы
- при фильтрации на фронте сразу выделите id связанных сущностей (бренд, цвет, и т.п.)
- правильно настроите индексы, и сделаете правильные запросы
Например, при хотя бы одном активном фильтре делаете 1 SELECT, а потому уже в PHP (или что там у вас) отфильтровываете по другим параметрам.
И да, надеюсь, тексты и тем более картинки (не в БД же они?) у вас не выбираются этим же запросом?
Если вдруг выбираются (типа SELECT * ..), замените "*" на "поле1, поле2" (только нужные), может оказаться приятным сюрпризом

Comments

[Александр]

У меня в основной таблице товаров около 25 полей, и 10 из них - это TEXT (в них лежат данные размером до 1000 символов). Как вы порекомендуете поступить? Изменить эти поля на VARCHAR или вообще вынести в отдельную таблицу?

Спасибо за ответ!

[ThunderCat]

Александр: Я понимаю что это наверно "страшная коммерческая тайна", но вы как Ганибал Лектор - присылаете нам информацию по кусочкам, то ухо, то палец. "А можно всех посмотреть?". Задача интересная, но если вы ее поподробнее опишете, то и ответ быстрее получится сформировать.

Answer 7

[Astrohas]

Но на сервере всего 4Гб памяти.

Тоже работал с АНАЛогичным сервером,и базой в 90гб!
Как вы будете иметь доступ к таблицам категорий? Обычно франкейнштейномеркеры делают вспомогательную таблицу типа "category - category_tabe_name". Но учтите что для изъятия любого товара из базы, придется делать минимум два запроса.
Также нужно учесть фильтры по нескольким категориям, и для например 2 категорий будут два запроса для запрашивания таблиц, и два для запрашивания информации из таблиц + еще один для результатирования.
Гораздо проще докупить памяти, поднастроит кешировоание, добавить дополнительные индексы и всякую всячину.
----
и если вы все еще хотите делить по таблицам советую использовать стандартные инструмены мускула вроде "partitioning" который особенно полезен, и на хабре есть мануальчик по этому https://habrahabr.ru/post/66151/

Comments

[laxikodeje]

Тоже работал у садомазохистов, с АНАЛогичным сервером,и базой в 90гб!

Зачем ругаете тех, кто вам платил?

Все дело в ВАШЕ умении.

У меня БД 2 Т на сервере 8 Г.
НИ ОДИН из запросов не выполняется дольше 3 секунд.

[chupasaurus]

laxikodeje: т.е. по вашему мнению, факт оплаты труда нивелирует условия труда.

[Arris]

chupasaurus: ну так вы же у них работали. Если все так плохо было - зачем работали? И почему не сделали лучше?

[Astrohas]

laxikodeje:

Все дело в ВАШЕ умении.

есть вещи которых можно оптимизировать. Но в моем случае несколько сервисов использовали эту базу данных, и реструктуризация бд, привлекла бы за собой изменения исходного кода всех этих сервисов.

Нет со скоростью у нас проблем вроде не было, ибо самая большая таблица у нас была максимум на 800к строк. Проблема была в полнейшей отсутствии нормализации и четкой структуризации. Масло в огонь добавляли наименования полей таблиц, например в одном join-е на 3 таблиц где ключевое поле "teacher_id" три вида этого выражения - "id_prep" , "teacher_id", "id_teacher". Тоже насчет факультетов, "faculty_id", "id_fakultet", "fakultet_id" .... и это все вершина айсберга.

Зачем ругаете тех, кто вам платил?

Работали бы вы с такой базой, сами бы превратились бы в серийного убийцу, с жаждой убить всех кто проектировал такую базу.

[Astrohas]

Arris:

ну так вы же у них работали. Если все так плохо было - зачем работали? И почему не сделали лучше?

Не в моих обязанностях было это все администрировать. Для сервисов над которыми я работаю был отдельный сервер с постгресом, индексами, кешированием и прочими блекджеками. И разгребать мусор той базы не хотелось

[Arris]

Хасан Истамкулов: "не хотелось". А ругать хочется. Молодца.

[Astrohas]

Arris:

"не хотелось". А ругать хочется. Молодца.

разгребание мусора не входило под мою ответственность.

[Илья Белобородов]

стандартные инструмены мускула вроде "partitioning"

вот это поворот. не знал о такой фиче

[laxikodeje]

Хасан Истамкулов:

Проблема была в полнейшей отсутствии нормализации

Это не является недостатком. Так же как и нормализация не является достоинством.

Разные бывают ситуации.

В данной конкретной задаче, поднятой топикстартером - как раз нужна ИМЕННО денормализация.

[laxikodeje]

chupasaurus:

т.е. по вашему мнению, факт оплаты труда нивелирует условия труда

Есть исполнитель.
Есть заказчик.

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

После чего исполнитель обвиняет в косячной системе заказчика, который вовсе не является специалистом в деле ради которого он и нанял профи-исполнителя.

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

[Astrohas]

laxikodeje: c той базой работало 8 специалистов, и 12 сервисов. Rebase базы, и апгрейд сервисов под новую базу обошелся бы в сотни человеко-часов. Для программистов это дело наверное дзена, но для работодателя это все равно что пинание балды. У всех было отвращение работать с такой базой.
Насчет обвинения работодателя. Я его не обвиняю. Но не я делал базу, и не мне было ее поддерживать. Но кое-что на что у меня нашлось время я сделал.
Да прежние таблицы были раньше разделены по 1500 (всего-то) записей. Первым же делом они были объединени в одну таблицу и сегментированы через partitioning. Далее я потратил много часов для перенастройки mysql (inno_db_per_tabe и тд). Далее я потратил время на переселения всех "программистов" с samba на ssh и git.

[chupasaurus]

laxikodeje: Это если исполнитель нанят по контракту на выполнение определённых работ, а не трудовому договору, и заказчик заинтересован в их результате и оценивает риски.
Вы без уточнения контекста заявиили, что ругать тех, кто платил, не надо потому что.
Arris: я не автор данного ответа.

[Arris]

chupasaurus: извините ,промахнулся, видимо.

Answer 8

[chupasaurus]

Clickhouse. Б-гоподобные индексы, column-based, вот это вот всё.

Answer 9

[Mountaineer]

Если вкратце, то у вас три варианта:
1. оптимизировать запрос
2. вертикальноє масштабирование БД (более мощный сервер)
3. горизонтальное масштабирование БД (шардинг)

То, что вы хотите - ето антипаттерн: https://stackoverflow.com/questions/16721772/mysql...

Answer 10

[Dimonchik]

память да, но для JOIN проц важнее, попрофилируйте лучше запросы

Answer 11

[al_gon]

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

SQL здесь не подходит.
Как реализуется набор фильтров для различных категорий по характеристикам товара?

Faceted Search with Solr

Answer 12

[Fortop]

В такой постановке вопроса - самый правильный вариант оценить стоимость каждого из решений на некоторый срок (года, два, десять) зависящий от скорости изменений в проекте и его среде окружения.
И выбрать более эффективное с точки зрения стоимости.

Answer 13

[akzhan]

Вам поможет такое эмпирическое правило - увеличение размеры таблицы в 10 раз замедляет поиск по индексу не более, чем вдвое. Увеличение таблицы в 100 раз уменьшает поиск не более, чем в 4 раза.

На самом деле это абсолютно неточно, мы не учитываем размещения промежуточных результатов разных соединений на диске и так далее. Но главное - тут становится очевидным, что партиционирование таблицы вам скорее во вред, чем на пользу.

Что я вам рекомендовал бы - просто вычитать EXPLAIN.

Далее просто сменить MySQL на Postgres.

И только после этого заниматься denormalization, NoSQL etc.

Answer 14

[Дмитрий]

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

Comments

[Everything_is_not_so_bad]

Что такое "классическая нормализация", подробнее?

[Дмитрий]

Про нормализацию можно почитать вот тут: https://m.habrahabr.ru/post/254773/ и любые источники и литература по запросу нормализация баз данных. На эту тему очень много статей и литературы. Рекомендую найти ту, которая "зайдёт".