索引-数据测试

测试环境：
linux系统下，oracle 11g 数据库。
测试方式：
插入数据：使用PL/SQL工具，开启一个command window(命令窗口)，通过exec 包名.存储过程名，来插入数据，打开 set timing on; 执行存储过程后会有执行时间。
查询数据：在本电脑上使用Navicat Premium工具，直接执行查询命令来看工具给出的执行时间。


先说一下我的测试结论(表中大概190w数据)，当然，前提是以索引列为条件：
1、插入少量数据：含有索引的表插入数据的时候是会变慢，但插入少量数据并不会慢多少比没有索引的表慢多少，可以忽略
2、插入大量数据：含有索引的表插入大量数据的时候会明显比没有索引的表慢
3、查询、更新、删除 少量数据时，含有索引比没有索引快很多
4、查询、更新、删除 大量数据时(大概总数据量的10%~15%以上)，含有索引比没有索引慢很多。
通过索引查询数据的过程：根索引->枝干索引(可能会有多步)->索引叶子节点->数据，是一个多级查询，这就是为什么Oracle的优化器在某种情况下会选择执行全表扫描而不执行索引查找的原因了，因为全表扫描执行起来实际上可能会更快一些。

故归根结底，效率的根源在于I/O的情况，随着查询数据量的增多，通过索引更新的I/O大于了通过遍历的I/O，所以会变慢。

索引改进性能的程度取决于数据的选择性，以及在表的块之间分布数据的方式。如果数据非常具有选择性，则表中的将只有很少的行匹配索引值（例如身份证号码），oracle将能够快速查询匹配的索引值的ROWID的索引，并且可以快速查询少量的相关数据块。如果数据不是非常具有选择性（例如国家名），则索引值可能返回很多rowid，导致从表中查询许多单独的块。
如果数据非常具有选择性，但是相关的行在表中的存储位置并不互相靠近(非聚集索引)，则会进一步减少索引的益处。如果匹配索引值的数据分散在表的多个块中，则必须从表中选择多个单独的块以满足查询。在一些情况中，当数据分散在表的多个块中时(其实就是查询大量数据时，比如大于表中总数据的5%)，最好是不使用索引，而是执行权表扫描，执行权表扫描时，oracle使用多块读取以快速扫描表。基于索引的读取是单块读取，因此在使用索引时的目标是减少解决查询所需的单个块的数量。

5、回到现实情况，我们做软件基本上是用不到这样操作大量数据的，除非是数据库迁移等这样的非软件使用流程，所以添加正确的索引对于数据库操作而言效率会有非常大的提升。

一、创建表，并插入测试数据

-- 部门表
CREATE TABLE DEPARTMENT (
	dept_id	NUMBER(16),
    dept_name VARCHAR2(32),
	primary key(dept_id)
);

-- 员工表
CREATE TABLE STAFF (
	user_id NUMBER(16),
	user_name VARCHAR2(32),
	dept_id	NUMBER(16),
	primary key(user_id),	
	foreign key(dept_id) references DEPARTMENT(dept_id)
	--另一种写法，可以给这个约束命名，而不使用系统默认名字：constraint FK_DEPT_ID foreign key (dept_id) references DEPARTMENT(dept_id)
	--自己命名比较容易管理
);

语法:create index 索引名 on 表名(列名) 
-- 添加外键索引
CREATE INDEX INDEX_STAFF_DEPT_ID ON STAFF(DEPT_ID);

-- 部门表2
CREATE TABLE DEPARTMENT2 (
	dept_id	NUMBER(16),
    dept_name VARCHAR2(32),
	primary key(dept_id)
);

-- 员工表2
CREATE TABLE STAFF2 (
	user_id NUMBER(16),
	user_name VARCHAR2(32),
	dept_id	NUMBER(16),
	primary key(user_id),
	foreign key(dept_id) references DEPARTMENT(dept_id)
);

-- 创建sequence作为STAFF、STAFF2表的主键user_id数据
CREATE SEQUENCE  "WLMEDICAL"."TEST_INDEX_SEQ"  MINVALUE 100 MAXVALUE 9999999999999999 INCREMENT BY 1 START WITH 101 CACHE 20 NOORDER  NOCYCLE 
CREATE SEQUENCE  "WLMEDICAL"."TEST_INDEX_SEQ2"  MINVALUE 100 MAXVALUE 9999999999999999 INCREMENT BY 1 START WITH 101 CACHE 20 NOORDER  NOCYCLE 

创建四张表，DEPARTMENT、STAFF对应，DEPARTMENT2、STAFF2对应。区别就是STAFF表给外键添加了索引。


下面添加测试数据：
给DEPARTMENT、DEPARTMENT2 各添加5w条一摸一样的测试数据通过如下存储过程：

