mysql - 为什么这个sql左连接这么慢？

Question

SELECT
    ad.id,
    d.device_id,
    d.d_id AS variable_id,
    d.d_name,
    d.oper_type,
    d.d_type,
    d.d_decimal AS 'decimal',
    d.data_prot_param,
    ad.tag,
    ad.modbus_reg_addr,
    ad.modbus_area 
FROM
    box_api_data ad
    LEFT JOIN box_data_info d ON d.d_id = ad.variable_id 
WHERE
    ad.unique_code = '7000224061716030893' 
    AND ad.topic_id = 572137887207493 
    AND d.unique_code = '7000224061716030893'

这个sql查询要6s多，但是我两张表数据量都不是很大呀。box_api_data目前40000条，box_data_info目前10006条。基本都是比较少的，这么个数据量连接查询居然干到了6s，是我sql有问题吗？目前两张表都没有索引。

CREATE TABLE `box_data_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unique_code` varchar(50) NOT NULL COMMENT '所属编号',
  `device_id` int(11) NOT NULL COMMENT '所属设备',
  `d_id` int(11) NOT NULL COMMENT '变量标识',
  `d_name` varchar(128) NOT NULL COMMENT '变量名称',
  `oper_type` int(11) NOT NULL COMMENT '操作类型(0:只读,1:只写,2:读写)',
  `d_type` int(11) NOT NULL COMMENT '数据类型',
  `unit` varchar(30) DEFAULT NULL COMMENT '变量单位',
  `d_decimal` int(11) DEFAULT '0' COMMENT '小数位数',
  `min_range` varchar(20) DEFAULT NULL COMMENT '最小量程',
  `max_range` varchar(20) DEFAULT NULL COMMENT '最大量程',
  `r_formula` varchar(156) DEFAULT NULL COMMENT '读公式',
  `w_formula` varchar(156) DEFAULT NULL COMMENT '写公式',
  `d_status` int(11) DEFAULT NULL COMMENT '变量状态 0:离线，1:在线',
  `is_store` int(11) DEFAULT '0' COMMENT '是否存储',
  `data_prot_param` varchar(1000) DEFAULT NULL COMMENT '特殊配置',
  `is_custom` int(11) NOT NULL DEFAULT '0' COMMENT '是否是自定义变量(0：不是，1：是)',
  `trans_modbus_param` varchar(1000) DEFAULT NULL COMMENT '转换Modbus协议参数',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  `data_index` int(11) DEFAULT NULL COMMENT '数据索引',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=848295 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='点表';

CREATE TABLE `box_api_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` bigint(20) DEFAULT NULL COMMENT '主题id',
  `variable_id` int(11) DEFAULT NULL COMMENT '数据标识',
  `device_id` int(11) DEFAULT NULL COMMENT '设备id',
  `tag` varchar(127) DEFAULT NULL COMMENT '数据标签',
  `modbus_area` varchar(10) DEFAULT NULL COMMENT 'modebus区域',
  `modbus_reg_addr` int(11) DEFAULT NULL COMMENT 'modebus寄存器地址',
  `modbus_data_type` int(11) DEFAULT NULL COMMENT 'modbus在寄存器中的实际数据类型',
  `opcua_data_con` mediumtext COMMENT 'opcua特殊数据',
  `type` char(10) DEFAULT 'variable' COMMENT '类型（variable:变量，rule:规则）',
  `unique_code` varchar(50) NOT NULL COMMENT '网关编号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2452557 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='接口变量表';

Answer 1

你这一个索引都没加，肯定慢了，另外你这如果不限制取回的行数，并且时 left join，那左表（主表）是会全表扫描的，在一定程度上也会慢。

# 添加索引
alter table box_data_info add index idx_unique_code_d_id (unique_code, d_id);
alter table box_api_data add index idx_topic_id_variable_id_unique_code (topic_id, variable_id, unique_code);


# 改写 SQL，如果你查询的两个表的 unique_code 始终是相匹配的，可以直接在关联的时候就筛选掉副表的数据

SELECT
    ad.id,
    d.device_id,
    d.d_id AS variable_id,
    d.d_name,
    d.oper_type,
    d.d_type,
    d.d_decimal AS 'decimal',
    d.data_prot_param,
    ad.tag,
    ad.modbus_reg_addr,
    ad.modbus_area 
FROM
    box_api_data ad
    LEFT JOIN box_data_info d ON d.d_id = ad.variable_id and ad.unique_code = d.unique_code
WHERE
    ad.unique_code = '7000224061716030893' 
    AND ad.topic_id = 572137887207493 

box_data_info 这个表的索引，你要看根据你的实际情况可以调整一下这两个字段的顺序，因为这里都在关联条件里面，应该是可以走到并覆盖的。