一.索引

     开始引入一个问题，如下图，创建一张results表，插入如图10行数据，提交后进行查看得到下图结果。

     当我们通过select语句去查询上表时，查询1次，1000次，10000次，查询到的记录的顺序是否会发生变化？如果会，为什么？如果不会，为什么？
     首先结果是肯定的，不论执行多少次，其查询到的记录的顺序都是不会改变的。下面解释下原因:DM 中表（列存储表和堆表除外）都是使用 B+树索引结构管理的，每一个普通表都有且仅有一个聚集索引，数据都通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。因此实际上在我创建成绩表时，数据库中实际存储的不仅是班级，姓名和成绩三列，还有rowid列，如下图。因我们在插入数据时已经根据插入的先后顺序和rowid列一一对应，且rowid列是唯一加有序的，因此我们不论查询多少次，其查询出的结果均是一致的。

1、聚集索引和二级索引

     聚集索引常叫主键索引，聚集索引的叶子节点对应的就是实际的一行数据，由于数据在物理上是一份，所以聚簇索引只能有一个。聚簇索引的索引键就是数据表的主键。当查询列是主键列时，此时可利用主键索引快读定位该数据。
     二级索引就是常说的普通索引、非聚簇索引，它是与聚簇索引配合使用的，它的B+树的叶子节点存放的是对应数据的主键。查找的时候先获取数据的主键，再根据主键获取实际的数据。当条件列是非主键列时，且此列数据分布均匀，此时可以创建该列的二级索引来提升查询速度，但当查询列并不仅是索引列时，此时会首先利用二级索引进行数据定位，随后利用聚集索引定位其余查询列数据。
     每一个普通表都有且仅有一个聚集索引，当建表语句未指定聚集索引键时，DM7 的默认聚集索引键是 ROWID。若指定索引键，表中数据都会根据指定索引键排序。当然也可以在建表后用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。但新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键，或在表中数据比较少时新建聚集索引，而尽量不要对数据量非常大的表建立聚集索引。
     当表数据体量达到一定程度时，加之我对表中某一列数据的查询效率需求很高时，重新制定聚集索引不是一个最佳的选择。那么可以创建该列上的二级索引已达到其数据有序存储效果，从而实现该列数据的高效率读取。如下图，创建了针对成绩列的二级索引，此刻相当于在数据库中维护一张rowid与成绩列的对照表,此刻着重强调下，成绩列是绝对有序的，rowid列是相对有序的。

create index idx_results on results(results desc);

     此时有人可能要问，为什么要维护rowid和成绩列对照表？
     当我们仅需要成绩列数据时，确实没有什么用处。但当我们需要查询某个成绩是哪个同学的，就会发现其用处。在执行此类SQL语句时，首先会根据条件快速定位到rowid值，然后在原表中根据rowid快读定位到对应行，从而拿到对应的班级列和姓名列数据即可。假如没有二级索引所维护的对照表，那么就需要在原表中拿出每一行的成绩列数据与条件列信息对比，直到比对完所有数据。

2、统计信息与执行计划

     上述所说的根据成绩列来定位班级及人名有两种办法，那么数据库是通过什么机制来选择最优路线来实现数据的读取？实际上是查询优化器通过分析可用的执行方式和查询所涉及的对象统计信息来生成最优的执行计划。
     下面我们简单介绍下：
     查询优化器分析语句运行时的所有因素，选择最优的方式去执行，提高了查询效率。因此，查询优化是数据库执行 SQL 语句的重要过程，决定了数据库的查询性能。对象统计信息描述数据是如何在数据库中存储的。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。达梦数据库的统计信息分三种类型：表统计信息、列统计信息、索引统计信息。通过直方图来表示。
     执行计划是指一条查询语句在数据库中的执行过程或访问路径的描述。执行计划的每行即为一个计划节点，主要包含三部分信息。第一部分为操作符及数据库具体执行了什么操作。第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。第三部分为操作符的补充信息。
     下面以查询results表整表数据执行计划简单举例说明执行计划三元组及节点的执行代价，执行计划如下图：

     例如第三个计划节点：操作符是 CSCN2 即全表扫描，代价估算是 0 ms，扫描的记录行数是 10 行，输出字节数是108个。操作符补充信息是INDEX33558980(RESULTS)。

