MySQL的drop/truncate Table影响分析和最佳实践

0.前言

MySQL上直接Drop张大表,会有什么影响，能否直接写个 drop table ; 或者 truncate table ; 甚至是delete * from 如果这张表足够大，比如1亿行记录，drop 的时间需要多久，期间我的MySQL是否能正常访问？

首先明确一点，现在讨论的是要删掉的大表一定是没人访问的表，否则如果这张表仍然还有被高频的访问，你敢直接删那基本上就是茅坑里点灯，找死！ 如果MySQL版本是5.5.23以下，直接DROP一张大表，也是守着茅坑睡觉，离死不远。 好，现在明确了这张表肯定没人访问了，你的MySQL版本也足够新，并不表示你就远离了茅坑，但如果这张表足够大，仍然有被崩到的风险。

大表：我们定义为5000万行以上，或者单表文件大于100G

我们要讨论的是innodb存储引擎,myisam等存储引擎，DROP 表又快又安全

1.drop table 的风险和避免方法

Drop table 要做的主要有3件事：

1. 把硬盘上的这个文件删了
2. 把内存中的这个库已经加载加来的Page删了，腾出空间
3. 把MySQL元数据字典中这张表关联信息删了

可能会引起的风险有3种：

1. MySQL长时间阻塞其他事务执行，大量请求堆积，实例假死。(锁)
2. 磁盘IO被短时间大量占用，数据库性能明显下降(IO)
3. 内存里的page大量置换，引起线程阻塞，实例假死（内存)

解决和避免的方法3种：

• io占用的问题，对这个表建一个硬链，使Drop table 表的时候并没有真的去磁盘上删那个巨大的ibd文件，事后再用truncate的方式慢慢的删除这个文件，如果是SSD盘和卡,drop table后再直接rm文件也没问题
• 内存和IO占用的问题，升级MySQL版本

  • MySQL 5.5.23 引入了 lazy drop table 来优化改进了drop 操作影响(改进，改进，并没有说完全消除!!!拐杖敲黑板3次)

  • MySQL5.7.8 拆分了AHI共用一个全局的锁结构 btr_search_latch

  • MySQL8.0 解决了truncate table 的风险

• 道路千万行，低峰第一条。选择低峰时间段，找个夜深人静，月黑风高的时候是更好的选择。

2.一组drop表的测试

drop一张5亿记录的大表究竟需要多久时间，1秒？5秒？10秒？2分钟？3小时？还是一两天？你是否有过靠谱的预计

先看一组新鲜的热气腾腾的，一组测试，看看不同条件下drop一张表所有的时间

测试环境

• 硬件:128G内存 3T宝存卡 xfs
• 系统:CentOS Linux release 7.6.1810
• MySQL version: 5.7.26
• innodb参数:innodb_buffer_pool_instances=1

作为对比我们再DROP一张不是那么大的表

不管是大表的DROP还是小表的DROP，我们不难总结出来以下规律

• drop 越大的表，同等条件下，执行用时越长
• drop 表如果做了硬链，比不做硬链，执行用时要短
• bufferPool的大小不显著影响 drop表的用时，但是bufferPool的繁忙程度很明显的影响了执行用时，越忙的bufferPool执行Drop表用时越长

而所有的上面试验中，我们都发现DROP一张大表，好像对线上其他的访问都没有影响，事实上是有点影响的

• 我们的DROP 表操作会导致本来就很BUSY的bufferpool置换动作变得更加频繁和拥挤
• 我们在drop 表期间，持有MDL锁，这时候show tables等命令，其实是会卡顿的
• 如果我们没做硬链，在drop table时会在磁盘上真实的删除一个大文件，期间IO使用率会明显上升 但是这些影响，在我们的这个压力测试环境下并没有体现（我们用Sysbench压了64个线程，QPS 大约2万左右)
• 我们对其他表的查询还在正常进行
• 我们的QPS/TPS指标没有明显波动
• 我们的MySQL实例还是好好的活着 这是为什么呢？我们执行一个这么莽撞而无理的SQL请求，似乎系统也没怎么波动，跟想象中的不一样啊！！！那是我们已经用了MySQL5.7了呀(拐棍连续杵地声！)，已经有了成熟的lazy drop table 方案了。

3.什么是MySQL的lazy drop table

