MySQL之创建高性能的索引(十三)

创建高性能的索引

维护索引和表

即使使用正确的类型创建了表并加上了合适的索引，工作也没有结束:还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的:找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

找到并修复损坏的表

表损坏(corruption)是很糟糕的事情。对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误——可以尝试运行CHECK TABLE来检查是否发生了表损坏(注意有些存储引擎不支持该命令；二有些引擎则支持以不同的选项来控制完全检查表的方式)。CHECK TABLE通常能够找出大多数的表和索引的错误。可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储疫情不支持，也可通过一个不做任何操作(no-op)的ALTER操作来重建表，例如修改表的存储引擎为当前欸度引擎。下面是一个针对InnoDB表的例子：

mysql> ALTER TABLE actor ENGINE=INNODB;

此外，也可以使用一些存储引擎相关的离线工具，例如myisamchk;或者将数据导出一份，然后再冲洗内倒入。不过，如果损坏的是系统区域，或者是表的"行数据"区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。如果InnoDB引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。InnoDB一般不会出现损坏。InnoDB的设计保证了它并不容易被损坏。如果发生损坏，一般要么是数据库的硬件问题例如内存或者磁盘问题(有可能)，抑或是InnoDB本身的缺陷(不太可能)。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的。不存在什么查询能够让InnoDB表损坏，也不用担心暗处有"陷阱"。如果某条查询导致InnoDB数据的损坏，那一定是遇到了bug,而不是查询的问题。如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断地损坏。可以通过设置innodb_force_recovery参数进入InnoDB地强制恢复模式来修复数据。另外，还可以使用开源地InnoDB数据恢复工具箱(InnoDB Data Recovery Toolkit)直接从InnoDB文件恢复出数据

更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。第一个API是recors_in_rage()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如MyISAM；但对于另一些存储引擎则是一个估算值。例如InnoDB.第二个API是info()，该接口返回各种类型的数据，包括索引的基数(每个键值有多少条记录).如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配地行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用地是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。每种存储引擎实现索引统计信息的方式不同，所以需要进行ANALYZE TABLE的频率也因不同的引擎而不同，每次运行的成本也不同:

• 1.Memory引擎根本存储索引统计信息
• 2.MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数。在整个过程需要锁表
• 3.直到MySQL5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中

可以使用SHOW INDEX FROM命令来查看索引的基数(Cardinality)。例如:

mysql> SHOW INDEX FROM 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:
Index_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: 121
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

这个命令输出了很多关于索引的信息，在MySQL手册中对上面每个字段的含义都有详细的解释。这里需要特别提及的是索引列的基数(Cardinality)，其显示了存储引擎估算索引列有多少个不同的取值(离散程度)。在MySQL5.0和更新的版本中，还可以通过INFORMATION_SCHEMA.STATISTICS表很方便地查询到这些信息。例如基于INFORMATION_SCHEMA的表，可以编写一个查询给出当前选择性比较低的索引。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给MySQL带来额外的压力

InnoDB的统计信息值得深入研究。InnoDB引擎通过抽象的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在老的InnoDB版本中，样本页面数是8，新版本的InnoDB可以铜鼓哦参数innodb_stats_sample_pages来设置样本页的数量.设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境

mysql> SHOW VARIABLES LIKE '%innodb_stats_sample_pages%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_stats_sample_pages | 8     |
+---------------------------+-------+
1 row in set (0.01 sec)

InnoDB会在表首次打开，或者执行ANALYZE TABLE，抑或表的大小发生非常大的变化大小变化超过十六分之一或者新插入了20亿行都会触发的时候计算索引的统计信息。InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据，这可能就是个很严重的问题，尤其是当IO比较慢的时候，客户端或者监控程序触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多的额外压力，这会让用户因为启动时间漫长而沮丧。只要SHOW INDEX查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题

mysql> SHOW VARIABLES LIKE '%innodb_stats_on_metadata%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

如果使用Percona版本，使用的就是XtraDB引擎而不是原生的InnoDB引擎，那么可以通过innodb_auto_update参数来禁止通过自动采样的方式更新索引统计信息，这时需要手动执行ANALYZE TABLE命令来更新统计信息。如果某些查询执行计划很不稳定的话，可以用该办法固话查询计划。如果想要更稳定的执行计划，并在系统重启后更快地生成这些统计i信息，那么可以使用系统表来持久化这些索引统计信息。甚至还可以在不同的机器间迁移索引统计信息，这样新环境启动时就无须再收集这些数据。在Percona5.1版本和官方的5.6版本都已经加入这个特性。一旦关闭索引统计信息的自动更新，那么久需要周期性地使用ANALYZE TABLEE来手动更新。否则，索引统计信息就会永远不变。如果数据分布发生大地变化，可能会出现一些很糟糕的执行计划。