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套接字:客户端与服务端位于同一服务器时才可使用,可以在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)保留缓存数据,最后通过一定频率将脏页刷新到文件。

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时指定的默认值和源代码中指定参数的默认值。其参数文件是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缓存异步的写入磁盘。

(0)

相关推荐

  • Access数据库安装详解

    Access数据库安装详解,按照步骤一步一步来,最后成功安装Access数据库 操作方法 01 由于access没有单独的安装包,所以你必须下载office2003数据包,首先先下载office 20 ...

  • 数据库使用详解:[1]oracle11g数据库如何安装

    欢迎阅读本篇文章,oracle11g数据库如何安装:oracle11g数据库如何安装,oracle怎么安装数据库,如何安装oracle数据库,oracle数据库安装比较麻烦,下面为大家分享一下orac ...

  • 数据库使用详解:[3]SQL Developer如何配置

    SQL Developer如何配置,初次使用SQL Developer时,是与oracle11g配合使用的,但是不知道SQL Developer怎么配置,如何配置SQL Developer,SQL D ...

  • mysql数据库转移至sqlserver数据库中详解(mysql和sqlserver数据转移)

    本文所涉及的机器环境如下:Windows XP SP3,MS SQL Server 2005,MySQL Server 5.1。 一.安装MySQL ODBC驱动 为MySQL安装Connector/ ...

  • JDBC访问数据库步骤详解

    对于很多Java初学者来说,JDBC访问数据库是一个复杂的过程,本文为大家详细列举JDBC访问数据库步骤! 操作方法 01 加载注册驱动 首先通过数据库厂商网站,获得JDBC驱动文件及驱动的文档说明, ...

  • mysql免安装版配置步骤详解分享

    操作方法 01 1.准备工作 下载mysql的最新免安装版本mysql-noinstall-5.1.53-win32.zip,解压缩到相关目录,如:d:\ mysql-noinstall-5.1.53 ...

  • MySql中启用InnoDB数据引擎的方法

    MySql中启用InnoDB数据引擎的方法

  • 经验分享:mysql 5.1.51 安装教程详解

    分享:mysql 5.1.51 安装教程详解:可能会比较长,不过很详细: 操作方法 01 下载的mysql安装文件,运行 mysql-5.1.51,出现如下界面: 02 向导启动,按Next继续,有三 ...

  • mysql存储过程详解

    mysql存储过程详解 操作方法 01 通过Mysql存储过程我们可以做很多事情 1.使用了存过程,很多相似性的删除,更新,新增等操作就变得轻松了,并且以后也便于管理! 2.存储过程因为SQL语句已经 ...