特此申明:
前段时间找工作所以看了《Mysql技术内幕InnoDB存储引擎》,整理的时候除了参考网上已有的笔记贴,加上自己整合的,可能和别人有雷同之处。不过无所谓啦,写出来自己看看,需要的朋友参考下,仅此而已。
一.mysql体系结构和存储引擎
1.1、数据库和实例的区别
数据库:物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm,myd,myi,ibd结尾的文件。
数据库实例:由数据库后台进程/线程以及一个共享内存区组成。数据库实例才是真正用来操作数据库文件的。
mysql数据库是单进程多线程的程序,与sql server比较类似。也就是说,Mysql数据库实例在系统上的表现就是一个进程。
1.2、mysql的体系结构
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套接字:客户端与服务端位于同一服务器时才可使用,可以在f中指定-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)保留缓存数据,最后通过一定频率将脏页刷新到文件。
2.3、master thread
2.3.1、master thread源码分析
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时指定的默认值和源代码中指定参数的默认值。其参数文件是f。
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缓存异步的写入磁盘。
如果觉得《Mysql技术内幕InnoDB存储引擎——InnoDB存储引擎》对你有帮助,请点赞、收藏,并留下你的观点哦!