mysql的热表有什么办法查么
时间: 2024-03-06 21:46:43 浏览: 22
可以使用MySQL的Performance Schema来查看热表信息。具体步骤如下:
1. 打开Performance Schema:在MySQL中执行`SET GLOBAL performance_schema = ON;`
2. 查看表的I/O操作次数:执行以下语句:
```
SELECT
(SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_ID = t.TABLE_ID) table_name,
sum(t.COUNT_READ) count_read,
sum(t.COUNT_WRITE) count_write
FROM
performance_schema.table_io_waits_summary_by_table t
WHERE
t.TABLE_SCHEMA = 'your_database_name'
GROUP BY
t.TABLE_ID
ORDER BY
sum(t.COUNT_READ + t.COUNT_WRITE) DESC
LIMIT 10;
```
其中,将`your_database_name`替换为你要查询的数据库名。
3. 查看表的锁等待次数:执行以下语句:
```
SELECT
(SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_ID = t.TABLE_ID) table_name,
sum(t.COUNT_READ_NORMAL + t.COUNT_READ_WITH_SHARED_LOCKS + t.COUNT_READ_HIGH_PRIORITY + t.COUNT_READ_NO_INSERT +
t.COUNT_WRITE_ALLOW_WRITE + t.COUNT_WRITE_CONCURRENT_INSERT + t.COUNT_WRITE_DELAYED + t.COUNT_WRITE_NORMAL) count_lock
FROM
performance_schema.table_lock_waits_summary_by_table t
WHERE
t.TABLE_SCHEMA = 'your_database_name'
GROUP BY
t.TABLE_ID
ORDER BY
sum(t.COUNT_READ_NORMAL + t.COUNT_READ_WITH_SHARED_LOCKS + t.COUNT_READ_HIGH_PRIORITY + t.COUNT_READ_NO_INSERT +
t.COUNT_WRITE_ALLOW_WRITE + t.COUNT_WRITE_CONCURRENT_INSERT + t.COUNT_WRITE_DELAYED + t.COUNT_WRITE_NORMAL) DESC
LIMIT 10;
```
其中,将`your_database_name`替换为你要查询的数据库名。
通过以上两个查询语句,可以了解到你的MySQL数据库中哪些表是热表。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)