测试数据

-- 建表
drop table if exists "FDW"

CREATE TABLE "FDW"."FDW"
(
"ID" VARCHAR(20) NOT NULL,
"NAME" VARCHAR(30),
"AGE" INT,
"HOBBY" VARCHAR(30),
"BIRTHDATE" DATETIME(6),
PRIMARY KEY("ID"));

-- 插入数据
insert into FDW values('a','符鼎威',1,'游泳',to_date('2024-01-01 00:00:00'))
insert into FDW values('b','saf',1,'跑步',to_date('2024-04-14 11:24:00'))
insert into FDW values('c','sdaf',3,'跳绳',to_date('2024-11-24 05:23:00'))
insert into FDW values('d','gfd',4,'骑行',to_date('2024-08-08 02:15:00'))
insert into FDW values('e','er',5,'走路',to_date('2024-06-15 18:22:00'))
insert into FDW values('f','heq',6,'篮球',to_date('2024-07-31 21:08:00'))
insert into FDW values('g','cxv',7,'足球',to_date('2024-02-11 15:04:00'))
insert into FDW values('h',null,null,null,null)

单字段索引

-- 1、单字段索引

-- 创建一个单字段索引来测试
create index NAME_IDX on FDW(NAME)

-- 建立一个时间索引来测试
create index BIRTHDATE_IDX on FDW(BIRTHDATE)

-- 普通查询走索引
explain select * from FDW where NAME = '1'

-- is not null会破坏索引
explain select * from FDW where NAME is not null

-- <> 符号会破坏索引
explain select * from FDW where NAME <> '符鼎威'

-- 左%符号会破坏索引
explain select * from FDW where NAME like '%符鼎威%'

-- 在索引字段上使用函数会破坏索引！！！

-- to_char函数会破坏索引
explain select * from FDW where to_char(NAME) = '1'

-- trunc函数会破坏索引
explain select * from FDW where trunc(BIRTHDATE,'ddd') = trunc('2024-05-11 08:01:00','ddd')

-- 用to_date会破坏索引
explain select * from FDW where to_date(BIRTHDATE) = to_date('2024-05-11 08:01:00')


-- in不会破坏索引
explain select * from FDW where NAME in ('符鼎威','saf')

-- or不会破坏索引
explain select * from FDW where (NAME ='符鼎威' or NAME is null)


-- 删除索引
drop index NAME_IDX
drop index BIRTHDATE_IDX

联合索引

TIP: 最左匹配原则，where条件中的条件必须是索引【从左到右的子集，不能有多余的条件】，否则不满足

-- 创建一个联合字段索引来测试
create index GROUP1_IDX on FDW(NAME,AGE)

-- 这样会走联合索引
explain select * from FDW where NAME = '符鼎威' and AGE = 1

-- 这样会走联合索引,是否命中不受where条件先后顺序影响
explain select * from FDW where AGE = 1 and NAME = '符鼎威' 

-- 这样不会走联合索引,因为where中有了多余的条件，不满足最左匹配原则
explain select * from FDW where AGE = 1 and NAME = '符鼎威' and HOBBY = '游泳'

-- 这样会走联合索引，因为满足最左匹配原则
explain select * from FDW where NAME = '符鼎威' 

-- 这样不会走联合索引，因为不满足最左匹配原则
explain select * from FDW where AGE = 1

-- 这样不会走联合索引，因为where中有了多余的条件，不满足最左匹配原则
explain select * from FDW where NAME = '符鼎威' and BIRTHDATE = to_date('2024-01-01')

-- 其中有一个字段是<>条件，就会破坏了索引，走全表扫描
explain select * from FDW where NAME = '符鼎威' and AGE <> 2

select 和 where 中各带一部分索引中字段

-- 只有索引中字段，无其它杂字段，会命中索引，但如果where条件不是最左，多了一步选择过程
explain select NAME from FDW where AGE = 1

-- 只有索引中字段，无其它杂字段，where条件是最左，会命中索引
explain select AGE from FDW where NAME = '符鼎威'

-- 不只有索引中字段，where条件不是最左，不会命中索引
explain select HOBBY,NAME from FDW where AGE = 1

-- 不只有索引中字段，where条件是最左，会命中索引
explain select HOBBY,AGE from FDW where NAME = '符鼎威'

-- 不只有索引中字段，where条件不是最左，不会命中索引
explain select HOBBY,AGE from FDW where NAME = '符鼎威' and BIRTHDATE = to_date('2024-01-01 00:00:00')

联合索引中字段全部在select中

-- 索引中字段全部在select中，此时不会命中索引
explain select NAME,AGE from FDW where HOBBY = '游泳'