注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

like 查询优化一例  

2011-10-25 21:17:43|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

    
          今天发现一生产库上的负载较高,是因为一个与 like 有关的 SQL 引起的,虽然情形比较简单,但也记录下;
这个查询语句走了全表扫描非常慢,可以改下SQL,优化后,执行时间由原来的 4秒可降到 1 毫秒以内,详细信息
如下。
 
 
--1 sql 语句
select user_id, account, nickname, sex, city, head_icon, country_code from tmp_user
where 1=1  and user_id <> 226102033  and sex=1 and province like '%云南%' limit 20;

    备注:从数据库日志来看,每天有大量类似语句,执行时间都在1秒以下,有的甚至达到 4秒。

 
--2 表结构
skytf=> \d tmp_user
                       Table "skytf.tmp_user"
    Column    |              Type              |                Modifiers               
--------------+--------------------------------+-----------------------------------------
 user_id      | integer                        | not null
 account      | character varying(32)          | not null
 nickname     | character varying(64)          |
 sex          | smallint                       | default 1
 birthday     | character varying(20)          | default '1991-01-01'::character varying
 email        | character varying(20)          |
 id_num       | character varying(40)          |
 mobile       | character varying(20)          |
 country      | character varying(32)          | default '中国'::character varying
 province     | character varying(20)          |
 city         | character varying(16)          |
 signature    | character varying(128)         |
 head_icon    | integer                        | default 0
 state        | integer                        | default 0
 reg_time     | timestamp(0) without time zone |
 country_code | integer                        | default 86
Indexes:
    "tmp_user_pkey" PRIMARY KEY, btree (user_id)
    "tbl_mpc_user_info_username_key" UNIQUE, btree (account)
    "tmp_user_birthday" btree (birthday)
    "tmp_user_city_index" btree (city)
    "tmp_user_nickname_index" btree (nickname)
    "tmp_user_province_index" btree (province)
    "tmp_user_reg_time_idx" btree (reg_time)
    "tmp_user_sex_index" btree (sex, city, birthday, country, id_num)
    "tmp_user_signature" btree (signature)
   
    备注:在字段 province 上有索引。
   
   
--3 表大小
skytf=> \dt+ tmp_user
                                   List of relations
     Schema     |        Name         | Type  |     Owner      |  Size   | Description
----------------+---------------------+-------+----------------+---------+-------------
 skytf | tmp_user | table | skytf | 2628 MB |    
 
 
--4优化前的语句
skytf=> explain analyze select user_id, account, nickname, sex, city, head_icon, country_code from tmp_user
skytf-> where 1=1  and user_id <> 226102033  and sex=1 and province like '%广西%' limit 20;
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..511445.19 rows=1 width=61) (actual time=514.228..4489.034 rows=3 loops=1)
   ->  Seq Scan on tmp_user  (cost=0.00..511445.19 rows=1 width=61) (actual time=514.225..4489.025 rows=3 loops=1)
         Filter: ((user_id <> 226102033) AND ((province)::text ~~ '%广西%'::text) AND (sex = 1))
 Total runtime: 4489.073 ms
(4 rows)
 
 
 
--5 优化后的语句
skytf=> explain analyze select user_id, account, nickname, sex, city, head_icon, country_code from tmp_user
skytf-> where 1=1  and user_id <> 226102033  and sex=1 and province like '山东%' limit 20;
                                                                          QUERY PLAN                                               
                          
------------------------------------------------------------------------------------------------------------------------------------
---------------------------
 Limit  (cost=0.00..6.36 rows=1 width=61) (actual time=0.104..0.312 rows=1 loops=1)
   ->  Index Scan using tmp_user_province_index on tmp_user  (cost=0.00..6.36 rows=1 width=61) (actual time=0.103..0.311 rows=1 loops=1)
         Index Cond: (((province)::text >= '山东'::text) AND ((province)::text < '山丝'::text))
         Filter: ((user_id <> 226102033) AND ((province)::text ~~ '山东%'::text) AND (sex = 1))
 Total runtime: 0.342 ms

  备注:优化后,从计划看出已经走索引 tmp_user_province_index 了,时间下降到 0.3 毫秒。


--6 总结
   1 当检索条件为 like '%something%' 时,这时用不到字段上的索引,会走全表扫描;
   2 当检索条件为 like 'something%' 时,这时可以用到检索列上的索引。
   3 在这种情况下, oracle 和 pg 类似,这里不测试了。

  评论这张
 
阅读(26942)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016