mysql技术内幕 inn..._Mysql技术内幕——InnoDB存储引擎

布满荆棘的人生 2023-01-11 03:41 146阅读 0赞

一．mysql体系结构和存储引擎

1.1、数据库和实例的区别

数据库：物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm，myd，myi，ibd结尾的文件。

数据库实例：由数据库后台进程/线程以及一个共享内存区组成。数据库实例才是真正用来操作数据库文件的。

mysql数据库是单进程多线程的程序，与sql server比较类似。也就是说，Mysql数据库实例在系统上的表现就是一个进程。

1.2、mysql的体系结构

![a775bf003715933c56e4d17575b0115e.png][]

mysql由连接池组件、管理服务和工具组件、sql接口组建、查询分析器组件、优化器组件、缓存组件、插件是存储引擎、物理文件。

1.3、mysql存储引擎

1.3.1、innodb存储引擎，特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性，并且实现sql标准的4种隔离级别，同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。

1.3.2、myisam存储引擎，myisam特点是不支持事物，适合olap应用，myisam表由MYD和MYI组成。mysql-5.0版本之前，myisam默认支持的表大小为4G，从mysql-5.0以后，myisam默认支持256T的表单数据。myisam只缓存索引数据。

1.3.3、NDB存储引擎，特点是数据放在内存中，mysql-5.1版本开始可以将非索引数据放到磁盘上。NDB之前的缺陷是join查询是mysql数据库层完成的，而不是存储引擎完成的，复杂的join查询需要巨大的网络开销，速度很慢。当前mysql cluster7.2版本中已经解决此问题，join查询效率提高了70倍。

1.3.4、memeory存储引擎，将数据放到内存中，默认使用hash索引，不支持text和blob类型，varchara是按照char的方式来存储的。mysql数据库使用memory存储引擎作为临时表还存储中间结果集(intermediate result)，如果中间集结果大于memorg表的容量设置，又或者中间结果集包含text和blog列类型字段，则mysql会把他们转换到myisam存储引擎表而放到磁盘上，会对查询产生性能影响。

1.3.5、archive存储引擎，压缩能力较强，主要用于归档存储。

1.3.6、federated存储引擎，不存储数据，他指向一台远程mysql数据库上的表。

1.3.7、maria存储引擎，myisam的后续版本,支持缓存数据和索引，行锁设计，支持mvcc，支持事务和非事务安全的选项，以及更好的BLOG字符类型的处理性能。

1.3.8、其他存储引擎，sphinx用于全文索引，infobright用于数据仓库。

1.4连接Mysql

1.4.1、TCP/IP：基于网络的连接，连接进行权限检查。

1.4.2、命名管道和共享内存：Windows系统上同一服务器上的两进程可通过命名管道连接，需在配置文件中启用--enable-named-pipe选项。

1.4.3、Unix套接字：客户端与服务端位于同一服务器时才可使用，可以在my.cnf中指定-socket=/tmp/mysql.sock，连接时指定./mysql -S/tmp/mysql.sock。

二．InnoDB存储引擎

2.2、innodb引擎架构

InnoDB的多个内存块组成了内存池，负责如下工作：

1).维护所有进程/线程需要访问的多个内部数据结构。

2).缓存磁盘上的数据，方便快速的读取，并且在对磁盘文件的数据进行修改之前在这里缓存。

3).重做日志缓存。

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据，此外、将已经修改的数据文件刷新到磁盘文件

2.2.1、后台线程

innodb存储引擎后台有7个线程，—–4个IO线程(insert buffer thread,log thread,read thread,write thread)，1个master thread，一个lock监控线程，一个错误监控线程。

2.2.2、内存

innodb存储引擎内存由以下三个部分组成：缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional memory pool)。可以使用 show engine innodb status来查看innodb\_buffer\_pool的使用情况。

innodb\_buffer\_pool\_size：具体看，缓冲池中的数据库类型有：索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hashindex)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)。

InnoDB工作方式：将数据文件按页(每页16K)读入InnoDBbuffer pool，然后按最近最少使用算法(LRU)保留缓存数据，最后通过一定频率将脏页刷新到文件。

![72661c8253afa7843060a0e4357fc052.png][]

2.3、master thread

2.3.1、master thread源码分析

![6676c9261a908bb503ec901476d21314.png][]

2.3.2、master thread的潜在问题

1、由于硬件的发展，现在的硬件性能已经提高了很多，如果innodb每秒最大刷新100个脏页，那么效率会很低，为了解决这个问题，innodb plugin提供了一个参数innodb\_io\_capacity，用来表示磁盘IO的吞吐量，默认值是200，规则如下：在合并插入缓存时，合并插入缓存的数量为innodb\_io\_capacity的5%；在从缓冲区刷新脏页时，啥新脏页的数量为innodb\_io\_capacity。

2、关于innodb\_max\_dirty\_pages\_pct值的争议，如果值过大，内存也很大或者服务器压力很大，那么效率很降低，如果设置的值过小，那么硬盘的压力会增加，建议是在75-80.并且innodb plugin引进了innodb\_adaptive\_flushng(自适应的刷新)，该值影响每秒刷新脏页的数量。

2.4、关键特性,为innodb提高性能的技术

2.4.1、插入缓存

当一个表有非聚集索引时，对于非聚集索引的叶子节点的插入不是顺序的，这时候需要离散的访问非聚集索引页，性能就在这里降低了，这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。

对于非聚集索引的插入和更新操作，不是每一次都直接插入索引页，而是先判断插入的非聚集索引页是否在缓存中，如果在就直接插入，如果不在就放入到一个插入缓冲区中，好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。

插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引)：索引是辅助索引；索引不是唯一的。

2.4.2、两次写

