mysql优化-----ddl语句

不念不忘少年蓝@ 2022-06-10 05:43 318阅读 0赞

author：skate  
time：2012/07/02

**mysql优化-----ddl语句 **

在drop table维护mysql数据库时，在drop操作期间，整个系统会被hang住，这个hang的时间的长短与Buffer Pool的大小相关。主要原因在于InnoDB在drop table时，会连续两次遍历buf pool LRU 链表，遍历的过程加锁，因此导致系统hang住。

第一遍用于释放adaptive hash中的记录：  
0. 获取当前buf pool的mutex，为遍历buf pool LRU list的准备  
1. 从LRU链表尾部开始遍历  
2. 对于每一个属于Drop Table的page，判断page中是否有项进入adaptive hash，若有，则收集当前page  
3. 收集到1024个这样的page后，释放buf pool mutex，集中调用函数buf\_LRU\_drop\_page\_hash\_batch，释放page在adaptive hash中的项  
4. 重新获取buf pool mutex，继续遍历buf pool LRU 链表，直至链表头  
5. 最终，释放buf pool mutex，退出

第二次遍历buf pool LRU链表，释放所有drop table对应的page  
1. 获取buf pool mutex  
2. 遍历buffer pool LRU链表  
3. 若为dirty page，则将dirty page设置为clean page，并从flush list中移除  
4. 将page从LRU list中移除，并且添加入free list  
5. 最后，释放buf pool mutex

测试：  
环境：  
os：redhat5.2  
mysql：5.5.24-log

session1：  
mysql> insert into tt2 select @a:=@a+1,name from tt2;  
Query OK, 4194304 rows affected (1 min 11.52 sec)  
Records: 4194304  Duplicates: 0  Warnings: 0

**开始drop tt2，同时在session2中insert一条记录**  
mysql> drop table tt2;  
Query OK, 0 rows affected (6.42 sec)

mysql>

session2：  
**正常insert一条记录需要0.03s**  
mysql> insert into t2 values (11,'22');  
Query OK, 1 row affected (0.03 sec)

**当在执行drop tt2的时候，就需要4.17s**  
mysql> insert into t2 values (11,'22');  
Query OK, 1 row affected (4.17 sec)

mysql>

总结：  
1.第一遍遍历LRU链表，会定期释放buf pool mutex，因此对于系统hang的影响较小；而第二遍会一直持有，对系统hang的影响较大。  
2.在维护drop操作时，一定要选择空闲时间，因为他会阻塞所有ddl和dml操作。

\---end----