《高性能MySQL》学习笔记——第一章 MySQL架构与历史

第一章 MySQL架构与历史

1.1 MySQL逻辑架构

  • 第一层：连接、线程处理、授权认证、安全等

  • 第二层：缓存、解析器、优化器等。第一层 + 第二层 = 服务器层？？？

  • 第三层：存储引擎，负责MySQL数据的存储和提取。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器中有一个线程，这个连接的查询只会在这个单独的线程中进行。如：

  • 使用命令行登录MySQL服务器后，show processlist就会新增一个线程；

  • 使用workbench打开一个连接后会新增两个线程（不知道为何有两个线程？），workbench的SQL tab和线程无关，开十个SQL tab还是两个线程；

  • 使用datagrip每打开一个query console并查询数据后就会开启一个线程。

1.1.2 优化与执行

特殊关键字提示优化器：hint

请求优化器解释优化过程：explain

1.2 并发控制

并发控制的问题：多个查询在同一时刻修改数据时产生的问题。

并发控制的两个层面：服务器层、存储引擎层。

1.2.1 读写锁

在处理并发的读或写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

  • 读锁：读锁之间是共享的，即读锁之间互不阻塞；

  • 写锁：写锁之间是排他的，即一个写锁会阻塞其他写锁和读锁，注意不仅仅是写锁会被阻塞，读锁也会被阻塞！

写锁比读锁的优先级高，即在一个锁队列中，一个写锁可能会插队到读锁前面，而读锁不能被插入到写锁前面，想想之前数据结构中学的优先队列。

1.2.2 锁粒度

锁粒度：对正在修改的数据片进行锁定的精度，即对一条数据进行修改时，是做到仅锁住这条数据，还是锁住整个表。

锁策略：在锁的开销和数据的安全性之间寻求平衡。一般商业数据库是在表上加行级锁。

表锁：锁定整张表。开销最小，

行级锁：锁定独写的某一条数据。开销最大。

1.3 事务

事务（Transaction）：是一组原子性的SQL查询，或者说一个独立的工作单元。

ACID：

  1. 原子性（atomicity）

一个事务必须被视为一个不可分割的最小单元，整个事务的操作要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性。

  1. 一致性（consistency）

数据库总是从一个一致状态转换到另外一个一致状态。

有一个博客对一致性的解释比较容易理解：

一致性有下面特点：

  • 如果一个操作触发辅助操作（级联，触发器），这些也必须成功，否则交易失败。

  • 如果系统是由多个节点组成，一致性规定所有的变化必须传播到所有节点（多主复制）。如果从站节点是异步更新，那么我们打破一致性规则，系统成为“最终一致性”。

  • 一个事务是数据状态的切换，因此，如果事务是并发多个，系统也必须如同串行事务一样操作。

在现实中，事务系统遭遇并发请求时，这种串行化是有成本的， Amdahl法则描述如下：它是描述序列串行执行和并发之间的关系。一个程序在并行计算情况下使用多个处理器所能提升的速度是由这个程序中串行执行部分的时间决定的。

大多数数据库管理系统选择（默认情况下）是放宽一致性，以达到更好的并发性。

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

  1. 隔离性（isolation）

隔离性是指，一个事务所做的修改在最终提交之前，对其他事务是不可见的（并不是完全不可见，有不同的隔离级别）。

  1. 持久性（durability）

事务一旦提交，其所作的修改就会永久保存到数据库中，此时即使系统奔溃，修改的数据也不会丢失。不可能有能做到100%持久性保证的策略。

1.3.1 隔离级别

READ UNCOMMITTED（未提交读）、READ COMMITTED（提交读，也是不可重复读）、REPEATABLE READ（可重复读，是MySQL的默认事务隔离级别）、SERIALIZABLE（可串行化）。

这篇博文对隔离级别的讲解很到位（若原文链接失效，可参考我自己的复制内容），可以参考。书中都是文字性的讲解，不太好理解。额外要注意的一点就是SERIALIZABLE会在读的每一行上加锁。

1.3.2 死锁

死锁：是指当两个或多个事务在同一资源上相互占用，请求锁定对方占用的资源，每个事务都在等待对方释放锁，从而等待状态永远不会结束形成恶性循环的现象。

书中这个例子很容易理解，如果凑巧两个事务的第一条SQL同时执行，则事务1锁定了stock_id=4的数据（假设叫做第1条数据），事务2锁定了stock_id=3的数据（假设叫做第2条数据），然后事务1等待事务2释放第2条数据的锁，而事务2要执行整个事务提交后才能释放数据2的锁，当时在执行第2条SQL时又要等事务1释放第1条数据的锁，这样就导致两个事务相互等待，且相互等待的状态永远不会结束，导致死锁。

数据库实现了各种死锁检测机制和死锁超时机制。当前InnoDB处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。（书中原文）

死锁的产生有些还和存储引擎的实现方式有关。

1.3.3 事务日志

使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘的事务日志中。可以理解为事务提交后并不会直接修改硬盘中的数据，而是先把事务日志写到硬盘的事务日志中，然后让数据在后台慢慢修改到硬盘中。通常称这种日志为预写式日志，修改数据要写入两次数据到硬盘中，第一次是日志，第二次才是数据根据日志进行修改。系统奔溃时，也可以通过硬盘中的事务日志进行数据恢复。

1.3.4 MySQL中的事务

如果不是显示地使用start transaction，则默认为每一个SQL语句都是一个事务。MySQL默认采用自动提交事务（autocommit）的方式。

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> SET AUTOCOMMIT = 0; -- 显示的将该线程下的提交方式修改为手动提交，即要手动使用COMMIT或ROLLBACK
Query OK, 0 rows affected (0.00 sec)

注意！某些命令在执行前会强制执行COMMIT提交掉当前事务的修改，如ALTER TABLE、LOCK TABLES等。

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置下一个事务的隔离级别，可以用在存储过程的start transaction之前。
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置当前会话事务的隔离级别
Query OK, 0 rows affected (0.00 sec)

虽然InnoDB会根据事务隔离级别自动锁定修改，但MySQL也支持显式锁定表：LOCK TABLES。但本书建议，只有在使用set transaction = 0的事务中可以使用LOCK TABLES，其他情况应该避免使用使用LOCK TABLES。

1.4 多版本并发控制（MVCC）

可以认为MVCC是行级锁的一个变种，但是它很多情况下避免了枷锁操作，虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

下面是本书中通过InnoDB的简化版行为来说明MVCC是如何工作的。

1.5 MySQL的存储引擎

  • InnoDB引擎：支持事务

  • MyISAM引擎：不支持事务和行级锁，但读取效率高

  • Archive：不支持引擎，支持行级锁，插入效率高

  • CSV引擎：用作数据交换很有用

  • Memory引擎：不支持行级锁，支持hash索引，查找效率特别高，比MyISAM效率还更高，数据保存在内存中的形式

中间省略大量内容...

如果需要对记录的日志做分析报表，则生成报表的SQL可能会导致日志插入效率下降，怎么办？一种办法是，利用MySQL内置的复制方案，复制一份到从库，然后在从库做查询操作，主库只用于高效的插入操作，因此从库的查询操作就不会影响到主库的插入性能。

posted @ 2020-11-07 02:30  yury757  阅读(145)  评论(0)    收藏  举报