Mysql技术内幕InnoDB存储引擎-事务&备份&性能调优

事务

transaction是数据库区别于文件系统的重要特性之一,innodb引擎完全符合事务的ACID特性。

Atomicity 原子性

Consistency一致性

Isolation 隔离性

Durability 持久性

7.1、事务概述

Atomicity 原子性:整个数据库事务是不可分割的工作单位。

Consistency一致性:事务将数据库从一种状态转变为下一种一致的状态。

Isolation 隔离性:一个事务的影响在该事务提交前对其他事务都不可见。

Durability 持久性:事务一旦提交,其结果就是永久性的。

7.2、事务的实现

      隔离性通过锁来实现,原子性、一致性、持久性通过数据库的redo和undo来完成。

7.2.1、redo

innodb通过redo日志文件和innodb log buffer来实现redo。

当事务开始时,innodb会记录该事务的一个LSN(Log Sequence Number,日志序列号)。当执行事务时,会往innodb log buffer写入事务日志。当事务提交时,必须将innodb存储引擎的日志缓存写入磁盘(默认的实现,通过innodb_flush_log_at_trx_commit=1)。这意味这磁盘上的页和内存缓冲池中的页是不同步的,

7.2.2、undo

undo和redo相反,redo是重做,undo是撤销。使用rollback语句请求回滚就可以利用undo信息将数据回滚到修改之前的样子。redo存放在redo日志里面,undo存放在数据库内部的一个特殊段(undo segment),undo段位于共享表空间里。undo是逻辑操作,它实际上是做与之前相反的工作,之前是insert,它就delete对于每个update,undo都会执行一个相反的update,当插入操作回滚时,表空间的大小并不会因此而收缩。

7.3、事务控制语句

AUTOCOMMIT = 0 关闭自动提交,等于1 开启自动提交。

START TRANSACTION | BEGIN 显示的开启一个事务。

COMMIT 提交你的事务。

ROLLBACK 回滚。

SAVEPOINT identifier SAVEPOINT允许你的事务中创建一个保存点,一个事务中可以有多个保存点。

RELEASE SAVEPOINT identifier 删除一个事务的保存点。

ROLLBACK TO [savepoint]identifier 回滚到之前创建的保存点,需要显示运行COMMIT或者ROLLBACK命令。

SET TRANSACTION 这个语句用来设置事务的隔离级别,innodb的隔离级别在6.4已经说过了。

completion_type = 0 时COMMIT和COMMIT WORK功能完全相同。

completion_type = 1 时COMMIT WORK等于COMMIT AND CHAIN,表示提交后马上开启一个相同隔离级别的事务。

complation_type = 2 时COMMIT WORK等于COMMIT AND RELEASE,当事务提交后会自动断开与服务器的链接。

7.4、隐式提交的SQL语句

      1.DDL语句

2.CREATEUSER,DROPUSER,GRANT、RENAME USER,REVOKE,SET PASSWORD。

3.ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE。

7.5、对于事务的统计

      QPS = Question Per Second = 每秒请求数。

TPS = Transaction Per Second = 每秒事务处理能力。

计算TPS的方法是com_commit + com_rollback/TIME = TPS。

7.6、事务隔离级别

      READ UNCOMMITTED(事务隔离最低的级别,有事务隔离就能解决更新丢失,但是存在脏读的问题)。

READ COMMITED(ORACLE和SQL SERVER默认的隔离级别,解决了脏读,但是一个事务多次读取的内容不同,出现了不可重复读的问题)。

READ REPEATABLE(可重复读,innodb引擎的默认事务隔离级别,解决了不可重复读的问题,但是产生了幻读,innodb通过Next-key lock解决了幻读)。

SERIALIZABLE(可串行化,通过强制事务排序解决幻读问题,会降低性能)总的看来innodb默认的 READ REPEATABLE是非常棒的。

7.7、分布式事务

innodb引擎支持XA分布式事务。

分布式事务指营运需多个独立的事务资源(transactional resources)参与一个全局的事务中。事务资源通常是关系型数据库系统。全局事务要求在其中所有参与的事务要么都提交、要么都回滚,对于原有的ACID有了提高。注意:在使用分布式事务时,innodb存储引擎的事务隔离级别必须设置成SERIALIABLE。

分布式事务由一个或多个资源管理器Resource Managers一个事务管理器Transaction Manager及一个应用程序Application Program。

1.资源管理器:提供访问事务资源的方法,通常一个DB就是一个资源管理器。

2.事务管理器:协调参与全局事务中的各个事务,需要和参与全局事务中的所有资源管理器进行通信。

3.应用程序:定义事务的边界,指定全局事务中的操作。

7.8、不好的习惯

7.8.1、在循环中提交事务

7.8.2、不要开启自动提交事务 set auto_commit = 0

7.8.3、使用自动回滚,存储过程中使用 declare exit handlerfor sqlexception rollback。

备份与恢复

8.1、备份于恢复概述

      hot backup(热备):指在数据库运行中直接备份,对正在运行的数据库没有任何影响。

      cold backup(冷备):指在数据库停止的情况下进行备份,一般只需要拷贝相关的数据库物理文件即可。

      warm backup(温备):指在数据库运行时进行,但是会对当前数据库的操作有所影响。

      逻辑备份:指备份后的文件内容是可读的,通常是文本文件,内容一般是SQL语言,或者表内的实际数据,如mysqlduma和select * into outfile的方法。

      裸文件备份:指拷贝数据库的物理文件,数据库既可以处于运行状态,也可以处于停止状态。

      完全备份:指对数据库进行一个完整的备份。

      增量备份:指在上次的完全备份基础上,对更新的数据进行备份。

      日志备份:指对Mysql数据库二进制日志的备份,通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。

