概述

MySQL 在整体架构上分为 Server 层和存储引擎层。

其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

单点（Single），适合小规模应用，复制（Replication），适合中小规模应用，集群（Cluster），适合大规模应用。

一 MySQL存储引擎与InnoDB体系架构介绍

存储引擎: 称为表类型（说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法）

InnoDB和MyISAM是在使用MySQL最常用的两个表类型。

MySQL用得比较多的就三种存储引擎:MylSAM、InnoDB、MEMORY

。

MySQL 5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。MySQL 8.0以后废弃了MylSAM。

1.1区别MyISAM，InnoDB，MEMORY

1.1.1 MyISAM

MyISAM：是5.5以前默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

这种存储引擎不支持事务，不支持行级锁（支持表锁），只支持并发插入的表锁，主要用于高负载的select。

注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

1.1.2 InnoDB：

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，支持行级锁、使用了B+Tree索引、支持自动增长列，支持外键约束。用于事务处理应用程序，具有众多特性，包括ACID事务支持。(提供行级锁)，5.5以后默认使用InnoDB存储引擎。存储形式为：.frm 表定义文件 .ibd 数据文件

1.1.3 Memory

· Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

使用存在于内存中的内容来创建表。

每个memory表只实际对应一个磁盘文件，格式是.frm，该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。因为它的数据是放在内存中的，但是一旦服务关闭，表中的数据就会丢失掉。

存储引擎默认使用哈希( HASH )索引,其速度比使用B-+Tree型要快。

Hash索引结构:其检索效率非常高,索弓|的检索可以一次定位。

B-Tree索引:需要从根节点到枝节点,最后才能访问到页节点这样多次的I0访问。

所以Hash索弓|的查询效率要远高于B-Tree索引。

虽然Hash索引效率高,但是Hash索引本身由于其特殊性也带来了很多限制和弊端,功能有限,支持也有限。

· BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

· Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

· Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

· Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

· Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

· Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

1.2 InnoDB存储引擎-内存结构

1.2.1 InnoDB Buffer Pool

InnoDB Buffer Pool :不仅仅缓存索引数据,还会缓存表的数据,而且完全按照数据文件中的数据快结构信息来缓存,这一点和Oracle SGA中的database buffer cache非常类似。所以，InnoDB Buffer Pool对InnoDB存储引擎的性能影响之大就可想而知了。

需要说明index page 即包括索引也包括数据（数据记录缓存）

Insert buffer 主要是内存写磁盘，index page主要是解决读磁盘，缓存数据。

1.2.2 Additional Memory Pool: ;

其参数innodb_ additional mem_ pool_ size 是InnoDB用来保存数据字典信息和其他内部数据结构的内存池的大小,单位是byte ,参数默认值为8M。数据库中的表数量越多,参数值应该越大,如果InnoDB用完了内存池中的内存,就会从操作系统中分配内存，同时在error log中打入报警信息,这个参数以后会被弃用。

1.2.3 redo buffer

innodb_ log_ _byffer_ size的大小: (默认8M)

将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中的3种情况:

1、Master Thread每一秒将重做日志缓冲刷新到重做日志文件 ;

2、每个事务提交时会将重做日志缓冲刷新到重做日志文件;

3、当重做日志缓冲池剩余空间小于1/2时 ,重做日志缓冲刷新到重做日志文件。

1.2.4 二进制日志缓冲区( Binlog Buffer )

主要用来缓存由于各种数据变更操做所产生的Binary Log信息。为了提高系统的性能，MySQL并不是每次都是将二进制日志直接写入Log File，而是先将信息写入BinlogBuffer中，当满足某些特定的条件之后再一次写入Log File文件中。

二进制日志和重做日志的对比3 :

类型

二进制日志:记录MySQL数据库相关的日志记录，包括InnoDB , MyISAM等其它存储引擎的日志。重做日志:只记录InnoDB存储弓|擎本身的事务日志。

内容

二进制日志:记录事务的具体操作内容,是逻辑日志。重做日志:记录每个页的更改的物理情况。

时间

二进制日志:只在事务提交完成后进行写入,只写磁盘一次,不论这时事务量多大。

重做日志:在事务进行中,就不断有重做日志条目(redo entry)写入重做日志文件。

1.2.5 Doube Write :

Doube Write :

是innodb表空间ibdata中一块连续的128 page=2M的存储空间，它的作用的是处理产生partial write时候的data recovery。

比如:如果发生了极端情况(断电)，InnoDB再次启动后，发现了一个Page数据已经损坏那么此时就可以从doublewrite buffer中进行数据恢复了。

它的主要工作原理:

A . dirty page刷新到数据文件之前，先刷到double write buffer里。

B .然后将page内容刷新到数据文件中。

1.2.6 二进制日志和重做日志的对比 :

1类别

重做日志:只记录InnoDB存储引擎本身的事务日志。

二进制日志:记录MySQL数据库相关的日志记录，包括InnoDB，MyISAM等其它存储引擎的日志。

2内容

二进制日志:记录事务的具体操作内容，是逻辑日志。

