`
蓝皮鼠
  • 浏览: 10721 次
  • 性别: Icon_minigender_1
  • 来自: 广州
文章分类
社区版块
存档分类
最新评论

使用MySQL的全文检索实现Like索引

阅读更多
    在数据库使用中,DBA都会告诉大家SQL的LIKE条件为%XXX%号时,由于不能使用索引,当数据量变大时(比如超过百万条),全表扫描会导致性能很差。
    但是在实际业务中,很难避免这种需求。比如模糊搜索用户帐号,昵称之类。既然这个需求必须做,但又不可以直接用LIKE。这里我和大家分享一下我们关于这种需求的一种解决方案。当然别人也可能采用过类似的办法,我不是很清楚。所以也用一下“原创”吧。

    MySQL数据库很早就支持全文索引,但是全文索引和LIKE语句是不同的。具体点说,全文索引的单位是词,耳LIKE匹配的是字符。当然实际的区别更大,比如“老鼠爱大米”这段文本用全文搜索的话,条件“老鼠爱大米”,“老鼠和大米”,“大米老鼠”,“大米与老鼠”会搜索到内容,但是“爱”,“鼠爱”,“爱大”不会搜索到内容。反之,使用LIKE搜索时,“老鼠和大米”,“大米老鼠”,“大米与老鼠”不会找到内容,而“爱”,“鼠爱”,“爱大”会找到内容。我们这里不讨论两种方式的优劣,根据实际情况每种功能都会有各自的实际需求。比如对于大段文本,全文检索是最好的方法,但是对于姓名,帐号,昵称等很短的通常无意义文本,LIKE会更合适一些。

    虽然全文检索和LIKE搜索不同,但是在特殊情况下,可以用全文搜索功能来实现LIKE搜索。具体就是每个字符作为一个词,而且使用双引号来限制词精确匹配(简单点说就是老鼠大米和大米老鼠不同),这样可以实现LIKE搜索的功能。

    下面还是说一下具体的做法吧。

    首先,数据库指定 --ft_min_word_len=2 --ft_stopword_file=""。第一个参数是告诉数据库,小于2个字符的词忽略。第二个是告诉数据库不忽略任何特殊词。这些设置是给实现功能创造条件。

    然后建搜索表
CREATE TABLE  tbl_search (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(500),
  PRIMARY KEY  (id),
  FULLTEXT KEY idx_name (name)
) ENGINE=MyISAM AUTO_INCREMENT=1;


   
	static String encode(String input) {
		if (input == null) return null;
		StringBuilder output = new StringBuilder();
		for (int i = 0, c = input.length(); i < c; ++i) {
			char ch = input.charAt(i);

			if (ch >= '0' && ch <= '9' || ch >= 'A' && ch <= 'Z' 
					|| ch >= '0' && ch <= '9' || ch >= 'A' && ch <= 'Z' 
					|| ch == '_' || ch == '-') {
				output.append(Integer.toHexString(ch)).append(' ');

			} else if (ch >= 'a' && ch <= 'z' || ch >= 'a' && ch <= 'z') {
				output.append(Integer.toHexString((int)ch - 32)).append(' ');

			} else {
				Character.UnicodeBlock block = Character.UnicodeBlock.of(ch);
				if (block == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
						|| block == Character.UnicodeBlock.KATAKANA
						|| block == Character.UnicodeBlock.HIRAGANA) {
					output.append(Integer.toHexString(ch)).append(' ');
				} else {
					// do nothing
				}
			}

		}

		// trim blank
		int last = output.length() - 1;
		if (last > 0 && output.charAt(last) == ' ') {
			output.deleteCharAt(last);
		}

		return output.toString();
	}


    使用上面的代码对要搜索的内容编码,比如内容是“蓝皮鼠2008”,编码后的结果是“84dd 76ae 9f20 32 30 30 38”。将编码后的内容存入name字段。
    使用如下SQL语句进行搜索

select * from tbl_search where match(name) against('"76ae 9f20 32"' in boolean mode)


    上面的搜索是搜“皮鼠2”,应该可以搜到相关的内容。

    最终效果:
    在150万条记录时,比模糊LIKE快50到100多倍
    LIKE %皮鼠% 用两秒左右
    match 用 0.005 到 0.01秒左右

    欢迎大家自己尝试,有效果的请顶一下



2
0
分享到:
评论
1 楼 xieye 2009-08-14  
这个方法很有意思!

相关推荐

    mysql实现全文检索

    要在MySQL中实现全文检索,首先需要创建一个包含全文索引的表。创建时,需要指定哪些字段将用于全文检索。例如: ```sql CREATE TABLE 表名 ( cCode CHAR(10), sTitle VARCHAR(250), sContent TEXT, FULLTEXT...

    MySQL全文索引、联合索引、like查询、json查询速度哪个快

    本文将深入探讨全文索引、联合索引、LIKE查询以及JSON查询这四种不同方法在实际应用中的速度比较,并通过实例进行详细解析。 首先,全文索引(Full-text Index)是MySQL提供的一种特殊类型的索引,专门用于提高全文...

    如何在MySQL中提高全文搜索效率

    在MySQL中提高全文搜索效率是优化数据库性能的关键步骤,尤其是对于那些处理大量文本数据的应用程序...正确配置和使用全文索引,对于那些需要处理大量文本信息的互联网应用来说,是提升用户体验和系统性能的关键策略。

    MySQL索引最佳实践

    - 使用`LIKE 'abc%'`的查询可以通过索引来优化,因为它可以转化为一个闭区间查询。 - 使用`LIKE '%abc'`的查询通常无法利用索引进行优化。 #### 六、多列索引的使用 - 多列索引是指在一个索引中包含多个字段。 - ...

    Mysql实现全文检索、关键词跑分的方法实例

    在此之前,MySQL仅支持英文的全文索引,对于非英文的全文搜索,需要开发者自己实现分词器来预处理数据。在5.7.6版本中,引入了ngram全文解析器,它可以对文本进行分词,将连续的n个字符作为单词。ngram的大小由全局...

    MySQL创建全文索引分享

    使用全文索引进行查询时,不能像使用`LIKE`关键字那样简单操作,而应该使用`MATCH`和`AGAINST`关键字。例如: ```sql SELECT * FROM article WHERE MATCH(title,content) AGAINST ('查询字符串'); ``` 这条SQL语句...

    php mysql like 实现多关键词搜索的方法

    在数据库中使用PHP进行多关键词搜索的方法主要有两种,一是使用LIKE关键字结合百分号(%)进行模式匹配,二是实现分词检索数据库。下面将分别对这两种方法进行详细阐述。 首先,来看使用LIKE关键字配合模式匹配的方法...

    mysql之Linux安装,重点是索引的优化笔记

    ### MySQL之Linux安装与索引优化笔记 #### 一、MySQL简介及Linux版安装 **1. MySQL概述** MySQL是一种关系型数据库管理系统(RDBMS),由瑞典MySQL AB公司开发,目前属于Oracle公司。它是一种开源软件,因其性能...

    mysql索引原理深入解析

    全文索引适用于长文本字段,如消息内容,可以提升`LIKE`查询的效率,但MySQL内置的全文索引功能有限,可能需要使用第三方搜索引擎。 接下来,我们探讨索引的存储模型。最基本的思路是利用二分查找,即有序数组,它...

    横瓜数据库全文检索中间件

    你无需对自己的数据库文件做任何更改,就可以为数据库文件创建可供编程语言调用的ActiveX DLL全文检索接口,可用于任何与数据库检索有关的查询,例如全文搜索、垂直搜索、海量数据库LIKE式快速查询等。软件主要特点...

    072401MySQL索引2

    若需在事务环境下使用全文索引,可以考虑使用Sphinx或Lucene这样的外部搜索引擎。 创建索引的语法如下: ```sql CREATE INDEX index_name ON table_name(column_name(length)) [USING {BTREE|HASH}] COMMENT '...

    如何实现MySQL数据库中文模糊检索

    在大数据量的场景下,建议在实际应用中进行性能测试,如果性能影响较大,可以考虑优化索引或者使用全文搜索引擎等更高效的解决方案。 总结来说,实现MySQL数据库中的中文模糊检索,关键在于理解字符集和排序规则对...

    Sphinx 全文检索实践指南 - 介绍.pdf

    51CTO技术沙龙第19期讲义:Sphinx 全文检索实践指南主讲:李沫南部分主要内容:全文检索 VS 数据库检索SELECT * FROM documents WHERE title like '%test%'CPU 100%'30秒内只能进行一次查询'没有找到记录......

    请描述MySQL有哪些索引类型

    MySQL中的索引是数据库优化的重要工具,用于加速数据检索,提高查询效率。下面将详细介绍标题和描述中提到的五种索引类型以及如何创建它们。 1. **普通索引(Index)**: 普通索引是最基础的索引类型,没有特定的...

    mysql全文模糊搜索MATCH AGAINST方法示例

    创建全文索引的表必须使用MyISAM引擎,因为InnoDB等其他引擎不支持全文索引。以下是如何创建一个具有全文索引的UTF8编码的MyISAM表的示例: ```sql CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT ...

    MySQL索引检查.docx

    1. 无索引分析:当我们执行`SELECT * FROM admin WHERE name LIKE 'k%'`这样的查询时,由于`name`字段没有索引,MySQL必须遍历整个`admin`表,通过`Using where`来筛选满足条件的行。结果显示`rows`为219852,这意味...

Global site tag (gtag.js) - Google Analytics