MySQL MySQL优化所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5474662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL OPTIMIZE all tables?
提问by Alan Storm
MySQL has an OPTIMIZE TABLEcommand which can be used to reclaim unused space in a MySQL install. Is there a way (built-in command or common stored procedure) to run this optimization for every table in the database and/or server install, or is this something you'd have to script up yourself?
MySQL 有一个OPTIMIZE TABLE命令，可用于回收 MySQL 安装中未使用的空间。有没有办法（内置命令或通用存储过程）为数据库和/或服务器安装中的每个表运行此优化，还是您必须自己编写脚本？
回答by Ike Walker
You can use mysqlcheckto do this at the command line.
您可以使用mysqlcheck在命令行执行此操作。
One database:
一个数据库：
mysqlcheck -o <db_schema_name>
All databases:
所有数据库：
mysqlcheck -o --all-databases
回答by Ismael Miguel
I made this 'simple' script:
我制作了这个“简单”的脚本：
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;
To run it, simply paste it in any SQL IDE connected to your database.
要运行它，只需将它粘贴到连接到数据库的任何 SQL IDE 中即可。
Notice: this code WON'T workon phpmyadmin.
注意：此代码不适用于 phpmyadmin。
How it works
这个怎么运作
It runs a show tablesstatement and stores it in a prepared statement. Then it runs a optimize tablein the selected set.
它运行一个show tables语句并将其存储在一个准备好的语句中。然后它optimize table在选定的集合中运行 a 。
You can control which tables to optimize by setting a different value in the var @tables_like(e.g.: set @tables_like = '%test%';).
您可以通过在 var 中设置不同的值来控制要优化的表@tables_like（例如：）set @tables_like = '%test%';。
回答by Scherbius.com
Following example php script can help you to optimize all tables in your database
以下示例 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());
}
}
?>
回答by Ivan Velkov
Do all the necessary procedures for fixing all tables in all the databases with a simple shell script:
使用一个简单的 shell 脚本执行所有必要的过程来修复所有数据库中的所有表：
#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze
回答by Muni
for all databases:
对于所有数据库：
mysqlcheck -Aos -uuser -p
For one Database optimization:
对于一个数据库优化：
mysqlcheck -os -uroot -p dbtest3
回答by Frank
From phpMyAdmin and other sources you can use:
您可以从 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>'
Then you can copy & paste the result to a new query or execute it from your own source.
If you don't see the whole statement:
然后您可以将结果复制并粘贴到新查询或从您自己的源执行它。如果您没有看到整个语句：
回答by Chris
If you want to analyze, repair and optimize all tables in all databases in your MySQL server, you can do this in one go from the command line. You will need root to do that though.
如果要分析、修复和优化 MySQL 服务器中所有数据库中的所有表，可以从命令行一次性完成。不过，您将需要 root 来执行此操作。
mysqlcheck -u root -p --auto-repair --optimize --all-databases
Once you run that, you will be prompted to enter your MySQL root password. After that, it will start and you will see results as it's happening.
运行后，系统将提示您输入 MySQL 根密码。之后，它将开始，您将看到正在发生的结果。
Example output:
示例输出：
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
If you don't know the root password and are using WHM, you can change it from within WHM by going to: Home > SQL Services > MySQL Root Password
如果您不知道 root 密码并且正在使用 WHM，您可以在 WHM 中更改它，方法是： 主页 > SQL 服务 > MySQL Root 密码
回答by si le
From command line:
从命令行：
mysqlcheck -o <db_name> -u<username> -p
then type password
然后输入密码
回答by Victor Driantsov
You can optimize/check and repair all the tables of database, using mysql client.
您可以使用 mysql 客户端优化/检查和修复数据库的所有表。
First, you should get all the tables list, separated with ',':
首先，你应该得到所有的表列表，用 ',' 分隔：
mysql -u[USERNAME] -p[PASSWORD] -Bse 'show tables' [DB_NAME]|xargs|perl -pe 's/ /,/g'
Now, when you have all the tables list for optimization:
现在，当您拥有所有要优化的表列表时：
mysql -u[USERNAME] -p[PASSWORD] -Bse 'optimize tables [tables list]' [DB_NAME]
回答by Jürgen Steinblock
The MySQL Administrator(part of the MySQL GUI Tools) can do that for you on a database level.
该MySQL Administrator（MySQL的GUI工具的一部分），可以为你做的数据库级别。
Just select your schema and press the Maintenancebutton in the bottom right corner.
只需选择您的架构并按下Maintenance右下角的按钮。
Since the GUI Tools have reached End-of-life status they are hard to find on the mysql page. Found them via Google: http://dev.mysql.com/downloads/gui-tools/5.0.html
由于 GUI 工具已达到生命周期终止状态，因此很难在 mysql 页面上找到它们。通过谷歌找到它们：http: //dev.mysql.com/downloads/gui-tools/5.0.html
I don't know if the new MySQL Workbench can do that, too.
我不知道新的 MySQL Workbench 是否也能做到这一点。
And you can use the mysqlcheckcommand line tool which should be able to do that, too.
您也可以使用mysqlcheck应该能够做到这一点的命令行工具。
