第六章、锁

     锁是区别文件系统和数据库系统的一个关键特性。

     6.1、什么是锁？

     锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁，从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。

     6.2、innodb存储引擎中的锁

     6.2.1、锁的类型

     S lock 共享锁 允许事务读一行数据

     X lock 排它锁 允许事务删除或者更新一条数据

     IS lock 意向共享锁 事务想要获得一个表中某几行的共享锁

     IX lock 意向拍他所 事务想要获得一个表中某几行的排它锁

     可以使用 show engine innodb status\\G查看 TRANSACTIONS中关于锁的信息。也可以使用 show full processlist查看相关信息。在innodb插件版本以后，可以通过查看INFORMATION—SCHEMA中的INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS三张表来分析存在的锁问题。

     具体参见 P207

     6.2.2、一致性的非锁定读操作

     一致性非锁定读(consistent nonlocking read)是指innodb通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。

     多版本控制是通过快照实现的，快照数据其实就是当前数据之前的历史版本，可能有多个版本。这种技术称为行多版本技术，由此带来的并发控制叫做多半本并发控制(multi version concurrency control,MVCC).

     在Read Committed和Repeatable Read(innodb默认的事务隔离级别)下，innodb存储引擎使用非锁定的一致性读。但是对于快照数据的定义却不同。

     在Read Commited级别，对于快照数据，非一致性读总是读取被锁定行的最新一份快照。

     在Repeatable级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

     6.2.3、SELECT…FOR UPDATE & SELECT…LOCK IN SHARE MODE

     SELECT…FOR UPDATE 可以获得一个X锁。

     SELECT…LOCK IN SHARE MODE 可以获得一个S锁。

     6.2.4、子增长和锁

     对于含有子增长计数器的表进行插入时，会执行”SELECT MAX(auto_inc_col) FROM t FOR UPDATE;”插入操作会更具这个自增长的计数器值加1赋予自增长列。这个实现方式叫做AUTO-INC Locking。这是一种特殊的锁，为了提高并发，它不会在事务执行完才释放，只是在语句执行后立即释放。

     从mysql-5.1.22版本开始，innodb引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了子增长值插入的性能。并且mysql-5.1.22开始，innodb引擎提供了一个参数

     innodb_autoinc_lock_mode，默认的值为1

     在讨论新的增长方式之前我们需要对自增长实现方式分类：

     INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等

     Simple insert：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。

     Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.

     Mixed-mode inserts:指其中一部分是子增长的，有一部分是确定的。

     现在有SIMPLE INSERT、BULK INSERTS、MIXED-MODE INSERTS三种类型的INSERT语句，有AUTO-inc locking(最早的)和轻量级互斥量的自增长两种auto—increment锁。

     innodb_autoinc_lock_mode=0 5.1.22之前的方式，也就是所有类型的insert都用AUTO-inc locking。

     innodb_autoinc_lock_mode=1 这个参数是5.1.22之后出现的也是之后的默认值，对于SIMPLE INSERT，使用轻量级互斥量的锁，对于BULK INSERT，使用AUTO-inc locking，

     innodb_autoinc_lock_mode=2 指不管什么情况都使用轻量级互斥的锁，效率最高，但是复制只能使用row-base replication，因为statement-base replication会出现问题。

     另外就是innodb和myisam的一个区别，innodb下，自增长必须是索引，而且必须是索引的第一个列，不然会报错，myisam不会出现这个问题。

     6.2.5、外键和锁

     innodb中，对于一个外键列，如果没有显示的对这个列加索引，innodb就自动的对其加一个索引。

     6.3、锁的算法

     innodb引擎有三种锁的算法设计：

     Record lock：单个记录上的锁

     Gap lock：间隙锁，锁定一个范围，但不包含记录本身

     Next-key lock：Gap lock+Next-key lock锁定一个范围，并锁定记录本身。

     6.4、锁问题

     本来锁问题会导致的是更新丢失、幻读、脏读、不可重复读，但是innodb作者却只写出了三种问题,可能是幻读通过innodb Next-key Lock解决了，作者就没有提及。

     这几个锁问题对应事务隔离的4个安全级别:

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

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

     READ REPEATABLE(可重复读，innodb引擎的默认事务隔离级别，解决了不可重复读的问题，但是产生了幻读，innodb通过MVCC解决了幻读).

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

     6.5、阻塞

     innodb中需要其他事务的锁释放它锁占用的资源，这个时候就会发生锁等待，这就是阻塞。

     innodb引擎有两个相关参数

     innodb_lock_wait_timeout 用来设定等待的时间，默认是50秒，这是一个动态参数，可以随时调整

     innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作，默认是OFF，代表不回滚，这是一个静态参数。

     6.6、死锁

     死锁会惨生阻塞，所以可以通过6.5的参数，让超时的阻塞回滚。还有就是开发的时候，每个事务对表，字段，行的操作，都是顺序的，这样可以很大程度上避免死锁。

     6.7、锁升级

     innodb不存在锁升级问题，锁升级一般在SQL SERVER中使用。

