一丶索引设计的原则

1.要在条件列上创建索引,而不是查询列.

最适合索引的列应该出现在WHERE子句后的列,或连接子句中指定的列.而不是SELECT后选择的列.

2.尽量使用唯一索引

考虑到某列中值的分布.索引的基数越大,索引的效果越好.

例如,存放出生日期的列具有不同值,很容易区分各行.而记录性别的列,只有"男",“女”,索引没什么用,不管搜索那个都会得出一半的值.

3.使用短索引

对字符串进行索引,尽量指定一个前缀长度.

例如,有一个CHAR(200)列,如果在前10个或前20个字符中,多数值是唯一的,那就不要对整列进行索引.

对前10个或前20个字符进行索引能够节省大量索引空间,可能会查询更快.

较小的索引设计的磁盘IO少也能提高速度. 对于较短的键值,索引块中能存更多的键值,因此,MYSQL在内存中能容纳更多的值.

4.利用最左前缀

在创建一个n列的索引时,相当于创建了MYSQL可利用的n个索引.多列索引可以起到多个索引的作用,因为可以列用索引中最左边的列集来匹配行.这样的列集称为最左前缀.

例如:以a,b,c创建一个组合索引后,利用a=?或者a=? and b=?或者a=? and b=? and c=?这三种查询,都可以使用这个索引.这就充分减少了索引的数量,提高了效率.

5.InnoDB引擎存储的表,尽量手工指定主键.

记录默认按照一定的顺序保存:

有明确的主键,则按照主键顺序保存.

没有主键,但有唯一索引,按照唯一索引的顺序保存.

两者都无,自动生成一个内部列,并按照内部列顺序保存.

按照主键或者内部列进行的访问是最快的,所以尽量手动指定主键.

当表中几个列都是唯一的,并且都可以作为主键的时候,选择一个经常使用的作为主键.

PS:InnoDB表的普通索引都会保存主键的键值,所以要尽可能的选择较短的数据类型,有效的减少索引的磁盘占用.

二丶索引设计的误区

1.不是所有的表都需要索引

数据量很小的表,索引扫描和全表扫描,并不会带来性能的大幅度提升.

而大表的查询,更新,删除则要尽可能通过索引.

2.不要过度索引

索引"越多越好"是错误的,因为索引会占用额外的磁盘空间,降低写操作的性能.修改表内容时,索引必须进行更新,可能还要重构,这回耗费大量时间.

尽量创建有需要的索引可以优化查询的速度.

3.谨慎创建低选择度索引

选择性低且数据分布均匀的列,因为过滤的结果集大,创建索引的效果不好;

选择性低且数据分布不均匀的列,比如男女比例为99%:1%,那么此时创建索引对于查询条件为"女"的过滤结果集就比较小.这时创建索引就比较合适.

来自<<深入浅出MYSQL>>