缓冲池信息相关的表
查看缓冲池中的信息，主要是通过information_schema库下的三张表进行查看：
+ INNODB_BUFFER_PAGE：INNODB缓冲池中page的情况
+ INNODB_BUFFER_PAGE_LRU：INNODB缓冲池中page的LRU(Least Recently Used)情况
+ INNODB_BUFFER_POOL_STATS：INNODB缓冲池状态总览
1
2
3
4
5
6
7
8
9
root:information_schema> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_information_schema (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
+-----------------------------------------------+
详情查看：[InnoDB INFORMATION_SCHEMA Buffer Pool Tables]
注意事项：
查询 INNODB_BUFFER_PAGE 或者 INNODB_BUFFER_PAGE_LRU 会影响数据库的性能！
(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-buffer-pool-tables.html)
从INNODB_BUFFER_PAGE表查询系统数据
系统数据记为MySQL用以存储数据库实例相关状态或者配置的数据，这部分数据同样会部分缓存在缓冲池中。
1
2
3
4
5
6
7
8
root:information_schema> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------+
| COUNT(*) |
+----------+
| 29573 |
+----------+
1 row in set (0.25 sec)
系统数据在缓冲池中的分布
查看系统数据在整个缓冲池中的分布情况：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)
) AS system_pages,
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
) AS total_pages,
(
SELECT ROUND((system_pages/total_pages) * 100)
) AS system_page_percentage;
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
| 29573 | 32768 | 90 |
+--------------+-------------+------------------------+
缓存中系统数据的属性
PAGE_TYPE字段可以查看系统数据的属性：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+-------------------+
| PAGE_TYPE |
+-------------------+
| SYSTEM |
| INODE |
| IBUF_INDEX |
| TRX_SYSTEM |
| RSEG_ARRAY |
| UNDO_LOG |
| FILE_SPACE_HEADER |
| IBUF_BITMAP |
| LOB_FIRST |
| LOB_DATA |
| LOB_INDEX |
| UNKNOWN |
| INDEX |
+-------------------+
13 rows in set (0.37 sec)
从INNODB_BUFFER_PAGE表查询用户数据
1
2
3
4
5
6
7
8
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';
+----------+
| COUNT(*) |
+----------+
| 3195 |
+----------+
用户数据在缓冲池中的分布情况
同理，查看用户数据在缓冲池中的分布情况：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
) AS user_pages,
(
SELECT COUNT(*)
FROM information_schema.INNODB_BUFFER_PAGE
) AS total_pages,
(
SELECT ROUND((user_pages/total_pages) * 100)
) AS user_page_percentage;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
| 3195 | 32768 | 10 |
+------------+-------------+----------------------+
查看用户数据对应的表
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
+----------------------+
| TABLE_NAME |
+----------------------+
| `wstestdb`.`sbtest2` |
| `wstestdb`.`sbtest1` |
+----------------------+
2 rows in set (0.15 sec)
从INNODB_BUFFER_PAGE表查看索引数据
当然，也可以使用该表查看索引被缓存的情况情况。索引缓存情况可以对比另一篇文章查看InnoDB索引在缓存中的情况。
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE INDEX_NAME='k_2' AND TABLE_NAME = '`wstestdb`.`sbtest2`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| k_2 | 167 | 3 |
+------------+-------+-----------------+
1 row in set (0.19 sec)
查看某个表的所有索引缓存情况：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME = '`wstestdb`.`sbtest2`'
GROUP BY INDEX_NAME;
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| PRIMARY | 1767 | 28 |
| k_2 | 167 | 3 |
+------------+-------+-----------------+
2 rows in set (0.21 sec)
查询LRU_POSITION信息
LRU可以反映表中热数据的情况，可以参考这个状态，判断是否会影响sql的查询，或者是否需要刷数据，使数据变“热”。
1
2
3
4
5
6
7
8
9
root:information_schema> select table_name,INDEX_NAME,min(LRU_POSITION),max(LRU_POSITION) from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU where table_name='`wstestdb`.`sbtest2`' group by INDEX_NAME;
+----------------------+------------+-------------------+-------------------+
| table_name | INDEX_NAME | min(LRU_POSITION) | max(LRU_POSITION) |
+----------------------+------------+-------------------+-------------------+
| `wstestdb`.`sbtest2` | PRIMARY | 485 | 4273 |
| `wstestdb`.`sbtest2` | k_2 | 527 | 2910 |
+----------------------+------------+-------------------+-------------------+
2 rows in set (0.03 sec)
查询INNODB_BUFFER_POOL_STATS表
查询INNODB_BUFFER_POOL_STATS表和直接执行show engine innodb status \G 和 查看Innodb_buffer的状态的结果相似：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
root:information_schema> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 32768
FREE_BUFFERS: 27417
DATABASE_PAGES: 5169
OLD_DATABASE_PAGES: 1888
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 0
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 3590
NUMBER_PAGES_CREATED: 1579
NUMBER_PAGES_WRITTEN: 68456
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 8.973448067156127
NUMBER_PAGES_GET: 25963207
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> SHOW ENGINE INNODB STATUS \G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 549453824
Dictionary memory allocated 494195
Buffer pool size 32768
Free buffers 27417
Database pages 5169
Old database pages 1888
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3590, created 1579, written 68456
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 5169, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
root:information_schema> SHOW STATUS LIKE 'Innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 190911 6:50:47 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 5169 |
| Innodb_buffer_pool_bytes_data | 84688896 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 68446 |
| Innodb_buffer_pool_pages_free | 27417 - |
| Innodb_buffer_pool_pages_misc | 182 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 25963233 |
| Innodb_buffer_pool_reads | 3384 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 4119405 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)