mariadb 当服务器承受更大的压力时，查询速度会变慢

daupos2t  于 2022-12-29  发布在  其他
关注(0)|答案(2)|浏览(142)

当服务器加载时，我如何提高以下查询的性能？我已经创建了一个组合索引，并从表中删除了该索引，因为它不拾取它，但有没有其他方法可以做到这一点？，通常我运行以下查询它需要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

请帮我一下，谢谢！

mariadb

2条答案

按热度按时间
qoefvg9y

qoefvg9y1#

这看起来像是“爆炸-内爆”综合症，即查询从JOINs构建一个大的临时表，然后用GROUP BY将其折叠。
也就是说，执行最少的JOINing操作以获取GROUP BY中表的id，* 然后 * 进入其他表（可能还返回到原始表）以获取其他列。
如果不知道哪些列在哪些表中，我就无法进一步帮助您。
其他提示...如果“amount”和“rate”是数字，并且如果您要用它们进行任何计算，它们应该是某种数字类型，而不是VARCHAR。
现在和重新制定查询后，这些可能会有所帮助：

orders:  INDEX(delivery_date, plan_id, pal_area)
cart:  INDEX(order_id,  out_of_stock)
routes:  INDEX(name,  id, route_type)
pr:  INDEX(route_id,  status, plan_id)
rra:  INDEX(allotment_id,  id, allotment_bts_date)
warehouse_planid:  INDEX(warehouse_id, plan_id)
ard:  INDEX(route_id,  created_date, plan_id, route_allocation_id)

添加复合索引时，删除具有相同前导列的索引。也就是说，当同时具有INDEX（a）和INDEX（a，b）时，删除前者。

赞(0）分享  回复(0）举报  2022-12-29
9w11ddsr

9w11ddsr2#

首先，无论何时使用分组依据，通常都需要将所有非聚合列作为分组依据的一部分，而其中只有几个值。
话虽如此，如果有些东西永远不会改变，您可以简单地对该列应用MAX（）或MIN（）。
现在，您的组仅包括：plan_id，pal_area，is_rvp，is_b2b，a.id但是您的数据包括与其他更深层次的组件关联的用户ID、路线、日期、名称等内容。但是如果给定计划下的给定订单可以覆盖不同的路线，您如何知道您在数据中提取的是最终正确的路线。
对于索引，我同意Rick建议的列表。这将有助于优化表之间的连接，确保键（以适当的顺序）最好地匹配您所追求的。
最后一个建议，对于MySQL，我已经遇到过很多次，但并不总是保证，添加STRAIGHT_JOIN子句，它告诉引擎按照您提供的顺序运行查询，例如

select STRAIGHT_JOIN
  (rest of query)
赞(0）分享  回复(0）举报  2022-12-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com