MySQL有一个OPTIMIZE TABLE命令,可以用来回收MySQL安装中未使用的空间。是否有一种方法(内置命令或通用存储过程)可以为数据库和/或服务器安装中的每个表运行这种优化,还是必须自己编写脚本?
当前回答
我的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
其他回答
如果本地版主允许这样做,我想推广我很久以前写的一个PHP库- https://github.com/Simbiat/optimize-tables 这个库的重点是允许根据表的参数和统计数据“智能”执行OPTIMIZE, ANALYZE, CHECK和REPAIR命令。我已经在CRON https://simbiat.ru上运行它2年多了,它一直很顺利(当然,除了一些调整和小的修复)。
你为什么要用这样的东西?嗯,README提供了更多细节,但简而言之,只有当您确实可以从中受益时,它才能帮助您运行相关操作。至少,它可以节省你的资源。
我做了这个“简单”的脚本:
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语句并将其存储在一个准备好的语句中。然后在选中的集合中运行一个优化表。
您可以通过在var @tables_like中设置不同的值来控制要优化哪些表(例如:set @tables_like = '%test%';)。
从命令行:
mysqlcheck -o <db_name> -u<username> -p
然后输入密码
如果你想分析、修复和优化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,您可以通过转到: Home > SQL Services > MySQL Root密码
您可以在命令行中使用mysqlcheck来完成此操作。
一个数据库:
mysqlcheck -o <db_schema_name>
所有数据库:
mysqlcheck -o --all-databases
推荐文章
- 如何关闭mysql密码验证?
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql,而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N