精华内容
下载资源
问答
  • MySQL - 如何优化模糊查询(like 模糊查询)

    万次阅读 热门讨论 2019-08-04 23:15:50
    在MySQL中,模糊查询肯定要使用like关键字,然后在加 %%,是代表前模糊还是后模糊。数据量小的情况下,不容易看出查询的效率,但是数据量达到百万级,千万级甚至更高的时候,查询的效率就很容易显现出来了,此时,...

    在MySQL中,模糊查询肯定要使用like关键字,然后在加 %%,是代表前模糊还是后模糊。数据量小的情况下,不容易看出查询的效率,但是数据量达到百万级,千万级甚至更高的时候,查询的效率就很容易显现出来了,此时,查询效率就显得很重要了,接下来,就要看你如何优化了。

    前面讲过

    MySQL - 如何提高SQL的查询效率(where条件优化)

    全局检索

    建立索引的情况下

    一般情况下like模糊查询的写法为(field已建立索引):

    SELECT `column` FROM `table` WHERE `field` like '%keyword%';

    上面的语句用explain解释来看,SQL语句并未用到索引,而且是全表搜索,如果在数据量超大的时候,可想而知最后的效率会是这样,和没有加索引是没什么区别的。

    对比下面的写法:

    SELECT `column` FROM `table` WHERE `field` like 'keyword%';

    这样的写法用explain解释看到,SQL语句使用了索引,搜索的效率大大的提高了!

    但是有的时候,我们在做模糊查询的时候,并非要想查询的关键词都在开头,所以如果不是特别的要求,"keywork%"并不合适所有的模糊查询

    这个时候,我们可以考虑用其他的方法。

    通过函数达到模糊查询的效果

    LOCATE('substr',str,pos)

    举例:

    SELECT LOCATE('xbar',`foobar`); 
    ###返回0 
    
    SELECT LOCATE('bar',`foobarbar`); 
    ###返回4
    
    SELECT LOCATE('bar',`foobarbar`,5);
    ###返回7

    解释:

    返回 substr 在 str 中第一次出现的位置,如果 substr 在 str 中不存在,返回值为 0 。

    如果pos存在,返回 substr 在 str 第pos个位置后第一次出现的位置,如果 substr 在 str 中不存在,返回值为0。

    解决模糊查询方案:

    SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
    

    备注:keyword是要搜索的内容,field为被匹配的字段,查询出所有存在keyword的数据

    POSITION('substr' IN `field`)

    position可以看做是locate的别名,功能跟locate一样:

    SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
    

    INSTR(`str`,'substr')

    SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 
    

    FIND_IN_SET(str1,str2)

    返回str2中str1所在的位置索引,其中str2必须以","分割开。

    SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);
    

    展开全文
  • Mssql 优化模糊查询

    2016-11-16 23:18:35
    '01,05,0b,0e' like '%01%' 改成 CHARINDEX('01', '01,05,0b,0e')>0
     '01,05,0b,0e' like '%01%' 
     改成
     CHARINDEX('01', '01,05,0b,0e')>0
    
    例子如下:
     SELECT ROW_NUMBER() OVER(ORDER BY OCCUR_DATE, SERIAL_NO) AS REC_NUM,
           SERIAL_NO, FRT_BIZ, CUST_CODE, CUST_NAME, CURR_STATUS, CURR_PHASE, 
           OP_USER, OP_NAME, CUACCT_CODE, INT_ORG,
           OCCUR_DATE, OCCUR_TIME, REMARK
           FROM FRT_BIZ_LOG
           WHERE OCCUR_DATE BETWEEN 20161001 AND 20161003
                 AND (CHARINDEX(','+CONVERT(VARCHAR, INT_ORG)+',', ','+'0'+',') > 0 OR '0' IN ('0', '999') )
                 AND CHARINDEX(FRT_BIZ, '01,05,0b,0e') > 0
    展开全文
  • 主要给大家介绍了关于pgsql查询优化模糊查询的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用pgsql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很...

    在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很重要!

    一般情况下like模糊查询的写法为(field已建立索引):

    SELECT`column`FROM`table`WHERE`field`like'%keyword%';

    上面的语句用explain解释来看,SQL语句并未用到索引,而且是全表搜索,如果在数据量超大的时候,可想而知最后的效率会是这样

    对比下面的写法:

    SELECT`column`FROM`table`WHERE`field`like'keyword%';

    这样的写法用explain解释看到,SQL语句使用了索引,搜索的效率大大的提高了!

     

    但是有的时候,我们在做模糊查询的时候,并非要想查询的关键词都在开头,所以如果不是特别的要求,"keywork%"并不合适所有的模糊查询

    我在网上搜索时发现很多mysql函数用来解决这个问题,我测试出来的结果是跟like相比并没有任何优势。

    1.LOCATE('substr',str,pos)方法

    SELECT`column`FROM`table`WHERELOCATE('keyword', `field`)>0

    2.POSITION('substr' IN `field`)方法

    SELECT`column`FROM`table`WHEREPOSITION('keyword'IN`filed`)

    3.INSTR(`str`,'substr')方法

    SELECT`column`FROM`table`WHEREINSTR(`field`,'keyword')>0

    这几种方法都试过后,发现百万级别数据以上,时间是跟like差不多,并没有解决问题,因为都没走到索引。

    这种情况下想要实现后几位模糊查询并且速度要快,在此我想了两个办法,一个是不需要mysql版本支持,一个需要mysql5.7版本以上,我测试的数据都是200W条数据以上的。

     

    第一种方法:新增一列字段,那个字段是你需要实现模糊查询的倒序,也就是原本是ABCD,那列字段就是DCBA

    然后在那个字段添上索引

    UPDATE tbl_ser_apply a set order_no_desc = REVERSE (SUBSTRING(a.order_no, -6))

    ALTER TABLE `tbl_ser_apply` ADD INDEX order_no_desc ( `order_no_desc` )

    我这边设的是后六位  也就是我把之前字段的后6位倒序后存入新的字段,也可以整个字段倒序后存入新的字段

    select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2

      ,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2

      from (

      select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,

      tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,

      tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,

      tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,

      tc.certificate_no as certificateNo

      from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on

      tmi.merchant_no=tui.mer_no  LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.order_no_desc like  REVERSE('%372191')

      ORDER BY tsa.create_time desc ) a  LEFT JOIN tbl_ser_apply a1 on  a.parentId=a1.id

    我的整个sql是这样的

    实际上最后查询的时候是这样

    where tsa.order_no_desc like  REVERSE('%372191')

    相当于是查询你新增字段,sql上加上reverse函数,这样子%就在后面,而且能实现整个字段的模糊查询

    这种做法需要修改sql和java代码,查询的是新增反向字段,而不是原来的字段

    这样就能实现走索引

    原来的sql不走索引的情况下查询出来需要20S,优化后只需要0.049S

    这种方法适合mysql5.7以下版本,这样能大大加快模糊查询速度,而且能到1000W以上应该都是没问题的

    第二种方法需要mysql5.7以上版本支持,用到虚拟列的方法,原理跟上述方法一样

    新增一列虚拟列,用函数索引计算你需要模糊查询的字段

    新增函数索引虚拟列,将虚拟列添加索引

    alter table tbl_ser_apply add column virtual_col varchar(20) as (REVERSE (SUBSTRING(tbl_ser_apply.order_no, -6)));

    ALTER TABLE `tbl_ser_apply` ADD INDEX  virtual_col ( ` virtual_col` )

    在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

    如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适

    综上,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式

     

    语法:

    [ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ]

    [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]

    这样做比上一个方法好的地方是,不需要修改java代码,只需要修改很小一部分的sql语句即可,上一个方法其实实现后要修改的java代码要不少,而且每次新增修改删除时,都要加上这个字段的代码,而新增虚拟列的话,那一列的字段是自动添加修改,通过计算得出的,所以代码完全不需要修改,只需要修改操作原来字段的sql即可。

    select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2

      ,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2

      from (

      select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,

      tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,

      tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,

      tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,

      tc.certificate_no as certificateNo

      from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on

      tmi.merchant_no=tui.mer_no  LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.virtual_col like  '372191%'

      ORDER BY tsa.create_time desc ) a  LEFT JOIN tbl_ser_apply a1 on  a.parentId=a1.id

    这是我新增虚拟列后的查询语句,同样也是查询的虚拟列的字段。

    相当于查的是这个where tsa.virtual_col like  '372191%',这样也能实现%在后面,也就能走索引。

    经过我的测试后,原来不走索引是20S 用第一种方法是0.049s 用第二种方法的话是0.1S  虽然慢了0.05S 那是计算数据的时间,但这样的方案已经大大缩短了模糊查询时间,而且不需要修改java代码,个人推荐使用第二种!

    展开全文
  • 一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配 案例 我们有一张千万级数据的检查报告表,需要通过检查报告来模糊...

    前言

          一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配    

    案例

          我们有一张千万级数据的检查报告表,需要通过检查报告来模糊搜索某个条件,我们先创建如下索引:

    CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);

          然后搜个简单的模糊匹配条件如 LIKE "血常规%",可以发现查询计划生成如下,索引并没有被使用上,这是因为传统的btree索引并不支持模糊匹配

          查阅文档后发现,pgsql可以在Btree索引上指定操作符:text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops,它们分别对应字段类型text、varchar和 char,官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式(LIKE或POSIX正则表达式)的查询中。”, 有些抽象,我们先试试看。创建如下索引并查询刚才的条件 LIKE"血常规%":(参考pgsql的文档 https://www.postgresql.org/docs/10/indexes-opclass.html

    CREATE INDEX lab_report_report_name_index ON lab.lab_report (report_name varchar_pattern_ops);

          发现确实可以走索引扫描 ,执行时间也从213ms优化到125ms,但是,如果搜索LIKE "%血常规%"就又会走全表扫描了!    这里我们引入本篇博客的主角"pg_trgm"和"pg_bigm"。

          创建这两个索引前分别需要引入如下两个扩展包 :

    CREATE EXTENSION pg_trgm;
    CREATE EXTENSION pg_bigm;

     这两个索引的区别是:“pg_tigm”为pgsql官方提供的索引,"pg_tigm"为日本开发者提供。下面是详细的对比:(参考pg_bigm的文档 http://pgbigm.osdn.jp/pg_bigm_en-1-2.html

    Comparison with pg_trgm

    The pg_trgm contrib module which provides full text search capability using 3-gram (trigram) model is included in PostgreSQL. The pg_bigm was developed based on the pg_trgm. They have the following differences:

    Functionalities and Featurespg_trgmpg_bigm
    Phrase matching method for full text search3-gram2-gram
    Available indexGIN and GiSTGIN only
    Available text search operatorsLIKE (~~), ILIKE (~~*), ~, ~*LIKE only
    Full text search for non-alphabetic language
    (e.g., Japanese)
    Not supported (*1)Supported
    Full text search with 1-2 characters keywordSlow (*2)Fast
    Similarity searchSupportedSupported (version 1.1 or later)
    Maximum indexed column size238,609,291 Bytes (~228MB)107,374,180 Bytes (~102MB)
    • (*1) You can use full text search for non-alphabetic language by commenting out KEEPONLYALNUM macro variable in contrib/pg_trgm/pg_trgm.h and rebuilding pg_trgm module. But pg_bigm provides faster non-alphabetic search than such a modified pg_trgm.
    • (*2) Because, in this search, only sequential scan or index full scan (not normal index scan) can run.

    pg_bigm 1.1 or later can coexist with pg_trgm in the same database, but pg_bigm 1.0 cannot.

       如无特殊要求推荐使用"pg_bigm",我们测试一下效果:

    CREATE INDEX lab_report_report_name_index ON lab_report USING gin (report_name public.gin_bigm_ops);

     

    可以使用位图索引扫描,对于本次案例,使用pg_trgm效果同pg_bigm。

    以上

    本文只是简单的介绍许多细节并未做深入的分析,欢迎留言指教或者讨论

     

    转载于:https://www.cnblogs.com/y-yp/p/11218387.html

    展开全文
  • 在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很...
  • 对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。 网上常见的是ASCII的英文字符优化,如下: [sql] view ...
  • mysql模糊查询优化

    万次阅读 2015-01-09 20:00:36
    使用下面的函数来进行模糊查询,如果出现的位置>0,表示包含该字符串。 查询效率比like要高。 如:  table.field like ‘%AAA%’ 可以改为 locate (‘AAA’ , table.field) > 0 注:locate(substr,str) 用...
  • Mysql模糊查询优化

    千次阅读 2019-03-22 11:02:09
    目的:mysql模糊查询优化 select* from test where name like '%keyword%'; 使用like关键词不会用到索引,查询效率也比较低 优化如下:使用instr,position,locate函数 SELECT * FROM test where instr('...
  • oracle性能优化-模糊查询

    千次阅读 2018-06-11 11:08:19
    当oracle数据库数据量非常大时,使用双%的模糊查询效率会变得比较慢,因为双%的模糊查询并没有使用索引,而是用的全表扫描。而单%则使用了索引进行检索。以下是几种检索方式的效率对比,表中有800万条数据。1.使用...
  • sql优化——模糊查询

    千次阅读 2018-04-26 10:44:47
    like 和 instr的查询效率 select name from user where instr(id, '99')> 0; 等价于 select name from user where id like '%99%'; LIKE查询一次,就走一次全表扫描,效率非常慢 同样的效果,现在来换...
  • Oracle 模糊查询优化

    千次阅读 2013-08-06 19:20:20
    模糊查询是数据库查询中经常用到的,一般常用的格式如下: (1)字段 like '%关键字%'  字段包含"关键字“的记录 即使在目标字段建立索引也不会走索引,速度最慢  (2)字段 like '关键字%' 字段以"关键字...
  • sql优化模糊查询

    千次阅读 2018-11-19 10:23:33
    like模糊查询 后通配 走索引 前通配 走全表。
  • 模糊查询优化

    2019-10-14 21:14:32
    单表数据量在几十万条的时候,发现对一个中文字段进行模糊查询的时,巨慢,几乎达到了几十秒。 解决 Mysql5.7是有中文的全文索引检索功能。 CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名) WITH PARSER NGRAM; ...
  • oracle模糊查询优化

    2016-11-09 23:13:16
    oracle模糊查询优化策略  1,%前置会导致不走索引 或者走索引但是不如不走 不如走全文检索  2,优化方案: (1)采取另一种写法: 运用charindex函数 如:select * from table1 where instr('c',...
  • java mongodb模糊查询优化

    千次阅读 2018-03-26 10:21:13
    query.addCriteria(Criteria.where("time").regex(Pattern.compile( "^.*" + CommUtil.toString(queryParams....这个是我之前用的模糊查询;利用 Pattern来拼接查询条件。但是后来发现当...
  • 模糊查询like优化

    2019-01-28 09:10:32
    原sql语句:select project_ name from project_info where project_name like "...所以采用另一种机制,为project_name建立索引,从索引表中模糊匹配索引值去查询索引值。 原sql语句中我只需要project_nam...
  • 优化后的快速模糊查询插件QuickQuery,新增模糊查询,翻页不清空数据和点击回调方法
  • es6.8模糊查询优化实践

    千次阅读 2019-12-06 10:50:07
    基于此,有必要对Elasticsearch的模糊查询进行性能优化。 https://github.com/memoryFuhao/elasticsearch_client (打个广告 以上链接是本人开发的一个es客户端工具,支持es大部分 CRUD操作 分页、分组、嵌套...
  • MySql模糊查询性能优化

    千次阅读 2019-06-27 15:54:19
    通用模糊查询操作: select * from n_order_pay_refund bean where 1=1 and bean.account_number like '%843121000000104259822%' order by bean.pay_time 调用instr()函数: select * from n_order_pay_refund ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 48,096
精华内容 19,238
关键字:

优化模糊查询