如何判断mysql索引是否完全适合内存 [英] How to figure out if mysql index fits entirely in memory

问题描述

有没有办法确定mysql索引是否完全适合可用内存？如果是这样，我将如何：

Is there a way to determine whether a mysql index fits entirely in available memory? If so, how would I:

• 确定mysql索引的大小


• 确定可用内存应用程序


• 确定索引是否完全适合内存

推荐答案

取决于存储引擎

SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB
FROM information_schema.tables WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','performance_schema','mysql');

从 key_buffer_size 中减去。如果答案> 0，则是

Subtract that from key_buffer_size. If the answer > 0, then Yes

SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB
FROM information_schema.tables WHERE engine='InnoDB';

从 innodb_buffer_pool_size 中减去。如果答案> 0，那么是

Subtract that from innodb_buffer_pool_size. If the answer > 0, then Yes

我在 DBA StackExchange

在专用数据库服务器上，确保 InnoDBSizeMB + IndexSizesMB 不超过RAM的75％。

On a dedicated DB Server, make sure InnoDBSizeMB+IndexSizesMB does not exceed 75% of RAM.

这篇关于如何判断mysql索引是否完全适合内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！