procedure p_department
IS
   
   cIndex NUMBER;
   dName varchar(32);

begin
   
    while cIndex<50000 loop
      cIndex:=TEST_DEPARTMENT_SEQ.nextval;
      dName:='department'||cIndex;
      insert into DEPARTMENT(DEPT_ID, DEPT_ID) values (cIndex, dName);

      insert into DEPARTMENT2(DEPT_ID, DEPT_ID) values (cIndex, dName);
    end loop;

dbms_output.put_line('department-----over');
commit;
end p_department;

二、添加数据测试

1、
当前表是空表，所以我们如果添加一条数据是看不到什么实际效果的，故我们首先添加大批量数据进行测试，
分四次每次添加30w条数据
为了尽量模拟一致，我们每次添加的数据都是一摸一样的包括sequence等，添加数据存储过程(添加时候注释、解注释一下另一条语句)：

procedure p_test
IS

cIndex NUMBER;
uName varchar(20);
depNum NUMBER;
idNum NUMBER;

begin
cIndex:=0;
depNum:=0;

while cIndex<300000 loop
cIndex:=cIndex+1;
depNum:=mod(cIndex,50000);--取余得出 部门id
IF depNum=0 then
	depNum:=50000;
END IF;
idNum:=TEST_INDEX_SEQ.NEXTVAL;
uName:='n'||idNum;
insert into STAFF (user_id, user_name, DEPT_ID) values (idNum, uName, depNum);
--insert into STAFF2 (user_id, user_name, DEPT_ID) values (idNum, uName, depNum);

end loop;
dbms_output.put_line('---over');
 commit;
end p_test;

测试结果：
第一次插入30w数据，
STAFF表(有外键索引)耗时：41.777s
STAFF2表(无外键索引)耗时：33.65s
第二次插入30w数据，
STAFF表(有外键索引)耗时：39.281s
STAFF2表(无外键索引)耗时：33.54s
第三次插入30w数据，
STAFF表(有外键索引)耗时：41.745s
STAFF2表(无外键索引)耗时：33.385s
第四次插入100w数据，此时表中已经存在90w条数据，
STAFF表(有外键索引)耗时：149.417s
STAFF2表(无外键索引)耗时：109.762s


可以看出插入大量数据的时候，性能差别还是比较大的。但是我们应该清楚，正常业务不可能一次插入这么多数据的，除非是数据库迁移等(如果是迁移可以先去掉索引，数据迁移完成再加上)。
所以，我们再测试下插入少量数据。
此时表中已经有190w条数据。

2、在表中已存在百万级数据的时候，给STAFF、STAFF2表各添加一条数据

insert into STAFF (user_id, user_name, DEPT_ID) values (1, '花花', 1);	
耗时：0.01s
insert into STAFF (user_id, user_name, DEPT_ID) values (2, '小明', 2);	
耗时：0.01s
insert into STAFF (user_id, user_name, DEPT_ID) values (3, '小常', 3);	
耗时：0.01s

insert into STAFF2 (user_id, user_name, DEPT_ID) values (1, '花花', 1);	
耗时：0.01s
insert into STAFF2 (user_id, user_name, DEPT_ID) values (2, '小明', 2);	
耗时：0.00s
insert into STAFF2 (user_id, user_name, DEPT_ID) values (3, '小常', 3);	
耗时：0.00s

可以看出，当表中已经存在大量数据的时候，插入一条数据，无论表中是否有索引，效率都还是比较高的。但我们也要考虑到现实使用过程中其实是有并发的。
下面我们再测试下，循环插入200条数据。
此时表中已经有190w条数据。
3、在表中已存在百万级数据的时候，给STAFF、STAFF2表各添加200条数据
第一次插入200数据，
STAFF表(有外键索引)耗时：0.031s
STAFF2表(无外键索引)耗时：0.031s
第二次插入200数据，
STAFF表(有外键索引)耗时：0.031s
STAFF2表(无外键索引)耗时：0.031s
第三次插入200数据，
STAFF表(有外键索引)耗时：0.031s
STAFF2表(无外键索引)耗时：0.031s
可能是我插入数据是while循环插入的，所以得出来的时间有误差，但测试出来的效果就是这样的，我连续执行了六次，只有一次有波动是0.46s 和 0.48s，竟然有索引的表
比没有索引的表插入数据还快，应该是工具误差的原因。
这组数据搞得我有些怀疑自己了～～，然后我又认真想了下，是不是表里面索引太少，我们实际开发中一个表存在多个索引或者组合索引应该是很正常的，稍后再测一下再多加一个索引试试。




