Повысьте эффективность этого запроса: обновление с помощью объединений и подзапросов

У меня есть база данных mysql с
- таблица Посылок, которые нужно отправить людям (здесь 16 000 записей), индексы на account_no, сервис
- таблица тарифов (500 000 записей) - тариф зависит от: района доставки, тарифа клиента и типа услуги (например, на следующий день и т. д.), индексов по площади, цене, сервису
- таблица первой части почтового индекса (или почтового индекса), которая дает площадь (3000) - таблица учетной записи клиента, содержащая курс цен (1600), индекс курса цен
Запрос находит цену, которая будет стоить отправить посылку, и обновляет цену клиента для этой посылки с уникальным идентификатором
Обновление 16000 записей посылок с ценой отправки каждой посылки занимает 70 секунд

UPDATE
   tbl_parcel AS t20, ( 
   SELECT 
      id, service, rate_group, area, 
      (
         SELECT
            rate 
         FROM
            tbl_rates_all t4 
         WHERE
            t4.service = t10.service 
            AND t4.area = t10.area 
            AND t4.rate_group = t10.rate_group 
      )
      AS price 
   FROM
      (
         SELECT
            id,
            t1.service,
            rate_group,
            area
         FROM
            tbl_parcel t1 
            JOIN
               tbl_account t2 
               ON t1.account_no = t2.account_no 
            JOIN
               tbl_pr_postcode t3 
               ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode 
      ) t10 
) AS src 
   SET
      t20.customer_price = src.price 
   WHERE
      t20.id = src.id

Занимает 70 секунд для записи 16000 посылок

В конечном итоге именно эта часть убивает эффективность

ОТ tbl_rates_all t4 КУДА t4.сервис = t10.сервис И t4.площадь = t10.площадь И t4.rate_group = t10.rate_group

Я мог бы иметь отдельные таблицы ставок для каждой ставки, поскольку это был первоначальный дизайн, поэтому переменная вызывала бы, например. tbl_rates001, в котором может быть только 3000 записей, а не 500 000. Проблема с этим в mysql заключалась в том, что при создании имени таблицы на лету это было невозможно без использования подготовленного оператора, поэтому я подумал, что этот метод бесполезен. Жаль, что вы не можете использовать пользовательскую переменную для хранения номера цены, а затем добавить ее к названию цены таблицы.

Я новичок в базах данных и запросах, поэтому, если вам что-то кричит, что поможет, спасибо за любой вклад

С Уважением

ДОПОЛНЕНИЕ ПО ЗАПРОСУ СХЕМЫ