重做日志:记录每个页的更改的物理情况。

3时间

二进制日志∶只在事务提交完成后进行写入，写磁盘一次，不论这时事务量多大。

重做日志:在事务进行中，就不断有重做日志条目(redo entry)写入重做日志文件。

1.3 InnoDB存储引擎-逻辑存储结构

Oracle是表空间、段、区、块

MySQL是表空间、段、区、页

表空间:所有的数据都放在表空间里面。

段:表空间有若干各段组成，常见的有数据段/索引|段/回滚段等

区:每64个连续的页组成区，因此区大小正好为1M。

页:页是InnoDB磁盘管理的最小单位,固定大小为16K。

行: InnoDB表中数据按行存储。

1.3.1表空间

表空间:所有数据都是存放在表空间中的, 启用了参数innodb_file_per_table ,则每张表内的数据可以单独放到一个表空间中,每张表空间内存放的只是数据,索引和插入缓冲，其他类的数据,如undo信息，系统事务信息，二次写缓冲等还是存放在原来你的共享表空间。

1.3.2段(segment)

段(segment) :常见的segment有数据段、索引段、回滚段。innodb是索引聚集表，所以数据就是索引,索引就是数据,那么数据段即是B+树的页节点(leaf node segment) ,索|段即为B+树的非索引节点(non-leaf node segment) ,而且段的管理是由引擎本身完成的。

1.3.3区(extend)

区(extend)：区是由64个连续的页主成,每个页大小为16K，即每个区的大小为(64* 1 6K)=1MB,对于大的数据段，mysql每次最多可以申请4个区,以此保证数据的顺序性能。

1.3.4页(page)

页(page)页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。

常见的类型有:

数据页B-tree Node ;

undo页Undo Log Page ;

系统页System Page ;

事务数据页Transaction system Page ;

插入缓冲位图页Insert Buffer Bitmap ;

插入缓冲空闲列表页Insert Buffer freeBitmap ;

未压缩的二进制大对象页Uncompressed BLOB Page ;

压缩的二进制大对象页Compressed BLOB Page.

1.3.5 行

行:innodb存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页最多可以存放16K/2 ~ 200行,也就是7992个行。

二 InnoDB buffer pool原理与配置

数据在内存中，读取就快，读硬盘特别慢。

2.1 buffer pool 参数

show variables like '%innodb_buffer_pool_size%';

2.1.1 innodb_buffer_pool_instances与innodb_buffer_pool_size参数分析

innodb_buffer_pool_size（缓冲池大小）

innodb_buffer_pool_chunk_size（定义InnoDB缓冲池大小调整操作的块大小）

innodb_buffer_pool_instances（InnoDB 缓冲池划分为的区域数-可以并发提高性能）

innodb_buffer_pool_size必须始终等于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的倍数。

1 innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数。

除32位Windows系统的其它所有平台上innodb_buffer_pool_instances参数的默认值为：

innodb_buffer_pool_size<1G时，默认值为1；

innodb_buffer_pool_size>1G时，默认值为8。

2、在Linux平台上，大于或等于1GB 时，默认值为8 。否则，默认值为1。

innodb_buffer_pool_instances参数的作用，要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为大于1（默认）的值，最大为64（最大）。仅当您将innodb_buffer_pool_size大小设置为1GB或更大时，此选项才生效。您指定的总大小将分配给所有缓冲池。为了获得最佳效率，指定的组合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。

3 innodb_buffer_pool_instanes的值最大为64，innodb_buffer_pool_instances 和 innodb_buffer_pool_size的组合，每个缓冲池实例至少为1GB。

1 MySQL企业用户的实际环境（大内存）：

1、在专用数据库服务器上，可以将innodb_buffer_pool_size设置为计算机物理内存大小的80％;

2、在innodb_buffer_pool_size设置比较大的情况下，可以将innodb_buffer_pool_instances的值设置为8-16，或者CPU的个数，保证一个pool 10G以上。

（注意innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数）

2.2 buffer pool原来与内部结构：

InnoDB缓冲池是通过LRU算法来管理page的。频繁使用的page放在LRU列表的前端，最少使用的page在LRU列表的尾端，缓冲池满了的时候，优先淘汰尾端的page。

InnoDB在内存中维护一个缓存池用于缓存数据和索引。缓存池可以被认为一条长LRU链表，该链表又分为2个子链表，一个子链表存放old pages(里面存放的是长时间未被访问的数据页)，另一个子链接存放new pages（里面存放的是最近被访问的数据页面）。old pages 默认占整个列表大小的37%（InnoDB_old_blocks_pct参数的默认值为37，取值范围是5~95），其余为new pages占用，如图下图所示。靠近LRU链表头部的数据页表示最近被访问，靠近LRU链表尾部的数据页表示长时间未被访问，而这两个部分交汇处成为midpoint。

这个页第1次读取的时候，该页先放到MID point的位置;

当被读到的第2次，才将这个页放到newpage的首部。

MID point > new page

MID point > old page > new page

