索引的优点
- 加快查找和排序的效率
- 加快表于表之间的连接
索引的缺点
- 增加了数据库的存储空间
- 插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
一般来说,应该在这些列上创建索引:
- 需要经常查找的列:提高查找速度
- 需要经常范围查找的列:因为索引已经排序,其指定的范围是连续的
- 需要经常排序的列:因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间
- 主键:强制该列的唯一性和组织表中数据的排列结构
- 需要经常连接的列:这些列主要是一些外键,可以加快连接的速度
- 需要经常用在WHERE子句中的列:加快条件的判断速度
一般来说,不应该在这些列上创建索引:
- 不需要经常查找、不需要经常范围查找、不需要经常排序、不需要经常连接、不需要经常用在WHERE子句中的列
- 只有很少数据值的列:如性别,只有两个值。
- 数据量很大的列
多列索引
可以基于数据库表中的单列或多列创建索引。
如:可能有一列存在重复值,那么需要另一列来区分一条记录
再如:经常同时搜索(排序)两列或多列 → 经常在同一查询中为姓和名两列设置判据
最好避免在单个表上创建多个索引
这样插入和修改数据时索引也要随之变动,效率会下降。
索引的分类
根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。
提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一性约束。
在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。
索引又可以分为聚集索引和非聚集索引
聚集索引:按照数据存放的物理位置为顺序的索引。因为数据存放的物理顺序只有一个,所以聚集所以也只能有一个。
非聚集索引:不按照物理位置为顺序的索引。可以有多个。
聚集索引就相当于新华字典中的字母目录,非聚集索引就相当于新华字典中的偏旁部首目录。
聚集索引和非聚集索引的优缺点比较:
- 聚集索引对多行的检索效率高(因为一旦第一个索引值的记录找到,具有连续索引值的记录也一定物理的紧随其后),非聚集索引对单行的检索效率高。
- 比起非聚集索引,聚集索引对数据的修改效率慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把数据插入到数据页的相应的位置,必须在数据页中进行数据的重排,降低了速度
建议用聚集索引的列:
- 包含很少数目的不同值的列
- 查询结果返回一个区间的列
- 查询结果返回某值相当的大量结果集的列
建议用非聚集索引的列:
- 包含大量数目的不同值的列
- 查询结果返回少量的结果集的列
- 用在order by中的列