小开

下面的示例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 Administrator (MySQL GUI工具的一部分)可以在数据库级别为你做这件事。

只需选择您的模式并按下右下角的Maintenance按钮。

由于GUI工具已经达到了生命结束的状态，他们很难在mysql页面上找到。通过谷歌:http://dev.mysql.com/downloads/gui-tools/5.0.html找到它们

我不知道新的MySQL Workbench是否也能做到这一点。

你可以使用mysqlcheck命令行工具，它也应该能够做到这一点。

小开

最佳答案

你可以在命令行中使用mysqlcheck来做到这一点。

一个数据库:

mysqlcheck -o <db_schema_name>

所有数据库:

mysqlcheck -o --all-databases

小开

从命令行:

mysqlcheck -o <db_name> -u<username> -p

然后输入密码

小开

使用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

小开

我做了这个“简单”的脚本:

set @tables_like = null;
set @optimize = null;
set @show_tables = concat("show tables where", ifnull(concat(" `Tables_in_", database(), "` like '", @tables_like, "' and"), ''), " (@optimize:=concat_ws(',',@optimize,`Tables_in_", database() ,"`))");


Prepare `bd` from @show_tables;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;


set @optimize := concat('optimize table ', @optimize);
PREPARE `sql` FROM @optimize;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;


set @show_tables = null, @optimize = null, @tables_like = null;

要运行它，只需将其粘贴到连接到数据库的任何SQL IDE中。

注意:这个代码在phpmyadmin上是不会起作用。

它是如何工作的

它运行show tables语句并将其存储在预处理语句中。然后它在选定的集合中运行optimize table。

你可以通过在变量@tables_like中设置不同的值(例如:set @tables_like = '%test%';)来控制要优化哪些表。

小开

用一个简单的shell脚本完成所有数据库中所有表的修复:

#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze

小开

我的建议是:从碎片率最高的表开始

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') 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中看不到整个语句: < / p >

小开

这个bash脚本将接受根密码作为选项，并逐个优化它，并输出状态:

#!/bin/bash


if [ -z "$1" ] ; then
echo
echo "ERROR: root password Parameter missing."
exit
fi
MYSQL_USER=root
MYSQL_PASS=$1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
TBLLIST=""
COMMA=""
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
for DBTB in `mysql ${MYSQL_CONN} -ANe"${SQL}"`
do
echo OPTIMIZE TABLE "${DBTB};"
SQL="OPTIMIZE TABLE ${DBTB};"
mysql ${MYSQL_CONN} -ANe"${SQL}"
done

小开

如果你想分析、修复和优化MySQL服务器中所有数据库中的所有表，你可以从命令行一气呵成。不过，您将需要root才能做到这一点。

mysqlcheck -u root -p --auto-repair --optimize --all-databases

运行该命令后，系统将提示您输入MySQL根密码。在那之后，它将开始，你将看到结果，因为它正在发生。

示例输出:

yourdbname1.yourdbtable1       OK
yourdbname2.yourdbtable2       Table is already up to date
yourdbname3.yourdbtable3
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK


etc..
etc...


Repairing tables
yourdbname10.yourdbtable10
warning  : Number of rows changed from 121378 to 81562
status   : OK

如果您不知道根密码并且正在使用WHM，您可以通过执行以下操作从WHM内部更改它: Home > SQL Services > MySQL Root Password . Home > SQL Services > MySQL Root Password

小开

一个初始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

小开

如果你是直接访问数据库，那么你可以写以下查询:

OPTIMIZE TABLE table1,table2,table3,table4......;

小开

我的1美分，添加了and 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

小开

如果本地版主允许这样做，我想推广我很久以前写的一个PHP库- https://github.com/Simbiat/optimize-tables
图书馆的意义在于允许“智慧”;根据表的参数和统计数据执行OPTIMIZE, ANALYZE, CHECK和REPAIR命令。我已经为https://simbiat.ru在CRON中运行了2年多了，它一直很顺利(当然，除了一些调整和一些小修复)

你为什么要用这样的东西?嗯，README提供了更多细节，但简而言之，只有当您确实可以从中受益时，它才能帮助您运行相关操作。至少，它可以节省你的资源。

MySQL优化所有表?

它是如何工作的