上一篇blog http://www.dbaxiaoyu.com/archives/2354 简单的讲述了index range scan的filter和access的区别，这里再进行一点补充，索引对于sql优化的意义确实是非常大的。 [oracle@redhat_ora11g ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Prod
上一篇blog http://www.dbaxiaoyu.com/archives/2354 简单的讲述了index range scan的filter和access的区别，这里再进行一点补充，索引对于sql优化的意义确实是非常大的。
[oracle@redhat_ora11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 3 06:38:37 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> create table tbak03 as select * from dba_objects;
Table created.
SQL> create index ind_tbak_owner_objid_type on tbak03(owner,object_id,object_type);
Index created.
SQL> select * from tbak03 where owner='SYS' and object_type='INDEX';
1212 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1061 | 214K| 163 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1061 | 214K| 163 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 148 | | 159 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='INDEX')
filter("OBJECT_TYPE"='INDEX')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
84 recursive calls
0 db block gets
536 consistent gets
1 physical reads
0 redo size
130958 bytes sent via SQL*Net to client
1403 bytes received via SQL*Net from client
82 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1212 rows processed
这个sql走的是index range scan，access过滤的条件是(“OWNER”=’SYS’ AND “OBJECT_TYPE”=’INDEX’)，但是filter中又出现了(“OBJECT_TYPE”=’INDEX’)，这个是表示优化器在index range scan扫描时不能绝对保证扫描leaf block满足(“OBJECT_TYPE”=’INDEX’)，所以需要在index range scan完成后再次对object_type进行过滤。
我们再看下面的sql语句的执行计划：
SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id>10000;
161 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 120 | 24840 | 139 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 120 | 24840 | 139 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 125 | | 135 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_ID">10000 AND "OBJECT_TYPE"='INDEX' AND "OBJECT_ID" IS
NOT NULL)
filter("OBJECT_TYPE"='INDEX')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
248 consistent gets
0 physical reads
0 redo size
18929 bytes sent via SQL*Net to client
633 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
161 rows processed
虽然同样是index range scan，access部分出现了三个条件值时，但是filter中还是出现了(“OBJECT_TYPE”=’INDEX’)这个条件，同样这里表示优化器在index range scan扫描时候不能保证扫描的leaf block绝对满足(“OBJECT_TYPE”=’INDEX’)。
上面两个sql都可以总结为一类sql，就是在index range scan时候，在谓词条件中如果复合索引的部分列没有包含或者没有等值谓词过滤条件，而且出现了此列在复合索引相关位置的后面的列的谓词条件，那么后面相关的列肯定需要走filter，index range scan扫描leaf block是没有办法确定扫描的列都是满足access的条件，当然leaf block扫描时就可能会扫描一些不满足access部分涉及的谓词条件的leaf block
而如果索引的所有列都出现在查询中，而且都是等值的谓词条件，那么索引的index range scan不会出现filter
SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id=10000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_ID"=10000 AND "OBJECT_TYPE"='INDEX')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
如果存在不等值的查询，只要保证这个不等值的谓词是复合索引的最后列，index range scan扫描时不会出现filter部分，索引范围扫描时候也能够精确的定位到需要扫描的leaf block
SQL> create index ind_tbak_owner_type_objid on tbak03(owner,object_type,object_id);
Index created.
SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id>10000;
3606 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1247367584
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 579 | 57321 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 579 | 57321 | 29 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_TYPE_OBJID | 579 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='INDEX' AND "OBJECT_ID">10000 AND "OBJECT_ID" IS
NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
667 consistent gets
15 physical reads
0 redo size
406174 bytes sent via SQL*Net to client
3163 bytes received via SQL*Net from client
242 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3606 rows processed
下面我们来看index skip scan的access和filter部分有何区别：
SQL> select * from tbak03 where object_id=10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 846494542
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 6 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10000)
filter("OBJECT_ID"=10000)
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
1615 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
index skip scan时候好像谓词部分access和filter也同时出现了，而且还是针对同一个值
再来看看下面的sql语句又出现index skip scan：
SQL> select * from tbak03 where object_id=10000 and owner>'SYS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 846494542
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 6 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">'SYS' AND "OBJECT_ID"=10000 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=10000)
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed
索引IND_TBAK_OWNER_OBJID_TYPE是(owner,object_id,object_type）的复合索引，但是上面这个查询没有出现我们熟知的index range scan，然后在filter部分再次出现object_id的过滤，而是直接以index skip scan的方式来扫描，然后再filter部分再次出现object_id的等值过滤，这跟我们熟知的index range scan时候当索引的前导列不存在时才出现是对立的。
优化器在index skip scan时是这么完成的，首先通过object_id=10000 and owner>’SYS’条件从root节点找到branch block再到leaf block然后索引范围扫描，当发现object_id不再满足条件时，则重新回到了root节点再到下一个定位的branch block和leaf block做范围扫描，跟之前index range scan所不同的就是，index skip scan会重新回到branch block再到leaf block，类似于一个索引的迭代，而index range scan则大多表现为一旦定位到branch block和leaf block后，就只会在leaf block通过双向指针滑动来扫描满足条件的leaf block，对于一些复合索引如果前导列不同值较少，然后后导列不同值较多，优化器评估object_id=10000 and owner>’SYS’这类谓词条件时会走index skip scan来完成查询。
接下来如果是下列谓词条件的话，优化器多半不会选择index range scan来完成查询。
SQL> create index ind_test02 on xiaoyu03(owner,object_id,data_object_id);
Index created.
SQL> select * from xiaoyu03 where object_id
177 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4138876341
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 43 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XIAOYU03 | 2296 | 197K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
151 consistent gets
0 physical reads
0 redo size
12358 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed
SQL> select /*+index_ffs(xiaoyu03 IND_OBJID_DATAID)*/* from xiaoyu03 where object_id
177 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4138876341
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 43 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XIAOYU03 | 2296 | 197K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
151 consistent gets
0 physical reads
0 redo size
12358 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed
这里的hint为什么无法生效了，索引快速扫描时优化器是没有办法回表的，这里由于需要回表，hint将会被忽略掉。
SQL> select /*+index(xiaoyu03)*/* from xiaoyu03 where object_id
177 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3964829153
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 78 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| XIAOYU03 | 2296 | 197K| 78 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IND_TEST02 | 2296 | | 31 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68 consistent gets
11 physical reads
0 redo size
23874 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed
强制走index，需要注意的index full scan其实也是index range scan，只是index full scan时oracle需要扫描所有的leaf block，但是其实扫描的机制还是一样的。
再来看另外比较常见的or和and来使用索引的两类查询，我们先来看下包含多个谓词条件的and查询：
SQL> create index ind_objid_dataobjid on tbak03(object_id,data_object_id);
Index created.
SQL> select * from tbak03 where object_id between 10000 and 10019 and data_object_id between 9980 and 10092;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3552957621
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_DATAOBJID | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=10000 AND "DATA_OBJECT_ID">=9980 AND "OBJECT_ID"
"DATA_OBJECT_ID"
filter("DATA_OBJECT_ID"=9980)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
对于上述这类包含两个不同谓词过滤条件的and条件，需要明确的and情况下oracle很多情况都会只用一个索引来完成查询，然后回表进行第二次过滤，但是如果是需要两个索引回表才能完成查询，优化器就只能选择全表扫描或者转换bitmap后做bitmap and回表。
SQL> drop index ind_objid_dataobjid;
Index dropped.
SQL> create index ind_objid on tbak03(object_id);
Index created.
SQL> create index ind_dataobjid on tbak03(data_object_id);
Index created.
SQL> select * from tbak03 where object_id between 8809 and 10003 and data_object_id between 19980 and 30002;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2924755239
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1386 | 13 (16)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TBAK03 | 14 | 1386 | 13 (16)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | IND_DATAOBJID | 1182 | | 4 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 8 | SORT ORDER BY | | | | | |
|* 9 | INDEX RANGE SCAN | IND_OBJID | 1182 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DATA_OBJECT_ID">=19980 AND "DATA_OBJECT_ID"
9 - access("OBJECT_ID">=8809 AND "OBJECT_ID"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
这里oracle将其中的索引b tree的rowid先转化为bitmap，然后将两个bitmap进行bitmap and最后再转化为rowed回表，一般而言出现这类执行计划都是建议创建更优秀的复合索引来较少sql语句消耗的资源。
再来看下包含两个谓词条件的or查询：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5svu4zamsud9q, child number 0
-------------------------------------
select /*+gather_plan_statistics*/object_name from tbak03 where
object_id between 10000 and 10019 or data_object_id between 8080 and
10092
Plan hash value: 2095522732
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 217 |00:00:00.01 | 76 | 1 |
| 1 | CONCATENATION | | 1 | | 217 |00:00:00.01 | 76 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 21 | 20 |00:00:00.01 | 10 | 0 |
|* 3 | INDEX RANGE SCAN | IND_OBJID | 1 | 21 | 20 |00:00:00.01 | 4 | 0 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 181 | 197 |00:00:00.01 | 66 | 1 |
|* 5 | INDEX RANGE SCAN | IND_DATAOBJID | 1 | 201 | 197 |00:00:00.01 | 16 | 1 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">=10000 AND "OBJECT_ID"
4 - filter((LNNVL("OBJECT_ID"=10000)))
5 - access("DATA_OBJECT_ID">=8080 AND "DATA_OBJECT_ID"
26 rows selected.
这个concatenation是类似oracle的union all，就是分别通过rowid回表，但是需要注意的是其中 谓词信息4 – filter(LNNVL(“DATA_OBJECT_ID”=9980))去掉了重复满足多个条件的数据，这样可以保证通过这种方式取回的数据是准确的。
可能有朋友会问到了，如果对于or创建一个复合索引是否可以避免上述这类concatenation方式了，其实or的情况下是不能单单利用一次index range scan来完成查询的，即使这个索引是个包含两个过滤条件的复合索引，优化器如果走index range scan，也需要走两次index range scan回表然后concatenation的方式，或者直接走一次index full scan然后回表。
SQL> create index ind_objid_dataobjid on tbak03(object_id,data_object_id);
Index created.
SQL> select /*+index(tbak03 ind_objid_dataobjid)*/object_name from tbak03 where object_id between 10000 and 10019 or data_object_id between 8080 and 10092;
217 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 392751159
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 222 | 6660 | 214 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 222 | 6660 | 214 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IND_OBJID_DATAOBJID | 222 | | 210 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DATA_OBJECT_ID"=8080 OR "OBJECT_ID"
"OBJECT_ID">=10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
254 consistent gets
208 physical reads
0 redo size
7594 bytes sent via SQL*Net to client
677 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
217 rows processed
再来另外一种更简单的or查询，需要注意的in (‘A’,’B’…)这种查询，oracle的查询转换也会改写为or的形式，由于都是同一个列的谓词条件，优化器大多数会走比concatenation更加高效的inlist iterator，inlist iterator的方式类似于在反复的从root节点到branch再到leaf block来定位满足条件的键值，然后回表，而concatenation的方式则是通过多次index range scan回表的方式合并结果集。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1guyuwmd6wsj6, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from tbak03 where object_id=90 or
object_id=29292
Plan hash value: 3397823708
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 7 | 2 |
| 1 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 7 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 2 | 2 | 2 |00:00:00.01 | 7 | 2 |
|* 3 | INDEX RANGE SCAN | IND_OBJID | 2 | 2 | 2 |00:00:00.01 | 5 | 2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_ID"=90 OR "OBJECT_ID"=29292))
21 rows selected.
关于索引的filter和access部分这里xiaoyu再举出上述的例子以供大家参考，下篇将对bitmap索引进行探讨。
原文地址：复合索引的filter和access(二）, 感谢原作者分享。
MySQL 성능을 모니터링하는 데 사용할 수있는 몇 가지 도구는 무엇입니까?Apr 23, 2025 am 12:21 AMMySQL 성능을 효과적으로 모니터링하는 방법은 무엇입니까? Mysqladmin, Showglobalstatus, Perconamonitoring and Management (PMM) 및 MySQL Enterprisemonitor와 같은 도구를 사용하십시오. 1. MySQLADMIN을 사용하여 연결 수를보십시오. 2. showglobalstatus를 사용하여 쿼리 번호를보십시오. 3.pmm은 자세한 성능 데이터 및 그래픽 인터페이스를 제공합니다. 4. MySQLENTERPRISOMITOR는 풍부한 모니터링 기능 및 경보 메커니즘을 제공합니다.
MySQL은 SQL Server와 어떻게 다릅니 까?Apr 23, 2025 am 12:20 AMMySQL과 SqlServer의 차이점은 1) MySQL은 오픈 소스이며 웹 및 임베디드 시스템에 적합합니다. 2) SQLServer는 Microsoft의 상용 제품이며 엔터프라이즈 수준 애플리케이션에 적합합니다. 스토리지 엔진의 두 가지, 성능 최적화 및 응용 시나리오에는 상당한 차이가 있습니다. 선택할 때는 프로젝트 규모와 향후 확장 성을 고려해야합니다.
MySQL을 통해 어떤 시나리오에서 SQL Server를 선택할 수 있습니까?Apr 23, 2025 am 12:20 AM고 가용성, 고급 보안 및 우수한 통합이 필요한 엔터프라이즈 수준의 응용 프로그램 시나리오에서는 MySQL 대신 SQLServer를 선택해야합니다. 1) SQLServer는 고 가용성 및 고급 보안과 같은 엔터프라이즈 수준의 기능을 제공합니다. 2) VisualStudio 및 Powerbi와 같은 Microsoft Ecosystems와 밀접하게 통합되어 있습니다. 3) SQLSERVER는 성능 최적화에서 우수한 성능을 발휘하며 메모리 최적화 된 테이블 및 열 스토리지 인덱스를 지원합니다.
MySQL은 문자 세트 및 콜라이트를 어떻게 처리합니까?Apr 23, 2025 am 12:19 AMmysqlmanagesCharactersetsandcollationsUtf-8AsthedEfault, confonfigurationAtdatabase, 테이블 및 columnlevels, andcolumnlevels, andcolumnlevels, andcolumnlevels, 1) setDefaultCharactersetandcollationforadatabase.2) secigurecharactersetandcollation
MySQL의 트리거는 무엇입니까?Apr 23, 2025 am 12:11 AMMySQL 트리거는 특정 데이터 작업이 수행 될 때 일련의 작업을 수행하는 데 사용되는 테이블과 관련된 자동 실행 된 저장 프로 시저입니다. 1) 트리거 정의 및 기능 : 데이터 검증, 로깅 등에 사용됩니다. 2) 작업 원칙 : 전후에 나누어지고 행 수준 트리거링을 지원합니다. 3) 사용의 예 : 급여 변경을 기록하거나 재고를 업데이트하는 데 사용할 수 있습니다. 4) 디버깅 기술 : ShowTriggers 및 ShowCreateTrigger 명령을 사용하십시오. 5) 성능 최적화 : 복잡한 작업을 피하고 인덱스 사용 및 거래 관리.
MySQL에서 사용자 계정을 어떻게 작성하고 관리합니까?Apr 22, 2025 pm 06:05 PMMySQL에서 사용자 계정을 작성하고 관리하는 단계는 다음과 같습니다. 1. 사용자 만들기 : CreateUser'Newuser '@'localhost'Identifiedby'Password '; 2. 권한 할당 : GrantSelect 사용, 삽입, UpdateOnmyDatabase.to'newuser'@'localhost '; 3. 권한 오류 수정 : Revokeallprivilegesonmydatabase.from'Newuser'@'localhost '; 그런 다음 권한을 재 할당합니다. 4. 최적화 권한 : showgra를 사용하십시오
MySQL은 Oracle과 어떻게 다릅니 까?Apr 22, 2025 pm 05:57 PMMySQL은 빠른 개발 및 중소형 응용 프로그램에 적합한 반면 Oracle은 대기업 및 고 가용성 요구에 적합합니다. 1) MySQL은 오픈 소스이며 사용하기 쉬우 며 웹 응용 프로그램 및 중소 기업에 적합합니다. 2) Oracle은 강력하고 대기업 및 정부 기관에 적합합니다. 3) MySQL은 다양한 스토리지 엔진을 지원하며 Oracle은 풍부한 엔터프라이즈 수준의 기능을 제공합니다.
다른 관계형 데이터베이스와 비교하여 MySQL을 사용하는 단점은 무엇입니까?Apr 22, 2025 pm 05:49 PM다른 관계형 데이터베이스와 비교하여 MySQL의 단점에는 다음이 포함됩니다. 1. 성능 문제 : 대규모 데이터를 처리 할 때 병목 현상을 만날 수 있으며 PostgreSQL은 복잡한 쿼리 및 빅 데이터 처리에서 더 잘 수행됩니다. 2. 확장 성 : 수평 스케일링 능력은 Google 스패너 및 Amazon Aurora만큼 좋지 않습니다. 3. 기능 제한 : 고급 기능에서 PostgreSQL 및 Oracle만큼 좋지 않으면 일부 기능에는 더 많은 사용자 정의 코드 및 유지 관리가 필요합니다.
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!
인기 기사
뜨거운 도구
PhpStorm 맥 버전
최신(2018.2.1) 전문 PHP 통합 개발 도구
맨티스BT
Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
MinGW - Windows용 미니멀리스트 GNU
이 프로젝트는 osdn.net/projects/mingw로 마이그레이션되는 중입니다. 계속해서 그곳에서 우리를 팔로우할 수 있습니다. MinGW: GCC(GNU Compiler Collection)의 기본 Windows 포트로, 기본 Windows 애플리케이션을 구축하기 위한 무료 배포 가능 가져오기 라이브러리 및 헤더 파일로 C99 기능을 지원하는 MSVC 런타임에 대한 확장이 포함되어 있습니다. 모든 MinGW 소프트웨어는 64비트 Windows 플랫폼에서 실행될 수 있습니다.
WebStorm Mac 버전
유용한 JavaScript 개발 도구
