一 案例1 关联更新、删除
1.1 数据内容
1.表结构
CREATE TABLE `tb_crawl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) DEFAULT NULL,
`group_unit` varchar(255) DEFAULT NULL,
`device_code` varchar(255) DEFAULT NULL,
`device_name` varchar(255) DEFAULT NULL,
`point_code` varchar(255) DEFAULT NULL,
`state` int(5) DEFAULT '0',
`desc` varchar(255) DEFAULT NULL,
`ptype` varchar(255) DEFAULT NULL,
`paixu` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12743 DEFAULT CHARSET=utf8;
2.表内容
1.2 常规编写的sql
1.sql语句
desc
update tb_crawl set state=1 where id in(
select id from (
select id,state from tb_crawl where group_unit=2 and state not in(1) order by device_code
)as t
)
2.查看执行效率
3. 可以看到Extra这栏存在:using filesort,如果数据达到一定量级后,存在慢sql的问题。目前耗时:0.032s
1.3 优化后sql
1.sql语句
desc
update tb_crawl as o join (
select id,state from tb_crawl where group_unit=2 and state not in(0) order by device_code
) as t
on t.id=o.id
set o.state=0
2.优化后
可见优化后sql:耗时0.015s