数据库的慢查询是我们在生产环境中必须经常检测的，如果慢查询语句过多，说明我们应该增加buffer_pool的大小了。常常检查的指标就是查看缓存命中率是否过低。
mysql> show status like 'innodb_buffer_pool%'; +---------------------------------------+--------------------------------------------------+ | 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 190107 7:19:16 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 31732 | | Innodb_buffer_pool_bytes_data | 519897088 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 1298499 | | Innodb_buffer_pool_pages_free | 1024 | | Innodb_buffer_pool_pages_misc | 8 | | Innodb_buffer_pool_pages_total | 32764 | | 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 | 438699486 | | Innodb_buffer_pool_reads | 536971 | | Innodb_buffer_pool_wait_free | 42342 | | Innodb_buffer_pool_write_requests | 237826577 | +---------------------------------------+--------------------------------------------------+ 18 rows in set (0.04 sec)
我们需要关注这几个值：Innodb_buffer_pool_read_requests表示read请求的次数，Innodb_buffer_pool_reads表示从物理磁盘中读取数据的请求次数
因此缓存命中率的计算方法是：（1- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests） *100 %，如果缓存命中率过低，则我们需要考虑扩充内存的大小或者是innodb_buffer_pool_size的值。