mysql嵌套查询和非嵌套查询对比
SELECT CONCAT( "【", device_.`name`, "】", info_.`name` ) `name`, history_.val, history_.created_date FROM b_analoginfo info_ LEFT JOIN b_systemdeviceinfo device_ ON device_.id = info_.device_id LEFT JOIN t_analogtype type_ ON type_.id = info_.analog_type_id INNER JOIN h_analog_value_history history_ ON device_.devicesn = history_.devicesn AND type_.analog_code = history_.analog_code AND type_.channel * (info_.address - 1) + info_.channel = history_.val_channel
SELECT CONCAT( "【", info_.device_name, "】", info_.`name` ) `name`, history_.val, history_.created_date FROM h_analog_value_history history_ INNER JOIN ( SELECT device_.`name` device_name, info_.`name`, device_.devicesn, type_.analog_code, type_.channel * (info_.address - 1) + info_.channel val_channel FROM b_analoginfo info_ LEFT JOIN b_systemdeviceinfo device_ ON device_.id = info_.device_id LEFT JOIN t_analogtype type_ ON type_.id = info_.analog_type_id ) info_ ON info_.devicesn = history_.devicesn AND info_.analog_code = history_.analog_code AND info_.val_channel = history_.val_channel
上面两个sql执行的时间是一样的,都是4292条数据 0.019s