mysql ssd inodb_mysql innodb体系结构

一.内存结构

1.buffer pool 缓冲池

缓冲数据和索引

(1).三条链表

- FREE链表

FREE链表就是存放没有被使用的数据块的链表，当数据缓存到内存中的时候，

就从FREE链表优先获取空快来缓存数据。如果FREE使用完，就从LRU链表利用LRU算法淘汰旧的数据。

- LRU链表

缓存数据的链表，利用LRU算法来淘汰旧的数据，保证我们的内存中始终是经常被访问到的数据

LRU：最近最少使用算法，最近不经常被使用的数据会被放到链表的一端(冷数据 冷端)，经常被访问的数据被放到另一端(热数据 热端)

innodb在LRU链表中加入mid_point位置，最新读取的页面，并不是放到列表的前端而是放入到mid_point位置，这样可以防止热快被新都区的大量数据替换出LRU链表

相关参数

innodb_max_dirty_pages_pct

默认75% LRU内的脏块如果超过75%，强制性的刷脏。

如果不确定，使用默认值就可以了。

如果业务有大量数据写入，而且不会频繁的访问或者修改(数据不活跃)，可以考虑调低一些。

如果业务数据比较活跃，可以挑高一些默认值

innodb_old_blocks_pct

确定modpoint位置， 默认37， modpoint指新读取到的?放入LRU(最近最少使?算法) 列表中的位置

innodb_old_blocks_time

表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端

5.7的新特性

Innodb buffer pool数据预热

数据库被频繁访问到，热点数据都被加载到内存，提高访问效率，数据库重启，

数据库内热点数据已经消失，需要业务再次反问的时候，再从硬盘调入到内存，在数据库的一段时时间内

访问效率比较低。

innodb_buffer_pool_dump_at_shutdown = 1

在关闭数据库得的时候，持久化热数据到硬盘

innodb_buffer_pool_load_at_startup = 1

在开启数据库的时候，把数据从硬盘直接载入到内存

innodb_buffer_pool_dump_pct = 40

选择热数据关闭数据库持久化到硬盘的百分比

ib_buffer_pool文件，就持久化innodb热数据的文件

- FLUSH链表

刷脏链表，脏块被写入硬盘，先载入flush链表，然后由flush链表进行刷脏。

innodb_flush_neighbors

刷新邻接页：合并IO请求，减少随机IO

默认是开的， 这个?定要开着，充分利?顺序IO去写数据

2.redo

- 相关参数:

innodb_log_files_in_group

innodb每个redo log file的成员数量,默认2

innodb_log_file_size

innodb日志文件大小,建议设置成buffer pool 1/10

-rw-r-----. 1 mysql mysql 268435456 Mar 7 20:09 ib_logfile0

-rw-r-----. 1 mysql mysql 268435456 Jul 12 2018 ib_logfile1

innodb_log_buffer_size

log bffer的大小，就是日志缓冲的大小，重做日志缓冲，

一般情况下8MB足够使用，如果不够放心，可以使用16MB

innodb_lock_wait_timeout

事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败.

innodb_flush_log_at_trx_commit

这个参数有三个值可以设置

0 log buffer每秒写入log file一次(数据库)，并且logfile的磁盘flush刷新同步进行(系统),这种情况下，log buffer仅仅在master thread

的每秒循环中执行

1 每次事务提交都会进行log buffer的写入log file(数据库)，并且flush到磁盘中(系统)

2 每次事务提交都会进行log buffer的写入到log file(数据库)，但是flush操作是每秒进行一次(系统)

这个参数设置为0的时候，速度最快，但是不安全。mysqld进程崩溃后,导致上一秒的数据全部丢失

当设置为1的时候，会造成一个事务的丢失

当设置为2的时候，速度较快，数据库崩溃会造成某个事务丢失，但是不会丢失一秒的数据，只有当服务器宕机或者断电才会造成1s的数据丢失

该参数的设置，要结合业务进行分析，如果是对于数据要求较大的OLTP系统，应该采用1.如果是OLAP系统，该类系统大多数存储各类日志，

数据库的日志插入压力如果比较大的话，可以考虑改成0或者2

这个参数配合sync_binlog(使binlog在每N次binlog写入后与硬盘 同步)，共同组成了innodb的日志刷盘策略和数据安全性。相当重要

