记mysql 之 delete where 一张很大(20G)的表

 delete from table where id < 5000 如果数据量很大的时候，会导致超时 ，mysql将表锁住。

1205 Lock wait timeout exceeded try restarting transaction

DELETE from ACTION_LOG where TYPE = 14 and FROM_UNIXTIME(TIME) < DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH);

show full processlist;

show engine innodb status;

CREATE TABLE `ACTION_LOG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ACCOUNT` varchar(256) NOT NULL DEFAULT '',
  `PLAYERID` int(10) unsigned NOT NULL,
  `PLAYERNAME` varchar(33) NOT NULL DEFAULT '',
  `PLAYERLEVEL` int(10) unsigned NOT NULL,
  `TYPE` int(10) unsigned NOT NULL,
  `TIME` int(10) unsigned NOT NULL,
  `INFO` mediumtext NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `TIME` (`TIME`),
  KEY `TYPE_TIME` (`TYPE`,`TIME`),
  KEY `PLAYERID_TYP_TIME` (`PLAYERID`,`TYPE`,`TIME`),
  KEY `PLAYERNAME_TYPE_TIME` (`PLAYERNAME`,`TYPE`,`TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=101217 DEFAULT CHARSET=utf8

<span style="font-size:14px;">res=1
count=1
while [ $res -gt 0 ]
do
    res=`mysql -uroot -p123456 -D temp_log -e "DELETE from ACTION_LOG where TYPE = 2 and FROM_UNIXTIME(TIME) < DATE_SUB(CURRENT_DATE(),INTERVAL 2 MONTH) limit 20000;select ROW_COUNT();"`
    #res1=`mysql -uroot -pboyojoy.com </span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-size:12px;">temp_log </span></span><span style="font-size:14px;">-e "select ROW_COUNT();"`
	res=${res:12}
	((count++))
	echo $res
	echo $count
done
</span>

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）：
innodb_trx         ## 当前运行的所有事务
innodb_locks       ## 当前出现的锁
innodb_lock_waits  ## 锁等待的对应关系


看到这个就非常激动，这可是解决了一个大麻烦，先来看一下表结构：
复制代码
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态：
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间；
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事务线程ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL语句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事务中有多少个表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事务拥有多少个锁
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的内存大小（B）
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事务更改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事务并发票数
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
+—————————-+———————+——+—–+———————+——-+
22 rows in set (0.01 sec)

参考 http://www.songyawei.cn/content/2856

   http://blog.csdn.net/jaymade/article/details/7468510

   http://zhengdl126.iteye.com/blog/1570865

   http://www.searchdatabase.com.cn/showcontent_72713.htm