[Bug] 使用mysql8.0.32版本查询特别慢

[qimy1314524]

APIJSON Version/APIJSON 版本号

7.1.5

Database Type & Version/数据库类型及版本号

mysql8.0.32

Environment/环境信息

- JDK/基础库:17
- OS/系统: win11

APIAuto Screenshots/APIAuto 请求与结果完整截屏

在评论里

Current Behavior/问题描述

1、当使用mysql8.0.26或者mysql8.0.32对数据进行查询的时候 速度非常慢 单条数据查询将近10s，切换过另外一个mysql也是同样十分慢
使用pg进行查询 50ms左右
2、请求参数{
    "[]": {
        "count":1,
        "User": {}
    }
}
3、数据表使用样例提供的mysql数据表导入到数据库中，未修改
4、DemoSQLConfig配置 static {
		DEFAULT_DATABASE = DATABASE_MYSQL;  // TODO 默认数据库类型，改成你自己的
		DEFAULT_SCHEMA = "test";  // TODO 默认数据库名/模式，改成你自己的，默认情况是 MySQL: sys, PostgreSQL: public, SQL Server: dbo, Oracle:

        // 表名和数据库不一致的，需要配置映射关系。只使用 APIJSONORM 时才需要；
        // 如果用了 apijson-framework 且调用了 APIJSONApplication.init 则不需要
        // (间接调用 DemoVerifier.init 方法读取数据库 Access 表来替代手动输入配置)。
        // 但如果 Access 这张表的对外表名与数据库实际表名不一致，仍然需要这里注册。例如
        //		TABLE_KEY_MAP.put(Access.class.getSimpleName(), "access");

		//表名映射，隐藏真实表名，对安全要求很高的表可以这么做
		TABLE_KEY_MAP.put("User", "apijson_user");
		TABLE_KEY_MAP.put("Privacy", "apijson_privacy");
	}

	@Override
	public String getDBVersion() {
		return "8.0.32";  // "8.0.11";  // TODO 改成你自己的 MySQL 或 PostgreSQL 数据库版本号  // MYSQL 8 和 7 使用的 JDBC 配置不一样
	}
	
	@JSONField(serialize = false)  // 不在日志打印 账号/密码 等敏感信息
	@Override
	public String getDBUri() {
		return "jdbc:mysql://xx.xx.xx.xx:33060?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=GMT%2B8"; // TODO 改成你自己的，TiDB 可以当成 MySQL 使用，默认端口为 4000
		//return "jdbc:postgresql://192.168.120.176:5433/power_wind_basic?currentSchema=data&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=GMT%2B8\n"; // TODO 改成你自己的，TiDB 可以当成 MySQL 使用，默认端口为 4000

	}
	
	@JSONField(serialize = false)  // 不在日志打印 账号/密码 等敏感信息
	@Override
	public String getDBAccount() {
		return "root";  // TODO 改成你自己的
	}

Expected Behavior/期望结果

Any additional comments?/其它补充说明？

相关日志：

2025-03-05 06:40:15.352: AbstractParser.DEBUG: 



<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
GET/parseResponse  request = 
{
    "[]": {
        "count":1,
        "User": {}
    }
}


