索引 #sql #存儲 #監控 #關系型資料庫 #索引 #開發索引 #引用索引 #索引分鐘 #索引指定 #選擇索引

SQL> create table tab_big as select * from dba_objects;

SQL> insert into tab_big select * from tab_big;

SQL> /

SQL> select count(*) from tab_big;

COUNT(*)

----------

199092

SQL> commit;

=============================================

索引

SQL> select count(*) from tab_big where OBJECT_ID=30001;

SQL> set autotrace traceonly

SQL> select * from tab_big where OBJECT_ID=30001;

* 1 | TABLE ACCESS FULL (全表掃描)

2823 consistent gets(邏輯讀2823次)

SQL> create index idx001 on tab_big(OBJECT_ID); (建立一個索引)

SQL> select * from tab_big where OBJECT_ID=30001;(再次執行)

|* 2 | INDEX RANGE SCAN (索引範圍掃描)

99 consistent gets (邏輯讀99次)

SQL> drop index idx001;(删除索引)

SQL> create index idx001 on tab_big(OBJECT_ID) tablespace users;

(索引也會存儲到表空間中,指定表空間,索引太多增删改就會慢,操作中會先讀索引再讀表)

===================================================

索引掃描方式

1.索引唯一掃描（index unique scan).這種掃描發生在主鍵或者唯一索引上，根據鍵值可以唯一确定要通路的記錄，這種掃描方式因為傳回的記錄數少，能夠快速定位記錄，掃描效率較高

2.索引範圍掃描（index range scan).這種一般發生在傳回多個值的時候，如where條件中>and <或者非唯一索引中的=時，範圍掃描要求傳回的結果集不能太多，否則不能從索引掃描上擷取益處，因為從索引隻能獲得rowid與索引列的值，，有可能還需要根據rowid回表一條條的去找行的其他資料，除非不需要回表便能從索引上獲得必需的資料。

使用index rang scan的3種情況：

(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。

(b) 在組合索引上，隻使用部分列進行查詢，導緻查詢出多行。

3.索引全掃描（index full scan)這種方式中，ORACLE會按照索引值的順序全部掃描該索引，類似全表掃描，效率不高。CBO根據統計資料得知進行全索引掃描比進行全表掃描更有效時，才能進行全索引掃描，而且此時查詢出的資料都必須可以從索引中直接得到。

4.索引快速掃描（index fast full scan).與索引全掃描不同，不按照索引值的順序來通路，而是直接讀取索引塊來通路索引資料。這種存取方法中，可以使用多塊讀功能。也可以直接使用并行讀方法以便于獲得最大吞吐量與縮短執行時間。

5.索引跳躍式掃描（index skip scan) .複合索引中第一個字段類型少，而第二個字段唯一，當條件中用到第二個字段時，将跳過第一個字段。

實際OLTP系統中隻有索引唯一掃描和索引範圍掃描。

B樹索引根-->支-->葉

索引中row id 跟表中有對應關系

move後表 rowid變化是以索引實效

位圖索引:(用的少,尤其在增删改比較多的時候)

主要針對大量相同值的列而建立(例如：類别，操作員，部門ID,庫房ID等)

把相同的放成一組

好處:這種方式存儲資料,相對于B*Tree索引,占用的空間非常小,建立和使用非常快

壞處:增删改時會鎖一組資料

SQL> create index idx001 on tab_big(OBJECT_ID) nologging;(不産生日志)

------------------------------------

大型索引得運作很久,放到背景運作(生産系統常用)

[oracle@sq123 ~]$ vi test.sh

#!/bin/bash

sqlplus -S /nolog >result.log <<EOF

set heading off feedback off pagesize 0 verify off echo off

conn sys/123456 as sysdba

create index idx002 on tab_big(OBJECT_ID);

exit

EOF

[oracle@sq123 ~]$ chmod 777 test.sh

SQL> drop index idx001;

[oracle@sq123 ~]$ nohup sh test.sh &(背景運作nohup,即使關閉會話也會背景運作)

檢視索引

SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where TABLE_NAME='TAB_BIG';

====================================================

位圖索引

SQL> show parameter bitmap

可以把create_bitmap_area_size(位圖索引用的緩存區)調大建立速度快

SQL> select 100*1024*1024 from dual;

100*1024*1024

-------------

104857600

SQL> alter system set create_bitmap_area_size=104857600 scope=spfile;(修改大小為100M)

SQL> select distinct OBJECT_TYPE from tab_big;

(distinct過濾掉重複的值)

開啟新的會話

SQL> select distinct OBJECT_TYPE from tab_big;(重新運作)

10973 consistent gets

2740 physical reads

SQL> create bitmap index idx009 on tab_big(OBJECT_TYPE) nologging;(建立位圖索引)

129 consistent gets

46 physical reads

