- 浏览: 952183 次
文章分类
- 全部博客 (428)
- Hadoop (2)
- HBase (1)
- ELK (1)
- ActiveMQ (13)
- Kafka (5)
- Redis (14)
- Dubbo (1)
- Memcached (5)
- Netty (56)
- Mina (34)
- NIO (51)
- JUC (53)
- Spring (13)
- Mybatis (17)
- MySQL (21)
- JDBC (12)
- C3P0 (5)
- Tomcat (13)
- SLF4J-log4j (9)
- P6Spy (4)
- Quartz (12)
- Zabbix (7)
- JAVA (9)
- Linux (15)
- HTML (9)
- Lucene (0)
- JS (2)
- WebService (1)
- Maven (4)
- Oracle&MSSQL (14)
- iText (11)
- Development Tools (8)
- UTILS (4)
- LIFE (8)
最新评论
-
Donald_Draper:
Donald_Draper 写道刘落落cici 写道能给我发一 ...
DatagramChannelImpl 解析三(多播) -
Donald_Draper:
刘落落cici 写道能给我发一份这个类的源码吗Datagram ...
DatagramChannelImpl 解析三(多播) -
lyfyouyun:
请问楼主,执行消息发送的时候,报错:Transport sch ...
ActiveMQ连接工厂、连接详解 -
ezlhq:
关于 PollArrayWrapper 状态含义猜测:参考 S ...
WindowsSelectorImpl解析一(FdMap,PollArrayWrapper) -
flyfeifei66:
打算使用xmemcache作为memcache的客户端,由于x ...
Memcached分布式客户端(Xmemcached)
mysql大表查询的时候,'String%'模糊查询可以使用B+树类型的索引prefix,然而'String%'匹配模式在应用中不是我们所需要的,往往需要'%String%',这是我们可以考虑使用FULLTEXT索引,INNODE是以红黑树来,存储全文索引,下面我们就来测试一下全文索引。
首先建表:
CREATE TABLE fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY (FTS_DOC_ID)
);
插入记录:
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('1', 'some one like you');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('2', 'you can you up');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('3', 'I like your style');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('4', 'one day ,i see you');
创建全文索引:
mysql> CREATE FULLTEXT INDEX idx_fts ON fts_a(body);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看索引:
mysql> show index from fts_a;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fts_a | 0 | PRIMARY | 1 | FTS_DOC_ID | A | 4 | NULL | NULL | | BTREE | | |
| fts_a | 1 | idx_fts | 1 | body | NULL | 4 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
设置索引参数:
mysql> SET GLOBAL innodb_ft_aux_table='test/fts_a';
Query OK, 0 rows affected
查看全文索引(倒排索引)信息:
mysql> select * from fts_a;
+------------+--------------------+
| FTS_DOC_ID | body |
+------------+--------------------+
| 1 | some one like you |
| 2 | you can you up |
| 3 | I like your style |
| 4 | one day ,i see you |
+------------+--------------------+
4 rows in set
mysql> select * from information_schema.INNODB_FT_INDEX_TABLE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| can | 2 | 2 | 1 | 2 | 4 |
| day | 4 | 4 | 1 | 4 | 4 |
| like | 1 | 3 | 2 | 1 | 9 |
| like | 1 | 3 | 2 | 3 | 2 |
| one | 1 | 4 | 2 | 1 | 5 |
| one | 1 | 4 | 2 | 4 | 0 |
| see | 4 | 4 | 1 | 4 | 11 |
| some | 1 | 1 | 1 | 1 | 0 |
| style | 3 | 3 | 1 | 3 | 12 |
| you | 1 | 4 | 3 | 1 | 14 |
| you | 1 | 4 | 3 | 2 | 0 |
| you | 1 | 4 | 3 | 2 | 8 |
| you | 1 | 4 | 3 | 4 | 15 |
| your | 3 | 3 | 1 | 3 | 7 |
+-------+--------------+-------------+-----------+--------+----------+
14 rows in set
删除记录innodb并不会立即删除索引,要进行优化操作,测试如下
mysql> DELETE FROM fts_a WHERE fts_doc_id=4;
Query OK, 1 row affected
mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
优化:
mysql> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected
mysql> OPTIMIZE TABLE test.fts_a;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| test.fts_a | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set
mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
mysql> SELECT * FROM information_schema.INNODB_FT_BEING_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
利用全文索引查询记录:
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+------------+-------------------+
| FTS_DOC_ID | body |
+------------+-------------------+
| 1 | some one like you |
| 3 | I like your style |
+------------+-------------------+
2 rows in set
从查询解释我们可以看出使用个全文索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | fts_a | fulltext | idx_fts | idx_fts | 0 | NULL | 1 | Using where |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
1 row in set
查询文档相关性
mysql>
SELECT FTS_DOC_ID,body,MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE) AS Relevance FROM fts_a ;
+------------+-------------------+--------------------+
| FTS_DOC_ID | body | Relevance |
+------------+-------------------+--------------------+
| 1 | some one like you | 0.0906190574169159 |
| 2 | you can you up | 0 |
| 3 | I like your style | 0.0906190574169159 |
| 5 | hell girls | 0 |
+------------+-------------------+--------------------+
4 rows in set
查询存在like和you的文档
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('+like +you' IN BOOLEAN MODE);
+------------+-------------------+
| FTS_DOC_ID | body |
+------------+-------------------+
| 1 | some one like you |
+------------+-------------------+
1 row in set
查看一般匹配查询,并没有使用索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE body LIKE '%like%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fts_a | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set
首先建表:
CREATE TABLE fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY (FTS_DOC_ID)
);
插入记录:
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('1', 'some one like you');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('2', 'you can you up');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('3', 'I like your style');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('4', 'one day ,i see you');
创建全文索引:
mysql> CREATE FULLTEXT INDEX idx_fts ON fts_a(body);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看索引:
mysql> show index from fts_a;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fts_a | 0 | PRIMARY | 1 | FTS_DOC_ID | A | 4 | NULL | NULL | | BTREE | | |
| fts_a | 1 | idx_fts | 1 | body | NULL | 4 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
设置索引参数:
mysql> SET GLOBAL innodb_ft_aux_table='test/fts_a';
Query OK, 0 rows affected
查看全文索引(倒排索引)信息:
mysql> select * from fts_a;
+------------+--------------------+
| FTS_DOC_ID | body |
+------------+--------------------+
| 1 | some one like you |
| 2 | you can you up |
| 3 | I like your style |
| 4 | one day ,i see you |
+------------+--------------------+
4 rows in set
mysql> select * from information_schema.INNODB_FT_INDEX_TABLE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| can | 2 | 2 | 1 | 2 | 4 |
| day | 4 | 4 | 1 | 4 | 4 |
| like | 1 | 3 | 2 | 1 | 9 |
| like | 1 | 3 | 2 | 3 | 2 |
| one | 1 | 4 | 2 | 1 | 5 |
| one | 1 | 4 | 2 | 4 | 0 |
| see | 4 | 4 | 1 | 4 | 11 |
| some | 1 | 1 | 1 | 1 | 0 |
| style | 3 | 3 | 1 | 3 | 12 |
| you | 1 | 4 | 3 | 1 | 14 |
| you | 1 | 4 | 3 | 2 | 0 |
| you | 1 | 4 | 3 | 2 | 8 |
| you | 1 | 4 | 3 | 4 | 15 |
| your | 3 | 3 | 1 | 3 | 7 |
+-------+--------------+-------------+-----------+--------+----------+
14 rows in set
删除记录innodb并不会立即删除索引,要进行优化操作,测试如下
mysql> DELETE FROM fts_a WHERE fts_doc_id=4;
Query OK, 1 row affected
mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
优化:
mysql> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected
mysql> OPTIMIZE TABLE test.fts_a;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| test.fts_a | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set
mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
mysql> SELECT * FROM information_schema.INNODB_FT_BEING_DELETED;
+--------+
| DOC_ID |
+--------+
| 4 |
+--------+
1 row in set
利用全文索引查询记录:
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+------------+-------------------+
| FTS_DOC_ID | body |
+------------+-------------------+
| 1 | some one like you |
| 3 | I like your style |
+------------+-------------------+
2 rows in set
从查询解释我们可以看出使用个全文索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | fts_a | fulltext | idx_fts | idx_fts | 0 | NULL | 1 | Using where |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
1 row in set
查询文档相关性
mysql>
SELECT FTS_DOC_ID,body,MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE) AS Relevance FROM fts_a ;
+------------+-------------------+--------------------+
| FTS_DOC_ID | body | Relevance |
+------------+-------------------+--------------------+
| 1 | some one like you | 0.0906190574169159 |
| 2 | you can you up | 0 |
| 3 | I like your style | 0.0906190574169159 |
| 5 | hell girls | 0 |
+------------+-------------------+--------------------+
4 rows in set
查询存在like和you的文档
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('+like +you' IN BOOLEAN MODE);
+------------+-------------------+
| FTS_DOC_ID | body |
+------------+-------------------+
| 1 | some one like you |
+------------+-------------------+
1 row in set
查看一般匹配查询,并没有使用索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE body LIKE '%like%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fts_a | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set
- FULLTEXT.rar (494 Bytes)
- 下载次数: 0
发表评论
-
Deadlock found when trying to get lock; try restarting transaction解决方式
2017-07-18 23:00 1963MySQL 事务的学习整理:http://blog.csdn. ... -
MySQL慢日志
2017-05-18 16:05 993The Slow Query Log:https://dev. ... -
The table is full问题解决过程
2017-05-06 15:29 7272The table‘xxxx’is full 设置临时表大小 ... -
百万级数据-程序迁移后续
2017-04-13 18:09 1582百万级数据-程序迁移:http://donald-draper ... -
Msyql日期字符串转换
2017-04-01 14:13 497Date和String的互相转换:http://www.tui ... -
Mysql添加约束
2017-03-31 16:28 857MySQL中对三种约束的支持:http://leekai.me ... -
Mysql FEDERATED引擎
2016-11-29 15:51 568使用mysql federated引擎构建MySQL分布式数据 ... -
MySQL触发器
2016-11-24 19:04 672CHANGE MASTER:http://dev.mysql. ... -
Mysql主从配置
2016-11-11 18:31 4841、主从服务器分别作以下操作: 1)版本一致 2)初始 ... -
百万级数据-程序迁移
2016-09-29 19:03 2570JVM学习笔记:http://blog.csdn.net/cu ... -
Mysql 备份工具XtraBackup增量备份
2016-08-05 18:11 683安装:http://donald-draper.iteye.c ... -
Mysql 备份工具XtraBackup全量备份
2016-08-05 16:41 521Percona安装:http://donald-draper. ... -
Mysql 备份工具XtraBackup 安装
2016-08-05 16:28 903开源热备工具XtraBackup下载:https://www. ... -
sysbench基准测试
2016-08-01 17:45 722下载sysbench:http://dev.mysql.com ... -
mysql 事务处理
2016-07-29 16:07 477创建表: CREATE TABLE `role` ( ` ... -
MySQL 物理文件的迁移
2016-07-26 15:39 2280参考资料:http://www.cnblogs.com/adv ... -
centos7 安装mysql
2016-07-26 11:36 707下载MYSQL-RPM包:http://downloads.m ... -
mysql 大表添加索引注意事项
2016-07-25 16:01 2583LINXU top命令: http://www.c ... -
mysql 大表分页查询测试分析优化
2016-07-25 11:30 1463索引概念: http://blog.csdn.net/xlur ... -
MySQL事务
2016-06-01 10:49 574事务基础知识:http://my.oschina.net/je ...
相关推荐
MySQL全文索引应用简明教程.pdf
MySQL全文索引应用简明教程[参考].pdf
主要介绍了MySQL全文索引、联合索引、like查询、json查询速度大比拼,通过实例代码截图的形式给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
前言:本文简单讲述全文索引的应用实例,MYSQL演示版本5.5.24。 Q:全文索引适用于什么场合? A:全文索引是目前实现大数据搜索的关键技术。 至于更详细的介绍请自行百度,本文不再阐述。...# MySQL全文索引
本文从以下几个方面介绍下MySQL全文索引的基础知识: MySQL全文索引的几个注意事项 全文索引的语法 几种搜索类型的简介 几种搜索类型的实例 全文索引的几个注意事项 搜索必须在类型为fulltext的索引列上,match...
主要给大家介绍了关于MySQL全文索引实现简单版搜索引擎的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用MySQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
mysql全文索引解决方案的最佳选择,coreseek是针对中文分词专门开发结合sphinx全文索引的数据库第三方工具。
php unicode 工具类 博文链接:https://alxw4616.iteye.com/blog/1886618
在MySql数据库中,有四种索引:聚焦索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FUNLLTEXT INDEX)。 全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术...
Solr3.6用DIH组件进行MySQL数据库全文索引部署包 完整的工程部署包 apache-solr-3.6.0.xml 放入apache-tomcat-7.0.27\conf\Catalina\localhost
③、标准插件式:以MySQL 5.1全文索引的标准插件形式开发,不修改MySQL源代码,不影响MySQL的其他功能,可快速跟进MySQL新版本; ④、支持版本多:支持所有的MySQL 5.1 Release Candidate版本,即MySQL 5.1.22 ...
Laravel开发-mysql-fulltext-laravel 让Laravel优雅地创建MySQL全文索引。
添加FULLTEXT(全文索引) 添加多列索引 ) mysql>ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, 、where条件列 、排序列或者分组列 、主键本身就是索引,无需再次...
建立全文索引的表的存储引擎类型必须为MyISAM 问题是match against对中文模糊搜索支持不是太好 新建一个utf8 MyISAM类型的表并建立一个全文索引 : CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT ...
一、前言 今天一个同事问我,如何使用 ...在 MySQL 5.7.6 版本之前,全文索引只支持英文全文索引,不支持中文全文索引,需要利用分词器把中文段落预处理拆分成单词,然后存入数据库。本篇文章测试的时候,采用的 Mysql