第七章、事务

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

     Automicity 原子性

     Consistency一致性

     Isolation 隔离性

     durability 持久行

     7.1、事务概述

     详细介绍了ACID属性。

     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)

     这意味这磁盘上的页和内存缓冲池中的页是不同步的，可以通过 show ENGINE INNODB STATUS\\G来观察当前磁盘和日志的”差距”，在LOG字段。Log sequence number表示当前的LSN，Log flushed up to表示刷新到重做日志文件的LSN，Last checkpoint at表示刷新到磁盘的LSN。

     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 回滚到之前创建的保存点

     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语句

     完成这些语句就会自动提交：

     DDL

     CREATE USER，DROP USER，GRANT、RENAME USER，REVOKE，SET PASSWORD

     ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE

     注意:TRUNCATE TABLE属于DDL语句，不能rollback。

     7.5、对于事务的统计

     QPS = Question Per Second = 每秒请求数

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

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

     7.6、事务隔离级别

     参考6.4

     7.7、分布式事务

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

     分布式事务指营运需多个独立的事务资源(transactional resources)参与一个全局的事务中。事务资源通常是关系型数据库系统。全局事务要求在其中所有参与的事务要么都提交、要么都回滚，对于原有的ACID有了提高。

     注意：在使用分布式事务时，innodb存储引擎的事务隔离级别必须设置成SERIALIABLE。

     7.8、不好的习惯

     7.8.1、在循环中提交事务

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

     7.8.3、使用自动回滚，存储过程中使用 declare exit handler for sqlexception rollback;

第八章、备份与恢复

     对于dba来说，备份应该占工作比重的50%以上。

     8.1、备份于恢复概述

     hot backup

     cold backup

     warm backup

     逻辑备份

     裸文件备份

     完全备份

     增量备份

     日志备份

     8.2、冷备

     关闭数据库，拷贝frm文件、共享表空间、单独表空间、重做日志文件，还有推荐备份my.cnf

     冷备份的优点：

     备份简单，只是拷贝相关文件

     备份文件易于在不同操作系统、不同mysql版本上进行恢复

     恢复简单

     恢复速度快

     缺点：

     冷备份比逻辑备份占用更多的空间

     要停止数据库

     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 outfile ‘/tmp/file’; 和 load data infile ‘/tmp/file’ into table tablename;

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

     8.5、热备

     8.5.1、ibbackup工作原理如下：

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

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

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

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

     在线备份

     备份性能好

     支持压缩备份

     跨平台支持

     8.5.2、xtrabackup

     暂时留空

     8.6、快照备份

     使用文件系统快照或者LVM快照备份数据库。暂时留空

     8.7、复制

     8.7.1、复制的工作原理

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

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

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

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

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

     主要功能：

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

     读取的负载平衡 通过读写分离实现

     数据库备份 复制对备份有帮助，但是复制不能代替备份

     高可用性和故障转移

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

