mysql优化数据表

一、优化数据表

当mysql进行表数据删除时，或者有很多变化和变长表行(VARCHAR表,VARBINARY、BLOB或文本列)进行了更改，会在数据文件中留下碎片所致。DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE `table_name` 优化表

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

OPTIMIZE TABLE运行过程中，MySQL会锁定表。

OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。

二、修复数据表

1、表损坏的原因分析

以下原因是导致mysql 表毁坏的常见原因：
①服务器突然断电导致数据文件损坏。
②强制关机，没有先关闭mysql 服务。
③mysqld 进程在写表时被杀掉。
④使用myisamchk 的同时，mysqld 也在操作表。
⑤磁盘故障。
⑥服务器死机。
⑦mysql 本身的bug 。

2、表损坏的症状

一个损坏的表的典型症状如下：
①当在从表中选择数据之时，你得到如下错误：
Incorrect key file for table: ‘…’. Try to repair it
②查询不能在表中找到行或返回不完全的数据。
③Error: Table ‘p’ is marked as crashed and should be repaired 。
④打开表失败： Can’t open file: ‘×××.MYI’ (errno: 145) 。

3、预防 MySQL 表损坏

可以采用以下手段预防mysql 表损坏：
①定期使用myisamchk 检查MyISAM 表（注意要关闭mysqld ），推荐使用check table 来检查表（不用关闭mysqld ）。

CHECK TABLE `tablename`

②在做过大量的更新或删除操作后，推荐使用OPTIMIZE TABLE 来优化表，这样既减少了文件碎片，又减少了表损坏的概率。
③关闭服务器前，先关闭mysqld （正常关闭服务，不要使用kill -9 来杀进程）。
④使用ups 电源，避免出现突然断电的情况。
⑤使用最新的稳定发布版mysql ，减少mysql 本身的bug 导致表损坏。
⑥对于InnoDB 引擎，你可以使用innodb_tablespace_monitor 来检查表空间文件内文件空间管理的完整性。
⑦对磁盘做raid ，减少磁盘出错并提高性能。
⑧数据库服务器最好只跑mysqld 和必要的其他服务，不要跑其他业务服务，这样减少死机导致表损坏的可能。
⑨不怕万一，只怕意外，平时做好备份是预防表损坏的有效手段。

4、MySQL表损坏的修复

MyISAM 表可以采用以下步骤进行修复 ：
①使用 reapair table 或myisamchk 来修复。

REPAIR TABLE `table_name` 修复表

②如果上面的方法修复无效，采用备份恢复表。

具体可以参考如下做法：
阶段1 ：检查你的表
如果你有很多时间，运行myisamchk *.MYI 或myisamchk -e *.MYI 。使用-s （沉默）选项禁止不必要的信息。
如果mysqld 服务器处于宕机状态，应使用–update-state 选项来告诉myisamchk 将表标记为’ 检查过的’ 。
你必须只修复那些myisamchk 报告有错误的表。对这样的表，继续到阶段2 。
如果在检查时，你得到奇怪的错误( 例如out of memory 错误) ，或如果myisamchk 崩溃，到阶段3 。
阶段2 ：简单安全的修复
注释：如果想更快地进行修复，当运行myisamchk 时，你应将sort_buffer_size 和Key_buffer_size 变量的值设置为可用内存的大约25% 。
首先，试试myisamchk -r -q tbl_name(-r -q 意味着“ 快速恢复模式”) 。这将试图不接触数据文件来修复索引文件。如果数据文件包含它应有的一切内容和指向数据文件内正确地点的删除连接，这应该管用并且表可被修复。开始修复下一张表。否则，执行下列过程：
在继续前对数据文件进行备份。
使用myisamchk -r tbl_name(-r 意味着“ 恢复模式”) 。这将从数据文件中删除不正确的记录和已被删除的记录并重建索引文件。
如果前面的步骤失败，使用myisamchk --safe-recover tbl_name 。安全恢复模式使用一个老的恢复方法，处理常规恢复模式不行的少数情况( 但是更慢) 。
如果在修复时，你得到奇怪的错误( 例如out of memory 错误) ，或如果myisamchk 崩溃，到阶段3 。
阶段3 ：困难的修复
只有在索引文件的第一个16K 块被破坏，或包含不正确的信息，或如果索引文件丢失，你才应该到这个阶段。在这种情况下，需要创建一个新的索引文件。按如下步骤操做：
把数据文件移到安全的地方。
使用表描述文件创建新的( 空) 数据文件和索引文件：
shell> mysql -u’dbuser’ -p’dbpass’ dbname
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
如果你的MySQL 版本没有TRUNCATE TABLE ，则使用DELETE FROM tbl_name 。
将老的数据文件拷贝到新创建的数据文件之中。（不要只是将老文件移回新文件之中；你要保留一个副本以防某些东西出错。）
回到阶段2 。现在myisamchk -r -q 应该工作了。（这不应该是一个无限循环）。
你还可以使用REPAIR TABLE tbl_name USE_FRM ，将自动执行整个程序。
阶段4 ：非常困难的修复
只有.frm 描述文件也破坏了，你才应该到达这个阶段。这应该从未发生过，因为在表被创建以后，描述文件就不再改变了。
从一个备份恢复描述文件然后回到阶段3 。你也可以恢复索引文件然后回到阶段2 。对后者，你应该用myisamchk -r 启动。
如果你没有进行备份但是确切地知道表是怎样创建的，在另一个数据库中创建表的一个拷贝。删除新的数据文件，然后从其他数据库将描述文件和索引文件移到破坏的数据库中。这样提供了新的描述和索引文件，但是让.MYD 数据文件独自留下来了。回到阶段2 并且尝试重建索引文件。

