MySQL性能终极优化-使用内存

MySQL性能终极优化-使用内存

使用innodb_buffer_pool_size 配置，充分利用内存提高性能。

1.查看现有数据占用存储空间大小

 
SELECt engine,
  count(*) as TABLES,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERe table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;

2.临时生效（MySQL5.7重启MySQL会失效，MySQL8.0重启不会失效）

# 单位bytes转换，1G=1x1024x1024x1024
# 当前8G
SET GLOBAL innodb_buffer_pool_size = 8589934592;

3.修改配置文件（重启MySQL不会失效）

innodb_buffer_pool_size=8000M