Gitlib
Ravior
2015-09-07
目录

Mysql核心知识汇总

# Mysql逻辑架构

mysql

Mysql逻辑架构主要分三层：

  1. 第一层负责连接处理，授权认证，安全等等
    • 每个客户端连接都会在服务器进程中拥有一个线程，服务器维护了一个线程池，因此不需要为每一个新建的连接创建或者销毁线程；
    • 当客户端连接到Mysql服务器时，服务器对其进行认证，通过用户名和密码认证，也可以通过SSL证书进行认证；
    • 一旦客户端连接成功，服务器会继续验证客户端是否具有执行某个特定查询的权限；
  2. 第二层负责编译并优化SQL
    • 这一层包括查询解析，分析，优化，缓存以及所有的的内置函数
    • 对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果
    • 所有跨存储引擎的功能都在这一层实现:存储过程，触发器，视图
  3. 第三层是存储引擎
    • 存储引擎负责在MySQL中存储数据、提取数据
    • 存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明
    • 存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求

# 主从复制

主从复制原理，简言之，就三步曲：

  • 主数据库有个binlog二进制文件，纪录了所有增删改Sql语句。（binlog线程）
  • 从数据库把主数据库的binlog文件的sql语句复制过来。（io线程）
  • 从数据库的relay log重做日志文件中再执行一次这些sql语句。（Sql执行线程）

Mysql

上图主从复制分了五个步骤进行：

  1. 主库的更新事件(update、insert、delete)被写到binlog
  2. 从库发起连接，连接到主库
  3. 此时主库创建一个binlog dump thread，把binlog的内容发送到从库
  4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
  5. 还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

# InnoDB文件存储结构

从物理意义上讲，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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 表结构文件

在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
1
2
3
4
5
6
7
8
9
10

# 表空间结构

mysql

表空间（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)
1
2
3
4
5
6
7

以上缓冲池的大小是：1073741824/1024/1024 = 1024M(1G)。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

缓冲池是占用最大块内存的部分，用来存放各种数据的缓存。

mysql

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]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

以上截取的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的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

mysql

通过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)
1
2
3
4
5
6
7
8
9
10
11
12
13

从日志缓冲写入磁盘有两个时间点：

  • 主线程每秒都会将重做日志缓冲写入磁盘的重做日志文件，不论事务是否已经提交;
  • 另外一个是由参数innodb_flush_log_at_trx_commit控制，表示在事务提交时，处理重做日志;
mysql> show variables like 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1
2
3
4
5
6
7

参数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是事务的四大特性。

  • 原子性(Atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
  • 一致性(Consistency)： 数据库总是从一个一致性的状态转换到另一个一致性的状态。
  • 隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • 持久性(Durability)：一旦事务提交，则其所做的修改不会永久保存到数据库

# 事务的隔离性问题

如果不考虑事务的隔离性，会出现以下问题：

  • 脏读：一个事务在更新一条记录，未提交前，第二个事务读到了第一个事务更新后的记录，那么第二个事务就读到了脏数据，会产生对第一个未提交数据的依赖。一旦第一个事务回滚，那么第二个事务读到的数据，将是错误的脏数据。
  • 幻读：一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。
  • 不可重复读（虚读）：一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变，就是不可重复读。

幻读和不可重复读的区别在于幻读是数据条数发生了变化（插入、删除），而不可冲突读在于数据发生了更新，前后读取的结果不一致。

# 事务隔离级别

脏读、不可重读度、幻读，其实都是数据库的一致性问题，必须由一定的事务隔离机制来解决，InnoDB下的事务隔离级别有以下四种：

  • 读未提交(Read uncommitted)：一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。（基本没用）
  • 读已提交(RC, Read Commit)：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，但仍然存在不可重复读和幻读问题。
  • 可重复读(RR, Repeatable Read)：同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但幻读依然存在。
  • 串行化(Serializable)：事务串行执行。避免了以上所有问题。
隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能

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)
1
2
3
4
5
6
7
8
9
10
11
12

# MVCC

MVCC(Mutil-Version Concurrency Control)，多版本并发控制，是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。

MVCC的实现是通过保存数据在某个时间点的快照(redo log)来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

  • SELECT：读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT：将当前事务的版本号保存至行的创建版本号
  • UPDATE：新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
  • DELETE：将当前事务的版本号保存至行的删除版本号

# InnoDB索引结构

Mysql索引用的B+树作为数据结构，如下图：

Mysql

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索引树如下：

Mysql

InnoDB索引采用聚簇索引，InnoDB数据&索引文件为一个idb文件，表数据文件本身就是主索引，相邻的索引临近存储。 叶节点data域保存了完整的数据记录(数据[除主键id外其他列data]+主索引[索引key:表主键id])。 叶子节点直接存储数据记录，以主键id为key,叶子节点中直接存储数据记录。(底层存储结构:** frm -表定义、 ibd: innoDB数据&索引文件)

由于InnoDB采用聚簇索引结构存储，索引InnoDB的数据文件需要按照主键聚集，因此InnoDB要求表必须有主键(MyISAM可以没有)。如果没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键，如果不存在这样的列，mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。 InnoDB的所有 辅助索引 都引用 数据记录的主键 作为data域。

Mysql

# InnoDB锁类型

Mysql

# 加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题。

  • 乐观锁
    • 每次去取数据，都很乐观，觉得不会出现并发问题。
    • 因此，访问、处理数据每次都不上锁。
    • 但是在更新的时候，再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。
  • 悲观锁
    • 每次去取数据，很悲观，都觉得会被别人修改，会有并发问题。
    • 因此，访问、处理数据前就加排他锁。
    • 在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。

# 锁粒度

  • 表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。
  • 行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
  • 页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

# 兼容性

  • 共享锁
    • 又称读锁（S锁）。
    • 一个事务获取了共享锁，其他事务可以获取共享锁，不能获取排他锁，其他事务可以进行读操作，不能进行写操作。
    • SELECT ... LOCK IN SHARE MODE 显示加共享锁。
  • 排他锁
    • 又称写锁（X锁）。
    • 如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
    • SELECT ... FOR UPDATE 显示添加排他锁。

# 锁模式

  • 记录锁： 在行相应的索引记录上的锁，锁定一个行记录
  • gap锁： 是在索引记录间歇上的锁,锁定一个区间
  • next-key锁： 是记录锁和在此索引记录之前的gap上的锁的结合，锁定行记录+区间。
  • 意向锁 是为了支持多种粒度锁同时存在；
#MySQL
上次更新: 2022/12/01, 11:09:34
Mysql主从复制
Mysql中间件MyCat使用

← Mysql中间件MyCat使用→

最近更新
01
常用游戏反外挂技术总结
11-27
02
Golang开发实践万字总结
11-11
03
Redis万字总结
10-30
更多文章>
Theme by Vdoing | Copyright © 2011-2022 Ravior | 粤ICP备17060229号-3 | MIT License