计算列+全文检索优化LIKE查询性能

计算列+全文检索优化LIKE查询性能

问题起因

众所周知mysql的like语句是不会走索引的，网上有文章说%在右边就会走索引，实际不会。在我们给印刷厂做设计接单的过程中发现一个问题，他们经常用文件名查询的方式进行模糊查询，也就是用 like ‘%关键字%’，当数据量过大时，查询会很慢，因为不走索引，会走全表扫描。

解决过程

1. 最开始我想到了在file_name中创建一个全文检索的索引，然后用

MATCH(file_name) AGAINST ( CONCAT('*',#{fileName,jdbcType=VARCHAR},'*')  in boolean mode ) 

进行查询，但是当查询中文，或者一些组合词语时，实际达不到like的查询效果。比如说一些商家的店名，是查询不出记录的
2. 在查询了一些相关资料后，mysql的base64函数给我了启发，我想是不是可以用base64做为查询条件呢，那么就要将文件名称转换成base64，这个用计算列就可以实现，然后在计算列创建一个全文检索的索引SQL代码如下，(数据量在执行速度会有点慢)

ALTER TABLE `pfs_live`.`tb_order` 
ADD COLUMN `file_name_base64` text CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS (to_base64(`file_name`)) STORED NULL AFTER `update_time`,
ADD FULLTEXT INDEX `fidx_tb_order_file_name_base64`(`file_name_base64`);

4. 再将mybatis的sql改成如下

MATCH(file_name_base64) AGAINST ( CONCAT('*',to_base64(#{fileName,jdbcType=VARCHAR}),'*')  in boolean mode ) 

总结

1. 数据库版本应该在5.7以上，因为5.7后才会有计算列。
2. 注意要使用in boolean mode，并且关键字要前后拼* 号