数据库索引的创建原则
建立索引的原则:
- 定义主键的数据列一定要建立索引。
- 定义有外键的数据列一定要建立索引。
- 对于经常查询的数据列最好建立索引。
- 对于需要在指定范围内的快速或频繁查询的数据列;
- 经常用在WHERE子句中的数据列。
- 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
- 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
- 对于定义为text、image和bit的数据类型的列不要建立索引。
- 对于经常存取的列避免建立索引
- 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
- 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。
组合多个索引
一个单独的索引扫描只能用于这样的条件子句:使用被索引字段和索引操作符类中的操作符, 并且这些条件以AND连接。假设在(a, b)上有一个索引, 那么类似WHERE a = 5 AND b = 6的条件可以使用索引,但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。
一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描,每个扫描使用一个条件, 最后将这些扫描的结果OR 在一起,生成最终结果。另外一个例子是,如果我们在x 和y上有独立的索引,一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句,然后把这几个结果AND 在一起,生成最终结果。
在大多数最简单的应用里,可能有多种索引组合都是有用的,数据库开发人员必须在使用哪个索引之间作出平衡。有时候多字段索引是最好的,有时候创建一个独立索引并依靠索引组合是最好的。比如,假如你的查询有时候只涉及字段x,有时候只涉及字段y,有时候两个字段都涉及, 那么你可能会选择在x和y上创建两个独立的索引, 然后依靠索引组合来处理同时使用两个字段的查询。你也可以在(x, y)上创建一个多字段索引, 它在同时使用两个字段的查询通常比索引组合更高效,但是对那些只包含y的查询几乎没有用,因此它不能是唯一一个索引。一个多字段索引和y上的独立索引可能会更好。因为对那些只涉及x的查询, 可以使用多字段索引,但是它会更大,因此也比只在x上的索引更慢。最后一个选择是创建三个索引, 但是这种方法只有在表的更新远比查询少,并且所有三种查询都很普遍的情况下才是合理的。如果其中一种查询比其它的很多,那么你可能更愿意仅仅创建两种匹配更常见查询的索引。
备注:设定某个列为主键时,是否默认为其创建索引?
答:一般创建主键时,大多数数据库会自动创建一个唯一索引,一般来说外键肯定是会被经常做连接查询的字段,所以数据库管理系统会自动加上索引。mysql / oralce / sql server 等都会自动添加。