mysql技术内幕-innodb存储引擎读书笔记(下)

转载 2012年03月31日 11:05:00

http://isadba.com/index.php/2012/03/mysql%E6%8A%80%E6%9C%AF%E5%86%85%E5%B9%95-innodb%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E8%AF%BB%E4%B9%A6%E7%AC%94%E8%AE%B0%E4%B8%8B/

第六章、锁

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

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通过Next-key lock解决了幻读).
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


相关文章推荐

Mysql技术内幕InnoDB存储引擎读书笔记--《三》文件

本章将分析构成Mysql数据库和InnoDB存储引擎表的各种类型文件,主要有参数文件、日志文件、socket文件、pid文件、Mysql表结构文件、存储引擎文件。3.1参数文件Mysql参数文件my....

MySQL技术内幕-InnoDB存储引擎-读书笔记(二)

MySQL技术内幕-InnoDB存储引擎-读书笔记(二) 作为php开发,使用mysql总是少不了的 系列文章博客链接 http://itsong.net/articles/466.html ...

MySQL技术内幕-InnoDB存储引擎-读书笔记(一)

MySQL技术内幕-InnoDB存储引擎-读书笔记(一) 作为php开发,使用mysql总是少不了的 第一章 MySQL体系结构和存储引擎 MySQL被设计为一个单进程多线程架构的数据库 ./my...

mysql技术内幕-innodb存储引擎读书笔记(上)

第一章、mysql体系结构和存储引擎 1.1、数据库和实例的区别 数据库:物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm,myd,myi,ibd结尾的文件。 ...

MySQL技术内幕-InnoDB存储引擎读书笔记(MySQL日志文件)

本文基于MySQL技术内幕-InnoDB存储引擎,详细介绍了mysql相关的日志文件,包括错误日志、查询日志、慢查询日志、binlog日志、redo、undo日志的相关配置参数,并重点分析了binlo...

MySQL技术内幕-InnoDB存储引擎读书笔记(mysql存储引擎概述)

MySQL技术内幕-InnoDB存储引擎读书笔记,主要总结InnoDB与MyISAM存储引擎的区别,并详细介绍了MVCC多版本并发控制协议。...

《MySQL技术内幕——InnoDB存储引擎》读书笔记(一)——MySQL体系结构和存储引擎

一、定义数据库和数据库实例 数据库 (database): 物理操作系统文件或其他形式文件类型的集合 实例(instance) :  MySQL数据库由后台线程以及一个共享内存区组成,共享内存可以...

InnoDB存储引擎redo log文件——《MySQL技术内幕InnoDB存储引擎》读书笔记

redo log的作用是记录事务日志。万一实例或介质失败,可以通过redo log进行恢复,保证数据的完整性。 每个InnoDB存储引擎至少有1个redo log文件组,每个文件组下至少有2个redo...

MySQL技术内幕:InnoDB存储引擎读书笔记(下)

第六章、锁   锁是区别文件系统和数据库系统的一个关键特性。   6.1、什么是锁?   锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储...
  • gao8658
  • gao8658
  • 2012年03月31日 22:55
  • 656
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql技术内幕-innodb存储引擎读书笔记(下)
举报原因:
原因补充:

(最多只允许输入30个字)