-- 表维护的实施;
1.表维护操作对于确定和更正数据库问题十分有用,如以下问题:
– 由于服务器崩溃而导致表损坏;
– 对表的查询处理速度较慢;
2.可使用多种工具执行表维护:
– MySQL Workbench;
– MySQL Enterprise Monitor;
– SQL(DML)维护语句;
– 实用程序:
— mysqlcheck;
— myisamchk;
– 服务器自动恢复;
-- 用于表维护操作的SQL;
1.有多个SQL语句可用于执行表维护:
– ANALYZE TABLE:更新索引统计信息;
– CHECK TABLE:彻底检查完整性;
– CHECKSUM TABLE:彻底检查完整性 ;
– REPAIR TABLE:修复;
– OPTIMIZE TABLE:优化;
2.每个语句均包含一个或多个表名称和可选的关键字;
3.维护语句和输出的示例:
• Table:指示对其执行操作的表;
• Op:指出操作(检查,修复,分析或优化);
• Msg_type:指示成功或失败;
• Msg_text:提供其他信息;
mysql> CHECK TABLE City, City1;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| world.City | check | status | OK |
+------------+-------+----------+----------+
-- ANALYZE TABLE语句;
1.分析并存储表的键分布统计信息:在对多个对象执行联接操作时,MySQL 使用所存储的键分布统计信息来确定优化程序联接表的顺序;
2.用于更好地进行查询执行选择:键分布确定了MySQL用于查询中的特定表的索引;
3.执行ANALYZE TABLE语句来分析并存储统计信息,或者配置InnoDB,以便在大量数据发生更改之后或者在查询表或索引元数据时自动收集统计信息:
1.在分析过程中,对于InnoDB和MyISAM,MySQL使用读取锁来锁定表;
2.此语句等效于使用mysqlcheck --analyze;
3.需要对表有SELECT和INSERT特权;
4.支持分区表,还可以使用ALTER TABLE...ANALYZE PARTITION检查一个或多分区;
5.如果自从运行上一个ANALYZE TABLE语句后表未发生任何更改,则MySQL不会分析该表;
4.默认情况下,MySQL会将ANALYZE TABLE语句写入二进制日志并将这些语句复制到复制从属角色中,禁止使用可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL执行日志记录;
5.控制MySQL收集和存储键分布统计信息方式的选项:
1.innodb_stats_persistent:此选项为ON时,MySQL将对新创建的表启用STATS_PERSISTENT设置;
1.使用CREATE TABLE或ALTER TABLE语句时,还可以对表设置STATS_PERSISTENT;
2.默认情况下,MySQL不会将键分布统计信息持久保留在磁盘上,因此有时必须生成这些信息(如服务器重新启动后);
3.对于启用了STATS_PERSISTENT的表,MySQL会将其键分布统计信息存储在磁盘上,从而不需要频繁地为这些表生成统计信息;
4.随着时间推移,通过此操作优化程序可以创建更一致的查询计划;
2.innodb_stats_persistent_sample_pages:MySQL通过读取STATS_PERSISTENT表的索引页样例(而并非整个表)重新计算统计信息;
1.默认情况下,将读取20页样例;
2.增大此数字可提高所生成的统计信息和查询计划的质量;
3.降低此数字可减少用于生成统计信息的I/O成本;
3.innodb_stats_transient_sample_pages:此选项用于控制对没有STATS_PERSISTENT设置的表的抽样索引页数量;
以下选项用于控制MySQL自动收集统计信息的方式:
4.innodb_stats_auto_recalc:启用此选项时,如果STATS_PERSISTENT表中10%的行自前一次重新计算后有所变化,则MySQL将自动为该表生成统计信息;
5.innodb_stats_on_metadata:启用此选项可在执行元数据语句(如SHOW TABLE STATUS)或查询INFORMATION_SCHEMA.TABLES时更新统计信息;默认情况下,此选项处于禁用状态;
6.ANALYZE TABLE 正常结果的示例;
-- CHECK TABLE语句;
1.检查表结构的完整性,并检查内容中是否包含错误;对于MyISAM表,还将更新键统计信息;
2.验证视图定义:例如视图定义中引用的表不再存在;
3.支持分区表:支持分区表。还可以使用ALTER TABLE...CHECK PARTITION检查一个或多个分区;
4.处理InnoDB,CSV,MyISAM和ARCHIVE表:
5.CHECK TABLE选项:
1.FOR UPGRADE:服务器将检查每个表以确定表结构是否与当前的MySQL版本兼容,可能会因为某种数据类型的存储格式或排序顺序发生变化而出现不兼容的情况;如果出现潜在的不兼容情况,则服务器将对表运行全面检查,如果全面检查成功,则服务器会使用当前的MySQL版本号标记表的.frm文件;对.frm文件进行标记可以确保以后对于与服务器版本相同的表进行检查的速度会加快;
2.QUICK:不扫描行来检查错误链接;
6.如果CHECK TABLE发现InnoDB表出现问题:
1.服务器将关闭,以防止错误扩散;
2.MySQL会将错误写入错误日志;
7.如果CHECK TABLE的输出表明某个表出现问题,请修复该表;
1.可以先使用CHECK TABLE语句检测硬件问题(如内存故障或磁盘扇区损坏),然后再修复表;
2.Msg_text输出列通常为OK,如果输出不是OK或Table is already up to date,请对该表运行修复;
3.如果该表被标记为corrupted或not closed properly,但CHECK TABLE在表中未发现任何问题,则会将该表标记为OK;
-- CHECKSUM TABLE语句;
1.报告表checksum:
1.CHECKSUM TABLE需要对表有SELECT特权;
2.用于验证表的内容在备份,回滚或其他操作前后是否相同;
3.对于不存在的表,CHECKSUM TABLE将返回NULL并生成警告;
2.逐行读取整个表以计算校验和:
1.默认的EXTENDED选项提供了此行为(将逐行读取整个表,并计算checksum);
2.QUICK选项对MyISAM表可用;
1.将报告实时表checksum(如果可用);否则将报告NULL,此操作非常快;
2.通过在创建表时指定CHECKSUM=1 表选项,对MyISAM表启用了实时checksum;
3.checksum值取决于表中的行格式,如果行格式发生了变化,则checksum也会更改;例如,VARCHAR的存储格式在MySQL 4.1之后的版本中有所变化,因此,在将4.1表升级到更高版本后,如果表中包含 VARCHAR字段,则checksum值将发生变化;
3.CHECKSUM TABLE语句的示例;
TIPS:如果两个表的checksums不同,则很可能这两个表存在某方面的差异;不过,因为CHECKSUM TABLE使用的散列函数无法保证不冲突,所以存在两个不同的表生成相同checksum的微弱可能性;
-- OPTIMIZE TABLE语句;
1.通过对表进行碎片整理来清理表
1.通过重新构建表并释放未使用的空间对表进行碎片整理;
2.合并被分隔开的记录和以非连续方式存储的记录;
3.需要对表有SELECT和INSERT特权;
2.在优化过程中锁定表;
3.更新索引统计信息:
1.例如,修改大量行之后,可以使用OPTIMIZE TABLE语句在InnoDB中重构一个FULLTEXT索引;
2.对于InnoDB表,OPTIMIZE TABLE将映射到ALTER TABLE,后者将重构表以更新索引统计信息并释放群集索引中未使用的空间;
3.InnoDB不会像其他存储引擎一样受碎片影响,因此不需要经常使用OPTIMIZE TABLE;
4.最适用于完全填充的永久表:
5.处理InnoDB,MyISAM和ARCHIVE表:
1.对使用ARCHIVE存储引擎的表使用OPTIMIZE TABLE可以压缩该表;
2.由SHOW TABLE STATUS所报告的ARCHIVE表中的行数始终比较准确;
3.优化操作过程中可能会出现一个.ARN文件;
6.支持分区表:还可以使用ALTER TABLE...OPTIMIZE PARTITION检查一个或多个分区;
7.OPTIMIZE TABLE选项:
1.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
8.已删除的行将保留在链接的列表中,而后续的INSERT操作将重用之前行的位置,OPTIMIZE TABLE对完全填充的表使用时效果最佳并且不会发生很大更改;如果数据更改较多并经常需要优化,则优化的优势将会大大降低;
-- REPAIR TABLE语句;
1.修复可能已损坏的MyISAM或ARCHIVE表;
1.不支持InnoDB;
2.支持分区表;
3.REPAIR TABLE选项:
1.QUICK:尝试仅修复索引文件,而不修复数据文件;此类型的修复与myisamchk --recover --quick所执行的修复相似;
2.EXTENDED:MySQL将逐行创建索引,而不是一次性创建有序索引;此类型的修复与myisamchk --safe-recover所执行的修复相似;
3.USE_FRM:使用.FRM文件重新创建.MYI文件;选项不能用于分区表
4.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
4.TIPS:
1.在执行表修复操作之前,最好对表进行备份;在某些情况下,该操作可能导致数据丢失;可能的原因包括(但不仅限于)文件系统错误;
2.如果服务器在REPAIR TABLE操作过程中崩溃,则为避免进一步的损坏,重启之后应立即执行另一REPAIR TABLE,然后再执行其他任何操作;
3.如果经常需要使用REPAIR TABLE从损坏的表进行恢复,请尝试找出根本原因,以防止相应损坏并避免使用REPAIR TABLE;
-- mysqlcheck客户机程序;
1.是用于检查,修复,分析和优化表的命令行客户机;
2.比发出SQL语句更加方便;
1.例如,如果提供数据库名称作为其参数,则mysqlcheck将确定该数据库所包含的表,并发出语句处理所有这些表;不需要提供明确的表名称作为参数;
2.由于mysqlcheck是命令行程序,因此可以在执行计划维护的操作系统作业中轻松使用该程序;
3.处理InnoDB,MyISAM和ARCHIVE表;
4.三种检查级别:
1.特定于表;
2.特定于数据库;
3.所有数据库;
5.部分mysqlcheck维护选项:
1.--analyze:执行ANALYZE;
2.--check:执行CHECK TABLE(默认);
3.--optimize:执行OPTIMIZE TABLE;
4.--repair:执行REPAIR TABLE;
6.Oracle建议首先在不使用任何选项的情况下运行mysqlcheck,如果需要修复再重新运行;
7.部分mysqlcheck修改选项:
1.--repair --quick:尝试快速修复;
2.--repair:正常修复(如果快速修复失败);
3.--repair --force:强制修复;
8.mysqlcheck示例:
shell> mysqlcheck --login-path=admin world_innodb
shell> mysqlcheck -uroot -p mysql user --repair
shell> mysqlcheck -uroot -p --all-databases
shell> mysqlcheck --login-path=admin --analyze --all-databases
9.默认情况下,mysqlcheck将其第一个非选项参数解释为数据库名称,并检查该数据库中的所有表;如果数据库名称后面有其他任何参数,则会将这些参数视为表名称,从而只检查这些表;
本幻灯片中显示的 mysqlcheck 示例演示了以下内容:
-- myisamchk实用程序;
1.是用于检查MyISAM表的非客户机实用程序;
2.myisamchk与mysqlcheck具有相似的用途,其差异包括:
1.可以启用或禁用索引;
2.不与MySQL服务器通信,直接(而不是通过服务器)访问表文件;
3.在使用myisamchk执行表维护的同时避免并发表访问:
1.确保服务器不会访问正在进行处理的表,一种实现方法是锁定表或停止服务器;
2.在命令提示符中,将位置更改为表所在的数据库目录;这是服务器数据目录的子目录,该目录的名称与要检查的表所在的数据库名称相同;(更改位置是为了更加便于引用表文件,可以跳过此步骤,但myisamchk必须包含表所在的目录;)
3.调用myisamchk,使用选项指示要执行的操作,后跟参数以指定myisamchk应对其执行操作的表;这些参数可以是表名称,也可以是表的索引文件的文件名(索引文件名与表名称相同,包含.MYI后缀);因此,可以通过table_name或table_name.MYI引用表;
4.重新启动服务器:请首先尝试--recover,因为--safe-recover比较慢;
3.部分myisamchk 选项:
1.--recover:修复表;
2.--safe-recover:修复--recover无法修复的表;
4.myisamchk示例:
shell> myisamchk /var/lib/mysql/mysql/help_topic
shell> myisamchk help_category.MYI
shell> myisamchk --recover help_keyword
-- mysqlcheck和myisamchk的选项;
1.mysqlcheck和myisamchk均使用多个选项来控制所执行的表维护操作的类型;
2.两者都适用:
• --analyze:分析表中键值的分布,通过加快基于索引的查找,这可以提高查询的性能;
• --check 或 -c:检查表中是否存在问题,如果未指定其他任何操作,则为默认操作;
• --check-only-changed 或 -C:跳过表检查(自上一次检查后已更改的表或未正常关闭的表除外),如果服务器在表打开时崩溃,则会出现后一种情况;
• --fast 或 -F:跳过表检查(未正常关闭的表除外);
• --medium-check 或 -m:运行中等表检查;
• --quick 或 -q:对于mysqlcheck,不包含修复选项的--quick会导致只检查索引文件,而不检查数据文件;对于这两个程序,将--quick与修复选项结合使用都会导致程序只修复索引文件,而不修复数据文件;
3.mysqlcheck & myisamchk:
• --auto-repair:如果检查操作发现了问题,则自动修复出现问题的表;
• --repair、--recover 或 -r:运行表修复操作;
• --extended、--extend-check 或 -e:运行扩展表检查,对于mysqlcheck,将此选项与修复选项结合使用时,将执行比单独使用修复选项时更彻底的修复;即,--repair --extended执行的修复操作比--repair执行的操作更彻底;
-- InnoDB表维护;
1.出现故障之后,InnoDB将自动恢复;
2.使用CHECK TABLE或客户机程序可找出不一致,不兼容和其他问题;
3.如果表检查表明存在问题,可以通过使用mysqldump对表进行转储来恢复该表:
1.备份表内容:shell> mysqldump > ;
2.然后,删除该表并从转储文件重新创建:shell> mysql < ;
4.如果MySQL服务器或其运行主机崩溃,则某些InnoDB表可能处于不一致状态,在InnoDB的启动序列中,会执行自动恢复;服务器很少因为自动恢复故障而无法启动,如果出现此情况,则可以:
1.重新启动服务器,将--innodb_force_recovery选项的值设置为1到6之间的值;这些值表示增加警告级别以避免崩溃,以及针对已恢复的表中可能存在的不一致状况增加容错级别,最好从值4开始,该值设置为只读模式;
1.1(SRV_FORCE_IGNORE_CORRUPT):即便检测到坏页也让服务正常运行,可以尝试使用SELECT * FROM tbl_name来跳过损坏的索引记录和页,对于导出数据比较有帮助;
2.2(SRV_FORCE_NO_BACKGROUND):阻止master线程和任何的purge线程运行;
3.3(SRV_FORCE_NO_TRX_UNDO):实例恢复后不运行事务回滚;
4.4(SRV_FORCE_NO_IBUF_MERGE):阻止插入缓存合并操作;不会统计表的统计信息;这个值会永久性的破坏数据文件,在使用这个值之后需要导出数据,删掉对象然后再重新创建对象;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
5.5(SRV_FORCE_NO_UNDO_LOG_SCAN):在数据库启动时不会去查看UNDO日志,InnoDB会把未提交事务处理为已提交;这个值会永久性的破坏数据文件,在MySQL 5.6.15之后,会设置InnoDB为只读模式;
6.6(SRV_FORCE_NO_LOG_REDO):在实例恢复时不会前滚REDO日志;这个值会永久性的破坏数据文件,使数据库的页为过期状态;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
2.当在--innodb_force_recovery设置为非零值的情况下启动服务器时,InnoDB将阻止INSERT,UPDATE或DELETE操作;因此,您应转储InnoDB表,然后在该选项生效时将这些表删除,再在不使用--innodb_force_recovery选项的情况下重新启动服务器;服务器启动之后,将从转储文件恢复InnoDB表;
3.如果前述步骤失败,则从前一个备份恢复表;
5.使用ALTER TABLE进行优化时,将重构表并释放群集索引中未使用的空间;
-- MyISAM表维护;
1.使用CHECK TABLE...MEDIUM(默认选项):
1.对于动态格式表和静态格式表,默认的CHECK TABLE检查类型均为MEDIUM;如果将静态格式表类型设置为CHANGED或FAST,则默认选项为QUICK;对于CHANGED和FAST,将跳过行扫描,因为这些行很少损坏;
1.如果表被标记为“已损坏”或“未正常关闭”,则CHECK TABLE将更改表;
2.如果未在表中发现任何问题,则会将表的状态标记为“最新”;
3.如果表已损坏,则问题最有可能存在于索引而不是数据中;
2.使用客户机程序运行myisamchk:shell> myisamchk --medium-check ;
3.表损坏通常发生在索引中,因而执行这些检查很有帮助;
4.如果表已损坏,则实用程序将修复该表;
2.设置服务器以运行检查并自动修复表:
1.使用--myisam-recover选项启用自动修复;选项值可以包含以逗号分隔的值列表,由以下一个或多个值组成:
1.DEFAULT:默认检查;
2.BACKUP:指示服务器对必须进行更改的所有表进行备份;
3.FORCE:执行表恢复,即使可能导致多行数据丢失也是如此;
4.QUICK:执行快速恢复;恢复将跳过一些不包含因删除或更新而产生的行间隔(也称为“洞”)的表;
2.服务器将在启动之后第一次访问每个MyISAM表时进行检查,以确保这些表前一次正确关闭;
3.强制从config文件恢复MyISAM表;
eg:要指示服务器对发现问题的MyISAM表执行强制恢复,但同时要备份其更改的所有表,请向选项文件中添加以下内容:
[mysqld] myisam-recover=FORCE,BACKUP;
补充:
MySQL在创建表的时候定义表的性质,共有三种状态:静态表,动态表,压缩表;
1.默认是静态表;
2.动态表:如果存在varchar,blob,text字段,表类型就是动态表;
3.压缩表:只读,使用很少的空间,使用myisampack工具创建;
-- MEMORY表维护;
1.使用DELETE...WHERE语句删除多个行时,MEMORY表不会释放内存;
2.要释放内存,必须执行空值ALTER TABLE操作;
-- ARCHIVE表维护;
1.ARCHIVE压缩问题:
1.插入表行时将对其进行压缩;
2.检索时,将根据需要对行进行解压缩;
3.一些SELECT语句可能会减弱压缩功能;
2.使用OPTIMIZE TABLE或REPAIR TABLE可以实现更好的压缩;
3.在未对表进行访问(读或写)时,OPTIMIZE TABLE有效;
-- 课后练习;