优化多个链接表的主键和索引设置

我的表格如下所述。

1. orders是将存储所有详细信息的主表，例如客户ID、商家ID、分配给哪个骑手ID。订单详细信息，如总计、小计、税收以及客户详细信息，如地址纬度、经度。它还保存了每个细节，例如订单被接受、准备好、交付的时间。

CREATE TABLE `orders` (
  `orderID` int UNSIGNED NOT NULL,
  `orderIDHash` varchar(10) NOT NULL,
  `customerID` mediumint UNSIGNED NOT NULL,
  `merchantID` smallint UNSIGNED NOT NULL,
  `branchID` smallint UNSIGNED NOT NULL,
  `riderID` smallint UNSIGNED NOT NULL,
  `orderDateTime` timestamp NOT NULL,
  `subTotal` decimal(10,0) NOT NULL,
  `tax` decimal(10,0) NOT NULL,
  `total` decimal(10,0) NOT NULL,
  `quantity` tinyint NOT NULL,
  `customerAddress` varchar(150) NOT NULL,
  `customerLatitude` float NOT NULL,
  `customerLongitude` float NOT NULL,
  `paymentID` varchar(30) NOT NULL,
  `paymentType` tinyint NOT NULL,
  `instructions` varchar(100) NOT NULL,
  `orderType` tinyint NOT NULL,
  `neworderNotify` tinyint NOT NULL,
  `orderAcceptedDateTime` timestamp NOT NULL,
  `merchantUserAcceptedID` smallint NOT NULL DEFAULT '0',
  `orderAcceptedNotify` tinyint NOT NULL DEFAULT '0',
  `orderReadyDateTime` timestamp NOT NULL,
  `merchantUserReadyID` smallint NOT NULL DEFAULT '0',
  `orderReadyNotify` tinyint NOT NULL DEFAULT '0',
  `orderInDeliveryDateTime` timestamp NOT NULL,
  `orderInDeliveryNotify` tinyint NOT NULL DEFAULT '0',
  `orderPickerOrDeliveryDateTime` timestamp NOT NULL,
  `orderCancelDateTime` timestamp NOT NULL,
  `orderStatus` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`orderID`),
  ADD UNIQUE KEY `orderIDHash` (`orderIDHash`),
  ADD KEY `customerID` (`customerID`),
  ADD KEY `merchantID` (`merchantID`),
  ADD KEY `riderID` (`riderID`),
  ADD KEY `branchID` (`branchID`),
  ADD KEY `orderStatus` (`orderStatus`),
  ADD KEY `orderAcceptedNotify` (`orderAcceptedNotify`),
  ADD KEY `orderReadyNotify` (`orderReadyNotify`),
  ADD KEY `orderInDeliveryNotify` (`orderInDeliveryNotify`);


ALTER TABLE `orders`
  MODIFY `orderID` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

2. 第二个表是ordersLine。该表基本上存储与一个特定订单相关的所有项目。因此它与上面的订单表有关。

CREATE TABLE `orderLine` (
  `orderID` int NOT NULL,
  `itemID` mediumint NOT NULL,
  `itemQuantity` tinyint NOT NULL,
  `itemPrice` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `orderLine`
  ADD PRIMARY KEY (`orderID`,`itemID`);
COMMIT;

3. CustomerMessageLog 基本上是例如上面的订单表，我们有这个字段 orderAcceptedNotify 它将更改其值，例如默认为 0，然后更改为 1，表示商家接受订单，当订单已通知用户时更改为 2。因此，我们将消息存储在此日志中，因此当用户登录时，他可以看到所有消息。不同类型的消息可以有不同的 messageID。

CREATE TABLE `customerMessageLog` (
  `orderID` int NOT NULL,
  `customerID` mediumint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `customerMessageLog`
  ADD PRIMARY KEY (`orderID`,`customerID`,`messageID`);
COMMIT;

4. 该表用于存储所有通知给骑手的消息。

CREATE TABLE `riderMessageLog` (
  `orderID` int NOT NULL,
  `riderID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
ALTER TABLE `riderMessageLog`
  ADD PRIMARY KEY (`orderID`,`riderID`,`messageID`);
COMMIT;

5. 该表用于存储所有通知给商家的消息。

CREATE TABLE `merchantMessageLog` (
  `orderID` int NOT NULL,
  `merchantID` smallint NOT NULL,
  `branchID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `merchantMessageLog`
  ADD PRIMARY KEY (`orderID`,`merchantID`,`branchID`,`messageID`);
COMMIT;

6. 这是将存储所有 orderStatus 以及上面大多数表中的 messageID 的表通过连接引用此表。

CREATE TABLE `statusMessage` (
  `statusID` tinyint UNSIGNED NOT NULL,
  `message` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `statusMessage`
--

INSERT INTO `statusMessage` (`statusID`, `message`) VALUES
(0, 'Pending'),
(1, 'Payment Completed'),
(2, 'Accepted'),
(3, 'Completed'),
(4, 'Self Picked Up'),
(5, 'Assigned Rider'),
(6, 'In Delivery'),
(7, 'Delivered');


ALTER TABLE `statusMessage`
  ADD PRIMARY KEY (`statusID`);

所以现在的挑战是我已将 orderID 设置为 DDMMYYHHMMSS，这是我在提交订单进行付款之前从用户手机获得的。为什么我这样做是支付网关的原因，我需要 orderID。另一方面，我也可以将其设置为自动增量，也可以在付款前插入所有订单和 orderLine，但如果付款失败，那就是另一个问题了处理插入的订单和订单线。那么哪种方法最有效。我还想在向用户显示主要内容时对其进行哈希处理？