两次写给innodb带来的是可靠性，主要用来解决部分写失败(partial page write)。在应用重做日之前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。

doublewrite有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另外一部分就是物理磁盘上的共享表空间中联系的128个页，即两个区，大小同样为2M。当缓冲池的张也刷新时，并不直接写硬盘，而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。

2.4.3、自适应哈西索引

由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。

2.5、启动、关闭、恢复

innodb\_fast\_shutdown影响InnoDB表关闭。该参数有0、1、2三个参数。

0 MySQL关闭时  完成所有的full purge和merge insertbuffer操作

1默认值 只将缓冲池内的一些脏页刷新至磁盘

2将日志都写入日志文件不会有任何事务丢失但下次启动时会进行recovery

innodb\_force\_recovery影响整个innodb存储引擎的恢复状况，该值默认为0，表示当需要恢复时，需要执行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，mysql数据库可能宕机，并把错误写入错误日志中。

三．文件

3.1参数文件

Mysql实例可以不需要参数文件，这是所有的参数值取决于编译Mysql时指定的默认值和源代码中指定参数的默认值。其参数文件是Mysql.cnf。

3.1.1、什么是参数

参数是一个键/值对。可以使用show variables like命令查看，也可以通过information\_schema的GLOBAL\_VARIABLES视图来查找。

3.1.2、参数类型

参数文件分为两类：动态参数和静态参数。动态参数意味着你可以在Mysql实例运行中进行更改；静态参数说明在整个实例生命周期内都不得进行更改，好像是只读的。对于动态参数，又可以分为global和session关键字，表明该参数的修改是基于当前会话还是真格实例的生命周期。有些动态参数只能在会话中进行修改，如autocommit；有些参数修改完后，在整个实例生命周期中都会生效，如binlog\_cache\_size；而有些参数既可以在会话又可以在整个实例的生命周期内生效，如read\_buffer\_size。

3.2、日志文件

3.2.1、错误日志

错误日志对Mysql的启动、运行、关闭过程进行了记录。出现Mysql不能正常启动时，第一个必须查找的文件应该就是错误日志文件。使用show variables like ‘log\_error’来定位文件。

3.2.2、慢查询日志

慢查询能为SQL语句的优化带来很好的帮助。设定一个阀值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。用参数long\_query\_time来设置。另一个参数log\_queries\_not\_using\_indexes，若运行的SQL语句没有使用索引，则这条SQL语句会被记录下来。

3.2.3、查询日志

查询日志记录了所有对Mysql请求的信息，不论这些请求是否得到正确的执行。默认文件名为：主机名.log。

3.2.4、二进制日志

二进制记录了对数据库执行更改的所有操作，但是不包括SELECT和SHOW操作，还包括了执行时间和更改操作时间。可用来恢复某些数据，同时也可以用来复制同步远程数据库。将binlog\_format设置成row，可以支持事务隔离级别为READ COMMITTED，以获得更好的并发性。在使用MIXED格式下，mysql采用STATEMENT格式进行二进制日志文件的记录，但是有一些情况下会使用ROW格式，可能的情况如下：

1、表的存储引擎为NDB，这个时候DML操作都会以ROW格式记录。

2、使用了uuid()、user(),current\_user(),found\_rows(),row\_count(),等不确定函数。

3、使用了insert delay语句

4、使用了用户定于的函数(UDF)

5、使用了临时表(temporary table)

注意：针对系统库mysql里面的表发生变化的处理规则如下：

1、 如果采用insert，update，delete直接操作表，则日志根据binlog\_format设定的格式记录。

2、 如果使用grant，revoke，set password等DCL语句，那么无论如何都会使用SBR模式记录。

3、 blockhole引擎不支持row格式，ndb引擎不支持statement格式。

3.3、套件字文件

Unix系统下本地连接Mysql可以采用Unix套接字方法，需要一个套接字文件，可以使用show variableslike ‘socket’查询。

3.4、pid文件和表结构定义文件

pid文件是实例启动是记录自己进程ID号的文件，表结构定义文件是以frm为后缀名的文件，还可以用来存放视图的定义。

3.5、innodb引擎文件

3.5.1、表空间文件

默认表空间文件为ibdata1文件innodb\_data\_file\_path存储数据，innodb\_file\_per\_table可以按表分别产生一个表空间.db文件，但仅存该表的数据索引和插入缓冲等信息，其他信息如undo信息，系统事务信息，double write buffer等还是存放在默认表空间(ibdata1或表空间组)里。

3.5.2、重做日志文件

redo log是在实例或者介质失败的时候，用来保证数据完整性。每个innodb存储引擎至少有一个重做日志组,每个重做日志文件组下至少又2个重做日志文件，如默认的ib\_logfile0、ib\_logfile1.为了得到更高的可靠性，你可以设置多个重做镜像日志组。

因为重做日志条目先被写到日志缓冲中，然后根据一定条件刷新到磁盘重做日志文件中。与redo log相关的就是innodb\_flush\_log\_at\_trx\_commit的值，对innodb的性能影响很大。他有0，1，2三个值，0代表提交事务时，并不同步写redo log，而是等master threas每秒写。1代表commit的时候就将redo log缓存写入磁盘，2代表commit的时候将redo log缓存异步的写入磁盘。

[a775bf003715933c56e4d17575b0115e.png]: /images/20221119/f0e5dba2d4994c99a80c08c965ad5e4b.png
[72661c8253afa7843060a0e4357fc052.png]: /images/20221119/2cf6ef7e15a2410fa663977a90fe9711.png
[6676c9261a908bb503ec901476d21314.png]: https://img-blog.csdnimg.cn/img_convert/6676c9261a908bb503ec901476d21314.png