MIDpoint>oldpage>刷回磁盘

MID point > newpage>oldpage>刷回磁盘

show variables like 'innodb_old%';可以查看InnoDB缓冲池结构的参数信息。

innodb_old_blocks_pct:控制old page子链表在LRU链表中的长度。

innodb_old_blocks_time:控制old page子链表的数据页移动到new page 子链表中的时机。

nnodb_old_blocks_pct参数是控制进入到sublist of old blocks区域的数量，初始化默认是37.

innodb_old_blocks_time参数是在访问到sublist of old blocks里面数据的时候控制数据不立即转移到sublist of new blocks区域，而是在多少微秒之后才会真正进入到new区域，这也是防止new区域里面的数据不会立即被踢出。

2.3 LRU list、free list、flush list

A.free list:启动时，有多个16K的空白页，这些页就存在free list中。

B.LUR list :当读取-个数据页的时候，就从free list中取出一个页，存放数据，并将这个页放入到LUR list。

C. flush list: 当LUR list中的页第一次被修改时，就将页的指针(page number)放到flush list（只要被修改过，无论改了多少次），就将页的指针(page number)放到flush list。

这个页第1次读取的时候，该页先放到MID point的位置;

当被读到的第2次，才将这个页放到newpage的首部。

free list > LUR list>flush list>磁盘> free list

show engine innodb status -来观察LRU列表及Free列表的状态。

show engine innodb status\G

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4395630592;

Dictionary memory allocated 28892957

Buffer pool size 262143

Free buffers 0

Database pages 258559

Old database pages 95424

Modified db pages 36012

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 72342127, not young 0

8.82 youngs/s, 0.00 non-youngs/s

Pages read 72300801, created 339791, written 13639066

8.56 reads/s, 0.35 creates/s, 3.79 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 258559, unzip_LRU len: 0

I/O sum[459]:cur[1], unzip sum[0]:cur[0]

Total memory allocated 4395630592;---总分配mysql内存

Dictionary memory allocated 28892957---数据字典内存

Buffer pool size表示缓冲池共有262143个page，即262143 * 16K/1024/1024，约为4GB

Free buffers表示当前Free列表中page的数量

Database pages表示LRU列表中page的数量

Old database pages表示LRU列表中old部分的page数量

Modified db pages表示的是脏页(dirty page)的数量

Pages made young表示LRU列表中page移动到new部分的次数

youngs/s, non-youngs/s表示每秒这两种操作的次数

Buffer pool hit rate表示缓冲池的命中率，该值若小于95%，需要观察是否全表扫描引起LRU污染

LRU len表示LRU中总page数量

可以看到Free buffers与Database pages的和不等于Buffer pool size，这是因为缓冲池中的页还会被分配给自适应哈希索引，Lock信息，Insert Buffer等页，这部分页不需要LRU算法维护。

脏页(dirty page)

LRU列表中的page被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时InnoDB通过Checkpoint机制将脏页刷新回磁盘。而Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，又存在于Flush列表中，二者互不影响。Modified db pages显示的就是脏页的数量。

2.4 buffer pool 预热

mysql 5.6 <每次启动buffer pooL中页是空的，每次都需要大量的时间加载新的页到内存中，启动后有一段时间性能差。

mysql 5.6 >每次停机会dump出buffer poo l的数据( SPACE, page number)，然后启动时load进buffer pool,预热。

MySQL服务启动一段时间后，InnoDB会将经常访问的数据（包括业务数据，管理数据）置入InnoDB缓冲池中，即InnoDB缓冲池保存的是频繁访问的数据（简称热数据）。当InnoDB缓冲池的大小是几十GB甚至是几百GB时，由于某些原因（例如数据库定期维护）重启MySQL服务，如何将之前InnoDB缓冲池中的热数据重新加载到InnoDB缓冲池中？简单地说：如何对InnoDB缓冲池进行预热，以便于MySQL服务器快速地恢复到重启MySQL服务之前的性能状态？

innodb缓冲池预热功能可以加载磁盘上dump下来的buffer信息到内存buffer pool中，这个功能可以加快业务查询（指任何关联该数据的操作，不仅限于select）速度。如果关闭该功能，在数据库刚打开时，内存是空的，没有加载任何业务数据，初始连接的业务基本上都需要从物理磁盘中读取数据到内存中。

show variables like '%buffer%pool%';

+-------------------------------------+----------------+

| Variable_name | Value |

+-------------------------------------+----------------+

| innodb_buffer_pool_chunk_size | 134217728 |

| innodb_buffer_pool_dump_at_shutdown | ON |

| innodb_buffer_pool_dump_now | OFF |

| innodb_buffer_pool_dump_pct | 40 |

| innodb_buffer_pool_filename | ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| innodb_buffer_pool_load_abort | OFF |

| innodb_buffer_pool_load_at_startup | ON |

| innodb_buffer_pool_load_now | OFF |

| innodb_buffer_pool_size | 1073741824 |

+-------------------------------------+----------------+

innodb_buffer_dump_at_shutdown：默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_at_startup:默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中。