第九章、性能调优

     性能调悠主要涉及一下内容：

     选择合适的CPU

     内存的重要性

     硬盘对数据性能的影响

     合理的设置raid

     操作系统的选择也很重要

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

     选择合适的基准测试工具

     9.1、选择合适的CPU

     OLTP的特点

     用户操作并发大

     事务处理的时间一般比较短

     查询的语句较为简单、一般走索引

     复杂的查询较少

     OLTP对CPU的需求不是很大，但是OLTP属于IO密集型的操作。

     OLAP对CPU的需求比较大，需存储空间要求比较大，对操作时间不太敏感。

     对于多核的CPU机器，可以修改innodb_read_io_threads和innodb_write_io_threads来增加IO线程，来充分利用多核的性能。

     9.2、内存的重要性

     学会判断内存是否已经达到瓶颈了？

     通常innodb存储引擎的缓冲池的命中率不应该小于99%。

     show global status like ‘innodb%read%’;

     Innodb_buffer_pool_reads 表示物理磁盘读取页的次数

     Innodb_buffer_pool_read_ahead 预读的次数

     Innodb_buffer_pool_ahead_evicted 预读的页，但是没有被读取就从缓冲池中被替换的数量，一般用来判断预读的效率

     Innodb_buffer_pool_read_requests 从缓冲池中读取页的次数

     Innodb_data_read 总共读入的字节数

     Innodb_data_reads 发起读取请求的次数，每次读取可能需要读取多个页

     计算缓冲池命中率 = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads)

     平均每次读取的字节数 = Innodb_data_read / Innodb_data_reads

     9.3、硬盘对数据库性能的影响

     9.3.1、传统机械硬盘

     机械硬盘两个重要的指标：寻道时间、转速。

     机械硬盘顺序访问的速度远远大于随机访问。

     可以通过raid提高数据库的性能，也可以将数据分布式的放到多个硬盘上。

     9.3.2、固态硬盘

     固态硬盘是有多个闪存组成的。

     固态硬盘优点：

     具有很高的随机读取能力、低延迟性、低功耗、防震等有点。

     固态硬盘缺点：

     闪存中的数据不可更新，只能通过山区的覆盖重写，而覆盖从写之前需要执行耗时的erase操作。earse操作通常以擦除块为单位，通常一个块是128KB。

     所以使用固态硬盘应该很好的利用他的随机读取性能，避免过多的写入。

     如果使用了固态硬盘，可以通过优化innodb_io_capacity参数，来充分利用固态硬盘的高IOPS特性。

     9.4、合理的利用raid

     9.4.1、raid类型

     raid的作用：

     增强数据集成度

     增强容错功能

     增加处理量或容量

     根据不同磁盘的组合方式，常见的raid组合可以分为raid0、raid1、raid5、raid10和raid01.

     raid0 具有所有raid级别中最高的读写性能，最高的存储空间利用率，最低的安全性，需要至少两快硬盘。

     raid1 具有所有raid级别中最高的安全性，最低的存储空间利用率，较高的读性能和稍微减弱的写性能，需要至少两块硬盘。

     raid5 是raid0和raid1的这种方案，具有和raid0相近的数据读取能力，写性能降低很利用，用于写入奇偶校验信息了，需要至少三块硬盘。

     raid10 先将所有硬盘视为raid 0的最低级别，也就是把所有硬盘分成两部分，做成raid0.然后把这两部分中的硬盘做成raid1.提供较高的保护性和不错的性能。至少4块硬盘。

     raid01 先将所有硬盘视为raid 1的最低级别，也就是把所有硬盘分成两部分，做成raid1,然后把这两部分中的硬盘做成raid0，提供了最高的性能，安全性较低。至少4块硬盘

     raid50 …..

     推荐使用raid10.

     9.4.2、RAID write Backup功能

     RAID write Backup功能就是将raid控制器要写入硬盘的数据写入自己的缓存中，并把他们安排到后期一起写入硬盘。这样做的目的肯定是提高磁盘性能。缓存比磁盘的IOPS高多非常多。

     缺点是断电可能会丢失数据，所以提供了BBU(Battery backup unit，电池备份单元)。由于电池维护较为麻烦，所以又新出了BFWC技术。具体参见我的

     9.4.3、RAID配置工具

     一般可以在BIOS配置界面调节，页可以使用系统工具调节。P308介绍了LSI公司的MegaCLI工具的使用。

     9.5、操作系统的选择

     推荐linux和freebsd。

     9.6、不同文件系统对数据库的影响

     推荐ZFS或者使用lvml逻辑卷管理。

     9.7、选择合适的基准测试工具

     9.7.1、sysbench

     参见我blog的sysbench的文章。

     9.7.2、mysql-tpcc

建议继续学习：

1. Innodb IO优化－配置优化    (阅读：6416)
2. Innodb分表太多或者表分区太多，会导致内存耗尽而宕机    (阅读：6003)
3. Innodb 表和索引结构    (阅读：4598)
4. Innodb如何使用内存    (阅读：3869)
5. InnoDB线程并发检查机制    (阅读：3836)
6. 快速预热Innodb Buffer Pool的方法    (阅读：3835)
7. InnoDB的缓存替换策略及其效果    (阅读：3525)
8. 多版本并发控制：PostgreSQL vs InnoDB    (阅读：3512)
9. Innodb文件表空间结构    (阅读：3471)
10. InnoDB之Dirty Page、Redo log    (阅读：3301)