订单查询优化,视图优化

订单的表结构选拔了僵直分表的战略,将订单相关的两样模块的字段维护在分裂表中

订单的表结构选拔了垂直分表的国策,将订单相关的两样模块的字段维护在分歧表中

mysql优化—订单查询优化:视图优化+索引创设,mysql订单查询

订单的表结构选取了僵直分表的政策,将订单相关的差别模块的字段维护在分化表中

997755.com澳门葡京 1

在订单处理那几个页面,须求查询各类维度,

997755.com澳门葡京 2

故而为了有利于查询创立了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_FILTEENCORE是订单处理之中,固定的询问条件,每一次查询都会有该部分条件,可是sql的写法包含了太多O瑞鹰,NOT,!=
等操作

优化第叁步:  依照工作规则统1一些字段,将有些革除规则改为正向命中的条件(第一版):

@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_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;
    }

改进:

壹.将在此之前的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);

 

订单的表结构选拔了垂直分表的国策,将订单相关的差异模块的字段维护在差别…

mysql语句优化总括(①)

Sql语句优化和目录

一.Innerjoin和左连接,右连接,子查询

A. inner join内连接也叫等值连接是,left/rightjoin是外接连。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

通过来之多地方的辨证inner join品质比较快,因为inner
join是等值连接,或然重临的行数比较少。但是大家要记得有个别语句隐形的使用了等值连接,如:

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推介:能用inner join连接尽量利用inner join连接

B.子查询的质量又比外连接属性慢,尽量用外连接来替换子查询。

Select* from A where exists (select * from B where id>=3000 and
A.uuid=B.uuid);

A表的多少为八万级表,B表为百万级表,在本机实行差不多用二秒左右,我们得以通过explain能够查看到子查询是一个相关子查询(DEPENDENCE
SUBQUERY);Mysql是先对外表A施行全表查询,然后根据uuid逐次奉行子查询,就算外层表是2个极大的表,大家能够设想查询质量会显现比那一个特别不佳。

1种简易的优化就是用innerjoin的格局来替代子查询,查询语句改为:

Select* from A inner join B using(uuid) where b.uuid>=3000;

订单查询优化,视图优化。以此讲话试行测试不到壹秒;

C.在行使ON 和 WHERE 的时候,记得它们的种种,如:

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id WHERE
B.NAME=’XXX’

施行进程会先实行ON
后边先过滤掉B表的片段行数。然则WHERE是后再过滤他们多个三番五次发生的记录。

而是在那边提示一下大家:ON前面包车型地铁准绳只可以过滤出B表的条数,不过接连重临的记录的行数照旧A表的行数是壹律。如:

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

回去的记录数是A表的条数,ON前边的尺码只起到过滤B表的记录数,而

SELECT A.id,A.name,B.id,B.name FROM A ,B WHERE A.id = B.id

回来的条数,是笛Carl积后,符合A.id = B.id这么些规格的记录

D.使用JOIN时候,应该用小的结果驱动打客车结果(left join
左边表结果尽量小,就算有标准应该放权左边先拍卖,right
join同理反向),同事尽量把牵涉到多表联合的询问拆分多少个query(三个表查询作用低,轻巧锁表和堵塞)。如:

Select * from A left join B ona.id=B.ref_id where B.ref_id>10;

能够优化为:select * from (select * from A wehre id >10) T1 left
join B onT1.id=B.ref_id;

贰.手无寸铁目录,加快查询品质.

A.在建立复合索引的时候,在where条件中用到的字段在复合索引中,则最佳把那么些字段放在复合索引的最左端,那样技艺动用索引,工夫增高查询。

B.有限帮忙连接的目录是同样的体系,意思便是A表和B表相关联的字段,必须是同类型的。那么些项目都创造了目录,这样技巧四个表都能使用索引,借使类型不均等,至少有一个表使用持续索引。

C.索引,不仅仅是主键和唯壹键,也足以是其余的任何列。在应用like个中多个有目录的字段列的时候。

如: select *from A name like ‘xxx%’;

以此sql会利用name的目录(前提name建立了目录);而上边包车型大巴讲话就使用持续索引

Select * from A name like ‘%xxx’;

因为‘%’代表任何字符,%xxx不明了怎么去索引的,所以利用持续索引。

D.复合索引

诸如有一条语句那样的:select* from users where area =’beijing’ and
age=22;

万一我们是在area和age上个别创建索引的话,由于mysql查询每回只可以利用2个目录,所以即使这么已经相对不做索引时全表扫描提升了过多频率,可是1旦area,age两列上创建复合索引的话将推动更加高的频率。假设我们成立了(area,age,salary)的复合索引,那么实际上一定于创立了(area,age,salary),(area,age),(area)四个目录,那样称呼一流左前缀天性。因而大家在创建复合索引的应有将最常用作限制标准的列放在最左边,依次递减。

E.索引不会含有有NULL值的列

