myisam表优化optimize

optimize table语句可以重新整理myisam表,愈合碎片,并且重新排序索引

 

当表经常被delete,update之后,我们需要定期优化,以减少存储空间,并使索引有序,提升sql执行效率

 

下面是一段脚本,可以设置为每周日凌晨4:00执行

如果db中表数据量多,每次执行optimize table都会要求连接一次db,可以改成先生成一个sql文件,然后一次性执行;

 

 

#!/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

 

 

0 4 * * 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

 

可以过滤log文件,将optimize失败的信息发mail给管理员

 

 

 

 

--4.29补充

没有经过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