一次隐藏较隐蔽的SQL优化问题[@more@]1. 场景说明:


stockpile ----IC卡的供求信息
xh-- 型号-----字符型
index SP_XH, 是函数index, 定义如下create index SP_XH on stockpile (upper(xh));

高频度查询SQL
select /*+ INDEX(s SP_XH) */ count(s.xh)
from stockpile s
where upper(s.xh) like 'TL431%'

COUNT(S.XH)
-----------
186875


2. 问题出现:


读完index SP_XH 统计出数量后, 为何有多读一次STOCKPILE, 幸好它对性能的恶化程度还不算非常厉害，


select /*+ INDEX(s SP_XH) */ count(s.xh)
from stockpile s
where upper(s.xh) like 'TL431%'

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=272 Card=1Bytes=14)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'STOCKPILE' (Cost=272 Card=284 Bytes=3976)
3 2 INDEX (RANGE SCAN) OF 'SP_XH' (NON-UNIQUE) (Cost=3 Card=284)


3. 分析解决:

a. 这个sql如果用count(*), 只读 index SP_XH { upper(s.xh) } , 不读表 。
b. 如果用 count(s.xh) , s.xh的数据只有在表中有 , 对应的index SP_XH { upper(s.xh) } 没有, 所以读完index还要读表。
c. 如改写成 count( upper(s.xh)) 一样可以只读index不读表。

select /*+ INDEX(s SP_XH) */ count( upper(s.xh))
from stockpile s
where upper(s.xh) like 'TL431%'


4. 回顾:

a. 函数index 中 存贮的是: 函数处理列后的值 和 行rowid。

b. count(列) 出现, 走的又是 列的函数index, count(列)会多读一次表。

c. count(列)出现时, 要看一下这对业务来说是否真的有必要, 如果只是程序员的一个书写习惯，不要轻易的忽视。