? 选择组合索引的前导列，必须根据具体的业务（SQL）写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的， 本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更
?选择组合索引的前导列，必须根据具体的业务（SQL）写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的，本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更多复杂的情况需要具体分析。
1.都是等值条件的列，谁做前导列都一样
|
DROP TABLE t; CREATE TABLE t? AS SELECT * FROM dba_objects; CREATE INDEX idx1_t ON t(owner,object_id); CREATE INDEX idx2_t ON t(object_id,owner); BEGIN ? dbms_stats.gather_table_stats(ownname => USER,tabname => ‘T’,estimate_percent => 100,cascade => TRUE); END; ?/ dingjun123@ORADB> ?SELECT COUNT(DISTINCT owner),COUNT(DISTINCT object_id),COUNT(*) FROM t; COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) ? COUNT(*) ——————– ———————— ———- ? ? ? ? ? ? ? ? ? 33 ? ? ? ? ? ? ? ? ? ?75250 ? ? ?75251 1 row selected. |
owner有33个不同的值，object_id有75250,显然object_id的选择性更好。但是下面的查询，应用idx1_t与idx2_t的性能一样(COST与CONSISTENT GETS一样）。
|
dingjun123@ORADB> ?SELECT/*+index(t idx1_t)*/ * FROM t ? 2 ? WHERE owner=’DINGJUN123′ AND object_id=75677; 1 row selected. Elapsed: 00:00:00.00 Execution Plan ———————————————————- Plan hash value: 2071967826 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?97 | ? ??2?? (0)| 00:00:01 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”=75677) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ? ? ??4 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ? ?1403 ?bytes sent via SQL*Net to client ? ? ? ? 416 ?bytes received via SQL*Net from client ? ? ? ? ? 2 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ? ? 1 ?rows processeddingjun123@ORADB> ? SELECT/*+index(t idx2_t)*/ * FROM t ? 2 ? WHERE owner=’DINGJUN123′ AND object_id=75677; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ———————————————————- Plan hash value: 3787301248 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?97 | ??? 2?? (0)| 00:00:01 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OBJECT_ID”=75677 AND “OWNER”=’DINGJUN123′) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ? ??? 4 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ? ?1403 ?bytes sent via SQL*Net to client ? ? ? ? 416 ?bytes received via SQL*Net from client ? ? ? ? ? 2 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ? ? 1 ?rows processed |
? ? ??虽然如此，但是要记住，这个索引不是仅仅给这2条SQL使用的，事实上可能我们有的查询谓词只有owner或object_id,这时候得考虑使用owner作为前导列还是使用object_id作为前导列。
? ??还有其他引用owner,object_id的情况，比如GROUP BY ,ORDER BY,甚至SELECT…都需要进行整体的分析，这样才能建立最佳的索引。
?
2.有的列是大于(等于)或小于(等于）或者是like 模糊匹配等不等条件，有的列是等值的条件，等值的一般作为前导列更好
–做5次，增加几十万行SYS的进去
INSERT INTO t SELECT * FROM t WHERE owner=’SYS’;
COMMIT;
–重新收集统计信息（省略）
|
dingjun123@ORADB> SELECT * FROM t ? 2 ?WHERE owner=’DINGJUN123′ ? 3 ?AND object_id>=107889; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ———————————————————- Plan hash value: 2071967826 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 | |*??2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? ? 1 | ? ? ? | ? ? 3 ? (0)| 00:00:01?| ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ???2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”>=107889 AND “OBJECT_ID” IS ? ? ? ? ? ? ? NOT NULL) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ? ??? 5 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ? ?1399 ?bytes sent via SQL*Net to client ? ? ? ? 416 ?bytes received via SQL*Net from client ? ? ? ? ? 2 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ? ? 1 ?rows processed |
? ? ??上面的SQL走idx1_t,注意观察谓词，只有access,说明索引完全被利用上，很显然因为owner是前导列，而且是等值查询，按照前导列查询，然后只要分析索引的第2列object_id,当发现不满足条件object_id>=107889之后就停止了，索引扫描没有浪费。
|
dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t ? 2 ?WHERE owner=’DINGJUN123′ ? 3 ?AND object_id>=107889; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ———————————————————- Plan hash value: 3787301248 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ? ? 1 | ? ? ? | ? ? 3 ? (0)| 00:00:01 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OBJECT_ID”>=107889 AND “OWNER”=’DINGJUN123′ AND “OBJECT_ID” IS ? ? ? ? ? ? ? NOT NULL) ? ??? ?filter(“OWNER”=’DINGJUN123′) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ? ? ??5 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ? ?1399 ?bytes sent via SQL*Net to client ? ? ? ? 416 ?bytes received via SQL*Net from client ? ? ? ? ? 2 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ? ? 1 ?rows processed |
? ? ??强制使用idx2_t,object_id是前导列，谓词有access,还有filter,说明索引没有被完全利用上，这是因为object_id的不是等值查询，满足object_id>=107889的，按照顺序搜索
所以，中间可能有一些不满足owner=’DINGJUN123′的，还要filter掉。
这种查询和不等值条件作为前导列的查询，一旦object_id>=107889不满足owner=’DINGJUN123′的很多，那么必然造成过多不必要的索引搜索，COST与逻辑读会上升很快，
从而性能急剧下降，因为本例子基本都满足owner条件，所以没有啥浪费。但是下面的例子：
|
dingjun123@ORADB> SELECT * FROM t ? 2 ?WHERE owner=’DINGJUN123′ ? 3 ?AND object_id>=100; 2540 rows selected. Elapsed: 00:00:00.15 Execution Plan ———————————————————- Plan hash value: 2071967826 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ?2539 | ? 238K| ? 499 ? (0)| 00:00:06 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ?2539 | ? 238K| ? 499 ? (0)| 00:00:06 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ?2539 | ? ? ? | ? ?12 ? (0)| 00:00:01 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”>=100 AND “OBJECT_ID” IS ? ? ? ? ? ? ? NOT NULL) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ? ??527 ?consistent gets ? ? ? ? ?21 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ?268134 ?bytes sent via SQL*Net to client ? ? ? ?2275 ?bytes received via SQL*Net from client ? ? ? ? 171 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ???2540 ?rows processed |
还是使用idx1_t,没有问题。看下面的强制使用idx2_t的。
|
dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t ? 2 ?WHERE owner=’DINGJUN123′ ? 3 ?AND object_id>=100;2540 rows selected. Elapsed: 00:00:00.33 Execution Plan ———————————————————- Plan hash value: 3787301248 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ?2539 | ? 238K| ?3762 ? (1)| 00:00:46 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ?2539 | ? 238K| ?3762 ? (1)| 00:00:46 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ?2539 | ? ? ? | ?3274 ? (1)| 00:00:40 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OBJECT_ID”>=100 AND “OWNER”=’DINGJUN123′ AND “OBJECT_ID” IS ? ? ? ? ? ? ? NOT NULL) ? ? ???filter(“OWNER”=’DINGJUN123′) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ???3763 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ?268134 ?bytes sent via SQL*Net to client ? ? ? ?2275 ?bytes received via SQL*Net from client ? ? ? ? 171 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ?2540 ?rows processed dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id >= 100; |
?SELECT COUNT(*) FROM t WHERE object_id >= 100;
返回1032649行，但是WHERE owner=’DINGJUN123′ AND object_id>=100 只返回2540行，要filter掉百万行，轮询索引，造成了极大的浪费。
3.如果都是比较，都是之类的表达式
??这种情况，前导列，根据谓词，选择条件能够定位最接近处理结果的基数，并能够减少索引后filter的工作，因为必然有一列是要走access之后的filter,最好是filter能够过滤较少数据，不要做过多过滤。
??
例如：
|
dingjun123@ORADB> SELECT * FROM t ? 2 ?WHERE owner>=’DINGJUN123′ ? 3 ?AND object_id>=107872;37 rows selected. Elapsed: 00:00:00.00 Execution Plan ———————————————————- Plan hash value: 3787301248 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? 205 | 19680 | ???43?? (0)| 00:00:01 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? 205 | 19680 | ? ?43 ? (0)| 00:00:01 | |* ?2 | ??INDEX RANGE SCAN ? ? ? ? ?| IDX2_T?| ? 205 | ? ? ? | ? ? 3 ? (0)| 00:00:01 | ————————————————————————————– Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OBJECT_ID”>=107872 AND “OWNER”>=’DINGJUN123′ AND “OBJECT_ID” ? ? ? ? ? ? ? IS NOT NULL) ? ? ? ?filter(“OWNER”>=’DINGJUN123′) Statistics dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id>=107872; |
??关闭index SKIP SCAN,因为owner种类很少，oracle选择skip SCAN
?alter session set “_optimizer_skip_scan_enabled” = false;
?
|
dingjun123@ORADB> SELECT/*+index(t idx1_t)*/ * FROM t ? 2 ?WHERE owner>=’DINGJUN123′ ? 3 ?AND object_id>=107872; 37 rows selected. Elapsed: 00:00:00.23 Execution Plan ———————————————————- Plan hash value: 2071967826 ————————————————————————————– | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————————– | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? 205 | 19680 | ?3740?? (1)| 00:00:45 | | ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? 205 | 19680 | ?3740 ? (1)| 00:00:45 | |* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? 205 | ? ? ? | ?3700 ? (1)| 00:00:45 | ————————————————————————————–Predicate Information (identified by operation id): ————————————————— ? ?2 – access(“OWNER”>=’DINGJUN123′ AND “OBJECT_ID”>=107872 AND “OWNER” IS ? ? ? ? ? ? ? NOT NULL) ? ? ? ?filter(“OBJECT_ID”>=107872) Statistics ———————————————————- ? ? ? ? ? 1 ?recursive calls ? ? ? ? ? 0 ?db block gets ? ? ???3688 ?consistent gets ? ? ? ? ? 0 ?physical reads ? ? ? ? ? 0 ?redo size ? ? ? ?6468 ?bytes sent via SQL*Net to client ? ? ? ? 438 ?bytes received via SQL*Net from client ? ? ? ? ? 4 ?SQL*Net roundtrips to/from client ? ? ? ? ? 0 ?sorts (memory) ? ? ? ? ? 0 ?sorts (disk) ? ? ? ? ?37 ?rows processed |
因为owner>=’DINGJUN123′返回大量行，但是事实结果很少，只有几十行，过滤object_id>=107872,需要做大量工作，逻辑读和COST增大千倍+，性能低下。
后记：
? ? ?当然如何选择前导列的顺序很复杂，得全盘考虑对应的谓词，SELECT的列等要素，还要考虑ORDER BY ,GROUP BY等列，比如3列组合索引，如何考虑顺序。
? ? ?后续会补充更多的组合索引如何创建的要点。
原文地址：组合索引如何选择前导列的几点考虑, 感谢原作者分享。
如何在 Windows 11 上修复 100% 的磁盘使用率Apr 20, 2023 pm 12:58 PM如何在Window11上修复100%的磁盘使用率查找导致100%磁盘使用的有问题的应用程序或服务的直接方法是使用任务管理器。要打开任务管理器，请右键单击开始菜单并选择任务管理器。单击磁盘列标题，查看占用最多资源的内容。从那里开始，您将很好地了解从哪里开始。但是，问题可能比仅仅关闭应用程序或禁用服务更严重。继续阅读以查找问题的更多潜在原因以及如何解决这些问题。禁用SuperfetchSuperfetch功能（在Windows11中也称为SysMain）有助于通过访问预取文件来减少启动时
如何在 Windows 11 中隐藏文件和文件夹并从搜索中移除？Apr 26, 2023 pm 11:07 PM<h2>如何在Windows11上从搜索中隐藏文件和文件夹</h2><p>我们首先要看的是自定义Windows搜索文件的位置。通过跳过这些特定位置，您应该可以更快地看到结果，同时还可以隐藏您想要保护的任何文件。</p><p>如果要从Windows11上的搜索中排除文件和文件夹，请使用以下步骤：</p><ol&
以下是6种修复Windows 11搜索栏不可用的方法。May 08, 2023 pm 10:25 PM如果您的搜索栏在Windows11中不起作用，有几种快速方法可以立即启动并运行！任何微软操作系统有时都可能遇到故障，最新的操作系统不能免除该规则。此外，正如Reddit上的用户u/zebra_head1所指出的那样，同样的错误出现在Windows11的22H2Build22621.1413上。用户抱怨切换任务栏搜索框的选项随机消失。因此，您必须为任何情况做好准备。为什么我无法在计算机上的搜索栏中键入内容？无法在计算机上键入可归因于不同的因素和过程。以下是您应该注意的一些事项：Ctfmon.
Windows 11 Outlook 搜索不工作：6 个修复方法Apr 22, 2023 pm 09:46 PM在Outlook中运行搜索和索引疑难解答您可以开始的更直接的修复之一是运行搜索和索引疑难解答。要在Windows11上运行疑难解答，请执行以下操作：单击开始按钮或按Windows键并从菜单中选择设置。当设置打开时，选择系统>疑难解答>其他疑难解答。在右侧向下滚动，找到SearchandIndexing，然后单击Run按钮。选择Outlook搜索不返回结果并继续屏幕上的说明。当您运行它时，疑难解答程序将自动识别并修复问题。运行疑难解答后，打开Outlook并查看搜索是否正常。如
PPT怎么组合两个图形Mar 20, 2024 pm 05:00 PM大家好，今天我来给小伙伴们分享PPT怎么组合两个图形的具体操作步骤，大家按照这个步骤去做，一步一步就能学会了操作，以后就可以举一反三了，步骤详情就在下方，小伙伴们快来认真的看一看吧！1.首先，在电脑上打开一个PPT文档，然后新建一个PPT幻灯片，（如下图所示）。2.接着，在上方菜单栏项目【插入】中找到【形状】，并在形状的下拉框中选择需要导入的形状，（如下图红色圈出部分所示）。3.利用PPT的插入功能，依次将三角形和圆形两个形状插入至PPT中，并调整形状的大小和位置，（如下图红色箭头指向所示）。4
如何解决Python的组合错误？Jun 24, 2023 pm 10:39 PMPython中的组合问题是指在给定一组元素的情况下，如何生成其所有可能的组合。这是在许多计算机科学应用程序中经常遇到的一个问题。Python中有多种方法来解决这个问题，但是不正确的实现会导致组合错误。本文将介绍如何解决Python中的组合错误问题。使用递归函数在Python中，使用递归函数通常是实现组合问题的最常见方法之一。递归函数是指函数在其自身内部调用自
C++ 中什么时候应该使用继承，什么时候应该使用组合？Jun 03, 2024 am 10:33 AM在C++中，继承用于建立“是-一个”关系，强制执行接口一致性。而组合用于建立“包含-一个”关系，提供灵活性。继承：当子类与基类具有“是-一个”关系时使用，如车辆与汽车。组合：当容器类与组件类具有“包含-一个”关系时使用，如游戏中的角色包含武器、防御、健康值。
深入剖析MySQL索引优化策略Jun 14, 2023 pm 12:01 PM作为一种常用的关系型数据库，MySQL在今天的互联网应用中扮演着至关重要的角色。而在MySQL优化策略中，索引的使用更是至关重要。在MySQL中，索引是一种数据结构，用于快速定位数据中的特定行。使用索引可以大大提高查询效率，减少数据库处理数据的时间和资源。但不正确的索引使用方式，同样会导致数据库性能的下降。下面我们来深入剖析MySQL索引的优化策略，帮助您更
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具，用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
VSCode Windows 64位元 下載
微軟推出的免費、功能強大的一款IDE編輯器
記事本++7.3.1
好用且免費的程式碼編輯器
Atom編輯器mac版下載
最受歡迎的的開源編輯器
