今天优化了下著录工具，之前在测试环境下，由于没有太多的数据，著录工具可以很快的查出图片数据，可是在正式环境，图片表有将近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的生命。

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