深度分享：Mysql数据库核心知识汇总
Mysql逻辑架构主要分三层：
第一层负责连接处理，授权认证，安全等等第二层负责编译并优化SQL第三层是存储引擎主从复制
主从复制原理，简言之，就三步曲：
上图主从复制分
|
Mysql逻辑架构
Mysql逻辑架构主要分三层： 第一层负责连接处理，授权认证，安全等等第二层负责编译并优化SQL第三层是存储引擎主从复制 主从复制原理，简言之，就三步曲：
上图主从复制分了五个步骤进行： 主库的更新事件(update、insert、delete)被写到binlog从库发起连接，连接到主库此时主库创建一个binlog dump thread，把binlog的内容发送到从库从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的dbInnoDB文件存储结构 从物理意义上讲，InnoDB表由共享表空间文件（ibdata1）、独占表空间文件（ibd）、表结构文件（.frm）、以及日志文件（redo文件等）组成。 gitlib@devops:/usr/local/mysql/data$ ls -alh total 185M drwxr-xr-x 11 mysql mysql 4.0K Sep 11 21:40 . drwxr-xr-x 13 mysql mysql 4.0K Aug 10 21:38 .. -rw-r----- 1 mysql mysql 56 Aug 10 21:36 auto.cnf -rw-r----- 1 mysql mysql 54K Aug 10 21:43 devops.err -rw-r----- 1 mysql mysql 6 Aug 10 21:36 devops.pid -rw-r----- 1 mysql mysql 86K Sep 11 21:40 ib_buffer_pool -rw-r----- 1 mysql mysql 76M Sep 11 21:40 ibdata1 -rw-r----- 1 mysql mysql 48M Sep 11 21:40 ib_logfile0 -rw-r----- 1 mysql mysql 48M Sep 7 17:12 ib_logfile1 -rw-r----- 1 mysql mysql 12M Sep 11 21:40 ibtmp1 drwxr-x--- 2 mysql mysql 4.0K Aug 10 21:36 mysql -rw-r----- 1 mysql mysql 154 Sep 11 21:40 mysql-bin.000001 -rw-r----- 1 mysql mysql 39 Sep 11 21:40 mysql-bin.index drwxr-x--- 2 mysql mysql 4.0K Aug 10 21:36 performance_schema drwxr-x--- 2 mysql mysql 12K Aug 10 21:36 sys 表结构文件 在MYSQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件。 gitlib@devops:/usr/local/mysql$ sudo ls -alh data/test/ total 597M drwxr-x--- 2 mysql mysql 4.0K Sep 6 09:12 . drwxr-xr-x 11 mysql mysql 4.0K Sep 7 15:40 .. -rw-r----- 1 mysql mysql 8.5K Sep 6 09:12 articles.frm -rw-r----- 1 mysql mysql 596M Sep 6 10:00 articles.ibd -rw-r----- 1 mysql mysql 61 Aug 11 16:32 db.opt -rw-r----- 1 mysql mysql 8.8K Aug 11 16:39 user.frm -rw-r----- 1 mysql mysql 912 Aug 11 16:40 user.MYD -rw-r----- 1 mysql mysql 3.0K Aug 11 16:40 user.MYI 表空间结构
表空间（tablespace)：所有数据都放在表空间中。如果开启了innodb_file_per_table选项，则InnoDB会为每张表开辟一个表空间。但是需要注意的是表空间存放的只是数据、索引和插入缓冲bitmap页，其他数据比如undo信息，插入缓冲索引页，系统事务信息，二次写缓冲还是会放在原来的共享表空间内。 如果rollback后，共享表空间不会自动收缩，但是会判断空间是否需要（比如undo空间），如果不需要的话，会将这些空间标记为可用空间，供下次undo使用。 段(segment)：表空间由各个段组成，比如数据段，索引段，回滚段等。 区(extent)：区由连续的页组成，在任何情况下区的大小都是1M。InnoDB存储引擎一次从磁盘申请大概4-5个区(4-5M)。在默认情况下，页的大小为16KB，即一个区中有大概64个连续的页。 页(page)：InnoDB磁盘管理的最小单位。B树节点= 一个物理Page（16K），数据按16KB切片为Page 并编号， 编号可映射到物理文件偏移(16K * N），B+树叶子节点前后形成双向链表。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。 缓冲池 InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。 在数据库中进行读取操作，首先将从磁盘中读到的页放在缓冲池中，下次再读相同的页中时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。 参数innodb_buffer_pool_size指定了缓冲池的大小： mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec) 以上缓冲池的大小是：1073741824/1024/1024 = 1024M(1G)。 对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。 缓冲池是占用最大块内存的部分，用来存放各种数据的缓存。
InnnoDB的存储引擎的工作方式总是将数据库文件按照页（每页16K)读取到缓冲池，然后按照LRU算法保留在缓冲池的缓存数据。如果数据库文件需要修改，总是先修改在缓存池中的页（发生修改后，该页即为脏页），然后再按照一定的频率将缓冲池的脏页刷新（flush）到文件。 mysql> show engine innodb status\G; ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 1099431936 Dictionary memory allocated 612965 Buffer pool size 65528 Free buffers 17336 Database pages 48077 Old database pages 17584 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 484399, not young 7 0.00 youngs/s, 0.00 non-youngs/s Pages read 231, created 122275, written 162031 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 48077, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] 以上截取的show engine innodb status命令返回的数据一部分，其中Buffer pool size表明了一个有多少个缓存帧（buffer frame)， 每个buffer frame为16K，Free buffers表示当前空闲的缓存帧，Database Pages表示已经使用的缓存帧，Modified db pages表示脏页的数量，如果free buffers较大，说明数据库压力大不，因为缓冲池中有大量的空闲页可用。 重做日志 默认情况在数据库数据文件夹下会有两个文件，ib_logfile0/ib_logfile1, 这就是重做日志文件，记录了对于InnoDB存储引擎的事务日志。 每个Innodb存储引擎至少有1个重做日志文件组，每个组至少包含2个重做日志文件(ib_logfile0,ib_logfile1)。 可以通过设置多个镜像日志组(mirrored log groups)，将不同组放到不同磁盘，提高重做日志的高可用性。 日志组中的文件大小是一致的，以循环的方式运行。文件1写满时，切换到文件2，文件2写满时，再次切换到文件1.日志组中的文件大小是一致的，以循环的方式运行。文件1写满时，切换到文件2，文件2写满时，再次切换到文件1(从头写入）。 为了保证数据的安全性，事务进行中时会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中, redo log是按照顺序写入的，磁盘的顺序读写的速度远大于随机读写。当数据库或主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、一致性和持久性。 对于写入重写日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo lopg buffer）中，然后按照一定的条件写入日志文件。 当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
通过innodb_log_buffer_size可以配置重写日志缓冲的的大小。 mysql> show variables like 'innodb_log%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | innodb_log_buffer_size | 16777216 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_write_ahead_size | 8192 | +-----------------------------+----------+ 7 rows in set (0.00 sec) 从日志缓冲写入磁盘有两个时间点： mysql> show variables like 'innodb_flush_log%'; +--------------------------------+-------+ 参数innodb_flush_log_at_trx_commit可设的值有0、1、2。0代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。而1和2不同的地方在于：1是在commit时将重做日志缓冲同步写到磁盘；2是重做日志异步写到磁盘，即不能完全保证commit时肯定会写入重做日志文件，只是有这个动作。 回滚日志 除了重做记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC，也即非锁定读。 事务开始之前，将当前的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性，事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。 默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。 ACID ACID是事务的四大特性。 事务的隔离性问题 如果不考虑事务的隔离性，会出现以下问题： 幻读和不可重复读的区别在于幻读是数据条数发生了变化（插入、删除），而不可冲突读在于数据发生了更新，前后读取的结果不一致。 事务隔离级别 脏读、不可重读度、幻读，其实都是数据库的一致性问题，必须由一定的事务隔离机制来解决，InnoDB下的事务隔离级别有以下四种：
MySQL 默认的级别是:Repeatable read 可重复读，级别越高，数据越安全，但性能越低。 mysql> show variables like 'transaction_%'; +----------------------------------+-----------------+ | Variable_name | Value | +----------------------------------+-----------------+ | transaction_alloc_block_size | 8192 | | transaction_allow_batching | OFF | | transaction_isolation | REPEATABLE-READ | | transaction_prealloc_size | 4096 | | transaction_read_only | OFF | | transaction_write_set_extraction | OFF | +----------------------------------+-----------------+ 6 rows in set (0.01 sec) MVCC MVCC(Mutil-Version Concurrency Control)，多版本并发控制，是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。 MVCC的实现是通过保存数据在某个时间点的快照(redo log)来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。 在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。 每个事务又有自己的版本号mysql数据库，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。 默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样： InnoDB索引结构 Mysql索引用的B+树作为数据结构，如下图：
Mysql中B+Tree在经典B+Tree的基础上进行了优化，增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能：如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。 MyISAM & InnoDB 都使用B+Tree索引结构。但是底层索引存储不同，MyISAM 采用非聚簇索引，而InnoDB采用聚簇索引。 MyISAM索引原理：采用非聚簇索引-MyISAM myi索引文件和myd数据文件分离，索引文件仅保存数据记录的指针地址。叶子节点data域存储指向数据记录的指针地址。 MyISAM索引按照B+Tree搜索，如果指定的Key存在，则取出其data域的值，然后以data域值-数据指针地址去读取相应数据记录。辅助索引和主索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。MyISAM索引树如下：
InnoDB索引采用聚簇索引，InnoDB数据&索引文件为一个idb文件，表数据文件本身就是主索引，相邻的索引临近存储。 叶节点data域保存了完整的数据记录(数据[除主键id外其他列data]+主索引[索引key:表主键id])。 叶子节点直接存储数据记录，以主键id为key,叶子节点中直接存储数据记录。(底层存储结构:** frm -表定义、 ibd: innoDB数据&索引文件) 由于InnoDB采用聚簇索引结构存储，索引InnoDB的数据文件需要按照主键聚集，因此InnoDB要求表必须有主键(MyISAM可以没有)。如果没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键，如果不存在这样的列，mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。 InnoDB的所有 辅助索引 都引用 数据记录的主键 作为data域。
InnoDB锁类型
加锁机制 乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题。 锁粒度 兼容性 锁模式 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