但是，无论如何，通过上面插入大量数据的测试可以看出，添加数据时候，表中有索引对插入效率是会有影响的，然后也可以看出插入数据越少这个影响就越低；这是因为默认的情况我们在oracle里面添加的索引
都是非聚集索引，所以添加数据的时候，从物理序看，数据还是添加在尾部的，只有索引是排序插入的。
多说一句，所以，插入时候对聚集索引影响非常大，因为聚集索引和数据的物理序时相同的，没插入一条数据都有可能造成大量数据物理序的改变。

4、再多加两个字段，然后再加一个索引，测试下

给表一添加字段：info_id、info_desc，然后给info_id 添加索引
alter table STAFF add info_id NUMBER(16);
CREATE INDEX INDEX_STAFF_INFO_ID ON STAFF(INFO_ID);
alter table STAFF add info_desc VARCHAR2(2048);

给表一添加字段：info_id、info_desc
alter table STAFF2 add info_id NUMBER(16);
alter table STAFF2 add info_desc VARCHAR2(2048);

把表1、表2的info_id全部赋值为USER_ID以方便测试
update STAFF set info_id=user_id;	--耗时 Affected rows : 1901203, Time: 130.06sec
update STAFF2 set info_id=user_id;	--耗时 Affected rows : 1901203, Time: 103.01sec

添加一条数据，可以给info_desc一个长点的字符串，篇幅有点长，一共执行了三次这里只贴出来一次

insert into STAFF (user_id, user_name, DEPT_ID, INFO_ID, INFO_DESC) values (10, '花花', 1, 10, '第一个方法就是判断索引中唯一键或不同键的数量。可以对表或索引进行分析来确定不同键的数量。可以查询 视图的 列来研究分析的结果。3、二元高度其实数据查询性能，这次查询系统所进行的I/O次数起到很关键作用。索引有一个二元高度(binary height)特性：索引的二元高度对把ROWID 返回给用户进程时所要求的 I/O 数量起到关键作用。二元高度的每个级别都会增加一个额外的读取块，而且由于这些块不能按顺序读取，它们都会要求一个独立的 I/O 操作。在下图(装作有图的样子，手机相册中)中，我们检索一个二元高度为3的索引，这样会返回一行数据给用户，同时有4个数据块碑读取：3个来自索引，1个来自表。随着索引的二元高度的增加，索引数据所要求的 I/O 数量也会随之增加。在对索引进行分析后，可以通过查询 的 列;一般来说，数据库块尺寸越大，索引的二元高度就越低。二元高度的每个额外级别()在DML操作期间会增加额外的性能成本。4、oracle 索引类型B树索引位图索引hash索引索引组织表索引反转键(reverse key)索引基于函数的索引(本地和全局索引)分区索引位图链接索引4.1 B树索引B树索引，在oracle中是一个通用索引，创建索引时他就是默认的索引类型，即我们创建的normal索引就是这种数据结构。B树索引可以是一个单列索引也可以是复合索引，b树索引最多包含32列。B树索引的查询顺序是：1树根----》2枝干----》3叶子----》4表其中2枝干可能有多级，3叶子节点保存的是值和rowid，得到对应的rowid后根据rowid进行4从表中查询数据。技巧：索引列的值都存储在索引中。因此，可以建立一个复合索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。');	
耗时：0.01s
insert into STAFF2 (user_id, user_name, DEPT_ID, INFO_ID, INFO_DESC) values (10, '花花', 1, 10, '第一个方法就是判断索引中唯一键或不同键的数量。可以对表或索引进行分析来确定不同键的数量。可以查询 视图的 列来研究分析的结果。3、二元高度其实数据查询性能，这次查询系统所进行的I/O次数起到很关键作用。索引有一个二元高度(binary height)特性：索引的二元高度对把ROWID 返回给用户进程时所要求的 I/O 数量起到关键作用。二元高度的每个级别都会增加一个额外的读取块，而且由于这些块不能按顺序读取，它们都会要求一个独立的 I/O 操作。在下图(装作有图的样子，手机相册中)中，我们检索一个二元高度为3的索引，这样会返回一行数据给用户，同时有4个数据块碑读取：3个来自索引，1个来自表。随着索引的二元高度的增加，索引数据所要求的 I/O 数量也会随之增加。在对索引进行分析后，可以通过查询 的 列;一般来说，数据库块尺寸越大，索引的二元高度就越低。二元高度的每个额外级别()在DML操作期间会增加额外的性能成本。4、oracle 索引类型B树索引位图索引hash索引索引组织表索引反转键(reverse key)索引基于函数的索引(本地和全局索引)分区索引位图链接索引4.1 B树索引B树索引，在oracle中是一个通用索引，创建索引时他就是默认的索引类型，即我们创建的normal索引就是这种数据结构。B树索引可以是一个单列索引也可以是复合索引，b树索引最多包含32列。B树索引的查询顺序是：1树根----》2枝干----》3叶子----》4表其中2枝干可能有多级，3叶子节点保存的是值和rowid，得到对应的rowid后根据rowid进行4从表中查询数据。技巧：索引列的值都存储在索引中。因此，可以建立一个复合索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。');	
耗时：0.01s

