精华内容
下载资源
问答
  • %xxx%这种方式对于数据量少的时候,我们倒可以随意用,但是数据量大的时候,我们就体验到了查询性能的问题,像老化的车子艰难趴着坡一样,并且这种方式并未使用到索引,而是全表扫描mysql高效模糊查询 代替like而...

    %xxx%这种方式对于数据量少的时候,我们倒可以随意用,但是数据量大的时候,我们就体验到了查询性能的问题,像老化的车子艰难趴着坡一样,并且这种方式并未使用到索引,而是全表扫描

    mysql 高效模糊查询 代替like而对于xxx% 或者%xxx方式,explain一下可以发现查询使用到了索引,性能提升了不少,当然这种方式不适用与所有的查询场景。

    可以采取以下的函数进行查询。

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

    POSITION('substr' IN field)方法

    INSTR(str,'substr')方法

    查询效率比如果:table.field like '%AAA%' 可以改为locate ('AAA' , table.field) > 0

    返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0.

    mysql> select LOCATE('bar', 'foobarbar'); -> 4 mysql> select LOCATE('xbar', 'foobar'); -> 0

    该函数是多字节可靠的。

    返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。

    mysql> select LOCATE('bar', 'foobarbar',5); -> 7mysql like模糊查询提高效率的奇葩方法

    一张表大概40万左右的数据,用like模糊查询title字段,很慢,title字段已经建立了索引,mysql 对 someTitle% 这样的模糊查询在有索引的前提下是很快的。

    所以下面这两台sql语句差别就很大了

    $sql1 = "...... title like someTitle%" (话费0.001秒)

    $sql2 = "...... title like %someTitle%" (话费0.8秒)

    这两句的效率相差了800倍,这很可观啊。

    所以我有个想法:在不用分词的方法的前提下,把存储的title字段,加一个特别的前缀,比如"im_prefix",比如一条记录的title="我是标题党",那么存储的时候就存储为"im_prefix我是标题党"。

    这样一来,我们要模糊查找"标题党"这个关键词的时候,就把sql写成这样:

    $sql1 = "...... title like im_prefix%标题党%" (花费0.001秒),前台显示数据的时候,自然把取到的title过滤掉"im_prefix"这个前缀了

    展开全文
  • 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提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配

    案例

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

    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 search

    3-gram

    2-gram

    Available index

    GIN and GiST

    GIN only

    Available text search operators

    LIKE (~~), ILIKE (~~*), ~, ~*

    LIKE only

    Full text search for non-alphabetic language

    (e.g., Japanese)

    Not supported (*1)

    Supported

    Full text search with 1-2 characters keyword

    Slow (*2)

    Fast

    Similarity search

    Supported

    Supported (version 1.1 or later)

    Maximum indexed column size

    238,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。

    以上

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

    展开全文
  • 对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。网上常见的是ASCII的英文字符优化,如下:select corp_code, corp_corp...
  • 主要给大家介绍了关于pgsql查询优化模糊查询的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用pgsql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 技巧说明 我们平时模糊查询第一个...所以,我就在网上搜索优化模糊查询的方法,先试了一条简单的: 用instr函数代替like ! instr函数有两个参数:instr(string1,string2) string1 是原字符串 string2 是要查找...
  • 一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配 案例 我们有一张千万级数据的检查报告表,需要通过检查报告来模糊...
  • 在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很...
  • Mysql模糊查询优化

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

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

    2019-10-14 21:14:32
    单表数据量在几十万条的时候,发现对一个中文字段进行模糊查询的时,巨慢,几乎达到了几十秒。 解决 Mysql5.7是有中文的全文索引检索功能。 CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名) WITH PARSER NGRAM; ...
  • 在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很...
  • %xxx%这种方式对于数据量少的时候,我们倒可以随意用,但是数据量大的时候,我们就体验到了查询性能的问题,像老化的车子艰难趴着坡一样,并且这种方式并未使用到索引,而是全表扫描mysql高效模糊查询 代替like而...
  • 对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。网上常见的是ASCII的英文字符优化,如下:select corp_code, corp_corp...
  • oracle模糊查询优化

    2016-11-09 23:13:16
    oracle模糊查询优化策略  1,%前置会导致不走索引 或者走索引但是不如不走 不如走全文检索  2,优化方案: (1)采取另一种写法: 运用charindex函数 如:select * from table1 where instr('c',...
  • 数据有4W多条,不多,但是模糊查询起来特别慢。1,尝试过用 select * from (select * from a union all select * from b。。。很多表union) as tempTable where name like "%a%" and person_id like "%1%",发现太慢...
  • 在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很...
  • Oracle模糊查询优化

    2017-04-25 15:20:53
    Oracle模糊查询时容易出现的问题
  • oracle性能优化-模糊查询

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

    2014-08-01 16:06:00
    在sql语句中使用 like模糊查询时,应该尽量避免%%,因为模糊查询是比较慢的,当出现这样的情况时,应该考虑优化。 举个例子:我在表中查询2012 年创建的记录 SELECT * FROM `component_data` WHERE creation_...
  • oracle 模糊查询优化

    2019-05-23 14:42:43
    数据量如何上千万建议使用全文索引,维护成本相对较高,如果几百万的数据,使用 like '%关键字%' 的查询方式,因为不走索引,效率不高,单 ‘%’ 匹配又不符合业务逻辑,如何优化? 数据库中有表数据量 一百万左右 ...
  • 对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。 网上常见的是ASCII的英文字符优化,如下: [sql] view ...
  • 模糊查询like优化

    2019-01-28 09:10:32
    原sql语句:select project_ name from project_info where project_name like "...所以采用另一种机制,为project_name建立索引,从索引表中模糊匹配索引值去查询索引值。 原sql语句中我只需要project_nam...
  • 模糊查询优化

    2013-07-18 14:27:13
    有个字段需要使用like '%aaa%'这样的形式,但是效率非常的低,有什么替代的方法可以提高效率?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,390
精华内容 556
关键字:

优化模糊查询