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时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时)

时间: 2012-11-21

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

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

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

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

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 添加、修改、删除表的列及约束等表的定义

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表的清空、删除和修改操作详解

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

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_

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

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

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

在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创建和删除表操作命令实例讲解

创建表 简单的方式 复制代码 代码如下: 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 drop database删除数据库命令实例讲解

mysql drop database命令用于删除一个数据库,如果试图使用drop database命令删除一个不存在的数据库,那么那么你会收到此错误:ERROR 1008 (HY000): Can't drop database 'tutorial_database'; database doesn't exist,本文章向大家介绍mysql drop database使用实例. 首先在使用drop database删除数据库之前,我们需要连接到数据库服务器. 我们可以使用以下命令登录到mys

mysql创建删除表的实例详解

表的创建命令需要: 表的名称 字段名称 定义每个字段(类型.长度等) 语法 下面是通用的SQL语法用来创建MySQL表: CREATE TABLE table_name (column_name column_type); 现在,我们将在 test 数据库中创建以下表. create table tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tuto

运用jQuery写的验证表单(实例讲解)

//运用jQuery写的验证表单 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equ

MySQL创建和删除数据表的命令及语法详解

创建数据表 创建MySQL数据表需要以下信息: 表名 表字段名 定义每个表字段 语法 以下为创建MySQL数据表的SQL通用语法: CREATE TABLE table_name (column_name column_type); 以下例子中我们将在 TUTORIALS 数据库中创建数据表tutorials_tbl: tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT N

MySQL创建和删除数据库的命令及相关PHP脚本的操作方法

使用 mysqladmin 创建数据库 使用普通用户,你可能需要特定的权限来创建或者删除 MySQL 数据库. 所以我们这边使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库. 实例 以下命令简单的演示了创建数据库的过程,数据名为 TUTORIALS: [root@host]# mysqladmin -u root -p create TUTORIALS Enter password:****** 以上命令执行成功后会创建 MySQL 数

MariaDB(MySQL)创建、删除、选择及数据类型使用详解

一.MariaDB简介(MySQL简介略过) MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品.在存储引擎方面,使用XtraDB(英语:XtraDB)来代替MySQL的InnoDB. MariaDB由MySQL的创始人Michael Widenius(英语:Michael Widenius)主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL A

jQuery+PHP+MySQL二级联动下拉菜单实例讲解

二级联动下拉菜单选择应用在在很多地方,比如说省市下拉联动,商品大小类下拉选择联动.本文将通过实例讲解使用jQuery+PHP+MySQL来实现大小分类二级下拉联动效果. 实现效果:当选择大类时,小类下拉框里的选项内容也随着改变. 实现原理:根据大类的值,通过jQuery把值传给后台PHP处理,PHP通过查询MySQl数据库,得到相应的小类,并返回JSON数据给前端处理. XHTML 首先我们要建立两个下拉选择框,第一个是大类,第二个是小类.大类的值可以是预先写好,也可以是从数据库读取. <lab

MySQL命令行删除表中的一个字段

先看看删除之前的表结构: mysql> select * from test; +------+--------+----------------------------------+------------+------------+------------+------------+ | t_id | t_name | t_password                       | t_birth    | birth      | birth1     | birth2     |

浅谈MySQL临时表与派生表

关于派生表 当主查询中包含派生表,或者当select 语句中包含union字句,或者当select语句中包含一个字段的order by 子句(对另一个字段的group by 子句)时,MySQL为了完成查询,则需要自动创建临时表存储临时结果集,这种临时表由MySQL自行创建,自行维护,成为自动创建的临时表.对于自动创建的临时表,由于内存临时表的性能更为优越,mysql总是首先使用内存临时表,而当内存临时表变得太大时,达到某个阈值的时候,内存临时表就转存为外存临时表.也就是说,外存临时表是内存临时