mysql优化-订单查询优化:视图优化+目录创建_mysql

drop view v_sale_order; CREATE VIEW `v_sale_order` AS SELECT     `so`.`sale_order_id` AS `v_sale_order_id`,     `so`.`sale_order_id` AS `sale_order_id`,     `so`.`sale_order_no` AS `sale_order_no`,     `so`.`order_type` AS `order_type`,     `so`.`platform_order_code2` AS `platform_order_code2`,     `so`.`platform_order_code` AS `platform_order_code`,     `so`.`platform_type` AS `platform_type`,     `so`.`platform_order_status` AS `platform_order_status`,     `so`.`created` AS `created`,     `so`.`end_time` AS `end_time`,     `so`.`total_num` AS `total_num`,     `so`.`total_sku` AS `total_sku`,     `so`.`modified` AS `modified`,     `so`.`seller_flag` AS `seller_flag`,     `so`.`seller_memo` AS `seller_memo`,     `so`.`seller_rate` AS `seller_rate`,     `so`.`snapshot_url` AS `snapshot_url`,     `so`.`status` AS `status`,     `so`.`step_trade_status` AS `step_trade_status`,     `so`.`trade_from` AS `trade_from`,     `so`.`trade_memo` AS `trade_memo`,     `so`.`trade_source` AS `trade_source`,     `so`.`type` AS `type`,     `so`.`shop_id` AS `shop_id`,     `so`.`origin_type` AS `origin_type`,     `so`.`sys_promotion_info` AS `sys_promotion_info`,      `sor`.`buyer_area` AS `buyer_area`,     `sor`.`buyer_email` AS `buyer_email`,     `sor`.`buyer_ip` AS `buyer_ip`,     `sor`.`buyer_memo` AS `buyer_memo`,     `sor`.`buyer_message` AS `buyer_message`,     `sor`.`buyer_nick` AS `buyer_nick`,     `sor`.`buyer_rate` AS `buyer_rate`,     `sor`.`receiver_address` AS `receiver_address`,     `sor`.`receiver_city` AS `receiver_city`,     `sor`.`receiver_country` AS `receiver_country`,     `sor`.`receiver_district` AS `receiver_district`,     `sor`.`receiver_mobile` AS `receiver_mobile`,     `sor`.`receiver_name` AS `receiver_name`,     `sor`.`receiver_phone` AS `receiver_phone`,     `sor`.`receiver_state` AS `receiver_state`,     `sor`.`receiver_town` AS `receiver_town`,     `sor`.`receiver_zip` AS `receiver_zip`,     `sor`.`area_id` AS `area_id`,     `sor`.`customer_id` AS `customer_id`,     `soc`.`courier_id` AS `courier_id`,     `soc`.`courier_order_no` AS `courier_order_no`,     `soc`.`courier_print_mark_state` AS `courier_print_mark_state`,     `soc`.`courier_print_time` AS `courier_print_time`,     `sof`.`alipay_id` AS `alipay_id`,     `sof`.`alipay_no` AS `alipay_no`,     `sof`.`payment` AS `payment`,     `sof`.`total_fee` AS `total_fee`,     `soi`.`invoice_order_no` AS `invoice_order_no`,     `soi`.`invoice_content` AS `invoice_content`,     `soi`.`invoice_type` AS `invoice_type`,     `soi`.`bank` AS `bank`,     `soi`.`title` AS `title`,     `soi`.`bank_account` AS `bank_account`,     `soi`.`tariff_lines` AS `tariff_lines`,     `sos`.`oms_process_type` AS `oms_process_type`,     `sos`.`play_state` AS `play_state`,     `sos`.`pause_state` AS `pause_state`,     `sos`.`stop_state` AS `stop_state`,     `sos`.`archive_state` AS `archive_state`,     `sos`.`is_paid` AS `is_paid`,     `sos`.`is_checked` AS `is_checked`,     `sos`.`is_approved` AS `is_approved`,     `sos`.`is_suspended` AS `is_suspended`,     `sos`.`is_invalidated` AS `is_invalidated`,     `sos`.`is_to_be_shipped` AS `is_to_be_shipped`,     `sos`.`is_after_sale` AS `is_after_sale`,     `sos`.`is_split` AS `is_split`,     `sos`.`is_combined` AS `is_combined`,     `sos`.`is_closed` AS `is_closed`,     `sos`.`is_after_sale_closed` AS `is_after_sale_closed`,     `sos`.`is_amount_changed` AS `is_amount_changed`,     `sos`.`is_part_changed` AS `is_part_changed`,     `sos`.`is_out_of_stock` AS `is_out_of_stock`,     `sos`.`pay_type` AS `pay_type`,     `sos`.`pay_time` AS `pay_time`,     `sos`.`original_order_id` AS `original_order_id`,     `sos`.`after_sale_note` AS `after_sale_note`,     `sos`.`suspend_note` AS `suspend_note`,     `sos`.`unapprove_note` AS `unapprove_note`,     `sos`.`after_sale_type` AS `after_sale_type`,     `sos`.`blacklist_type` AS `blacklist_type`,      `sow`.`warehouse_id` AS `warehouse_id`,     `sow`.`retry_num` AS `retry_num`,     `sow`.`out_warehouse_time` AS `out_warehouse_time`,     `sow`.`purchase_order_no` AS `purchase_order_no`,     `sow`.`purchase_order_id` AS `purchase_order_id`,     `sow`.`wms_order_state` AS `wms_order_state`,     `sow`.`checked_time` AS `checked_time`,     `so`.`creator` AS `creator`,     `so`.`create_time` AS `create_time`,     `so`.`last_updater` AS `last_updater`,     `so`.`last_update_time` AS `last_update_time`,     `so`.`is_usable` AS `is_usable`,     `so`.`tenant_id` AS `tenant_id` FROM     (         (             (                 (                     (                         (                             `sale_order` `so`                             LEFT JOIN `sale_order_receiver` `sor` ON (                                 (                                     `so`.`sale_order_id` = `sor`.`sale_order_id`                                 )                             )                         )                         LEFT JOIN `sale_order_status` `sos` ON (                             (                                 `so`.`sale_order_id` = `sos`.`sale_order_id`                             )                         )                     )                     LEFT JOIN `sale_order_warehouse` `sow` ON (                         (                             `so`.`sale_order_id` = `sow`.`sale_order_id`                         )                     )                 )                 LEFT JOIN `sale_order_courier` `soc` ON (                     (                         `so`.`sale_order_id` = `soc`.`sale_order_id`                     )                 )             )             LEFT JOIN `sale_order_invoice` `soi` ON (                 (                     `so`.`sale_order_id` = `soi`.`sale_order_id`                 )             )         )         LEFT JOIN `sale_order_finance` `sof` ON (             (                 `so`.`sale_order_id` = `sof`.`sale_order_id`             )         )     );

