中国福网客服中心
中国福网客服中心

数据库索引

数据库索引索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

 

 

 

目录

[显示全部]

简述编辑本段回目录

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构,例如employee表的姓(lname)列。如果要按姓查找特定职员,与必须搜索表中的所有行相比,索引会帮助更快地获得该信息。

索引提供指向存储在表的指定列中的数据值的指针,然后根据指定的排序顺序对这些指针排序。数据库使用索引的方式与使用书籍中的索引的方式很相似:它搜索索引以找到特定值,然后顺指针找到包含该值的行。

在数据库关系图中,可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表,或保存该表所在的关系图时,索引将保存在数据库中。

注意:并非所有的数据库都以相同的方式使用索引。有关更多信息,请参见数据库服务器注意事项,或者查阅数据库文档。

作为通用规则,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引占用磁盘空间,并且降低添加、删除和更新行的速度。在多数情况下,索引用于数据检索的速度优势大大超过它的。

索引列编辑本段回目录

可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。

如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

确定索引的有效性:

检查查询的WHERE和JOIN子句。在任一子句中包括的每一列都是索引可以选择的对象。
对新索引进行试验以检查它对运行查询性能的影响。
考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。

类型编辑本段回目录

根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引主键索引聚集索引。有关数据库所支持的索引功能的详细信息,请参见数据库文档。

提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键唯一约束

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。

主键索引

数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

聚集索引

在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

创建索引编辑本段回目录

最普通的情况,是为出现在where子句的字段建一个索引。为方便讲述,先建立一个如下的表。

CREATETABLEmytable(
 idserialprimarykey,
 category_idintnotnulldefault0,
 user_idintnotnulldefault0,
 adddateintnotnulldefault0
);

如果在查询时常用类似以下的语句:

SELECT * FROM mytable WHERE category_id=1;

最直接的应对之道,是为category_id建立一个简单的索引:

CREATE INDEX mytable_categoryid ON mytable (category_id);

OK.如果有不止一个选择条件呢?例如:

SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

第一反应可能是,再给user_id建立一个索引。不好,这不是一个最佳的方法。可以建立多重的索引。

CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

注意到在命名时的习惯了吗?使用"表名_字段1名_字段2名"的方式。很快就会知道为什么这样做了。

现在已经为适当的字段建立了索引,不过,还是有点不放心吧,可能会问,数据库会真正用到这些索引吗?测试一下就OK,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令:

EXPLAIN
 SELECT * FROM mytable
WHERE category_id=1 AND user_id=2;
 This is what Postgres 7.1 returns (exactlyasI expected)
 NOTICE:QUERY PLAN:
 Index Scan using mytable_categoryid_useridon
 mytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN

以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引(一个好开始),而且它使用的是创建的第二个索引。看到上面命名的好处了吧,马上知道它使用适当的索引了。

接着,来个稍微复杂一点的,如果有个ORDERBY字句呢?不管你信不信,大多数的数据库在使用orderby的时候,都将会从索引中受益。
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY add date DESC;

很简单,就象为where字句中的字段建立一个索引一样,也为ORDERBY的字句中的字段建立一个索引:
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);
注意:"mytable_categoryid_userid_adddate"将会被截短为"mytable_categoryid_userid_addda"
CREATE
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY add date DESC;
NOTICE:QUERY PLAN:
Sort(cost=2.03..2.03 rows=1 width=16)
->Index Scanusing mytable_categoryid_userid_addda
onmytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN

看看EXPLAIN的输出,数据库多做了一个没有要求的排序,这下知道性能如何受损了吧,看来对于数据库的自身运作是有点过于乐观了,那么,给数据库多一点提示吧。

为了跳过排序这一步,并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,将给该数据库一个额外的提示--在ORDERBY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。

EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY category_id DESC,user_id DESC,adddate DESC;
NOTICE:QUERY PLAN:
Index Scan Backward using
mytable_categoryid_userid_add daon my table
(cost=0.00..2.02 rows=1 width=16)
EXPLAIN

现在使用料想的索引了,而且它还挺聪明,知道可以从索引后面开始读,从而避免了任何的排序。

以上说得细了一点,不过如果数据库非常巨大,并且每日的页面请求达上百万算,想会获益良多的。不过,如果要做更为复杂的查询呢,例如将多张表结合起来查询,特别是where限制字句中的字段是来自不止一个表格时,应该怎样处理呢?通常都尽量避免这种做法,因为这样数据库要将各个表中的东西都结合起来,然后再排除那些不合适的行,搞不好开销会很大。

如果不能避免,应该查看每张要结合起来的表,并且使用以上的策略来建立索引,然后再用EXPLAIN命令验证一下是否使用了料想中的索引。如果是的话,就OK。不是的话,可能要建立临时的表来将他们结合在一起,并且使用适当的索引。

要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。

以上介绍的只是一些十分基本的东西,其实里面的学问也不少,单凭EXPLAIN是不能判定该方法是否就是最优化的,每个数据库都有自己的一些优化器,虽然可能还不太完善,但是它们都会在查询时对比过哪种方式较快,在某些情况下,建立索引的话也未必会快,例如索引放在一个不连续的存储空间时,这会增加读磁盘的负担,因此,哪个是最优,应该通过实际的使用环境来检验。

在刚开始的时候,如果表不大,没有必要作索引,意见是在需要的时候才作索引,也可用一些命令来优化表,例如MySQL可用"OPTIMIZETABLE"。

参考资料编辑本段回目录

附件列表

文章内容仅供参考,如果您需要解决具体问题建议您提交有问必答。 1

标签: 数据库索引

收藏到: Favorites  

同义词: 暂无同义词

文章信息
客服导航
如果您在使用我们的产品中遇到问题,建议您首先在“常见问题”中查询解决方法;
如果没有找到该问题的解决方法,您可以在“问题搜索”中进行搜索;
如果搜索后没有找到满意答案,您可以“在线提问”,我们会在1个工作日内给您答复。