事务
原文:http://yingminxing.com/mysql%E6%8A%80%E6%9C%AF%E5%86%85%E5%B9%95innodb%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%EF%BC%8D%E4%BA%8B%E5%8A%A1%E5%A4%87%E4%BB%BD%E6%80%A7%E8%83%BD%E8%B0%83%E4%BC%98/
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。