CREATE TABLE `tbl_x_rate_all` (
`id` bigint(20) NOT NULL, 
`service` varchar(4) NOT NULL,
`chargetype` char(1) NOT NULL,
`area` smallint(6) NOT NULL,
`rate` float(7,2) NOT NULL,
`rate_group` smallint(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `tbl_x_rate_all` ADD PRIMARY KEY (`id`), ADD KEY `rate_group` (`rate_group`), ADD KEY `area` (`area`), ADD KEY `service` (`service`),   
ADD KEY `chargetype` (`chargetype`);

Опубликуйте свою схему tbl_rates_all, включая индексы.

emix 22.05.2019 16:47

спасибо сделал так

Rich 22.05.2019 17:06

Это тоже убийца: LEFT(full_pcode, locate(' ', full_pcode) - 1). В MySQL функции не могут использовать индексы.

Strawberry 22.05.2019 17:12

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

Rich 22.05.2019 17:14

Я понятия не имею, как может работать ваш подзапрос price, какой в ​​нем смысл? Как он может ссылаться на t10, внешний псевдоним изнутри? Чем подзапрос отличается от простого rate as Price?

Nae 22.05.2019 17:23

Привет Нэ. Если я создам виртуальную таблицу t10, у меня будет rate_group, area и service. Из этого я могу использовать те, что в таблице Rates_all, чтобы получить цену. извините, я думаю, что мое имя плохое. rate(price) и rate_group можно легко спутать

Rich 22.05.2019 17:41

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

Strawberry 22.05.2019 17:45

Привет, Strawberry. Я только что просмотрел коррелированные подзапросы! Так что я прав, говоря, что я нахожусь в парке некоррелированных подзапросов (?) Спасибо! Я думаю, что я мог попробовать по-другому (?), И время было 5 минут +

Rich 22.05.2019 18:00
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
0
8
48
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

@Богатый --

Ничего не выскакивает. Возможно, вы сможете получить некоторые незначительные улучшения, создав несколько временных таблиц, обработав SET вне основного запроса и используя некоторые OUTER APPLY вместо вложенных запросов.

Если вы новичок в mysql/базах данных в целом, функция EXPLAIN может быть очень полезна при оптимизации.

https://dev.mysql.com/doc/refman/5.5/en/using-explain.html

Привет Брайан, спасибо за ваш вклад до сих пор! Я проверю вышеизложенное и вернусь к вам

Rich 22.05.2019 17:07
Ответ принят как подходящий

Если предположить, что id, rate_group и area из t1 внутри t10, то ваш запрос будет медленнее? версия того, что ниже:

UPDATE
    tbl_parcel AS t20
INNER JOIN (
    SELECT
        t1.id,
        t4.rate as price
    FROM tbl_parcel t1
    JOIN tbl_account t2 ON t1.account_no = t2.account_no
    JOIN tbl_pr_postcode t3 ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode
    LEFT JOIN tbl_rates_all t4 ON t1.service = t4.service AND t1.area = t4.area
        AND t1.rate_group = t4.rate_group
    ) src ON t20.id = src.id
SET
    t20.customer_price = src.price
WHERE
    t20.id = src.id

Я предполагаю, что вы можете еще больше потерять подзапрос, который, как правило, громоздкий:

UPDATE
    tbl_parcel AS t20
INNER JOIN tbl_parcel t1 ON t20.id = t1.id
INNER JOIN tbl_account t2 ON t1.account_no = t2.account_no
INNER JOIN tbl_pr_postcode t3 ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode
LEFT JOIN tbl_rates_all t4 ON t1.service = t4.service AND t1.area = t4.area
        AND t1.rate_group = t4.rate_group
SET
    t20.customer_price = t4.rate
WHERE
    t20.id = t1.id
    -- can also replace with
    -- TRUE
    -- or lose it altogether
;

Вы можете попробовать добавить индекс для соединений t1 и t4, если у вас есть основания полагать, что соединение является узким местом:

create index tbl_rates_all_service_area_rate_group_index
    on tbl_rates_all (service, area, rate_group);

create index tbl_parcel_service_area_rate_group_index
    on tbl_parcel (service, area, rate_group);

Привет, Наэ .. очень ценю предложение. Я дал ему хороший тест. При больших числах в таблице посылок t1, например. 22000 и у меня и у вас около 120 секунд. буквально секунду или 2 разные. Твой всегда на секунду быстрее. На небольших записях посылок t1, например <1000, я в основном получаю, что ваши данные на 1% быстрее, примерно на 7 секунд, но иногда и на 10% быстрее. Ваш всегда быстрее, но в основном на 1% быстрее. как вы думаете. еще раз спасибо. Богатый

Rich 23.05.2019 15:55

@Rich Если вы все еще считаете, что узким местом является соединение t4, попробуйте добавить индексы к t1 и t4 для соединения столбцов в том порядке, в котором они соединяются.

Nae 23.05.2019 18:25

Не могли бы вы расширить «в порядке их присоединения». У меня уже есть индексы для столбцов соединения. Вы говорите о создании временной таблицы с индексами? Я тоже попробовал ваше второе предложение, но, увы, аналогичные результаты. Хотя приятно видеть, что это можно сделать по-разному. еще раз спасибо

Rich 24.05.2019 08:34

@Rich Вы можете создать один индекс для нескольких столбцов одновременно. В этом случае вы можете захотеть иметь один индекс для service, area и rate_group на t4, а затем еще один индекс для service, area и rate_group на t1.

Nae 24.05.2019 09:08

хорошо, спасибо, Наэ.. я посмотрю на это и вернусь. та Рич

Rich 24.05.2019 09:55

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

Rich 25.05.2019 09:51

Другие вопросы по теме