用ELK分析每天4亿多条腾讯云MySQL审计日志(4)--MySQL全文索引

      前言：

       该文章将会介绍以下：

          1，MySQL全文索引的使用

          2，全文索引停止词STOPWORD

          3，使用全文索引的高效和准确

      最近事情比较少，刚好可以梳理一下以前的工作，做一下总结！

      在以前的解决方案中有介绍百万级如何使用MySQL全文索引方法，在没使用ELK前，线上13个实例的SQL日志都是手工导入的MySQL的innodb表中，

 建立全文索引来分析的。

      分析的SQL如下：   布尔文本搜索(IN BOOLEAN MODE )

SELECT count(*) 调用次数,user 账号,'crm1' 实例,'tab' 表名,'select' 操作,`sql` 样例SQL FROM cc WHERE 
MATCH(`sql`) AGAINST('+tab +select' IN BOOLEAN MODE) group by user

   因为13个实例数据量超过4亿，将这13个日志分成多个表,  如果表行数特别大会报错，需要调整参数innodb_ft_cache_size到最大值。

   同时查询出的结果会有少量的错误：

     这里的操作是select语句，但实际是insert，因为insert语句里包含有：select这个关键字，也被统计到。但如果如下查询有：     

MATCH(`sql`) AGAINST('+tab +select -from -for' IN BOOLEAN MODE) 

     这里的： -from -for  是无法生效的，因为from和for属于全文索引STOPWORD：   

SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

   

    如何解决这些少量的错误，想了一下，保持前缀的方式来判断，以前缀的操作为主：   

SELECT count(*) 调用次数,user 账号,'crm1' 实例,'tab' 表名,'select' 操作,`sql` 样例SQL FROM cc WHERE 
MATCH(`sql`) AGAINST('+tab +select' IN BOOLEAN MODE) and `sql` like concat('select','%') group by user

   该SQL的确能准确排除掉干扰，但是执行效率太慢了，以前几个小时能分析完的，使用该方法需要几十小时，一旦报错，又要几十小时，无法保证能分析完成

    有没有其他办法，即能准确又高效？

     想了想，既然2个都只满足其中一部分，不如2者结合起来使用， 怎么结合：

       方案1直接分析不变，分析完结果后，对结果表：ea_tj 再进行分析：      

select * from ea_tj where sqltext not like concat(op,'%') 

   查出这些异常的结果，对这些少量异常结果再进行：`sql` like concat('select','%')  分析：

    这样就解决 ：准确和高效

     MySQL全文索引方法分析，比数据放到ELK再分析有巨大性能差距。

     相关文档：

        用ELK分析每天4亿多条腾讯云MySQL审计日志(1)--解决过程

        用ELK分析每天4亿多条腾讯云MySQL审计日志(2)--EQL

        用ELK分析每天4亿多条腾讯云MySQL审计日志(3)--下载日志

        ELK查询和汇总