在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