记一次sql优化调整

表结构

CREATE TABLE `orderformrookie` (
  `ID` BIGINT(20) NOT NULL,
  `CAINIAO_ID` BIGINT(20) NOT NULL COMMENT 'cainiaowaybilldetail的ID',
  `ORDER_ID` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '订单号',
  `SYSORDER_ID` VARCHAR(50) NOT NULL COMMENT '系统订单号(面单类型+id组成)',
  `BARCODE_ID` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '运单号',
  `SELLER_ID` BIGINT(50) DEFAULT NULL COMMENT '商家ID',
  `SEND_SITE` VARCHAR(50) DEFAULT NULL COMMENT '发件网点',
  `SEND_NAME` VARCHAR(50) DEFAULT NULL COMMENT '发件人',
  `SEND_MOBILE` VARCHAR(255) DEFAULT NULL COMMENT '发件人手机',
  `SEND_ADDRESS` VARCHAR(600) DEFAULT NULL COMMENT '发件人地址',
  `SEND_PROVINCE_NAME` VARCHAR(255) DEFAULT NULL,
  `SEND_CITY_NAME` VARCHAR(255) DEFAULT NULL,
  `SEND_AREA_NAME` VARCHAR(255) DEFAULT NULL,
  `SEND_TOWN_NAME` VARCHAR(255) DEFAULT NULL,
  `SEND_DETAIL_ADDRESS` VARCHAR(512) DEFAULT NULL,
  `RECEIVE_SITE` VARCHAR(50) DEFAULT NULL COMMENT '收件网点',
  `RECEIVE_NAME` VARCHAR(50) DEFAULT NULL COMMENT '收件人姓名',
  `RECEIVE_PHONE` VARCHAR(50) DEFAULT NULL COMMENT '收件人电话',
  `RECEIVE_ADDRESS` VARCHAR(600) DEFAULT NULL COMMENT '收件人地址',
  `RECEIVE_PROVINCE_NAME` VARCHAR(255) DEFAULT NULL,
  `RECEIVE_CITY_NAME` VARCHAR(255) DEFAULT NULL,
  `RECEIVE_AREA_NAME` VARCHAR(255) DEFAULT NULL,
  `RECEIVE_TOWN_NAME` VARCHAR(255) DEFAULT NULL,
  `RECEIVE_DETAIL_ADDRESS` VARCHAR(512) DEFAULT NULL,
  `WEIGHT` BIGINT(20) DEFAULT NULL COMMENT '总重量',
  `VOLUME` BIGINT(20) DEFAULT NULL COMMENT '总体积',
  `REGION_PLACE` VARCHAR(200) DEFAULT NULL COMMENT '大笔',
  `GATHER_PLACE` VARCHAR(200) DEFAULT NULL COMMENT '集包地',
  `CREATE_TIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '下单时间',
  `MODIFY_TIME` DATETIME DEFAULT NULL COMMENT '更新时间',
  `ORDER_STATUS` INT(15) DEFAULT '0' COMMENT '订单类型，默认0未处理，-1正常(已有轨迹)，1预回收，2已回收',
  `SEND_STATUS` INT(15) DEFAULT '0' COMMENT '是否推送过的 0未推送，1已推送',
  `GIS_STATUS` INT(1) DEFAULT '0' COMMENT '是否发送给gis,0:未发送,1:已发送,2:已接收,状态1是为gis批量接口准备',
  `ORDER_FORM_ITEM` VARCHAR(500) DEFAULT NULL COMMENT '商品信息列表',
  `PRODUCT_NAME` VARCHAR(255) DEFAULT NULL COMMENT '商品名称(只保存第一个订单)',
  `PRODUCT_COUNT` BIGINT(19) DEFAULT NULL COMMENT '商品数量(只保存第一个订单)',
  `TRACK_STATUS` INT(1) DEFAULT '0' COMMENT '运单轨迹状态,0:菜鸟下单,5:揽件,6:运输中,7:签收,8:运单回收',
  `MSG_VERSION` BIGINT(19) DEFAULT NULL COMMENT '面单版本号（消息异常乱序时使用，新消息的版本号大于旧消息）',
  `ACTIVE` INT(15) DEFAULT '1' COMMENT '是否活跃(0:不活跃,1:活跃)',
  `IS_UPDATE` VARCHAR(2) DEFAULT 'N' COMMENT '是否是更新数据(更新数据需走更新补录接口)',
  `RECOVER_TIME` DATETIME DEFAULT NULL COMMENT '回收时间',
  `SITE_WEIGHT` DOUBLE(20,2) DEFAULT '0.00' COMMENT '网点重量',
  `BALANCE_WEIGHT` DOUBLE(20,2) DEFAULT '0.00' COMMENT '结算重量',
  `MATCH_MAP_TYPE` INT(5) DEFAULT '0' COMMENT '网点匹配类型,-1:盲区,无效地址,-11:其他',
  PRIMARY KEY (`ID`,`CREATE_TIME`),
  KEY `UK_BARCODE_ID` (`BARCODE_ID`),
  KEY `ORDER_ID` (`ORDER_ID`) USING BTREE,
  KEY `SEND_STATUS` (`SEND_STATUS`) USING BTREE,
  KEY `TRACK_STATUS` (`TRACK_STATUS`) USING BTREE,
  KEY `INDEX_CREATETIME_SITEID` (`CREATE_TIME`,`SEND_SITE`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='菜鸟订单表'
/*!50100 PARTITION BY RANGE ( TO_DAYS(create_time))
(PARTITION p180515 VALUES LESS THAN (737194) ENGINE = InnoDB,
 PARTITION p180530 VALUES LESS THAN (737209) ENGINE = InnoDB,
 PARTITION p180615 VALUES LESS THAN (737225) ENGINE = InnoDB,
 PARTITION p180630 VALUES LESS THAN (737240) ENGINE = InnoDB,
 PARTITION p180715 VALUES LESS THAN (737255) ENGINE = InnoDB,
 PARTITION p180730 VALUES LESS THAN (737270) ENGINE = InnoDB,
 PARTITION p180815 VALUES LESS THAN (737286) ENGINE = InnoDB,
 PARTITION p180830 VALUES LESS THAN (737301) ENGINE = InnoDB,
 PARTITION p180915 VALUES LESS THAN (737317) ENGINE = InnoDB,
 PARTITION p180930 VALUES LESS THAN (737332) ENGINE = InnoDB,
 PARTITION p181015 VALUES LESS THAN (737347) ENGINE = InnoDB,
 PARTITION p181030 VALUES LESS THAN (737362) ENGINE = InnoDB,
 PARTITION p181115 VALUES LESS THAN (737378) ENGINE = InnoDB,
 PARTITION p181130 VALUES LESS THAN (737393) ENGINE = InnoDB,
 PARTITION p181215 VALUES LESS THAN (737408) ENGINE = InnoDB,
 PARTITION p181230 VALUES LESS THAN (737423) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

SQL脚本：

SELECT t1.id,
            t1.order_id,
            t1.sysorder_id,
            t1.barcode_id,
            t1.seller_id,
            t1.send_site,
            t1.send_name,
            t1.send_mobile,
            t1.send_address,
            t1.receive_site,
            t1.receive_name,
            t1.receive_phone,
            t1.receive_address,
            t1.region_place,
            t1.gather_place,
            t1.create_time,
            t1.active,
            t1.track_status,
            t3.client_name,
            t3.client_encode,
            t3.site_name AS sendsitename,
            t4.first_center_site_name,
            t4.org_name AS receivesitename FROM
                (SELECT id,
                order_id,
                sysorder_id,
                barcode_id,
                seller_id,
                send_site,
                send_name,
                send_mobile,
                send_address,
                receive_site,
                receive_name,
                receive_phone,
                receive_address,
                region_place,
                gather_place,
                create_time,
                active,
                track_status
                FROM
                orderformrookie
                WHERE
                    CREATE_TIME >= '2018-08-01 00:00:00' AND CREATE_TIME <= '2018-08-02 23:59:59'
                     AND SEND_SITE = '15992'
                ) t1
        LEFT JOIN  (
        SELECT DISTINCT (b.USER_ID),b.BUSINESS_CODE,b.SITE_ID FROM businesses b
        WHERE (b.STATE = 1 OR b.STATE = 4) AND b.SITE_ID = 15992
        ) t2 ON t1.SELLER_ID = t2.BUSINESS_CODE AND t1.SEND_SITE = t2.SITE_ID
        LEFT JOIN sysuser t3 ON t2.USER_ID=t3.ID
        LEFT JOIN siteinfo t4 ON t1.RECEIVE_SITE = t4.BIZ_ID AND t4.STATE=1
        ORDER BY t1.id DESC

explain结果：

执行结果：

解决方法：

首先强制使用索引


似乎好了一些，个人认为应该还有优化的空间。

继续优化，如果单独使用createtime字段条件的话，非常快，因为create_time是分区键值：

如果加上site_id条件则立马性能下来了。

再继续进行调整，为site_id单独加一个索引：
ALTER TABLE orderformrookie ADD INDEX idx_send_site (SEND_SITE);
重新执行sql的执行计划

直接用到索引定位数据，再执行一下sql：

到此，优化完成，然后删除无用索引
ALTER TABLE orderformrookie DROP INDEX INDEX_CREATETIME_SITEID;

总结：

使用分区表，分区键不需要单独建立索引，直接通过分区键来检索的话，速度非常快，但是当如果加入另一个条件进行检索的话，如果没有索引，则仍然会进行全表扫描操作，故此单独为该条件字段加一个索引，在sql中就会直接引用了。因为这个表是前几天从普通标改为分区表的，当时这个索引INDEX_CREATETIME_SITEID是普通表里使用的，当表变成分区表的时候，那么这个索引就属于废弃索引，不能被使用了，需要重新创建一个索引即可。