只要列中包蕴有NULL值都将不会被含有在目录中(除非是唯1值的域,可以存在1个NULL),复合索引中假若有一列含有NULL值,那么这壹列对于此复合索引是无济于事的。所以大家在数据库设计时毫无让字段的默许值为NULL.

F.使用短索引

对串列举行索引,假若恐怕应该内定1个前缀长度。例如,假使有一个CHA大切诺基(25伍)的列,倘诺在钱11个可能20字符内,多数值是唯一的,那么就不要对整个列实行索引。短索引不仅能够加强查询速度而且能够节约磁盘空间和I/O操作。

G.排序的目录难点

Mysql查询只是用2个目录,由此一旦where子句中已经使用了目录的话,那么order
by中的列是不会利用索引的。因而数据库默许排序能够符合须求景况下毫不采纳排序操作;尽量不要包涵八个列的排序,假若急需最佳给那么些列创制复合索引。

三.limit千万级分页的时候优化。

A.在大家平常用limit,如:

Select * from A order by id limit 1,10;

如此那般在表数据很少的时候,看不出什么性质难题,倘使达到千万级,如:

Select * from A order by id limit10000000,10;

固然如此都以只询问拾记录,不过那几个就质量就令人受不了了。所以怎么当表数据非常的大的时候,我们还承袭用持久层框架如hibernate,ibatis就会有一对质量难题,除非持久层框架对那么些大数据表做过优化。

B.在遭受上边的情事,大家得以用此外壹种语句优化,如:

Select * from A where id>=(Select idfrom a limit 10000000,1) limit
10;

确实那样快了广大,不过前提是,id字段建立了目录。恐怕这些还不是最优的,其实还足以这么写:

Select * from A where id between 10000000and 10000010;

如此的频率进一步高。

4.尽量防止Select * 命令

A.从表中读取更加多的数目,查询会变得更加慢。它会扩张磁盘的操作时间,依然在数据库服务器与web服务器是独立分开的气象下,你将会经历十分悠久的网络延迟。仅仅是因为数量不须求的在服务器之间传输。

5.尽量绝不采用BY RAND()命令

A.若是您真须要自由展现你的结果,有众多更加好的门路实现。而那个函数恐怕会为表中每一个单独的行推行BY
RAND()命令—那么些会损耗处理器的拍卖能力,然后给您1味重临1行。

六.选择limit 1赚取唯1行

A.有时要询问一张表时,你要了然须求看一行,你恐怕去询问一条优秀的笔录。你可以动用limit
1.来终止数据库引擎继续扫描整个表或许索引,如:

Select * from A where namelike ‘%xxx’ limit 1;

如此这般倘诺查询符合like ‘%xxx’的记录,那么引擎就不会一连扫描表或许索引了。

7.尽量少排序

A.排序操作会消耗较多的CPU能源,所以裁减排序能够在缓存命中率高档

8.尽量少OR

A.当where子句中存在多个标准以“或”并存的时候,Mysql的优化器并从未很好的缓解其施行布置优化难点,再拉长mysql特有的sql与Storage分层架构方式,形成了其品质相比地下,大多时候使用union
all也许union(须要的时候)的主意替代“or”会获取越来越好的功用。

9.尽量用union all 代替union

A.union和union
all的差异首假设前者须求将四个(恐怕多少个)结果集合并后再张开唯1性过滤操作,那就会提到到排序,扩张大气的cpu运算,加大财富消耗及推迟。所以当大家得以料定不或许出现重复结果集恐怕不在乎重复结果集的时候,尽量接纳union
all而不是union.

997755.com澳门葡京,10.幸免类型调换

A.那里所说的“类型转变”是指where子句中冒出column字段的档次和扩散的参数类型不等同的时候发出的类型调换。人为的上经过转移函数进行调换,直接促成mysql不可能采取索引。假诺非要转型,应该在传播参数上进展退换。

1一.决不在列上进行演算

A. 如下面:select * fromusers where
YEA帕杰罗(adddate)<200七;将要种种行举办演算,这一个导致索引失效实行全表扫描,由此大家得以改成:

Select * from users where adddate<’2007-01-01’;

1二.尽量决不使用NOT IN和<>操作

A. NOT IN和<>操作都不会选择索引,而是将会议及展览开全表扫描。NOT
IN能够NOT EXISTS代替,id<>3则能够运用id>3 or id <3;假若NOT
EXISTS是子查询,还足以尽量转化为外接连大概等值连接,要看具体sql的政工逻辑。

B.把NOT IN转化为LEFT JOIN如:

SELECT * FROM customerinfo WHERE CustomerIDNOT in (SELECT CustomerID
FROM salesinfo );

优化:

SELECT * FROM customerinfo LEFT JOINsalesinfoON
customerinfo.CustomerID=salesinfo. CustomerID WHEREsalesinfo.CustomerID
IS NULL;

