Как оптимизировать SQL-запрос, объединяющий 3 таблицы

Привет,

У меня есть следующий запрос SQL, который выполняется около 4 секунд:

select 
    o.id, tu.status_type, m.upload_date
from 
    (select order_id, max(last_updated) as maxudate from tracking_update group by order_id) t
inner join 
    tracking_update tu on t.order_id=tu.order_id and t.maxudate=tu.last_updated
right join 
    fgw247.order o on t.order_id=o.id
left join 
    manifest m on o.manifest_id=m.id
where 
    (m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD')

Запрос объединяет следующие 3 таблицы:

Order, Manifest и Tracking_Update.

Запрос вернет заказы, которые соответствуют следующим критериям:

  1. Заказы < 30 дней с любым статусом доставки
  2. Заказы> 30 дней и не статус доставки

Заказ считается доставленным, если последняя отслеживаемая дата обновления для этого заказа имеет статус-типа 'D' или 'XD'

Теперь заказы перечислены в таблице заказов. В таблице Order есть столбец manifest_id, который ссылается на Манифест, созданный при загрузке заказа.

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

Наконец, таблица tracking_update содержит tracking_updates для каждого заказа. У заказа может быть несколько tracking_updates.

В настоящее время таблица tracking_update содержит более 1 млн записей.

Ниже перечислены операторы создания для каждой таблицы:

CREATE TABLE "order" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "ShipmentId" varchar(50) DEFAULT NULL,
  "RecipientName" varchar(160) DEFAULT NULL,
  "CompanyName" varchar(160) DEFAULT NULL,
  "Address1" varchar(160) DEFAULT NULL,
  "Address2" varchar(160) DEFAULT NULL,
  "City" varchar(50) DEFAULT NULL,
  "State" varchar(3) DEFAULT NULL,
  "ZIP" int(11) DEFAULT NULL,
  "TEL" int(11) DEFAULT NULL,
  "Email" varchar(255) DEFAULT NULL,
  "Bottles" int(11) DEFAULT NULL,
  "Weight" float DEFAULT NULL,
  "Resi" tinyint(1) DEFAULT NULL,
  "Wave" int(11) DEFAULT NULL,
  "url_slug" varchar(50) DEFAULT NULL,
  "manifest_id" int(11) DEFAULT NULL,
  "shipment_date" datetime DEFAULT NULL,
  "tracking_number" varchar(30) DEFAULT NULL,
  "shipping_carrier" varchar(10) DEFAULT NULL,
  "last_tracking_update" datetime DEFAULT NULL,
  "delivery_date" datetime DEFAULT NULL,
  "customer_code" varchar(50) DEFAULT NULL,
  "sub_cust_code" int(11) DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "ShipmentID" ("ShipmentId"),
  KEY "manifest_id" ("manifest_id"),
  KEY "order_idx3" ("tracking_number"),
  KEY "order_idx4" ("customer_code"),
  CONSTRAINT "order_ibfk_1" FOREIGN KEY ("manifest_id") REFERENCES "manifest" ("id")
);

Таблица отслеживания обновлений:

CREATE TABLE "tracking_update" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "order_id" int(11) DEFAULT NULL,
  "ship_update_date" datetime DEFAULT NULL,
  "message" varchar(400) DEFAULT NULL,
  "location" varchar(100) DEFAULT NULL,
  "status_type" varchar(2) DEFAULT NULL,
  "last_updated" datetime DEFAULT NULL,
  "hash" varchar(32) DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "order_id" ("order_id"),
  KEY "tracking_update_idx2" ("status_type"),
  KEY "tracking_update_idx3" ("ship_update_date"),
  CONSTRAINT "tracking_update_ibfk_1" FOREIGN KEY ("order_id") REFERENCES "order" ("id")
);

Таблица манифеста:

CREATE TABLE "manifest" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "upload_date" datetime DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL,
  "destination_gateway" varchar(40) DEFAULT NULL,
  "arrived" tinyint(1) DEFAULT NULL,
  "customer_code" varchar(50) DEFAULT NULL,
  "upload_user" varchar(50) DEFAULT NULL,
  "trip_id" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
);

Вот также EXPLAIN для оператора select, экспортированного с помощью JSON:

{
    "data":
    [
        {
            "id": 1,
            "select_type": "PRIMARY",
            "table": "m",
            "type": "ALL",
            "possible_keys": "PRIMARY",
            "key": null,
            "key_len": null,
            "ref": null,
            "rows": 220,
            "Extra": "Using where"
        },
        {
            "id": 1,
            "select_type": "PRIMARY",
            "table": "o",
            "type": "ref",
            "possible_keys": "manifest_id",
            "key": "manifest_id",
            "key_len": "5",
            "ref": "fgw247.m.id",
            "rows": 246,
            "Extra": "Using index"
        },
        {
            "id": 1,
            "select_type": "PRIMARY",
            "table": "tu",
            "type": "ref",
            "possible_keys": "order_id",
            "key": "order_id",
            "key_len": "5",
            "ref": "fgw247.o.id",
            "rows": 7,
            "Extra": "Using where"
        },
        {
            "id": 1,
            "select_type": "PRIMARY",
            "table": "<derived2>",
            "type": "ref",
            "possible_keys": "<auto_key0>",
            "key": "<auto_key0>",
            "key_len": "11",
            "ref": "fgw247.o.id,fgw247.tu.last_updated",
            "rows": 13,
            "Extra": "Using index"
        },
        {
            "id": 2,
            "select_type": "DERIVED",
            "table": "tracking_update",
            "type": "index",
            "possible_keys": "order_id",
            "key": "order_id",
            "key_len": "5",
            "ref": null,
            "rows": 1388275,
            "Extra": null
        }
    ]
}

Любая помощь приветствуется

ОБНОВИТЬ

Это текущий запрос, который я использую, который НАМНОГО быстрее:

SELECT 
    o.*, tu.*
FROM
    fgw247.`order` o
JOIN
    manifest m
ON
    o.`manifest_id` = m.`id` 

JOIN
    `tracking_update` tu
ON
    tu.`order_id` = o.`id` and tu.`ship_update_date` = (select max(last_updated) as last_updated from tracking_update where order_id = o.`id` group by order_id)
WHERE
    m.`upload_date` >= '2015-12-14 11:50:12' 
    OR 
        (o.`delivery_date` IS NULL AND m.`upload_date` < '2015-12-14 11:50:12')
LIMIT 100
Источник

1 ответ

Рассмотрим следующие варианты, чтобы оптимизировать выполнение вашего запроса:

  1. Типы соединений: вы уверены, что вам нужны левые и правые соединения? Вы хотите сообщить о заказах, которые даже не включены в манифест? Если нет, то используйте внутреннее соединение вместо левого и правого.

  2. Дополнительные индексы: в поле manifest.upload_date нет индекса, его следует добавить. Я также создал бы составной индекс для полей order_id, update_date и status_type (в этом порядке!) Для таблицы tracking_update.

  3. использование union вместо or критерий, в котором: Mysql традиционно не очень хорош в оптимизации or критерий в where статьи. Так что включи (m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') в союз с 2 запросами. Эти 2 запроса будут такими же, как существующий запрос до части where. 1-й запрос будет иметь только (m.upload_date >= '2015-12-12 00:00:00') условие в предложении where, в то время как второй будет иметь (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') критерии.

Если вы добавляете новые индексы, то, пожалуйста, проверьте, использует ли запрос их, запустив новое объяснение.

Источник
Другие вопросы по тегам