526互联

记录一次MySQL多表查询,order by不走索引的情况.

发布时间 2023-11-29 15:50:59作者: 火星大能猫

首先是表结构,部分字段脱敏已删除

 

CREATE TABLE `log_device_heart` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `device_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `time_periods_begin` datetime NOT NULL,
  `time_periods_end` datetime DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `num` int DEFAULT NULL,
  `other_num` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `device_number` (`device_number`) USING BTREE,
  KEY `time_periods_begin_desc` (`time_periods_begin` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=1168466 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `m_device` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '仪器类型',
  `number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仪器编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仪器名称',
  `organization` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '机构',
  `dealer` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '经销商',
 
  `area` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '区县',
  `engineer_id` int DEFAULT NULL COMMENT '工程师id',
  `update_date` datetime DEFAULT NULL,
  `maintain_date` datetime DEFAULT NULL COMMENT '保养时间',
  `dealer_id` int DEFAULT NULL COMMENT '经销商id',
  `organization_id` int DEFAULT NULL COMMENT '机构id',
  `socket_heart_last_time` datetime DEFAULT NULL COMMENT 'socket最后一次心跳时间',
  `flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '标志位，0:未填写地址；1:已填写地址',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `fk_device` (`engineer_id`) USING BTREE,
  KEY `index_m_device_number` (`number`) USING BTREE,
  KEY `organization_id` (`organization_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4615 DEFAULT CHARSET=utf8;

CREATE TABLE `m_organization` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `deleted_flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志',
  `dealer_id` int DEFAULT NULL COMMENT '经销商id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10066 DEFAULT CHARSET=utf8;

 

 

其中log_device_heart大概200w条数据,其它表都在200条左右
这是实际业务中的查询条件,这个项目是后续接手的,所以是不熟悉的Java项目

<select id="findDeviceHeartList" resultType="com.egs.biz.entity.vo.rsp.DeviceLogRsp$HeartRsp" parameterType="com.egs.biz.entity.vo.req.DeviceLogReq">
        SELECT
        t3.`name` as orgName,t1.num,t1.other_num as otherNum,
        t1.device_number as deviceNumber,t1.time_periods_begin as timePeriodsBeginStr,
        t1.time_periods_end as timePeriodsEndStr,t1.create_time as  createTimeStr,t1.date as updateTimeStr
        FROM log_device_heart t1  FORCE INDEX (time_periods_begin_desc)
        left join m_device t2
        on t1.device_number=t2.number
        left join m_organization t3
        on t3.id=t2.organization_id
        WHERE 1=1
        <if test="heartReq.organization!=null and heartReq.organization!=''">
            and t3.`name` like '%${heartReq.organization}%'
        </if>
        <if test="heartReq.dataCleanFlag==true">
            and organization_id<>'10007' and organization not like '%测试机器%'
        </if>
        <if test="heartReq.deviceNumber!=null and heartReq.deviceNumber!=''">
            and t1.device_number=#{heartReq.deviceNumber}
        </if>
        <if test="heartReq.heart4GNum!=null and heartReq.heart4GNum!=''">
            and t1.num < #{heartReq.heart4GNum}
        </if>
        <if test="heartReq.startDate!=null">
            and t1.time_periods_begin >= DATE_FORMAT(#{heartReq.startDate},'%Y-%m-%d 00:00:00')
        </if>
        <if test="heartReq.endDate!=null">
            and t1.time_periods_begin  <= DATE_FORMAT(#{heartReq.endDate},'%Y-%m-%d 23:59:59')
        </if>
        order by t1.time_periods_begin desc
    </select>

 

 

1 常规查询写法分析

 

select t3.`name` as orgName,t1.num,t1.other_num as otherNum,
t1.device_number AS deviceNumber,
t1.time_periods_begin AS timePeriodsBeginStr,
t1.time_periods_end AS timePeriodsEndStr,
t1.create_time AS createTimeStr,
t1.date AS updateTimeStr 
FROM
    log_device_heart t1 -- use index(time_periods_begin_desc)
    STRAIGHT_JOIN m_device t2 ON t1.device_number = t2.number
    LEFT JOIN m_organization t3 ON t3.id = t2.organization_id 
WHERE
    1 = 1 
ORDER BY
    t1.time_periods_begin DESC 
    LIMIT 10;

查询耗时3秒左右,查询计划显示驱动表并没有走索引,

 200多w的主表数据,显然是无法接受这个结果的.

于是加上force index

 

SELECT
    t3.`name` AS orgName,
    t1.num,
    t1.other_num AS otherNum,
    t1.device_number AS deviceNumber,
    t1.time_periods_begin AS timePeriodsBeginStr,
    t1.time_periods_end AS timePeriodsEndStr,
    t1.create_time AS createTimeStr,
    t1.date AS updateTimeStr 
FROM
    log_device_heart t1  force INDEX ( time_periods_begin_desc )
    LEFT JOIN m_device t2 ON t1.device_number = t2.number
    LEFT JOIN m_organization t3 ON t3.id = t2.organization_id 
WHERE
    1 = 1 
ORDER BY
    t1.time_periods_begin DESC 
    LIMIT 10
> OK
> 时间: 0.001s

查询耗时可以忽略,提升了1000多倍.

但这会带来另一个问题,当我where条件添加筛选的时候,强制索引会导致效率降低,如下:

SELECT
    t3.`name` AS orgName,
    t1.num,
    t1.other_num AS otherNum,
    t1.device_number AS deviceNumber,
    t1.time_periods_begin AS timePeriodsBeginStr,
    t1.time_periods_end AS timePeriodsEndStr,
    t1.create_time AS createTimeStr,
    t1.date AS updateTimeStr 
FROM
    log_device_heart t1  force INDEX ( time_periods_begin_desc )
    LEFT JOIN m_device t2 ON t1.device_number = t2.number
    LEFT JOIN m_organization t3 ON t3.id = t2.organization_id 
WHERE
    1 = 1 
    -- and t1.device_number='MDA20122110039' --该查询性能正常
     and t3.`name`='xxx医院' 
ORDER BY
    t1.time_periods_begin DESC 
    LIMIT 10;

该查询耗时0.6秒,已经较慢了.

查看查询计划,优化成了t3作为驱动表了,

 那么根据情况,把,left join 改成straight_join

SELECT   t3.`name` as orgName,t1.num,t1.other_num as otherNum,
        t1.device_number as deviceNumber,t1.time_periods_begin as timePeriodsBeginStr,
        t1.time_periods_end as timePeriodsEndStr,t1.create_time as  createTimeStr,t1.date as updateTimeStr
        FROM log_device_heart t1  -- use index(time_periods_begin_desc)
        STRAIGHT_JOIN m_device t2
        on t1.device_number=t2.number
        left join m_organization t3
        on t3.id=t2.organization_id
        WHERE 1=1
                 and t1.device_number='MDA20123010006'
                 and t3.`name`='xxx医院' 
        --           and organization_id<>'10007' and organization not like '%学术机%'
                 order by t1.time_periods_begin desc limit 100;

改成这样子,速度变成0.009秒.

查询计划也正常了

 

STRAIGH_JOIN会有两个问题,本质上更类似于inner join,不过这边对我的逻辑影响不大

第二个就是当查询结果匹配不到,会奇慢无比.大概耗时10秒左右.

目前尚未有更好的解决方法.