innodb_buffer_pool_load_now:默认为关闭OFF。如果开启该参数，启动MySQL服务时，以手动方式将本地热数据加载到InnoDB缓冲池。

innodb_buffer_pool_dump_now默认为关闭OFF。如果开启该参数，停止MySQL服务时，以手动方式将InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

innodb_buffer_pool_dump_pct

#关闭mysql服务时，转储活跃使用的innodb buffer pages的比例，默认25%；配合innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown 两个参数同时使用#

#如果启用新的参数比如40 ，每个innodb buffer pool instance中有100个，每次转储每个innodb buffer 实例中的40个pages#

innodb_buffer_pool_filename | ib_buffer_pool

指定本地缓存文件名字

2.5 buffer pool调整

mysql 5.7 <不能在线调整，需要重启才生效。

mysql 5.7 >可以在线调整，需要改my. cnf后重启永久生效。

什么时候需要调整:

1)机器增加物理内存

2)性能原因或历史原因，需要调整。

不要在业务繁忙时间调整，尽量在非业务时间。

调整时，会按块的方式去调整和移动单位是chunk 128M，innodb_ buffer_pool_ chunk_ size

调整innodb_buffer_pool_size大小

show variables like '%innodb_buffer_pool_size';

比如服务器128G内存*0.8=102G。设置不大于80%。

select 100*1024*1024*1024 from dual;

+--------------------+

| 100*1024*1024*1024 |

+--------------------+

| 107374182400 |

+--------------------+

1 row in set (0.00 sec)

我的环境是3G，我现在先调整由1G到2G

mysql> set global innodb_buffer_pool_size=2048M;

ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

mysql> set global innodb_buffer_pool_size=2G;

ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

mysql> select 2*1024*1024*1024 from dual;

2147483648

set global innodb_buffer_pool_size=2147483648;

Query OK, 0 rows affected (0.00 sec)

show variables like '%innodb_buffer_pool_size';

+-------------------------+------------+

| Variable_name | Value |

+-------------------------+------------+

| innodb_buffer_pool_size | 2147483648 |

+-------------------------+------------+

1 row in set (0.00 sec)

在修改Mysql参数文件-（就可以永久生效了，重启也不怕）

innodb_buffer_pool_size = 2G

更加直观

select * from information_schema.INNODB_BUFFER_pool_stats;

三 InnoDB存储引擎的常用参数配置

3.1 启用innodb存储引擎

默认就是InnoDB

show variables like 'default_stor%';

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| default_storage_engine | InnoDB |

+------------------------+--------+

1 row in set (0.00 sec)

在my.cnf加入参数即可，重启mysql服务

default-storage-engine=INNODB

3.2 INNODB重要参数

show variables like '%innodb%';

3.2.1内存 innodb_buffer_pool_size(类似ORACLE的SGA) 需要设置

用户innodb数据和索引的缓存，默认128M，innodb最重要的性能参数，建议值不超过80%，一般是75%。

(如果数据量小，可以是数据量+10%，数据量20G，物理内存是32G，这时候可以设置buffer pool为22G)

show variables like '%innodb_buffer_pool_size%';

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| innodb_buffer_pool_size | 536870912 |

+-------------------------+-----------+

1 row in set (0.00 sec)

在my.cnf加入参数即可，重启mysql服务

default-storage-engine=INNODB

innodb_buffer_pool_size=256G

innodb_buffer_pool_size=512M

查看缓冲池命中率

show engine innodb status\G

Buffer pool hit rate 1000 / 1000 ##缓冲池命中率，它度量自上次Innodb状态输出后到本次输出这段时间内的命中率。

1 MySQL企业用户的实际环境（大内存）：

1、在专用数据库服务器上，可以将innodb_buffer_pool_size设置为计算机物理内存大小的80％;

2、在innodb_buffer_pool_size设置比较大的情况下，可以将innodb_buffer_pool_instances的值设置为8-16，或者CPU的个数，保证一个pool 10G以上。

（注意innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数）

3.2.2 innodb_buffer_pool_instances与innodb_buffer_pool_size参数分析

innodb_buffer_pool_size（缓冲池大小）

innodb_buffer_pool_chunk_size（定义InnoDB缓冲池大小调整操作的块大小）

innodb_buffer_pool_instances（InnoDB 缓冲池划分为的区域数-可以并发提高性能）

1 innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数。

除32位Windows系统的其它所有平台上innodb_buffer_pool_instances参数的默认值为：

innodb_buffer_pool_size<1G时，默认值为1；

innodb_buffer_pool_size>1G时，默认值为8。

2、在Linux平台上，大于或等于1GB 时，默认值为8 。否则，默认值为1。

3 innodb_buffer_pool_instanes的值最大为64，innodb_buffer_pool_instances 和 innodb_buffer_pool_size的组合，每个缓冲池实例至少为1GB。

1 MySQL企业用户的实际环境（大内存）：

1、在专用数据库服务器上，可以将innodb_buffer_pool_size设置为计算机物理内存大小的80％;