@Service public class OrderService extends TemplateService {      public static final String DEFALUT_FILTER = " AND NOT(is_split = 1 AND archive_state=3) AND NOT(is_combined = 1 AND archive_state=4) " +             " AND NOT(oms_process_type =0) AND (v_sale_order.platform_order_status != 'TRADE_FINISHED' OR origin_type=2) " +             "AND NOT is_invalidated=1" +             " AND NOT archive_state=5 AND NOT archive_state=6";     public static final String HISTORY_FILTER = " AND NOT(is_split = 1 AND archive_state=3) AND NOT(is_combined = 1 AND archive_state=4) " +
　　　　　　　 " AND NOT archive_state=5 AND NOT archive_state=6";  }

@Service public class OrderService extends TemplateService {      /**      订单处理:      过滤掉:合并拆分的订单      过滤掉:交易完成或交易关闭      要求:跑过预处理      要求:已付款或者货到付款      要求:未作废的      */     public static final String DEFALUT_FILTER = " AND archive_state IN (0, 1) AND v_sale_order.is_paid = 1 AND oms_process_type = 1 " +             " AND v_sale_order.is_invalidated=0 AND is_closed = 0";     /**      * 订单查询:      * 过滤掉:合并拆分的订单      */     public static final String HISTORY_FILTER = " AND archive_state IN (0, 1)";     public static final String AFTER_FILTER = " AND archive_state IN (0,1,2) AND is_paid=1";    }

