性能优化不是一项简单的工作,但也不是复杂的难事,关键在于对InnoDB存储引擎特性的了解。如果之前各章的内容读者已经完全理解并掌握了,那就应该基本掌握了如何使InnoDB存储引擎更好地工作。本章将从以下几个方面集中讲解InnoDB存储引擎的性能问题:选择合适的CPU内存的重要性硬盘对数据库性能的影响合理地设置RAID操作系统的选择也很重要不同文件系统对数据库的影响选择合适的基准测试工具1、选择合适的CPU用户首先需要清楚当前数据库的应用类型。一般而言,可分为两大类:OLTP(OnlineTransactionProcessing,在线事务处理)和OLAP(OnlineanalyticalPro
1、复制的工作原理复制(replication)是MySQL数据库提供的一种高可用高性能的解决方案,一般用来建立大型的应用。总体来说,replication的工作原理分为以下3个步骤:主服务器(master)把数据更改记录到二进制日志(binlog)中。从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relaylog)中。从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性。复制的工作原理并不复杂,其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是,复制不是完全实时地进行同步,而是
MySQL数据库本身并不支持快照功能,因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区中,然后对该分区进行快照操作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统,Solaris的ZFS文件系统,GNU/Linux的逻辑管理器(LogicalVolumeManager,LVM)等。这里以LVM为例进行介绍,UFS和ZFS的快照实现大致和LVM相似。LVM是LINUX系统下对磁盘分区进行管理的一种机制。LVM在硬盘和分区之上建立一个逻辑层,来提高磁盘分区管理的灵活性。管理员可以通过IⅥM系统轻松管理磁盘分区,例如,将若干个磁
1、ibbackupibbackup是InnoDB存储引擎官方提供的热备工具,可以同时备份MyISAM存储引擎和InnoDB存储引擎表。对于InnoDB存储引擎表其备份工作原理如下:记录备份开始时,InnoDB存储引擎重做日志文件检查点的LSN。复制共享表空间文件以及独立表空间文件。记录复制完表空间文件后,InnoDB存储引擎重做日志文件检查点的LSN复制在备份时产生的重做日志。对于事务的数据库,如MicrosoftSQLServer数据库和Oracle数据库,热备的原理大致和上述相同。可以发现,在备份期间不会对数据库本身有任何影响,所做的操作只是复制数据库文件,因此任何对数据库的操作都是允许
二进制日志非常关键,用户可以通过它完成point-in-time的恢复工作。MySQL数据库的replication同样需要二进制日志。在默认情况下并不启用二进制日志,要使用二进制日志首先必须启用它。如在配置文件中进行设置:[mysqld]log-bin=mysql-bin对于InnoDB存储引擎只简单启用二进制日志是不够的,还需要启用一些其他参数来保证最为安全和正确地记录二进制日志,因此对于InnoDB存储引擎,推荐的二进制日志的服务器配置应该是:[mysqld]log-bin=mysql-binsync_binlog=1innodb_support_xa=1在备份二进制日志文件前,可以通过
1、mysqldumpmysqldump备份工具最初由IgorRomanenko编写完成,通常用来完成转存(dump)数据库的备份及不同数据库之间的移植,如从MySQL低版本数据库升级到MySQL高版本数据库,又或者从MySQL数据库移植到Oracle、MicrosoftSQLServer数据库等。mysqldump的语法如下:mysqldump[arguments]>filename如果想要备份所有的数据库,可以使用--all-databases选项:mysqldump--all-databases>dump.sql如果想要备份指定的数据库,可以使用--databases选项:m
对于InnodB存储引擎的冷备非常简单,只需要备份MySQL数据库的frm文件,共享表空间文件,独立表空间文件(*bd),重做日志文件。另外建议定期备份MySQL数据库的配置文件my.cnf,这样有利于恢复的操作。通常DBA会写一个脚本来进行冷备的操作,DBA可能还会对备份完的数据库进行打包和压缩,这都并不是难事。关键在于不要遗漏原本需要备份的物理文件,如共享表空间和重做日志文件,少了这些文件可能数据库都无法启动。另外一种经常发生的情况是由于磁盘空间已满而导致的备份失败,DBA可能习惯性地认为运行脚本的备份是没有问题的,少了检验的机制。正如前面所说的,在同一台机器上对数据库进行冷备是远远不够的
可以根据不同的类型来划分备份的方法。根据备份的方法不同可以将备份分为:HotBackup(热备)ColdBackup(冷备)WarmBackup(温备)HotBackup是指数据库运行中直接备份,对正在运行的数据库操作没有任何的影响。这种方式在MySQL官方手册中称为OnlineBackup(在线备份)。ColdBackup是指备份操作是在数据库停止的情况下,这种备份最为简单,一般只需要复制相关的数据库物理文件即可。这种方式在MySQL官方手册中称为OfflineBackup(离线备份)。WarmBackup备份同样是在数据库运行中进行的,但是会对当前数据库的操作有所影响,如加一个全局读锁以保
长事务(Long-Livedtransactions),顾名思义,就是执行时间较长的事务。比如,对于银行系统的数据库,每过一个阶段可能需要更新对应账户的利息。如果对应账号的数量非常大,例如对有1亿用户的表account,需要执行下列语句:UPDATEaccountSETaccount_total=account_total*(1+interest_rate)这时这个事务可能需要非常长的时间来完成。可能需要1个小时,也可能需要4、5个小时,这取决于数据库的硬件配置。DBA和开发人员本身能做的事情非常少。然而,由于事务ACID的特性,这个操作被封装在一个事务中完成。这就产生了一个问题,在执行过程中
1、在循环中提交开发人员非常喜欢在循环中进行事务的提交,下面是他们可能常写的一个存储过程:CREATEPROCEDUREload1(countINTUNSIGNED)BEGINDECLAREsINTUNSIGNEDDEFAULT1;DECLAREcCHAR(80)DEFAULTREPEAT('a',80);WHILEs<countDOINSERTINTOt1 SELECTNULL,C;COMMIT;SETs=s+1;ENDWHILE;END;其实,在上述的例子中,是否加上提交命令COMMIT并不关键。因为InnoDB存储引擎默认为自动提交,所以在上述的存储过程中去掉COMMIT,结果其实
1、MySQL数据库分布式事务InnoDB存储引擎提供了对XA事务的支持,并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactionalresources)参与到一个全局的事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交,要么都回滚,这对于事务原有的ACID要求又有了提高。另外,在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。XA事务允许不同数据库之间的分布式事务,如一台服务器是MySQL数据库的,另台是Oracle数据库的,又可能还有一台服务器是S
令人惊讶的是,大部分数据库系统都没有提供真正的隔离性,最初或许是因为系统实现者并没有真正理解这些问题。如今这些问题已经弄清楚了,但是数据库实现者在正确性和性能之间做了妥协。ISO和ANISSQL标准制定了四种事务隔离级别的标准,但是很少有数据库厂商遵循这些标准。比如Oracle数据库就不支持READUNCOMMITTED和REPEATABLEREAD的事务隔离级别。SQL标准定义的四个隔离级别为:READUNCOMMITTEDREADCOMMITTEDREPEATABLEREADSERIALIZABLEREADUNCOMMITTED称为浏览访问(browseaccess,仅仅针对事务顶的REA
由于InnoDB存储引擎是支持事务的,因此InnoDB存储引擎的应用需要在考虑每秒请求数(QuestionPerSecond,QPS)的同时,应该关注每秒事务处理的能力(TransactionPerSecond,TPS)。计算TPS的方法是(com_commit+com_rollback)/time。但是利用这种方法进行计算的前提是:所有的事务必须都是显式提交的,如果存在隐式地提交和回滚(默认auto_commit=1),不会计算到com_commit和com_rollback变量中。如:mysql>showglobalstatuslike'com_commit';+----------
以下这些SQL语句会产生一个隐式的提交操作,即执行完这些语句后,会有一个隐式的COMMIT操作DDL语句:ALTERDATABASEUPGRADEDATADIRECTORYNAME,ALTEREVENT,ALTERPROCEDURE,ALTERTABLE,ALTERVIEW,CREATEDATABASE,CREATEEVENT,CREATEINDEX,CREATEPROCEDURE,CREATETABLE,CREATETRIGGER,CREATEVIEW,DROPDATABASE,DROPEVENT,DROPINDEX,DROPPROCEDURE,DROPTABLE,DROPTRIGGER,D
在MySQL命令行的默认设置下,事务都是自动提交(autocommit)的,即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务需使用命令BEGIN、STARTTRANSACTION,或者执行命令SETAUTOCOMMIT=0,禁用当前会话的自动提交。每个数据库厂商自动提交的设置都不相同,每个DBA或开发人员需要非常明白这一点,这对之后的SQL编程会有非凡的意义,因此用户不能以之前的经验来判断MySQL数据库的运行方式。在具体介绍其含义之前,先来看看用户可以使用哪些事务控制语句。STARTTRANSACTION|BEGIN:显式地开启一个事务。COMMIT:要想使用这个语句
若事务为非只读事务,则每次事务提交时需要进行一次fsync操作,以此保证重做日志都已经写入磁盘。当数据库发生宕机时,可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能,然而磁盘的fsync性能是有限的。为了提高磁盘fsync的效率,当前数据库都提供了groupcommit的功能,即一次fsync可以刷新确保多个事务日志被写入文件。对于InnoDB存储引擎来说,事务提交时会进行两个阶段的操作:修改内存中事务对应的信息,并且将日志写入重做日志缓冲调用fsync将确保日志都从重做日志缓冲写入磁盘。步骤2)相对步骤1)是一个较慢的过程,这是因为存储引擎需要与磁盘打交道。但当有事务进行这个过程
delete和update操作可能并不直接删除原有的数据。例如,对上一小节所产生的表t执行如下的SQL语句:DELETEFROMtWHEREa=1;表t上列a有聚集索引,列b上有辅助索引。对于上述的delete操作,通过前面关于undolog的介绍已经知道仅是将主键列等于1的记录deleteflag设置为1,记录并没有被删除,即记录还是存在于B+树中。其次,对辅助索引上a等于1,b等于1的记录同样没有做任何处理,甚至没有产生undolog。而真正删除这行记录的操作其实被“延时”了,最终在purge操作中完成。purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引
1、基本概念重做日志记录了事务的行为,可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作,这时就需要undo。因此在对数据库进行修改时,InnoDB存储引擎不但会产生redo,还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了,又或者用户用一条ROLLBACK语句请求回滚,就可以利用这些undo信息将数据回滚到修改之前的样子。redo存放在重做日志文件中,与redo不同,undo存放在数据库内部的一个特殊段(segment)中,这个段称为undo段(undosegment)。undo段位于共享表空间内。用户通常对undo有这样的误解:undo用于将数据库
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redolog和undolog来完成。redolog称为重做日志,用来保证事务的原子性和持久性。undolog用来保证事务的一致性。有的DBA或许会认为undo是redo的逆过程,其实不然。redo和undo的作用都可以视为是一种恢复操作,redo恢复提交事务修改的页操作,而undo回滚行记录到某个特定版本。因此两者记录的内容不同,redo通常是物理日志,记录的是页的物理修改操作。undo是逻辑日志,根据每行记录进行记录。1、基本概念重做日志用来实现事务的持久性,即事务ACID中的D。其由两部分组成:一是内存中的重做日志缓冲(redolo
1、概述事务可由一条非常简单的SQL语句组成,也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作,要么都做修改,要么都不做,这就是事务的目的,也是事务模型区别与文件系统的重要特征之一。理论上说,事务有着极其严格的定义,它必须同时满足四个特性,通常所说的事务的ACID特性。值得注意的是,虽然理论上定义了严格的事务要求,但是数据库厂商出于各种目的,并没有严格去满足事务的ACID标准。例如,对于MySQL的NDBCluster引擎来说,虽然其支持事务,但是不满足D的要求,即持久性的要求。对于Oracle数据库来说,其默认的事务隔离级别为READCO
锁升级(LockEscalation)是指将当前锁的粒度降低。举例来说,数据库可以把一个表的1000个行锁升级为一个页锁,或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源,而且想避免锁的开销,那数据库中会频繁出现锁升级现象。MicrosoftSQLServer数据库的设计认为锁是一种稀有的资源,在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源,防止系统使用太多的内存来维护锁,在一定程度上提高了效率。即使在MicrosoftSQLServer2005版本之后,SQLServer数据库支持了行锁,但是其设计和InnoDB存储引擎完全不同,在以下情况下
1、死锁的概念死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。若无外力作用,事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待,将任何的等待都转化为回滚,并且事务重新开始。毫无疑问,这的确可以避免死锁问题的产生。然而在线上环境中,这可能导致并发性能的下降,甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重,因为这很难被发现并且浪费资源。解决死锁问题最简单的一种方法是超时,即当两个事务互相等待时,当一个等待时间超过设置的某一阈值时,其中一个事务进行回滚,另一个等待的事务就能继续进行。在InnoDB存储引擎中,参数innodb_lock_wa
因为不同锁之间的兼容性关系,在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。阻塞并不是一件坏事,其是为了确保事务可以并发且正常地运行。在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒),innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OFF,代表不回滚)。参数innodb_lock_wait_timeout是动态的,可以在MySQL数据库运行时进行调整mysql>setinnodb_lock_waittimeout=60;QueryO
通过锁定机制可以实现事务的隔离性要求,使得事务可以并发地工作。锁提高了并发,但是却会带来潜在的问题。不过好在因为事务隔离性的要求,锁只会带来三种问题,如果可以防止这三种情况的发生,那将不会产生并发异常。1、脏读在理解脏读(DirtyRead)之前,需要理解脏数据的概念。但是脏数据和之前所介绍的脏页完全是两种不同的概念。脏页指的是在缓冲池中已经被修改的页,但是还没有刷新到磁盘中,即数据库实例内存中的页和磁盘中的页的数据是不一致的,当然在刷新到磁盘之前,日志都已经被写入到了重做日志文件中。而所谓脏数据是指事务对缓冲池中行记录的修改,并且还没有被提交(commit)。对于脏页的读取,是非常正常的。脏
1、行锁的3种算法InnoDB存储引擎有3种行锁的算法,其分别是RecordLock:单个行记录上的锁GapLock:间隙锁,锁定一个范围,但不包含记录本身Next-KeyLock:GapLock+RecordLock,锁定一个范围,并且锁定记录本身RecordLock总是会去锁住索引记录,如果InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。Next-KeyLock是结合了Gaplock和RecordLock的一种锁定算法,在Next-KeyLock算法下,InnoDB对于行的查询都是采用这种锁定算法。例如一个索引有10,11,1
外键主要用于引用完整性的约束检查。在InnoDB存储引擎中,对于一个外键列,如果没有显式地对这个列加索引,InnoDB存储引擎自动对其加一个索引,因为这样可以避免表锁——这比Oracle数据库做得好,Oracle数据库不会自动添加索引,用户必须自已手动添加,这也导致了Oracle数据库中可能产生死锁。对于外键值的插入或更新,首先需要查询父表中的记录,即SELECT父表。但是对于父表的SELECT操作,不是使用一致性非锁定读的方式,因为这样会发生数据不一致的问题,因此这时使用的是SELECT…LOCKINSHAREMODE方式,即主动对父表加一个S锁。如果这时父表上已经这样加X锁,子表上的操作会
自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-incrementcounter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:SELECTMAX(auto_inc_col)FROMtFORUPDATE;插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INCLocking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后
在默认配置下,即事务的隔离级别为REPEATABLEREAD模式下,InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下,用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句,即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(lockingread)操作:SELECT…FORUPDATESELECT…LOCKINSHAREMODESELECT…FORUPDATE对读取的行记录加一个X锁,其他事务不能对已锁定的行加上任何锁。SELECT…LOCKINSHAREMODE对读取的行记录加一个S锁
一致性的非锁定读(consistentnonlockingread)是指InnoDB存储引擎通过行多版本控制(multiversioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB存储引擎会去读取行的一个快照数据。如图所示。图6-4直观地展现了InnoDB存储引擎一致性的非锁定读。之所以称其为非锁定读,因为不需要等待访问的行上X锁的释放。快照数据是指该行的之前版本的数据,该实现是通过undo段来完成。而undo用来在事务中回滚数据,因此快照数据本身是没有额外的开销。此外,读取快照
InnoDB存储引擎实现了如下两种标准的行级锁:共享锁(SLock),允许事务读一行数据。排他锁(XLocK),允许事务删除或更新一行数据。如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称这种情况为锁兼容(LockCompatible)。但若有其他的事务T3想获得行r的排他锁,则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。 XS XSX不兼容不兼容S不兼容兼容从表中可以发现X锁与任何的锁都不兼容,而S锁仅和S锁兼容。需要特别注意的是,S和X锁都是行锁,兼容是指对同一记录