三、myisamchk参数说明

myisamchk的使用一般有三种方式：
$ myisamchk --recover --quick `tablename`
$ myisamchk --recover `tablename`
$ myisamchk --safe-recover `tablename`

第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题。

在使用myisamdchk修复会优化表时，必须保证mysqld服务器没有使用该表，最好关闭mysqld服务；如果不关闭mysqld，在运行myisamchk之前应执行mysqladmin flash-tables。如果服务器和myisamchk同时访问表，表可能会被破坏。

1、myisamchk的常用选项
--check, -c
检查表的错误。如果你不明确指定操作类型选项，这就是默认操作。

--check-only-changed, -C
只检查上次检查后有变更的表。

--extend-check, -e
非常仔细地检查表。如果表有许多索引将会相当慢。

--fast，-F
只检查没有正确关闭的表。

--force, -f
如果myisamchk发现表内有任何错误，则自动进行修复。

--information, -i
打印所检查表的统计信息。

--medium-check, -m
比--extend-check更快速地进行检查。只能发现99.99%的错误

--update-state, -U
将信息保存在.MYI文件中，来表示表检查的时间以及是否表崩溃了。该选项用来充分利用--check-only-changed选项，
但如果mysqld服务器正使用表并且正用--skip-external-locking选项运行时不应使用该选项。

--read-only, -T
不要将表标记为已经检查。如果你使用myisamchk来检查正被其它应用程序使用而没有锁定的表很有用

2、myisamchk的修复选项
--backup, -B
将.MYD文件备份为file_name-time.BAK

--character-sets-dir=path
字符集安装目录。

--correct-checksum
纠正表的校验和信息。

--data-file-length=len, -D len
数据文件的最大长度

--extend-check，-e
进行修复，试图从数据文件恢复每一行。一般情况会发现大量的垃圾行。不要使用该选项,除非你不顾后果。

--force, -f
覆盖旧的中间文件(文件名类似tbl_name.TMD)，而不是中断

--keys-used=val, -k val
对于myisamchk，该选项值为位值，说明要更新的索引。选项值的每一个二进制位对应表的一个索引，其中第一个索引对应位0。
选项值0禁用对所有索引的更新，可以保证快速插入。通过myisamchk -r可以重新**被禁用的索引。

--parallel-recover, -p
与-r和-n的用法相同，但使用不同的线程并行创建所有键。

--quick，-q
不修改数据文件，快速进行修复。

--recover, -r
可以修复几乎所有一切问题，除非唯一的键不唯一时(对于MyISAM表，这是非常不可能的情况)。如果你想要恢复表，
这是首先要尝试的选项。如果myisamchk报告表不能用-r恢复，则只能尝试-o。
在不太可能的情况下-r失败，数据文件保持完好。 

--safe-recover, -o
使用一个老的恢复方法读取，按顺序读取所有行，并根据找到的行更新所有索引树。这比-r慢些，
但是能处理-r不能处理的情况。该恢复方法使用的硬盘空间比-r少。一般情况，你应首先用-r维修，如果-r失败则用-o。

--sort-recover, -n
强制myisamchk通过排序来解析键值，即使临时文件将可能很大。


3、myisamchk的其他选项
myisamchk支持以下表检查和修复之外的其它操作的选项：

--analyze，-a
分析键值的分布。这通过让联结优化器更好地选择表应该以什么次序联结和应该使用哪个键来改进联结性能。
要想获取分布相关信息，使用myisamchk --description --verbose tbl_name命令或SHOW KEYS FROM tbl_name语句。

--sort-index, -S
以从高到低的顺序排序索引树块。这将优化搜寻并且将使按键值的表扫描更快。

--set-auto-increment[=value], -A
强制从给定值开始的新记录使用AUTO_INCREMENT编号(或如果已经有AUTO_INCREMENT值大小的记录，应使用更高值)。
如果未指定value，新记录的AUTO_INCREMENT编号应使用当前表的最大值加上1。

--description, -d
打印出关于表的描述性信息。

参考链接：
1、MYSQL数据表损坏的原因分析和修复方法小结
http://www.imooc.com/article/41221
2、myisamchk修复丢失索引文件和数据格式文件
http://home.51.com/skyking0815/diary/item/10048759.html
3、myisamchk命令使用总结
https://www.cnblogs.com/gengwudaxia/p/5956017.html