问题描述
此mysql查询运行了大约10个小时，尚未完成.出了点大问题.
This mysql query is running for around 10 hours and has not finished. Something is horribly wrong.
两个表格(文本和垃圾邮件)在这里.垃圾邮件将垃圾邮件条目的ID存储在我要删除的文本中.
Two tables (text and spam) are here. Spam stores the ids of spam entrys in text that I want to delete.
DELETE FROM tname.text WHERE old_id IN (SELECT textid FROM spam);
垃圾邮件只有2列，均为整数. 800K条目的文件大小为几Mb.这两个整数都是主键.
spam has just 2 columns, both are ints. 800K entries has a file size of several Mbs. Both ints are primary keys.
文本有3列. id(主键)，文本，标志.大约1200K条目，以及大约2.1 GB的大小(大多数垃圾邮件).
text has 3 columns. id (prim key), text, flags. around 1200K entries, and around 2.1 gigabyte size (most spam).
该服务器是一个至强四核，2 GB内存(不要问我为什么).仅apache(为什么?)和mysqld正在运行.它是旧的免费bsd和mysql 4.1.2(不要问我为什么)
The server is a xeon quad, 2 gigabyte ram (don't ask me why). Only apache (why?) and mysqld is running. Its an old free bsd and mysql 4.1.2 (don't ask me why)
线程:6个问题:188805慢查询:318个打开:810个刷新表:1个打开表:157每秒查询平均数:7.532
Threads: 6 Questions: 188805 Slow queries: 318 Opens: 810 Flush tables: 1 Open tables: 157 Queries per second avg: 7.532
Mysql my.cnf:
Mysql my.cnf:
[mysqld]
datadir=/usr/local/mysql
log-error=/usr/local/mysql/mysqld.err
pid-file=/usr/local/mysql/mysqld.pid
tmpdir=/var/tmp
innodb_data_home_dir =
innodb_log_files_in_group = 2
join_buffer_size=2M
key_buffer_size=32M
max_allowed_packet=1M
max_connections=800
myisam_sort_buffer_size=32M
query_cache_size=8M
read_buffer_size=2M
sort_buffer_size=2M
table_cache=256
skip-bdb
log-slow-queries = slow.log
long_query_time = 1
#skip-innodb
#default-table-type=innodb
innodb_data_file_path = /usr/local/mysql/ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/
innodb_buffer_pool_size = 128M
innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit=1
#innodb_additional_mem_pool_size=1M
#innodb_lock_wait_timeout=50
log-bin
server-id=201
[isamchk]
key_buffer_size=128M
read_buffer_size=128M
write_buffer_size=128M
sort_buffer_size=128M
[myisamchk]
key_buffer_size=128M[server:~] dmesg | grep memory
real memory = 2146828288 (2047 MB)
avail memory = 2095534080 (1998 MB)
read_buffer_size=128M
write_buffer_size=128M
sort_buffer_size=128M
tmpdir=/var/tmp
查询仅使用一个cpu，top表示25％的cpu时间(所以1/4).
The query is using just one cpu, top says 25% cpu time (so 1 of 4).
real memory = 2146828288 (2047 MB)
avail memory = 2095534080 (1998 MB)
62 processes: 2 running, 60 sleeping
CPU states: 25.2% user, 0.0% nice, 1.6% system, 0.0% interrupt, 73.2% idle
Mem: 244M Active, 1430M Inact, 221M Wired, 75M Cache, 112M Buf, 31M Free
Swap: 4096M Total, 1996K Used, 4094M Free
PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
11536 mysql 27 20 0 239M 224M kserel 3 441:16 94.29% mysqld
有什么想法要解决吗?
推荐答案
根据我的经验，子查询通常是导致SQL语句执行时间缓慢的原因，因此我尝试避免这种情况.试试这个:
In my experience sub queries are often a cause of slow execution times in SQL statements, therefor I try to avoid them. Try this:
DELETE tname FROM tname INNER JOIN spam ON (tname.old_id = spam.textid);
免责声明:此查询未经测试，请先进行备份！ :-)
Disclaimer: This query is not tested, make backups first! :-)
这篇关于在mysql基础上使用子查询删除非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！