解决 ShardingSphere 第一次请求慢的喷血日子

落日映苍穹つ 2022-10-21 13:58 594阅读 0赞

学习起因：整合了 ShardingSphere 的项目第一次访问的时候很慢，第二次访问的时候速度就快了，然后老大让我去一探究竟，然后下面就是探索的血泪史。

![20210505110851470.png][]

通过 [Arthas][] 生成火焰图，定位到慢在 ShardingSphere sql 解析，第一次请求使用 ANTLR 解析 SQL 耗时较长，而第二次请求由于缓存原因耗时很小

curl -O https://arthas.aliyun.com/arthas-boot.jar
    java -jar arthas-boot.jar
    profiler start
    profiler stop

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70][]

相关源码：

private SQLStatement parse0(final String sql, final boolean useCache) {
            if (useCache) {
                Optional<SQLStatement> cachedSQLStatement = cache.getSQLStatement(sql);
                // 缓存存在一样的sql，就从缓存里取，sql 是还含有占位符 ？的 sql          
                if (cachedSQLStatement.isPresent()) {
                    return cachedSQLStatement.get();
                }
            }
            ParseTree parseTree = new SQLParserExecutor(databaseTypeName, sql).execute().getRootNode();
            SQLStatement result = (SQLStatement) ParseTreeVisitorFactory.newInstance(databaseTypeName, VisitorRule.valueOf(parseTree.getClass())).visit(parseTree);
            if (useCache) {
                cache.put(sql, result);
            }
            return result;
    }

神奇的现象：解析一条 sql 的平均时间为 500 ms，但在没有缓存的情况下，第二条 sql 的解析时间为 25 ms左右

long start = System.currentTimeMillis();
            String databaseType = "MySQL";
            SQLParserEngine parseEngine = SQLParserEngineFactory.getSQLParserEngine(databaseType);
            String sql = "select 'X'";
            parseEngine.parse(sql, true);
            System.out.println("第一次解析 sql 耗时：" + (System.currentTimeMillis() - start));
    
            long start2 = System.currentTimeMillis();
            SQLParserEngine parseEngine2 = SQLParserEngineFactory.getSQLParserEngine(databaseType);
            String sql2 = "select * from t_order_n where sharding_id=? and parent_order_no=?";
            parseEngine2.parse(sql2, true);
            System.out.println("第二次解析 sql 耗时：" + (System.currentTimeMillis() - start2));

![20210505123546988.png][]

充满好奇的我去剖析源码，然后发现 shardingJDBC 第一次解析 sql 主要慢在用工厂类创建单例对象，第二次解析时不用重新创建对象。

下面测试的代码是从 org.apache.shardingsphere.sql.parser.SQLParserEngine\#parse 中抽离出来的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1][]

解决方案1：项目一启动就提前用 Antrl 去解析一条公共的sql ：selec ‘x’，或者提前实例化相关对象

@PostConstruct 
    public void load() { 
    
        // 提前解析一条公共的 sql
        String databaseType = "MySQL"; 
        SQLParserEngine parseEngine = SQLParserEngineFactory.getSQLParserEngine(databaseType); 
        String sql = "select 'X'"; 
        parseEngine.parse(sql, true); 
    
        // 提前实例化相关对象
        SQLParser sqlParser = SQLParserFactory.newInstance(databaseType, sql);
        ParseASTNode execute = (ParseASTNode)sqlParser.parse();
        ParseTree parseTree = execute.getRootNode();
        ParseTreeVisitorFactory.newInstance(databaseType, VisitorRule.valueOf(parseTree.getClass()));
        
    }

解决方案2：预热 sql。启动过程中从存储捞取 SQL ( 当然项目初始上线必然为空 ) 进行 SQL 解析并使用反射填充缓存，而在容器销毁之前则同样通过反射将这个期间内收集到 SQL 保存作为后续的预加载。此外，考虑到平滑发布的必要性，启动过程加载及时抛错；同时只在启动和销毁的只执行一次，因此性能和并发并无问题。