一三.选用批量布署节省交互(最好是运用存款和储蓄进度)

A. 尽量使用insert intousers(username,password) values(‘test一’,’pass一’),
(‘test二’,’pass二’), (‘test3’,’pass叁’);

14. 锁定表

A.
就算工作是保证数据库完整性的三个极度好的艺术,但却因为它的独占性,有时会影响数据库的性质,特别是在众多的运用体系中.由于业务试行的经过中,数据库将会被锁定,因而别的的用户请求只好暂且等候直到该事务付钱.借使一个数据库系统唯有少数几个用户来利用,事务形成的熏陶不会化为三个太大难题;但要是有数不尽的用户同时做客三个数据库系统,例如访问二个电子商务网址,就会生出比较严重的响应延迟.其实有个别情形下大家得以透过锁定表的艺术来获得更加好的质量.如:

LOCK TABLE inventory write

Select quanity from inventory whereitem=’book’;

Update inventory set quantity=11 whereitem=’book’;

UNLOCK TABLES;

此间,大家用多个select语句收取开端数据,通过一些计量,用update语句将新值更新到列表中。包蕴有write关键字的LOCK
TABLE语句能够确定保证在UNLOCK
TABLES命令被实行此前,不会有其它的拜会来对inventory实行插队,更新大概去除的操作。

一5.对多表关联的询问,建立视图

A.对多表的涉及大概会有总体性上的难点,我们得以对多表建立视图,那样操作简便话,增扩大少安全性,通过视图,用户只好查询和修改钦点的数量。且拉长表的逻辑独立性,视图能够屏蔽原有表结构转换带来的熏陶。

Sql语句优化和索引
1.Innerjoin和左连接,右连接,子查询 A. inner
join内一连也叫等一连是,left/rightjoin是外接连。 S…

997755.com澳门葡京 3

997755.com澳门葡京 4

在订单处理这些页面,供给查询各样维度,

在订单处理那么些页面,须要查询种种维度,

997755.com澳门葡京 5

997755.com澳门葡京 6

为此为了便利查询创造了v_sale_order视图(老版本)

因此为了便于查询成立了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`
            )
        )
    );
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 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_FILTE翼虎是订单处理之中,固定的查询条件,每一回查询都会有该部分条件,然而sql的写法包涵了太多O奔驰M级,NOT,!=
等操作

DEFAULT_FILTETiguan是订单处理个中,固定的询问条件,每一遍查询都会有该有的条件,可是sql的写法包括了太多O兰德Highlander,NOT,!=
等操作

优化第2步:  根据工作规则统一壹些字段,将部分免除规则改为正向命中的条件(第三版):

优化第二步:  依据作业规则统11些字段,将一些免去规则改为正向命中的条件(第二版):

@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";
   }
@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表中,可是在此之前视图的成立格局固定了最左边的表,因而修改视图创设的本子,如下:

优化第一步:  
订单处理比较订单查询多了好些个定点条件,当先拾1分之5介乎sale_order_status表中,可是以前视图的创始方式固定了最左侧的表,因而修改视图创设的脚本,如下:

从一定的left join改为 Join

从一定的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`))) 
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_courier的courier_id字段,
where courier_id=
xx,并且sale_order_courier的courier_id字段上一度确立了目录,那么explain后第三个查询的表就是sale_order_courier)

之前sale_order表始终作为v_sale_order实际查询时的第四个表,而不可超过走索引

之前sale_order表始终作为v_sale_order实际查询时的率先个表,而高不可攀走索引

(P.S.自身如今的接头:mysql多表关联查询只有最右边表可以走索引,其他表的目录只可以是涉嫌的id作为目录) 

(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
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

 

 

 

 

跟着创办的目录(第三版),生效

紧接着创办的目录(第3版),生效

--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);
--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(第三版)

鉴于有新供给必要改换一定的查询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;
    }
@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;
    }

改进:

改进:

①.将事先的is_paid 移除以前的目录

一.将事先的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);
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)中的一个数据库对象,索引的意义便是增长对表的搜求查询速度,索引是因而连忙访问的主意来打开火速稳固数据,从而减弱了对磁盘的读写操作。索引是数据库的三个指标,它不可能独立存在,必须对有个别表对象实行注重。

视图正是一个表或多个表的询问结果,它是一张虚拟的表,因为它并无法积存数据。

 

Q&A:

1.MySQL视图能够用索引吗?

自己想答案是必然的,其索引是起家在视图后边的忠实表上,而不是手无寸铁在视图上.

 

索引是存放在在形式(schema)中的贰个数据库对象,索引的遵循正是加强对表的探索查询速度,索引是经过急迅访问的不2秘技来实行神速稳定数据,从而缩短了对磁盘的读写操作。索引是数据库的三个对象,它不可能独立存在,必须对有个别表对象举行正视。

视图便是1个表或多少个表的询问结果,它是一张虚拟的表,因为它并不能积存数据。

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website