当两个参数都为1的时候，速度最慢，但是数据最安全。

LSN 日志序列号，对应日志文件的偏移量

LSN是一个单调递增的整数

LSN=旧LSN+写入物理事务的字节数

show engine innodb status\G;

---

LOG

---

Log sequence number 3906122 #当前系统LSN

Log flushed up to 3906122 #当前已经写入日志的LSN

Pages flushed up to 3906122 #当前最旧的脏页数据对应的LSN

Last checkpoint at 3906113 #当前已经写入checkpoint的LSN

redo的效率

(1).脏块的数据量比较大，落盘脏块的话，一个块16KB，我们即使只修改一条数据，那么也需要整个脏块落盘，对于系统的IO压力很大，而redo记录的是数据库的修改的数据页的变化情况，数据量相对小很多

(2).redo落盘是顺序IO

(3).redo数据块和硬盘块大小是一样的，IO效率高

3.undo

(1).5.7的新特性

在线回收undo表空间

5.5版本以及之前版本，undo是系统表空间内部，

undo表空间的扩大，造成系统表空间的扩大，系统表空间的扩大，不能收缩。

造成系统表空间无限扩大，撑满磁盘的情况。

5.6版本中，可以将undo表空间从系统表空间分离出来

5.7版本，推出了系统自动在线回收undo表空间

(2).相关参数

innodb_undo_directory = /home/mysql3306/mysql3306/

-rw-r-----. 1 mysql mysql 10485760 Jul 12 2018 undo001

-rw-r-----. 1 mysql mysql 10485760 Jul 12 2018 undo002

-rw-r-----. 1 mysql mysql 10485760 Jul 12 2018 undo003

innodb_undo_logs = 128 #must >=35.default 128

innodb_undo_tablespaces = 3 #must >=2

innodb_undo_log_truncate = 1 开启在线回收undo表空间

innodb_max_undo_log_size = 1000M回收undo表空间的最大值，超过1G之后，可以出发undo truncate

innodb_purge_rseg_truncate_frequency = 128

(3).检查点

检查点解决的问题:

- 缩短数据库恢复时间

- 缓冲池不够用时，刷新脏页到磁盘

- 重做日志不够用时，刷新脏页

检查点类型:

- sharp checkpoint

全量检查点(关闭数据库)，把所有的脏块落盘

- fuzzy checkpoint

数据库运行的时候，进行页面的落盘操作，刷新部分脏块

每一秒或十秒落盘

redo不可用的时候，这时候刷新到磁盘是从脏页链表中刷新的。

刷新flush list的时候

4.innodb关键特性

(1).change buffer

5.5版本之前，只支持insert操作优化

5.5版本进行了改进，开始支持del等dml操作

- 作用:为了优化在dml操作下，对辅助索引的维护成本，mysql引入了insert buffer。首先在内存里面构建一颗树，将DML数据修改进行合并，合并完成后然后再一次性合并到硬盘上的辅助索引里面，减少IO请求次数，减少随机IO，减少相关排序动作。

- insert buffer场景

必须是辅助索引

辅助索引是不唯一(因为merge操作的过程中，不会检查唯一性)

- 使用insert buffer的过程

判断辅助索引是否在内存中，如果存在直接把数据插入辅助索引

如果不存在，则使用插入insert buffer

合并操作

- insert buffer和辅助索引合并(merge)操作的时间

1S 10S合并

如果用户需要查询辅助索引的数据，那么会优先进行合并，然后在返回给用户数据

(2).double write buffer

- 为了避免half write的产生:

页面的刷新会遇到部分写的问题，也就是说对于只写了其中一个页面，只写了一部分的内容，innodb的页面大小是16KB，但是写入过程中只写了4KB(操作系统仅仅保证512字节写入的完整性)，这个是时候因为页面不是完整的，因此不能通过redo来进行恢复。redo恢复的前提条件是页是完整的。在数据库崩溃后，传统的数据库会使用redo log进行恢复，恢复的原理是通过redo对数据也进行重新进行物理操作，但是如果这个数据页本身发生了损坏，那么redo对其进行重做也是没有作用的，innodb的二次写，在写入的时候，创造了一个关于页的副本，这样即使在发生写失效后，也可以通过副本页，对还原重做。

- double write的实现机制

Double write分为两部分

