Skip to content

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

Open
Open
[Bug] 使用mysql8.0.32版本查询特别慢#799
Labels
Performance 性能

Description

@qimy1314524
qimy1314524
opened on Mar 5, 2025

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"}}]
>>>>>>>>>>>>>>>
Activity
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Metadata
Metadata
Assignees
No one assigned
    Labels
    Performance 性能
    Type
    No type
    Projects
    No projects
      Milestone
      No milestone
      Relationships
      None yet
      Development
      No branches or pull requests
      Issue actions