? 选择组合索引的前导列，必须根据具体的业务（SQL）写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的， 本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更
?选择组合索引的前导列，必须根据具体的业务（SQL）写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的，本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更多复杂的情况需要具体分析。
1.都是等值条件的列，谁做前导列都一样
|
owner有33个不同的值，object_id有75250,显然object_id的选择性更好。但是下面的查询，应用idx1_t与idx2_t的性能一样(COST与CONSISTENT GETS一样）。
|
dingjun123@ORADB> ?SELECT/* 索引(t idx1_t)*/ * FROM t ？ 2 ? WHERE Owner='DINGJUN123' AND object_id=75677; 已选择 1 行。 已过去：00:00:00.00 执行计划 ————————————————————- 计划哈希值：2071967826 ——————————————————————————————– |身份证号？|手术 ？ ？ ？ ？ ？ ？ ？ ？ ？ |姓名 ？ |行？|字节 |成本（%CPU）|时间 ？ ？ | ——————————————————————————————– | ？ 0 |选择语句？ ？ ？ ？ ？ ？| ？ ？ ？ ？| ？ ？ 1 | ？ ?97 | ？ ??2?? (0)| 00:00:01 | | ？ 1 | ?按索引 ROWID 访问表| T？ ？ ？| ？ ？ 1 | ？ ?97 | ？ ？ 2 ? (0)| 00:00:01 | |* ?2 | ？索引范围扫描？ ？ ？ ？ ？| IDX1_T | ？ ？ 1 | ？ ？ ？ | ？ ？ 1 ? (0)| 00:00:01 | ——————————————————————————————– 谓词信息（通过操作id标识）： —————————————————— ？ ?2 – 访问(“OWNER”='DINGJUN123' AND “OBJECT_ID”=75677) 统计 ————————————————————- ？ ？ ？ ？ ？ 1 ?递归调用 ？ ？ ？ ？ ？ 0 ?db 块获取 ？ ？ ？ ？ ??4 ?一致获取 ？ ？ ？ ？ ？ 0 ?物理读取 ？ ？ ？ ？ ？ 0 ?重做大小 ？ ？ ？ ?1403 ?通过 SQL*Net 发送到客户端的字节 ？ ？ ？ ？ 416 ?通过 SQL*Net 从客户端接收的字节 ？ ？ ？ ？ ？ 2 ?SQL*Net 往返于客户端 ？ ？ ？ ？ ？ 0 ?排序（记忆） ？ ？ ？ ？ ？ 0 ?排序（磁盘） ？ ？ ？ ？ ？ 1 ?已处理行dingjun123@ORADB> ？ SELECT/* 索引(t idx2_t)*/ * FROM t ？ 2 ? WHERE Owner='DINGJUN123' AND object_id=75677; 已选择 1 行。 已过去：00:00:00.01 执行计划 ————————————————————- 计划哈希值：3787301248 ——————————————————————————————– |身份证号？|手术 ？ ？ ？ ？ ？ ？ ？ ？ ？ |姓名 ？ |行？|字节 |成本（%CPU）|时间 ？ ？ | ——————————————————————————————– | ？ 0 |选择语句？ ？ ？ ？ ？ ？| ？ ？ ？ ？| ？ ？ 1 | ？ ?97 | ??? 2?? (0)| 00:00:01 | | ？ 1 | ?按索引 ROWID 访问表| T？ ？ ？| ？ ？ 1 | ？ ?97 | ？ ？ 2 ? (0)| 00:00:01 | |* ?2 | ？索引范围扫描？ ？ ？ ？ ？| IDX2_T | ？ ？ 1 | ？ ？ ？ | ？ ？ 1 ? (0)| 00:00:01 | ——————————————————————————————– 谓词信息（通过操作id标识）： —————————————————— ？ ?2 – 访问(“OBJECT_ID”=75677 AND “OWNER”='DINGJUN123′) 统计 ————————————————————- ？ ？ ？ ？ ？ 1 ?递归调用 ？ ？ ？ ？ ？ 0 ?db 块获取 ？ ？ ？ ??? 4 ?一致的获取 ？ ？ ？ ？ ？ 0 ?物理读取 ？ ？ ？ ？ ？ 0 ?重做大小 ？ ？ ？ ?1403 ?通过 SQL*Net 发送到客户端的字节 ？ ？ ？ ？ 416 ?通过 SQL*Net 从客户端接收的字节 ？ ？ ？ ？ ？ 2 ?SQL*Net 往返于客户端 ？ ？ ？ ？ ？ 0 ?排序（记忆） ？ ？ ？ ？ ？ 0 ?排序（磁盘） ？ ？ ？ ？ ？已处理 1 行 |
? ? ??虽然如此，但是要记住，这个索引不是仅仅给这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之后就停止了，索引扫描没有浪费。
? 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的。
? 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; ? COUNT(*) ———- ? ?1032649 |
?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′) 统计 dingjun123@ORADB>选择 COUNT(*) FROM t WHERE object_id>=107872; |
??关闭index SKIP SCAN，因为所有者种类很少，oracle选择skip SCAN
？更改会话设置“_optimizer_skip_scan_enabled”= false;
？
|
MySQL中的存储过程是什么？May 01, 2025 am 12:27 AM存储过程是MySQL中的预编译SQL语句集合，用于提高性能和简化复杂操作。1.提高性能：首次编译后，后续调用无需重新编译。2.提高安全性：通过权限控制限制数据表访问。3.简化复杂操作：将多条SQL语句组合，简化应用层逻辑。
查询缓存如何在MySQL中工作？May 01, 2025 am 12:26 AMMySQL查询缓存的工作原理是通过存储SELECT查询的结果，当相同查询再次执行时，直接返回缓存结果。1）查询缓存提高数据库读取性能，通过哈希值查找缓存结果。2）配置简单，在MySQL配置文件中设置query_cache_type和query_cache_size。3）使用SQL_NO_CACHE关键字可以禁用特定查询的缓存。4）在高频更新环境中，查询缓存可能导致性能瓶颈，需通过监控和调整参数优化使用。
与其他关系数据库相比，使用MySQL的优点是什么？May 01, 2025 am 12:18 AMMySQL被广泛应用于各种项目中的原因包括：1.高性能与可扩展性，支持多种存储引擎；2.易于使用和维护，配置简单且工具丰富；3.丰富的生态系统，吸引大量社区和第三方工具支持；4.跨平台支持，适用于多种操作系统。
您如何处理MySQL中的数据库升级？Apr 30, 2025 am 12:28 AMMySQL数据库升级的步骤包括：1.备份数据库，2.停止当前MySQL服务，3.安装新版本MySQL，4.启动新版本MySQL服务，5.恢复数据库。升级过程需注意兼容性问题，并可使用高级工具如PerconaToolkit进行测试和优化。
您可以使用MySQL的不同备份策略是什么？Apr 30, 2025 am 12:28 AMMySQL备份策略包括逻辑备份、物理备份、增量备份、基于复制的备份和云备份。1.逻辑备份使用mysqldump导出数据库结构和数据，适合小型数据库和版本迁移。2.物理备份通过复制数据文件，速度快且全面，但需数据库一致性。3.增量备份利用二进制日志记录变化，适用于大型数据库。4.基于复制的备份通过从服务器备份，减少对生产系统的影响。5.云备份如AmazonRDS提供自动化解决方案，但成本和控制需考虑。选择策略时应考虑数据库大小、停机容忍度、恢复时间和恢复点目标。
什么是mySQL聚类？Apr 30, 2025 am 12:28 AMMySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe
如何优化数据库架构设计以在MySQL中的性能？Apr 30, 2025 am 12:27 AM在MySQL中优化数据库模式设计可通过以下步骤提升性能：1.索引优化：在常用查询列上创建索引，平衡查询和插入更新的开销。2.表结构优化：通过规范化或反规范化减少数据冗余，提高访问效率。3.数据类型选择：使用合适的数据类型，如INT替代VARCHAR，减少存储空间。4.分区和分表：对于大数据量，使用分区和分表分散数据，提升查询和维护效率。
您如何优化MySQL性能？Apr 30, 2025 am 12:26 AMtooptimizemysqlperformance，lofterTheSeSteps：1）inasemproperIndexingTospeedUpqueries，2）使用ExplaintplaintoAnalyzeandoptimizequeryPerformance，3）ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections，4）
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
热工具
SublimeText3 Linux新版
SublimeText3 Linux最新版
适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。
SublimeText3汉化版
中文版，非常好用
Dreamweaver Mac版
视觉化网页开发工具
PhpStorm Mac 版本
最新（2018.2.1 ）专业的PHP集成开发工具
