我的1美分，添加和TABLE_TYPE='BASE TABLE'，所以我们可以跳过'VIEW'类型。

for table in `mysql -sss -e "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') and TABLE_TYPE='BASE TABLE' order by data_free desc;"`
do
mysql -e "OPTIMIZE TABLE $table;"
done

从phpMyAdmin和其他来源/编辑器，您可以使用:

SET SESSION group_concat_max_len = 99999999;
SELECT GROUP_CONCAT(concat('OPTIMIZE TABLE `', table_name, '`;') SEPARATOR '') AS O
FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_TYPE = 'BASE TABLE'
AND table_name!='dual'
AND TABLE_SCHEMA = '<your databasename>'

然后，您可以复制和粘贴结果到一个新的查询或执行它从您自己的源。 如果你在phpMyAdmin中看不到整个语句:

一个初始bash脚本列出并运行针对db的工具…

#!/bin/bash

declare -a dbs
unset opt

for each in $(echo "show databases;" | mysql -u root) ;do

        dbs+=($each)

done



echo " The system found [ ${#dbs[@]} ] databases." ;sleep 2
echo
echo "press 1 to run a check"
echo "press 2 to run an optimization"
echo "press 3 to run a repair"
echo "press 4 to run check,repair, and optimization"
echo "press q to quit"
read input

case $input in
        1) opt="-c"
        ;;
        2) opt="-o"
        ;;
        3) opt="-r"
        ;;
        4) opt="--auto-repair -c -o"
        ;;
        *) echo "Quitting Application .."; exit 7
        ;;
esac

[[ -z $opt ]] && exit 7;

echo " running option:  mysqlcheck $opt in 5 seconds  on all Dbs... "; sleep 5

for ((i=0; i<${#dbs[@]}; i++)) ;do
        echo "${dbs[$i]} : "
        mysqlcheck $opt ${dbs[$i]}  -u root
    done

下面的示例php脚本可以帮助您优化数据库中的所有表

<?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());

   }
}

?>

使用mysql客户端可以对数据库中的所有表进行优化/检查和修复。

首先，你应该得到所有的表列表，用'，'分隔:

mysql -u[USERNAME] -p[PASSWORD] -Bse 'show tables' [DB_NAME]|xargs|perl -pe 's/ /,/g'

现在，当你有所有要优化的表列表时:

mysql -u[USERNAME] -p[PASSWORD] -Bse 'optimize tables [tables list]' [DB_NAME]

对于所有数据库:

mysqlcheck -Aos -uuser -p 

数据库优化:

mysqlcheck -os -uroot -p dbtest3