MySQL 索引和数据表该如何维护

目录

• 查找和修复数据表冲突

• 更新索引统计

查找和修复数据表冲突

数据表最糟糕的事情就是发生冲突。使用MyISAM存储引擎时，通常因为崩溃导致冲突。然而，当存在硬件故障、MySQL内部Bug或操作系统Bug时，所有的存储引擎都可能遭受索引冲突。

冲突的索引可能导致查询返回错误的结果，在没有重复值时的重复索引错误增加，甚至可能导致全表扫描或崩溃。如果你遇到过偶发的事件，例如一个你认为不会发生的错误，这个时候运行CHECK TABLE命令去检测数据表是否有冲突（注意有些数据库引擎不支持这个命令，有些则支持多种选项参数去指定如何检测表）。通常，CHECK TABLE命令会捕获大部分的数据表和索引错误。

你可以通过REPAIR TABLE命令修复数据表错误，但是也不是全部存储引擎都支持这个命令。这个时候你需要执行一个“没有操作”的ALTER语句，例如将一个数据表的引擎修改为和当前的引擎一样，例如可以对InnoDB的数据表执行下面的语句：

ALTER TABLE innodb_tb1 ENGINE=INNODB;

相应地，你也可以使用一个存储引擎指定的离线修复工具，例如myisamchk，或者导出数据再重新导入。然而，如果冲突发生在系统区，或者在数据表的数据行区域，而不是索引的话，你可能无法使用这些办法。这种情况下，你可能需要从你的备份中恢复数据或从冲突的文件中恢复数据。

如果你在InnoDB中也遇到了冲突，这会是极其严重的错误，你需要使用正确的方法去分析问题。InnoDB通常不会发生冲突。它的设计对冲突处理很健壮。冲突会是硬件故障（如内存区错误或磁盘错误），DBA的操作错误（如在MySQL环境外操作了数据库文件）或InnoDB自身的Bug (这种概率很低）的表现。通常的一个原因类似视图使用rsync工具创建备份的错误。这时没有可执行的查询——由于这会引起InnoDB的数据冲突，而你认为这会避免。如果你通过一个有问题的查询引起了InnoDB的数据冲突，那这并不是你的错误，这是InnoDB的Bug。

如果真的遇到了数据冲突，最重要的事情是搞清楚引起冲突的原因，在这之前不要简单地修复数据，也许这个冲突会自动消失。你可以通过innodb_force_recovery参数将InnoDB修改为强制恢复模式来修复数据（可以查阅MySQL的操作手册）。你也可以使用开源的Percona InnoDB数据恢复工具（www.percona.com/software/my…）从损坏的数据文件中提取数据。

更新索引统计

MySQL查询优化器在决定如何使用索引前，会调用两个API获取索引值的分布。第一个是records_in_range方法，该方法接收一个范围参数，然后返回该范围的结果数量。对于MyISAM引擎来说返回结果是准确的，但是对于InnoDB来说是估计值。

第二个API是info方法，该方法返回多种类型的数据，包括索引候选者（即每个索引对应的记录数量估计值）。

当存储引擎给查询优化器提供不太准确的数据行数信息，或查询计划过于复杂而无法估计准确的行数时，优化器使用索引统计去估计数据行数。MySQL优化器是基于查询代价做出决策的，最主要的代价准则就是这次查询会查找的数据量。如果索引统计从来没有生成，或者是过期了，优化器可能会做出错误的决定。解决的方案是运行ANALYZE TABLE命令，该命令会重建索引统计。

每个存储引擎实现索引统计的方式不同，因此你运行ANALUZE TABLE命令的频率也会不同，运行该命令的代价也不同，典型的存储引擎对索引统计处理方式如下：

• Memory引擎不存储索引统计。

• MyISAM在磁盘存储索引统计，并且ANALYZE TABLE在计算候选数据行的时候使用全索引扫描。整个表在这个过程中会被锁定。

• InnoDB在MySQL 5.5版本中不在磁盘存储索引统计，而是通过随机的索引采样实现并且将结果存在内存中。
  

可以通过SHOW INDEX FROM命令检查索引的候选者。例如：

这个命令给了很多索引相关的信息，可以查阅MySQL的手册了解具体细节。这里需要特别关注的是Cardinality列。该列展示了存储引擎估计的索引对应了多少个不同的值。在MySQL 5.0及更新的版本中，也可以通过INFORMATION_SCHEMA.STATISTICS表中获取这些信息，这十分方便。例如，你可以根据INFORMATION_SCHEMA查询去找到那些低筛选性的索引。但是注意，对于数据量庞大的服务器，这些中间表可能会导致服务器的负荷大量增加。

InnoDB的统计值得深入研究。统计的结果是通过索引数据页的随机采样计算得到的，这是假设剩余未被采样到的数据也是类似的分布。在旧的InnoDB版本中，这个采样的页数是8，但最新版本的可以通过innodb_stats_sample_pages变量调整。将这个值设置为大于8有助于生成更具代表性的索引统计，尤其是对于大的数据表，但所需要花的代价也会不同。

InnoDB在数据表第一次打开，运行ANALUZE TABLE和数据表存储大小显著改变时（1/16的变化量或20亿行的插入）会计算索引统计。

INFORMATION_SCHEMA表的某些查询，运行SHOW TABLE STATUS，执行SHOW INDEX查询或MySQL命令行客户端启用了自动完成设置，InnoDB也会计算索引统计。这实际会对大数据量，或I/O速度很慢的服务器造成严重的问题。客户端程序或监控工具导致发生重新采样会导致很多锁和加重服务器负担，也会影响终端用户的启动时间。由于SHOW INDEX命令会更新索引统计，而如果你不更改的话你无法观测到索引统计。你可以通过禁用innodb_stats_on_metadata（默认是关闭的）选项去避免这些问题。下面的命令可以查出InnoDB索引统计相关的系统变量。

SHOW GLOBAL VARIABLES WHERE Variable_name like 'innodb_stats％'

如果使用的是包含了替换InnoDB的Percona XtraDB存储引擎的Percona服务器，你可以做进一步的配置。innodb_stats_auto_update选项可以让你禁止自动采样，可以有效冻结自动统计计算，除非你手动运行ANALYZE TABLE。这可以让你摆脱不稳定的查询。这个特性是基于那些大型部署系统客户的要求添加的。

为追求更高的查询计划稳定性和更快的系统启动速度，你可以使用系统级的数据表存储索引统计。这种方式在系统重启或InnoDB第一次启动打开数据表时不需要重新计算索引统计。这个特性在Percona 5.1版本已经得到支持，并且在标准的MySQL 5.6版本已经得到支持。Percona服务器这个特性是通过innodb_use_sys_stats_table选项启用的。在MySQL 5.6版本后，是通过innodb_stats_persistent选项控制的，默认是ON。同时，还有一个变量控制单表的，innodb_stats_auto_recalc变量默认为ON，会在数据表变化量超过10％时重新统计该表的索引统计（手册可以参考：dev.mysql.com/doc/refman/…）。

如果你没有配置自动更新索引统计，你需要定期使用ANALYZE TABLE命令来更新索引统计，除非你知道不更新不会导致糟糕的查询计划。

来源：https://juejin.cn/post/6943582411141840927