8.2、冷备

      关闭数据库,拷贝frm文件、共享表空间、单独表空间、重做日志文件,还有推荐备份my.cnf。冷备份的优点:备份简单,只是拷贝相关文件;备份文件易于在不同操作系统、不同mysql版本上进行恢复;恢复简单;恢复速度快。冷备份的缺点:冷备份比逻辑备份占用更多的空间,空间存放很多其他数据,如Undo短和插入缓冲等;冷备并不总是可以轻易跨平台。

8.3、逻辑备份

      mysqldump最常用的逻辑备份工具。主要提示几个重要的参数。

      –single-transaction 在备份开始前使用start transaction以此保证备份的一致性,需要保证没有其他DDL语句,针对innodb有效。

      –lock-tables -l 在备份中依次锁住每个建构下所有的表,保证备份的一致性,一般用于myisam备份。

      –lock-all-tables -x 在备份过程中,对所有架构中的所有表上锁。

      –master-data=value 当值为1时,转储文件中有change master语句,如果值为2,change master语句是被注释的,此参数会忽略–lock-tables.如果没有使用–singletransaction则会自动使用–lock-all-tables。

      –events -E 备份事件调度器。

      –routines -R 备份存储过程和函数。

      –triggers 备份触发器。

      –tab=path 产生TAB分割的数据文件。对于每张表mysqldump会创建一个包含create table的table_name.SQL和一个包含数据的tbl_name.txt,可以使用参数改变默认的分割符和换行符。

8.3.2、select …. into outfile

      Select … into语句也是一种逻辑备份的方法,或者更准确地说是到处一张表中的数据。

8.3.3、逻辑备份的恢复

      Mysqldump的恢复操作比较简单,因为备份文件就是导出的SQL语句,一般只需要执行这个文件就可以了。通过mysqldump可以恢复存储过程,触发器,事件,数据,但是不能导出视图。

      Load data infile:若是通过mysqldump –tab或select into outfile导出的数据需要恢复时,需要用过Load data infile命令进行导入。

8.3.4、mysqlimport

      Mysqlimport可以并发导入多个文件,而不是并发导入一个文件。

8.4、二进制日志备份与恢复

      备份二进制日志文件前,可以通过flush logs命令来生成一个新的二进制日志文件,然后备份之前的二进制日志。也可以通过mysqlbinlog命令导出到一个文件,然后再通过source命令导入。

8.5、热备

8.5.1、ibbackup

      1、记录备份开始时,innodb存储引擎重做日志文件检查点的LSN。

      2、拷贝共享表空间文件以及独立表空间文件。

      3、记录拷贝完表空间文件后,innodb存储引擎重做日志文件检查点的LSN。

      4、拷贝在备份时产生的重做日志。

它的优点:

      在线备份:不阻塞任何SQL语句。

      备份性能好:备份的实质是复制数据库文件和重做日志文件。

      支持压缩备份:可以支持不同级别的压缩。

      跨平台支持:可以运行在Linux,Windows,Unix平台上。

8.5.2、xtrabackup

      工作原理:首先完成一个完全备份,并记录下此时检查点的LSN;在进行增量备份时,比较表空间的每个页的LSN是否大于上次备份时的LSN,如果是则备份该页,同时记录当前检查点的LSN。

8.6、快照备份

      Mysql数据库本身并不支持快照功能,因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。LVM是Linux系统下对磁盘分区进行管理的一种机制,LVM在硬盘和分区之上建立一个逻辑层来提高磁盘分区管理的灵活性。

8.7、复制

8.7.1、复制的工作原理

      a、主服务器把数据更新记录写道binlog。

      b、从服务器把主服务器的二进制日志拷贝到自己的中继日志中。

      c、从服务器重做做中继日志中的时间,把更新应用到自己服务器上。

      从服务器有两个线程,一个io线程,负责读取主服务器的二进制日志,并将起保存为中继日志,另外一个sql线程,负责执行中继日志。

8.7.2、快照+复制的备份架构

      复制可以用来作为备份,但其功能不仅限于备份,其主要功能:

      1.数据分布:mysql的复制并不需要很多带宽,可以跨机实现数据拷贝。

      2.读取的负载平衡:通过读写分离实现。

      3.数据库备份:复制对备份有帮助,但是复制不能代替备份。

      从服务器做快照可以避免复制对误操作的处理能力,主需要根据从服务器的快照,然后根据二进制日志执行point-in-time的恢复即可。

性能调优

      选择合适的CPU:InnoDB存储引擎一般都应用在OLTP的数据库中,这种应用的特点:用户操作的并发量;事务处理的时间一般比较短;查询的语句较为简单,一般都走索引;复杂的查询较少。

      内存的重要性:内存的大小最能直接反应数据库的性能。InnoDB存储引擎既缓存数据,又缓存索引,并将其缓存于一个很大的缓冲池(InnoDBBuffer Pool)中。

      硬盘对数据库性能的影响:传统机械硬盘;固态硬盘。

      合理的设置RAID。

      操作系统的选择也很重要:Linux系统是最常见的操作系统追求稳定性。

      不同的文件系统对数据库性能的影响

      选择合适的基准测试工具:sysbench,mysql-tpcc。

发表评论

电子邮件地址不会被公开。 必填项已用*标注