网站建设演示ppt模板下载,网络推广引流有哪些渠道,做自媒体需要用的网站,dw简单的网页代码原文#xff1a;http://t.dbdao.com/archives/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86-%E8%A1%A8%E7%9A%84%E7%BB%B4%E6%8A%A4.html一、目标完成本课程后#xff0c;你应该能够#xff1a;认识不同类型的表维护操作执行维护表的SQL语句使用客户端和实用程序维护…原文http://t.dbdao.com/archives/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86-%E8%A1%A8%E7%9A%84%E7%BB%B4%E6%8A%A4.html一、目标完成本课程后你应该能够认识不同类型的表维护操作执行维护表的SQL语句使用客户端和实用程序维护表根据具体的存储引擎维护表二、实施表的维护表的维护操作对识别和解决数据库问题非常有用例如因服务器崩溃而损坏的表表上查询处理缓慢许多工具可以执行表的维护MySQL Workbench(MySQL工作台)MySQL Enterprise Monitor(MySQL企业版监视器)SQL(DML)Maintenance statements(SQL 维护语句)工具mysqlcheckMyisamchkServer auto-recovery(服务器自动恢复)三、使用SQL进行表的维护操作有多条SQL语句可以执行表的维护ANALYZE TABLE : 更新索引的统计信息CHECK TABLE : 检查表的完整性CHECKSUM TABLE : 报告表数据的一致性检测结果REPAIR TABLE : 修复表OPTIMIZE TABLE : 优化表每条语句需要一个或多个表名和可选关键字。一条维护语句和输出的样例备注在执行请求的操作之后服务器返回操作的结果信息到客户端。结果信息以四列格式显示Table : 标示执行操作的表Op : 操作的名字(check,repair,analyze,或optimize)Msg_type : 提供一个成功或失败的指示器Msg_text : 提供额外的信息如果想获取关于MySQL表维护的更多信息可以参照文档MySQL Reference Manual一、ANALYZE TABLE(分析表)语句分析并存储表的键值分布统计信息为执行查询提供更好的选择对使用InnoDB,NDB和MyISAM存储引擎的表生效支持分区表ANALYZE TABLE选项NO_WRITE_TO_BINLOG或LOCAL : 不记录二进制日志一条ANALYZE TABLE语句结果的样例备注当你执行一个除常数之外的连接时MySQL使用存储的键值分布统计信息决定如何优化表连接的顺序。另外键值分布决定了查询中具体的表MySQL使用什么索引。你可以执行ANALYZE TABLE语句分析和存储统计信息或者你可以配置InnoDB在数据变化后或者你查询表或索引的元数据时将会自动收集统计信息。ANALYZE TABLE 的特点在分析过程中MySQL在使用InnoDB和MyISAM引擎的表上加上一个只读锁这条语句等价于使用mysql check –analyze语句需要有表的SELECT和INSERT权限支持分区表。你也可以使用ALTER TABLE …ANALYZE PARTITION来检查一个或多个分区。如果表自从上次执行ANALYZE TABLE语句后没有发生过改变MySQL将不分析该表。默认情况下MySQL将ANALYZE TABLE语句写入二进制日志并且将其复制到从库。使用可选的关键字NO_WRITE_TO_BINLOG或它的别名LOCAL来设置不记录日志。续备注你可以使用以下选项控制MySQL如何收集和存储键值分布统计信息innodb_stats_persistent :当该选项设置为ONMySQL将在新建的表上启用STATS_PERSISTENT设置。你也可以在使用CREATE TABLE 或者 ALTER TABLE 语句时使用STATS_PERSISTENT。默认情况下MySQL不会持续将键值分布的统计信息写入磁盘所以它们(统计信息)必须在某些时间(重新)生成比如服务器重启。当(对某些表)启用STATS_PERSISTENT时MySQL将存储这些表的键值分布统计信息至磁盘这样这些表将不需要再频繁生成统计信息。这使得随着时间的推移优化器可以创建更一致(稳定)的查询计划。innodb_stats_persistent_sample_pages :MySQL通过对STATS_PERSISTENT表的索引页采样而非整张表重新生成统计信息。默认情况下它对样例的20页进行采样增加页数可以提升生成的统计信息的准确性和生成更精准的查询计划。减少页数可以减少生成统计信息时I/O的消耗。innodb_stats_transient_sample_pages :当STATS_PERSISTENT未设置的时候该选项控制表上索引页的采样数量。以下选项控制MySQL如何自动收集统计信息。innodb_stats_auto_recalc :启用该选项时当STATS_PERSISTENT表自上次重新统计后有10%行记录改变时MySQL将自动收集统计信息。innodb_stats_on_metadata :当你执行SHOW TABLE STATUS或其他元数据语句或当你查询INFOMATION_SCHEMA.TABLES时启用该选项将更新统计信息。默认情况下该选项是禁用的。一、CHECK TABLE(检查表)语句检查表结构和内容的完整性验证视图定义支持分区表对使用InnoDB,CSV,MyISAM和ARCHIVE(存储引擎)的表生效CHECK TABLE选项FOR UPGRADE : 检查当前服务器的表是否工作QUICK : 不扫描不正确链接的行如果CHECK TABLE发现使用InnoDB存储引擎的表有错误服务器将会被关闭以防止错误传播MySQL将会记录错误到错误日志备注CHECK TABLE特点对于使用MyISAM存储引擎的表键值统计信息也会被更新。也可以检查视图的问题例如表中引用的视图定义并不存在。支持分区表。你也可以使用ALTER TABLE…CHECK PARTITION来检查一个或多个分区。对于FOR UPGRADE子句服务器检查每个表以确认表结构是否与当前版本的MySQL兼容。数据类型的存储格式更改或者它的排序顺序改变都可能导致不兼容。如果这里可能存在不兼容服务器将在表上运行一个完整的检查如果完整检查成功服务器将会在表的.frm文件上标记当前MySQL版本的数字。标记.frm文件可以确保该表未来做(与文件标记)同样版本服务器检查时快速检查。在InnoDB,MyISAM和ARCHIVE存储引擎上可使用FOR UPGRADE。使用InnoDB和MyISAM存储引擎的表上可使用QUICK。MyISAM支持更多选项。续五、CHECK TABLE(检查表)语句一个好的CHECK TABLE结果样例备注如果CHECK TABLE的输出表明表有问题修复该表。例如你可以在修复表之前使用CHECK TABLE语句检测硬件问题(例如内存错误或磁盘扇区损坏)。Msg_text输出列正常显示为“OK”。如果得到的(结果)不是“OK”或“Table is already up to date”对表执行修复。如果表被标记为“corrupted”或“not closed properly”但CHECK TABLE没有在表上发现任何错误将会标记表为“OK”。一、CHECKSUM TABLE(校验表)语句报告表的checksum(该语句)用来验证表备份回滚或者其他操作之前或之后内容是否一致读取整个表逐行进行校验默认选项EXTENDED提供这种行为QUICK选项在MyISAM表上可用。MyISAM 上默认选项为设置CHECKSUM1。一条CHECKSUM TABLE语句的样例备注CHECKSUM TABLE特点CHECKSUM TABLE需要SELECT表的权限对于不存在的表CHECKSUM TABLE返回“NULL”并且生成一条警告如果使用了EXTENDED选项逐行读取整张表并且计算checksum的值。如果使用QUICK选项如果表的在线checksum可用将会给出报告否则报告(显示)为“NULL”这操作将会很快速。你创建表时为MyISAM表指定CHECKSUM1将会启用在线checksum。如果既没有指定QUICK又没有指定EXTENDEDMySQL设置选项为EXTENDED除非MyISAM表CHECKSUM1。checksum的值取决于表的行记录的格式如果行记录的格式改变checksum也会改变。比如“VARCHAR”存储类型在MySQL4.1之后发生了改变所以如果你升级一张4.1版本的表到更新的版本包含VARCHAR列的表的checksum值将会改变。续备注注意如果两张表的checksums值不同这可能说明它们可能在某些方面不同。自从CHECKSUM TABLE使用哈希函数后不能保证没有冲突这将会有极小的可能导致两张不同的表得到相同的checksum值。一、OPTIMIZE TABLE(优化表)语句整理表的碎片重建表和释放未使用的空间以整理碎片优化过程中会锁定表更新索引统计信息在一张永久的数据密集/完全填充的表上是比较好的在InnoDB,MyISAM和ARCHIVE表上生效支持分区表OPTIMIZE TABLE选项NO_WRITE_TO_BINLOG或LOCAL不记录二进制日志备注OPTIMIZE TABLE特点对删除、更新和接合记录等引起的分散的非连续的数据中涉及到的未使用的可回收空间进行碎片整理需要表的SELECT和INSERT权限支持分区表。你可以使用ALTER TABLE…OPTIMIZE PARTITION检查一个或多个分区。例如你可以在表的行记录数发生大幅度修改后使用OPTIMIZE TABLE语句在InnoDB中重建一个FULLTEXT(全文)索引。在InnoDB表中OPTIMIZE TABLE和ALTER TABLE类似将重建表以更新索引统计信息和群集索引中空闲的未使用的空间InnoDB和其他存储引擎产生碎片的方式不同所以你不需要经常(在InnoDB上)使用OPTIMIZE TABLE。在ARCHIVE存储引擎上使用OPTIMIZE TABLE可以压缩表。使用SHOW TABLE STATUS语句查看ARCHIVE表行数产生的结果往往比较准确。在执行优化操作时会产生一个.ARN文件。续七、OPTIMIZE TABLE(优化表)语句以下OPTIMIZE TABLE语句优化了两张mysql数据库中数据密集/完全填充的表备注对于MyISAM表在对表的大部分数据删除或者在可变长度行记录(包含VARCHARVARBINARYBLOB或TEXT列)做出修改后使用OPTIMIZE TABLE语句。被删除的行都保存在一个链接列表中并且随后的INSERT(插入)操作重复使用旧的行的位置。OPTIMIZE TABLE在完全填充且数据很少改变的表上有较好的优化小故宫。当数据变化比较多时优化效果变差并且你不得不经常进行优化。一、REPAIR TABLE(修复表)语句修复可能已经损坏的MyISAM或ARCHIVE表不支持InnoDB优化过程中会锁定表支持分区表REPAIR TABLE选项QUICK : 仅仅修复索引树EXTENDED : 逐行创建索引(替代以排序方式一次创建一个索引)USE_FRM : 使用.FRM文件重新创建 .MYI 文件NO_WRITE_TO_BINLOG或LOCAL不记录二进制日志备注REPAIR TABLE特点QUICK 选项仅仅尝试对索引而非数据文件进行修复。这个修复选项和myisamchk –recover –quick 接近。EXTENDED选项MySQL逐行创建索引而非以排序方式一次创建一个索引。这种类型修复与myisamchk –safe-recover接近。USE_FRM选项不能在分区表上使用。需要表上的SELECT和INSERT权限。支持分区表。你可以使用ALTER TABLE…REPAIR PARTITION来检查一个或多个分区。最好在队标执行修复操作之前对表进行备份在某些情况下该操作可能会导致数据丢失。可能会引起(但是不限于)数据文件错误。如果在执行REPAIR TABLE操作期间服务器崩溃你必须在服务器重启之后执行其他任何操作之前再次执行REPAIR TABLE指令来避免进一步的错误。如果你需要频繁的执行REPAIR TABLE语句来修复某张故障表尝试去找出底层原因来预防类似错误并且消除使用REPAIR TABLE的需求。续八、REPAIR TABLE(修复表)语句一个REPAIR TABLE语句样例一、mysqlcheck客户端程序mysqlcheck是一条检查修复分析和优化表的客户端命令行可以更方便的执行一条SQL语句对InnoDBMyISAM和ARCHIVE存储引擎的表生效三个检查级别指定表指定数据库指定所有数据库一些mysqlcheck维护选项–analyze : 执行一条ANALYZE TABLE语句–check : 执行一条CHECK TABLE语句(该选项为默认选项)–optimize : 执行一条OPTIMIZE TABLE语句–repair : 执行一条REPAIR TABLE语句备注在某些情况下mysqlcheck可以更方便直接地执行SQL语句。例如日如果你将数据库名作为参数mysqlcheck会对该数据库中包含的所有表和问题语句都进行处理你不需要提供明确的表名作为参数。同样的由于mysqlcheck是一个命令行程序你可以方便地使用它在操作系统作业上执行计划管理。续九、mysqlcheck客户端程序Oracle建议(为什么不是Mysql建议⊙﹏⊙)先不加选项执行myqlcheck命令。如果需要修复再次执行它。一些mysqlcheck限制选项–repair –quick : 尝试执行一次快速修复–repair : 常规修复(如果快速修复失败)–repair –force : 强制执行一次修复mysqlcheck样例备注默认情况下mysqlcheck将第一个非选项参数解读为数据库名并且检查数据库中所有表。如果在数据库名参数后有其他参数将被作为表名并且仅仅检查这些表。上图展示的mysqlcheck 样例说明了以下内容第一条带选项的指令仅仅检查world_innodb数据库中的City和Country表。带有–database(或 -B)选项的样例中,mysqlcheck会将该参数作为数据库名并且检查名为world_innodb和test数据库中的表。带有–all-database(或 -A)选项时mysqlcheck检查所有数据库中的所有表。一、myisamchk 工具myisamchk 是一个检查MyISAM引擎表的非客户端实用工具myisamchk 和mysqlcheck工具有以下差异它可以启用和禁用索引它可以不通过服务器直接访问表文件这避免了并发表查询一些myisamchk 的选项–recover : 修复一张表–safe-recover : 修复一张不能使用–recover选项修复的表myisamchk样例备注从概念上看myisamchk和mysqlcheck在功能上很相似。然而myisamchk并不和MySQL服务器通信。相反的它直接从文件访问表。在执行表维护时使用myisamchk可以避免表的并发访问确保在服务器工作时不能访问表。保障的方法是锁定表或者停止服务器。在命令提示符中将当前目录更改为数据库中存放表的路径。 这是具有和数据库包含的将要被检查的表有同样名字的服务器的数据目录的子目录(切换目录的原因是可以更方便的查看表文件)使用选项调用myismchk指明将要执行的操作跟在myismchk指令后的参数指定将要被操作的表名。每个参数都可以是表名或表上索引文件的名字。索引文件名和表名一致并添加.MYI后缀。因此一张表可以被写作table_name(表名)或MYI(该表的索引文件名)。重启服务器。注意先使用–recoverinwei–safe-recover会慢许多。一、mysqlcheck和myisamchk的选项控制维护执行类型的选项备注myisamchk和mysqlcheck都有多个控制表维护操作类型的选项。上图的表中列举了一些常用的选项其中大部分可以在两个程序(mysqlcheck和myisamchk)中使用。除了这种情况外(其余情况)将在相关选项说明中指出。–analyze :分析表中键值分布。这可以提高基于索引查找的查询语句速度的性能。–auto-repair :如果检查操作发现问题自动修复表。–check 或 -c :检查表的问题。当没有指定其他选项时该选项为默认动作。–check-only-changed 或 -c :跳过表查询除非表自从上次检查后发生了改变或者没有正确关闭。后者可能在表打开时服务器崩溃的情况下发生。–fast 或 -F :跳过表检查除非表没有被正确关闭。–extended,–extend-check,或 -e :运行扩展的表检查。对于mysqlcheck当该选项和repair(修复)选项一起使用时将会比单独使用repair (修复)选项时进行更全面彻底的修复。就是说使用–repair –extended选项比使用 –repair选项执行更彻底的修复操作。续十一、mysqlcheck和myisamchk的选项–extended,–extend-check,或 -e :运行中级表检查–quick或-q :对mysqlcheck指令使用–quick选项不使用repair(修复)选项仅仅只有索引文件被检查而不检查数据文件。对于两个程序(mysqlcheck和myisamchk)使用–quick选项和repair(修复)选项仅仅只有索引文件被检查而不检查数据文件。–repair,–recover,或 -r :运行一个表修复操作。一、InnoDB表维护InnoDB引擎表在故障发生后自动修复使用CHECK TABLE或者一个客户端程序找出不一致、不兼容和其他问题。使用mysqldump语句通过导出还原一张表然后删除它并且从导出的文件重新创建表。为了在(服务器)崩溃后恢复表使用–innodb_force_recovery选项启动服务器或者从一个备份复原表。使用ALTER TABLE重建表和释放在群集索引中未使用的空间以进行优化备注如果表检查显示有问题使用mysqldump指令通过导出还原表到一致性状态删除它然后再从导出的文件重建表。万一发生数据库运行的主机或者服务器崩溃使用InnoDB存储引擎的表可能额处于不一致的状态。作为(服务器)启动的一个步骤InnoDB存储引擎将执行自动恢复。极少见的情况下可能服务器由于错误的自动恢复不能启动。如果遇到这样的情况可以尝试以下的过程重启服务器时将–innodb_force_recovery选项值设置为1 – 6。这个值表明避免崩溃警告的增进级别和恢复表时宽限表可能不一致的增进级别。建议值从4开始可以防止insert buffer(插入缓冲区)合并操作发生。当服务器以参数–innodb_force_recovery选项设置为非零值启动时InnoDB阻止INSERT,UPDATE或DELETE操作。因此当该选项生效时你需要导出InnoDB表然后删除表。然后再去掉–innodb_force_recovery选项启动服务器当服务器启动后从导出的文件恢复表。如果前面描述的步骤都失败了从以前的备份还原表。一、MyISAM表维护使用CHECK TABLE…MEDIUM(该选项为默认选项)使用客户端程序运行myisamchk表损坏使用通常发生在索引中标记这些检查将会很有用。当表损坏时该工具修复表。设置服务器运行检查并且自动修复表。使用 –myisam-recover选项启用自动修复服务器在启动后第一次访问是检查每一张MyISAM表以确保上一次是正常关闭。根据“config”文件强制恢复MyISAM表。备注默认的CHECK TABLE(检查表)检查类型是为动态与静态格式的表执行MEDIUM(中级)检查。如果一个静态格式表类型设置为CHANGED或FAST则默认值为QUICK。对于CHANGED和FAST值将会跳过行扫描因为这些行很少会出现损坏。如果表被标记为“corrupted”或“not closed ”CHECK TABLE将修改表。如果没有发现表上有任何问题将会被标记为“up to date”。如果表有损坏问题多数出现在索引而不是数据上。选项 –myisam-recover可以包含以下一个或多个以逗号分割的列表DEFAULT : 默认检查BACKUP : 告诉服务器对必须改变的表进行备份FORCE : 即使丢失超过一行数据仍然执行表恢复QUICK :执行一次快速恢复。恢复跳过删除或更新的行没有间隔(known as“holes”)的表。续备注例如告知服务器对发现有问题的MyISAM表进行强制恢复但要对表改变的部分做一个备份添加以下内容到你的选项文件[mysqld]Myisam-recoverFORCE,BACKUP一、内存表维护当使用一条..WHERE语句删除行记录时MEMORY(内存)表不释放内存。为了释放内存你必须执行一条空ALTER TABLE操作。为了查看MEMORY(内存)表的存储要求执行SHOW TABLE STATUS并且添加Data_length和Index_length列。如果删除行没有重复值Data_free列为非零。备注以下是一条SHOW TABLE STATUS语句在world_innodb数据库City表副本CityCopy表上执行的输出结果。CityCopy表使用MEMORY存储引擎mysql SHOW TABLE STATUS LIKE ‘CityCopy’\G*************************** 1. row ***************************Name: CityCopyEngine: MEMORYVersion: 10Row_format: FixedRows: 4079Avg_row_length: 67Data_length: 383072Max_data_length: 10808373Index_length: 253984Data_free: 0…:1 row in set (0.00 sec)The total memory the CityCopy table occupies can be calculated as follows:Data_length (383072) Index_length (253984) 637056 bytes二、ARCHIVE表维护ARCHIVE压缩问题数据插入时表的行记录被压缩。进行检索时行记录可以根据需要不压缩、一些SELECT语句可能导致压缩恶化使用OPTIMIZE TABLE或REPAIR TABLE可以实现更好的压缩。OPTIMIZE TABLE在表没有被访问(读或写)时生效。三、总结在本课程中你已经学习到如何认识表维护操作的类型执行SQL语句维护表使用客户端和工具程序维护表根据具体的存储引擎维护表实践13-1概览测验 – 维护表在本测验中你需要回答关于MySQL表维护的问题。实践13-2概览使用维护表的SQL语句在本测验中你使用SQL语句检查和优化表。实践13-3概览使用表维护工具在本测验中你使用MySQL命令行工具检查并修复一张表。
