需要创建时大致分为以下几种
1 表的主关键字:自动建立唯一索引
2 直接条件查询的字段 :经常用于WHERE查询条件的字段,这样能够提高整个表的查询速度
3 查询中与其他表关联的字段 :例如与字段建立了外键关系(也是提高查询速度了)
对于主键、外键,可以如下理解
关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键
比如
学生表(学号,姓名,性别,班级)
其中每个学生的学号是唯一的,学号就是一个主键
课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键
成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录,学号和课程号的组合才可以唯一标识一条记录,所以 学号和课程号的属性组是一个主键
成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键
4 查询排序的字段
5 唯一性约束列:如果某列具有唯一性约束,那么为了确保数据的唯一性,可以在这些列上创建唯一索引
6 大表中的关键列 :在大表中 如果查询效率很低,那么在关键列上创建索引能提高效率
不需要创建时大概一下几种
1 小表 对于小表创建索引 可能会带来额外的开销,因为在小数据集中扫描整个表可能比使用索引更快
2 频繁的插入 更新 和删除操作 :索引的维护成本会随着上述操作而增加。如果表经常被修改,过多的索引可能会影响性能
3 数据重复且分布平均的表字段 :假设一个表有数万行记录,但是sex只有man和woman两种值,且每个值概率大约50%,那么对于这种字段建索引没多大意义。
4 很少被查询的列
5 查询结果总行数较小的表 : 如果查询的结果集总行数很少,使用索引可能没多大的性能提升
索引失效的场景
1 OR条件:当查询中使用多个OR条件时,如果这些条件不涉及同一列,索引可能无法有效使用。数据库可能会选择全表扫描而不是使用多个索引。
2 对列进行类型转换:如果在查询中对列进行类型转换,例如将字符列转换为数字或者日期,索引可能会失效。
3使用通配符前缀搜索:在使用通配符前缀(如 LIKE 'prefix%')进行搜索时,大多数索引无法正常使用,因为索引通常按列的完整值进行排序。
4 不等号条件 当查询条件中包含不等号条件时,索引可能会失效。通常情况下,索引只用于等值比较。
5 表连接中的列类型不匹配:如果在连接操作中涉及的两个表类型不匹配,索引可能会失效。例如一个表的列是整数,而另一个列是字符,连接时可能会导致索引失效。