MYSQL数据库mysql innodb数据库引擎解析

《MYSQL数据库mysql innodb数据库引擎解析》要点：
本文介绍了MYSQL数据库mysql innodb数据库引擎解析，希望对您有用。如果有疑问，可以联系我们。

MYSQL实例本节内容：
mysql innodb数据库引擎

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

MYSQL实例1.1、数据库和实例的区别

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

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

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

MYSQL实例1.2、mysql的体系结构

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

MYSQL实例1.3、mysql存储引擎

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)等高性能技术.

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

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

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

MYSQL实例1.3.5、archive存储引擎,压缩能力较强,主要用于归档存储.

MYSQL实例1.3.6、federated存储引擎,不存储数据,他指向一台远程mysql数据库上的表.

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

MYSQL实例1.3.8、其他存储引擎,sphinx用于全文索引,infobright用于数据仓库.

MYSQL实例1.4连接Mysql

MYSQL实例1.4.1、TCP/IP：基于网络的连接,连接进行权限检查.

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

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

MYSQL实例二．InnoDB存储引擎

MYSQL实例2.2、innodb引擎架构
www.jbxue.com
InnoDB的多个内存块组成了内存池,负责如下工作：

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

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

MYSQL实例3).重做日志缓存.

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

MYSQL实例2.2.1、后台线程

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

MYSQL实例2.2.2、内存

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

MYSQL实例innodb_buffer_pool_size：具体看,缓冲池中的数据库类型有：索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hashindex)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary).

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

MYSQL实例

MYSQL实例
2.3、master thread

MYSQL实例2.3.1、master thread源码分析
">

MYSQL实例2.3.2、master thread的潜在问题

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

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

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

MYSQL实例2.4.1、插入缓存

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

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

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

MYSQL实例2.4.2、两次写

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

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

MYSQL实例2.4.3、自适应哈西索引

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

MYSQL实例2.5、启动、关闭、恢复
innodb_fast_shutdown影响InnoDB表关闭.该参数有0、1、2三个参数.

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

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

MYSQL实例2将日志都写入日志文件不会有任何事务丢失但下次启动时会进行recovery
innodb_force_recovery影响整个innodb存储引擎的恢复状况,该值默认为0,表示当需要恢复时,需要执行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,mysql数据库可能宕机,并把错误写入错误日志中.

MYSQL实例三．文件

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

MYSQL实例3.1.1、什么是参数
参数是一个键/值对.可以使用show variables like命令查看,也可以通过information_schema的GLOBAL_VARIABLES视图来查找.

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

MYSQL实例3.2、日志文件

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

MYSQL实例3.2.2、慢查询日志
慢查询能为SQL语句的优化带来很好的帮助.设定一个阀值,将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中.用参数long_query_time来设置.另一个参数log_queries_not_using_indexes,若运行的SQL语句没有使用索引,则这条SQL语句会被记录下来.

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

MYSQL实例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实例注意：针对系统库mysql里面的表发生变化的处理规则如下：
1、 如果采用insert,update,delete直接操作表,则日志根据binlog_format设定的格式记录.
2、 如果使用grant,revoke,set password等DCL语句,那么无论如何都会使用SBR模式记录.
3、 blockhole引擎不支持row格式,ndb引擎不支持statement格式.

MYSQL实例3.3、套件字文件
Unix系统下本地连接Mysql可以采用Unix套接字方法,需要一个套接字文件,可以使用show variableslike ‘socket’查询.

MYSQL实例3.4、pid文件和表结构定义文件

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

MYSQL实例3.5、innodb引擎文件

MYSQL实例3.5.1、表空间文件
默认表空间文件为ibdata1文件innodb_data_file_path存储数据,innodb_file_per_table可以按表分别产生一个表空间.db文件,但仅存该表的数据索引和插入缓冲等信息,其他信息如undo信息,系统事务信息,double write buffer等还是存放在默认表空间(ibdata1或表空间组)里.

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

MYSQL实例因为重做日志条目先被写到日志缓冲中,然后根据一定条件刷新到磁盘重做日志文件中.与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实例就是这些了,有关mysql innodb数据库引擎的详细介绍,希望对大家有所帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!