数据库联合索引 vs 多个单列索引（公司大型项目实际碰到的问题）

置顶 Mr.Java.

已于 2022-10-15 10:40:58 修改

阅读量4.4k

点赞数 4

分类专栏： # oracle 文章标签： oracle 索引联合索引单列索引联合索引和单列索引

于 2021-03-17 16:18:36 首次发布

本文链接：https://blog.csdn.net/q258523454/article/details/114933096

版权

oracle 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/q258523454/article/details/114933096

Oracle / MySQL都可以参考!

目录：

Oracle 19c 联合索引 vs 多个列索引

Oracle 19c 联合索引 vs 多个列索引

我们在创建索引的时候。到底是选择联合索引还是选择多个单列索引？

环境准备

数据库oracle 19c

100万条随机数据

若只看结论，直接滑到最后

单列索引（多个）

建表（多个单列索引测试）：

CREATE TABLE "TEST"."STUDENT" 
( 
 "ID" NUMBER(10,0) NOT NULL ENABLE, 
 "NAME" VARCHAR2(10) NOT NULL ENABLE, 
 "NAME2" CHAR(10) DEFAULT 'default', 
 "AGE" NUMBER(4,0) NOT NULL ENABLE
) 
;

COMMENT ON COLUMN "TEST"."STUDENT"."NAME" IS '姓名';
COMMENT ON COLUMN "TEST"."STUDENT"."NAME2" IS '姓名2';
COMMENT ON COLUMN "TEST"."STUDENT"."AGE" IS '年龄';

CREATE UNIQUE INDEX STUDENT_ID_IDX ON STUDENT(ID);
CREATE UNIQUE INDEX STUDENT_AGE_IDX ON STUDENT(AGE);

写脚本插入数据。除了故意让有几个id字段重复之外，其他的全部随机

总共插入了接近100万条数据。（实际数据80万)

查看数据量

// 数据量
SELECT count(*) FROM student;
COUNT(*)|
--------|
  802610|


ID   |COUNT(*)|
-----|--------|
56789|   20004|
12345|    1106|
67072|      63|

为了测试，故意准备了三个占比不同的id

首先测试 id=56789

SELECT count(*) FROM STUDENT WHERE id=56789;
COUNT(*)|
--------|
   20004|

SELECT count(*) FROM STUDENT WHERE age=907;
COUNT(*)|
--------|
      71|

SELECT count(*) FROM STUDENT WHERE id=56789 AND age='907'
COUNT(*)|
--------|
       1|


explain plan FOR SELECT * FROM STUDENT WHERE id=56789 AND age='907' ;
SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                      |
-------------------------------------------------------------------------------------------------------|
Plan hash value: 3916273152                                                                            |
                                                                                                       |
-------------------------------------------------------------------------------------------------------|
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     ||
-------------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                    |                 |     2 |    48 |    53   (2)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT         |     2 |    48 |    53   (2)| 00:00:01 ||
|   2 |   BITMAP CONVERSION TO ROWIDS       |                 |       |       |            |          ||
|   3 |    BITMAP AND                       |                 |       |       |            |          ||
|   4 |     BITMAP CONVERSION FROM ROWIDS   |                 |       |       |            |          ||
|*  5 |      INDEX RANGE SCAN               | STUDENT_AGE_IDX |    80 |       |     3   (0)| 00:00:01 ||
|   6 |     BITMAP CONVERSION FROM ROWIDS   |                 |       |       |            |          ||
|*  7 |      INDEX RANGE SCAN               | STUDENT_ID_IDX  |    80 |       |    49   (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
                                                                                                       |
Predicate Information (identified by operation id):                                                    |
---------------------------------------------------                                                    |

我们可以看到同时用到了 id 和 age 两个索引

继续测试 id=123456

SELECT count(*) FROM STUDENT WHERE id=12345;
COUNT(*)|
--------|
    1106|

SELECT count(*) FROM STUDENT WHERE age=7777;
COUNT(*)|
--------|
      65|


SELECT count(*) FROM STUDENT WHERE id=12345 AND age='7777' ;
COUNT(*)|
--------|
       2|


explain plan FOR SELECT * FROM STUDENT WHERE id=12345 AND age='7777' ;
SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                                                     |
------------------------------------------------------------------------------------------------------|
Plan hash value: 2814013459                                                                           |
                                                                                                      |
------------------------------------------------------------------------------------------------------|
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     ||
------------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                    |                |     1 |    24 |     8   (0)| 00:00:01 ||
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT        |     1 |    24 |     8   (0)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN                  | STUDENT_ID_IDX |     6 |       |     3   (0)| 00:00:01 ||
------------------------------------------------------------------------------------------------------|
                                                                                                      |
Predicate Information (identified by operation id):                                                   |
---------------------------------------------------                                                   |
                                                                                                      |
   1 - filter("AGE"=7777)                                                                             |
   2 - access("ID"=12345)

可以看到只用到了 id 索引。

再测试一下 id=67072，结果一样。只用到了一个索引字段。

可以看出： 多个单列索引同时使用的时候可能只用到一个，也可能同时用到多个。

什么时候才会同时生效呢？

这个取决于数据库优化器，以上面的第一个id=56789查询结果为例，数据库认为 id=56789 本身就有2万条，而age=907的才71条。

那么数据库优化器选择先用age的索引,然后再用id的索引。

至于为什么后面两个 id=12345 和 id=67072 为什么不同时走2个索引，可能是因为数据库优化器认为id本身就能筛选和定位到很小范围，可能没必要走第二个索引。

联合索引

这个就是对对上面的单个索引同时创建 (id,age)，相当于创建了(id)+ (id,age)两个索引，这个不测试了。

结论：

索引的目的:为了查询

禁止建索引的字段:
1.区分度不大的字段, eg: 性别
2.大数据类型的字段, eg: text数据类型
3.不常作查询条件的字段

创建联合索引还是多个单列索引?
目前版本的数据库大都是支持索引合并功能的, 即查询可能会同时使用多个单列索引
1.如果 a,b,c 经常作为单独的查询条件, 则可考虑分别为 a,b,c 创建单个索引
2.如果 a,b,c 经常按 a,b,c 联合形式查询, 则创建组合索引 (a,b,c), 这里包含索引: (a),(a,b),(a,b,c),满足最左匹配原则
3.如果 a,b,c 经常按 a,b联合形式查询和单独c查询则可考虑创建组合索引 (a,b)和单独索引(c)
这样会多一个索引空间, 根据业务情况看是否有必要

总结: 根据实际业务情况优先考虑能否创建联合索引,其次再考虑多个单列索引
原因:
1.过多的索引会消耗物理空间
2.过多的索引会影响增删改的维护速度
3.不能完全依赖mysql数据库index merge或者Oracle的优化器, 这个是由数据库来决定,不能保证每次都触发索引优化

参考文献：

17 Optimizer Hints

Understanding Oracle INDEX-MERGE Joins