Oracle sql 调优：使用虚拟索引在生产环境测试创建索引对数据库-mysql教程-PHP中文網

首頁

資料庫

mysql教程

Oracle sql 调优：使用虚拟索引在生产环境测试创建索引对数据库

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:02 PM

oraclesql使用環境生產索引虛擬調優

虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器

虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员
可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器为某个
SQL 语句创建的执行计划代价很高，SQL tuning advisor 可能会建议在某个列上创建索引，但是在生产环境下，我们是没法随意
来创建索引和测试这些更改的。我们需要确保要创建的索引不会对数据库中运行的其他查询的执行计划产生任何影响。虚拟索引的出现就
是为了解决这个问题的：

下面我们来做一个测试来介绍虚拟索引的用法

1) 创建示例表

SQL> create table test as select * from dba_objects;

2) 对该表执行任意的查询

16:43:55 system@PROD> select * from test where object_name = 'EMP';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT EMP 75315 75315 TABLE 2011-09-18 18:03:42 2013-03-10 17:07:42 2011-09-18:18:03:42 VALID N N N 1

3) 查看上述查询的执行计划

16:44:31 system@PROD> set autotrace traceonly explain
16:44:42 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='EMP')

Note
-----
- dynamic sampling used for this statement (level=2)

4) 在 test 表的 object_name 字段上面创建虚拟索引

16:45:44 system@PROD> create index test_index on test(object_name) nosegment;

Index created.

注意，在创建虚拟索引时需要在 CREATE INDEX 语句中指定 nosegment 子句，执行上述语句后，实际上数据库中
并未创建索引段，也就是并未给 test_index 对象分配存储空间，这点我们可以通过下面步骤来验证。

5) 通过 dba_objects 可以查看到刚刚创建的 test_index 对象

16:46:02 system@PROD> set autotrace off

16:50:16 system@PROD> col object_name for a20;
16:50:26 system@PROD> select object_name,object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
TEST_INDEX INDEX

但是通过 dba_indexes、dba_segments 和 dba_extents 我们查看不到该对象

16:53:06 system@PROD> select index_name,index_type,table_name from dba_indexes where index_name = 'TEST_INDEX';

no rows selected

16:55:50 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name = 'TEST_INDEX';

no rows selected

16:56:46 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_extents where segment_name = 'TEST_INDEX';

no rows selected

通过上述的查询可以看出，数据库中创建了该对象，但未创建相应的 segment ，分配存储空间。

6) 再次查看之前 sql 的执行计划，看看是否使用了刚刚创建的虚拟索引

16:57:11 system@PROD> set autotrace traceonly explain
16:58:47 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='EMP')

Note
-----
- dynamic sampling used for this statement (level=2)

--我们看到创建虚拟索引后，执行计划并未改变

7) 我们需要修改数据库的隐含参数 _USE_NOSEGMENT_INDEXES 来强制session使用虚拟索引

17:01:24 system@PROD> alter session set "_USE_NOSEGMENT_INDEXES"=true;

Session altered.

8) 再次查看执行计划

17:02:06 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 12 | 2484 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_INDEX | 309 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='EMP')

Note
-----
- dynamic sampling used for this statement (level=2)
-----------------------------------------------------------------------------------------

设置 _USE_NOSEGMENT_INDEXES 隐含参数后，优化器将使用在此表上创建的虚拟索引。在其他session中运行该查询时，不会使用
此虚拟索引，因为我们只是修改了 session 级别的隐含参数。

虚拟索引使用的注意事项：

1、可以对虚拟索引执行 analyze 操作

17:07:13 system@PROD> analyze index TEST_INDEX compute statistics;

Index analyzed.

2、无法对虚拟索引执行 rebuild 操作，否则会报 ORA-8114: "User attempted to alter a fake index" 错误

17:07:52 system@PROD> alter index TEST_INDEX rebuild;
alter index TEST_INDEX rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

3、可以像普通索引那样删除虚拟索引

17:19:20 system@PROD> drop index test_index;

