如何创建合适的索引－主要是创建多列索引还是单列索引？

查看:  13665 | 回复:  10

收藏 |      如何创建合适的索引－主要是创建多列索引还是单列索引？  [复制链接]

w_z_y 论坛徽章: 0	电梯直达 1#   发表于 2004-12-8 15:21  |  只看该作者  如我有一个表 UserInfo {    id number(10,0) not null,    username varchar(100) not null,    realname varchar(100) not null,    birthday date not null } 其中id列是PK，username 是唯一键(unique) 以上四列经常被当作条件放到where 字句里面。 我是分别在每一列上创建一个索引，还创建一个索引包含一列呢？ 请多指教！！ 如： 1）select * from userInfo where id = 10; select * from userInfo where username = '1111'; select * from userInfo where name = '1111'; select * from userInfo where birthday= to_date('2003-10-10','yyyy-mm-dd'); 这个肯定创建单列索引了 2）select * from userinfo where  name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd'); 如何创建索引？？ 3）select * from userinfo where  id = 1111 and name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd');
	本版精华 技术人生系列 · 我和数据中心的故事（第六期）-Oracle内存过度消耗风险提醒 Oracle SQL命令通解三步曲 技术人生系列 · 我和数据中心的故事（第一期）——小机上运行Oracle数据库需要注意的 《Oracle Golden Gate图文并茂快速掌握》（附下载） Oracle 运维最佳实践 Oracle中的锁和10704对高级队列锁的跟踪实验 无私奉献 企业级DBA--学习手册(中文版)体系结构,性能优化 数据库笔试面试题库（Oracle、MySQL、MS SQL Server等） 自动化运维趋势越来越猛，DBA将何去何从。 Oracle 12C 集群安装（4节点）详解
	使用道具 举报 回复

ytjutao 论坛徽章: 0	2#   发表于 2004-12-8 15:32  |  只看该作者 每一列创建索引
	使用道具 举报 回复

oracle-plus 论坛徽章: 2	3#   发表于 2004-12-8 15:49  |  只看该作者 下面的內容是使用composite indexes的好處和一些建議，你看一下看對你有沒有用： Choosing Composite Indexes A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:  Improved selectivity  Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with more accurate selectivity.  Reduced I/O  If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.  A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:  CREATE INDEX comp_ind  ON tab1(x, y, z); These combinations of columns are leading portions of the index: x, xy, and xyz. These combinations of columns are not leading portions of the index: yz, y, and z.  Follow these guidelines for choosing keys for composite indexes: Consider creating a composite index on keys that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.  If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.  Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections. Follow these guidelines for ordering keys in composite indexes:  Create the index so the keys used in WHERE clauses make up a leading portion.  If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.  If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.  If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.  有一點也注意的是：如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。 所以，你要根據你的查詢需求來決定是否使用Composite Indexes
	使用道具 举报 回复

grassbell 招聘 :  数据库管理员 论坛徽章: 21	4#   发表于 2004-12-26 17:27  |  只看该作者 最初由 oracle-plus 发布 [B] 有一點也注意的是：如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。 所以，你要根據你的查詢需求來決定是否使用Composite Indexes [/B] 这个是8i的限制，9i不是这样了
	使用道具 举报 回复

wlidflower 论坛徽章: 31	5#   发表于 2004-12-26 18:03  |  只看该作者 创建索引不光要看查询 还要注意对表进行delete insert是否很频繁 综合考虑 索引并不是越多越好 索引多了自然系统对维护索引的代价也多了
	使用道具 举报 回复

xzh2000 招聘 :  数据库管理员 论坛徽章: 66	6#   发表于 2004-12-26 18:05  |  只看该作者 三个索引足了 1 id上的主键 2 username上的unique index 3 name+birthday的组合索引
	使用道具 举报 回复

hanjs 论坛徽章: 24	7#   发表于 2007-11-15 18:15  |  只看该作者 最初由 grassbell 发布 [B] 这个是8i的限制，9i不是这样了 [/B] 这个要看sql怎么写的。 SQL> desc user_info 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER CODE                                               VARCHAR2(10) NAME                                               VARCHAR2(20) FDATE                                              DATE REMARK                                             VARCHAR2(50) 在name,fdate上建立复合索引。 select name,fdate from user_info where fdate=trunc(sysdate) 这样可以利用到索引，如果查询中还有别的列，那么就不行了！ 一般我们认为like '%xx%'是不会走索引的，可是如果只查询了索引列或count等，还是可以利用索引的！
	使用道具 举报 回复

jieyancai  该用户已被删除	8#   发表于 2007-11-15 20:08  |  只看该作者 将这四列作一个复合索引
	使用道具 举报 回复

seamanczg1984 论坛徽章: 8	9#   发表于 2007-11-15 21:52  |  只看该作者 最初由 hanjs 发布 [B] 这个要看sql怎么写的。 SQL> desc user_info 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER CODE                                               VARCHAR2(10) NAME                                               VARCHAR2(20) FDATE                                              DATE REMARK                                             VARCHAR2(50) 在name,fdate上建立复合索引。 select name,fdate from user_info where fdate=trunc(sysdate) 这样可以利用到索引，如果查询中还有别的列，那么就不行了！ 一般我们认为like '%xx%'是不会走索引的，可是如果只查询了索引列或count等，还是可以利用索引的！ [/B] SQL> create table test1(id int,code varchar2(20),zode varchar2(10)); Table created. SQL> begin   2     for i in 1..1000 loog   3  /         for i in 1..1000 loog                          * ERROR at line 2: ORA-06550: line 2, column 19: PLS-00103: Encountered the symbol "LOOG" when expecting one of the following: * & - + / at loop mod remainder rem <an exponent (**)> || multiset SQL> begin   2     for i in 1..1000 loop   3     insert into test1 values(i,'xxx'||i,'zode'||i);   4     end loop;   5  commit;   6  end;   7  / PL/SQL procedure successfully completed. SQL> create index ind_id_code on test1(id,code); Index created. SQL> set autotrace on SQL> set autotrace trace SQL> set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SQL> set autotrace trace exp SQL> select id,code from test1 where code='xxx10'; Execution Plan ---------------------------------------------------------- Plan hash value: 3343096337 -------------------------------------------------------------------------------- ---- | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time    | -------------------------------------------------------------------------------- ---- |   0 | SELECT STATEMENT     |             |     1 |    25 |     3   (0)| 00:00: 01 | |*  1 |  INDEX FAST FULL SCAN| IND_ID_CODE |     1 |    25 |     3   (0)| 00:00: 01 | -------------------------------------------------------------------------------- ---- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("CODE"='xxx10') Note -----    - dynamic sampling used for this statement SQL> 是index fast full scan啊，
	使用道具 举报 回复

seamanczg1984 论坛徽章: 8

10#   发表于 2007-11-15 21:59  |  只看该作者 SQL> select id,code from test1 where id=10; Execution Plan ---------------------------------------------------------- Plan hash value: 2428865550 -------------------------------------------------------------------------------- | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |             |     1 |    25 |     2   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IND_ID_CODE |     1 |    25 |     2   (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("ID"=10) Note -----    - dynamic sampling used for this statement SQL> 如果这么写就是INDEX RANGE SCAN了， 估计oracle看到sql是只查询id,code这两个字段，index中正好已经包括这两个字段，因此不用scan table了。但是上面一个查询条件是code用不到index 的key因此使用了full index scan，而这个的查询条件是id用到了key，因此使用了index range scan，实际上这两个是不一样的 个人认为，建index的目的是为了让oracle使用index range scan。个人愚见，望大佬指正。