一条Select语句引发的反思-续

墨蓝 2022-06-05 01:59 109阅读 0赞

21G，2亿+条的数据在删除的时候又出现了一个问题，网上有一箩筐的答案，本着学到就是赚到的精神还是记录下了。

**报错如下**：

mysql> delete from t_test;
    ERROR 1206 (HY000): The total number of locks exceeds the lock table size

**报错原因**：

MySQL的`innodb_buffer_pool_size` 参数的配置为128M，对比下线上该参数的配置为2310M。查了下关于innodb\_buffer\_pool\_size的介绍：

*  该参数和MyISAM的key\_buffer\_size有相似之处，但也是有差别的。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。
 *  **分配内存的原则**：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用DB服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb\_buffer\_pool\_size=64M
 *  [INNODB\_BUFFER\_POOL\_SIZE:设置最佳内存][INNODB_BUFFER_POOL_SIZE] 这篇文章对于该参数的介绍非常仔细，感兴趣的可以看看。  
    
    
     *  为什么是占用内存的80%左右？
     *  共享服务情况下，如何设置最优的内存占用？

**解决方法**：

linux下修改`/etc/my.cnf`， **增加** innodb\_buffer\_pool\_size = 2G

重启mysql:

*  service mysqld restart——0.5版之后是mysqld
 *  /etc/init.d/mysql restart

**解决结果**：

mysql> delete from t_test;
    Query OK, 260357874 rows affected (26 min 25.96 sec)

**小插曲**：

笔者在删除数据之后发现记录虽然变成0条，但该表占用的空间大小没变，怀着探索的精神深入查了下原因：

*  delete from table\_name where 条件删除数据后，数据表占用的空间大小不会变。
 *  不跟条件直接delete的时候。如：delete from table\_name清除了数据，同时数据表的空间也会变为0。

**这是因为删除操作后在数据文件中留下碎片所致。DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间**

**小插曲解决方案**：

1.  OPTIMIZE TABLE 指令对表进行优化
    
     *  语法：`OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]`
     *  例子：`optimize table t_test`
     *  解释
        
        在中删除了大量的数据后，可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据，或者如果已经对含有可变长度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）进行了很多更改，就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令**可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费**  。OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。
        
        在OPTIMIZE TABLE运行过程中，MySQL会锁定表。要慎用
2.  TRUNCATE TABLE删除数据
    
     *  语法：`TRUNCATE TABLE table_name;`
     *  例子：`tuncate table t_test`
     *  解释
        
        TRUNCATE TABLE 可以完全的清空表的数据， 需要 DROP(删除表) 的权限。  
        逻辑上， TRUNCATE TABLE 和 删除所有记录的 DELETE 类似， 也和删除表(DROP TABLE) 再重建表(CREATE TABLE) 类似。  
        只是为了性能， 它使用 DML 方法删除数据的。  
        因此它不能回滚数据， 它不能触发 DELETE 触发器， 如果有外键依赖与它的话， 将不能执行。
        
            TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it by passes the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships. Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement.

**DDL 这个词是什么意思呢** ？

原来 SQL 的命令分四部分： DDL,DML,DCL,TCL。

*  DDL(Data Definition Language)  
    数据库定义语言(statements are used to define the database structure or schema)  
    用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束  
    DDL不需要commit  
    命令: CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME
 *  DML(Data Manipulation Language)  
    数据操纵语言(statements are used for managing data within schema objects)  
    由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。  
    需要commit.  
    命令: SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE
 *  DCL(Data Control Language)  
    数据库控制语言  
    授权，角色控制等  
    命令: GRANT,REVOKE
 *  TCL(Transaction Control Language)  
    事务控制语言  
    命令: SAVEPOINT,ROLLBACK,SET TRANSACTION

## 参考资料 ##

*  [浅谈MySQL删除或清空表的数据][MySQL]
 *  [mysql innodb引擎优化（一）][mysql innodb]
 *  [MySQL性能调优my.cnf详解][MySQL_my.cnf]
 *  [INNODB\_BUFFER\_POOL\_SIZE: GET THE BEST OF YOUR MEMORY][INNODB_BUFFER_POOL_SIZE_ GET THE BEST OF YOUR MEMORY]

[INNODB_BUFFER_POOL_SIZE]: https://www.centos.bz/2016/11/innodb_buffer_pool_size-set-the-right-size/
[MySQL]: http://github.tiankonguse.com/blog/2015/01/10/mysql-delete-data.html
[mysql innodb]: http://www.imysql.com/comment/16414
[MySQL_my.cnf]: https://blog.linuxeye.cn/379.html
[INNODB_BUFFER_POOL_SIZE_ GET THE BEST OF YOUR MEMORY]: http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_buffer_pool_size/