(删除位圖索引,再對OBJECT_TYPE建立 B樹索引,會發現邏輯讀多出很多)

---------------------------------------------------------

online

SQL> create index t11 on t1(object_id);

(沒有加online,如果建立索引的過程中插入資料不等待)

SQL> create index t11 on t1(object_id) online;

(如果建立索引的過程中插入資料等待)

-----------------------------------------------------------

B樹的函數索引

SQL> desc tab_big;

SQL> select max(TIMESTAMP) from t1;

SQL> select count(*) from t1 where substr(TIMESTAMP,1,10)='2012-04-24';

(substr 為截取,1,10為從1開始到10個字元)

420

SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';

2814 consistent gets(邏輯讀)

SQL> create index idx101 on tab_big(TIMESTAMP) nologging online parallel 2;

(parallel 并行,安cpu算,伺服器多cpu 看伺服器的空閑資源有多少)

4.重新檢視(還是全表掃描)

(結果沒有變邏輯讀還是很高)

5.建立函數索引

SQL> drop index idx101;

SQL> create index idx101 on t1(substr(TIMESTAMP,1,10)) nologging online parallel 2;

結果值降了

隻有oracle有函數,位圖索引

-------------------------------

複合索引

如果where條件為2個

建立2個單一條件索引,性能沒有建立一個複合索引好

因為單的會搜尋2次,複合一次 20 ICOL$

---------------------------------

重新組織索引

場景:表上增删改太多會有很多碎片,應該重新組織,或move後索引實效需要重新組織.

1.檢視索引

SQL> select t.index_name,t.status from user_indexes t;

IDX101 VALID

2.移動

SQL> alter table tab_big move;

(重新檢視)

IDX101 UNUSABLE(索引失效)

(結果沒有用上索引邏輯讀很多)

4.重新組織索引(rebuild其實就是先删再建立)

SQL> alter index IDX101 rebuild nologging parallel 2;

IDX101 VALID(又生效了 valid有效的意思)

5.SQL> set autotrace traceonly

(發現結果邏輯讀又很少)

--------------------------------------

索引排序

系統預設索引排序為asc

可以設定為倒序 desc

SQL> create index idx001 on tab_big (object_id desc);

**DB2 支援雙向索引 oracle不支援**

SQL> select object_id from order by 1 desc;

(改為倒序效率相對高,一般用在日期列,顯示最新資訊)

-------------------------------------------

索引組織表

索引組織表(IOT)不僅可以存儲資料，還可以存儲為表建立的索引。索引組織表的資料是根據主鍵排序後的順序進行排列的，這樣就提高了通路的速度。但是這是由犧牲插入和更新性能為代價的(每次寫入和更新後都要重新進行重新排序)。

create table indexTable(

ID varchar2 ( 10 ),

NAME varchar2 ( 20 ),

constraint pk_id primary key ( ID )

)

organization index;

建立IOT時，必須要設定主鍵，否則報錯

SQL> insert into indexTable (id,name)select object_id,owner from dba_objects;

--------------------------------------------

反轉索引:

反轉索引在存儲鍵值的時候，先把鍵值反轉，再進行存儲，比如abcd就反轉為dcba，一般反轉索引用來解決熱塊，原理就是利用鍵值反轉，把索引塊打亂，把熱點分散到不同的索引塊。

create index a111 on a123(id) reverse;

---------------------------------------------

壓縮索引:

oracle 索引壓縮（key compression）是oracle 9i 中引入的一項新特性。該特性可以壓縮索引或者索引組織表中的重複鍵值，進而節省存儲空間。非分區的unique 索引和non-unique（至少兩列）索引都能夠被壓縮。bitmap 索引不能夠進行壓縮。

在oracle 索引壓縮中有幾個比較糾結的術語，需要說明一下。索引壓縮是通過将索引中的鍵值拆分成兩部分實作的，也就是grouping piece 也稱作prefix 和 unique piece 也稱作suffix 。grouping piece 是用來壓縮的被unique piece 共享的部分。如果鍵值不能提供unique piece，那麼oracle 将會使用rowid 來唯一辨別。隻有B-tree 索引的葉子節點能夠被壓縮，分支節點不能夠被壓縮。索引壓縮是在單個block 中完成的，不能夠跨blocks進行索引壓縮。grouping piece (prefix) 和 unique piece (suffix) 存儲在同一個索引 block 中。

具體prefix 和 suffix 是怎麼劃分的呢？預設prefix 長度等于索引列的數量減去1。當然我們可以人為控制prefix 的長度，非唯一索引的最大prefix 長度等于索引列的數量。唯一索引的最大prefix 長度等于索引列的數量減去1。比如，假設索引有三個列：

預設的時候：prefix (column1,column2) suffix (column3)

如果有以下幾組鍵值（1,2,3),（1,2,4),(1,2,7),(1,3,5),(1,3,4),(1,4,4) 那麼在prefix中重複的（1,2），(1,3) 将會被壓縮至保留一份。

