搜索框的数据库实现思路和分析
最近遇到了一个问题：搜索框怎么实现？具体来说，数据库查询层面怎么实现？恰好最近在做的项目中也有这个功能，于是进行了一些探索和尝试。
功能分析
搜索框一般是对一个或多个字段进行模糊/精确匹配的一个功能。最简单的搜索框是对一个字段进行精确匹配，直接WHERE然后**=**就行了，要实现模糊匹配，可以把=换成LIKE，然后把条件加上通配符%。这个方法同样适用于下拉框（性质和搜索是一样的，只是前端已经预设好查询的内容）。
比较复杂但也是比较常见的搜索框，一般是多个字段的模糊/精确匹配。这篇文章主要讨论的就是这种比较复杂的搜索框。从一个字段延伸到多个字段，最显而易见的办法就是用OR把各个字段连接起来，用=/LIKE作判断。
（笨方法）想当然法
思路
想当然法是单个搜索框的延伸，也是我最开始使用的方法。用一个表（可以把它叫做搜索表），把需要搜索的信息（字段）全部放在一起。查询的时候用=/LIKE作判断，用OR把各个字段连接起来。
实现
SQL
SELECT * FROM studentWHEREname LIKE '%2018100%' ORstudent_number LIKE '%2018100%' ORid_card LIKE '%2018100%' ORphone LIKE '%2018100%' ORdorm LIKE '%2018100%'
Java
（最后加了一个LIMIT做分页）
public String search(String content, String college, int page, int pageSize) { String sql = "SELECT name,student_number,college,major FROM student_info_2019"; if (!"".equals(college) || !"".equals(content)) { sql += " WHERE"; } if (!"".equals(college)) { sql += " college = '" + college + "'"; if (!"".equals(content)) { sql += " AND"; } } if (!"".equals(content)) { sql += " name LIKE '" + content + "' OR student_number = '" + content + "' OR id_card = '" + content + "' OR phone = '" + content + "' OR dorm = '" + content + "'"; } sql += " LIMIT " + page * pageSize + " ," + pageSize + ";"; return sql;}
分析
先看看性能。
（以下测试使用的都是2w行的学生信息数据，查询时间均使用三次取平均的方式计算。）
查询时间是可接受的范围。但是有一个问题，这个表不符合第一范式，也就是说有太多冗余的字段。
那么是不是还可以其他的表按照第一范式设计，但专门开设一个搜索表，用来存储搜索的信息呢？（甚至还有一个好处，输出的时候不需要再对字段进行处理）所以来到第二个办法。
（笨方法改进）独立设搜索表
思路
专门开设一个搜索表来存储要搜索的信息，但需要与其他几个对应表保持同步。这里同步可以在对其他几个对应的表进行修改时同时修改搜索表中的内容（再执行一次查询）也可以通过触发器来保持同步。
因为这种方法实在太笨而且繁琐，就不实现了。
分析
这个方法会有严重的性能问题（尤其是高并发的时候），最致命的地方就是需要多一次查询来进行同步。同时这个表是完完全全的冗余，数据量大时存储空间的开销十分可观。
另外，在查询时，如果字段数量很多，可能需要写很多的OR，另外需要给所有这些字段建立索引。这时候考虑到，可以引进全文搜索。
（使用全文检索）独立设搜索表
思路
通过恰当地建立索引，可能可以有效提高查询的效率。
实现
需要注意的是，5.7之前的mysql只有MyISAM引擎可以使用全文检索，而MyISAM引擎不支持事务（transaction）；5.7之后的mysql的Innodb引擎也可以使用全文检索了。
执行查询前需要添加索引，字段选择需要搜索的字段，索引类型选择FULLTEXT。
SELECT * FROM studentWHERE MATCH (name,student_number,id_card,phone,dorm) AGAINST ('2018100xxxx')
（敏感信息打码处理）
分析
性能上确实比前面的方法有所提升（事实上是所有方法中性能最高的）。
然而这个方法最后实现的是伪模糊查询，只能实现字段的模糊，不能实现搜索内容的模糊（即只能对搜索内容进行精确匹配，如2018100abcd精确匹配2018100abcd而不能通过2018100匹配到所有的2018100xxxx）。
还有另外一个致命问题是，全文检索对中文的支持不是太好，会出现问题。如果有搜索中文的需求，全文检索不太适合。
（最终采用）无痛法
思路
经过前面那些改进，基本确定了一个完美（伪）的方法必须满足下面的要求
-
满足第一范式（不能有冗余）
-
查询效率要高（尽量接近全文检索）
-
能实现（真）模糊查询
-
对中文支持要好
-
写起来要方便优雅（不需要写一大堆OR）
所以把这个表拆分成了两个，查询时使用连接，同时优化WHERE语句。
实现
SQL
SELECT * FROM student sJOIN student_info si ON s.student_number=si.student_numberWHERE CONCAT(s.name,s.student_number,phone,id_card) LIKE '%2018100%'
Java
public String search(String content, String college, int page, int pageSize) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM student s "); sql.append("JOIN student_info si ON s.student_number=si.student_number"); if (!"".equals(college) || !"".equals(content)) { sql.append(" WHERE"); } if (!"".equals(college)) { sql.append(" college='").append(college).append("'"); if (!"".equals(content)) { sql.append(" AND"); } } if (!"".equals(content)) { sql.append(" CONCAT(s.name,s.student_number,phone,id_card) LIKE '%").append(content).append("%'"); } sql.append(" LIMIT ").append(page * pageSize).append(" ,").append(pageSize).append(";"); return sql.toString(); }
ps. 这里用StringBuilder而不是String的原因是StringBuilder的性能比String高（String每次操作都会新建对象），但StringBuilder不是线程安全的，在多线程环境下应使用StringBuffer。
分析
还是先看看性能。（这里顺便与使用了CONCAT之后的“笨方法”比较）
| 方法 | 1 | 2 | 3 |
| 笨方法 | 0.065 | 0.065 | 0.066 |
| 新方法 | 0.084 | 0.089 | 0.086 |
竟然还是笨方法好？这里提出一个猜测，前面的一堆OR在实际执行查询的时候是被优化了，而使用CONCAT可能就是之前执行的优化，所以使用CONCAT之后对比OR有一点点的提升。
当然这个最终采用的方法不会产生冗余，查询性能相差也不大，所以才最终采用！
总结
虽然最终采用的是多表连接、优化WHERE的查询方法，但是单从性能上说，单表全文索引>单表模糊>多表连接。全文索引对中文支持差、不能实现真模糊、同样存在单独搜索表的问题，但性能明显翻倍。单独搜索表可以实现真模糊、性能也适中，但是冗余开销较大、需要解决同步问题。多表连接是最无痛的方法，但是性能偏低。但当前这个项目对高并发没有要求，也不要求很高的反应速度，因此使用了比较规范的无痛方法。