可以看出，插入少量数据的时候，其实有无索引差别并不大。

三、更新数据测试

1、先更新一条数据

update STAFF SET info_desc='呵呵' where info_id=1800001;
耗时 0.01s
update STAFF SET info_desc='呵呵' where info_id=1800002;
耗时 0.01s
update STAFF SET info_desc='呵呵' where info_id=1800003;
耗时 0.01s
update STAFF2 SET info_desc='呵呵' where info_id=1800001;
耗时 0.09s
update STAFF2 SET info_desc='呵呵' where info_id=1800002;
耗时 0.08s
update STAFF2 SET info_desc='呵呵' where info_id=1800003;
耗时 0.09s

可以看出，如果更新一条数据，条件列有索引会比没有索引的快很多。

2、更新大量（过半）数据，表中一共190w条数据。

update STAFF SET info_desc='呵呵' where info_id<1000000;
55.02s
update STAFF SET info_desc='呵呵' where info_id>1000000;
43.90sec
update STAFF2 SET info_desc='呵呵' where info_id<1000000;
42.34s
update STAFF2 SET info_desc='呵呵' where info_id>1000000;
37.63sec
可以得出，当前表中共190w数据，更新过半数据时，条件列有索引会比没有索引的慢很多。

3、更新表中 10% 的数据 

update STAFF SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:07.42
update STAFF SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:05.26
update STAFF SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:07.89

update STAFF2 SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:04.18
update STAFF2 SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:04.77
update STAFF2 SET info_desc='呵呵' where info_id<190000;
Elapsed: 00:00:06.21
可以得出，当前表中共190w数据，更新10%数据时，条件列有索引仍然会比没有索引的慢很多。

综上，当更新 大量数据时有索引会变慢，这个变慢的原因想了很久都没想太明白(主要是不理解数据库底层I/O等操作逻辑)，然后就到处找资料，
找了很久在一本书上看到一句话，原话是：“ 索引通常能提高查询的性能。select语句、update和delete命令的where子句的性能(当访问的行较少时)可以从索引中获益。”
关键是括号中的表述：“当访问的行较少时”，所以我才想到应该是数据I/O导致的。
无论如何，可以明确的是，当更新很少量数据的时候，有索引会比没有索引快很多。其实通过索引查询数据一般会经过四大步：根索引->枝干索引(可能会有多步)->索引叶子节点->数据，
不通过索引的话，就是直接一条一条遍历了，那么有可能是随着数据的增多，通过索引更新的I/O大于了通过遍历的I/O，所以会变慢；因为索引要先找到对应的rowid，然后根据rowid找数据。

4、更新索引列

这个其实刚才添加测试数据的时候已经测试过了,
"
把表1、表2的info_id全部赋值为USER_ID以方便测试
update STAFF set info_id=user_id;	--耗时 Affected rows : 1901203, Time: 130.06sec
update STAFF2 set info_id=user_id;	--耗时 Affected rows : 1901203, Time: 103.01sec
"
更新索引列是会变慢的。

四、查询

1、查询一条数据，效率增加非常多
select * from STAFF where info_id=1800001;
0.01s
select * from STAFF2 where info_id=1800001;
0.09s

2、查询大量数据，where子句中的列含有索引会比不含索引的还要慢
select * from STAFF where info_id<300000;
4.29s  4.08s  4.10s  4.07s
select * from STAFF2 where info_id<300000;
3.98s  3.81s  3.85s  3.82s

我觉得这个应该还是 I/O 的问题。遍历的话只需要遍历即可，索引的方式需要先找到索引-rowid，然后再根据rowid检索数据，这个时候遍历的I/O已经小于索引方式的I/O了，故无索引效率更高。
还是老生常谈的两个点：
  提示1：我们实际开发中一般不会查找这么多数据
  提示2：聚集索引更有利于范围查找和group by分组，因为聚集索引的数据物理序都是挨着的，所以如果是聚集索引的话肯定有索引快于无索引的情况。

五、删除

DELETE from STAFF where info_id=1;
0.01
DELETE from STAFF2 where info_id=1;
0.12

删除和更新规律差不多，删除数据少的时候，有索引会非常快。删除数据量大的时候有索引的就会变慢，因为删除索引也是一部分I/O和引起索引节点裂变排序。
关键是一个平衡点的问题，小于这个平衡点有索引做查询、删除、更新就会很快，大于这个平衡点就会很慢；而这个平衡点应该就是我们查询、删除、更新数据的百分比。