大家帮忙做做实验：like的时候‘%’的位置和是否索引扫描的关系

以前有个人问过我一个问题，在查询的时候百分号的位置跟是否进行索引扫描的关系，它的答案大概是：%出现在字符串的第一个位置则不进行索引扫描，如果在中间和末尾则进行索引扫描，为了验证这个结论是否正确我在DB2中做了如下实验:

db2cmd
db2start
db2 connect to sample（如果sample数据库不存在可以用db2sampl创建）
db2 "create table test(col1 varchar(20))"
db2 "insert into test(col1) values('qooinmdfoaf00'),('adfafeee')"
db2 "create index col1 on test(col1 asc )"

db2expln -d sample -u luzl yqqlm0421 -terminal -q "select * from test where col1 like '%k'" -g

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "LUZL"


Statement:

  select *
  from test
  where col1 like '%k'


Section Code Page = 1208

Estimated Cost = 0.014781
Estimated Cardinality = 0.200000

Access Table Name = LUZL.TEST  ID = 2,5
|  Index Scan:  Name = LUZL.COL1  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: COL1 (Ascending)
|  #Columns = 1
|  #Key Columns = 0
|  |  Start Key: Beginning of Index
|  |  Stop Key: End of Index
|  Index-Only Access
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Index Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 1
Return Data Completion

End of section


Optimizer Plan:

     RETURN
     (   1)
       |
     IXSCAN
     (   2)
    /      \
 Index:  Table:
 LUZL    LUZL
 COL1    TEST

很明显，使用了索引扫描，大家再别的数据库试试，是否这个结论仅仅是传说还是，有根据呢？

评论

1 楼 myreligion 2010-05-14  

mysql好像是：%在后面如果有索引会使用索引，%在前面不使用。