模糊查询优化 Flink+ES
公司需要对商品名称进行模糊模糊查询，考虑到商品表存量数据千万级，直接数据库模糊查询效率肯定极其低下，所以选择使用ElasticSearch对商品信息进行模糊查询。
因为需要代替原有的查询接口，保持原有查询接口的入参出参，所以需要全量+增量同步MySQL数据到ES进行索引创建。方案选择使用Flink进行全量+增量同步。
原有方案是通过订阅Kafka的topic(现成的，结合debezium监听binlog日志)+Flink CDC 实现，但是这只能针对增量数据进行监听。虽然修改debezium的参数可以达到全量后增量的效果，但因为有其他系统监听这个topic，不得不放弃该方案，只使用该方案做增量数据同步。
Flink CDC
因为一开始对Flink CDC不太熟悉，所以一直认为CDC只能用于增量查询，并且我修改MySQL数据源startOption通过调试，也只会获取最近的增量数据，或者说数据库目前存的最早的binlog（binlog一般是会定期清理的）。
MySqlSource<String> source = MySqlSource.<String>builder()
.hostname(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "hostname"))
.serverTimeZone("GMT+8")
.port(Integer.parseInt(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "port")))
.username(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "username"))
.password(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "password"))
.tableList(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "tableList"))
.databaseList(FlinkParameterToolUtils.getParameterValue(env, NACOS_MYSQL_CONFIG_PREFIX + "databaseList"))
.deserializer(jdd)
.startupOptions(StartupOptions.initial())
.build();
不一定会有这种情况，如果有可以加上debezium的参数试试：
就算我修改为
StartupOptions.initial()依旧没办法获取所有的历史数据。后面请教前辈才知道还需要添加debezium的参数。可以实现先全量再增量，基于debezium。
全量数据会对数据当前状态进行快照，并划分为多个chunk进行数据源同步，增量则是监听binlog日志。
//设置debezium属性
Properties properties = new Properties();
//启动增量快照读取
/**
* 在执行增量快照读取时，MySQL CDC source 需要一个用于分片的的算法。 MySQL CDC Source 使用主键列将表划分为多个分片（chunk）。 默认情况下，MySQL CDC source 会识别表的主键列，并使用主键中的第一列作为用作分片列。 如果表中没有主键， 增量快照读取将失败，你可以禁用 scan.incremental.snapshot.enabled 来回退到旧的快照读取机制。
* */
properties.setProperty("scan.incremental.snapshot.enabled", "true");
//先全量再增量
properties.setProperty("scan.startup.mode", "initial");
//将数字类型转为string，不然读出来数字值有问题
properties.setProperty("decimal.handling.mode", "string");
MySqlSource<String> source = MySqlSource.<String>builder()
//...
.debeziumProperties(properties)
.build();
但是考虑到如果flink重启，或者flink对该任务资源分配的问题导致该任务报错重启那么又会重新执行该任务，又重新全量跑数据的风险。该任务在跑完全量后还是得先cancel掉，再使用监听kafka的方式进行数据增量同步。（这里解释有问题，后续还是使用的CDC的方案，因为flink有一个savepoint的机制不仅可以防止重启后重复全量同步还可以动态新增待监听的数据表）
动态加表
官网介绍：MySQL CDC 连接器 — CDC Connectors for Apache Flink® documentation (ververica.github.io)
可以不使用命令的方式完成
-
停止job时不能直接在dashboard使用cancel，应该调用rest api的方式停止Job，此时会将当前job打一个快照（savepoint），用于后续重启应用时恢复。
curl -X POST -u username:password http://host:port/xxx/jobs/"JOBID"/stop -
执行完成后，flink会自动savepoint并结束任务，在dashboard的CheckPoint栏->OverView->Latest Savepoint可以查看到savepoint保存的地址
-
在创建source时需要加上配置，才能扫描新表，不然没用。
MySqlSource<String> source = MySqlSource.<String>builder() ... .scanNewlyAddedTableEnabled(true); -
修改完nacos配置后，重新submit job，注意重新提交时需要附上savepoint的地址。
Flink JDBC
因为一开始，并不知道需要调整debezium的参数达到全量同步，所以考虑自定义source，分页查询数据表的方式达到效果。只看下优化后的吧。
/**
* 需要继承RichSourceFunction<String>
* 代码采用了build创建的方式，方便写入一些nacos的配置项，提高可读性
*
**/
public class MySQLSourceFunction extends RichSourceFunction<String> {
private DruidDataSource dataSource;
private String username;
private String password;
private String drivername;
private String dbURL;
private String queryTemplate;
// 动态开始Id（始终表示当前查询最小ID），每次SQL循环会更新
private long startId = 0L;
private String[] params;
private String[] paramsType;
private volatile Queue<String> buffer;
private ExecutorService executorService;
private volatile boolean isRunning = true;
public static MySQLSourceFunction.MysqlSourceFunctionBuilder buildMySQLSourceFunction() {
return new MySQLSourceFunction.MysqlSourceFunctionBuilder();
}
/**
* open方法进行一些参数的初始化
* 包括数据库链接、数据缓存等
**/
@Override
public void open(Configuration parameters) throws Exception {
super.open(parameters);
Properties properties = new Properties();
properties.setProperty("url", dbURL);
properties.setProperty("username", username);
properties.setProperty("password", password);
properties.setProperty("initialSize", "5");
properties.setProperty("maxActive", "10");
// 这里设置了数据库连接池
this.dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
this.buffer = new ConcurrentLinkedQueue<>();
this.executorService = Executors.newSingleThreadExecutor();
}
@Override
public void run(SourceContext<String> sourceContext) {
executorService.execute(() -> {
while (isRunning) {
if (buffer.isEmpty()) {
try {
generateSourceData();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
});
while (isRunning) {
while (!buffer.isEmpty()) {
sourceContext.collect(buffer.poll());
}
}
}
private void generateSourceData() throws SQLException {
// 执行SQL语句返回结果集
try (DruidPooledConnection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(queryTemplate)) {
generateParams(ps);
// 启用流式查询
ps.setFetchSize(Integer.MIN_VALUE);
ResultSet resultSet = ps.executeQuery();
log.info("当前执行到最大ID为{}", startId);
// 获取ResultSet对象的列的数量、类型和属性。
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
int rowCount = 0;
JSONObject jsonObject = new JSONObject();
while (resultSet.next()) {
jsonObject.clear();
rowCount++;
for (int i = 1; i <= columnCount; i++) {
jsonObject.put(md.getColumnLabel(i), resultSet.getObject(i));
// 动态计算startId
if (md.getColumnLabel(i).equals("id")) {
startId = Math.max(resultSet.getLong(i), startId);
}
}
buffer.offer(JSONObject.toJSONString(jsonObject.toJavaObject(GoodsMp.class)));
}
if (rowCount == 0 && buffer.isEmpty()) {
this.isRunning = false;
}
}
}
/**
* 插入SQL参数
*
* @param ps PreparedStatement
*/
private void generateParams(PreparedStatement ps) throws SQLException {
// 插入参数
for (int i = 0; i < params.length; i++) {
String paramType = paramsType[i];
String param = params[i];
if (paramType.startsWith("startId:")) {
// 配置了动态开始ID
paramType = paramType.split(":", 2)[1];
if (startId > 0L) {
param = String.valueOf(startId);
}
}
// 参数类型判断
if (ParamsTypeEnum.Int.getType().equals(paramType.toLowerCase())) {
ps.setInt(i + 1, Integer.parseInt(param));
} else if (ParamsTypeEnum.Long.getType().equals(paramType.toLowerCase())) {
ps.setLong(i + 1, Long.parseLong(param));
} else if (ParamsTypeEnum.String.getType().equals(paramType.toLowerCase())) {
ps.setString(i + 1, param);
}
}
}
@Override
public void cancel() {
}
@Override
public void close() throws Exception {
super.close();
this.isRunning = false;
if (dataSource != null) {
//关闭连接和释放资源
dataSource.close();
}
}
public static class MysqlSourceFunctionBuilder {
private final MySQLSourceFunction source = new MySQLSourceFunction();
public MysqlSourceFunctionBuilder() {
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setUsername(String username) {
this.source.username = username;
return this;
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setPassword(String password) {
this.source.password = password;
return this;
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setDrivername(String drivername) {
this.source.drivername = drivername;
return this;
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setDBUrl(String dbURL) {
this.source.dbURL = dbURL;
return this;
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setQuery(String query) {
this.source.queryTemplate = query;
return this;
}
public MySQLSourceFunction.MysqlSourceFunctionBuilder setParams(String params) {
this.source.params = params.split(",");
return this;
}
public MysqlSourceFunctionBuilder setParamsType(String parameterValue) {
this.source.paramsType = parameterValue.split(",");
return this;
}
public MySQLSourceFunction finish() {
if (this.source.username == null) {
log.info("Username was not supplied separately.");
}
if (this.source.password == null) {
log.info("Password was not supplied separately.");
}
if (this.source.dbURL == null) {
throw new IllegalArgumentException("No database URL supplied");
} else if (this.source.queryTemplate == null) {
throw new IllegalArgumentException("No query supplied");
} else if (this.source.drivername == null) {
throw new IllegalArgumentException("No driver supplied");
} else {
return this.source;
}
}
}
}
优化点1：分页查询优化
分页查询
select * from goods order by id limit offset,size
一开始我是使用的这种sql去循环查询数据，但是发现随着分页数量的增大，上述sql的查询速度越慢，千万级别的数据表到后续的查询速度就不可恭维了。虽然id是主键。但是随着分页数量的增大依旧很慢，比如offset到了200万那么查询速度就已经到了10几秒了。
以为数据库会先order by ，然后再遍历前offset条数据，再取offset 后 数量（size）的数据，也就是说如果是 limit 200000,1000 数据库会遍历200000条数据，这性能就很慢了。
那么如何优化呢？首先我们知道id主键是聚簇索引，MySQL InnoDB对于聚簇索引使用的是B+树，叶子节点存的是整行的数据，所以在跳过offset时，数据库需要处理大量结果集 selcet * 消耗内存等系统资源，所以可以如下优化。
select * from goods a inner join
(select id from goods order by id limit 2000000,1000) b on a.id=b.id;
虽然说查询性能 select id from goods order by id limit 2000000,1000 与select * from goods order by id limit 2000000,1000是一致的但是返回的结果集的大小不一致，可能与业务有点特殊，该表字段较多，导致结果集很大，所以在跳过offset的时消耗系统资源较多，会导致查询速度远慢于第二个sql语句。
这个查询速度差不多得2秒左右，已经优化很多，但还能不能优化呢？
可以看到前面分页查询的主要性能缺陷在于，需要遍历前offset的数据，随之分页数量增大那么查询性能越慢。那么如果我可以不让他遍历前offset的数据，不就加大了查询速度嘛。下面看看第三条SQL
select * from goods where id>=xxx order by id desc limit 1000;
这条sql直接到了毫秒级的查询速度。因为id是有索引的，只要我知道之前的查询最大的id，那么我就可以当成where条件防止遍历前offset的数据导致的查询效率慢的问题。
我这里的业务是全量遍历所以很容易记录上一次查询到的id，如上述代码的startId。
优化点2：数据库链接优化
查询速度解决了，flink的source开始嗖嗖跑。一开始并没有使用连接池，而是使用的DriverManager创建数据库链接，并且循环查询时ps一直没有重新获取，导致，在flink运行过程中当数据量达到300万条时，JVM的task heap空间就已经达到2G，无法再进行同步，job宕机重启。
并且我是逮着一个PreparedStatement ps一直用。相当于我每次循环都不会新建链接也不会重新获取prepareStatement ，resultSet也没有关闭。这个问题查了很久。
最后通过工具，jprofiler实时监控本地运行flink发现，存在大量的buffer未被GC掉，根据溯源发现是查询到的结果集导致的。所以想到应该是 ps及rs循环没有关闭导致，结果集与其存在引用关系，很难GC掉，导致随着数据查询的增多，JVM内存占用空间加大。
所以最后使用了 数据库连接池以及每次重新获取ps的方式来断掉之前处理过的结果集的引用关系让JVM GC掉才解决内存不断增长，job自动关闭的问题。
// 使用try限制 connection与ps的作用域
try (DruidPooledConnection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(queryTemplate)) {
generateParams(ps);
// 启用流式查询，提高性能
ps.setFetchSize(Integer.MIN_VALUE);
}
优化点3：合理使用Flink反压机制
flink的反压机制可以看看这个文档Flink详解系列之九–反压机制和处理 - 知乎 (zhihu.com)
总的来说反压是在实时数据处理中，数据管道某个节点上游产生数据的速度大于该节点处理数据速度的一种现象。反压会从该节点向上游传递，一直到数据源，并降低数据源的摄入速度。
在自定义数据源中，主要发送数据源信息的代码在
sourceContext.collect(buffer.poll()); //buffer是本地查询到的结果集缓存
可以说，当下游数据处理赶不上上有数据传输时，flink会自动限制该方法collect的速度，通过阻塞的方式限制流数据向下游传输。
因为我们上述优化优化了sql的查询速度，所以导致sql查询速度很快，而es的写是很消耗性能的（空间换时间）所以明显下游es写索引的处理速度不及上游数据库查询的速度，造成反压，可能会造成数据源内存堆积从而导致OOM。优化如下：
// open
this.buffer = new ConcurrentLinkedQueue<>();
// run
executorService.execute(() -> {
while (isRunning) {
if (buffer.isEmpty()) {
try {
generateSourceData();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
});
while (isRunning) {
while (!buffer.isEmpty()) {
sourceContext.collect(buffer.poll());
}
}
起一个子线程进行生成数据存在缓存队列中，限制当buffer消耗完成，也就是下游正常接收时才进入循环查询数据。这样当collect无法请求传输数据时（反压），就会减缓对buffer数据的collect，而buffer会等到消耗完成才会进行新的查询，这样就解决了上下游处理时间不一致的情况。
ElasticSearch
当所有数据都跑到es了，就要开始写查询es的接口了。
我这里有两个字段需要进行模糊查询，一个是名称，一个是编号。名称是中文，编号是数字。而es无法对非text类型进行分词，所以在创建索引是将编号字段改为了text类型并使用ngram定义了分词器,中文使用ik分词。
searchInfo.getParams().forEach((key, value) -> {
if (wildcardQueryParams.contains(key)) {
wildcardQueries.put(key, String.valueOf(value));
} else {
matchQueries.put(key, String.valueOf(value));
}
});
发现这种查询并不算快，400ms左右。考虑es是否能像MySQL一样，比如先精准查询再模糊查询？
答案是肯定的。
首先先摒弃matchQuery作为精准查询的方法，改为使用termQuery，因为matchQuery会使用分析器对查询文本进行分析，生成相应的词项。而termQuery不会进行任何分析，直接在倒排索引中查找完全匹配的词项。
同时termQuery既可以做过滤条件、也可以做查询条件，那么有什么区别？应该用什么？
- 过滤条件（filter context）
- 用于精确匹配的情况，如数字、日期或布尔值等。
- 不计算分数，只关注文档是否匹配。
- 性能非常高，因为Elasticsearch可以直接从倒排索引中查找匹配的文档。
- 查询条件（query context）
- 用于全文搜索和模糊匹配的情况。
- 计算分数，并考虑相关性因素。
- 执行速度通常比过滤条件慢，因为它需要进行额外的操作。
根据分析，我们业务使用过滤条件更合理。然后就执行先后顺序的问题，理论上说先执行精准查询再执行模糊查询的效率更高
// es查询
HashMap<String, String> wildcardQueries = new HashMap<>();
HashMap<String, String> termQueries = new HashMap<>();
searchInfo.getParams().forEach((key, value) -> {
if (wildcardQueryParams.contains(key)) {
wildcardQueries.put(key, String.valueOf(value));
} else {
termQueries.put(key, String.valueOf(value));
}
});
// 先添加term过滤查询
termQueries.forEach((key, value) -> queryBuilder.filter(QueryBuilders.termQuery(key, value)));
// 再添加wildcard查询
wildcardQueries.forEach((key, value) -> queryBuilder.must(QueryBuilders.wildcardQuery(key, value)));
现在基本上就维持在80ms左右，还是优化了很多。
es hits total值问题
在分页查询时获取到的hits是有数据的但是总数为0
这个是个官方的bug，ES_记一次分页查询(getHits().getTotalHits() 获取总条目)为0的问题-CSDN博客
需要升级client版本至6.8.4，我的版本是7.x还是有这个问题，后续发现RequestConverters这个类我全局搜有两个，发现我有两个版本的client，那么答案呼之欲出了，把不需要的版本去掉就行。
总数大于10000时与数据库总数对不上
当我查询时发现，我数据库查询同样的条件总数为83000条，但是es查询出来是10000条，很明显不对。
原因：es 7.x版本后对分页查询做了限制当文档数大于10000时，只显示10000。
解决方案：查询时加上参数track_total_hits:true
searchSourceBuilder.trackTotalHits(true);
至此，结束。
扫一扫
596
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
