InnoDB大数据插入的优化和FULLTEXT索引性能优化的调研

FULLTEXT索引加在了vivo_tags字段上，关于FULLTEXT索引的介绍可以参考《MySQL技术内幕——InnoDB存储引擎》的P229~P248，也可以参考https://www.cnblogs.com/tommy-huang/p/4483684.html

在插入数据之前，我们首先看一下当前数据库所在的环境：

show global variables like 'innodb_buffer_pool_size'; #缓冲池总大小

show global variables like 'innodb_buffer_pool_instances'; #缓冲池的数量

可见测试环境music库缓冲池128M，缓冲池有8个。

缓冲池用来弥补CPU和磁盘IO之间的鸿沟，在写入数据的时候，首先修改缓冲池中的页（页是InnoDB存储引擎最小的管理单位），再以一定的频率刷新到磁盘上，而多个缓冲池可以减少数据库内部的资源竞争，增加数据库的并发处理能力，因此缓冲池总大小和缓冲池数量都是决定数据库处理性能的重要指标。这里可以扩大缓冲池size来提升插入速度，由于是公共的测试环境，所以这里不做修改。

1.2.2 innodb_flush_log_at_trx_commit 和 sync_binlog

innodb_flush_log_at_trx_commit ：redo日志刷新到磁盘的策略，默认为1，即每次事务提交，都刷入到磁盘中。这是安全系数最高的同步策略，但是在大量数据插入时，会因为频繁的磁盘IO操作，大大影响插入速度。

sync_binlog：二进制日志刷到磁盘的策略，默认是0，也就是不开启二进制日志，但在很多主从数据库架构中，这个参数都是大于0的。打开这个参数，也会大大影响插入速度。

1.3插入数据

写一个批量插入的存储过程：

这里手动开启了一个事务，手动控制每一万条插入数据提交一次。这是因为，在默认条件下，插入操作每进行一次，就自动提交一次，如果插入1000万条，就提交了1000万次，这无疑是很崩溃的。但是这个10000次插入操作提交一次，也没有经过严格验证，是否就是最好的选择，因为这和缓冲池大小有关系，如果插入10000条时，缓冲池已经满了，也会强制刷入磁盘中，而如果缓冲池太大，10000次又显得太少了。实际这边可以加一个记录表，记录每一次提交的成功与否情况，可以帮助我们实时监控插入操作进行到哪里进行得怎样了，毕竟1000万条的插入还是相当漫长的。

然后调用存储过程：

实测用了大概28分钟。

2.全文索引

当查询含有100008这个字段的信息时，用like查询：

用全文检索查询：

奇怪，怎么全文检索反而慢呢？

原因是，like查询前10条的时候，是采用全表扫描的方式，一直查找满10条的时候，就会返回数据。而回顾一下1.3中的存储过程，可以看到100008这个字段的命中率大约是33%，相当于，全表扫描到30条的时候，就已经拿到足够的数据返回了。

但是当查询第1000000到1000010条时，由于要扫描大约3百万条数据才能获得结果集，因此速度就非常慢了：

而反观全文检索的查询方式，由于采用的是倒排索引的实现方式，先是把所有100008的所有倒排索引列出来，然后在这个集合里面取前10个，这里100008的文档数超过300万，因此速度就比较慢了。

为了验证这个观点，我往t_song_info_test_test表中插入两条数据

此时，200008在1千万+2条数据中总共只出现了两次。此时，执行查询语句：

只花了0.047秒。

3.注意点

3.1全文检索的默认最小长度是4

长度小于4的字段，不会被记录到全文检索中，也就查不到结果

修改ft_min_word_len的值即可。

3.2分词并不智能

全文检索是根据空格符或者“,”这类的明显的分隔符字段来分词的。并不想elasticsearch那样支持中文分词，但是从 MySQL 5.7.6开始，内置的InnoDB支持自定义分词长度对中文进行分词，可以参考[ www.jianshu.com/p/c48106149… ](MySQL 5.7 中文全文检索使用教程)。