oracle（索引）12.19

索引

堆表（hot）

【存储内容按照某种内容排序组成索引】
数据插入时时存储位置时随机的，主要是数据库内部的空闲情况决定，获取数据是按照命中率计算，全表扫表时不见得先插入的数据先查到。

索引组织表<聚集索引>（iot）

【索引提高dql（搜索）速度  降低dml速度】
数据存储是把表按照索引的方式存储的，数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。
索引表的查询效率比堆表高（相当于查询索引的效率），插入数据的速度比堆表慢。
iot表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序，
而 hot 表数据写入的顺序是按写入时间顺序存储的。
每张innodb表只能创建一个聚集索引，聚集索引可以由一列或多列组成。
innodb是聚集索引组织表，他的聚集索引选择规则是这样的：
	首先选择显示定义的主键索引做为聚集索引；
	如果没有，则选择第一个不允许null的唯一索引；
	还是没有的话 ，就采用innodb引擎内置的rowid作为聚集索引；

唯一索引（unique）：

	不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）

主键索引：

	主键索引要求主键的每个值是唯一的（创建主键自动创建主键索引）

聚集索引（clustered）：

	  表中各行的为物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个索引，主键列被默认为聚集索引

非聚集索引（clustered）：

	表中各行的为物理顺序与键值的逻辑（索引）顺序不匹配，

常用数据支持情况：

	Oracle 支持堆表，也支持索引组织表
	postgresql 只支持堆表，不支持索引组织表
	innodb指支持索引组织表

OLTP （高并发）和 OLAP 的区别

【oltp 强调：高并发】
【olap强调：单位时间的存储量  计算】
联机事务处理  OLTP（on—line   transaction  processing） 主要是执行基本日常的事务处理，比如数据库记录的增删改查。比如在银行的一笔交易记录，就是一个典型的事务。
OLTP的特点一般有：
	1，实时性要求高。前期，银行异地汇款，要隔天到账，而现在是纷纷钟到账的节奏，说明现在银行的实时处理能力大大增强。
	2，数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
	3，交易一般时确定的，比如银行存取款的金额肯定时确定的，所以OLTP是对确定性的数据进行存取
	4，高并发，并且要求满足ACID原则。比两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。

联机分析处理 OLAP（on-line  analytical processing） 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果，典型的应用就是复杂的动态的报表系统（这个系统就像游戏里排行榜，）
OLAP的特点一般有：
	1，实时性要求不是很高，比如最常见的应用就是天极更新数据，然后出对应的数据报表。
	2，数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大：
	3,OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要，一般会将用户所有关心的唯独数据，存入对应数据平台。
	总结：
		OLTP  系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
		并发（同时）（降低锁） →  对应生产期数据：快速定位数据（在DQL[ 搜索 ] 少用索引）
		OLAP： 系统则强调数据分析，强调sql执行时常，强调磁盘I/O  ，强调分区等。
		规模（千表） 准对旧数据（归档数据） /预估，总结，决策，分析（多用索引）

索引

1，索引应该建立在where子句频引用的字段上，如果大表上频繁使用某列或某几列，并且检索的行数低于总数的15%，那么应该考虑在这些列上建立索引，
2，如果经常需要基于某列或某几列执行排序操作，那么在这些列上建立索引可以加块数据排序速度。
3，限制表的索引个数，索引主要用于加快查询速度，但会降低DML速度。索引越多DML操作越慢，尤其对insert，delete 操作影响较大。
4，指定索引块空间，Oracle 会将数据添加到所以块，会将数据存储到预留的索引块位置上，该空间大小由pctfre属性设定，是一种牺牲空间提升速度的做法，
5，在大表上建立索引时，可以使用nologging属性，降低建立索引产生的日志 、 降低建立索引时间，，提高并行建立效率。
6，不要在小表上建立索引。
7，为了提高多表联查性能，应该在连接列上建立索引。

（以下是堆表的解释：）

索引是否使用是归数据库i系统管理，也可以用语法强制使用索引。

图解索引

索引分裂：

原先预留的位置不够，再创一个节点。
	索引分裂分为 91：发还在后加  
			      55：中间插入

索引重建：

将全表重读  (优先使用在线重建，然后重组，然后重建)

索引重组

读索引，将叶子均分（优先清空，原先多少叶子还剩多少叶子）

索引语法

1，b树索引创建
	（1），创建索引，例： create index  index_name  on  table_name(column_name...);
	  (2) ， 修改表，例alter  table   table_name  add  index  index_name (column_name,....);
	  (3),  创建表的时候指定索引，例： create  table  table_name(  [....]  , index  index_name(column_name) );  
2，b数索引删除
	drop  index  index_name
3，B树反转
	createn  index   index_name  on   table_name(column_name)  reverse
	alter   index   index_name   rebuild    reverse
4，重建
	alter   index  index_name   rebuild
		重新生成索引将删除该索引并创建一个新索引。 此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。
		这样可以减少获取所请求数据所需的页 读取数，从而提高磁盘性能。