在第2节中我们做了各种各样的花样测试，对比了各种条件下删除表的速度，但是都没有引起MySQL访问异常，我们总结原先是因为MySQL较新的版用了lazy drop table 方法，什么是lazy drop ,它被设计用来解决什么样的问题，以及关联到的 bufferPool，lru列表 等概念是什么，想着用一些简单的描述来说清楚。如果:

• 你不关注MySQL是怎么实现的lazy drop table ,你只想知道有这么个技术。
• 你不想了解这些枯燥的名词，你只想看看真实的CASE中，MySQL删除一张大表时产生的关联影响和动作
• 你正在蹲坑，你不想看这堆名词的解释，你怕你的脚麻了，或者产生其他更为严重的后果。
• 你是个成熟的DBA，你对这些概念已经了解了。或者大约知道是怎么会事。

那么，其实你可以跳过这一节，直接到第4节，看具体的CASE,在那里有一个真实的新鲜的，冒着热气的删除一个大表，在内存中前后的影响以及对应的步骤分析。

名词储备

在我们理解MySQL5.5.23以后采用了lazydrop 优化后为什么对线上影响小之前，我们回顾一些基本而又重要的概念。

1. Page MySQL innodb是用page来组织数据和索引的,通常一个Page是16k，在内存中，在磁盘中都是一页页的Page
2. BufferPool 当我们申请一个10G内存的MySQL实例时，通常说的是给这个实例设置了10G的BufferPool ,其他也有一些内存占用，但是量太小，被我们忽略了，MySQL的DATA/INDEX加载到内存都放在这
3. Lru MySQL Lru链表里存的是pageno和指向bufferpool的指针，不同于平时我们说的缓存过期方法，MySQL的LRU链表是有新旧两个队列，37%的冷区只在1秒内3次被访问后移到热区
4. AHI InnoDB存储引擎会监控对表上各索引页的查询，如果监控到某个索引页被频繁查询，并诊断后发现如果为这一页的数据创建Hash索引会带来更大的性能提升，则会自动为这一页的数据创建Hash索引，并称之为自适应Hash索引

什么是lazy drop

drop table dbatest 这个SQL在对表进行删除的时候，需要清理掉buffer pool中的page，但如果表比较大，占用过多的buffer pool，清理的动作会影响到在线的业务，所以MySQL 5.5.23以后的lazy drop table，帮我们很大程度上消除了大表删除的影响。

• 同步方式: 扫描lru链表，如果page属于Ｔ表，就从lru链表，hash表， flush list中删除，回收bufferpool中的block 并标记到free list中。
• lazy方式: 扫描lru链表 如果page属于Ｔ表，就给page设置一个space_was_being_deleted属性，等lru置换或者checkpoint flush dirty block的时候进行清理。 即在扫描LRU链表过程中，如果dirty page属于drop table，那么就直接从flush list中remove掉

注意这里的页并没有真正的在buffer Pool中清理掉，也没有从lru中清理了，会在等LRU过期和脏页刷新时再去真实的清理。

下面我们将从实际测试CASE中复现这个lazy drop table过程

4.drop table时innodb buffer pool的处理

下面我们开始我们第二个测试，测试过程中，用一个精心设计过的BufferPool占用情况分类报表，来展示这仅有的4G内存的BufferPooL各阶段的情况

实际:drop 一张热表时innodb buffer pool的变化

• 步骤1: 这是一开始的bufferpool状态,我们刚建了一张同结构的dbatest_new的空表，同时执行了,select count(*) from dbatest

• 步骤2: 执行SQL,alter table dbatest rename to dbatest_old;alter table dbatest_new rename to dbatest; ,可能你会问了，这个测试不是要DROP 表吗，为啥要多此一步，别慌,只是顺带着说明一件事，你先记住我们DROP表前，做了一个RENAME的操作即可

• 步骤3: 执行SQL,drop table dbatest_old;

第二个测试的小结

在MySQL5.7.26的版本中我们做了这个删除大表的测试

1. drop表时，MySQL采用了lazy drop 的方法，表删除的时候bufferPool 和lru 列表并没有立即消除原来的page和引用
2. rename表时，并不会消除内存中的关于这个表的page,因为采用了spaceid做对应关系，跟表名没有关系，更换表名不会影响内存中表和索引的缓存
3. drop表后的一段时间内(5-30min)，bufferPool中busy部分会显著增加（呃，事实上这个实验上看起来不明显 ，因为这个实验是在模拟一个高并发访问的SQL实例，所以一直有内存抢占，BufferPool中的busy部分一直都很大，我的另一些测试是在一个空跑的实例上做的，会看到busy部分一直停留在0直到，DROP表后，busy部分明显增加，最后Free出来大量的bufferpool空间）