2025-03-05 06:40:15.352: AbstractParser.DEBUG: parseResponse  startTime = 1741171215352<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


 
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  parentPath = null; name = null; table = null; alias = null
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  type = 0; isTable = false; isArrayMainTable = false
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  isEmpty = false; tri = false; drop = false
2025-03-05 06:40:15.352: AbstractParser.DEBUG: onArrayParse  query = null; count = 1; page = null; join = null
2025-03-05 06:40:15.352: AbstractParser.DEBUG: onArrayParse  size = 1; page = 0
2025-03-05 06:40:15.352: AbstractParser.ERROR: onJoinParse  set == null || set.isEmpty() >> return null;
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  parentPath = []; name = 0; table = 0; alias = null
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  type = 1; isTable = false; isArrayMainTable = false
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  isEmpty = false; tri = false; drop = false
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  parentPath = []/0; name = User; table = User; alias = null
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  type = 2; isTable = true; isArrayMainTable = true
2025-03-05 06:40:15.352: AbstractObjectParser.DEBUG: AbstractObjectParser  isEmpty = true; tri = false; drop = false
2025-03-05 06:40:15.353: AbstractSQLExecutor.DEBUG: 
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
已生成 1 条 SQL
execute  startTime = 1741171215353
database = ; schema = ; sql = 
SELECT * FROM `test`.`apijson_user` LIMIT 1
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2025-03-05 06:40:15.352: AbstractParser.INFO: 
getObject:  parentPath = null;
 name = null; request = {"[]":{"count":1,"User":{}}}
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  path = null; name = null <<<<<<<<<<<<<
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  return  >>>>>>>>>>>>>>>>
2025-03-05 06:40:15.352: AbstractParser.INFO: 


 onArrayParse parentPath = ; name = []; request = {"count":1,"User":{}}
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  path = ; name = [] <<<<<<<<<<<<<
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  return [] >>>>>>>>>>>>>>>>
2025-03-05 06:40:15.352: AbstractParser.INFO: 
getObject:  parentPath = [];
 name = 0; request = {"User":{}}
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  path = []; name = 0 <<<<<<<<<<<<<
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  return []/0 >>>>>>>>>>>>>>>>
2025-03-05 06:40:15.352: AbstractParser.INFO: 
getObject:  parentPath = []/0;
 name = User; request = {}
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  path = []/0; name = User <<<<<<<<<<<<<
2025-03-05 06:40:15.352: AbstractParser.INFO: getPath  return []/0/User >>>>>>>>>>>>>>>>
2025-03-05 06:40:15.353: AbstractParser.INFO: onVerifyRole  config = {"aliasWithQuote":"`User`","cache":0,"cassandra":false,"clickHouse":false,"combineMap":{"&":[]},"configTable":false,"count":1,"dBVersion":"8.0.32","dBVersionNums":[8,0,32],"dameng":false,"databricks":false,"db2":false,"distinct":false,"elasticsearch":false,"explain":false,"fakeDelete":false,"hive":false,"idKey":"id","influxDB":false,"joinString":"","kafka":false,"keyPrefix":false,"kingBase":false,"mQ":false,"main":true,"mariaDB":false,"method":"GET","milvus":false,"mongoDB":false,"mySQL":true,"objectParser":{"arrayConfig":{"aliasWithQuote":"`null`","cache":0,"cassandra":false,"clickHouse":false,"combineMap":{"&":[]},"configTable":false,"count":1,"dBVersion":"8.0.32","dBVersionNums":[8,0,32],"dameng":false,"databricks":false,"db2":false,"distinct":false,"elasticsearch":false,"explain":false,"fakeDelete":false,"hive":false,"idKey":"id","influxDB":false,"joinString":"","kafka":false,"keyPrefix":false,"kingBase":false,"mQ":false,"main":true,"mariaDB":false,"method":"GET","milvus":false,"mongoDB":false,"mySQL":true,"oracle":false,"page":0,"position":0,"postgreSQL":false,"prepared":true,"preparedValueList":[],"presto":false,"query":0,"quote":"`","redis":false,"sQLDatabase":"MYSQL","sQLSchema":"test","sQLServer":false,"sQLite":false,"snowflake":false,"tDengine":false,"test":false,"tiDB":false,"trino":false,"type":2,"userIdKey":"userId","version":0,"withAsEnable":false,"withAsExprPreparedValueList":[]},"breakParse":false,"invalidate":false,"method":"GET","parentPath":"[]/0","parser":{"defaultQueryCount":10,"maxArrayCount":5,"maxObjectCount":5,"maxQueryCount":100,"maxQueryDepth":5,"maxQueryPage":100,"maxSQLCount":200,"maxUpdateCount":10,"method":"GET","minQueryPage":0,"needVerifyContent":false,"needVerifyLogin":false,"needVerifyRole":false,"request":{"[]":{"User":{}}},"root":true,"sQLExecutor":{"cachedSQLCount":0,"executedSQLCount":0,"executedSQLDuration":0,"generatedSQLCount":0,"parser":{"$ref":".."},"sqlResultDuration":0,"transactionIsolation":0},"session":{"attributeNames":[],"creationTime":1741171189036,"id":"3ADADAA6E48EC32C2AB8DA2F4C5E9A62","lastAccessedTime":1741171211423,"maxInactiveInterval":1800,"new":false,"servletContext":{"attributeNames":["org.apache.catalina.resources","org.apache.catalina.webappVersion","org.springframework.web.context.WebApplicationContext.ROOT","org.springframework.web.context.support.ServletContextScope","org.apache.tomcat.InstanceManager","org.apache.tomcat.util.threads.ScheduledThreadPoolExecutor","org.apache.catalina.jsp_classpath","jakarta.servlet.context.tempdir","jakarta.websocket.server.ServerContainer","org.apache.tomcat.JarScanner","org.springframework.web.servlet.FrameworkServlet.CONTEXT.dispatcherServlet"],"contextPath":"","defaultSessionTrackingModes":["COOKIE","URL"],"effectiveMajorVersion":3,"effectiveMinorVersion":0,"effectiveSessionTrackingModes":["COOKIE","URL"],"filterRegistrations":{"requestContextFilter":{"className":"org.springframework.boot.web.servlet.filter.OrderedRequestContextFilter","initParameters":{},"name":"requestContextFilter","servletNameMappings":[],"urlPatternMappings":["/*"]},"Tomcat WebSocket (JSR356) Filter":{"className":"org.apache.tomcat.websocket.server.WsFilter","initParameters":{},"name":"Tomcat WebSocket (JSR356) Filter","servletNameMappings":[],"urlPatternMappings":["/*"]},"characterEncodingFilter":{"className":"org.springframework.boot.web.servlet.filter.OrderedCharacterEncodingFilter","initParameters":{},"name":"characterEncodingFilter","servletNameMappings":[],"urlPatternMappings":["/*"]},"formContentFilter":{"className":"org.springframework.boot.web.servlet.filter.OrderedFormContentFilter","initParameters":{},"name":"formContentFilter","servletNameMappings":[],"urlPatternMappings":["/*"]}},"initParameterNames":[],"majorVersion":6,"minorVersion":0,"serverInfo":"Apache Tomcat/10.1.20","servletContextName":"application","servletRegistrations":{"dispatcherServlet":{"className":"org.springframework.web.servlet.DispatcherServlet","initParameters":{},"mappings":["/"],"name":"dispatcherServlet"}},"sessionCookieConfig":{"attributes":{},"httpOnly":false,"maxAge":-1,"secure":false},"sessionTimeout":30,"virtualServerName":"Tomcat/localhost"}},"startFrom1":false,"transactionIsolation":0,"verifier":{"visitor":{}},"version":0,"visitor":{"$ref":"$.objectParser.parser.verifier.visitor"}},"path":"[]/0/User","position":0,"response":{},"sQLConfig":{"$ref":".."},"sqlRequest":{},"table":"User"},"oracle":false,"page":0,"parser":{"$ref":"$.objectParser.parser"},"position":0,"postgreSQL":false,"prepared":true,"preparedValueList":[],"presto":false,"query":0,"quote":"`","redis":false,"sQLDatabase":"MYSQL","sQLSchema":"test","sQLServer":false,"sQLite":false,"snowflake":false,"tDengine":false,"table":"User","test":false,"tiDB":false,"trino":false,"type":0,"userIdKey":"id","version":0,"withAsEnable":false,"withAsExprPreparedValueList":[]}
2025-03-05 06:40:15.353: AbstractSQLExecutor.INFO: >>> execute  result = getCache('SELECT * FROM `test`.`apijson_user` LIMIT 1', 0) = null
2025-03-05 06:40:15.353: AbstractSQLExecutor.INFO: select  connection  = null
2025-03-05 06:40:24.874: AbstractSQLExecutor.DEBUG: 

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 execute while (rs.next()){  index = 0


2025-03-05 06:40:24.874: AbstractSQLExecutor.DEBUG: execute  while (rs.next()) { resultList.put( 0, result); 
 >>>>>>>>>>>>>>>>>>>>>>>>>>> 


2025-03-05 06:40:24.874: AbstractSQLExecutor.DEBUG: 

 execute  endTime = 1741171224874; duration = 9521
 return resultList.get(0);
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


2025-03-05 06:40:24.874: AbstractParser.DEBUG: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 


 已执行 1/200 条 SQL 


 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2025-03-05 06:40:24.874: AbstractParser.DEBUG: putQueryResult  queryResultMap.containsKey(valuePath) >> queryResultMap.put(path, result);
2025-03-05 06:40:24.874: AbstractParser.WARN: getChild  parent == null || pathKeys == null || pathKeys.length <= 0 >> return parent;
2025-03-05 06:40:24.874: AbstractParser.DEBUG: 
 onArrayParse <<<<<<<<<<<<<<<<<<<<<<<<<<<<
 isExtract >> putQueryResult  startTime = 1741171224874; endTime = 1741171224874; duration = 0
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2025-03-05 06:40:24.874: 

AbstractParser.DEBUG: <<<<<<<<<<<<<<<<<<<<<<< close >>>>>>>>>>>>>>>>>>>>>>> 





<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
2025-03-05 06:40:24.874: AbstractParser.DEBUG: GET/parseResponse  request = 
{"[]":{"count":1,"User":{}}}


2025-03-05 06:40:24.874: AbstractParser.DEBUG: GET/parseResponse return response = 
{"[]":[{"User":{"id":38710,"sex":0,"name":"TommyLemon","tag":"Android&Java","head":"https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","contactIdList":[82003,82005,90814,82004,82009,82002,82044,93793,70793],"pictureList":["https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","https://common.cnblogs.com/images/icon_weibo_24.png"],"date":"2017-02-01 19:21:50.0"}}],"ok":true,"code":200,"msg":"success","debug:info|help":" \n提 bug 请发请求和响应的【完整截屏】，没图的自行解决！ \n开发者有限的时间和精力主要放在【维护项目源码和文档】上！ \n【描述不详细】 或 【文档/常见问题 已有答案】 的问题可能会被忽略！！ \n【态度 不文明/不友善】的可能会被踢出群，问题也可能不予解答！！！ \n\n **环境信息**  \n系统: Windows 11 10.0 \n数据库: DEFAULT_DATABASE = MYSQL \nJDK: 17.0.10 amd64 \nAPIJSON: 7.1.0 \n   \n【常见问题】：https://github.com/Tencent/APIJSON/issues/36 \n【通用文档】：https://github.com/Tencent/APIJSON/blob/master/Document.md \n【视频教程】：https://search.bilibili.com/all?keyword=APIJSON","time":1741171224874,"sql:generate|cache|execute|maxExecute":"1|0|1|200","depth:count|max":"3|5","time:start|duration|end|parse|sql":"1741171215352|9522|1741171224874|1|9521"}


2025-03-05 06:40:24.874: AbstractParser.DEBUG: GET/parseResponse  endTime = 1741171224874;  duration = 9522
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



2025-03-05 06:40:24.874: AbstractSQLExecutor.INFO: >>> execute  putCache('SELECT * FROM `test`.`apijson_user` LIMIT 1', resultList);  resultList.size() = 1
2025-03-05 06:40:24.874: AbstractParser.INFO: 
 putQueryResult  valuePath = []/0/User; result = {"id":38710,"sex":0,"name":"TommyLemon","tag":"Android&Java","head":"https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","contactIdList":[82003,82005,90814,82004,82009,82002,82044,93793,70793],"pictureList":["https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","https://common.cnblogs.com/images/icon_weibo_24.png"],"date":"2017-02-01 19:21:50.0"}
 <<<<<<<<<<<<<<<<<<<<<<<
2025-03-05 06:40:24.874: AbstractParser.INFO: onArrayParse  return response = 
[{"User":{"id":38710,"sex":0,"name":"TommyLemon","tag":"Android&Java","head":"https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","contactIdList":[82003,82005,90814,82004,82009,82002,82044,93793,70793],"pictureList":["https://static.oschina.net/uploads/user/1218/2437072_100.jpg?t=1461076033000","https://common.cnblogs.com/images/icon_weibo_24.png"],"date":"2017-02-01 19:21:50.0"}}]
>>>>>>>>>>>>>>>

[qimy1314524]

[TommyLemon]

可能是第一次请求 MySQL 比较慢，再调用一次，还慢的话直接把 SQL 放数据库执行看看，如果慢就是 MySQL 数据库问题，不慢应该是 MySQL JDBC 问题

[qimy1314524]

1、mysql跑了很多次，每次稳定在9秒
"time:start|duration|end|parse|sql": "1741224595490|9613|1741224605103|7|9606"
time:start|duration|end|parse|sql": "1741224803711|9569|1741224813280|15|9554"
"time:start|duration|end|parse|sql": "1741224826295|9565|1741224835860|3|9562"
2、测试了mysql8.4 速度很快，只有第一次200ms左右，后续20ms左右。
3、测试了和mysql在同一台机器上面的pg,速度也非常快。
4、测试了3台mysql8.0.32、mysql8.0.26或者8.0.28的mysql 都是非常慢，直接运行sql非常快。

[qimy1314524]

mysql8.4版本：
"time:start|duration|end|parse|sql": "1741224941627|7|1741224941634|2|5"
"time:start|duration|end|parse|sql": "1741224966237|11|1741224966248|4|7"

[TommyLemon]

应该是 MySQL JDBC 问题，改下版本试试
https://mvnrepository.com/artifact/com.mysql/mysql-connector-j

https://mvnrepository.com/artifact/mysql/mysql-connector-java