建立索引常用的规则

索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

因为太多的索引与不充分、不正确的索引对性能都毫无益处：
1、在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。
2、过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

总的来说，小型表肯定不建索引， 或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。
还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。

•一般原则

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

•复合索引的考虑

1、正确选择复合索引中的主列字段，一般是选择性较好的字段；其前导列一定是使用最频繁的列；
2、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
3、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
4、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

•建立索引的其他考虑事项

1、索引不会包含有NULL值的列
    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
    所以我们在数据库设计时不要让字段的默认值为NULL。

2、使用短索引
    对串列进行索引，如果可能应该指定一个前缀长度。
    例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。
    短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3、where和order by的索引问题
    mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
    因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4、like语句操作
    一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。
    like “%a%” 不会使用索引而like “aaa%”可以使用索引。

5、不要在列上进行运算
    如：select * from users where YEAR(adddate)

6、不使用NOT IN和操作
    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

7、对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。
    有些字段重复值比较多。如性别字段主要就是“男”、“女”;职位字段中也是有限的几个内容。
    此时，在这些字段上添加索引也不会显著的增加查询速度，减少用户响应时间。
    相反，因为需要占用空间，反而会降低数据库的整体性能。

8、对于按范围查询的列，最好建立索引。
    因为索引已经排序，其保存的时候指定的范围是连续的，查询可以利用索引的排序，加快查询时间，减少用户等待时间。

9、排序、统计和索引
    排序和统计的字段如果通过索引去访问，将大大提高排序速度。

10、多个索引
    经常和主字段一块查询，但主字段就已经将记录减少到只有几条的时候，如果再按后面的字段索引查询，将对性能不产生太大的影响。

11、索引查询和全表扫描（参考值）
    有原始排序的表，40%以内可以用索引查询，40%以上应全表扫描；
    未排序的表，7%以内应该用索引查询，7%以上应该全表扫描。

12、索引不生效
    select * from table where tbl_id = ' 123'        避免隐形转换
    select * from table where sum*2 < 100         移到操作符右边
    select * from table where sum <> 100          改为 a>0 or a <0
    select * from table where tbl_id like '%abc'    索引将不起作用
    IS NULL 或 IS NOT NULL                            改为 a>0 或 a>' '等

13、建立索引，要考虑到数据的变化趋势。

14、频繁修改、添加、删除的表，要定期重建索引。