information_schema和performance_schema是MySQL的系統(tǒng)數(shù)據(jù)庫(kù)，分別用于存儲(chǔ)元數(shù)據(jù)和性能指標(biāo)。information_schema提供數(shù)據(jù)庫(kù)結(jié)構(gòu)信息，如表、列、權(quán)限等，不可修改且僅包含結(jié)構(gòu)性元數(shù)據(jù)；performance_schema則記錄服務(wù)器運(yùn)行時(shí)的性能數(shù)據(jù)，如查詢等待、資源消耗等，需啟用特定儀器才能獲取詳細(xì)信息。使用前者可動(dòng)態(tài)查詢數(shù)據(jù)庫(kù)對(duì)象結(jié)構(gòu)，后者可用于排查性能瓶頸。兩者用途不同但互補(bǔ)，掌握其用法對(duì)管理和優(yōu)化MySQL至關(guān)重要。

When you see information_schema and performance_schema in MySQL, they’re not your regular databases. They’re system databases that provide metadata and performance metrics about the MySQL server itself.

information_schema: Your Metadata Reference

This is basically MySQL’s built-in catalog. It holds information about all other databases, tables, columns, privileges, and more — but not the actual data inside tables.

Think of it like a phone book for your database structure. If you want to know what tables exist in a database or what columns a specific table has, this is where you look.

Some common use cases:

• Checking which tables exist in a certain database
• Finding out column types and constraints
• Seeing which users have access to what

For example, if you run:

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db';

You’ll get a list of all tables in your_db.

Important notes:

• You can query it but shouldn’t modify it directly
• It's read-only
• It doesn't store historical or runtime data, just structural metadata

performance_schema: Insight Into MySQL Performance

This one is more about how the server is doing under the hood. It tracks resource usage, wait times, query execution details, and more.

It’s useful when you're trying to debug performance issues or optimize queries.

Some things you can find here:

• How many times each query was executed
• How long queries waited for locks
• Thread states and connection activity

To get something useful from it, you usually need to enable specific instrumentation or consumers. For example:

UPDATE performance_schema.setup_instrument SET ENABLED = 'YES' WHERE NAME LIKE '%wait%';

Then you can check wait events:

SELECT * FROM performance_schema.events_waits_current;

But keep in mind:

• It adds some overhead (though minimal by default)
• Not all instruments are enabled out of the box
• The tables can be complex and require some learning to interpret correctly

When to Use Which?

Use information_schema when you need to:

• List objects (tables, views, routines) across databases
• Get schema-level details (like column types, collation, etc.)
• Write dynamic SQL or tools that inspect database structure

Go to performance_schema when you:

• Are troubleshooting slow queries or high load
• Want to understand internal waits and bottlenecks
• Need to monitor real-time server behavior without heavy logging

They serve very different purposes, but both are super handy once you know how to use them.

That’s basically it.

以上是信息_schema和performance_schema數(shù)據(jù)庫(kù)是什么？的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！