訂單的表結構采用了垂直分表的策略,將訂單相關的不同模塊的字段維護在不同表中
在訂單處理這個頁面,需要查詢各種維度,
因此為了方便查詢創建了v_sale_order視圖(老版本)
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"; }
DEFAULT_FILTER是訂單處理裡面,固定的查詢條件,每次查詢都會有該部分條件,但是sql的寫法包含了太多OR,NOT,!= 等操作
優化第一步: 根據業務規則合並一些字段,將一些排除條件改為正向命中的條件(第二版):
@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"; }
優化第二步: 訂單處理相比訂單查詢多了很多固定條件,大部分處於sale_order_status表中,但是之前視圖的創建方式固定了最左邊的表,因此修改視圖創建的腳本,如下:
從固定的left join改為 Join
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`)))
最左邊表可根據查詢條件動態的變化,(如條件過濾查詢sale_order_courier的courier_id字段, where courier_id= xx,並且sale_order_courier的courier_id字段上已經建立了索引,那麼explain後第一個查詢的表就是sale_order_courier)
之前sale_order表始終作為v_sale_order實際查詢時的第一個表,而無法走索引
(P.S.本人目前的理解:mysql多表關聯查詢只有最左邊表可以走索引,其余表的索引只能是關聯的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);
由於有新需求需要改造固定的查詢sql(第三版)
@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; }
改進:
1.將之前的is_paid 移除之前的索引
2.調整索引的順序,移除毫無辨識度的字段
(第二版)
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);