原文：http://blog.chinaunix.net/u2/67276/showart_573951.html
optimize table语句可以重新整理myisam表,愈合碎片,并且重新排序索引
当表经常被delete,update之后,我们需要定期优化,以减少存储空间,并使索引有序,提升sql执行效率
下面是一段脚本,如果db中表数据量多,每次执行optimize table都会要求连接一次db,可以改成先生成一个sql文件,然后一次性执行。
shell脚本(optimize_table.sh)代码如下：
#!/bin/sh
#cocolala 2008/4/22
/usr/local/mysql/bin/mysql -uroot -pxxx db -s -e "show tables ;" >/tmp/tablelist.txt
for tablename in `cat /tmp/tablelist.txt`
do
/usr/local/mysql/bin/mysql -uroot -pxxx db -s -e \
"
optimize table ${tablename};
"
echo "optimize table ${tablename} ok "
done
把脚本放到crontab里面去，设置为每周日凌晨3:00执行，
0 3 * * 0 /home/mysql/optimize_table.sh >/home/mysql/optimize_table.log 2>&1
需要注意的是: optimize table语句会要求table lock,不要在业务正常运行时执行,以免造成业务sql的lock wait
在实际维护的一台DB中,运行时间如下,5031个表table,优化之前占用空间18G,优化后占用15G
效果明显,但对sql语句性能影响较小,看不出明显变化
Uptime: 98 days 7 hours 48 min 24 sec
------补充资料------
没有经过optimize的表用myisamchk检查时的结果
# /usr/local/mysql/bin/myisamchk -dv xxx
MyISAM file: xxx
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2008-01-23 4:06:27
Recover time: 2008-01-23 4:06:27
Status: open,changed
Data records: 11293 Deleted blocks: 1799
Datafile parts: 25370 Deleted data: 373036
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 2828976 Keyfile length: 508928
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 299
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 1 4 unique unsigned long 1 91136 1024
2 96 8 multip. longlong 5 250880 1024
3 24 4 multip. unsigned long 47 358400 1024
21 1 int8 33
optimize过的表
# /usr/local/mysql/bin/myisamchk -dv tmp_total_20071215
MyISAM file: tmp_total_20071215
Record format: Fixed length
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2007-12-15 1:03:30
Status: changed,analyzed,optimized keys,sorted index pages
Data records: 66840 Deleted blocks: 0
Datafile parts: 66840 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 10026000 Keyfile length: 645120
Max datafile length: 644245094398 Max keyfile length: 17179868159
Recordlength: 150
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique unsigned long 1 99328 1024 |