2、在innodb_buffer_pool_size设置比较大的情况下，可以将innodb_buffer_pool_instances的值设置为8-16，或者CPU的个数，保证一个pool 10G以上。

（注意innodb_buffer_pool_size必须为 innodb_buffer_pool_instances 的倍数）

3.2.3 日志innodb_log_buffer_size（日志缓冲区）

默认是16M就够了。

show variables like '%innodb_log_buffer_size%';

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

| innodb_log_buffer_size | 16777216 |

+------------------------+----------+

1 row in set (0.00 sec)

3.2.4 日志文件大小innodb_log_file_size 需要设置

指定重做日志大小,数据库挂了以后的操作。

5.5以前最大是4G，5.6>512G。

小业务256M够了，中型业务一般保持在1G左右，大型业务一般在2G。

innodb log顾名思义：即innodb存储引擎产生的日志，也可以称为重做日志文件，默认在innodb_data_home_dir下面有两个文件ib_logfile0和ib_logfile1。MySQL官方手册中将这两个文件叫文InnoDB存储引擎的日志文件；

innodb log的作用：当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性；

innodb log的写原理：

看红色框框的那部分

每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1；

日志组中每个重做日志的大小一致，并循环使用；

InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；

为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面；

（发现以上特性跟ORACLE的连接重做日志文件简直是一样的）

二、innodb log的相关参数

运行脚本：show variables like 'innodb%log%'; 查看重做日志的相关参数

mysql> show variables like 'innodb%log%';

常用设置的参数有：

innodb_mirrored_log_groups 镜像组的数量，默认为1，没有镜像；

innodb_log_group_home_dir 日志组所在的路径，默认为data的home目录；

innodb_log_files_in_group 日志组的数量，默认为2；

innodb_log_file_size 日志组的大小,默认为5M；

innodb_log_buffer_size 日志缓冲池的大小，图上为30M;

三、参数的相关调优

3.1 重做日志文件的大小设置跟ORACLE一样，面临的问题是相似的。

当innodb log设置过大的时候，可能会导致系统崩溃后恢复需要很长的时间；

当innodb log设置过小的时候，当一个事务产生大量的日志的时候，需要多次切换重做日志文件，会产生类似如下的报警；

130702 12:53:13 InnoDB: ERROR: the age of the last checkpoint is 2863217109,

InnoDB: which exceeds the log group capacity 566222311.

InnoDB: If you are using big BLOB or TEXT rows, you must set the

InnoDB: combined size of log files at least 10 times bigger than the

四、重做日志与二进制日志的区别

4.1 记录的范围不同：二进制日志会记录MySQL的所有存储引擎的日志记录（包括InnoDB、MyISAM等)，

而InnoDB存储引擎的重做日志只会记录其本身的事务日志。

4.2 记录的内容不同：二进制日志文件记录的格式可以为STATEMENT或者ROW也可以是MIXED，其记录的都是关于一个事务的具体操作内容。

InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况。

4.3 写入的时间也不同：二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入到重做日志文件中。

show variables like '%innodb_log_file%';

+---------------------------+-----------+

| Variable_name | Value |

+---------------------------+-----------+

| innodb_log_file_size | 209715200 |

| innodb_log_files_in_group | 2 |

+---------------------------+-----------+

2 rows in set (0.00 sec)

3.2.5事务刷新 innodb_flush_log_at_trx_commit

(控制事务的提交方式，控制日志刷新到硬盘的方式)

show variables like '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

1 row in set (0.00 sec)

有3个值:0,1,2默认是1

0:每秒1次写入到log file中，同时会进行文件系统到磁盘的同步操作，但每个事务的提交不会从log buffer到log file。速度快，不安全，出现故障会丢失一秒的事务，比如游戏数据库建议设置为0，不会触发文件系统到磁盘的同步。

1：每个事务的提交commit会从log buffer到LOG file。同时触发文件系统到磁盘的同步操作，同时触发文件系统到磁盘的同步操作。最安全。

2：每个事务的提交commit会从log buffer到LOG file，不会触发文件系统到磁盘的同步。不会触发文件系统到磁盘的同步。但是每秒会有一次文件系统到磁盘的同步。

3.2.6 innodb_flush_method

lfinnodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default lf innodb_flush_method is set to NULL on Windows, the async_unbuffered option is used by default.

show variables like '%innodb_flush_method%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| innodb_flush_method | |

+---------------------+-------+

1 row in set (0.00 sec)

3.2.7 INNODB Doube Write :

Doube Write :

是innodb表空间ibdata中一块连续的128 page=2M的存储空间，它的作用的是处理产生partial write时候的data recovery。

比如:如果发生了极端情况(断电)，InnoDB再次启动后，发现了一个Page数据已经损坏那么此时就可以从doublewrite buffer中进行数据恢复了。

它的主要工作原理:

A . dirty page刷新到数据文件之前，先刷到double write buffer里。

B .然后将page内容刷新到数据文件中。

默认就是开启

show variables like '%double%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| innodb_doublewrite | ON |

