mysql设计索引的原则

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为 256 字节。大多数存储引擎有更高的限制。

在 MySQL 5.1 中，对于 MyISAM 和 InnoDB 表，前缀可以达到 1000 字节长。请注意前缀的限制应以字节为单位进行测量，而 CREATE TABLE 语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建 FULLTEXT 索引。该索引可以用于全文搜索。只有 MyISAM 存储引擎支持FULLTEXT 索引，并且只为 CHAR 、 VARCHAR 和 TEXT 列。索引总是对整个列进行，不支持局部(前缀) 索引。

 

也可以为空间列类型创建索引。只有 MyISAM 存储引擎支持空间类型。默认情况 MEMORY(HEAP) 存储引擎使用 hash 索引，但也支持 B- 树索引。

 

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

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

2. 使用惟一索引。

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

3. 使用短索引。

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

4. 利用最左前缀。

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

5. 不要过度索引。

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

 

注：

 

1.建立索引的时机：若表中的某字段出现在select、过滤、排序条件中，为该字段建立索引是值得的。

2.对于like '%xxx'的模糊查询，普通的索引是无法满足的，需要建立全文索引。

3.对于有多个条件的，比如: "...where a=xxx and b=yyy","...where a=xxx order by b","...where a=xxx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用，如索引尺寸可能比数据本身大，因为组合索引的组合条目多。所以在实际应用中，要量身定做，使用慢查询分析工具分析。

4.开启索引缓存，直接在内存中查找索引，不用再磁盘中。

5.建立索引是有代价的，当update、delete语句执行时，会使得索引更新，将耗掉更多的时间。可以使用mysqlreport报告，了解select、update、delete、insert、replace各语句所占的百分比。