CREATE OR REPLACE VIEW v_sale_order AS   SELECT     `so`.`sale_order_id`             AS `v_sale_order_id`,     `so`.`sale_order_id`             AS `sale_order_id`,     `so`.`sale_order_no`             AS `sale_order_no`,     `so`.`order_type`                AS `order_type`,     `so`.`platform_order_code2`      AS `platform_order_code2`,     `so`.`platform_order_code`       AS `platform_order_code`,     `so`.`platform_type`             AS `platform_type`,     `so`.`platform_order_status`     AS `platform_order_status`,     `so`.`created`                   AS `created`,     `so`.`end_time`                  AS `end_time`,     `so`.`total_num`                 AS `total_num`,     `so`.`total_sku`                 AS `total_sku`,     `so`.`modified`                  AS `modified`,     `so`.`seller_flag`               AS `seller_flag`,     `so`.`seller_memo`               AS `seller_memo`,     `so`.`seller_rate`               AS `seller_rate`,     `so`.`snapshot_url`              AS `snapshot_url`,     `so`.`status`                    AS `status`,     `so`.`step_trade_status`         AS `step_trade_status`,     `so`.`trade_from`                AS `trade_from`,     `so`.`trade_memo`                AS `trade_memo`,     `so`.`trade_source`              AS `trade_source`,     `so`.`type`                      AS `type`,     `so`.`shop_id`                   AS `shop_id`,     `so`.`origin_type`               AS `origin_type`,     `so`.`sys_promotion_info`        AS `sys_promotion_info`,     `sor`.`buyer_area`               AS `buyer_area`,     `sor`.`buyer_email`              AS `buyer_email`,     `sor`.`buyer_ip`                 AS `buyer_ip`,     `sor`.`buyer_memo`               AS `buyer_memo`,     `sor`.`buyer_message`            AS `buyer_message`,     `sor`.`buyer_nick`               AS `buyer_nick`,     `sor`.`buyer_rate`               AS `buyer_rate`,     `sor`.`receiver_address`         AS `receiver_address`,     `sor`.`receiver_city`            AS `receiver_city`,     `sor`.`receiver_country`         AS `receiver_country`,     `sor`.`receiver_district`        AS `receiver_district`,     `sor`.`receiver_mobile`          AS `receiver_mobile`,     `sor`.`receiver_name`            AS `receiver_name`,     `sor`.`receiver_phone`           AS `receiver_phone`,     `sor`.`receiver_state`           AS `receiver_state`,     `sor`.`receiver_town`            AS `receiver_town`,     `sor`.`receiver_zip`             AS `receiver_zip`,     `sor`.`area_id`                  AS `area_id`,     `sor`.`customer_id`              AS `customer_id`,     `soc`.`courier_id`               AS `courier_id`,     `soc`.`courier_order_no`         AS `courier_order_no`,     `soc`.`courier_print_mark_state` AS `courier_print_mark_state`,     `soc`.`courier_print_time`       AS `courier_print_time`,     `sof`.`alipay_id`                AS `alipay_id`,     `sof`.`alipay_no`                AS `alipay_no`,     `sof`.`payment`                  AS `payment`,     `sof`.`total_fee`                AS `total_fee`,     `soi`.`invoice_order_no`         AS `invoice_order_no`,     `soi`.`invoice_content`          AS `invoice_content`,     `soi`.`invoice_type`             AS `invoice_type`,     `soi`.`bank`                     AS `bank`,     `soi`.`title`                    AS `title`,     `soi`.`bank_account`             AS `bank_account`,     `soi`.`tariff_lines`             AS `tariff_lines`,     `sos`.`oms_process_type`         AS `oms_process_type`,     `sos`.`play_state`               AS `play_state`,     `sos`.`pause_state`              AS `pause_state`,     `sos`.`stop_state`               AS `stop_state`,     `sos`.`archive_state`            AS `archive_state`,     `sos`.`is_paid`                  AS `is_paid`,     `sos`.`is_checked`               AS `is_checked`,     `sos`.`is_approved`              AS `is_approved`,     `sos`.`is_suspended`             AS `is_suspended`,     `sos`.`is_invalidated`           AS `is_invalidated`,     `sos`.`is_to_be_shipped`         AS `is_to_be_shipped`,     `sos`.`is_after_sale`            AS `is_after_sale`,     `sos`.`is_split`                 AS `is_split`,     `sos`.`is_combined`              AS `is_combined`,     `sos`.`is_closed`                AS `is_closed`,     `sos`.`is_after_sale_closed`     AS `is_after_sale_closed`,     `sos`.`is_amount_changed`        AS `is_amount_changed`,     `sos`.`is_part_changed`          AS `is_part_changed`,     `sos`.`is_out_of_stock`          AS `is_out_of_stock`,     `sos`.`pay_type`                 AS `pay_type`,     `sos`.`pay_time`                 AS `pay_time`,     `sos`.`original_order_id`        AS `original_order_id`,     `sos`.`after_sale_note`          AS `after_sale_note`,     `sos`.`suspend_note`             AS `suspend_note`,     `sos`.`unapprove_note`           AS `unapprove_note`,     `sos`.`after_sale_type`          AS `after_sale_type`,     `sos`.`blacklist_type`           AS `blacklist_type`,     `sow`.`warehouse_id`             AS `warehouse_id`,     `sow`.`retry_num`                AS `retry_num`,     `sow`.`out_warehouse_time`       AS `out_warehouse_time`,     `sow`.`purchase_order_no`        AS `purchase_order_no`,     `sow`.`purchase_order_id`        AS `purchase_order_id`,     `sow`.`wms_order_state`          AS `wms_order_state`,     `sow`.`checked_time`             AS `checked_time`,     `so`.`creator`                   AS `creator`,     `sos`.`create_time`              AS `create_time`,     `so`.`last_updater`              AS `last_updater`,     `sos`.`last_update_time`         AS `last_update_time`,     `sos`.`is_usable`                AS `is_usable`,     `sos`.`tenant_id`                AS `tenant_id`   FROM ((((((`sale_order_status` `sos`     JOIN `sale_order_receiver` `sor` ON ((`sos`.`sale_order_id` = `sor`.`sale_order_id`))) JOIN     `sale_order` `so` ON ((`so`.`sale_order_id` = `sos`.`sale_order_id`))) JOIN     `sale_order_warehouse` `sow` ON ((`sos`.`sale_order_id` = `sow`.`sale_order_id`))) JOIN     `sale_order_courier` `soc` ON ((`sos`.`sale_order_id` = `soc`.`sale_order_id`))) JOIN     `sale_order_finance` `sof` ON ((`sos`.`sale_order_id` = `sof`.`sale_order_id`))) LEFT JOIN     `sale_order_invoice` `soi` ON ((`sos`.`sale_order_id` = `soi`.`sale_order_id`))) 