+--------------------+-------+

3.2.8 innodb_thread_concurrency 需要设置

INNODB存储引擎中允许的最大的线程并发数。默认为0，表示不被限制。

该值建议设置如下:

当并发用户线程数量小于64，建议设置innodb_thread_concurrency=0；

在大多数情况下，最佳的值是小于并接近虚拟CPU的个数；

show variables like '%thread%';

innodb_thread_concurrency 64

3.2.9系统表空间与临时表空间路径

show variables like '%innodb%data%file%';

+----------------------------+----------------------------------------------------------+

| Variable_name | Value |

+----------------------------+----------------------------------------------------------+

| innodb_data_file_path | ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G |

| innodb_temp_data_file_path | ibtmp1:200M:autoextend:max:20G

ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G

ibtmp1:1G:autoextend:max:20G

四 MySQL参数设置

4.1 基本设置basic settings

4.1.1 max_connections连接设置

#max_connections代表数据库同时允许的最大允许连接数

#连接有两种常见状态:sleep / query

#sleep代表连接处于闲置状态

#query代表连接正处于处理任务的状态

#sleep + query连接的总量不能超过max_ connections的设置值

#否则会出现经典错误:"ERROR 1040: Too many connetcions"

show variables like 'max_connections'; ##查看目前最大连接

set global max_connections=600; ##设置最大连接

vim /mysql/data/3306/my.cnf ##永久修改

show status like '%Max_used_co%';

+---------------------------+---------------------+

| Variable_name | Value |

+---------------------------+---------------------+

| Max_used_connections | 168 |

| Max_used_connections_time | 2020-12-05 21:31:41 |

+---------------------------+---------------------+

4.1.2 thread_cache_size

优化参数thread_cache_size

thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

thread_cache_size大小的设置：

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

物理内存设置规则：通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(-->表示要调整的值) 根据物理内存设置规则如下：

1G ---> 8

2G ---> 16

3G ---> 32

>3G ---> 64

show status like 'Threads%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 1 |

| Threads_running | 1 |

+-------------------+-------+

#Threads_ connected 代表当前已经有多少连接(Sleep+Query)

#Threads _created 代表历史总共创建过多少个数据库连接

#Threads_ running 代表有几个连接正处于"工作"状态，也是目前的并发数

# Threads_cached 线程缓存中的线程数

thread_cache_size=768+

4.1.3 twait_ timeout和interactive_ timeout

#wait_timeout和interactive_timeout 空连接数据库自动关闭

#这两个参数都是空连接至超过一段时间后,数据库连接自动关闭(默认28800秒,即8小时)

#interactive_timeout针对交互式连接，wait_ timeout针对非交互式连接。

#说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

show variables like '%timeout%';

wait_timeout | 1800

interactive_timeout | 1800

1800/60=30分钟

show processlist;

mysql> show processlist;

+----+------+-----------+------+---------+------+----------+------------------+

+----+------+-----------+------+---------+------+----------+------------------+

Time 闲置时间

4.2 query 缓存参数设置

若命中率在50-70%的范围之内，则表明Query Cache的缓存效率较高。如果命中率明显小于50%，那么建议禁用（将query_cache_type设置为0(OFF)）

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。

查询缓存会跟踪查询中涉及的每个表，如果这写表发生变化，那么和这个表相关的所有缓存都将失效。

4.2.1 参数说明

mysql> show variables like '%query_cache%';

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1048576 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

1. have_query_cache 表示mysqld是否支持Query Cache。

2. query_cache_limit 表示Query Cache可以缓存的单条查询的最大结果集的大小，默认值为1MB。如果某次查询的结果集大小超过这个系统变量的值，那么Query Cache就不会缓存这次查询的结果集。

3. query_cache_min_res_unit表示MySQL为Query Cache每次分配内存的最小空间大小，也就是用于缓存查询结果的最小内存空间的大小，默认值为4KB。

4. query_cache_size 表示Query Cache可以使用的最大内存空间的大小，默认值为1MB。设置的值必须是1024的整数倍，若不是整数倍，MySQL则会自动调整降低至达到1024倍数的最大值。

5. query_cache_type 表示Query Cache的工作模式，同时也是Query Cache功能的开关，可以设置为0（OFF）、1（ON）和2（DEMAND）三种值：

0（OFF）：关闭Query Cache功能，任何情况下都不会使用Query Cache。

1（ON）：开启Query Cache功能，但是当SELECT语句中使用了SQL_NO_CACHE选项之后，将不会使用Query Cache。

2（DEMAND）：开启Query Cache功能，但是只有当SELECT语句中使用了SQL_CACHE选项之后，才会使用Query Cache。

6. query_cache_wlock_invalidate

控制当有写锁加在表上的时候，是否先让该表相关的Query Cahce失效，具有1（ON）和0（OFF）两种取值：

1（ON）：在写锁定的同时将使该表相关的所有Query Cache失效。

0（OFF）：在写锁定的同时仍然允许读取该表相关的Query Cache。

