欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL优化之InnoDB优化

程序员文章站 2023-11-30 08:56:10
学习计划很容易就被打断,坚持也不容易。最近公司里开会,要调整业务方向,建议学习nodejs。nodejs之前我就会一点,但是没有深入研究。node的语法和客户端js基本上是...

学习计划很容易就被打断,坚持也不容易。最近公司里开会,要调整业务方向,建议学习nodejs。nodejs之前我就会一点,但是没有深入研究。node的语法和客户端js基本上是一样的,这半年来很少开发有客户端的东西。本来js基础还行的我,也对这块的知识陌生了。看起来知识都是用进废退的,不常用了,过不了多久就会遗忘。所以又重新复习了js的相关知识。学习了node的服务器与socket知识。mysql的计划就这样的搁浅起来,星期天的时候吃吃喝喝睡睡,早上又懒的要命,熬着熬着就熬到了下午。废话不多说了,继续进行mysql的优化系列,这次看下innodb的优化项。

innodb的主索引是聚簇索引,索引与数据公用表空间。对于innodb来说,数据就是索引,索引就是数据。innodb缓存机制与myisam的最大区别在于,innodb不仅缓存索引,同时还会缓存数据。

一、innodb缓存池

innodb缓存池(innodb buffer pool)是提升innodb提升性能的关键,它既可以缓存数据,又可以缓存索引,甚至其他的管理数据(元数据、行级锁)等。可以使用show variables like 'innodb%pool%'; 来查看相关的参数选项。

mysql> show variables like 'innodb%pool%';
+-------------------------------------+----------------+
| variable_name            | value     |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size   | 8388608    |
| innodb_buffer_pool_dump_at_shutdown | off      |
| innodb_buffer_pool_dump_now     | off      |
| innodb_buffer_pool_filename     | ib_buffer_pool |
| innodb_buffer_pool_instances    | 8       |
| innodb_buffer_pool_load_abort    | off      |
| innodb_buffer_pool_load_at_startup | off      |
| innodb_buffer_pool_load_now     | off      |
| innodb_buffer_pool_size       | 134217728   |
+-------------------------------------+----------------+

innodb_buffer_pool_size

innodb_buffer_pool_size是用于设置innodb缓存池(innodbbufferpool)的大小,默认值是128m.innodb缓存池的大小对innodb的整体性能影响较大,如果当前的mysql服务器专门用作mysql服务,那么可以尽量的增加该参数的大小。

innodb_buffer_pool_instance

innodb_buffer_pool_instance默认值是1,表示innodb缓存池被划分为一个区域,适当的增加该参数值,可以提升innodb的并发性能。

innodb_additional_mem_pool_size

指定innodb用于来存储数据字典和其他内部数据的缓存大小,默认值是2m.innodb的表个数越多,就应该适当的增加该参数的大小。

二、innodb缓存池内部结构

innodb在内存中维护一个缓存池用于缓存数据和索引。缓存池可以认为是一条很长的链表(list).该链表分为两个子链表,一个子链表存放old page数据,old page 数据是长时间未被访问的数据页,亮一个子链表存放new page,new page 是最近被访问的数据页。old page 默认占整个链表大小的37%,可以通过innodb_old_blocks_pct参数查看.

mysql> show variables like 'innodb_old_blocks%';
+------------------------+-------+
| variable_name     | value |
+------------------------+-------+
| innodb_old_blocks_pct | 37  |
| innodb_old_blocks_time | 1000 |
+------------------------+-------+

old page 和 new page 的交汇点称为midpoint。

当用户访问数据时,innodb首先会再innodb缓存中查找数据,如果缓存池中没有数据,innodb会将硬盘中的数据插入到innodb缓存池中,如果缓存池已满,则利用lru算法清楚过期的老数据

三、innodb缓存池预热。

mysql服务器启动一段时间后,innodb会将经常访问的数据(业务数据,管理数据)放入innodb缓存中,即innodb缓存池中保存的是频繁需要访问的数据(简称热数据)。当innodb缓存池的大小是几十g或者上百g的时候,如果重启mysql,如果将之前innodb缓存池中的热数据加载到innodb缓存池中呢?

如果单靠innodb自身预热的innodb缓存池,将会是一个不短的时间周期,这对于业务繁忙的系统来说,长时间的挂机,是严重的生产事故,不能够容忍。幸好在mysql5.6版本支持关闭服务时,可以将热数据保存至硬盘,mysql重启是首先将硬盘中的热数据加载到innodb的缓存中去,这样可以缩短预热的时间,提高业务繁忙高并发时的效率。

mysql> show variables like '%innodb%pool%';
+-------------------------------------+----------------+
| variable_name            | value     |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size   | 8388608    |
| innodb_buffer_pool_dump_at_shutdown | off      |
| innodb_buffer_pool_dump_now     | off      |
| innodb_buffer_pool_filename     | ib_buffer_pool |
| innodb_buffer_pool_instances    | 8       |
| innodb_buffer_pool_load_abort    | off      |
| innodb_buffer_pool_load_at_startup | off      |
| innodb_buffer_pool_load_now     | off      |
| innodb_buffer_pool_size       | 134217728   |
+-------------------------------------+----------------+

innodb_buffer_pool_dump_at_shutdown

默认是关的,如果开启参数,停止mysql服务是,innodb缓存中的热数据将会保存到硬盘中。

innodb_buffer_pool_load_at_starup

默认是关闭的,如果开启该参数,启动mysql服务时,mysql将本地硬盘的热数据加载到innodb缓存池中。

innodb_buffer_pool_dump_now

默认关闭,如果开启该参数,停止mysql服务时,以手动方式将innodb缓存池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_now

默认关闭,如果开启该参数,启动mysql服务时,以手动方式将本地硬盘的数据加载到innodb缓存池中,

innodb_buffer_pool_filename

如果开启innodb预热功能,停止mysql服务是,mysql将innodb缓存池中的热数据保存到数据库根目录下,默认文件名是这个参数的值。

开启innodb缓存后,可以使用如下命令查看当前innodb缓存池预热的状态信息:

show status like 'innodb_buffer%';
+---------------------------------------+-------------+
| variable_name             | value    |
+---------------------------------------+-------------+
| innodb_buffer_pool_dump_status    | not started |
| innodb_buffer_pool_load_status    | not started |
| innodb_buffer_pool_pages_data     | 218     |
| innodb_buffer_pool_bytes_data     | 3571712   |
| innodb_buffer_pool_pages_dirty    | 0      |
| innodb_buffer_pool_bytes_dirty    | 0      |
| innodb_buffer_pool_pages_flushed   | 1      |
| innodb_buffer_pool_pages_free     | 7973    |
| innodb_buffer_pool_pages_misc     | 0      |
| innodb_buffer_pool_pages_total    | 8191    |
| 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   | 1497    |
| innodb_buffer_pool_reads       | 219     |
| innodb_buffer_pool_wait_free     | 0      |
| innodb_buffer_pool_write_requests   | 1      |
+---------------------------------------+-------------+

这里面的英语都比较简单,就不解释了。

四、innodb实时监控

mysql> show engine innodb status\g