新上线的系统很多数字类型的字段都是使用varchar2类型存放,要转换成number类型时,和开发人员对number类型的字段在查询时加上单引号走不走索引的问题产生了分歧,大家都知道,如果使用char类型存放数字,在查询时如果不加单引号是不会走索引的,测试信息如下,数据库版本11.2.0.4.0。
创建测试表如下。
01 | SQL> conn dbdream/dbdream |
05 | SQL> create table ind_text as select object_id,object_name from dba_objects; |
09 | SQL> ALTER TABLE IND_TEXT ADD OBJECT_CHAR VARCHAR2(22); |
13 | SQL> UPDATE IND_TEXT SET OBJECT_CHAR=OBJECT_ID; |
21 | SQL> ALTER TABLE IND_TEXT MOVE; |
创建索引。
01 | SQL> create index ind_OBJECT_ID on ind_text(OBJECT_ID); |
05 | SQL> create index ind_OBJECT_NAME on ind_text(OBJECT_NAME); |
09 | SQL> CREATE INDEX IND_OBJECT_CHAR ON IND_TEXT(OBJECT_CHAR); |
现在OBJECT_ID字段是用number类型存放数字,OBJECT_CHAR字段使用varchar2类型存放数字,通过下面的测试可以看到如果查询OBJECT_CHAR字段字段,不加单引号是不会走索引的。
01 | SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR='100832'; |
03 | OBJECT_CHAR OBJECT_NAME |
04 | -------------------- ------------------ |
08 | ---------------------------------------------------------- |
09 | Plan hash value: 1825303736 |
11 | ----------------------------------------------------------------------------------------------- |
12 | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
13 | ----------------------------------------------------------------------------------------------- |
14 | | 0 | SELECT STATEMENT | | 1 | 78 | 1 (0)| 00:00:01 | |
15 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 78 | 1 (0)| 00:00:01 | |
16 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_CHAR | 1 | | 1 (0)| 00:00:01 | |
17 | ----------------------------------------------------------------------------------------------- |
19 | SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR=100832; |
21 | OBJECT_CHAR OBJECT_NAME |
22 | -------------------- -------------------- |
26 | ---------------------------------------------------------- |
27 | Plan hash value: 2154511419 |
29 | ------------------------------------------------------------------------------ |
30 | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
31 | ------------------------------------------------------------------------------ |
32 | | 0 | SELECT STATEMENT | | 6 | 468 | 143 (3)| 00:00:02 | |
33 | |* 1 | TABLE ACCESS FULL| IND_TEXT | 6 | 468 | 143 (3)| 00:00:02 | |
34 | ------------------------------------------------------------------------------ |
那么到底查询用number类型存放的数字,查询时不加单引号肯定是走索引的,加上单引号会不会走索引呢,看下面的测试。
01 | SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID=100832; |
04 | -------------- ----------- |
08 | ---------------------------------------------------------- |
09 | Plan hash value: 417862871 |
11 | --------------------------------------------------------------------------------------------- |
12 | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
13 | --------------------------------------------------------------------------------------------- |
14 | | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | |
15 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 79 | 2 (0)| 00:00:01 | |
16 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | |
17 | --------------------------------------------------------------------------------------------- |
19 | SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID='100832'; |
22 | -------------- ---------- |
26 | ---------------------------------------------------------- |
27 | Plan hash value: 417862871 |
29 | --------------------------------------------------------------------------------------------- |
30 | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
31 | --------------------------------------------------------------------------------------------- |
32 | | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | |
33 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 79 | 2 (0)| 00:00:01 | |
34 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | |
35 | --------------------------------------------------------------------------------------------- |
可见如果使用number类型存放数字,不论查询时加不加单元号都会走索引的。
总结:如果使用字符类型存放数字,只有在查询时使用单引号将数字引起来才会使用索引;如果是使用数字类型存放数字,查询时不管是否使用单引号,都会走索引。
建议继续学习:
- 由浅入深探究mysql索引结构原理、性能分析与优化 (阅读:15126)
- 浅谈MySQL索引背后的数据结构及算法 (阅读:9964)
- 由浅入深理解索引的实现(2) (阅读:6458)
- HBase二级索引与Join (阅读:5848)
- 如何建立合适的索引? (阅读:5457)
- InnODB和MyISAM索引统计集合 (阅读:5296)
- Innodb 表和索引结构 (阅读:4861)
- mysql查询中利用索引的机制 (阅读:4819)
- MySQL索引背后的数据结构及算法原理 (阅读:4485)
- mysql索引浅析 (阅读:4137)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习