Mysql inndodb 存储引擎的简单总结（组成结构，锁，事务，备份，优化）

上面这张图总结了innodb mysql的逻辑和物理架构组成，锁与事务，备份策略以及调优点，以下就每个点进行总结说明。

在线程处理方面，Mysql是多线程的架构，由一个master线程，一个锁监控线程，一个错误监控线程，和多个IO线程组成。并且对一个连接会开启一个线程进行服务。

io线程又分为节省随机IO的insert buffer，用于事务控制的类似于oracle的redo log，以及多个write，多个read的硬盘和内存交换的IO线程。

在内存分配方面，包括innodb buffer pool ，以及log buffer。其中innodb buffer pool包括insert buffer、datapage、index page、数据字典、自适应hash。

在逻辑数据结构方面，innodb包括表空间、段、区、页/块，行。索引结构是B- tree结构，包括二级索引和主键索引，二级索引的叶子节点是主键PK，根据主键索引的叶子节点指向存储的数据块。

在物理存储文件方面，包括frm表定义文件，每个表一个的ibd文件（需要进行设置），ibdata文件（系统表空间MySQL数据库文件，存储InnoDB系统信息和用户数据库表数据和索引，数据字典，undo，所有表共用），

ib_logfile日志文件，binlog二进制日志文件。

为了保证一致性，对于锁的分类，一般分为共享锁和排他锁；mysql主要有这么几个锁，行锁，页锁，表锁，innodb存储引擎是行级锁。

共享锁（S--只允许当前或其他线程读，不允许任何线程修改该记录）和排他锁（X---只允许当前线程update或者delete一行记录，不允许其他线程操作（包括查询该记录）

在做select in share mode查询的时，会产生一个共享锁，当然类似select for update会产生一个排他锁。

使用锁的时候，要主要死锁的产生，一般是交叉进行锁相应的资源，常见的场景是，

一个线程根据主键更新索引值（先给数据行加X锁，再给索引块中的记录加X锁），另外一个是根据索引更新相关字段（先给索引块中的记录加X锁，再给数据行加X锁）。

为了保证数据之间的完整性，Mysql innodb支持事务操作，

并且考虑到一致性和并发性的不同要求，事务之间隔离级别有

read uncommited（脏读、不可重复读、幻读(隔离级别最低，并发性能高)）、

read commited（会出现不可重复读、幻读问题（锁定正在读取的行））、

repeated read(mysql默认)（会出幻读（锁定所读取的所有行））、

serialize read（证所有的情况不会发生（锁表））。

在备份方面，分为冷备份（物理文件copy，数据文件、undo、插入缓冲等-------停机），温备份（dump，import----要加锁），热备份（xtrabackup）。

可以进行有全量的备份，增量的备份(通过binlog或者lsn(xtrabackup))，或者实时性的备份（replication复制）

在性能调优方面，可以从以下几个角度来考虑，

a、操作系统级别，内核以及socket的优化，网络优化bond

b、server级别（连接管理、网络管理、table管理、日志）

c、应用级别（比如索引的考虑，schema的优化适当冗余；优化sql查询导致的CPU问题和内存问题）

d、IO存储级别，

innodb主要用在OLTP类应用，一般都是IO密集型的应用，在提高IO能力的基础上，充分利用cache机制。需要考虑的内容有，

1、在保证系统可用内存的基础上，尽可能的扩大innodb buffer pool，一般设置为物理内存的3/4

2、日志和数据的存储，需要分开，日志是顺序的写，需要做raid1+0，并且用buffer-IO；数据是离散的读写，走direct IO即可，

避免走文件系统cache带来的开销。

存储能力，SAS盘raid操作（raid卡缓存，关闭读cache，关闭磁盘cache，关闭预读，只用writeback buffer，不过需要考虑充放电的问题），

当然如果数据规模不大，数据的存储可以用高速的设备，Fusion IO、SSD。

对于数据的写入，控制脏页刷新的频率，对于数据的读取，控制cache hit率；因此而估算系统需要的IOPS，评估需要的硬盘数量

（fusion io上到IOPS 在10000以上，普通的硬盘150）

4、文件系统的使用，只在记录事务日志的时候用文件系统的cache；尽量避免mysql用到swap

（可以将vm.swappiness=0，内存紧张时，释放文件 系统cache）

5、IO调度优化，减少减少磁头移动

以下是相关参数参考：

假设 Mysql(物理内存32G，CPU物理40core)

扩展表空间带来的抖动问题，需要预先分配表空间

innodb_file_per_table（true）---分散IO

innodb_flush_log_at_trx_commit（设置成1，<2<0）2是文件系统，1是直接flush到存储上

sync_binlog（设置成1<0）0是文件系统

innodb_flush_method（默认值为fdatasync，O_DSYNC和O_DIRECT，这里设置为O_DIRECT）

binlog_cache_size(默认32k)

innodb_buffer_pool_size（设置成24G）

innodb_max_dirty_pages_pct(默认90%--->75%)

innodb_read_io_threads/innodb_write_io_threads（默认都是4个，对于高速的存储设备么可以设置大一些）

innodb_adaptive_flushing(ON，根据断重做日志产生速度确定需要刷新脏页的最合适数目)

innodb_thread_concurrency（cpu core的两倍，80）

innodb_io_capacity（默认值为200---刷新脏页和插入缓冲）

innodb_doublewrite

innodb_purge_threads

max_connections(默认500)

max_user_connections（默认30，改为500）

innodb_page_size（8K，如果全表扫描16K，如果ssd，4k随机性能好）