以上环境变量经常需要调整的是query_cache_limit和query_cache_min_res_unit，它们都需要根据实际业务进行相应的调整。例如，如果缓存的查询结果集大多数都小于4KB的话，则可以适当的调整query_cache_min_res_unit的值，以避免造成内存的浪费。如果查询结果集的大小又都大于1MB时，就需要调整query_cache_limit的值，避免因为结果集大小超过限制而不被缓存。

4.2.2 开启后状态查询

show status like '%Qcache%';

+-------------------------+---------+

| Variable_name | Value |

+-------------------------+---------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 1031832 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 2 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

1. Qcache_free_blocks 表示Query Cache中目前还有多少空闲的内存块。如果该值比较大，则说明Query Cache中的内存碎片可能比较多。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个较大的空闲内存块。

2. Qcache_free_memory 表示Query Cache目前空闲的内存大小。

3. Qcache_hits 表示有多少次查询在Query Cache命中。

4. Qcache_inserts 向Query Cache中插入新记录的次数，也就是查询没有命中的次数。

5. Qcache_lowmem_prunes 表示由于Query Cache的内存不足而从缓存中删除的查询结果的数量。如果这个数值在不断增长，那么一般是Query Cache的空闲内存不足（通过Qcache_free_memory判断），或者内存碎片较严重（通过Qcache_free_blocks判断）。

6. Qcache_not_cached 表示没有被缓存的查询数量。有三种情况会导致查询结果不会被缓存：其一，由于query_cache_type的设置；其二，查询不是SELECT语句；其三，使用了now()之类的函数，导致查询语句一直在变化。

7. Qcache_queries_in_cache 表示Query Cache中当前包含的查询结果数量。

8. Qcache_total_blocks 表示Query Cache中的内存块总数量。

4.2.3 常见问题

1 Query Cache命中率是什么？有什么用？

命中率低于50，需要禁用

① 可缓存查询的Query Cache命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%

② 涵盖所有查询的Query Cache命中率 = Qcache_hits / (Qcache_hits + Com_select) * 100%

若命中率在50-70%的范围之内，则表明Query Cache的缓存效率较高。如果命中率明显小于50%，那么建议禁用（将query_cache_type设置为0(OFF)）或按需使用（将query_cache_type设置为2(DEMAND)）Query Cache，节省的内存可以用作InnoDB的缓冲池。

2应当在什么条件下使用Query Cache？

实际上，并不是所有表都适合使用Query Cache。造成Query Cache失效的原因主要是相应的表发生了变更，那么就应该避免在变更频繁的表上使用Query Cache。MySQL针对Query Cache有两个专用的SQL选项：SQL_NO_CACHE和SQL_CACHE。若将query_cache_type设置为1（ON），那么通过SQL_NO_CACHE选项便能强制不使用Query Cache；若将query_cache_type设置为2（DEMAND），那么通过SQL_CACHE选项便能强制使用Query Cache。通过强制不使用Query Cache，可以让MySQL在频繁变更的表上不使用Query Cache，这样减少了内存开销，也减少了hash计算和查找的开销。

3一个查询语句在Query Cache中缓存的查询结果，在什么情况下会失效？

为了保证Query Cache中的内容与是实际数据绝对一致，当表中的数据有任何变化，包括新增、修改、删除等，都会使所有引用到该表的Query Cache缓存数据失效。

4 Query Cache碎片率是什么？有什么用？

Query Cache碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果Query Cache碎片率超过20%，则可以用FLUSH QUERY CACHE整理内存碎片；

flush query cache;

Query OK, 0 rows affected, 1 warning (0.00 sec)

5 Query Cache利用率是什么？有什么用？

Query Cache利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

Query Cache利用率在25%以下的话，说明query_cache_size设置的过大，可适当减小；Query Cache利用率在80%以上，而且Qcache_lowmem_prunes > 50的话，说明query_cache_size可能有点小，或者就是内存碎片太多。

6 如何判断Query Cache是空闲内存不足，还是内存碎片太多？

如果Qcache_lowmem_prunes值比较大，表示Query Cache的内存空间大小设置太小，需要增大。

如果Qcache_free_blocks值比较大，表示内存碎片较多，需要使用FLUSH QUERY CACHE语句清理内存碎片。

7 系统变量query_cache_min_res_unit应当设置为多大？

query_cache_min_res_unit的计算公式如下所示：

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

其中，一般不建议将Query Cache的大小（也就是query_cache_size系统变量）设置超过256MB。

4.2.4 优缺点

1. 优点

Query Cache的查询，发生在MySQL接收到客户端的查询请求、查询权限验证之后和查询SQL解析之前。也就是说，当MySQL接收到客户端的查询SQL之后，仅仅只需要对其进行相应的权限验证之后，就会通过Query Cache来查找结果，甚至都不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互。由于Query Cache是基于内存的，直接从内存中返回相应的查询结果，因此减少了大量的磁盘I/O和CPU计算，导致效率非常高。

2. 缺点

即使Query Cache的优点很明显，但是也不能忽略它所带来的一些缺点：

