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

qq489498494

　　目录(?)[-]
　　事务
　　事务概述
　　事务的实现
　　redo
　　undo
　　事务控制语句
　　隐式提交的SQL语句
　　对于事务的统计
　　事务隔离级别
　　分布式事务
　　不好的习惯
　　备份与恢复
　　备份于恢复概述
　　冷备
　　逻辑备份
　　select into outfile
　　逻辑备份的恢复
　　mysqlimport
　　二进制日志备份与恢复
　　热备
　　ibbackup
　　xtrabackup
　　快照备份
　　复制
　　复制的工作原理
　　快照复制的备份架构
　　性能调优
　　
　　
　　事务
　　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。
　　http://blog.csdn.net/yingminxing/article/details/8281994