当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

%开发误区where条件%在最前面不走索引

今天优化了下著录工具,之前在测试环境下,由于没有太多的数据,著录工具可以很快的查出图片数据,可是在正式环境,图片表有将近3000万的记录,著录工具打开需要的图片信息要3分多的时间,这么长的时间客户是无法接受的,开发人员找我一起研究。

SQL> select count(id) from images;

 COUNT(ID)
----------
  27481256

数据库大致信息结构如下:

SQL> select id,identifier,jpg_path from images where rownum<10;

ID  IDENTIFIER             JPG_PATH
--  --------------------   --------------------------------------------------
 1  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0001.jpg
 2  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0002.jpg
 3  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0003.jpg
 4  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0004.jpg
 5  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0005.jpg
 6  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0006.jpg
 7  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0007.jpg
 8  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0008.jpg
 9  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0009.jpg

9 rows selected.

开发人员说,因为他只知道图片的名字(例如:18-0220-003-0001.JPG),而不知道图片的路径(例如:/waiwubu/0220),因此有一条SQL是通过图片的名字找到对应的ID,然后将ID的值传递给令一条SQL,这条SQL通过ID的值找到图片,上表的ID是主键,IDENTIFIER和JPG_PATH字段都有索引,但是他写的SQL却不会走索引,SQL大致如下:

SQL> select id,identifier,jpg_path from images where jpg_path like '%02-01-006-002701-0002-0001.jpg';

ID  IDENTIFIER            JPG_PATH
--  --------------------  --------------------------------------------------
 1 02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                          6-0027012-01-006-002701-0002-0001.jpg
Elapsed: 00:00:30.73

开发人员不知道 LIKE条件,如果把%放到前面是不走索引的,客户的DBA竟然说LIKE条件不走索引,看下上面SQL的执行计划,肯定是全表扫描:

Execution Plan
----------------------------------------------------------
Plan hash value: 1450182775
---------------------------------------------------------------------------
|Id| Operation  | Name | Rows  | Bytes |Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------
|0|SELECT STATEMENT|   |  1374K|   106M| 98394  (1)|00:22:58|     |       |
|1|PARTITION HASH ALL|  |  1374K|   106M| 98394 (1)|00:22:58|  1  |     8 |
|* 2|TABLE ACCESS FULL|IMAGES|1374K| 106M|98394 (1)|00:22:58|  1  |     8 |
---------------------------------------------------------------------------

并不是LIKE条件不走索引,而是将%放到前面不走索引,下面是个简单的例子:

SQL> select id,identifier,jpg_path from images where jpg_path like
'W:172-01(JPG)2-01-006(002701-002900)2-01-006-0027012-01-006-
002701-0002-0001%';
ID  IDENTIFIER             JPG_PATH
--  ---------------------  --------------------------------------------------
 1  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0001.jpg
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 1856429361
----------------------------------------------------------------------------
|Id|Operation   | Name   |Rows|Bytes|Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------
|0|SELECT STATEMENT|     | 1  |  81 |   18   (0)| 00:00:01 |       |       |
|1|PARTITION HASH ALL|   |  1 |  81 |   18   (0)| 00:00:01 |     1 |     8 |
|2|TABLE ACCESS BY LOCAL INDEX ROWID|IMAGES|1|81|18(0)|00:00:01| 1 |     8 |
|* 3|INDEX RANGE SCAN|I_J_P| 1 |    |   17   (0)| 00:00:01 |     1 |     8 |
----------------------------------------------------------------------------

其实还有一种业务逻辑,这套系统的同一个IDENTIFIER的值会对应多张图片,也就是多条JPG_PATH的记录,而且著录的时候也会查询多个IDENTIFIER对应的图片,如果不按图片名查询,而是按IDENTIFIER来查询(开发人员是知道IDENTIFIER的,而且图片名的前5串就是IDENTIFIER),不但业务更容易实现,而且性能也会有很大的提升。

SQL> select id,identifier,jpg_path from images where IDENTIFIER='02-01-006-002701-0002';

ID  IDENTIFIER             JPG_PATH
--  ---------------------  --------------------------------------------------
 1  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0001.jpg
 2  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0002.jpg
… …
29  02-01-006-002701-0002  W:172-01(JPG)2-01-006(002701-002900)2-01-00
                           6-0027012-01-006-002701-0002-0029.jpg

29 rows selected.
Elapsed: 00:00:00.04

希望在一些比较大的系统中,开发人员尽量不要在LIKE条件中将%放到最前面,在实现功能的同时,尽量考虑下性能问题,减少DBA的工作量也就是在拯救DBA的生命。

本文固定链接: http://www.dbdream.com.cn/2012/07/%e5%bc%80%e5%8f%91%e8%af%af%e5%8c%ba/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年07月05日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: %开发误区where条件%在最前面不走索引 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

%开发误区where条件%在最前面不走索引:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter