您的位置:新葡亰496net > 网络数据库 > mysql语句优化总括,浅谈MySQL索引优化解析

mysql语句优化总括,浅谈MySQL索引优化解析

发布时间:2019-10-05 12:49编辑:网络数据库浏览(154)

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

    mysql优化---订单查询优化:视图优化 索引创建,mysql订单查询

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

    新葡亰496net 1

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

    新葡亰496net 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_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_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);
    

     

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

    mysql语句优化总结(一)

    Sql语句优化和索引

    1.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表为百万级表,在本机执行差不多用2秒左右,我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询,然后根据uuid逐次执行子查询,如果外层表是一个很大的表,我们可以想象查询性能会表现比这个更加糟糕。

    一种简单的优化就是用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

    返回的条数,是笛卡尔积后,符合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;

    2.建立索引,加快查询性能.

    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查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果area,age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引,那么其实相当于创建了(area,age,salary),(area,age),(area)三个索引,这样称为最佳左前缀特性。因此我们在创建复合索引的应该将最常用作限制条件的列放在最左边,依次递减。

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

    只要列中包含有NULL值都将不会被包含在索引中(除非是唯一值的域,可以存在一个NULL),复合索引中只要有一列含有NULL值,那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.

    F.使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在钱10个或者20字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    G.排序的索引问题

    Mysql查询只是用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

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

    A.在我们平时用limit,如:

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

    这样在表数据很少的时候,看不出什么性能问题,倘若到达千万级,如:

    Select * from A order by id limit10000000,10;

    虽然都是只查询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()命令—这个会消耗处理器的处理能力,然后给你仅仅返回一行。

    6.利用limit 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的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的cpu运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用union all而不是union.

    10.避免类型转换

    A.这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换,直接导致mysql无法使用索引。如果非要转型,应该在传入参数上进行转换。

    11.不要在列上进行运算

    A. 如下面:select * fromusers where YEAR(adddate)<2007;将在每个行进行运算,这些导致索引失效进行全表扫描,因此我们可以改成:

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

    12.尽量不要使用NOT IN和<>操作

    A. NOT IN和<>操作都不会使用索引,而是将会进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可以使用id>3 or id <3;如果NOT EXISTS是子查询,还可以尽量转化为外连接或者等值连接,要看具体sql的业务逻辑。

    新葡亰496net,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;

    13.使用批量插入节省交互(最好是使用存储过程)

    A. 尽量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);

    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进行插入,更新或者删除的操作。

    15.对多表关联的查询,建立视图

    A.对多表的关联可能会有性能上的问题,我们可以对多表建立视图,这样操作简单话,增加数据安全性,通过视图,用户只能查询和修改指定的数据。且提高表的逻辑独立性,视图可以屏蔽原有表结构变化带来的影响。

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

    为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还在等啥子?撸起袖子就是干!

    新葡亰496net 3

    案例分析

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

    我们先简单了解一下非关系型数据库和关系型数据库的区别。

    新葡亰496net 4

    MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL,非关系型数据库。它的特点是性能高,扩张性强,模式灵活,在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充,它在数据的一致性,数据的安全性,查询的复杂性问题上和关系型数据库还存在一定差距。

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

    MySQL是关系性数据库中的一种,查询功能强,数据一致性高,数据安全性高,支持二级索引。但性能方面稍逊与MongoDB,特别是百万级别以上的数据,很容易出现查询慢的现象。这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的。

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

     公司ERP系统数据库主要是MongoDB(最接近关系型数据的NoSQL),其次是Redis,MySQL只占很少的部分。现在又重新使用MySQL,归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上,对MySQL的性能分析也就显得格外重要。

     

    我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。

    之前的代码(老版本):

    说明:需要用到的sql已经放在了github上了,喜欢的同学可以点一下star,哈哈。

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

    最基础的sql语句

    mysql语句优化总括,浅谈MySQL索引优化解析。 

    mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
     ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
    | id  | transaction_id   | gross | net | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date     |
     ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
    | 10000 | 81X97310V32236260E |  6.6 | 6.13 |    1 |      10 | ok    | ok        | auto    |      1 | itdragon  | 2017-08-18 17:01:49 |
     ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
    
    mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
    | id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |  33.33 | Using where |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
    

    优化第二步:   订单处理相比订单查询多了很多固定条件,大部分处于sale_order_status表中,但是之前视图的创建方式固定了最左边的表,因此修改视图创建的脚本,如下:

    查询的本身没有任何问题,在线下的测试环境也没有任何问题。可是,功能一旦上线,查询慢的问题就迎面而来。几百上千万的订单,用全表扫描?啊?哼!

    从固定的left join改为 Join

    怎么知道该sql是全表扫描呢?通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示:

    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`))) 
    
    1. id : 查询序列号为1。
    2. select_type : 查询类型是简单查询,简单的select语句没有union和子查询。
    3. table : 表是 itdragon_order_list。
    4. partitions : 没有分区。
    5. type : 连接类型,all表示采用全表扫描的方式。
    6. possible_keys : 可能用到索引为null。
    7. key : 实际用到索引是null。
    8. key_len : 索引长度当然也是null。
    9. ref : 没有哪个列或者参数和key一起被使用。
    10. Extra : 使用了where查询。

    最左边表可根据查询条件动态的变化,(如条件过滤查询sale_order_courier的courier_id字段, where courier_id= xx,并且sale_order_courier的courier_id字段上已经建立了索引,那么explain后第一个查询的表就是sale_order_courier)

     因为数据库中只有三条数据,所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL,全表扫描的性能是最差的,假设数据库中有几百万条数据,在没有索引的帮助下会异常卡顿。

    之前sale_order表始终作为v_sale_order实际查询时的第一个表,而无法走索引

    初步优化:为transaction_id创建索引

    (P.S.本人目前的理解:mysql多表关联查询只有最左边表可以走索引,其余表的索引只能是关联的id作为索引) 

    mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
    mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
     ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
    | id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra |
     ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | 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

    这里创建的索引是唯一索引,而非普通索引。

     

    唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。

     

    普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描,直到将索引文件扫描完为止。(这里没有贴出代码)
    显而易见,const的性能要远高于ref。并且根据业务逻辑来判断,创建唯一索引是合情合理的。

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

    再次优化:覆盖索引

    --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);
    
    mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
     ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
    | id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra    |
     ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | Using index |
     ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
    

     

    这里将select * from 改为了 select transaction_id from 后 Extra 显示 Using index,表示该查询使用了覆盖索引,这是一个非常好的消息,说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。

    由于有新需求需要改造固定的查询sql(第三版)

    根据业务逻辑来的,查询结构返回transaction_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;
        }
    

    场景二,订单管理页面,通过订单级别和订单录入时间排序

    改进:

    业务逻辑:优先处理订单级别高,录入时间长的订单。
     既然是排序,首先想到的应该是order by, 还有一个可怕的 Using filesort 等着你。

    1.将之前的is_paid 移除之前的索引

    最基础的sql语句

    2.调整索引的顺序,移除毫无辨识度的字段

    mysql> explain select * from itdragon_order_list order by order_level,input_date;
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    | id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    

     (第二版)

    首先,采用全表扫描就不合理,还使用了文件排序Using filesort,更加拖慢了性能。

    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在4.1版本之前文件排序是采用双路排序的算法,由于两次扫描磁盘,I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间,但如果数据量太大,buffer的空间不足,会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置,还不如自己乖乖地建索引。

     

    初步优化:为order_level,input_date 创建复合索引

     

    mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
    mysql> explain select * from itdragon_order_list order by order_level,input_date;
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    | id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
     ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
    

    Q&A:

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

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

     

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

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

     

    mysql语句优化总括,浅谈MySQL索引优化解析。创建复合索引后你会惊奇的发现,和没创建索引一样???都是全表扫描,都用到了文件排序。是索引失效?还是索引创建失败?我们试着看看下面打印情况

    mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
    | id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra    |
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | Using index |
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
    

    将select * from 换成了 select order_level,input_date from 后。type从all升级为index,表示(full index scan)全索引文件扫描,Extra也显示使用了覆盖索引。可是不对啊!!!!检索虽然快了,但返回的内容只有order_level和input_date 两个字段,让业务同事怎么用?难道把每个字段都建一个复合索引?

    MySQL没有这么笨,可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

    mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
    | id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra |
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | NULL |
     ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
    

    再次优化:订单级别真的要排序么?

    其实给订单级别排序意义并不大,给订单级别添加索引意义也不大。因为order_level的值可能只有,低,中,高,加急,这四种。对于这种重复且分布平均的字段,排序和加索引的作用不大。

    我们能否先固定 order_level 的值,然后再给 input_date 排序?如果查询效果明显,是可以推荐业务同事使用该查询方式。

    mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
     ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
    | id | select_type | table        | partitions | type | possible_keys    | key         | key_len | ref  | rows | filtered | Extra         |
     ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
    | 1 | SIMPLE   | itdragon_order_list | NULL    | ref | idx_order_levelDate | idx_order_levelDate | 5    | const |  1 |   100 | Using index condition |
     ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
    

    和之前的sql比起来,type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5,说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值,选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。

    上面两个案例只是快速入门,我们需严记一点:优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。

    索引简介

    官方定义:索引(Index) 是帮助MySQL高效获取数据的数据结构。

    大家一定很好奇,索引为什么是一种数据结构,它又是怎么提高查询的速度?我们拿最常用的二叉树来分析索引的工作原理。

    看下面的图片:

    新葡亰496net 5

    创建索引的优势

    1 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。

    2 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则真好降低了排序的成本。

    创建索引的劣势

    1 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。

    2 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

    3 优质索引创建难:索引的创建并非一日之功,也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

    索引分类

    我们常说的索引一般指的是BTree(多路搜索树)结构组织的索引。其中还有聚合索引,次要索引,复合索引,前缀索引,唯一索引,统称索引,当然除了B 树外,还有哈希索引(hash index)等。

    1. 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
    2. 唯一索引:索引列的值必须唯一,但允许有空值
    3. 复合索引:一个索引包含多个列,实际开发中推荐使用

    实际开发中推荐使用复合索引,并且单表创建的索引个数建议不要超过五个

    基本语法:

    创建:

    create [unique] index indexName on tableName (columnName...)
    alter tableName add [unique] index [indexName] on (columnName...)
    

    删除:

    drop index [indexName] on tableName
    

    查看:

    show index from tableName
    

    哪些情况需要建索引:

    1 主键,唯一索引
    2 经常用作查询条件的字段需要创建索引
    3 经常需要排序、分组和统计的字段需要建立索引
    4 查询中与其他表关联的字段,外键关系建立索引

    哪些情况不要建索引:

    1 表的记录太少,百万级以下的数据不需要创建索引
    2 经常增删改的表不需要创建索引
    3 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
    4 频发更新的字段不适合创建索引
    5 where条件里用不到的字段不需要创建索引

    性能分析

    MySQL 自身瓶颈

    MySQL自身参见的性能问题有磁盘空间不足,磁盘I/O太大,服务器硬件性能低。
    1 CPU:CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
    2 IO:磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候
    3 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态

    explain 分析sql语句

    使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL 是如何处理sql语句。

     ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
     ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 
    

    id

    select 查询的序列号,包含一组可以重复的数字,表示查询中执行sql语句的顺序。一般有三种情况:
     第一种:id全部相同,sql的执行顺序是由上至下;
     第二种:id全部不同,sql的执行顺序是根据id大的优先执行;
     第三种:id既存在相同,又存在不同的。先根据id大的优先执行,再根据相同id从上至下的执行。

    select_type

    select 查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询
    simple:简单的select 查询,查询中不包含子查询或者union
    primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary
    subquery:在select或where 列表中包含了子查询
    derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
    union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived
    union result:从union表获取结果的select

    partitions

    表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。

    type

    这是一个非常重要的参数,连接类型,常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。
     性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all
    对java程序员来说,若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
    all:(full table scan)全表扫描无疑是最差,若是百万千万级数据量,全表扫描会非常慢。
    index:(full index scan)全索引文件扫描比all好很多,毕竟从索引树中找数据,比从全表中找数据要快。
    range:只检索给定范围的行,使用索引来匹配行。范围缩小了,当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。
    ref:非唯一性索引扫描,本质上也是一种索引访问,返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事,匹配的结果是多个并非唯一值。
    eq_ref:唯一性索引扫描,对于每个索引键,表中有一条记录与之匹配。比如查询公司的CEO,匹配的结果只可能是一条记录,
    const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中,MySQL就能将该查询转换为一个常量。
    system:表只有一条记录(等于系统表),这是const类型的特列,平时不会出现,了解即可

    possible_keys

    显示查询语句可能用到的索引(一个或多个或为null),不一定被查询实际使用。仅供参考使用。

    key

    显示查询语句实际使用的索引。若为null,则表示没有使用索引。

    key_len

    显示索引中使用的字节数,可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

    ref

    显示索引的哪一列或常量被用于查找索引列上的值。

    rows

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,值越大越不好。

    extra

    Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
    Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
    Using index: 表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。
     覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。
    Using index condition: 在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
    Using where: 表明使用了where 过滤
    Using join buffer: 表明使用了连接缓存
    impossible where: where 语句的值总是false,不可用,不能用来获取任何元素
    distinct: 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

    filtered

    一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。

    通过explain的参数介绍,我们可以得知:
    1 表的读取顺序(id)
     2 数据读取操作的操作类型(type)
     3 哪些索引被实际使用(key)
     4 表之间的引用(ref)
     5 每张表有多少行被优化器查询(rows)

    性能下降的原因

    从程序员的角度
    1 查询语句写的不好
    2 没建索引,索引建的不合理或索引失效
    3 关联查询有太多的join

    从服务器的角度
    1 服务器磁盘空间不足
    2 服务器调优配置参数设置不合理

    总结

    1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
    2 创建索引后,查询数据变快,但更新数据变慢。
    3 性能下降的原因很可能是索引失效导致。
    4 索引创建的原则,经常查询的字段适合创建索引,频繁需要更新的数据不适合创建索引。
    5 索引字段频繁更新,或者表数据物理删除容易造成索引失效。
    6 擅用 explain 分析sql语句
    7 除了优化sql语句外,还可以优化表的设计。如尽量做成单表查询,减少表之间的关联。设计归档表等。

    到这里,MySQL的索引优化分析就结束了,有什么不对的地方,大家可以提出来。如果觉得不错可以点一下推荐。

    以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

    您可能感兴趣的文章:

    • MySQL 索引分析和优化
    • MySQL索引背后的之使用策略及优化(高性能索引策略)
    • Mysql使用索引实现查询优化
    • MySQL中索引优化distinct语句及distinct的多字段操作
    • Mysql性能优化案例 - 覆盖索引分享

    本文由新葡亰496net发布于网络数据库,转载请注明出处:mysql语句优化总括,浅谈MySQL索引优化解析

    关键词: