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

本文解决了MySQL在删除大量数据时出现的锁表错误,并详细介绍了如何调整innodb_buffer_pool_size参数来优化性能。此外,还探讨了DELETE与TRUNCATE TABLE的区别及使用场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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:设置最佳内存 这篇文章对于该参数的介绍非常仔细,感兴趣的可以看看。
    • 为什么是占用内存的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

参考资料

(2024-2025学年第二学期) 考试对象:24级大数据(2年制) 订立标准人:周雄、黎林、张忠文 王者荣耀数据操作 一.题目描述 文件archer.txt中记录了手游《王者荣耀》射手的相关信息,内容如下所示,其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件,将查询结果打印出来。 1 后羿 5986 1784 396 336 remotely archer 2 马可波罗 5584 200 362 344 remotely archer 3 鲁班七号 5989 1756 400 323 remotely archer 4 李元芳 5725 1770 396 340 remotely archer 5 孙尚香 6014 1756 411 346 remotely archer 6 黄忠 5898 1784 403 319 remotely archer 7 狄仁杰 5710 1770 376 338 remotely archer 8 虞姬 5669 1770 407 329 remotely archer 9 成吉思汗 5799 1742 394 329 remotely archer 10 百里守约 5611 1784 410 329 remotely archer assassin 分析一下:字段都是基本类型,字段的顺序需要注意一下。字段之间的分隔符是制表符,需要使用row format语法进行指定。 二、功能实现 1.创建archer表 1.1字段信息 字段含义:id、name(英雄名称)、hp_max(最大生命)、mp_max(最大法力)、attack_max(最高物攻)、defense_max(最大物防)、attack_range(攻击范围)、role_main(主要定位)、role_assist(次要定位)。 1.2在下面贴上创建archer表的HQL语句和完成截图 2.在Hive服务器构造英雄数据 使用vi编辑器或文件上传工具,在hive的服务器上创建archer.txt文件 3.确定archer表的hdfs对应路径 打开浏览器,找到第一步创建的表的hdfs路径(以表名t_archer为例) 可以确定该表在hdfs上的路径为:/user/hive/warehouse/db_hero.db/t_archer 4.上传数据文件到hdfs上 打开hive服务器主机,将archer.txt文件上传到对应的表文件夹下。 hadoop fs -put archer.txt /user/hive/warehouse/db_hero.db/t_archer 执行查询操作,查询t_archer的所有数据,此处贴上查询结果,看是否能将上述文件中所有数据查询出来。 5.数据备份 根据所学知识,自行对t_archer的数据进行备份,备份表以t_archer_bak为例,完成备份后,贴上备份SQL和截图效果。 6.load命令使用 用load命令插入2条数据到t_archer中,贴出命令和截图 7.数据恢复 使用t_archer_bak来还原t_archer中的数据,删除t_archer_bak备份表,贴出命令和截图。 8. 总结和心得 针对本任务的完成过程,写一篇心得,不少于500字,围绕以下话题: 1.总结hive数据仓库的使用; 2.这个过程中有那些注意事项; 3.本次练习对你有什么启发; 三、评分规则(总分 100 分) 评分项 分值 评分标准 正确创建archer表 20 分 - 建表语法无误;(5分) - 字段个数跟样例数据匹配;(5分) - 字段类型正确设计;(5分) - 分隔符命令delimiter正确使用;(5分) 在hive的服务器上添加archer.txt 10 分 - vi命令操作正确(5 分) - 保存的数据和提供的数据一致(5分) 上传数据文件到hdfs 10 分 - t_archer表对应的hdfs路径正确(5 分) - hadoop fs xx命令正确,不报错(5 分) 备份 10 分 - 能正确使用备份命令(8 分) - 备份数据和t_archer一致(2 分) load命令使用 10 分 - load命令正确(5分) -命令中涉及的本地路径真实(5分) 数据恢复 10 分 - 命令准确(8分) - 数据恢复效果真实(2分) 总结 30 分 - 字数符号要求(10 分) - 内容真实,准确总结了此次实操过程(10 分) - 内容反应了此处过程的问题,有自己的深刻见解和反思(10 分)
06-23
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值