drop table 和truncate table的风险对比

MySQL8.0针对该问题说明：

On a system with a large InnoDB buffer pool and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations could cause a temporary drop in system performance due to an LRU scan that occurred when removing an InnoDB tables adaptive hash index entries.

To address this problem, TRUNCATE TABLE now invokes the same code as DROP TABLE and CREATE TABLE. The problem was addressed for DROP TABLE in MySQL 5.5.23.

意思为：

当InnoDB buffer pool比较大和innodb_adaptive_hash_index启用时，TRUNCATE TABLE操作可能由于发生了LRU扫描，删除InnoDB表的自适应散列索引项时，导致系统性能暂时下降。为了解决这个问题，TRUNCATE TABLE现在调用与DROP TABLE相同的代码删除表。因为在MySQL 5.5.23后，DROP TABLE解决了这个问题。

总结：

5.5.23版本之前，采用truncate+drop方式

5.5.23版本之后，采用drop方式

8.0版本之后，采用truncate方式

pt-online-schema-change 在改表以后的删表是否有风险

  41 Query	INSERT LOW_PRIORITY IGNORE INTO `tfm`.`__dbatest_new` (`id`, `k`, `c`, `pad`, `testid`) SELECT `id`, `k`, `c`, `pad`, `testid` FROM `tfm`.`dbatest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '9999187')) AND ((`id` <= '10000000')) LOCK IN SHARE MODE /*pt-online-schema-change 26396 copy nibble*/
  41 Query	SHOW WARNINGS
  41 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
  41 Query	SHOW VARIABLES LIKE 'version%'
  41 Query	SHOW ENGINES
  41 Query	SHOW VARIABLES LIKE 'innodb_version'
  41 Query	ANALYZE TABLE `tfm`.`__dbatest_new` /* pt-online-schema-change */
  41 Query	RENAME TABLE `tfm`.`dbatest` TO `tfm`.`_dbatest_old`, `tfm`.`__dbatest_new` TO `tfm`.`dbatest`
  41 Query	DROP TABLE IF EXISTS `tfm`.`_dbatest_old`
  41 Query	DROP TRIGGER IF EXISTS `tfm`.`pt_osc_tfm_dbatest_del`
  41 Query	DROP TRIGGER IF EXISTS `tfm`.`pt_osc_tfm_dbatest_upd`
  41 Query	DROP TRIGGER IF EXISTS `tfm`.`pt_osc_tfm_dbatest_ins`
  41 Query	SHOW TABLES FROM `tfm` LIKE '\_\_dbatest\_new'
  42 Quit	
  41 Quit	

源码解读

这部分我们从DROP TABLE 在MysQL和innodb的源码上跟踪下

嵌套了多层

• mysql_rm_table
• —->mysql_rm_table_no_locks
• ——–>

1.入口函数:mysql_rm_table

bool mysql_rm_table(THD *thd,TABLE_LIST *tables, my_bool if_exists,
                    my_bool drop_temporary)
{
 //变量定义
 //准备日志
 //判断临时表
 //从tdc里移除信息
 //加锁
//以上情节，在我们想drop表的时候，理论上不会有业务访问，移TDC等都不会有阻塞，可以忽略
  error= mysql_rm_table_no_locks(thd, tables, if_exists, drop_temporary,
                                 false, false);
  my_ok(thd);

}

我们继续跟到下一个

2.mysql_rm_table_no_locks

int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, bool drop_temporary, bool drop_view, bool dont_log_query)
{
  
  //定义了一堆的变量。。

    //定义了变量。。和加了一些assert断言检查 
    if ((drop_temporary && if_exists) || !error)
    {
       //临时表 ，这里我们不看
    }
    else if (!drop_temporary)
    {
      non_temp_tables_count++;
      // 检查表锁，wait_while_table_is_used
      if (thd->locked_tables_mode)
      {
       // 检查表锁，wait_while_table_is_used
      }
    
    }

    error= 0;
    if (drop_temporary  ((access(path, F_OK) && ha_create_table_from_engine(thd, db, alias)) ||   (!drop_view &&  dd_frm_type(thd, path, &frm_db_type) != FRMTYPE_TABLE)))
    {
      // 这是删除临时表的一种特殊情况
    }
    else
    {
      char *end;
      if (frm_db_type == DB_TYPE_UNKNOWN)
      {
        dd_frm_type(thd, path, &frm_db_type);
        DBUG_PRINT("info", ("frm_db_type %d from %s", frm_db_type, path));
      }
      table_type= ha_resolve_by_legacy_type(thd, frm_db_type);
      if (frm_db_type != DB_TYPE_UNKNOWN && !table_type)
      {
        my_error(ER_STORAGE_ENGINE_NOT_LOADED, MYF(0), db, table->table_name);
        wrong_tables.mem_free();
        error= 1;
        goto err;
      }
      // Remove extension for delete
      *(end= path + path_length - reg_ext_length)= '\0';
      DBUG_PRINT("info", ("deleting table of type %d",
                          (table_type ? table_type->db_type : 0)));
      error= ha_delete_table(thd, table_type, path, db, table->table_name,
                             !dont_log_query);

      //下面的一堆错误处理，我们也不用管了，继续跟到 ha_delete_table
}

3.ha_delete_table –>ha_innobase::delete_table

int ha_innobase::delete_table(
/*======================*/
	const char*	name)	/*!< in: table name */
{
	dberr_t	err;
	THD*	thd = ha_thd();
	char	norm_name[FN_REFLEN];
	trx_t*	trx = innobase_trx_allocate(thd);
	ulint	name_len = strlen(name);
	ut_a(name_len < 1000);
	/* Either the transaction is already flagged as a locking transaction
	or it hasn't been started yet. */
	ut_a(!trx_is_started(trx) || trx->will_lock > 0);

	/* We are doing a DDL operation. */
	++trx->will_lock;

	/* Drop the table in InnoDB */

	err = row_drop_table_for_mysql(
		norm_name, trx, thd_sql_command(thd) == SQLCOM_DROP_DB,
		true, handler);

	if (err == DB_TABLE_NOT_FOUND) {
  //一堆错误处理....

4.row_drop_table_for_mysql 一个625行的大函数

一路跟着足迹我们来到了这个巨大的函数,这个函数逻辑很复杂,,把它从上到下拆成这几块来看

1. row_mysql_lock_data_dictionary 加锁，锁住整个数据字典
2. trx_start_for_ddl 开启一个事务
3. row_add_table_to_background_drop_list
4. 拼接了一个巨大的SQL，用来从系统表中清理信息
5. row_drop_table_from_cache 清缓存
6. row_drop_single_table_tablespace 删除表空间
   • 这个又会调用 fil_delete_tablespace
   • buf_LRU_flush_or_remove_pages 这一步就是最容易出问题的地方，清理lru列表和缓存
   • os_file_delete 最终unlink文件
7. row_mysql_unlock_data_dictionary 释放锁 简化以后的代码如下：

{
 
 //1.
      row_mysql_lock_data_dictionary(trx); 
      locked_dictionary = true;
      nonatomic = true;
 
//2.
  /* This function is called recursively via fts_drop_tables(). */
  if (!trx_is_started(trx)) {

    if (!dict_table_is_temporary(table)) {
      trx_start_for_ddl(trx, TRX_DICT_OP_TABLE);
    } else {
      trx_set_dict_operation(trx, TRX_DICT_OP_TABLE);
    }

//3.
    row_add_table_to_background_drop_list(table->name.m_name);
  
//4.  
    sql = "PROCEDURE DROP_TABLE_PROC () IS\n"
      "sys_foreign_id CHAR;\n"
      "table_id CHAR;\n"
      "index_id CHAR;\n"
      "foreign_id CHAR;\n"
      "space_id INT;\n"
      "found INT;\n";
   //。。。这里省了很多SQL拼接语句

    sql +=  "DELETE FROM SYS_VIRTUAL\n"
      "WHERE TABLE_ID = table_id;\n";

    sql += "END;\n";

    err = que_eval_sql(info, sql.c_str(), FALSE, trx);
 
//5.
    /* Free the dict_table_t object. */
    err = row_drop_table_from_cache(tablename, table, trx);
    if (err != DB_SUCCESS) {
      break;
    }

//6.
    /* We can now drop the single-table tablespace. */
    err = row_drop_single_table_tablespace(
      space_id, tablename, filepath,
      is_temp, is_encrypted, trx);


//7.
funct_exit:

  mem_heap_free(heap);  
  ut_free(filepath);
  trx_commit_for_mysql(trx);   
  row_mysql_unlock_data_dictionary(trx);

}

5.buf_LRU_flush_or_remove_pages–>buf_LRU_drop_page_hash_for_tablespace

我们把上一步提到的buf_LRU_flush_or_remove_pages单独拿出来说 它加了几层判断 ，真实调用的是buf_LRU_drop_page_hash_for_tablespace

在这里我们要首重注意的是： 这一段，每当处理了num_entries>=BUF_LRU_DROP_SEARCH_SIZE次以后,就会释放锁 buf_pool_mutex_exit() 批量释放一些上页的1000个循环中收集到的ahi空间 再buf_pool_mutex_enter(buf_pool);加锁。然后goto scan_again，再重新扫一次

**这个很重要，这里的锁机制保证了在BUF_LRU_DROP_SEARCH_SIZE（这个值默认是1024） 就会放掉持有的buf_pool mutex锁（拐棍杵地！！！，记住了吗）为什么我们说MySQL5.5.23以后改进了，因为它不长时间持有锁，它每循环1024次以后就放掉了mutex锁，而在代码里循环1024次，是非常快的，对其他进程影响就非常非常小。但是我们一路追踪代码到这，前面还持有的数据字典锁，MDL锁并没有在这里放，但是这种大锁对普通的事务冲突不大。 **

然后我们知道每个循环里只是做了个标记，并没有去删LRU和bufferpool,用了一个page_arr记录了要删除的pageno. 然后每1024次以后，我们放掉了锁，同时把这1024个PAGE:buf_LRU_drop_page_hash_batch 注意这里的问题 我们要批量释放的是AHI自适应哈希这部分缓存，真正的LRU和bufferpool依然不会清理，那部分空间释放的太慢了，需要在以后让它自己过期了由master进程清理 这里就是我们说的lazy drop逻辑 以及我们要注意到AHI在早期版本中所有进程共用一把锁，在这里也容易产生冲突，直到5.7.8版本

 /* 
 Store the page number so that we can drop the hash
    index in a batch later. */
    page_arr[num_entries] = bpage->id.page_no();
    ut_a(num_entries < BUF_LRU_DROP_SEARCH_SIZE);
    ++num_entries;

//如果num_entries小于BUF_LRU_DROP_SEARCH_SIZE
    if (num_entries < BUF_LRU_DROP_SEARCH_SIZE) {
      goto next_page;
    }

      /* Array full. We release the buf_pool->mutex to obey
    the latching order. */
    buf_pool_mutex_exit(buf_pool);

    buf_LRU_drop_page_hash_batch(
      id, page_size, page_arr, num_entries);

    num_entries = 0;

    buf_pool_mutex_enter(buf_pool);

 goto scan_again;

drop table源码小结

1. 我们从MySQL入口处跟踪mysql_rm_table,跟踪到Innodb的buf_LRU_drop_page_hash_for_tablespace
2. 在代码中看到了MySQL优化锁占用的处理方式，每隔BUF_LRU_DROP_SEARCH_SIZE次释放一次锁，处理AHI完再加锁，多次循环的处理
3. 我们也看到了LRU和Buffer在最终的函数里并没有处理删除逻辑，而是等待LRU过期后被清理

小结

如果线上核心业务线有一个几个T数据的大表，被业务认为是没有用的表需要删除 ，这里有一个最保守的最佳实践

0. 确认无人访问，这个表最近是否真的没有被访问过。
1. 选择业务低峰时间,和这个实例上的库负责人沟通或者看性能监控，选一个低峰时间段。
2. 检查MySQL版本 MySQL5.5.23版本以上/最好是5.7.8以上
3. 检查架构，检查你的高可用架构是否支持这种长SQL的并发执行。
4. 做硬链,把ibd做个硬链，延后慢慢处理。这个很重要
5. rename表名 如第二个测试内容，改表名不能把内存中的表缓存清理掉，但是它能保证DROP这个表,没有其他SQL也在访问这张表，没有锁冲突。
6. 闲置表或DELETE部分数据 将表重命名后放置一段时间，等缓存失效，或提前分多批次DELETE掉数据，都是可以降低影响
7. 如果mysql版本低于5.7.8.禁用innodb_adaptive_hash_index ,危险操作，注意影响
8. 如果mysql版本低于8.0.**把truncate分解成create table /rename table /drop table **

– done