5，重组
	alter  index  index_name   coalsece
	     重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使叶有序可以提高索引扫描的性能，索引在分配给它的现有页内重新组织，二不会分配新叶。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移叶。
	     重新组织进程使用最少的系统资源，而且，重新组织使自动联机执行的，该进程使自动联机执行的，该进程不持有长期阻塞锁，所以不会组织运行查询或更新，
	     索引碎片不太多时，可以重新组织索引
6，强制索引
	select /* + index ( table_name , index_name ) * / column_name [ , column_name2]  from  table_name  where ?

rbo 规则优化（淘汰）
cbo 代价优化

单列多列索引

1，多列索引只有在where条件中含有索引中的首列字段时才有效
2，多列索引中靠前的字段范围搜索会使靠后字段的索引失效。

位图索引

1，索引块的一个索引行中存储键值、起止rowid，以及这些键值的位置编码
2，位置编码中的每一位表示键值对应的数据行的有无.位数=表的总记录数
3，所需的位图个数=索引列的不同键值多少，列的不同值越少，所需的位图就越少。
4，当根据键值查询时，可以根据起始rowid和位图状态，快速定位数据
5,这样与b* 树那样直接保存 rowid 的区别在于 每次都要进行rowid 的换算工作。uMN_name））；

函数索引

where 或者 having 筛选条件中，带有函数的字段不会触发原字段的索引，可对字段单独创建函数索引。


一句话搜索复杂where，尽量不表回读

规则是：热点字段在前，依次往下
索引查询必须先第一字段
where必须有索引的第一字段
访问除了第一个后面的有可能无法触发，变成全表扫描，所以必须有第一字段
字段顺序有严格要求，第一字段必须顺序对，而且有第一字段，区分度高的放在前面
表（字段a，字段b，字段c，字段d）
如果搜索，字段b，c，d中的数据时，选择度不高，就会走全表扫描

cbo与rbo

Oracle数据库中的优化器又叫查询优化器(Query optimizer) 。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。0racle的优化器有两种，基于规则的优化器(RBO) 与基于代价的优化器(CB0)
RBO: Rule- -Based optimization基于规则的优化器
CB0: Cost-Based optimization基于代价的优化器

RBO自ORACLE 6以来被采用，一直沿用至ORACLE 9i。ORACLE 10g开始，ORACLE 已经彻底丢弃了RBO. 
它有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”,
也就是说RBO对数据不“敏感”;它根据0RACLE指定的优先顺序规则，对指定的表进行执行计划的选择。
CBO是一种比RBO更加合理、可靠的优化器，计算各种可能“执行计划” 的“代价”，即cost，从中选用cost最低的执行方案，作为实际运行方案。

----------------------RBO优化器规则--------------------------------------
Oracle 根据可用的访问路径及其等级来选择执行计划通常认为等级越高的访问路径运行sql语句越慢，如果一个语句有多个路径可走，Oracle 总是选择等级较低的访问路径。
1、rowid定位单行(single row by rowid)
2、群集连接定位单行(single row by cluster join)
3、带有唯一键或主键的哈希键定位单行(single row by hash cluster key wi th unique or primary key)
4、唯一键或主键定位单行(single row by unique or pr imary key)
5、群集连接(clustered join)
6、哈希簇键(hash cluster key)
7、索引簇键(indexed cluster key)
8、复合索引扫描( composite index)
9、单列索引扫描(sing1e-column indexes)
10、索引列进行有限制范围的查询(bounded range search on indexed columns)
11、索引列进行无限制范围的查询(unbounded range search on indexed columns )
12、排序合并联立(sort merge join)
13、索引列上使用max或min函数(max or min of indexed co1umn)
14、索引列上使用order by 子句(order by on indexed column)
15、全表扫描（full table scan）

---------------CBO优化器规则--------------
一个sql语句，经过语义分析后进入查询转换器，再发送到评估器，评估器使用数据字典(统计信息)做辅助评估所有查询方案，择优发送给行源生成器。
CBO 优化器三大组件
1、查询转换器
查询转换器的输入是一个经过分析的查询，查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式来产生最优计划。有四种转换技术。
[1]视图合并
[2]谓词推进
[3]非嵌套子查询
[4]物化视图的查询重写
2、评估器
评估器要计算执行计划的总成本
[1]选择度:是一个0到1的数，0表示无选中，1表示全部选中。
[2]基数:表示行源中的行数量。这里的行源可以是表、视图、也可以是联立的子查询结果
[3]成本:就是度量资源消耗的单位。- 个查询的资源消耗从3个方向计算: I/0 成本、cpu成本、网络成本。
3、计划生成器
计划生成器的主要功能是为了给定的查询语句测试不同的执行计划，然后挑一个成本最低的输出。
! ！统计信息与直方图是cBn准确择代的关键 ！！