MySQL删除修改数据优化

删除

删除未在他表出现的数据

下面有一条效率较差的删除语句，主要功能是将t1表中id未出现在t2表的记录删除。效率差的原因是in中用了子查询，导致删除语句不会走索引，从而导致锁全表，继而导致删除效率差。

1
2
3
4
5


delete from t1 
  where id not in 
  (
    select id from t2
  )

优化方法:取消子查询，改用关联删除，这样就可以使用建在id列上的索引。关联删除/更新在建立索引的情况下效率远高于in/exists

优化后的SQL语句如下：

1
2
3


delete t1 
  from t1 left join t2 on t1.id=t2.id
  where t2.id is null

注：上面这条是MySQL独有的优化，达梦数据库和Oracle中可用如下语句。（拾人牙慧，未经验证，用这两个数据库的朋友可以自行验证一下）(+)表示单侧关联，该符号在哪边哪边就是副表。

1
2
3
4
5
6


delete from t1
  where rowid in(
    select t1.rowid 
      from t1,t2
      where t2.id(+)=t1.id and t2.id is null
  )

删除多表相同数据

好的删除语句如下：

1
2
3


delete t1,t2
  from t1,t2
  where t1.id=t2.id

这样可以同时删除从主从表删除。比如一条记录记录在多张表中，删除这条记录需要同时删除两张表的记录。这样可以保证要么全部删除，要么全部不删，间接满足了事务一致性。（数据库只会从一个状态转移至另外一个状态，即拥有这条记录和没有这条记录的两个状态。这一条记录可以看成是一个入库记录、一张支票）

删除全表数据

用truncate替代delete。这里涉及delete的机制，delete并不是直接在磁盘中删除记录，而是将记录加一个标记，并设置为不可见，然后在数据库压力小时异步删除磁盘中的数据。但是这样有一个问题，虽然标记为删除后，查询表记录不可见。但是记录仍然占有着磁盘空间，这会拖慢查询数据库的速度。

1

truncate table t1;

更新

差的更新语句如下：

1
2
3


update t1
  set c1=''
  where id in (...)

如删除篇中所说，由于in中属性过多，in不会再走索引（当属性值大于4个后就不会再走索引，in中是子查询的话就不会走索引）。所以这里的更新效率慢，并且还会锁住整表。

关于为什么全表扫描会锁住整张表可以看小林的教程：update 没加索引会锁全表？

优化方式：

创建id临时表，临时表只有id一个字段
批量插入临时表，记录为上面差的更新语句中的记录，也即是需要更新的记录的id
将临时表于原表关联更新

补充：插入更新

插入一条数据，如果存在主键或唯一键冲突，则更新记录

注：使用此语句时，必须在表中定义主键或唯一约束

1
2
3
4
5


insert into [table_name] (column1,column2,column3...)
  values (values1,values2,values3...)
  on duplicate key update
  column1 = values(column1),
  column2 = values(column2),...

例如：

1
2
3
4


insert into users (id,`name`)
  values (1,'Alice')
  on duplicate key update
  name = values(`name`)

PostgreSQL中用法如下：

1
2
3
4


insert into users (id,`name`)
  values (1,'Alice')
  on conflicate (id) do 
  update set name = excluded.name

如果想同时修改多个字段也可用下面的写法

1
2
3
4


insert into users (id,`name`，age)
  values (1,'Alice',18)
  on conflicate (id) do 
  update set (`name`,age) = excluded.(`name`,age)

如果想遇见冲突主键不做处理可用如下语句

1
2
3
4


insert into users (id,`name`)
  values (1,'Alice')
  on conflicate (id) do 
  nothing