この記事は最終更新から6年以上経過しています。内容が古くなっている可能性があります。
MySQ 8.0 では新たに information_schema に innodb_chached_indexes テーブルが追加されました。
このテーブルを見ることでインデックスがどれくらい innodb buffer pool にキャッシュされているかがわかるようになりました。
MySQL :: MySQL 8.0 Reference Manual :: 24.35.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
目次
INNODB_CACHED_INDEXES テーブル
格納されているデータは下記のように INDEX_ID で格納されているため、実際のインデックス名と紐付けるにはちょっとした工夫が必要になります。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql > select * from information_schema.innodb_cached_indexes limit 5; +------------+----------+----------------+ | SPACE_ID | INDEX_ID | N_CACHED_PAGES | +------------+----------+----------------+ | 4294967294 | 1 | 1 | | 4294967294 | 2 | 1 | | 4294967294 | 3 | 1 | | 4294967294 | 4 | 1 | | 4294967294 | 5 | 1 | +------------+----------+----------------+ 5 rows in set (0.00 sec) |
上記のリファレンスにも記載がありますが、以下のSQLでテーブル名とインデックス、キャッシュされてるページ数が確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql > 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 | +------------+------------+----------------+ | test/t1 | PRIMARY | 1 | | test/t1 | idx_c2 | 1 | | test/t1 | idx_c3 | 1 | +------------+------------+----------------+ 3 rows in set (0.02 sec) |
キャッシュに乗っているインデックスのパーセントを取得する
もう少しSQLを工夫をすることでインデックスがどれくらいキャッシュに乗っているかのパーセンテージも出すことができます。
|
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 |
SELECT cached.database_name, cached.table_name, cached.index_name, cached.cached_pages, cached.cached_size, stats.index_pages, stats.index_size, (cached.cached_pages / stats.index_pages) * 100 AS '%' FROM (SELECT SUBSTRING_INDEX(tables.name, '/', 1) AS database_name, SUBSTRING_INDEX(tables.name, '/', -1) AS table_name, indexes.name AS index_name, cached.n_cached_pages AS cached_pages, cached.n_cached_pages * @@innodb_page_size AS cached_size 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) AS cached LEFT JOIN (SELECT database_name, table_name, index_name, sum(stat_value) as index_pages, sum(stat_value) * @@innodb_page_size as index_size FROM mysql.innodb_index_stats WHERE stat_description = 'Number of pages in the index' GROUP BY database_name, table_name, index_name ) AS stats ON stats.database_name = cached.database_name AND stats.table_name = cached.table_name AND stats.index_name = cached.index_name |
実行結果
|
1 2 3 4 5 6 7 |
+---------------+------------+------------+--------------+-------------+-------------+------------+---------+ | database_name | table_name | index_name | cached_pages | cached_size | index_pages | index_size | % | +---------------+------------+------------+--------------+-------------+-------------+------------+---------+ | test | t1 | PRIMARY | 204 | 3342336 | 225 | 3686400 | 90.6667 | | test | t1 | idx_c2 | 125 | 2048000 | 161 | 2637824 | 77.6398 | | test | t1 | idx_c3 | 125 | 2048000 | 161 | 2637824 | 77.6398 | +---------------+------------+------------+--------------+-------------+-------------+------------+---------+ |
参考：How to calculate a specific InnoDB index size ?
まとめ
MySQL 8.0 で追加された INNODB_CACHED_INDEXES テーブルの情報から、インデックス単位でどれだけメモリにキャッシュされているかがわかるようになりました。パフォーマンス改善の一つの指標として使っていただければと思います。