二.单表优化

     还是一样，我们先抛出一个问题，走索引一定会比全表扫描快吗?
     创建T1，T2，T3三张表，表创建语句及插入语句如下：

create  table  T1(ID int,NAME varchar(200));
insert into T1 select level,'DAMENG' from dual connect by level< 10000000;

create  table  T2(ID int,NAME varchar(200));
insert into T2 select level,'DAMENG' from dual connect by level< 9999999;
update T2 set ID=2 where rowid< 10000001;

create  table  T3(ID int,NAME varchar(200));
insert into T3 values(1,’GUOWANG’)

1、索引扫描与全表扫描效率

1）索引扫描效率优于全表扫描

     1.查询T1表，执行SQL语句

select * from T1 where id=1;

     执行计划如下图：

     此时SQL语句执行时间450ms。
     2.在T1表上创建ID列的二级索引，随后再次执行步骤1）sql语句，执行计划如下图：

     此时SQL语句执行时间14ms。

2）全表扫描优于索引扫描

     1.查询T2表，执行SQL语句，

select count(name)  from t2 where id=1;

     执行计划如下图：

     此时SQL语句执行时间464ms，各操作符具体耗时如下图：

     可见时间基本耗费在全表扫描上。
     2.在T2表上创建ID列的二级索引，随后再次执行步骤1）sql语句，执行计划如下图：

     此时SQL语句执行时间13.4s，各操作符具体耗时如下图：

     可见时间基本耗费在回表操作上。
     由上面两个例子可以看出，走索引并不一定比全表扫描快。这是因为当数据分布严重不均的时候，当需要获取的数据超过50%时，索引扫描会需要更多的进入表的次数去进行定位，其效率反倒不如全表扫描去批量的定位高。因此在单表优化时，列的数据分布情况对于其是否可以成为索引列有着很重要的作用。

三．多表查询

1、连接方式

     哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。
     嵌套循环连接最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。

2、连接原则

1）小表在前

     首先创建一张T3表，表中包含ID，NAME两列，插入1行数据，其中ID值为1，NAME列是“GUOWANG”，首先禁用T1表ID列索引。
     1.查询T1和T3表ID列值一致时两表的NAME值，执行SQL语句

Select * from T1,T3 where T1.id=T3.id;

     执行计划如下图：

     此时SQL语句执行时间为470ms。
     2.查询T1和T3表ID列值一致时两表的NAME值，强行指定连接方式是先t1表，在t3表。执行SQL语句

Select /*+USE_HASH(t1,t3)*/* from T1,T3 where T1.id=T3.id;

     执行计划如下图：

     此时SQL语句执行时间为5.1秒。
     由上步骤可以看出，在多表连接查询时要遵循小表在前的原则。

2）连接列上加索引

     1.删除T1表之前创建的二级索引，查询T1和T3表ID列值一致时两表的NAME值，执行SQL语句

Select count(*) from T1,T3 where T1.id=T3.id;

     执行计划如下图：

     此时SQL语句执行时间为225ms。
     如果此时强制T1和T3表间连接使用嵌套循环连接，执行SQL语句：

select/*+USE_NL(T1,T3)*/ count(*) from T1,T3 where T1.id=T3.id;

     执行计划如下：

     此时SQL语句执行时间为324ms，可见此时嵌套循环相比哈希代价更高，执行时间更长。优化器自动选择了代价低的计划。
     2.在T1表上创建ID列索引,创建语句如下：

CREATE INDEX INDEX_ID ON T1("ID" DESC) ;

     查询T1和T3表ID列值一致时两表的NAME值，执行SQL语句

Select count(*) from T1,T3 where T1.id=T3.id;

     执行计划如下图：

     此时SQL语句执行时间为11ms。
     由上步骤可以看出，在多表连接查询时在连接列上添加索引可在一定程度提升SQL执行效率。