MySQL 删除大表的性能问题解决方案

微博上讨论MySQL在删除大表engine=innodb（30G+）时，如何减少MySQL hang的时间，现做一下简单总结：

当buffer_pool很大的时候（30G+），由于删除表时，会遍历整个buffer pool来清理数据，会导致MySQL hang住，解决的办法是：
1、当innodb_file_per_table=0的时候，以上不是问题，因为采用共享表空间的时候，该表所占用的空间不会被删除，buffer pool中的相关页不会 被discard。
2、当innodb_file_per_table=1的时候，并且当buffer_pool比较大的时候，遍历整个buffer pool 需要很多的时间(table_cache 会被锁住，所有的DML操作被阻止)。

认识误区：
将innodb 表改为 myisam表，这个是没有效果的，该操作会删除旧表，建立新表，依然会遍历整个buffer_pool。

解决思路：
1、采用脚本形式，批量删除部分记录
2、可以再slave上进行操作，进行主备切换（成本高）
3、Percona 5.1.58以上版本都支持innodb_lazy_drop_table（bug不少，慎用）
4、与buffer pool无关，但是可以加快 删除数据文件的速度，同样能减少MySQL hang住的时间。即：对数据文件建立硬链接，（依赖原理：OS HARD LINK 当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名只是删除了一个指针而已，不会删除数据文件。当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时）

赞 (0)

相关推荐

  • mysql支持跨表delete删除多表记录

    前几天写了Mysql跨表更新的一篇总结,今天我们看下跨表删除. 在Mysql4.0之后,mysql开始支持跨表delete. Mysql可以在一个sql语句中同时删除多表记录,也可以根据多个表之间的关系来删除某一个表中的记录. 假定我们有两张表:Product表和ProductPrice表.前者存在Product的基本信息,后者存在Product的价格. 第一种跨表删除的方式是不用join,在delete时指定用半角逗号分隔多个表来删除,如下sql语句: 复制代码 代码如下: DELETE p.

  • MySQL实现快速删除所有表而不删除数据库的方法

    本文实例讲述了MySQL实现快速删除所有表而不删除数据库的方法.分享给大家供大家参考,具体如下: 如果直接使用phpmyadmin操作的话肯定非常简单,勾选数据表->点击删除->点击确定,操作完毕! 这里介绍一下快速删除数据表的SQL命令操作方法. 删除表的命令: drop table 表名; 如果有200张表,执行200次,想想就不想动手了. 下面提供一个使用information_schema库的方案: 复制代码 代码如下: SELECT CONCAT('drop table ',tabl

  • mysql中多表删除其中ID相同记录的方法

    mysql中多表删除其中ID相同记录的方法

      问题源自一个5年前的百度知道回答,刚看到这个问题时我刚上班21天,遂根据自己的那一丁点经验和临时操作检验做出了当时认为正确的回答:  但很快就被人否定,我猜主要原因就是我说的最后一句话过于绝对,但由于自己太相信自己了,而且有一大堆理由支撑着,所以没有深入研究. 最近又有网友评论了我的这个回答,我才开始查了一下百度,发现Mysql4.0以上版本已经可以通过一条delete删除多个表的数据了.看来我确实在误人子弟了,只能说声抱歉,好在楼主没有选我的答案,要不然罪过大了. 最后总结一下: MySq

  • mysql表的清空、删除和修改操作详解

    一.清除mysql表中数据 delete from 表名; truncate table 表名; 不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容. 效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据. delete的效果有点像将mysql表中所有记录一条一条删除到删完, 而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表.

  • MySQL创建和删除表操作命令实例讲解

    创建表 简单的方式 复制代码 代码如下: CREATE TABLE person ( number INT(11), name VARCHAR(255), birthday DATE ); 或者是 复制代码 代码如下: CREATE TABLE IF NOT EXISTS person ( number INT(11), name VARCHAR(255), birthday DATE ); 查看mysql创建表: 复制代码 代码如下: SHOW CREATE table person; CRE

  • 在MySQL中删除表的操作教程

    丢弃现有MySQL的表是很容易的.但是需要非常小心,删除任何现有的一个表后将无法恢复,因为数据丢失. 语法: 下面是通用的SQL语法丢弃(删除)MySQL表: DROP TABLE table_name ; 从命令提示符删除表: 只需要在mysql>提示符下执行DROP TABLE SQL命令. 例子: 下面是一个例子,它删除表 tutorials_tbl: root@host# mysql -u root -p Enter password:******* mysql> use TUTORI

  • MySQL 添加、修改、删除表的列及约束等表的定义

    ALTER TABLE:添加,修改,删除表的列,约束等表的定义. 查看列:desc 表名; 修改表名:alter table t_book rename to bbb; 添加列:alter table 表名 add column 列名 varchar(30); 删除列:alter table 表名 drop column 列名; 修改列名MySQL: alter table bbb change nnnnn hh int; 修改列名SQLServer:exec sp_rename't_stude

  • MySQL中批量删除指定前缀表的sql语句

    MySQL中批量删除指定前缀表的sql语句

    复制代码 代码如下: Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'dede_%'; "dede"为要删除的表前缀,执行此SQL语句后会生成一串SQL语句,必须再执行生成的这些SQL语句才能真正执行删除操作 另外一个就是批量修改表名: 复制代码 代码如下: Select CONCAT( 'ALTER TABLE ', table_

  • php中批量删除Mysql中相同前缀的数据表的代码

    方法一: 复制代码 代码如下: <?php mysql_connect('','',''); mysql_select_db(''); $rs=mysql_query('show tables'); while($arr=mysql_fetch_array($rs)){ $TF=strpos($arr[0],'class_'); if($TF===0){ $FT=mysql_query("drop table $arr[0]"); if($FT){ echo "$arr

  • mysql delete 多表连接删除功能

    单个表的删除: DELETE FROM tableName WHERE columnName = value; 删除表内的所有行: 即:保留表的结构.属性.索引 DELETE FROM tablename; DELETE * FROM tablename; 删除同一张表内的所有内容(删除数据.表结构) TRUNCATE customer; 无法报告删除了几行,且只能用于单一表 多表连接删除: DELETE orders,itrms FROM orders,items WHERE orders.u

随机推荐