一部分是内存中的double write buffer ，大小为2MB(16k一个页，一共128个页)

第二部分是磁盘共享表空间的128个数据页，在对脏页进行落盘的时候，并不是直接进行落盘，而是先复制到double write buffer，然后再分别写入到共享表空间然后再写入表空间。

-相关参数

innodb_doublewrite

(3).自适应hash 索引

针对于热点数据优化

在数据库内部经常被访问到的数据，数据库自动判断，然后使用自适应hash索引对于热点数据进行索引

hash算法

1.hash算法 用于加密

单向加密算法：只能加密不能解密，一般情况下，在数据库中存储密码，都是加密存储

hash 相同的字符串经过hash加密，得到的结果是一样的

hash算法建立索引后的优势：

1.散列算法：可以分散热数据

2.hash算法查询效率很高，O(1)

hash缺点：因为散列算法，所以不能进行范围查询

md5 用于校验

只能where id=1

不能 where id>10

相关参数:

innodb_adaptive_hash_index

二.后台线程

1.mysql单进程多线程

IO 线程 负责数据(数据块)的读写的线程，异步IO线程，使用linux本身的aio系统库

默认mysql数据库有4读 4写

如果压力比较大，可以8读 8写

innodb_write_io_threads | 8

innodb_read_io_threads | 8

2.一次刷脏块的个数

innodb_io_capacity

刷脏块，硬盘性能

200 SATA/SAS

500-2000 RAID SAS SATA

5000 SSD(500MB/s)

20000(尽量不要超过2w) PCIE/fusion-io(3GB/S 1.5-2.5GB/S)

3.

purge thread

负责undo数据块的删除

insertbuffer thread

负责insert buffer 与辅助索引的合并操作

redolog thread

负责重做日志缓冲的磁盘写入

master thread

优先级最高

4.innodb_page_cleaners

在MySQL5.6中，开启了?个独?的page cleaner线程来进?刷lru list 和flush list。默认每隔?秒运??次

MySQL5.7 可设置多个page cleaner线程提?脏?刷新效率 ;

(1). 5.6版本以前，脏?的清理?作交由master线程的；

(2). Page cleaner thread是5.6.2引?的?个新线程(单线程)，从master线程中卸下buffer pool刷脏?的?作独?出来的线程(默认是启?个线程)；

(3). 5.7开始?持多线程刷脏?；

show global status like ‘%wait_free‘;如果值很?，则需要增加innodb_page_cleaners值，同时增加写线程。

innodb_page_cleaners 1 (4-16)

5.

innodb_buffer_pool_size 配置成物理内存总大小的75%

innodb_buffer_pool_instances = 1 防止FREE,LRU,FLUSH链表在高并发下发生latch冲突，因此可以配置多个innodb buffer pool实例，每个实例都有独立的链表，这样可以减少争用

此参数在1GB以下不生效，建议每个buffer pool instance的大小>2GB

建议设置:

8GB 2个

16GB 4个

32/48 6个

64GB 8个

6.

5.7支持在线修改buffer pool大小

在线修改需要注意事项

- 必须是innodb_buffer_pool_chunk_size的整数倍

- 在低业务压力时间做

在线修改buffer pool大小：

innodb_buffer_pool_chunk_size | 134217728

134217728*5=671088640

set global innodb_buffer_pool_size=671088640;

7.innodb_data_file_path 系统表空间配置参数

innodb的表空间文件的大小，ibdata1:1024M:autoextend

指定表空间为1024MB并且开启自动扩展，该表空间文件，只能自动扩展，不能自动收缩。因此需要注意磁盘的大小。

innodb_data_home_dir = /home/mysql3306/mysql3306/

innodb_data_file_path = ibdata1:512M:autoextend

innodb_data_home_dir = /home/mysql3306/mysql3306/

innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend

注意事项：autoextend只能在最后一个表空间文件

innodb_data_home_dir =

innodb_data_file_path =/home/mysql3306/mysql3306/ibdata1:512M;/home/mysql3306/mysql33061/ibdata2:512M:autoextend

8.innodb_file_per_table

是否开启独立表空间，建议开启

- 每个表都有自已独立的表空间。

- 每个表的数据和索引都会存在自已的表空间中。

- 可以实现单表在不同的数据库中移动。

- 空间可以回收mysql innodb体系结构​blog.itpub.net