@Configuration
    @ConditionalOnProperty(
            value = {"warmer.enable"},
            havingValue = "true"
    )
    public class WarmerConfiguration {
    
        public static String prefix;
    
        @Value("${spring.application.name}")
        public void setPrefix(String name) {
            prefix = name;
        }
    
        @Bean
        @ConditionalOnClass(RedissonClient.class)
        @ConditionalOnBean(RedissonClient.class)
        @ConditionalOnMissingBean(WarmerStorage.class)
        public WarmerStorage warmerStorage(RedissonClient redissonClient) {
            return new RedissonWarmerStorage(redissonClient);
        }
    
        @Bean
        @DependsOn("shardingDataSource")
        public Warmer4SQL warmer4SQL(WarmerStorage warmerStorage) {
            return new Warmer4SQL(warmerStorage);
        }
    
        // fixme 需要考虑 sql 的数量和长度问题
        @Slf4j
        public static class Warmer4SQL implements ApplicationContextAware, InitializingBean {
    
            private WarmerStorage warmerStorage;
    
            public Warmer4SQL(WarmerStorage warmerStorage) {
                this.warmerStorage = warmerStorage;
            }
    
            private static Map<String, SQLParseEngine> ENGINES;
            private static Field cacheInSQLParseEngine;
            private static Field cacheInSQLParseResultCache;
            private static volatile boolean init = false;
    
            static {
                try {
                    Field _ENGINES = SQLParseEngineFactory.class.getDeclaredField("ENGINES");
                    _ENGINES.setAccessible(true);
                    ENGINES = (Map<String, SQLParseEngine>) _ENGINES.get(null);
                    cacheInSQLParseEngine = SQLParseEngine.class.getDeclaredField("cache");
                    cacheInSQLParseEngine.setAccessible(true);
                    cacheInSQLParseResultCache = SQLParseResultCache.class.getDeclaredField("cache");
                    cacheInSQLParseResultCache.setAccessible(true);
                    classesInClassLoader = ClassLoader.class.getDeclaredField("classes");
                    classesInClassLoader.setAccessible(true);
                    init = true;
                } catch (Exception e) {
                    log.warn("cannot get fields because of !", e);
                }
            }
    
            @PostConstruct
            public void load() {
                if (!init) return;
                long start = System.currentTimeMillis();
                log.info("loading at {}", start);
                loadSQL();
                long end = System.currentTimeMillis();
                log.info("loaded at {}, consumed {} milliseconds", end, end - start);
            }
    
            private void loadSQL() {
                ENGINES.keySet().forEach(databaseTypeName -> {
                    Set<String> sqls = warmerStorage.load(databaseTypeName);
                    log.info("the amount of sql fetched from storage is {}", sqls.size());
                    sqls.forEach(sql -> ENGINES.get(databaseTypeName).parse(sql, true));
                    log.debug("the details of sql fetched from storage are {}", sqls);
                });
            }
    
            // 异常已由 postProcessBeforeDestruction 处理
            @PreDestroy
            public void save() {
                if (!init) return;
                long start = System.currentTimeMillis();
                log.info("saving at {}", start);
                saveSQL();
                long end = System.currentTimeMillis();
                log.info("saved at {}, consumed {} milliseconds", end, end - start);
            }
    
            private void saveSQL() {
                ENGINES.keySet().forEach(databaseTypeName -> {
                    Set<String> sqls = getParsedSQL(databaseTypeName);
                    log.info("the amount of sql going to be saved is {}", sqls.size());
                    warmerStorage.save(databaseTypeName, sqls);
                    log.debug("the details of sql going to be saved are {}", sqls);
                });
            }
    
            @SneakyThrows
            private Set<String> getParsedSQL(String databaseTypeName) {
                return ((Cache) cacheInSQLParseResultCache.get(cacheInSQLParseEngine.get(ENGINES.get(databaseTypeName)))).asMap().keySet();
            }     
           
        }
    
    }

在项目启动类加入上述代码后，发现第一次还是有点慢，重新生成火焰图，发现慢在了 sql 路由

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 2][]

解决方案：项目启动前执行一次完整的查询，即 sql解析，改写，路由，执行

@PostConstruct 
    public void load() throws Exception { 
            Connection connection = dataSource.getConnection();
            String sql = "select 'X'";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.execute();
    }

结果：从 678 ms 变成 379 ms

[20210505110851470.png]: /images/20221021/aa1e52c7a57640a584d1b3dd8b6d5bae.png
[Arthas]: https://arthas.gitee.io/install-detail.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70]: /images/20221021/dc7cc837660d4693a7906be7933a0509.png
[20210505123546988.png]: /images/20221021/f95ddf70cf7544cdaa79f6b0158a1d80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1]: /images/20221021/2bcc77cc4ae74d1a9caf6ba53804ed03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 2]: /images/20221021/7fc5f144d90b48b1be6889885da30d9e.png