sql多表数据删除

Question

delete from acid_event,tcphdr,event,data,iphdr using acid_event, tcphdr,event,data,iphdr 
where acid_event.cid = tcphdr.cid 
and event.cid=tcphdr.cid and tcphdr.cid = data.cid and iphdr.cid = data.cid这个多张表删除  速度很慢
有什么好的改进方法呀
请教下....

Answer 1

在连接字段上建立索引没有 ？你的SQL语句j是内连接。

Answer 2

expalin select * from acid_event,tcphdr,event,data,iphdr using acid_event, tcphdr,event,data,iphdr 
where acid_event.cid = tcphdr.cid 
and event.cid=tcphdr.cid and tcphdr.cid = data.cid and iphdr.cid = data.cid看一下执行计划。需要检查你的索引。show index from acid_event;
show index from tcphdr;
...
这么多表同时删除，会有很多影响因素，锁等待，索引的优化。
建议还是按照常规的方法，一个表一个表进行删除。

Answer 3

select ae.cid as scn,timestamp, inet_ntoa(ip_src) as 'srcip', \
layer4_sport as 'srcport', inet_ntoa(ip_dst) as 'dstip',layer4_dport  as 'dstport', \
data_payload as 'data' from acid_event as ae, data as da \
where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6 order by timestamp;主要的关联是cid这个字段
我的查询语句是这个 然后把记录 转移到oracle后 会把记录删除掉

Answer 4

CREATE TABLE event  ( sid    INT     UNSIGNED NOT NULL,
                      cid    INT     UNSIGNED NOT NULL,
                      signature   INT      UNSIGNED NOT NULL, 
                      timestamp     DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid),
                      INDEX       sig (signature),
                      INDEX       time (timestamp));CREATE TABLE iphdr  ( sid    INT     UNSIGNED NOT NULL,
                      cid    INT     UNSIGNED NOT NULL,
                      ip_src      INT      UNSIGNED NOT NULL,
                      ip_dst      INT      UNSIGNED NOT NULL,
                      ip_ver      TINYINT  UNSIGNED,
                      ip_hlen     TINYINT  UNSIGNED,
                      ip_tos     TINYINT  UNSIGNED,
                      ip_len    SMALLINT UNSIGNED,
                      ip_id       SMALLINT UNSIGNED,
                      ip_flags    TINYINT  UNSIGNED,
                      ip_off      SMALLINT UNSIGNED,
                      ip_ttl      TINYINT  UNSIGNED,
                      ip_proto    TINYINT  UNSIGNED NOT NULL,
                      ip_csum    SMALLINT UNSIGNED,
                      PRIMARY KEY (sid,cid),
                      INDEX ip_src (ip_src),
                      INDEX ip_dst (ip_dst));# All of the fields of a tcp header
CREATE TABLE tcphdr(  sid    INT     UNSIGNED NOT NULL,
                      cid    INT     UNSIGNED NOT NULL,
                      tcp_sport   SMALLINT UNSIGNED NOT NULL,
                      tcp_dport   SMALLINT UNSIGNED NOT NULL,
                      tcp_seq     INT      UNSIGNED,
                      tcp_ack     INT      UNSIGNED,
                      tcp_off     TINYINT  UNSIGNED,
                      tcp_res     TINYINT  UNSIGNED,
                      tcp_flags   TINYINT  UNSIGNED NOT NULL,
                      tcp_win     SMALLINT UNSIGNED,
                      tcp_csum    SMALLINT UNSIGNED,
                      tcp_urp     SMALLINT UNSIGNED,
                      PRIMARY KEY (sid,cid),
                      INDEX       tcp_sport (tcp_sport),
                      INDEX       tcp_dport (tcp_dport),
                      INDEX       tcp_flags (tcp_flags));
# Packet payload
CREATE TABLE data   ( sid           INT      UNSIGNED NOT NULL,
                      cid           INT      UNSIGNED NOT NULL,
                      data_payload  TEXT,
                      PRIMARY KEY (sid,cid));

Answer 5

delete from acid_event,tcphdr,event,data,iphdr using acid_event, tcphdr,event,data,iphdr 
where acid_event.cid = tcphdr.cid 
and event.cid=tcphdr.cid and tcphdr.cid = data.cid and iphdr.cid = data.cid
这几项为什么要重复？

Answer 6

explain  结果
1 SIMPLE acid_event ALL 4
1 SIMPLE tcphdr ALL 4 Using where
1 SIMPLE event ALL 4 Using where
1 SIMPLE iphdr ALL 4 Using where
1 SIMPLE data ALL 4 Using where

Answer 7

从EXPLAIN的结果来看，什么索引都没用上。这样速度会比较慢。贴一下你的 show index tablename;

Answer 8