--archive_state in()结果太多 走不了索引 CREATE INDEX oms_normal on sale_order_status(tenant_id,is_usable,is_paid,oms_process_type,is_invalidated,is_closed,last_update_time);  CREATE INDEX oms_check on sale_order_status(tenant_id,is_usable,is_paid,oms_process_type,is_invalidated,is_closed, is_checked,last_update_time);  CREATE INDEX oms_suspend on sale_order_status(tenant_id,is_usable,is_paid,oms_process_type,is_invalidated,is_closed, is_suspended,last_update_time);  --sale_order CREATE INDEX shop_idx on sale_order(shop_id,order_type); CREATE INDEX platform_idx on sale_order(platform_order_status,order_type);  --sale_order_warehouse CREATE INDEX warehouse_idx on sale_order_warehouse(warehouse_id);  --sale_order_courier CREATE INDEX courier_idx on sale_order_courier(courier_id);

@Service public class OrderService extends TemplateService {      private static final String isPaySql = " AND (is_paid = 1 OR pay_type = 4 ) ";     /**      * 订单处理:      * 过滤掉:合并拆分的订单      * 过滤掉:交易完成或交易关闭      * 要求:跑过预处理      * 要求:已付款或者货到付款      * 要求:未作废的      */     public static final String DEFALUT_FILTER = " AND archive_state IN (0, 1)  AND oms_process_type = 1 " +             isPaySql + " AND v_sale_order.is_invalidated=0 AND is_closed = 0";     /**      * 订单查询:      * 过滤掉:合并拆分的订单      */     public static final String HISTORY_FILTER = " AND archive_state IN (0, 1)";     public static final String AFTER_FILTER = " AND archive_state IN (0,1,2) " + isPaySql;     }

CREATE INDEX oms_normal_v2 on sale_order_status(tenant_id,is_closed,oms_process_type,is_invalidated,last_update_time);  CREATE INDEX oms_check_v2 on sale_order_status(tenant_id,is_closed,oms_process_type,is_invalidated,is_checked,last_update_time);  CREATE INDEX oms_suspend_v2 on sale_order_status(tenant_id,is_closed,oms_process_type,is_invalidated,is_suspended,last_update_time);