Schema的优化和索引 - 索引和表的维护

当你已经创建了一张表，有合适的数据类型，并添加了索引之后，其实你的工作还并没有结束：你还需要维护你的表和索引使它们工作的更好。表的维护有三个主要的目标：发现和解决表的损坏，维护准确的索引统计，并且要降低存储碎片。

 

找到和修复损坏的表

最差的事情莫过于表已经损坏了。对于MyISAM，大部分是由于当机所造成的。然而，所有的存储引擎都会由于硬件问题或者MySQL内部BUG再或者操作系统的原因导致索引的损坏。

 

损坏的索引能导致查询返回不正确的结果，当没有重复值出现却抛出重复键值的错误，或者导致查询死锁和当机。如果你碰到了奇怪的行为-比如一个你意想不到的错误，就CHECK TABLE来查看表是否损坏。CHECK TABLE一般可以检查大部分表和索引的损坏。

 

你可以使用REPAIR TABLE来修复。但是并不是所有的引擎都支持这个命令。这样你可以使用ALTER命令，比如修改和表相同的存储引擎。

 

mysql> ALTER TABLE innodb_tbl ENGINE=INNODB;

 

你也可以使用离线的针对存储引擎的修复工具。比如myisamchk或者删除数据再重新加载。然而，如果换坏是发生在系统中，或者在表中的“行数据”取代了索引，你就无能为力了。这种情况下，你只能从备份中恢复表或者从损坏的文件中恢复数据。以后会详细说到。

 

更新索引的统计

MySQL的查询优化器使用两个API从存储引擎中得知当决定怎样使用索引的时候，索引是怎样分布的。第一个是records_in_range调用。它传入终结点范围并且返回了范围的记录的值。第二个就是info(),它返回了不同类型的数据，包括了索引的基数（对于每个键值有多少数据）。

 

当存储引擎并没有提供给优化器关于查询行数的准确信息，这个优化器就会使用索引的统计信息。这个信息你可以使用ANALYZE TABLE来估计下行数。MySQL的优化器是基于成本的，并且最主要的消耗因素就是这个查询要访问多少数据。如果这个统计信息没有生成，或者如果它们过期了，优化器可能就会有个比较差的决定。方案就是使用ANALYZE TABLE来生成统计数据。

 

每个存储引擎生成索引的统计数据各不相同，索引你使用ANALYZE TABLE的频率也不同，同样的消耗成本也不同：

 

• Memory存储引擎不会存储索引统计信息。
• MyISAM在硬盘上存储统计信息，并且ANALYZE TABLE执行了全索引扫描来计算。这个过程这张表是锁定的。
• InnoDB并不是在硬盘上存储统计信息。但是使用随机索引进入首次打开的表的方法来估算它们。对于InnoDB，ANALYZE TABLE使用的是随机的方式。因此统计结果不精确的，它们不需要手动更新，除非你服务器运行了很长时间。ANALYZE TABLE也不回加锁消耗也相对低些。因此你可以在线的更新统计信息而不会影响正常工作。

你可以使用SHOW INDEX FROM来查看索引信息。

mysql> SHOW INDEX FROM sakila.actor\G

*************************** 1. row ***************************

Table: actor

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: actor_id

Collation: A

Cardinality: 200

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

*************************** 2. row ***************************

Table: actor

Non_unique: 1

Key_name: idx_actor_last_name

Seq_in_index: 1

Column_name: last_name

Collation: A

Cardinality: 200

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

给出了很多索引信息。MySQL文档有详细说明。我们要注意的是Cardinality。这显示了在索引中存储引擎估算了多少个唯一的值。MySQL5.0中你也可以在INFORMATION_SCHEMA.STATISTICS表中获得这些信息，这样更方便了。举个例子，如果你可以写一条查询INFORMATION_SCHEMA的语句，来发现选择性更低的索引。

减少索引和数据碎片

B-TREE索引比较容易有碎片。这样会降低性能的。碎片的索引会糟糕的并且/或者不连续的存放在硬盘上。B-TREE索引需要随机硬盘访问来“跳”到叶子页上，因此随机访问是个规则，并不是例外。然而，如果他们物理存储是连续的并且轻微压缩的，那么叶子页的表现仍会很好。如果并不是这样，我们就说它们是碎片的，并且范围扫描和全索引扫描速度会成倍的降低。尤其是对于全索引覆盖的语句。

表的数据存储也会变为碎片。然而，数据存储碎片要比索引碎片复杂的多。有两种数据碎片：

• 行碎片.当行存储在多个位置的多个片段伤的时候，就产生了这个类型的碎片。即使查询需要索引中的一个单独的行，行碎片也能导致性能的降低。
• 内部行碎片。当逻辑的连续页或者行并不是连续的存储在硬盘上就导致了内部行的碎片。它能影响全表扫描和聚簇索引范围扫描，这些操作一般都可以从连续存储在硬盘上的数据得到一些好处。

MyISAM表都会遇到这两种碎片，而INNODB不会有短行的碎片。

为了去掉碎片数据，你可以使用OPTIMIZE TABLE或者丢掉和重新导入数据。

这两个方法对于大部分引擎来说是比较好用的。对于如MyISAM的引擎，通过排序算法重新创建索引来消除碎片。到目前为止，还没有方法消除InnoDB的碎片。在M又SQL5.0中InnoDB还不同通过排序来创建索引。甚至你重新删除和创建索引，它们还是碎片的。

对于一些不支持OPTIMIZE TABLE的存储引擎。你可以使用ALTER TABLE来重新创建表。仅仅要更改表的存储引擎，当然这个存储引擎就是当前表的。

mysql> ALTER TABLE <table> ENGINE=<engine>;