索引案例与维护索引和表

索引的案例

假设一个在线约会网站的用户信息表中含有国家、地区、城市、性别、眼睛颜色等。若想让该表有更高效的索引组和来提供这些特征的各种组合来搜索用户，应该如何去做呢？

支持多种过滤条件

一般来说我们需要看看哪些列拥有很多不同的取值，哪些列在where子句中出现得最频繁。在有更多不同值得列上创建索引的选择性会更好。country列的选择性通常不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。所以可以在创建不同组合索引的时候将（sex,country）列作为前缀。这里的原因有二：其一，几乎所有的查询都会用到sex列。其二，索引中即使加上这一点也没有坏处，因为我们可以通过在where中添加and sex in (‘m’,’f’) 来让Mysql选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上你这个列的条件，mysql 才能够匹配索引的最左前缀。

我们在设计表的索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。
接下来我们考虑其他常见的where条件组合，如：（sex，country，age），（sex，country，region，age），（sex，country，region，city，age），这样的组合我应该尽量进行重用索引而不是建立大量的组合索引，可以使用前面提到的in()技巧来避免同时需要（sex，country，age）和（sex，country，region，age）的索引。如果没有指定这个字段搜索，就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束。同时我们也要去忽略一些生僻的搜索条件。我们一直都是在将age列放到索引的最后面。因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。而age多半则是范围查询例如查找年龄在18到25岁之间的人。

避免多个范围条件

例如我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：
where eye_color in(‘brown’,’blue’,’hazel’) and hair_color in (‘black’,’red’,’blonde’,’brown’)
and sex in(‘M’,‘F’) and last_online > date_sub(now(),InteRVAL 7 DAY) and age Between 18 and 25
这个查询有两个范围条件，last_online 列 和 age 列，Mysql可以使用last_online 列索引或者age列索引，但无法同时使用它们。

优化排序

如果一个查询匹配的结果有上百万行的话怎样？例如如果where子句只有sex列，如何排序？
对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建（sex，rating）索引用于下面的查询：
mysql>select from profiles where sex=’M’ order by rating limit 10;
这个查询同时使用了order by 和 limit，如果没有索引的话会很慢。即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。可以减少mysql扫描那些需要丢弃觉得行数。

维护索引和表

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

找到并修复损坏的表

表损坏是很糟糕的事情。对于myIsam存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、mysql本身的缺陷或者操作系统的问题导致索引损坏。
当我们遇到一些查询返回错误的结果或者古怪的问题，我们可以尝试运行check table来检查是否发生表损坏。可以使用repair table命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，也可通过一个不做任何操作的alter操作来重建表，例如修改表的存储引擎为当前的引擎。下面是一个针对InnoDB表的例子：
mysql>alter table innodb_tb1 engine=innodb;
此外，也可以使用一些存储引擎相关的离线工具，例如myisamchk;或者将数据导出一份，然后在重新导入。不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。这种情况可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

更新索引统计信息

mysql的查询优化器会通过两个api来了解存储引擎的索引值得分布信息，以决定如何使用索引。
records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如myIsAm；对于另一些存储引擎则是一个估算值，例如InnoDB。
第二个api是info（）,该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片：
行碎片：这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
对于myisam 表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动短小的行并重写到一个片段中。
可以通过执行optimize table 或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于一些存储引擎如myisam，可以通过排序算法重建索引的方式来消除碎片。在新版InnoDB中添加了在线添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。
对于不支持optimize table的存储引擎，可以通过alter table操作来重建表。对于开启了expand_fast_index_creation参数的percona server按照这种方式重建表，则会同时消除表和索引的碎片化。

java高级教程–安全