Mysql优化---订单查询优化(1):视图优化+索引创建

35岁的程序员 2018-09-14 17:41:50 ⋅ 122 阅读

作者:聚变归来

https://www.cnblogs.com/victor2302/p/6073821.html


订单的表结构采用了垂直分表的策略,将订单相关的不同模块的字段维护在不同表中

在订单处理这个页面,需要查询各种维度,

因此为了方便查询创建了v_sale_order视图(老版本)

drop view v_sale_order;CREATEVIEW `v_sale_order` ASSELECT
    `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作为索引) 

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sos ref idx_sale_order_id,oms_normal_v2,oms_check_v2,oms_suspend_v2 oms_check_v2 10 const,const,const,const 271 Using where; Using filesort
1 SIMPLE sor ref idx_sale_order_id idx_sale_order_id 9 egenie.sos.sale_order_id 1 NULL
1 SIMPLE soc ref idx_sale_order_id idx_sale_order_id 9 egenie.sos.sale_order_id 1 NULL
1 SIMPLE sof ref idx_sale_order_id idx_sale_order_id 9 egenie.sos.sale_order_id 1 NULL
1 SIMPLE so eq_ref PRIMARY PRIMARY 8 egenie.sos.sale_order_id 1 NULL
1 SIMPLE sow ref idx_sale_order_id idx_sale_order_id 9 egenie.sos.sale_order_id 1 NULL
1 SIMPLE soi ref idx_sale_order_id idx_sale_order_id 9 egenie.sos.sale_order_id 1 NULL

 

 

随后创建的索引(第一版),生效

--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_courierCREATE 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);

 

 

Q&A:

1.MySQL视图可以用索引吗?

我想答案是肯定的,其索引是建立在视图后面的真实表上,而不是建立在视图上.

 

索引是存放在模式(schema)中的一个数据库对象,索引的作用就是提高对表的检索查询速度,索引是通过快速访问的方法来进行快速定位数据,从而减少了对磁盘的读写操作。索引是数据库的一个对象,它不能独立存在,必须对某个表对象进行依赖。

视图就是一个表或多个表的查询结果,它是一张虚拟的表,因为它并不能存储数据。


---------------END----------------

后续的内容同样精彩

长按关注“IT实战联盟”哦



全部评论: 0

    我有话说:

    IntelliJ IDEA 开启很慢,运行不流畅,大项目卡顿?一招配置解决!

    来源:Java面试题精选 一、前言 IDEA默认启动配置主要考虑低配置用户,参数不高(默认最低128m,最高512m),导致启动慢,然后运行也不流畅,这里我们需要优化下启动和运行配置;但是在工作中

    MySql实战篇:写一个简单的存储过程,完成订单定时任务

    前言之前我们分享了MySql的性能优化索引详解等内容,本篇文章主要是针对想要入门MySql存储过程的读者,主要实现的业务是订单库里面的超过30分钟没有支付的订单全部置为失效订单......

    「性能与架构」MySQL 8 查询优化新工具 Explain Analyze

    Explain 是我们常用的查询分析工具,可以对查询语句的执行方式进行评估,给出很多有用的线索。

    MySql实战篇:正确理解并使用MySql索引

    索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统......

    关于MySQL 通用查询日志和慢查询日志分析

    MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能:通用查询日志和慢查询日志。

    iOS TableView性能优化

    TableView的性能优化非常考验开发的基本功,之前做项目实战的时候经常被这个问题困扰

    MySql 8 新特性 - CTE 通用表表达式(先睹为快)

    前言Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。下面和大家一起熟悉下 CTE......

    Java Web实战篇-轻松提高千万级数据库查询效率

    通过优化数据库设计、java后台和数据库优化达到提高千万级数据查询的效率。

    「小米技术」Soar一键优化工具--SQL优化和改写的自动化工具

    SOAR(SQL Optimizer And Rewriter)是一个对SQL进行优化和改写的自动化工具。由小米人工智能与云平台的数据库团队开发与维护。

    代码这样优化,瞬间清爽多了

    今天这篇文章我们又来聊聊代码优化。 隐藏行为细节 在平时的开发过程中,你肯定会碰到这样的业务,比如判断车速是否正常。一般编码如下: float speed = 60f

    今日头条 iOS 安装包大小优化 - 新阶段、新实践

    前言 今日头条 iOS 端从 2016 年起就关注到了安装包大小的问题,并启动了包大小优化。2017 年,我们将当时的经验发表为技术文章 《干货|今日头条iOS端安装包大小优化—思路与实践

    HQChart 1.9444 版本发布, 优化绘图算法

    系统简介 HQChart是国内第1个基于传统PC股票客户端软件(C++)移植到js/py平台的一个项目, 包含K线图图形库及麦语法(分析家语法)指标执行器. 平台支持:js, vue, uniapp

    移动H5前端五大性能优化方案(实战篇)

    移动H5前端五大性能优化方案(实战篇)

    富文本编辑器CKEditor 4.14.1 发布,优化编辑器和表格大小

    CKEditor是新一代的FCKeditor,是一个重新开发的版本,CKEditor是全球最优秀的网页在线文字编辑器之一。

    专业解决 MySQL 查询速度慢与性能差

    什么影响了数据库查询速度?关于数据库性能并不是DBA才关心的事。

    Netty单机百万连接及高性能优化

    关于netty的学习和介绍,可以去github看官方文档,这里良心推荐《netty实战》和《netty权威指南》两本书,前者对于新手更友好,原理和应用都有讲到,多读读会发现很多高性能的优化点。

    抖音品质建设 - iOS启动优化《原理篇》

    前言 启动是 App 给用户的第一印象,启动越慢用户流失的概率就越高,良好的启动速度是用户体验不可缺少的一环。启动优化涉及到的知识点非常多面也很广,一篇文章难以包含全部,所以拆分成两部分:原理和实践

    抖音品质建设 - iOS启动优化《实战篇》

    前言 启动是 App 给用户的第一印象,启动越慢,用户流失的概率就越高,良好的启动速度是用户体验不可缺少的一环。启动优化涉及到的知识点非常多,面也很广,一篇文章难以包含全部,所以拆分成两部分:原理和