主页 / user-67305

Eustace's questions

Martin Hope
Eustace
Asked: 2015-05-29 00:31:57 +0800 CST
  • 1

我正在尝试运行此查询。* 只是为了简化，我选择了 ~10 个特定字段进行一些计算。

SELECT * FROM `orders`
    LEFT JOIN `orders_processed` ON `orders_processed`.`order_id` = `orders`.`id`
    LEFT JOIN `returns` ON
        `orders_processed`.`date_id`=`returns`.`date_id`
        AND `orders_processed`.`product_id`=`returns`.`product_id`
        AND `orders_processed`.`variant_id`=`returns`.`variant_id`
WHERE `orders`.`date_id` BETWEEN @from AND @to

其中 date_id 是格式为“%Y%m%d”（例如 20150501）的日期，不是唯一的（多行具有相同的 date_id），orders并且returns每行约 200k 行，orders_processed约 400k。我正在使用 运行此查询@from := 20140505，@to := 20150505但它停止了。我假设这是因为

`orders_processed`.`date_id`=`returns`.`date_id`

因为当我把它改成

`orders_processed`.`order_id`=`returns`.`order_id`

它瞬间运行。但是，计算结果发生变化，我不希望那样。有什么方法可以将连接的这一部分更改为更有效率的东西吗？

此外，可能值得一提的是，它在一个相同的、稍微大一点的数据库（相同的模式，只是用新数据更新）上运行良好。

在较小的数据库上查询的 EXPLAIN：

选择类型 | 表 | 表格类型 | 可能的键 | 键 |key_len| 参考 | 行 | 额外的
---------------------------------------------- ---------------------------------------------- --------------
    简单 | 订单| 范围 | 日期_id | 日期_id | 5 | 空 |89264 | 在哪里使用；使用索引
---------------------------------------------- ---------------------------------------------- --------------
    简单 |o_processed| 参考 | uni,order_id | 大学 | 4 |订单.id| 1 | 无效的
---------------------------------------------- ---------------------------------------------- --------------
SIMPLE|returns|ref|date_id,returns_ibfk_1,returns_ibfk_5|returns_ibfk_1|3|orders_processed.variant_id|5|使用哪里

更大数据库的解释结果是相同的，除了 的“行” orders，它是 95020。

CREATE TABLEs（简化，删除不相关的列）

    CREATE TABLE IF NOT EXISTS `orders` (
    `id` int(10) unsigned NOT NULL DEFAULT '0',
    `customer` int(10) unsigned NOT NULL DEFAULT '0',
    `date_time` datetime DEFAULT NULL,
    `date_id` int(10) unsigned DEFAULT NULL,
    `shipping` decimal(10,2) unsigned DEFAULT NULL,
    `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY (`date_id`),
    CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id`) REFERENCES `ref_order_id`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`date_id`) REFERENCES `cr_calendar` (`date_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders_processed` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `order_id` int(10) unsigned NOT NULL DEFAULT '0',
    `date_id` int(10) unsigned DEFAULT NULL,
    `product_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `variant_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
    `quantity` smallint(5) unsigned DEFAULT '0',
    `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uni`(`order_id`,`product_id`,`variant_id`,`price`),
    KEY(`order_id`),
    KEY(`product_id`),
    KEY(`date_id`),
    KEY(`quantity`),
    KEY(`variant_id`),
    KEY(`updated_on`),
    CONSTRAINT `orders_processed_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `orders_processed_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `ref_order_id`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `orders_processed_ibfk_3` FOREIGN KEY (`date_id`) REFERENCES `calendar`(`date_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `returns` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `order_id` int(10) unsigned NOT NULL DEFAULT '0',
    `customer_id` int(8) unsigned not null default 0,
    `product_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `variant_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `date_id` int(10) unsigned DEFAULT NULL,
    `refund` decimal(10,2) unsigned DEFAULT '0.00',
    `reason` smallint(5) unsigned NOT NULL DEFAULT 0,
    `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uni`(`order_id`,`product_id`,`variant_id`,`reason`),
    KEY(`date_id`),
    KEY(`reason`),
    KEY(`updated_on`),
    CONSTRAINT `returns_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `returns_ibfk_2` FOREIGN KEY (`reason`) REFERENCES `ref_reasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `returns_ibfk_3` FOREIGN KEY (`order_id`) REFERENCES `ref_order_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `returns_ibfk_4` FOREIGN KEY (`variant_id`) REFERENCES `variants`(`variant_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql join