设为首页收藏本站淘宝精选
开启辅助访问
快捷导航 发布信息
搜索
PCOS技术网»首页 › ≡技术交流≡ › 电脑技术交流 › 批量给表整理碎片
查看: 431|回复: 2

批量给表整理碎片

 关闭 [复制链接]
amwdnuff

签到天数: 2090 天

连续签到: 2 天

[LV.Master]伴坛终老IIII
发表于 2009-7-25 20:16 | 显示全部楼层 |阅读模式
批量给表整理碎片



个存储过程目的是给一个库的所有表来整理碎片的。一个表随着插入很频繁，或者一直更新不停的，就会积累好多碎片。如果及时整理一下，查询效率会高出好多。
不过要注意对数据巨大的表整理的时间可能非常的长。
1、原存储过程
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&#39;s exact name.
set @stmt = concat(&#39;select table_name from information_schema.tables where table_schema = &#39;&#39;&#39;,db_name,&#39;&#39;&#39; order by table_name asc limit &#39;,i,&#39;,1 into @tb_name&#39;);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set @stmt = concat(&#39;optimize table &#39;,db_name,&#39;.&#39;,@tb_name);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set i = i + 1;
end while;
-- Refresh tables.

flush tables;
END$$

DELIMITER ;


2、调用示例：

mysql> use mysql
Database changed
mysql> call sp_optimize_tables(&#39;david_test&#39;);
+------------------------------+----------+----------+----------+
| 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.test_article_content | optimize | status | OK |
+----------------------------------+----------+----------+----------+
1 row in set (37.81 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)
3、在SHELL中调用
#!/bin/sh
#
# Created by david yeung.
# Optimize all the table one by one.
#
cd /usr/local/mysql/bin
./mysql -uoptimize_user -poptimize -e \"use \\\"$1\\\";call mysql.sp_optimize_tables(\\\"$1\\\")\"

不过要注意给optimize_user的select,insert权限。
PCOS系统下载站：http://zhuangji.wang
回复

使用道具 违规提醒

amwdnuff

签到天数: 2090 天

连续签到: 2 天

[LV.Master]伴坛终老IIII
 楼主| 发表于 2009-7-25 20:16 | 显示全部楼层

批量给表整理碎片

批量给表整理碎片



个存储过程目的是给一个库的所有表来整理碎片的。一个表随着插入很频繁，或者一直更新不停的，就会积累好多碎片。如果及时整理一下，查询效率会高出好多。
不过要注意对数据巨大的表整理的时间可能非常的长。
1、原存储过程
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&#39;s exact name.
set @stmt = concat(&#39;select table_name from information_schema.tables where table_schema = &#39;&#39;&#39;,db_name,&#39;&#39;&#39; order by table_name asc limit &#39;,i,&#39;,1 into @tb_name&#39;);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set @stmt = concat(&#39;optimize table &#39;,db_name,&#39;.&#39;,@tb_name);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set i = i + 1;
end while;
-- Refresh tables.

flush tables;
END$$

DELIMITER ;


2、调用示例：

mysql> use mysql
Database changed
mysql> call sp_optimize_tables(&#39;david_test&#39;);
+------------------------------+----------+----------+----------+
| 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.test_article_content | optimize | status | OK |
+----------------------------------+----------+----------+----------+
1 row in set (37.81 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)
3、在SHELL中调用
#!/bin/sh
#
# Created by david yeung.
# Optimize all the table one by one.
#
cd /usr/local/mysql/bin
./mysql -uoptimize_user -poptimize -e \"use \\\"$1\\\";call mysql.sp_optimize_tables(\\\"$1\\\")\"

不过要注意给optimize_user的select,insert权限。
PCOS系统下载站：http://zhuangji.wang
回复 支持 反对

使用道具 违规提醒

amwdnuff

签到天数: 2090 天

连续签到: 2 天

[LV.Master]伴坛终老IIII
 楼主| 发表于 2009-7-25 20:16 | 显示全部楼层

批量给表整理碎片

批量给表整理碎片



个存储过程目的是给一个库的所有表来整理碎片的。一个表随着插入很频繁，或者一直更新不停的，就会积累好多碎片。如果及时整理一下，查询效率会高出好多。
不过要注意对数据巨大的表整理的时间可能非常的长。
1、原存储过程
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&#39;s exact name.
set @stmt = concat(&#39;select table_name from information_schema.tables where table_schema = &#39;&#39;&#39;,db_name,&#39;&#39;&#39; order by table_name asc limit &#39;,i,&#39;,1 into @tb_name&#39;);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set @stmt = concat(&#39;optimize table &#39;,db_name,&#39;.&#39;,@tb_name);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = &#39;&#39;;
set i = i + 1;
end while;
-- Refresh tables.

flush tables;
END$$

DELIMITER ;


2、调用示例：

mysql> use mysql
Database changed
mysql> call sp_optimize_tables(&#39;david_test&#39;);
+------------------------------+----------+----------+----------+
| 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.test_article_content | optimize | status | OK |
+----------------------------------+----------+----------+----------+
1 row in set (37.81 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)
3、在SHELL中调用
#!/bin/sh
#
# Created by david yeung.
# Optimize all the table one by one.
#
cd /usr/local/mysql/bin
./mysql -uoptimize_user -poptimize -e \"use \\\"$1\\\";call mysql.sp_optimize_tables(\\\"$1\\\")\"

不过要注意给optimize_user的select,insert权限。
PCOS系统下载站：http://zhuangji.wang
回复 支持 反对

使用道具 违规提醒

返回列表 发新帖

本版积分规则