show index from data;
data 0 PRIMARY 1 sid A BTREE
data 0 PRIMARY 2 cid A 10 BTREE
show index from event;
event 0 PRIMARY 1 sid A BTREE
event 0 PRIMARY 2 cid A 14 BTREE
event 1 sig 1 signature A BTREE
event 1 time 1 timestamp A BTREE
show index from acid_event;
acid_event 0 PRIMARY 1 sid A BTREE
acid_event 0 PRIMARY 2 cid A 8 BTREE
acid_event 1 signature 1 signature A 4 BTREE
acid_event 1 sig_name 1 sig_name A 4 YES BTREE
acid_event 1 sig_class_id 1 sig_class_id A 4 YES BTREE
acid_event 1 sig_priority 1 sig_priority A 2 YES BTREE
acid_event 1 timestamp 1 timestamp A 2 BTREE
acid_event 1 ip_src 1 ip_src A 4 YES BTREE
acid_event 1 ip_dst 1 ip_dst A 4 YES BTREE
acid_event 1 ip_proto 1 ip_proto A 2 YES BTREE
acid_event 1 layer4_sport 1 layer4_sport A 8 YES BTREE
acid_event 1 layer4_dport 1 layer4_dport A 8 YES BTREE
show index from iphdr
iphdr 0 PRIMARY 1 sid A BTREE
iphdr 0 PRIMARY 2 cid A 14 BTREE
iphdr 1 ip_src 1 ip_src A BTREE
iphdr 1 ip_dst 1 ip_dst A BTREE
show index from tcphdr
tcphdr 0 PRIMARY 1 sid A BTREE
tcphdr 0 PRIMARY 2 cid A 14 BTREE
tcphdr 1 tcp_sport 1 tcp_sport A BTREE
tcphdr 1 tcp_dport 1 tcp_dport A BTREE
tcphdr 1 tcp_flags 1 tcp_flags A BTREE

Answer 9

多表删除其实跟多表联接查询一样的道理的，要提高速度，关键要合理利用索引，可以用
explain SQL语句
来跟踪问题所在不过，删除时，速度又比同等条件的select要慢，主要因为写日志(delete要比update慢的原因）。

Answer 10

可是我数据每秒数据是几万进来
删除那么慢 mysql随时都有爆掉
所以我怎么写这个删除原表的数据 提高这个删除的速度

Answer 11

可是我数据每秒数据是几万进来 
删除那么慢-----------------------------
这样的数据量的话，那看来你的逻辑处理设计就有问题了
我这的csdn又开始BT了，又不能刷新看新贴子了
只能看“我回复的帖子”
好郁闷哦，最近老是这样，而且一出现这样的情况，就半天再也看不到新帖子了

Answer 12

可是我数据每秒数据是几万进来 
这样的话，你删除的时候，企不是所有的表正处理INSERT状态中？如果这样，你需要考虑你的数据库结构的设计了。

Answer 13

可是现在 我主要负责的是 及时的把mysql的数据转移到Oracle上，然后把mysql的数据删除掉就可以了。
现在的问题就是 移到oracle后 删除mysql的数据太慢了

Answer 14

先想办法把查询的速度提高。select * from acid_event,tcphdr,event,data,iphdr using acid_event, tcphdr,event,data,iphdr 
where acid_event.cid = tcphdr.cid 
and event.cid=tcphdr.cid and tcphdr.cid = data.cid and iphdr.cid = data.cid这句现在要花多久？

Answer 15

我查询出来的很快 然后是插入到oracle都很快  就是 
上面那个多表关联删除太慢了 

Answer 16

select 查询如果很快，那对WHERE部分和索引则没什么优化的必要了。那估计导致慢的原因可能是锁的问题。在执行你的这个delete 的等待中，show processlist 看一下

Answer 17

show processlist 是在查询编辑器里看的吗？

Answer 18

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info             |
+----+------+----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:2723 | csdn | Query   |    0 | NULL  | show processlist |
+----+------+----------------+------+---------+------+-------+------------------+
1 row in set (0.06 sec)mysql>

Answer 19

show processlist;
3 root localhost snort Sleep 91
4 root localhost snort Sleep 108
5 root localhost snort Sleep 109
6 root localhost snort Sleep 107
7 root localhost:58697 Sleep 9247
8 root localhost:58698 snort Sleep 14704
9 root localhost snort Sleep 90
10 root localhost snort Sleep 78
11 root localhost snort Sleep 106
14 root localhost snort Sleep 105
16 root 192.168.1.56:11588 snort Sleep 12
17 root localhost snort Sleep 111
18 root 192.168.1.56:14650 snort Query 53 Sending data delete from acid_event,tcphdr,event,data,iphdr using acid_event, tcphdr,event,data,iphdr 
where aci
19 root 192.168.1.56:14657 snort Sleep 274
20 root localhost Sleep 28
21 root 192.168.1.56:14739 snort Query 0 show processlist

Answer 20

影响的数据栏: 25000
时间: 985.297ms我删除5000条记录 关联5张表 就25000
可是真正执行完 用的时间有用了20分钟左右
这个显示的时间应该是commit的时间吧

Answer 21

处理逻辑？
这个一时我还真不知道怎么语言表达哦...其实，你这里的处理就是一个逻辑处理，你都知道问题所在了（尽量减少对入库表操作，相关数据处理迁移到别的服务器上再处理，以让入库服务器“专心入库”）考虑这样处理：入库数据分表，数据只根据自增id来取导入oracle（当然要记录上次取的id最大值了），这样读取速度会快很多；然后删除操作不直接对正在入库的表操作，而是在第2天直接truncate table即可（truncate前一天的数据，因为新一天的入库已经写到了新表的）。

Answer 22

尽量减少对入库表操作，相关数据处理迁移到别的服务器上再处理，以让入库服务器“专心入库”
这句话是对的 可是原始数据还是在入库服务器，所以数据转移的时候 不可避免要去删除入库服务器的数据
还有就是 你不能用truncate table去删除表的所有数据 因为表的内容不全是你要删的 还有别的关联的
因为新一天的入库已经写到了新表的 这个什么意思呀 新的一天的数据 还是存放到原来那几张表呀
而且你也不能保证 我那时候就没数据进去  这个不能很确定