当服务器加载时,我如何提高以下查询的性能?我已经创建了一个组合索引,并从表中删除了该索引,因为它不拾取它,但有没有其他方法可以做到这一点?,通常我运行以下查询它需要198.24毫秒一旦加载到服务器上,然后它需要大约24-30秒。
SELECT
`u`.`id` AS `user_id`,
`orders`.`plan_id`,
`u`.`username`,
`allotments`.`status_updated_by_admin`,
`allotments`.`driver_ats_date`,
`allotments`.`admin_bts_date`,
`allotments`.`driver_bts_date`,
`pal_area` AS `route_name`,
count(distinct orders.order_id) AS `total_order`,
count(distinct orders.shipment_id) AS `total_address`,
count(
distinct (
case when orders.status != 'orderinprocess' then orders.order_id end
)
) AS `total_marked_order`,
count(
distinct (
case when orders.status = 'delivered' then orders.order_id end
)
) AS `total_deliverd`,
count(
distinct (
case when orders.status = 'cancel' then orders.order_id end
)
) AS `total_cancel`,
count(
distinct (
case when orders.status = 'reschedule' then orders.order_id end
)
) AS `total_reschedule`,
count(
distinct (
case when orders.status = 'orderinprocess' then orders.order_id end
)
) AS `total_process`,
count(
distinct (
case when orders.status != 'orderinprocess' then orders.shipment_id end
)
) AS `total_marked_address`,
`cart`.`out_of_stock`,
`routes`.`id` AS `route_id`,
`allotments`.`id` AS `allotment_id`,
`allotments`.`admin_cash_return` AS `cash_status`,
`pr`.`status` AS `route_check_status`,
(
CASE WHEN orders.source in(
'field', 'nonfield', 'non-field',
'o2o', 'b2bapp', 'field1', 'panel',
'e-bazaar', 'b2c-field', 'b2c-field1',
'b2c-non-field', 'b2c-b2bapp', 'b2b-inbound',
'outbound-b2b', 'inbound-b2b', 'b2c-outbound-b2b',
'b2c-inbound-b2b', 'b2c-o2o'
) THEN 1 ELSE 0 END
) AS `is_b2b`,
(
CASE WHEN routes.route_type = 'rvp' THEN 1 ELSE 0 END
) AS `is_rvp`,
`allotments`.`delivery_boy_id`,
`allotments`.`field_exc`,
`allotments`.`inventory_update_status`,
IF(rra.id IS NOT NULL, 1, 0) AS `is_rvp_route`,
`rra`.`allotment_bts_date` AS `rvp_allotment_bts_date`
FROM
`orders`
INNER JOIN `routes` ON routes.name = orders.pal_area
INNER JOIN `plan_routes` `pr` ON pr.route_id = routes.id
and pr.plan_id = orders.plan_id
INNER JOIN `warehouse_planid` ON warehouse_planid.plan_id = orders.plan_id
LEFT JOIN `allotment_route_details` `ard` ON ard.route_id = routes.id
and date(ard.created_date) = orders.delivery_date
and ard.plan_id = orders.plan_id
LEFT JOIN `allotments` ON allotments.id = ard.route_allocation_id
and orders.plan_id = allotments.plan_id
and allotments.allotment_date = orders.delivery_date
LEFT JOIN `rvp_route_allotments` `rra` ON rra.allotment_id = allotments.id
LEFT JOIN `user` `u` ON u.id = allotments.user_id
LEFT JOIN `cart_order_outofstock` `cart` ON cart.order_id = orders.order_id
WHERE
(
orders.delivery_date= '2022-12-27'
)
AND (
`warehouse_planid`.`warehouse_id` = '141'
)
AND (`orders`.`plan_id` = 'U238490')
GROUP BY
`pal_area`,
`plan_id`,
`is_rvp`,
`is_b2b`,
`allotments`.`id`
解释计划;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: warehouse_planid
type: const
possible_keys: wp_planid,warehouse_id,warehouse_planid_plan_id
key: wp_planid
key_len: 603
ref: const
rows: 1
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: delivery_date,pal_area,orders_plan_id,idx_plan_order_id
key: orders_plan_id
key_len: 603
ref: const
rows: 5569
Extra: Using index condition; Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: routes
type: eq_ref
possible_keys: PRIMARY,name_2,name
key: name_2
key_len: 767
ref: dealshare_delivery.orders.pal_area
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ard
type: ref
possible_keys: route_id,plan_id
key: route_id
key_len: 5
ref: dealshare_delivery.routes.id
rows: 3
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: allotments
type: eq_ref
possible_keys: PRIMARY,allotment_date,allotments_plan_id
key: PRIMARY
key_len: 4
ref: dealshare_delivery.ard.route_allocation_id
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: rra
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30
Extra: Using where; Using join buffer (flat, BNL join)
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dealshare_delivery.allotments.user_id
rows: 1
Extra: Using where
*************************** 8. row ***************************
id: 1
select_type: SIMPLE
table: cart
type: ref
possible_keys: order_id_fk1
key: order_id_fk1
key_len: 4
ref: dealshare_delivery.orders.order_id
rows: 1
Extra: Using where
*************************** 9. row ***************************
id: 1
select_type: SIMPLE
table: pr
type: ref
possible_keys: route_id
key: route_id
key_len: 5
ref: dealshare_delivery.routes.id
rows: 3
Extra: Using where
表格结构;
Table: orders
Create Table: CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`transaction_type` varchar(255) NOT NULL,
`sub_type` varchar(255) NOT NULL,
`document_type` varchar(255) NOT NULL,
`doc_number` varchar(255) NOT NULL,
`doc_date` varchar(255) NOT NULL,
`warehouse` varchar(255) NOT NULL,
`deal_no` varchar(255) NOT NULL,
`product_name` varchar(255) NOT NULL,
`item_description` varchar(255) NOT NULL,
`hsn_code` varchar(255) NOT NULL,
`item_quantity` varchar(255) NOT NULL,
`weight` varchar(255) DEFAULT NULL,
`item_unit` varchar(255) NOT NULL,
`cgst_rate` varchar(255) NOT NULL,
`cgst_amount` varchar(255) NOT NULL,
`sgst_rate` varchar(255) NOT NULL,
`sgst_amount` varchar(255) NOT NULL,
`igst_rate` varchar(255) NOT NULL,
`igst_amount` varchar(255) NOT NULL,
`cess_rate` varchar(255) NOT NULL,
`cess_amount` varchar(255) NOT NULL,
`transaction_value` varchar(255) NOT NULL,
`order_id` varchar(255) NOT NULL,
`source` varchar(255) NOT NULL,
`verified_date` varchar(255) NOT NULL,
`dispatch_date` varchar(255) DEFAULT NULL,
`status` varchar(255) NOT NULL,
`sub_status` varchar(255) DEFAULT NULL,
`shipment_id` varchar(100) NOT NULL,
`sku_quantity` varchar(100) DEFAULT NULL,
`sku_id` varchar(255) NOT NULL,
`delivery_date` varchar(255) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
`van_no` varchar(255) DEFAULT NULL,
`attempts` varchar(255) DEFAULT NULL,
`rescheduled_date` varchar(255) DEFAULT NULL,
`rescheduled_timeslote` varchar(255) DEFAULT NULL,
`last_modified_date` varchar(255) NOT NULL,
`latitude` varchar(255) DEFAULT NULL,
`longitude` varchar(255) DEFAULT NULL,
`priority` varchar(255) NOT NULL,
`delivery_partner_name` varchar(255) DEFAULT NULL,
`mode_transport` varchar(255) DEFAULT NULL,
`delivery_boy_name` varchar(255) DEFAULT NULL,
`vehicle_no` varchar(255) DEFAULT NULL,
`pal_pincode` varchar(255) NOT NULL,
`pal_area` varchar(255) NOT NULL,
`pal_location` varchar(255) NOT NULL,
`tag` varchar(255) DEFAULT NULL,
`dmk` varchar(255) DEFAULT NULL,
`route` varchar(255) NOT NULL,
`route_type` varchar(255) DEFAULT NULL,
`payment_type` varchar(255) DEFAULT NULL,
`order_type` varchar(100) DEFAULT NULL,
`club_order_id` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`orderupdated` int(1) NOT NULL DEFAULT 0,
`plan_id` varchar(200) DEFAULT NULL,
`utm_source` varchar(200) DEFAULT NULL,
`sku_mrp` varchar(20) DEFAULT NULL,
`warehouse_id` int(11) DEFAULT NULL,
`user_address_id` bigint(20) DEFAULT NULL,
`delivery_slot` varchar(10) DEFAULT NULL,
`eta_delivery_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `status` (`status`),
KEY `delivery_date` (`delivery_date`),
KEY `product_name` (`product_name`),
KEY `pal_area` (`pal_area`),
KEY `orders_plan_id` (`plan_id`),
KEY `pal_location` (`pal_location`),
KEY `club_order_id` (`club_order_id`),
KEY `orders_shipment_id` (`shipment_id`),
KEY `orders_order_id_pan_id` (`order_id`,`plan_id`),
KEY `orders_created_at` (`created_at`),
KEY `orders_billed_amount` (`billed_amount`),
KEY `orders_cin` (`customer_cin`),
KEY `idx_plan_order_id` (`plan_id`,`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=165726520 DEFAULT CHARSET=utf8
Table: routes
Create Table: CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`route_type` varchar(10) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
`updated_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `name_2` (`name`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=136730 DEFAULT CHARSET=utf8
************************** 1. row ***************************
Table: allotment_route_details
Create Table: CREATE TABLE `allotment_route_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`route_allocation_id` int(11) DEFAULT NULL,
`route_id` int(11) DEFAULT NULL,
`plan_id` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT current_timestamp(),
`modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`warehouse_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `route_allocation_id` (`route_allocation_id`),
KEY `route_id` (`route_id`),
KEY `plan_id` (`plan_id`),
KEY `idx_created_date` (`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1298186 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Table: warehouse_planid
Create Table: CREATE TABLE `warehouse_planid` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`warehouse_id` int(11) DEFAULT NULL,
`plan_id` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`plan_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`slot` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`process_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`delivery_date` datetime DEFAULT NULL,
`type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`status` int(11) DEFAULT 1,
`picklist_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT current_timestamp(),
`modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`reason` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`reason_details` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `wp_planid` (`plan_id`),
KEY `warehouse_id` (`warehouse_id`),
KEY `warehouse_planid_plan_id` (`plan_id`),
KEY `warehouse_plan_delivery_date` (`delivery_date`),
KEY `idx_created_date` (`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=226446 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
请帮我一下,谢谢!
2条答案
按热度按时间qoefvg9y1#
这看起来像是“爆炸-内爆”综合症,即查询从
JOINs
构建一个大的临时表,然后用GROUP BY
将其折叠。也就是说,执行最少的
JOINing
操作以获取GROUP BY
中表的id,* 然后 * 进入其他表(可能还返回到原始表)以获取其他列。如果不知道哪些列在哪些表中,我就无法进一步帮助您。
其他提示...如果“amount”和“rate”是数字,并且如果您要用它们进行任何计算,它们应该是某种数字类型,而不是
VARCHAR
。现在和重新制定查询后,这些可能会有所帮助:
添加复合索引时,删除具有相同前导列的索引。也就是说,当同时具有INDEX(a)和INDEX(a,b)时,删除前者。
9w11ddsr2#
首先,无论何时使用分组依据,通常都需要将所有非聚合列作为分组依据的一部分,而其中只有几个值。
话虽如此,如果有些东西永远不会改变,您可以简单地对该列应用MAX()或MIN()。
现在,您的组仅包括:plan_id,pal_area,is_rvp,is_b2b,a.id但是您的数据包括与其他更深层次的组件关联的用户ID、路线、日期、名称等内容。但是如果给定计划下的给定订单可以覆盖不同的路线,您如何知道您在数据中提取的是最终正确的路线。
对于索引,我同意Rick建议的列表。这将有助于优化表之间的连接,确保键(以适当的顺序)最好地匹配您所追求的。
最后一个建议,对于MySQL,我已经遇到过很多次,但并不总是保证,添加STRAIGHT_JOIN子句,它告诉引擎按照您提供的顺序运行查询,例如