mysql的表优化

多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。
了解查询优化器的工作原理。
优化表的格式。
维护你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)
使用MySQL的扩展功能以让一切快速完成。

从官方文档来看,以上两个命令对索引的作用描述为:

For MyISAM tables, OPTIMIZE TABLE works as follows:

1. If the table has deleted or split rows, repair the table.

2. If the index pages are not sorted, sort them.

3. If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

2.  REPAIR TABLE QUICK
If QUICK is given, REPAIR TABLE tries to repair only the index tree. This type of repair is like that done by myisamchk –recover –quick

OPTIMIZE TABLE对索引进行了橾作,索引文件大小无变化。耗时相对不多。 应是对现有的索引进行了排序。
REPAIR TABLE QUICK 测试后,发现索引文件变小。耗时相对较长和固定。应是对整个表的索引进行了重建。

下面是一个文件,优化所有表

Description

If you have a database driven site and you want to optimize MySQL tables then this is perfect. It goes through all the tables in a MySQL database and does table optimization on each one using the MySQL Optimize Table syntax.

The code

 <?php  

dbConnect()  

$alltables mysql_query("SHOW TABLES");  

while ($table mysql_fetch_assoc($alltables)) 
{  

   foreach ($table as $db => $tablename) 
   { 
       mysql_query("OPTIMIZE TABLE '".$tablename."'") 
           or die(mysql_error()); 
   } 
    
}  

?>