什么是index skip scan？
create index idx01 on tab1(name,id);
select xx from tab1 where id=xxx;
SQL
这条查询能用到索引idx01吗？****不一定
skip scan能解决什么问题？
简单说，如果id的选择度很高，又没有合适的索引。走index scan用上一个索引比全表性能更好的情况下skip scan也可以提高性能。看下不同数据库是怎么实现的？
如何实现的？
前导列拆分
create index idx02 on stadents(性别,name);
需求语句
select * from students where name='张三'
SQL
使用index会把上面的语句逻辑拆分
select * from students where 性别='男' and name='张三'
union
select * from students where 性别='女' and name='张三';
SQL
这样拆分后，查询只要走两次索引扫描，不用走全表扫描。
Oracle
CREATE TABLE test_objects AS
SELECT * FROM all_objects;
CREATE INDEX test_objects_i ON test_objects (owner, object_name, subobject_name);
EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_OBJECTS', cascade => TRUE);
SQL> SET AUTOTRACE ON
SQL> SELECT owner, object_name
2 FROM test_objects
3 WHERE object_name = 'DBMS_OUTPUT';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DBMS_OUTPUT
SYS DBMS_OUTPUT
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 64 | 14 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 2 | 64 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL
PostgreSQL
PostgreSQL18中引入了 index skip scan
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=389.72..389.97 rows=100 width=50) (actual time=0.155..0.156 rows=0 loops=1)
Buffers: shared hit=12
-> Sort (cost=389.72..389.97 rows=100 width=50) (actual time=0.153..0.154 rows=0 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=12
-> Index Scan using idx_users_status_created on users (cost=0.43..386.40 rows=100 width=50) (actual time=0.145..0.146 rows=0 loops=1)
Index Cond: (created_at > (CURRENT_DATE - '7 days'::interval))
Index Searches: 4
Buffers: shared hit=12
Planning Time: 0.221 ms
Execution Time: 0.204 ms
(12 rows)
SQL
PostgreSQL中使用了Index Scan using idx_users_status_created而不是单独的index skip，这里多了4次index searches，可以看出PostgreSQL把这当成了索引扫描的增强。最终是否选择skip scan还是要看代价的。
MySQL
MySQL 8.0引入了索引跳跃扫描
达梦
通过USE_INDEX_SKIP_SCAN支持
OceanBase支持
www.oceanbase.com/knowledge-b…
参考链接：
