FindInSet性能优化

背景：最近在mysql性能优化中遇到一个findinset（）函数的性能问题：坦然说在数据量低的情况下，find_in_set的性能还是不错的，但是在30w左右开始严重劣化，到那时数据库结构已经定下来了，拆表的代价太大，而且带来很多冗余数据，找了很多方法还是不行，甚至我都开始考虑用ES来做了，但是自己搭一套ES集群实在麻烦，最终跑到隔壁组找了个专业dba，很快就解决了我的问题，看来专业的事情还是得交给专业的人来做哦！

findinset（）

先介绍一下findinset吧！

不少数据表设计的时候使用一个字段来存储多对多关系，比如：

表 user中有一个字段叫 category, category存储的是 "1,3,9" 这样的类型的数据，实际上是category的id 用逗号分隔开来的

 要查询一个用户属于id为2分类的用户可以这么写：

select * from `user` where find_in_set('2',`user`.`category`)；

具体find_in_set 的使用请参照手册：

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

虽然这样很好用，但问题是如果数据量大，又无法走索引，是很慢的。

取网友的一个例子：

user 表录入 100万的数据，同时建立 user_category 表，每个user有 3 个分类，那么category表里有300万条记录：

CREATE TABLE `user_category` (     `id` int(11) NOT NULL AUTO_INCREMENT,     `user_id` int(11) DEFAULT NULL,     `category_id` int(11) DEFAULT NULL,     PRIMARY KEY (`id`),     KEY `category_id` (`category_id`),     KEY `user_id` (`tax_id`)   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT
 现在比较一下在百万级的数据量上使用 join 链接外键查询和find_in_set查询的性能 

1. 使用 find_in_set 查询，平均时间在2.2秒左右

SELECT SQL_NO_CACHE COUNT(*) FROM `user` WHERE FIND_IN_SET(65,category)

2. 使用left join ， 使用了右表中的索引，平均时间在0.2秒左右

SELECT SQL_NO_CACHE COUNT(DISTINCT(`user`.id)) FROM `user`    LEFT JOIN `user_category` ON `user`.`id`= `user_category`.`user_id`   WHERE `user_category`.`category_id`=75

这是采用一种空间换时间的办法。但是如果实在项目后期，又无法改变表结构又该怎么办呢？

既然问题的核心在于：findinset函数无法走索引，那给他加上索引不久好了

那么，全文索引登场了。

全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的.

关于全文索引的详情请参考这个：https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

使用全文索引

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如:

select * from fulltext_test where match(content,tag) against('xxx xxx');

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

如：首先创建测试表，插入测试数据

create table test (
    id int(11) unsigned not null auto_increment,
    content text not null,
    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;
insert into test (content) values ('aaaa'),('bbbb'),('cccc');

按照全文索引的使用语法执行下面查询：
select * from test where match(content) against('aaa');

注意：

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

对比结果：

使用findinset需要10s左右，使用全文索引只需要0.3s左右