A查询语句的hash计算和hash查找带来的资源消耗。如果将query_cache_type设置为1（也就是ON），那么MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。虽然hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了。

B Query Cache的失效问题。如果表的变更比较频繁，则会造成Query Cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。

C查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询（因为他们的hash值会不同）。

D相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存。

4.3 Sort_Buffer_Size

每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。

1Sort_Buffer_Size 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。

2 Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

高并发可能会耗尽系统的内存资源。例如:500个连接将会消耗500*sort_buffer_size(2M)=1G

show variables like '%sort%';

+--------------------------------+-------------+

| Variable_name | Value |

+--------------------------------+-------------+

| sort_buffer_size | 33554432 | #32M

五 my.cnf参数管理

My.cnf默认/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

生产环境，一般手工指定参数文件

mysqld --defaults-file=/mysql/data/3306/my.cnf --user=mysql &

5.1参数分类

mysql参数

按服务器变量分:静态变量参数、动态变量参数

按修改级别分:

会话级别:

set session 参数名=参数值;

set @@session.参数名=参数值;

全局级别:

set global 参数名=参数值;

set @@global.参数名=参数值;

--注意:动态变量参数、会话级别，在下次重启失效，默认会去读my.cnf，如果想永久生效，还是改my.cnf文件。

5.2 如何查看mysql所有的参数

方法1 系统命令行

mysqld --verbose --help | more

方法2数据库命令查看

mysql -uroot -proot

mysql> show variables;

show variables like '%innodb%';

查innodb相关的参数

show session variables ;

show global variables ;

select @@session.autocommit;

select @@autocommit ;

select @@global.autocommit ;

5.3 官方手册

System variable 系统变成=yes，可以修改。

name :参数名

Cmd-Line:配置文件,mysqld_safe -变量名=值

option file: 配置文件,yes , mysqld_safe -变量名=值

System Var :能改,yes是系统变量

statusVar:不能改,yes是指标

VarScope:全局/会话/两者

dynamic:动态/静态

5.4 设置参数文件

永久修改-必须要修改参数文件

5.4.1 需要重启数据库-生产不推荐-需要重启

修改参数的时候，可以先看看官方稳定，比如wait_timeout，需要修改interactive_timeout。

mysql> show variables like 'wait_timeout';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout | 1800 |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'interactive_timeout';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| interactive_timeout | 1800 |

+---------------------+-------+

1 row in set (0.00 sec)

修改my.cnf文件

vim /mysql/data/3306/my.cnf

interactive_timeout = 1800

wait_timeout = 1800

[root@mysql5 ~]# service mysql restart

Shutting down MySQL.... [确定]

Starting MySQL. [确定]

show variables ##会话

show global variables ##全局变量

mysql> show variables like '%timeout%';

+-----------------------------+----------+

| Variable_name | Value |

+-----------------------------+----------+

| interactive_timeout | 1860 |

| wait_timeout | 1860 |

+-----------------------------+----------+

mysql> select @@global.wait_timeout;

+-----------------------+

| @@global.wait_timeout |

+-----------------------+

| 1900 |

+-----------------------+

1 row in set (0.00 sec)

5.4.2 不需要重启数据库-生产推荐

再数据库命令行执行，再修改配置文件。

mysql> set global interactive_timeout=1800;

Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout=1800;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%timeout%';

+-----------------------------+----------+

| Variable_name | Value |

+-----------------------------+----------+

| interactive_timeout | 1900 |

=| wait_timeout | 1900 |

+-----------------------------+----------+

mysql> select @@global.wait_timeout;

+-----------------------+

| @@global.wait_timeout |

+-----------------------+

| 1900 |

+-----------------------+

1 row in set (0.00 sec)

5.4.3 会话修改-推出会话-还原

set wait_timeout=1200;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1200 |

+----------------+

1 row in set (0.00 sec)

set session wait_timeout=1400;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1400 |

+----------------+

1 row in set (0.00 sec)

set local wait_timeout=1500;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1500 |

+----------------+

1 row in set (0.01 sec)

set @@session.wait_timeout=1600;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1600 |

+----------------+

1 row in set (0.00 sec)

5.5 设置参数

5.5.1 全局参数

set global wait_timeout=1800;

set @@global.wait_timeout=1600;

set @@ global.wait_timeout =@@ session.wait_timeout;

5.5.2 会话参数

set wait_timeout=1200;

set session wait_timeout=1400;

set @@session.wait_timeout=1600;

set local wait_timeout=1500;

set @@session.wait_timeout=@@global.wait_timeout;

5.6 参数查看

5.6.1 全局参数

select @@global.wait_timeout;

show global variables like '%timeout%';

5.6.2 会话参数

select @@wait_timeout;

select @@session.wait_timeout;

select @@local.wait_timeout;

show variables like '%timeout%';

show local variables like '%timeout%';

show session variables like '%timeout%';

转载地址：http://fabai.baihongyu.com/

你可能感兴趣的文章

python大坑：AttributeError: 'module' object has no attribute 'Workbook'