-
mysql优化模糊查询_MySQL的LIKE模糊查询优化
2021-01-19 00:32:08%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
-
ue模糊查询_pgsql查询优化之模糊查询
2020-12-24 03:06:17前言一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大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。
以上
本文只是简单的介绍许多细节并未做深入的分析,欢迎留言指教或者讨论
-
mysql汉字模糊查询优化_MySQL优化索引及优化汉字模糊查询语句
2021-02-08 15:02:04对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。网上常见的是ASCII的英文字符优化,如下:select corp_code, corp_corp... -
pgsql查询优化之模糊查询实例详解
2020-09-09 02:30:47主要给大家介绍了关于pgsql查询优化之模糊查询的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用pgsql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧 -
Oracle模糊查询优化小技巧
2018-03-02 04:50:18技巧说明 我们平时模糊查询第一个...所以,我就在网上搜索优化模糊查询的方法,先试了一条简单的: 用instr函数代替like ! instr函数有两个参数:instr(string1,string2) string1 是原字符串 string2 是要查找... -
pgsql查询优化之模糊查询
2019-07-20 17:43:00一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配 案例 我们有一张千万级数据的检查报告表,需要通过检查报告来模糊... -
MySql like模糊查询优化 后缀模糊查询走索引 百万级别数据优化
2019-07-18 15:58:17在使用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:33like模糊查询 后通配 走索引 前通配 走全表。 -
模糊查询的优化
2019-10-14 21:14:32单表数据量在几十万条的时候,发现对一个中文字段进行模糊查询的时,巨慢,几乎达到了几十秒。 解决 Mysql5.7是有中文的全文索引检索功能。 CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名) WITH PARSER NGRAM; ... -
mysql模糊查询优化_【MySQL】Mysql模糊查询like提速优化
2021-01-18 19:12:36在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很... -
mysql模糊u查询优化_MySQL的LIKE模糊查询优化
2021-02-07 03:26:38%xxx%这种方式对于数据量少的时候,我们倒可以随意用,但是数据量大的时候,我们就体验到了查询性能的问题,像老化的车子艰难趴着坡一样,并且这种方式并未使用到索引,而是全表扫描mysql高效模糊查询 代替like而... -
加快mysql模糊搜索_mysql优化模糊搜索查询
2021-02-05 06:44:19对于模糊查询语句,最不利的情况是要like '%key%'这样的查询,但是如果是like 'key%'这种情况,那么mysql的索引在些查询方式上还是可以优化的。网上常见的是ASCII的英文字符优化,如下:select corp_code, corp_corp... -
oracle模糊查询优化
2016-11-09 23:13:16oracle模糊查询的优化策略 1,%前置会导致不走索引 或者走索引但是不如不走 不如走全文检索 2,优化方案: (1)采取另一种写法: 运用charindex函数 如:select * from table1 where instr('c',... -
mysql模糊查询优化_mysql模糊查询的优化方法--亲自实践
2021-01-18 19:12:36数据有4W多条,不多,但是模糊查询起来特别慢。1,尝试过用 select * from (select * from a union all select * from b。。。很多表union) as tempTable where name like "%a%" and person_id like "%1%",发现太慢... -
mysql 优化 - like模糊查询优化
2018-12-04 15:31:45在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很... -
Oracle模糊查询优化
2017-04-25 15:20:53Oracle模糊查询时容易出现的问题 -
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 '%关键字%' 的查询方式,因为不走索引,效率不高,单 ‘%’ 匹配又不符合业务逻辑,如何优化? 数据库中有表数据量 一百万左右 ... -
MySQL优化索引及优化汉字模糊查询语句
2016-11-24 16:56:20对于模糊查询语句,最不利的情况是要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%'这样的形式,但是效率非常的低,有什么替代的方法可以提高效率?
-
简单增删查改新闻管理系统
-
基于流形结构的图像地理信息标注方法
-
小程序的全栈开发新时代
-
arwin:Visual C ++项目中的arwin-源码
-
2021-02-25
-
阿里集团八年容器化演进之路
-
2013年上半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
使用Jenkins搭建iOS/Android持续集成打包平台
-
2011年上半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
2016年下半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
Linux基础入门系列课程
-
破折号-源码
-
常用的分布式事务解决方案
-
2010年下半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
-191-4-源码
-
MaxScale 实现 MySQL 读写分离与负载均衡
-
kafka中kafkaConsumer的poll拉取方法
-
Glasterfs 分布式网络文件系统
-
Flutter布局详解
-
Galera 高可用 MySQL 集群(PXC v5.7+Hapro)