记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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: MySQL中的DELETE操作是一个很常见的操作,但如果删除的数据量很大,就可能出现删除缓慢的情况。 可能导致MySQL DELETE操作缓慢的原因有以下几点: 1. 索引问题:如果需要删除的没有正确的索引,MySQL将需要遍历整个来查找需要删除的数据,这将导致删除操作变得非常缓慢。确保在需要删除的字段上创建了正确的索引可以大大加速删除操作。 2. 锁问题:MySQL在进行DELETE操作时会对需要删除的行进行加锁,避免其他操作同时对同一行进行修改。但如果有其他操作正在持有锁并且锁的争夺比较激烈,就可能导致DELETE操作等待锁释放而变得缓慢。可以通过优化事务执行时间,合理设置事务的隔离级别以及减少并发操作来解决锁问题。 3. 日志问题:MySQL的写操作会生成日志,包括二进制日志和redo日志。DELETE操作会产生大量的日志录,这些日志的写入可能会成为性能瓶颈。可以通过优化日志的写入速度,如调整日志的刷新频率或更换更快的存储设备来加速DELETE操作。 4. 服务器资源问题:如果服务器的内存、CPU或磁盘等资源有限或者配置不合理,就可能导致DELETE操作缓慢。通过增加服务器的硬件资源、优化服务器的配置或提高服务器性能可以加速DELETE操作的执行。 综上所述,如果MySQLDELETE操作缓慢,可以通过创建正确的索引、优化事务隔离级别、调整日志写入速度、优化服务器资源等方法来提高删除的速度。 ### 回答2: MySQLDELETE语句在批量删除数据时可能会变得缓慢的几个原因。 首先,删除操作会导致数据库的写入操作。当删除大量数据时,MySQL需要遍历和修改索引,同时写入事务日志,这些都是耗时的操作。如果数据库中存在大量的索引,那么删除操作的效率会更低。 其次,如果删除操作没有使用合适的WHERE条件,那么MySQL可能需要全扫描来找到符合删除条件的录。全扫描对于大来说是非常耗时的。 另外,如果在删除操作期间有其他会话正在读取需要删除的数据,那么MySQL会进行锁定以保证数据的一致性。这样一来,其他会话就需要等待锁释放,导致删除操作变慢。 为了提高MySQL DELETE语句的批量删除效率,可以采取以下几个措施: 1. 添加合适的WHERE条件,尽量减少需要扫描的录数量。可以使用索引来加速查找符合条件的录。 2. 考虑将DELETE语句分批执行,不要一次性删除大量数据,可以设置循环批量删除一定数量的录。 3. 如果没有必要的话,可以暂时禁用或减少对删除操作的读取请求,减少锁冲突。 4. 可以调整MySQL的配置参数,如innodb_buffer_pool_size(增大缓冲池)、innodb_log_file_size(增大日志文件大小)等,来提升删除操作的性能。 综上所述,MySQLDELETE批量删除操作变慢可能是由于写入操作的耗时、全扫描、锁冲突等原因所导致的。通过合适的WHERE条件、分批执行、减少锁冲突和调整配置参数等方法,可以提高其执行效率。 ### 回答3: MySQLDELETE语句在批量删除数据时可能会变得缓慢的几个原因: 1. 事务处理:如果删除操作在事务中执行,MySQL会为每个删除操作生成一个事务日志,并进行回滚和恢复的操作。这些额外的步骤会导致删除操作变慢。在批量删除数据时,建议禁用事务处理或将多个删除操作合并为一个事务,从而减少事务日志的生成和处理。 2. 索引:如果要删除的有多个索引,那么每次删除一条录时,MySQL需要更新索引。在批量删除数据时,可以考虑删除索引,进行删除操作后再重新创建索引,以减少更新索引的次数。 3. 对应关系:如果要删除的与其它存在关联关系,那么MySQL需要检查和更新相关的约束和触发器。在进行批量删除操作时,可以禁用或延迟触发器和约束的检查,以加快删除操作的速度。 4. 锁机制:删除操作可能会引起行级锁、级锁或页面锁的产生,当发生锁冲突时,MySQL将会等待锁的释放。在批量删除数据时,可以尝试使用更高级别的锁模式,如级锁或段级锁,以减少锁冲突和等待时间。 5. 查询优化:MySQL的查询优化器会根据的统计信息和索引情况来选择最优的执行计划。在批量删除数据时,可以使用ANALYZE TABLE命令来更新的统计信息,以帮助查询优化器选择更优的执行计划。 总之,如果MySQLDELETE语句批量删除数据的速度较慢,可以考虑禁用事务处理、删除索引、禁用或延迟触发器和约束的检查、优化锁机制和更新的统计信息等方法来提高删除操作的效率。具体的优化方法需要根据具体的数据库和结构进行调整和测试。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值