【转】利用optimize、存储过程和系统表对mysql数据库表进行批量碎片清理释放表空间
本文收集于本人的笔记本,由于找不到原文出处。在此省略,如哪位知道可以联系我加上。
核心是利用mysql系统表和“optimize table 表名”命令,对mysql数据表进行空间的释放。由于delete和drop table都不会释放表空间(truncate 命令会释放表空间【将所有的数据都删除】),所以需要利用optimize 命令进行释放。
这个存储过程目的是给一个库的所有表来整理碎片的。一个表随着插入很频繁,或者一直更新不停的,就会积累好多碎片。如果及时整理一下,查询效率会高出好多。
DELIMITER $$ DROP PROCEDURE IF EXISTS `mysql`.`sp_optimize_tables`$$ CREATE PROCEDURE `mysql`.`sp_optimize_tables`(
IN db_name varchar(255))
BEGIN
-- Created by david yeung 20080128. -- To optimize all the tables in exact database. declare cnt int default 0;
declare i int default 0;
select count(*) as total from information_schema.tables where table_schema = db_name into cnt;
while i < cnt
do
-- Get the table's exact name.
set @stmt = concat('select table_name from information_schema.tables where table_schema = ''',db_name,''' order by table_name asc limit ',i,',1 into @tb_name');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = '';
set @stmt = concat('optimize table ',db_name,'.',@tb_name);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = '';
set i = i + 1;
end while;
-- Refresh tables. flush tables;
END$$ DELIMITER ;
调用示例:
mysql> use mysql
Database changed
mysql> call sp_optimize_tables('david_test');
+------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------+----------+----------+----------+
| david_test.test1 | optimize | status | OK |
+------------------------------+----------+----------+----------+
1 row in set (0.26 sec)
+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| david_test.test2| optimize | status | OK |
+--------------------------+----------+----------+----------+
1 row in set (0.35 sec)
+---------------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------------+----------+----------+----------+
| david_test.test3 | optimize | status | OK |
+---------------------------------------+----------+----------+----------+
1 row in set (0.45 sec)
+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| david_test.test_article | optimize | status | OK |
+--------------------------+----------+----------+----------+
1 row in set (4.13 sec)
...
+----------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------------+----------+----------+----------+
| david_test.test_article_content | optimize | status | OK |
+----------------------------------+----------+----------+----------+
1 row in set (37.81 sec)
+-----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| david_test.members | optimize | status | OK |
+-----------------------+----------+----------+----------+
1 row in set (40.02 sec)
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| david_test.test_site | optimize | status | OK |
+--------------------+----------+----------+----------+
1 row in set (40.31 sec)
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| david_test.t | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (41.10 sec)
Query OK, 0 rows affected (41.13 sec)
最新文章
- wpf 遍历控件及其值
- 一款好用且免费的语句分析工具Plan Explorer
- 常用js,css文件统一加载方法,并在加载之后调用回调函数
- 文字对齐之text-align总结
- ERP
- 关于Windows Azure 地缘组(Affinity Groups)
- Android 自定义View修炼-Android 实现自定义的卫星式菜单(弧形菜单)View
- 商务智能(BI)技术
- POJ 1564 Sum It Up(DFS)
- 2014年1月24日 Oracle 连接查询与子查询
- Redis被攻击
- 各种编码之间的关系以及getBytes的使用
- break语句和continue语句
- ajax知识点及正则表达式总结
- PowerShell基础
- Java虚拟机8:垃圾收集(GC)-3(垃圾收集算法)
- Linux服务器丢包故障的解决思路及引申的TCP/IP协议栈理论
- 误操作yum导致error: rpmdb解决方法
- centos7 系统管理systemd学习记录
- 机器学习(二十七)— EM算法