Mysql删除大量数据

发表于   |   分类于   |     |   阅读次数

mysql删除原理及用法

通过终端help delete，查询delete的用法及注意事项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
mysql> help delete;
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

For the single-table syntax, the DELETE statement deletes rows from
tbl_name and returns a count of the number of deleted rows. This count
can be obtained by calling the ROW_COUNT() function (see
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html). The
WHERE clause, if given, specifies the conditions that identify which
rows to delete. With no WHERE clause, all rows are deleted. If the
ORDER BY clause is specified, the rows are deleted in the order that is
specified. The LIMIT clause places a limit on the number of rows that
can be deleted.

For the multiple-table syntax, DELETE deletes from each tbl_name the
rows that satisfy the conditions. In this case, ORDER BY and LIMIT
cannot be used.

where_condition is an expression that evaluates to true for each row to
be deleted. It is specified as described in
http://dev.mysql.com/doc/refman/5.5/en/select.html.

Currently, you cannot delete from a table and select from the same
table in a subquery.

You need the DELETE privilege on a table to delete rows from it. You
need only the SELECT privilege for any columns that are only read, such
as those named in the WHERE clause.

As stated, a DELETE statement with no WHERE clause deletes all rows. A
faster way to do this, when you do not need to know the number of
deleted rows, is to use TRUNCATE TABLE. However, within a transaction
or if you have a lock on the table, TRUNCATE TABLE cannot be used
whereas DELETE can. See [HELP TRUNCATE TABLE], and [HELP LOCK].

URL: http://dev.mysql.com/doc/refman/5.5/en/delete.html

DELETE 语句支持以下修饰符:

  • LOW_PRIORITY，如果您指定LOW_PRIORITY,则DELETE的执行被延迟,直到没有其它客户端读取本表时再执行。
  • QUICK，对于MyISAM表,如果您使用QUICK关键词,则在删除过程中,存储引擎不会合并索引端结点,这样可 以加快部分种类的删除操作的速度。
  • IGNORE，在删除行的过程中,IGNORE关键词会使MySQL忽略所有的错误。

DELETE 注意事项

  • DELETE删除数据时，如果没有加WHERE条件，则全部删除表数据，相当于truncate table。
  • 删除数据时调用ROW_COUNT()函数返回删除数据。
  • 删除时加入ORDER BY，则删除顺序被指定。
  • 删除时加入LIMIT，则删除数量被指定。
  • 多表删除数据时，ORDER BY 和 LIMIT不能使用。
  • 当删除表数据时，不能同时对表做子查询。
  • 删除表数据需要DELETE权限，如果加了WHERE条件，也需要SELECT权限。
  • 在一个事务中或者表加锁时，TRUNCATE TABLE不能使用但是DELETE能使用。

删除大量数据注意事项

在生产环境中，删除大量数据，会消耗太多的IO资源，会造成一些不良现象主从延时、数据文件无法收缩、锁表等。做大量数据删除时，请注意以下事项：

  1. 批量删除，这样往往可以工作得更快，你可能需要在每次批量删除前sleep一段时间，控制删除的频率，这样的目的是减少对生产系统的IO冲击，把符合平均分布，避免从库滞后太多；
  2. 按照主键的序列分批分次，或者基于时间分批分次。
  3. 基于硬件的性能，每批删除的记录数，可以选择几百到几千到几万的数据量，但不要太大，MySQL很难同时处理好大事务和随机小事务；
  4. 如果要删除大部分数据，那么可以考虑的方式是，创建一个新表，insert要保留的数据，然后切换表；
  5. 对于大表(InnoDB)删除大量数据，如果是一个大的事务，中止删除数据的操作可能需要几倍的时间用于回滚，这样会导致严重的IO瓶颈，而批量删除可以让我们的回滚恢复要快得多。
  6. 需要留意空间的释放，选择独立表空间会更有利于释放空间。

删除实战

通过写存储过程的方式去删除数据，附上代码

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE PROCEDURE delete_large() 
BEGIN
REPEAT
	DELETE from legou_home_order where create_time<1451577600 LIMIT 1000;
UNTIL ROW_COUNT()=0 
END REPEAT;
END //
DELIMITER ;

通过mysql命令行执行call delete_large()，如果执行时间过久可以创建event执行

mysql命令行查询存储过程的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show procedure status\G
*************************** 1. row ***************************
                  Db: Db
                Name: delete_large
                Type: PROCEDURE
             Definer: root@%
            Modified: 2016-06-03 11:39:45
             Created: 2016-06-03 11:39:45
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

查看存储过程创建语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
show create procedure delete_large\G
*************************** 1. row ***************************
           Procedure: delete_large
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `delete_large`()
BEGIN
        REPEAT
        DELETE from legou_home_order where create_time<1451577600 LIMIT 1000;
        UNTIL ROW_COUNT()=0 END REPEAT;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
坚持原创技术分享，您的支持将鼓励我继续创作！