问题描述
我在典型的共享托管服务产品上运行一组自行开发的应用程序.我基于D/B元数据的前缀，从允许的表的静态配置表列表移至基于表列表的表.当我确实将此版本推广到公共服务时，我的每请求延迟平均增加了 2.3-2.4秒.一些检测表明，这完全取决于一个SQL查询:
I run a set of self-developed applications on a typical shared hosting service offering. I moved from a static configured table list of allowed tables to one based on a list of tables bases on a prefix from the D/B metadata. When I did promote this version to the public service, my per-request latency increased by an average 2.3–2.4 secs. Some instrumentation revealed that this was entirely down to one SQL query:
SELECT TABLE_NAME AS name
FROM information_schema.tables
WHERE TABLE_SCHEMA = '<DBname>'
AND TABLE_NAME LIKE '<TablePrefix>%';
之所以使用它，是因为我想在结果集中明确命名该列.但是，使用备用查询对此进行编码会增加一行额外的代码，这些代码在< 2毫秒/strong>中运行:
I used this because I wanted to explicitly name the column in the result set. However, coding around this with an alternate query adds an extra line of code which ran in <2 mSec:
SHOW TABLES LIKE '<TablePrefix>%';
我的服务提供商使用Enterprise MySql 5.0.92-50，因此我无法进行任何性能分析.这是一个扩展问题，因为在我的开发环境和可以配置文件的测试VM上不会发生.它们支持数千个用户，因此实时模式将非常庞大，但是即使如此，连接和大多数查询也只需要几毫秒.
My service provider uses Enterprise MySql 5.0.92-50 so I can't do any profiling. This is a scaling issue as it doesn't occur on my dev environment and test VM where I can profile. They support thousands of users so the live schema will be extremely large, but even so connection and most queries only take milliseconds.
有人知道为什么在大型多用户系统上查询基于内存的information_schema会花这么长时间吗?
Does anyone know why querying the memory-based information_schema on a large multi-user system would take so long?
推荐答案
信息架构尚未优化，没有索引，只有带有元数据的表，通常，当您从架构运行SELECT时，它会打开并读取文件
The information schema is not optimized, there are no indexes, just tables with metadata, and usually, when you run SELECT from the schema, it opens and reads files.
请查看本文-优化INFORMATION_SCHEMA查询;在某些情况下会为您提供帮助.
Have a look at this article - Optimizing INFORMATION_SCHEMA Queries; in some cases it will help you.
这篇关于缓慢查询information_schema.tables的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！