2000W条数据,加入全文检索的总结

时间：2023-12-27 21:25:49

一) 前期准备测试:

  1. 旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
  2. 不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引，所以具体信息要随时关注官网，

  3. 下载mySql5.7

    1. https://www.baidu.com/link?url=khagDiqblcdIqVeGBc4wlqAemFd5LUgs9fSUba99s2sJuvxeqY3Y3S08HEpgJ0Zphhh7LxItOZ-_eBFJf8cgX5-fmCjeNGI8onXBKCGwWL7&wd=&eqid=ee8b4c100002ff7e0000000459faecde
  4. 直接使用 alter table testtable add fulltext index testfulltext(clumn1,clumn2),可检索数字,英文,测时检索中文失败

  5. 解决无法查询中文,修改配置文件

    1. my.ini
    2. [mysqld] ngramtokensize=2

  6. 建表同时加入全文检索

    CREATE TABLE articles ( id INTUNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT,FULLTEXT (title,body) WITH PARSER ngram ) ENGINE=InnoDB CHARACTER SET utf8mb4;

  7. 修改方式添加全文检索 alter table testtable add fulltext index testfulltext(clumn1,clumn2) with parser ngram;

  8. 新建方式添加全文检索 CREATE FULLTEXT INDEX ft_email_name ON student(name) with parser ngram;`
  9. 验证全文检索结果 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('关键词' IN NATURAL LANGUAGE MODE);
  10. 关键字不能太短,小于两个字无法匹配

二) 2000W数据实测:

  1. 数据库已经建好,数据已经插入,总数据量为1980W条数据

  2. 添加全文检索: CREATE FULLTEXT INDEX ft_pat_search ON pat_patent(`aa`,`ad`,`agc`,`an`,`pn`,`in`,`pa`,`ti`,`co`) with parser ngram;

  3. 查询: SELECT * FROM pat_patent WHERE MATCH (`aa`,`ad`,`agc`,`an`,`pn`,`in`,`pa`,`ti`,`co`) AGAINST ('三星' IN NATURAL LANGUAGE MODE);
  4. 结果用时183s,耗时太长
    1. 分析原因1:索引量过大导致过慢,尝试缩短检索字段(只选用一个字段)

    2. CREATE FULLTEXT INDEX ft_pat_search_ti ON pat_patent(`ti`) with parser ngram;

    3. 结果可明显提升检索速率,检索用时1.3s;
    4. 依旧不理想,继续分析原因,可能是查询结果数据量过大,传输受限,尝试加入分页

    5. SELECT * FROM pat_patent WHERE MATCH (`aa`,`ad`,`agc`,`an`,`pn`,`in`,`pa`,`ti`,`co`) AGAINST ('三星' IN NATURAL LANGUAGE MODE) LIMIT 10, 10;

    6. 查询结果0.5s,测试成功

三)总结:

  1. 版本更新至5.7,注意数据库路径选择在非C盘,负责会在添加全文检索时报错ERRO:1878
  2. 添加检索式加上 with parser ngram,配置文件加上ngramtokensize=2,可解决搜索中文问题
  3. 提高查询效率,可加入分页

相关文章