Отображение большого количества данных в таблице подкачки без сильного влияния на БД

Давайте создадим этот запрос из его составных частей, чтобы посмотреть, что мы можем оптимизировать.

Замечание: вы выбираете 100 самых последних строк из объединения двух больших таблиц продуктов.

Итак, начнем с попытки оптимизировать подзапросы, извлекающие данные из таблиц продуктов. Вот один из них.

              SELECT line_id,assembly_datetime datetime,...
FROM table_product_stereos
WHERE company_id=#

Но посмотрите, вам нужно только 100 новых записей здесь. Итак, давайте добавим

               ORDER BY assembly_datetime DESC
LIMIT 100

на этот запрос. Кроме того, вы должны поместить составной индекс в эту таблицу следующим образом. Это позволит индексам удовлетворять поискам WHERE и ORDER BY.

 CREATE INDEX id_date ON table_product_stereos (company_id, assembly_datetime)

Все те же соображения относятся к запросу от table_product_televisions, Закажите его по времени, ограничьте до 100 и внесите в указатель.

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

              SELECT t.line_id,t.assembly_datetime datetime,...
FROM table_product_stereos AS t
JOIN table_manufacture_line AS m   ON m.line_id = t.line_id
AND m.line_name LIKE '%test'
WHERE company_id=#
ORDER BY assembly_datetime DESC
LIMIT 100

Далее вы используете UNION объединить эти два набора результатов запроса в один. UNION имеет функцию устранения дубликатов, что отнимает много времени. (Вы знаете, что у вас нет дубликатов, но MySQL нет.) Использование UNION ALL вместо.

Собрав все это вместе, самый внутренний подзапрос становится этим. Нам нужно завершить подзапросы, потому что SQL сбит с толку UNION а также ORDER BY пункты на том же уровне запроса.

           SELECT * FROM (
SELECT line_id,assembly_datetime datetime,...
FROM table_product_stereos
WHERE company_id=#
ORDER BY assembly_datetime DESC
LIMIT 100
) AS st
UNION ALL
SELECT * FROM (
SELECT line_id,assembly_datetime datetime,...
FROM table_product_televisions
WHERE company_id=#
ORDER BY assembly_datetime DESC
LIMIT 100
) AS tv

Это дает вам 200 строк. Он должен получить эти строки довольно быстро.

200 строк гарантированно будет достаточным, чтобы предоставить вам 100 самых последних предметов позже, после того как вы выполните ORDER BY ... LIMIT операция. Но эта операция должна обрабатывать только 200 строк, а не 100K +, поэтому она будет намного быстрее.

Наконец, заверните этот запрос в материал внешнего запроса. Присоединяйся к table_manufacture_line информация и исправить часовой пояс.

Если вы делаете индексацию и ORDER BY ... LIMIT Операция ранее, этот запрос должен стать очень быстрым.

Диалог комментариев в вашем вопросе указывает мне, что у вас может быть несколько типов продуктов, а не только два, и что у вас есть сложные критерии выбора для вашего постраничного отображения. С помощью UNION ALL при большом количестве строк производительность снижается: она преобразует несколько проиндексированных таблиц во внутренний список строк, которые просто невозможно эффективно найти.

Вы действительно должны подумать о том, чтобы поместить два вида данных о продукте в одну таблицу вместо того, чтобы UNION ALL несколько таблиц продуктов. Установки, которые у вас сейчас есть, негибкие и не будут легко масштабироваться. Если вы структурируете свою схему с помощью основной таблицы продуктов и, возможно, некоторых атрибутивных таблиц для информации по конкретным продуктам, через два года вы окажетесь намного счастливее. Шутки в сторону. Пожалуйста, рассмотрите возможность внесения изменений.