功能优化之MySQL优化

背景

     昨晚上海的一个朋友让帮忙看一个问题，新开发的功能执行效率不高，有没有效率更好的处理方式，我问：找到效率慢的地方没有？他说他发现有个SQL执行效率很低，耗时130秒，我跟朋友说：遇到问题不能猜，得用科学的方法从正面入手，找到效率慢的地方，再采用合适的方式进行优化，一般像这样的问题，一般使用Alibaba的开源工具Arthas的trace命令可以方便的定位到具体是哪一部分代码慢，不了解Arthas的同学可以自行上网学习一波，这里不介绍该工具的使用，经过一系列的排查，最终发现确实是那个SQL的问题，一般遇到SQL执行慢的问题，我们首先会想到是否用到了索引，SQL：select column1,column2 .... from table where column1 = 'uuid',这个SQL也不复杂，explain发现也用到了索引，索引类型ref,也是正确的，继续排查看一下SQL执行结果有多少数据量，发现查出来6W数据，会不会是索引列的区分度不够导致没有使用到索引，但是确实用到了索引，也不是这哥问题导致的，继续看该表有多少数据，count(id)有700w数据，数据量也不大百万级别的索引数也不会这么慢呀，这个表的列也不多，也不会因为查询时列偏移量导致查询慢的问题，而且这个表也是进行了分表，分成了16个表，也进一步将数据分片，降低索引树的高度，提高查询效率最终，让我不由得感觉是MySQL服务的问题，然后我就查看MySQL相关重要的参数，命令：show variables like 'innodb_buffer_pool_size',发现配置是2G大小，因为MySQL服务器有专门的的DBA管理，也登录不上linux服务器去查看机器配置，一般MySQL相关参数的配置和Linux服务器的配置是有很大关系的，就比如Linux的配置很高，innodb_buffer_pool_size的配置就可以相对的很大。

1.为什么需要innodb buffer pool？

在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。

2. MySQL InnoDB buffer pool 里包含什么？

• 数据缓存
  InnoDB数据页面

• 索引缓存
  索引数据

• 缓冲数据
  脏页（在内存中修改尚未刷新(写入)到磁盘的数据）

• 内部结构
  如自适应哈希索引，行锁等。

3. 如何设置innodb_buffer_pool_size?

innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为2**32 -1,在64-bit平台上最大值为2**64-1。当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。

大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。

3.1 配置缓冲池大小时，请注意以下潜在问题

• 物理内存争用可能导致操作系统频繁的paging

• InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。

• 缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。

• 初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。

  • innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
  • innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
  • innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。

增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，
则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小。 可以通过状态变量Innodb_buffer_pool_resize_status报告在线调整缓冲池大小操作的状态。

 执行语句

 show status like 'Innodb_buffer_pool_resize%';

3.2 配置示例

在以下示例中，innodb_buffer_pool_size设置为1G，innodb_buffer_pool_instances设置为1。innodb_buffer_pool_chunk_size默认值为128M。

1G是有效的innodb_buffer_pool_size值，因为1G是innodb_buffer_pool_instances = 1 * innodb_buffer_pool_chunk_size = 128M的倍数

 执行语句

1. show variables like 'innodb_buffer_pool%';

2. innodb_buffer_pool_size=（1024*1024）/innodb_buffer_pool_chunk_size/innodb_buffer_pool_instances

3.3 在线调整InnoDB缓冲池大小

         SET GLOBAL innodb_buffer_pool_size = 3221225472

3.4 监控在线缓冲池调整进度

        SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

4. 配置的innodb_buffer_pool_size是否合适？

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

例如，在我的服务器上，检查当前InnoDB缓冲池的性能

show status like 'innodb_buffer_pool_read%';

Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests*100 即 1700/940668*100=0.18072263540378

意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。

InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

5. InnoDB缓冲池状态变量有哪些？

可以运行以下命令进行查看：

show global status like '%innodb_buffer_pool_pages%';

说明：

• Innodb_buffer_pool_pages_data
  InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时，报告的Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total。

• Innodb_buffer_pool_pages_dirty
  显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。

• Innodb_buffer_pool_pages_flushed
  表示从InnoDB缓冲池中刷新脏页的请求数。

• Innodb_buffer_pool_pages_free
  显示InnoDB缓冲池中的空闲页面

• Innodb_buffer_pool_pages_misc
  InnoDB缓冲池中的页面数量很多，因为它们已被分配用于管理开销，例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。

• Innodb_buffer_pool_pages_total
  InnoDB缓冲池的总大小，以page为单位。

• innodb_buffer_pool_reads
  表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

• innodb_buffer_pool_read_requests
  它表示从内存中逻辑读取的请求数。

• innodb_buffer_pool_wait_free
  通常，对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时，InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb_buffer_pool_size，则此值应该很小。如果大于0，则表示InnoDb缓冲池太小。

• innodb_buffer_pool_write_request
  表示对缓冲池执行的写入次数。

结果

经过一系列计算，将innodb_buffer_pool_size的配置由2G调大到4G，命令：set global innodb_buffer_pool_size = 4G,最终该SQL的执行时间由130s优化到15s，注意：set命令设置的参数在服务器重启之后就会失效，想要永久生效，需要修改MySQL的配置文件，重启MySQL服务，事已至此，我们明白了最终的优化方向，MySQL服务的参数配置不合理导致SQL执行效率很慢。这是DBA的工作没做到位了，可以让DBA重新调整MySQL参数配置。

发问

为什么innodb_buffer_pool_size的大小会影响SQL的执行效率？

原理：

MySQL的数据最终会持久化到磁盘上，我们在执行SQL的时候，MySQL会将数据从磁盘上读到内存里，在内存里进行修改，这时候数据被称为脏页，MySQL会通过异步刷盘的方式将数据持久化到磁盘上，将数据读到内存，也就是将数据放到innodb_buffer_pool里，我们可以把innodb_buffer_pool看做MySQL的缓存，假如SQL涉及的数据页很多，innodb_buffer_pool空间很小，innodb_buffer_pool不足以储存相关数据页，最终会导致频繁的从磁盘上IO读取，利用不到innodb_buffer_pool缓存，导致SQL执行效率很慢，innodb_buffer_pool里用到冷热数据储存算法以及LRU（最近最少出现次数）算法以更加科学的方式最大限度避免频繁从磁盘上读取数据。

至此，问题解决，能力有限，有什么问题，大佬们多多指教，不胜感激!