Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

InnoDB索引在缓存中的情况

innodb的缓存情况

从MySQL8.0开始,可以通过information_schema中的INNODB_CACHED_INDEXES表,查看索引在缓存中的情况。


所需权限
需要有对于该表的PROCESS权限,否则会报错:

1
2
You must have the PROCESS privilege to query this table.

查看索引缓存状态

(1)查询INNODB_CACHED_INDEXES表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root:information_schema> select * from INNODB_CACHED_INDEXES ;
+------------+----------+----------------+
| SPACE_ID   | INDEX_ID | N_CACHED_PAGES |
+------------+----------+----------------+
| 4294967294 |        1 |              1 |
| 4294967294 |        2 |              1 |
| 4294967294 |        3 |              1 |
| 4294967294 |        4 |              1 |
| 4294967294 |        5 |              1 |
| 4294967294 |        7 |              1 |
| 4294967294 |        8 |              1 |
| 4294967294 |        9 |              1 |
| 4294967294 |       10 |              1 |
| 4294967294 |       11 |              1 |
| 4294967294 |       12 |              1 |
| 4294967294 |       13 |              1 |
| 4294967294 |       14 |              1 |
| 4294967294 |       15 |              2 |
...


(2)获取表名对应关系
可以根据tables和columns表获取表名:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
root:information_schema> SELECT
  tables.NAME AS table_name,
  indexes.NAME AS index_name,
  cached.N_CACHED_PAGES AS n_cached_pages
FROM
  INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
  INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
  INFORMATION_SCHEMA.INNODB_TABLES AS tables
WHERE
  cached.INDEX_ID = indexes.INDEX_ID
  AND indexes.TABLE_ID = tables.TABLE_ID;
+------------------+------------+----------------+
| table_name       | index_name | n_cached_pages |
+------------------+------------+----------------+
| wstestdb/sbtest2 | PRIMARY    |            273 |
| wstestdb/sbtest2 | k_2        |            166 |
| wstestdb/sbtest1 | PRIMARY    |            287 |
| wstestdb/sbtest1 | k_1        |            165 |
+------------------+------------+----------------+
4 rows in set (0.01 sec)


(3)多次查询,观察变化
再次查询:

1
2
3
4
5
6
7
8
9
10
11
root:information_schema> SELECT   tables.NAME AS table_name,   indexes.NAME AS index_name,   cached.N_CACHED_PAGES AS n_cached_pages FROM   INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,   INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,   INFORMATION_SCHEMA.INNODB_TABLES AS tables WHERE   cached.INDEX_ID = indexes.INDEX_ID   AND indexes.TABLE_ID = tables.TABLE_ID;
+------------------+------------+----------------+
| table_name       | index_name | n_cached_pages |
+------------------+------------+----------------+
| wstestdb/sbtest2 | PRIMARY    |           1764 |
| wstestdb/sbtest2 | k_2        |            166 |
| wstestdb/sbtest1 | PRIMARY    |            831 |
| wstestdb/sbtest1 | k_1        |            165 |
+------------------+------------+----------------+
4 rows in set (0.01 sec)

(4)表的大小
环境介绍:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root:information_schema> select count(*) from wstestdb.sbtest2;
+----------+
| count(*) |
+----------+
|   116716 |
+----------+

root:information_schema> show create table  wstestdb.sbtest2 \G 
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE if not exists `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=700001 DEFAULT CHARSET=utf8

结果

缓存情况

可以看出,大约有 5.89%的数据和索引页被缓存在缓冲池中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
root:information_schema> SELECT COUNT(*) AS total_pages FROM information_schema.INNODB_BUFFER_PAGE;
+----------+
| total_pages |
+----------+
|    32768 |
+----------+
1 row in set (0.15 sec)

root:information_schema> select (1764+166)/32768;
+------------------+
| (1764+166)/32768 |
+------------------+
|           0.0589 |
+------------------+
1 row in set (0.00 sec)

缓冲池中的信息

可以根据之前的文章InnoDB缓冲池中的内容,查看缓冲池中的情况。

附加信息

对于索引缓存情况,pmm已经支持对其进行监控:
使用pmm监控索引缓存情况:Which Indexes are Cached? Discover with PMM

comments powered by Disqus