索引设计原则和注意事项

一、索引设计原则

1、选择唯一性索引

• 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
• 如学生的学号是唯一的，而如果使用姓名，可能存在同名现象，从而降低查询速度。

2、为常作为查询条件的字段建立索引(即where后面常用字段)

• 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建⽴立索引，可以提⾼高整个表的查询速度

3、为经常需要排序、分组和联合操作的字段建立索引

• 经常需要order by、group by、distinct和union等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效的避免排序操作

4、小表不建议索引(超过200w数据的表，建立索引)

• 包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

5、尽量使用数据量少的索引

• 如果索引的值很长，那么查询的速度会受到影响
• 例如：对一个char(100)类型的字段进行全文检索需要的时间肯定比对char(10)类型的字段需要的时间更多

6、限制索引的数目

• 每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大
• 修改表时，对索引的重构和更新很麻烦
• 越多的索引，会使更新表变得很浪费时间

7、经常被用来过滤记录的字段

• primary key 字段，系统自动创建主键的索引
• unique key 字段，系统自动创建对应的索引
• foreign key 约束所定义的作为外键的字段
• 在查询中用来连接表的字段

8、索引的创建必须考虑数据的操作方式

• 内容很少变动，经常被查询，为它多创建几个索引无所谓
• 经常性，例行性变动的表而言，则需要谨慎地创建确实必要的索引

9、尽量使用前缀索引

• 如果索引字段的值很长，最好使用值的前缀来索引
• 例如：text和blog类型的字段，进行全文检索会浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度

10、删除不再使用或者很少使用的索引

• 表中数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响

二、合理使用索引注意事项

    索引是针对数据库表中的某些“列”，因此在创建索引时，需要仔细考虑在哪些列上创建索引，在哪些列上不能创建索引。一般来说，在如下列上可以考虑创建索引：

1、“最左前缀匹配原则”，复合索引(联合索引、多列索引)，mysql会从做从左向右匹配直到遇到范围查询(>、<、between、like)就“停止匹配”。比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的复合索引，d是用不到索引的；如果建立(a,b,d,c)顺序的复合索引则都可以用到，a,b,d的顺序可以任意调整。

2、在经常需要搜索的列上，可以加快搜索的速度。

3、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。

4、在经常用在连接的列上，这些列主要是一些外键，可以加快连表的速度。

5、在经常需要根据范围进行搜索的列上，因为索引已经排序，其指定的范围是连续的。

6、在经常需要排序的列上创建索引，因为索引已经排序，可以利用索引排序加快查询。

7、经常使用在where子句中的列上，加快条件的判断速度。

三、不合理使用索引注意事项

1、对于那些在查询中很少使用或者参考的列不应该创建索引。

2、对于那些取值很少的列，如性别等等。

3、对于那些定义为text、image和bit数据类型的列不应该增加索引。