yflytom opened a new issue, #28045:
URL: https://github.com/apache/shardingsphere/issues/28045
version:
spirngboot 2.xx
shardingjdbc 5.1.1
mysql 5.7
--------
我发现在启动的时候加载TableMetaDataLoaderEngine
类的loadByDialect方法，当第二次调用loadByDialect方法时，会很慢，发现第二次慢的原因是：实现类MySQLTableMetaDataLoader类的load方法中，`Map<String,
Collection<ConstraintMetaData>> constraintMetaDataMap =
columnMetaDataMap.isEmpty() ? Collections.emptyMap() :
loadConstraintMetaDataMap(dataSource,
columnMetaDataMap.keySet());`这个方法的查询sql很慢，sql是：`SELECT CONSTRAINT_NAME,
TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME IN ('xxxxx') AND REFERENCED_TABLE_SCHEMA IS NOT
NULL`，造成慢的原因我认为是KEY_COLUMN_USAGE
表数据过多，后来查看文章发现可以指定TABLE_SCHEMA就会快很多，那么是否可以优化下这个查询的sql逻辑，指定查询的库名，我目前在生产环境中库比较多，所有表加起来有上万左右，不指定库名需要查询5分钟左右，如果指定库名就会快�
�多几秒左右，下面是代码截图，验证效果由于是生产环境就不截图了！
I found that when the loadByDialect method of the TableMetaDataLoaderEngine
class is loaded at startup, when the loadByDialect method is called for the
second time, it will be very slow. The reason for the second slowness is: in
the load method of the MySQLTableMetaDataLoader class, `Map<String, Collection
<ConstraintMetaData>> constraintMetaDataMap = columnMetaDataMap.isEmpty() ?
Collections.emptyMap() : loadConstraintMetaDataMap(dataSource,
columnMetaDataMap.keySet());`The query sql of this method is very slow, the sql
is: `SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME F ROM
information_schema .KEY_COLUMN_USAGE WHERE TABLE_NAME IN ('xxxxx') AND
REFERENCED_TABLE_SCHEMA IS NOT NULL`, I think the reason for the slowness is
that there is too much data in the KEY_COLUMN_USAGE table. Later, I checked the
article and found that specifying TABLE_SCHEMA will be much faster. So can this
query be optimized? Sql logic, specify the name of the query library. I
currently have a lo
t of libraries in the production environment. All the tables add up to tens of
thousands. If you don’t specify the library name, it takes about 5 minutes to
query. If you specify the library name, it will be much faster for about a few
seconds. The following is a screenshot of the code. The verification effect is
not taken because it is a production environment!
May I ask how should I modify now to achieve the desired effect?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
