Mysql中针对存储IO特点进行的性能优化

在Mysql存储过程中，联系上下组件之间，解密存储过程中Mysql的优化

Mysql的IO介绍及原因详解，请看上一篇，本篇专注于Mysql的存储IO内幕

目录

一、总序

二、索引辅助

三、日志辅助

四、缓存辅助

---

一、总序

MySQL主要优化：对磁盘IO进行优化（其他地方也有优化，如网络层的优化等）。

其核心是由存储引擎来完成的。

存储引擎在 MySQL 的体系架构中位于第三层，负责对 MySQL 中的数据进行存储和提取，是与文件打交道的子系统，它是根据底层提供的文件访问层抽象接口定制的一种文件访问机制，这个机制就叫作 MySQL 存储引擎。从 MySQL 5.5 开始，默认采用 InnoDB 作为存储引擎。
如下是InnoDB的存储引擎架构图：

返回顶部目录

---

二、索引辅助

关于索引的详解，可以查看Mysql的索引机制

1）索引：一条语句执行慢，第一本能反应就是加索引，加索引可以走索引树搜索，避免全表扫描，也就是减少IO次数

2）聚簇索引：索引项的排序方式和表中的数据记录排序方式一直的索引。也就是说聚簇索引的顺序就是数据的物理存储顺序。他会根据聚簇索引键的顺序来存储表中的数据，即对标的数据按照索引键的顺序进行排序，然后存储到磁盘上，因为数据在物理存放时只有一种排列方式，所以一个表只能有一个聚簇索引。(找到索引了就找到需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。)，在大部分根据主键（或某个特定值）查询情况下，比较适用，避免回表，减少IO次数

3）覆盖索引：需要查询的内容，已经在本索引树上了，避免了回表，减少IO次数

4）组合索引：索引可以覆盖多个数据列，如index(a,b,c)。由最左前缀原则，包含了index(a)与index(a,b)。减少了索引的维护成本，将资源分配给其他，间接的减少了IO次数

返回顶部目录

---

三、日志辅助

日志辅助缓存、事务等安全，缓存才是对IO性能起作用的。这里我们重点介绍一下redo log。

执行一次写操作，写内存、写redo log，为什么要写redo log不直接写磁盘呢？因为写redo log是顺序写IO，写磁盘是随机写IO，顺序IO性能优于随机IO。结论：redo log的存在使用顺序写IO替代随机写IO

1）我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证持久性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

2）因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

3）因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

4）redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

5）在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中。

返回顶部目录

---

四、缓存辅助

缓冲才是解决IO问题的核心，下面是相关的几个缓存

buffer_pool_size：参数用于设置缓冲池大小，缓冲池越大，存放的数据页也就越多。执行一次查询，查询数据对应的数据页在内存中，就可以直接返回，不需要进行IO操作，直接在内存中完成，其性可想而知。
关于buffer_pool_size大小设置，官方文档是这么建议的：

On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

change buffer：执行一次写操作，对应数据的数据页不在内存中，写change buffer、写redo log，写change buffer避免将数据页读入内存，减少随机读IO。结论change buffer的存在为了减少随机读IO

返回顶部目录