最近刚好又聊到了组合索引,由于担心自己记忆的不准确就实践了一番,结果发现和百度的好像不太一样,对此有疑惑的我,于是写下了这篇文章,希望对大家有用。
第一种情况:组合索引(主键)
1、建表
-- CREATE TABLE CREATE TABLE A ( AID NUMBER NOT NULL, ACOU NUMBER NOT NULL, ACOUA CHAR(30) NOT NULL ) TABLESPACE BDCK PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED ); -- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS ALTER TABLE A ADD CONSTRAINT PA PRIMARY KEY (AID, ACOU, ACOUA) USING INDEX TABLESPACE BDCK PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED );
2、数据生成,我用的PLSQL的数据生成工具
3、索引测试
语句1:使用组合索引全条件查询
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU='35552' AND A.ACOUA='TaiwanTaoyuan'
![](https://i-blog.csdnimg.cn/blog_migrate/de0a8b4995b744090997a7e5c05d77d0.png)
语句2:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE '%35552%'
![](https://i-blog.csdnimg.cn/blog_migrate/93d253019c81a713d29bb17c847d8ed0.png)
语句3:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE '35552%'
![](https://i-blog.csdnimg.cn/blog_migrate/93d253019c81a713d29bb17c847d8ed0.png)
语句4:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE '%TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/93d253019c81a713d29bb17c847d8ed0.png)
语句5:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE 'TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/bb7e712ef213b7eb6acb208909f904ea.png)
语句6:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA='TaiwanTaoyuan'
![](https://i-blog.csdnimg.cn/blog_migrate/7815d145551a8870faf1ce57b5af1ce6.png)
语句7:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA='%TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/7815d145551a8870faf1ce57b5af1ce6.png)
语句8:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA LIKE 'TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/7815d145551a8870faf1ce57b5af1ce6.png)
语句全部命中索引PA,但6、7、8优化器目标不同产生的结果不同,
以语句6为例限定行数的执行计划如下:
以语句6为例限定行数的执行计划如下:
![](https://i-blog.csdnimg.cn/blog_migrate/e4d10e8b5104e0774b47c21c32e7e6a4.png)
其余优化目标均不走索引!
第二种情况:组合索引(非主键)
1、接上表,重新创建索引
-- Drop primary, unique and foreign key constraints alter table A drop constraint PA cascade; -- Create/Recreate indexes create index ka on A (aid, acou, acoua);
2、索引测试
语句1:使用组合索引全条件查询
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU='35552' AND A.ACOUA='TaiwanTaoyuan'
![](https://i-blog.csdnimg.cn/blog_migrate/29b3101c6af99ea957a99fef379a9468.png)
语句2:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE '%35552%'
![](https://i-blog.csdnimg.cn/blog_migrate/735e09dcbdb061af4418045bf44c15a1.png)
语句3:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE '35552%'
![](https://i-blog.csdnimg.cn/blog_migrate/735e09dcbdb061af4418045bf44c15a1.png)
语句4:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE '%TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/735e09dcbdb061af4418045bf44c15a1.png)
语句5:
SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE 'TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/26a5c01e14d4af09a4f99b368affb636.png)
语句6:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA='TaiwanTaoyuan'
![](https://i-blog.csdnimg.cn/blog_migrate/85990a9cde2933e51a07f697df19d26f.png)
语句7:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA='%TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/85990a9cde2933e51a07f697df19d26f.png)
语句8:
SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA LIKE 'TaiwanTaoyuan%'
![](https://i-blog.csdnimg.cn/blog_migrate/85990a9cde2933e51a07f697df19d26f.png)
和组合索引(主键)一样,全部走索引,只是索引扫描类型不一致!
结论:
1、组合索引任意字段查询都走索引,和顺序无关
2、查询条件有前缀索引和无前缀索引只影响索引扫描类型,在不同优化器下无前缀,只有在全表以及行数限制的条件下走索引