Index dropped.

4、在Oracle 9.2 to 11.1 中利用 DBMS_METADATA.get_ddl 来获取虚拟索引的 DDL 脚本时，不会输出虚拟索引的 nosegment 子句
我个人测试的环境是 11.2.0.3.5 可以输出 nosegment 子句

17:13:46 system@PROD> select dbms_metadata.get_ddl('INDEX','TEST_INDEX','SYSTEM') DDL from dual;

DDL
--------------------------------------------------------------------------------

CREATE INDEX "SYSTEM"."TEST_INDEX" ON "SYSTEM"."TEST" ("O
BJECT_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT

http://blog.csdn.net/xiangsir/article/details/8693814

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

說明InnoDB重做日誌和撤消日誌的作用。Apr 15, 2025 am 12:16 AM

InnoDB使用redologs和undologs確保數據一致性和可靠性。 1.redologs記錄數據頁修改，確保崩潰恢復和事務持久性。 2.undologs記錄數據原始值，支持事務回滾和MVCC。

在解釋輸出（類型，鍵，行，額外）中要查找的關鍵指標是什麼？Apr 15, 2025 am 12:15 AM

EXPLAIN命令的關鍵指標包括type、key、rows和Extra。 1）type反映查詢的訪問類型，值越高效率越高，如const優於ALL。 2）key顯示使用的索引，NULL表示無索引。 3）rows預估掃描行數，影響查詢性能。 4）Extra提供額外信息，如Usingfilesort提示需要優化。

在解釋中使用臨時狀態以及如何避免它是什麼？Apr 15, 2025 am 12:14 AM

Usingtemporary在MySQL查詢中表示需要創建臨時表，常見於使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通過優化索引和重寫查詢避免其出現，提升查詢性能。具體來說，Usingtemporary出現在EXPLAIN輸出中時，意味著MySQL需要創建臨時表來處理查詢。這通常發生在以下情況：1)使用DISTINCT或GROUPBY時進行去重或分組；2)ORDERBY包含非索引列時進行排序；3)使用複雜的子查詢或聯接操作。優化方法包括：1)為ORDERBY和GROUPB

描述不同的SQL交易隔離級別（讀取未讀取，讀取，可重複的讀取，可序列化）及其在MySQL/InnoDB中的含義。Apr 15, 2025 am 12:11 AM

MySQL/InnoDB支持四種事務隔離級別：ReadUncommitted、ReadCommitted、RepeatableRead和Serializable。 1.ReadUncommitted允許讀取未提交數據，可能導致臟讀。 2.ReadCommitted避免臟讀，但可能發生不可重複讀。 3.RepeatableRead是默認級別，避免臟讀和不可重複讀，但可能發生幻讀。 4.Serializable避免所有並發問題，但降低並發性。選擇合適的隔離級別需平衡數據一致性和性能需求。

MySQL與其他數據庫：比較選項Apr 15, 2025 am 12:08 AM

MySQL適合Web應用和內容管理系統，因其開源、高性能和易用性而受歡迎。 1)與PostgreSQL相比，MySQL在簡單查詢和高並發讀操作上表現更好。 2)相較Oracle，MySQL因開源和低成本更受中小企業青睞。 3)對比MicrosoftSQLServer，MySQL更適合跨平台應用。 4)與MongoDB不同，MySQL更適用於結構化數據和事務處理。

MySQL索引基數如何影響查詢性能？Apr 14, 2025 am 12:18 AM

MySQL索引基数对查询性能有显著影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。

MySQL：新用戶的資源和教程Apr 14, 2025 am 12:16 AM

MySQL學習路徑包括基礎知識、核心概念、使用示例和優化技巧。 1)了解表、行、列、SQL查詢等基礎概念。 2)學習MySQL的定義、工作原理和優勢。 3)掌握基本CRUD操作和高級用法，如索引和存儲過程。 4)熟悉常見錯誤調試和性能優化建議，如合理使用索引和優化查詢。通過這些步驟，你將全面掌握MySQL的使用和優化。