何時需索引? 1、The column is used frequently in the where clause or join condition. 2、The column contains a wide range of values. 3、The column contains a large number of null values. 4、Two or more culumn are frequently used together in a where clause or join condition. 5、The table is large and most queries are expected to retrieve less than 2-4% of the rows. 何時不需索引? 1、The table is small. 2、The columns are not often used as a condition in the query. 3、Most queries are expected to retrieve more than 2-4% of the rows. 4、The table is updated frequently. Note:若where條件包含is not null即使建立index依然會造成Full table scan。
简单的说,如果将表看作一本书,索引的作用就类似于书中的目录。在没有目录的情况下,要在书中查找制定的内容(即查询条件),必须查阅全书;而有了目录之后,只需要通过目录就可以快速地找到包含所需内容的页。类似的,如果在表中插叙指定的纪录,在没有索引的情况下,必须遍历整个表,而有了索引之后,只需要先在索引中找到符合条件的索引列值,就可以通过保存在索引中的ROWID(相当于页码)快速找到表中对应的纪录。因此,为表建立索引,既能够减少查询操作的时间开销,又能产少I/O操作的开销。
目录和索引之所以能提高查询速度,是因为它们是按查询条件存储数据的,数据量少而且排列有序,便于采用数学方法进行快速定位。另外还提供了一个指向内容的指针,即书的页码或纪录的ROWID
有索引不一定快
全表扫描也不一顶慢索引就是一个类似书的目录的作用
他里面储存着rowid,是一种用空间换时间的方法我建议你看看oracle如何访问表中的数据全表扫描
rowid
索引应该是这三个
剩下的自己google之
《数据库系统概论(第三版)》
[align=center]==== 思想重于技巧 ====
[/align]
索引的基本原理,以及数据是如何被访问的
http://topic.csdn.net/u/20080521/15/c5ee330e-596d-4957-8032-5bb9a80a9218.html
1、The column is used frequently in the where clause or join condition.
2、The column contains a wide range of values.
3、The column contains a large number of null values.
4、Two or more culumn are frequently used together in a where clause or join condition.
5、The table is large and most queries are expected to retrieve less than 2-4% of the rows.
何時不需索引?
1、The table is small.
2、The columns are not often used as a condition in the query.
3、Most queries are expected to retrieve more than 2-4% of the rows.
4、The table is updated frequently.
Note:若where條件包含is not null即使建立index依然會造成Full table scan。
索引不采用一次读入BUFFER,而是一次只读一个块,对小表加索引比较的快