mysql索引设计原则

引用自深入浅出mysql数据库开发

1、搜索的索引列， 不一定是所要选择的列

搜索的索引列， 不一定是所要选择的列。 换 句话说， 最 适合索引的列是出现在WHERE 子 句中的列，或连接子句中指定的列，而不是出现S在ELECT 关键字后的选择列表中的列。

2、使用惟一索引

考虑某列中值的分布。 对于惟一值的列， 索 引的效果最好， 而具有多个
重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分 各行。 而用来记录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处(不管 搜索哪个值，都会得出大约一半的行)

3、使用短索引

如果对串列进行索引， 应该指定一个前缀长度， 只要有可能就应该这做样。 例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的， 那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O较少，较短的值比较起来更快。更为重要的是，对于较短的键值， 索引高速缓存中的块能容纳更多的键值， 因此MySQL 也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。
(当然， 应该利用一些常识。 如仅用列值的一第个字符进行索引是不可能有多大好处的， 因为这个索引中不会有许多不同的值。)

4、利用最左前缀

在创建 一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。 多列索引可起几个索引的作用， 因 为可利用索引中最左边的列集来匹配行。 这 样的列集 称为最左前缀。 (这与索引一个列的前缀不同，索引一个列的前缀是利用该的n前个字 符作为索引值。 )

5、不要过度索引

每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时， 索引必须进行更新， 有时可能需要重构， 因 此， 索引越多， 所花的时 间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。 此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索 引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的 最好索引。 只 保持所需的索引有利于查询优化。 如 果想给已索引的表增加索引， 应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这 个索引了，因为已经有了。

6、考虑在列上进行的比较类型

索 引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只
将某个列用于其他类型的运算时(如 STRCMP( )) ，对其进行索引没有价值。