选择索引

　　假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：

　　用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列：

　　当然，显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表-行组合。

　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有“M”和“F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR（100）。较小的值通过几个方面改善了索引的处理速度：

　　● 较短的值可以更快地进行比较，因此索引的查找速度更快了。

　　● 较小的值导致较小的索引，需要更少的磁盘I/O。

　　● 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。

　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR（200）数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为"最左前缀"（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：