MySql 高性能 5.5 维护索引和表 189页

维护表有三个主要目的：

1 找到并修复损坏的表

2 维护准确的索引统计信息

3 减少碎片

5.5.1 找到并修复损坏的表

   

对于MyISAM 存储引擎  ，表损坏通常是系统崩溃导致的。

其他引擎也会由于硬件问题 MySql本身的缺陷或者操作系统的问题导致。

Innodb 引擎的表出现损坏，一定是出现了严重的错误。一般不会出现损坏，因为设计保证了它不容易出现损坏。

出现损坏的原因：

1 数据库硬件问题，比如内存或者磁盘问题。

2 数据库管理员的 误操作，例如在MySq外部操作了数据文件。

3 Innodb本身缺陷。

常见的错误 比如尝试使用rsync 备份Innodb导致。不存在查询能让Innodb表损坏，如果一条查询导致Innod表损坏，一定是遇到BUG 而不是查询问题。

损坏的索引会导致的问题：

 1 查询返回错误结果或者莫须有的主键冲突问题。

如何修复：

 1 CHECK TABLE 检查是否发生表损坏。（有些存储引擎不支持该命令，有些通过其他的选项控制完全检查表的方式）。CHECK 通常能找到大多数的表和索引的错误。

  2 REPAIR TABLE 来修复损坏的表，如果存储引擎不支持和可以通过ALTER 操作来重建表。 

     比如 针对Innodb表 AlTER TABLE innodb_1 ENGINE=INNODB;

 3 使用离线工具 比如myisamchk 。

 4 将数据导出一份然后重新导入。

 5 设置 innodb_force_recovery 参数进入Innodb 强制恢复模式来修复数据。可以参考mysql 手册。

 6 使用开源的InnoDB 数据库恢复工具 InnoDB Data Recovery Toolkit 直接从InnoDB 数据文件恢复数据。

如果损坏的系统区域或者而是表的行数据而不是索引，那么就要从备份恢复表，或者尝试从损坏的数据文件中尽可能的恢复数据。

5.5.2  更新索引统计信息

Mysql 查询优化器通过两个API 了解存储引擎的索引值分布信息，用来决定如何使用索引。

records_in_range()  通过向存储引擎传入两个边界值获取这个范围大概有多少条记录。MyISAM 返回精准值 InnoDB返回估算值。

info()  返回各种类型的数据，包括索引的基数，（每个键值有多少条记录）

优化器根据存储引擎返回的估算数据进行sql优化。如果表没有统计信息或者统计信息不准确可能就是做出错误的决定。

各存储引擎的统计信息的支持：

1 Memory 引擎不存储索引统计信息。

2 MyISAM 索引统计信息储存在磁盘上，ANALYZE TABLE  需要一次全索引扫描来计算索引基数，整个过程会锁表。

3 InnoDB也不在磁盘存储索引统计信息，通过随机索引访问进行评估将信息存储在表中。

可以使用 SHOW INDEX FROM TABLE 命令查看索引的基数。Cardinality 显示存储的估算索引列。 在Mysql5.5 之后可以通过 INFORMATION_SCHEMA.STATISTICS表查询这些信息。

InnoDB 通过抽取样本计算统计信息，老版本样本页面是8 ，新版本通过 innodb_stats_samole_page 来设置样本页数。可以设置更大的值。生成更准确的索引信息。

什么时候生成统计信息：

1 InnoDB 表会在首次打开。

2 执行 ANALYZE TABLE 

3 表大小发生非常大的变化。表大小变化超过16分之1 或者新插入20亿行数据 会触发。

4 InnoDB 在打开某些INFORMATION_SCHEMA 表 或者SHOW TABLE STATUS 和 SHOW INDEX 或者客户端开启自动补全功能的时候会触发。

更新统计信息可能会导致什么问题：

1 导致大量的锁。

2 启动时间长。

可以关闭 innodb_stats_on_metadata 参数 来避免这些问题。

Percona 版本 使用的是XtraDB 引擎而不是 原生InnoDB引擎，可以通过InnoDB_staus_auto_update 参数禁止自动采样。这样就需要手动ANALUZE TABLE 更新统计信息。可以用这个参数固化查询计划。

如果想要稳定的执行计划，并在系统重启后更快的生成统计信息，可以使用系统表持久化这些表的统计信息。

Percona 5.1 和Mysql 5.6 版本都加入这个特性 分别是 InnoDB_use_sys_stats_table和 Innodb_analyze_is_persistent

5.5.3 减少索引和数据碎片

B tree 索引可能会碎片化，碎片的索引会以很差或者无序的方式存在磁盘上。

范围扫描或者索引覆盖扫描 速度都会降低。

表的数据存储也可能碎片化，分为三种“

1 行碎片。数据行被存储在多个地方的多个片段中。

2 行间碎片。逻辑上顺序的页或者行在磁盘上不是顺序存储。对全表扫描或者聚簇索引扫描有很大影响。本来可以顺序扫描，现在就不行。

3 剩余空间碎片。数据页有大量的空余空间，导致服务器读取大量不需要的数据。

MySIAM 表 三种碎片都会存在，InnoDB不会出现短小行碎片，InnoDB会移动并重写到一个片段上。

如何整理数据：

1 OPTIMIZE TABLE 

2 导出 导入 整理数据

3 对于MySIAM 可以通过排序算法重建索引方式消除碎片

4 新版本的InnoDB 提供在线添加删除索引的功能 先删除再重建索引来消除索引碎片。

5 ALTER TABLE  tablename  ENGINE=<engine>; Percona Server 开启了 expand_fast_index_creation 参数，可以同时消除表碎片和索引碎片。对于标准MySql版本只会消除表碎片（实际是聚簇索引） ，可以先删除所有索引，然后重建表，最后重新创建索引的方式模拟Percona Server 的这个功能。

可以通过实际测量来确认是否需要消除索引和表的碎片，percona 的XtraBackup 有个--stats参数以非备份的方式运行，而只是打印索引和表的统计信息，包括页中的数据行，和空余空间，来确定数据的碎片化程度，另外也要参考数据是否已经达到稳定程度，如果碎片化整理将表压缩到一起可能反而导致后续的更新操作触发一系列的页分裂和重组，会对性能造成不良的影响。直到数据再次达到稳定。