索引壓縮适合于那些鍵值重複率高的索引，這樣才能夠達到壓縮鍵值，節省存儲空間目的。索引壓縮以後一個索引塊可以存放更多的鍵值，這樣當進行full index scan,full fast index scan 的時候IO性能會更好，但是CPU的負載會增加，至于總體的性能就要看IO性能的提高和CPU負載增加那個是主要方面了。

SQL> create table o1 as select * from dba_objects;

SQL> create table o2 as select * from dba_objects;

SQL> create table o3 as select * from dba_objects;

SQL> create index o1_i on o1(object_id) compress 1; (1為壓縮一列)

SQL> create index o2_i on o2(object_id);

SQL> select index_name,compression,leaf_blocks from user_indexes

2 where index_name in ('O1_I','O2_I');

INDEX_NAME COMPRESS LEAF_BLOCKS

------------------------------ -------- -----------

O1_I ENABLED 152

O2_I DISABLED 110

結果:object_id都是唯一的壓縮以後索引反而占用了更大的空間，還不如不壓縮。

----------------------------------------------

監控索引

SQL> create index idx002 on tab_big(object_name);

SQL> create index idx003 on tab_big(created);

2.啟動監控功能

SQL> select 'alter index sys.'||index_name||' monitoring usage;' from user_indexes where table_name='FF';

(用sql語句生成監控sql語句)

3.開始監控

SQL> alter index sys.IDX001 monitoring usage;

SQL> alter index sys.IDX002 monitoring usage;

SQL> alter index sys.IDX003 monitoring usage;

4.檢視監控資訊表

SQL> select * from v$object_usage;

MON(監控狀态) USE(有沒有被使用過)

yes no

SQL> select count(*) from tab_big where object_id=30001;

6.重新檢視

(對應的監控 USE 狀态為也是)

取消監控

SQL> alter index sys.IDX001 nomonitoring usage;

----------------

删除索引

alter index idx001 unusable;

(unusable索引失效增删改時索引就不更新了)

(用在資料倉庫或大資料更新 ,先讓索引失效,然後重新組織表,效率會高很多)

檢視索引的大小

SQL> select SEGMENT_NAME,BYTES from user_segments t where t.segment_type='INDEX';

======================================

監控索引使用

SQL> create table tt as select * from dba_objects;

SQL> create index itt on tt(object_id);

SQL> select * from tt where object_id=22;

通過library cache資料

SQL> SELECT OBJECT_NAME

FROM V$SQL_PLAN A, V$SQLAREA B

WHERE A.SQL_ID = B.SQL_ID

AND A.OBJECT_TYPE = 'INDEX'

AND OBJECT_OWNER IN ('SYS', 'SCOTT');

---------------------

分區索引(分區表下使用)

删除分區全局就實效了,是以一般用本得分區索引

1.先建立hash分區表(表名為test6)

2.插入些資料

3.建立本地分區表

SQL> create index idx_par on test6(ID) local;

(local 為本地有幾個分區就綁定幾個索引)

4.檢視分區索引

SQL> select * from user_ind_partitions;

create tablespace ts01 logging datafile '/oracle/app/oradata/TEST/ts01.dbf' size 100m;

create tablespace ts02 logging datafile '/oracle/app/oradata/TEST/ts02.dbf' size 100m;

create tablespace ts03 logging datafile '/oracle/app/oradata/TEST/ts03.dbf' size 100m;

create tablespace ts04 logging datafile '/oracle/app/oradata/TEST/ts04.dbf' size 100m;

建立範圍分區表

SQL> create table test123 partition by range(object_id)

(

partition p1 values less than (10000) tablespace ts01,

partition p2 values less than (20000) tablespace ts02,

partition p3 values less than (50000) tablespace ts03,

partition p4 values less than (maxvalue) tablespace ts04)

as select * from dba_objects;

建立全局分區表索引

SQL> create index idx123 on test123(object_id)

global partition by range(object_id)

partition idx_1 values less than(10000) tablespace ts01,

partition idx_2 values less than(25000) tablespace ts02,

partition idx_3 values less than(50000) tablespace ts03,

partition idx_4 values less than(maxvalue) tablespace ts04);

SQL> set autotrace traceonly;

SQL> select * from test123 where object_id=3001;

SQL> drop index idx123;

------------------------------------------------------

建立本地分區表索引

SQL> create index idx123 on test123(object_id) local;

select index_name,PARTITIONED from dba_indexes where table_name='TEST123';

(顯示類型為分區)

SQL> desc user_part_indexes

本文轉自陳繼松 51CTO部落格，原文連結：http://blog.51cto.com/chenjisong/1737389，如需轉載請自行聯系原作者

索引

繼續閱讀

壓縮編碼M-JPEG、MPEG4、H.264

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離