網(wǎng)站手機站怎么做的,公司免費招聘網(wǎng)站,網(wǎng)站建設(shè)的針對對象,大學生做簡歷的網(wǎng)站索引是Oracle數(shù)據(jù)庫性能優(yōu)化的核心組件#xff0c;如同書籍的目錄#xff0c;能快速定位數(shù)據(jù)位置#xff0c;減少磁盤I/O開銷。合理設(shè)計和維護索引可顯著提升查詢效率#xff0c;而誤用則可能導致性能下降。 一、核心理論#xff1a;索引的本質(zhì)與分類 1. 索引的核心作用 加…索引是Oracle數(shù)據(jù)庫性能優(yōu)化的核心組件如同書籍的目錄能快速定位數(shù)據(jù)位置減少磁盤I/O開銷。合理設(shè)計和維護索引可顯著提升查詢效率而誤用則可能導致性能下降。一、核心理論索引的本質(zhì)與分類1. 索引的核心作用加速查詢通過索引結(jié)構(gòu)快速定位數(shù)據(jù)避免全表掃描。強制執(zhí)行約束主鍵、唯一鍵約束依賴索引確保數(shù)據(jù)唯一性。優(yōu)化表連接外鍵列上的索引可減少表連接時的鎖定問題提升關(guān)聯(lián)查詢性能。2. 主流索引類型及適用場景Oracle提供多種索引類型需根據(jù)數(shù)據(jù)特征和業(yè)務(wù)場景選擇B樹索引默認索引類型適用于高基數(shù)列值唯一性高列支持等值查詢、范圍查詢是OLTP系統(tǒng)的首選。位圖索引適用于低基數(shù)列如性別、狀態(tài)數(shù)據(jù)倉庫環(huán)境中優(yōu)勢明顯可高效處理多列組合查詢但不支持高并發(fā)DML操作。索引組織表IOT數(shù)據(jù)存儲與索引結(jié)構(gòu)融合適用于主鍵查詢頻繁的場景節(jié)省存儲空間但插入性能略低。專門索引包括不可見索引用于測試驗證、基于函數(shù)的索引適配WHERE子句中的函數(shù)操作、反向鍵索引解決RAC環(huán)境索引塊爭用等。分區(qū)索引分為本地分區(qū)索引與表分區(qū)一一對應(yīng)維護成本低和全局分區(qū)索引跨分區(qū)全局生效適用于特定查詢場景。3. 索引與性能的平衡索引并非越多越好索引會占用磁盤空間增加DML操作INSERT/UPDATE/DELETE的開銷需同步更新索引。低基數(shù)列創(chuàng)建B樹索引、高并發(fā)更新列創(chuàng)建位圖索引都會導致性能反噬。優(yōu)化器會自動判斷是否使用索引當查詢需掃描表中大部分數(shù)據(jù)時可能選擇全表掃描而非索引。二、實操指南索引的創(chuàng)建、維護與調(diào)優(yōu)1. 索引創(chuàng)建精準落地最佳實踐1基礎(chǔ)B樹索引創(chuàng)建適用于主鍵、唯一鍵、外鍵及高基數(shù)查詢列-- 普通B樹索引單列CREATEINDEXcust_idx_lastnameONcust(last_name)TABLESPACEreporting_index;-- 復合B樹索引多列前導列需高頻查詢CREATEINDEXcust_idx_name_salONcust(last_name,salary)COMPRESS2;-- 唯一索引強制執(zhí)行唯一性CREATEUNIQUEINDEXcust_idx_ssnONcust(ssn)NOLOGGING;關(guān)鍵參數(shù)說明TABLESPACE指定索引表空間建議與表數(shù)據(jù)分離便于維護。COMPRESS N壓縮前導列重復值節(jié)省存儲空間復合索引優(yōu)先使用。NOLOGGING創(chuàng)建時不生成重做日志加速大索引創(chuàng)建數(shù)據(jù)倉庫場景適用。2位圖索引創(chuàng)建僅適用于數(shù)據(jù)倉庫、低基數(shù)列、少DML操作場景-- 單位列位圖索引CREATEBITMAPINDEXf_sales_date_idxONf_sales(d_date_id)LOCALNOLOGGING;-- 位圖連接索引星型模式事實表與維度表關(guān)聯(lián)CREATEBITMAPINDEXf_sales_cust_bmjONf_sales(d_customers.cust_name)FROMf_sales,d_customersWHEREf_sales.d_customer_idd_customers.d_customer_idLOCAL;3索引組織表創(chuàng)建適用于主鍵查詢頻繁、列數(shù)較少的表CREATETABLEprod_sku(prod_sku_id NUMBER,sku VARCHAR2(256),CONSTRAINTprod_sku_pkPRIMARYKEY(prod_sku_id,sku))ORGANIZATIONINDEXOVERFLOWTABLESPACEoverflow_s;-- 非鍵列存儲到溢出段優(yōu)化性能2. 索引維護保障長期高效運行1統(tǒng)計信息收集優(yōu)化器依賴統(tǒng)計信息判斷索引使用效率需定期更新-- 收集表及索引統(tǒng)計信息EXECDBMS_STATS.GATHER_TABLE_STATS(ownnameUSER,tabnameCUST,cascadeTRUE,-- 級聯(lián)收集索引統(tǒng)計method_optFOR ALL COLUMNS SIZE AUTO);-- 單獨收集索引統(tǒng)計信息EXECDBMS_STATS.GATHER_INDEX_STATS(ownnameUSER,indexnameCUST_IDX_LASTNAME);2索引碎片處理索引經(jīng)頻繁刪除/更新后會產(chǎn)生碎片需通過重建、合并優(yōu)化-- 重建索引碎片嚴重、索引不可用時ALTERINDEXcust_idx_lastname REBUILD ONLINE;-- ONLINE允許重建期間查詢ALTERINDEXcust_idx_lastname REBUILDPARTITIONp2023TABLESPACEnew_index_ts;-- 分區(qū)索引重建-- 合并索引碎片較輕無需額外空間ALTERINDEXcust_idx_lastnameCOALESCE;-- 收縮索引釋放未使用空間ALTERINDEXcust_idx_lastname SHRINK SPACE COMPACT;注意重建索引需占用與原索引相當?shù)呐R時空間建議在維護窗口執(zhí)行分區(qū)索引優(yōu)先重建受影響分區(qū)而非全索引。3索引狀態(tài)管理-- 標記索引不可用批量加載數(shù)據(jù)前避免維護開銷ALTERINDEXcust_idx_lastname UNUSABLE;-- 恢復不可用索引ALTERINDEXcust_idx_lastname REBUILD;-- 隱藏索引測試索引有效性不影響業(yè)務(wù)ALTERINDEXcust_idx_lastname INVISIBLE;-- 會話級啟用隱藏索引ALTERSESSIONSEToptimizer_use_invisible_indexesTRUE;3. 索引調(diào)優(yōu)解決性能瓶頸1優(yōu)化器索引使用控制-- 強制使用索引優(yōu)化器誤判時SELECT/* INDEX(cust cust_idx_lastname) */*FROMcustWHERElast_nameSTARK;-- 避免使用索引全表掃描更高效時SELECT/* FULL(cust) */*FROMcustWHEREsalary5000;-- 調(diào)整索引成本系數(shù)傾向使用索引ALTERSESSIONSEToptimizer_index_cost_adj50;-- 默認100值越小越傾向索引2SQL調(diào)優(yōu)顧問自動獲取索引建議Oracle內(nèi)置工具可分析高負載SQL并生成索引建議-- 查看自動SQL調(diào)優(yōu)作業(yè)建議默認每日運行SETLONG1000000;SELECTDBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASKFROMDUAL;-- 手動運行SQL調(diào)優(yōu)顧問針對特定SQLDECLAREtask_id VARCHAR2(100);BEGINtask_id :DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_textSELECT * FROM cust WHERE last_name STARK AND salary 5000,user_nameUSER,task_namecust_query_tune);DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_idtask_id);DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(task_idtask_id));END;/3常見索引失效場景及解決函數(shù)操作導致失效WHERE子句中列使用函數(shù)需創(chuàng)建基于函數(shù)的索引-- 原查詢索引失效SELECT*FROMcustWHEREUPPER(last_name)STARK;-- 創(chuàng)建基于函數(shù)的索引CREATEINDEXcust_idx_upper_lastnameONcust(UPPER(last_name));復合索引前導列未使用復合索引(a,b)查詢僅用b列需調(diào)整索引順序或創(chuàng)建單獨索引。統(tǒng)計信息陳舊執(zhí)行DBMS_STATS.GATHER_TABLE_STATS更新統(tǒng)計信息。4. 索引監(jiān)控與清理-- 監(jiān)控索引是否被使用ALTERINDEXcust_idx_lastname MONITORINGUSAGE;-- 查看監(jiān)控結(jié)果V$OBJECT_USAGESELECTindex_name,usedFROMv$object_usageWHEREindex_nameCUST_IDX_LASTNAME;-- 刪除無用索引確認無依賴后DROPINDEXcust_idx_lastname;三、Oracle 索引創(chuàng)建與維護常用 SQL 腳本集索引創(chuàng)建腳本1. B樹索引默認類型-- 1.1 單列B樹索引高基數(shù)列首選CREATEINDEXidx_cust_lastnameONcust(last_name)TABLESPACEidx_ts NOLOGGING;-- 數(shù)據(jù)倉庫場景加速創(chuàng)建不生成重做日志-- 1.2 復合B樹索引前導列需高頻查詢CREATEINDEXidx_cust_name_salONcust(last_name,salary)COMPRESS2;-- 壓縮前導列重復值節(jié)省空間-- 1.3 唯一B樹索引強制執(zhí)行唯一性CREATEUNIQUEINDEXidx_cust_ssnONcust(ssn)TABLESPACEidx_ts;2. 位圖索引低基數(shù)列、數(shù)據(jù)倉庫-- 2.1 單列位圖索引CREATEBITMAPINDEXidx_f_sales_dateONf_sales(d_date_id)LOCALNOLOGGING;-- 分區(qū)表需指定LOCAL-- 2.2 位圖連接索引星型模式事實表與維度表關(guān)聯(lián)CREATEBITMAPINDEXidx_f_sales_cust_bmjONf_sales(d_customers.cust_name)FROMf_sales,d_customersWHEREf_sales.d_customer_idd_customers.d_customer_idLOCAL;3. 特殊功能索引-- 3.1 基于函數(shù)的索引適配WHERE子句函數(shù)操作CREATEINDEXidx_cust_upper_lastnameONcust(UPPER(last_name));-- 3.2 不可見索引測試驗證不影響業(yè)務(wù)CREATEINDEXidx_cust_invisibleONcust(email)INVISIBLE;-- 3.3 反向鍵索引RAC環(huán)境避免索引塊爭用CREATEINDEXidx_cust_id_reverseONcust(cust_id)REVERSE;-- 3.4 分區(qū)索引本地分區(qū)與表分區(qū)一一對應(yīng)CREATEINDEXidx_emp_dept_partONemp(department_id)LOCAL(PARTITIONp2023_q1TABLESPACEidx_ts_q1,PARTITIONp2023_q2TABLESPACEidx_ts_q2);統(tǒng)計信息收集腳本優(yōu)化器依賴統(tǒng)計信息判斷索引有效性需定期更新-- 1. 收集表及關(guān)聯(lián)索引統(tǒng)計信息推薦EXECDBMS_STATS.GATHER_TABLE_STATS(ownnameUSER,tabnameCUST,cascadeTRUE,-- 級聯(lián)收集索引統(tǒng)計method_optFOR ALL COLUMNS SIZE AUTO);-- 2. 單獨收集索引統(tǒng)計信息EXECDBMS_STATS.GATHER_INDEX_STATS(ownnameUSER,indexnameIDX_CUST_LASTNAME);-- 3. 收集基于函數(shù)的索引統(tǒng)計含隱藏虛擬列EXECDBMS_STATS.GATHER_TABLE_STATS(ownnameUSER,tabnameCUST,method_optFOR ALL HIDDEN COLUMNS SIZE AUTO);索引碎片處理腳本-- 1. 重建索引碎片嚴重、索引不可用時-- 1.1 普通索引在線重建不影響查詢ALTERINDEXidx_cust_lastname REBUILD ONLINE;-- 1.2 分區(qū)索引重建僅重建受影響分區(qū)ALTERINDEXidx_emp_dept_part REBUILDPARTITIONp2023_q1TABLESPACEnew_idx_ts;-- 2. 合并索引碎片較輕無需額外空間ALTERINDEXidx_cust_name_salCOALESCE;-- 3. 收縮索引釋放未使用空間ALTERINDEXidx_cust_lastname SHRINK SPACE COMPACT;索引狀態(tài)管理腳本-- 1. 標記索引不可用批量加載數(shù)據(jù)前避免維護開銷ALTERINDEXidx_cust_lastname UNUSABLE;-- 2. 恢復不可用索引ALTERINDEXidx_cust_lastname REBUILD;-- 3. 隱藏/顯示索引測試索引有效性ALTERINDEXidx_cust_invisible VISIBLE;-- 顯示索引ALTERINDEXidx_cust_invisible INVISIBLE;-- 隱藏索引-- 4. 會話級啟用隱藏索引ALTERSESSIONSEToptimizer_use_invisible_indexesTRUE;索引監(jiān)控與查詢腳本-- 1. 監(jiān)控索引是否被使用ALTERINDEXidx_cust_lastname MONITORINGUSAGE;-- 2. 查看索引監(jiān)控結(jié)果V$OBJECT_USAGESELECTindex_name,used,monitoringFROMv$object_usageWHEREindex_nameIDX_CUST_LASTNAME;-- 3. 查詢索引基本信息類型、表空間、狀態(tài)SELECTindex_name,index_type,tablespace_name,statusFROMuser_indexesWHEREtable_nameCUST;-- 4. 查詢分區(qū)索引狀態(tài)SELECTindex_name,partition_name,statusFROMuser_ind_partitionsWHEREindex_nameIDX_EMP_DEPT_PART;-- 5. 查詢索引碎片情況通過INDEX_STATS視圖ANALYZEINDEXidx_cust_lastname VALIDATE STRUCTURE;SELECTlf_rows,del_lf_rows,del_lf_rows/lf_rows*100del_pctFROMindex_stats;索引刪除與清理腳本-- 1. 刪除無用索引確認無依賴后DROPINDEXidx_cust_invisible;-- 2. 刪除約束關(guān)聯(lián)的索引需先刪除約束ALTERTABLEcustDROPCONSTRAINTcust_pk KEEPINDEX;-- 保留索引ALTERTABLEcustDROPCONSTRAINTcust_pk;-- 自動刪除關(guān)聯(lián)索引-- 3. 釋放索引未使用空間ALTERINDEXidx_cust_lastnameDEALLOCATEUNUSED;核心使用原則高基數(shù)列如主鍵、唯一鍵用B樹索引低基數(shù)列如性別、狀態(tài)用位圖索引索引創(chuàng)建時指定獨立表空間便于維護和備份定期監(jiān)控索引使用情況刪除無用索引減少DML開銷數(shù)據(jù)批量加載前禁用索引加載后重建提升效率。