《高性能 MySQL》读书笔记一
MySQL 逻辑架构

第一层:连接处理,授权认证,安全校验等… 第二层:查询分析,优化,缓存,内置函数实现。第三层:由存储引擎来负责对数据进行存储和查询。
连接管理与安全
每个客户端连接都会在 MySQL 服务端进程中拥有一个线程,每个连接的查询只会在自己单独的线程中执行,因此 MySQL 是支持并发的。为了保证数据的一致性,MySQL 支持四种事务隔离级别和各种锁机制。
当客户端连接服务端时,服务端会对连接进行认证。认证基于用户名、密码以及主机 IP。
优化与执行
MySQL 会解析查询的 SQL,并创建内部数据结构,对其进行各种优化。包括:重写顺序,表的读取顺序,选择合适的索引等…
用户可以通过特殊的关键字,影响它的决策过程。例如:USE INDEX。
并发控制
读写锁
-
共享锁(读锁):共享的,相互不阻塞,多个连接可以同时读取同一数据,互不干扰。
-
排他锁(写锁):线程阻塞的,当其中一个线程在执行写入时,会阻塞其他线程,包括读和写。为了防止某个线程在写入数据时,其他线程同时修改数据。
锁粒度
尽量只锁定需要修改的部分数据,而非整张表。锁定的数据量越少,系统的并发程度则越高。
加锁需要消耗资源,锁的各种操作:获得锁,检查锁,释放锁等都会增加系统的开销。
锁策略:在锁的开销和数据的安全性之间取得平衡。
大多数数据库系统都没有更多的选择,一般都是在表上施加 “行锁”。
MySql 提供了更多的选择,MySql 支持多种存储引擎,每种存储引擎都可以实现自己的锁策略和锁粒度。
表锁
MySql 中最基本的锁策略,开销最小的策略,会锁定整张表。当用户对表进行写操作时,会阻塞其他用户的读写操作。
写锁比读锁有更高的优先级,写锁的请求会被插入到读锁队列的前面。
行级锁
除了给整个表加锁之外,MySql 还支持给部分数据加锁——行锁。行锁可以支持最大的并发处理,但同时也带来了最大的系统开销。
事务
事务就是一组原子性的 Sql 语句,如果数据库可以正常执行每一条 sql 语句,那么就执行该组 sql(提交),如果其中某一条执行失败,那么所有语句都不会执行(回滚)。
一个运行良好的事务处理系统,必须通过严格的 ACID 测试:
-
原子性(atomicity):一个事务必须被视为一个不可分割的最小单元,整个事务的所有操作要么全部执行成功,要么全部回滚,不可能只执行事务的部分操作。
-
一致性(consistency):数据库总是从一个一致性的状态,转换到另一个 一致性的状态。
-
隔离性(isolation):一个事务所做的修改,最终提交前,“通常来说”对其他事务是不可见的。为什么要加“通常来说”呢?因为事务有不同的 隔离级别。
-
持久性(durability):一旦事务提交,则其所作的修改就永久保存到数据库中。即使此时系统崩溃,修改的数据也不会丢失。没有 100%的持久性保证。
支持 ACID 的存储引擎比不支持 ACID 的存储引擎需要更大的系统开销,性能也会受到影响。
MySql 提供了多种存储引擎供用户选择,对于一些不需要事务支持的查询类应用,可以选择 MyIsam 存储引擎来获得更好的性能。
隔离级别
MySQL 支持四种标准的事务隔离级别:
-
READ UNCOMMITTED(未提交读) 事务中的修改即使没有提交,对其他事务也是可见的。事务可以读取未提交的数据,即“脏读”。这个级别会导致很多问题,实际一般很少用。例:事务 A 修改了数据,事务 B 读取了事务 A 修改后的数据,之后事务 A 回滚了,事务 B 读到的就是脏数据。事务 B 可以读取事务 A 实时修改的数据。
-
READ COMMITTED(提交读):事务做的修改在提交前,对其他事务是不可见的,也叫做:不可重复读。大多数数据库默认的隔离级别是 READ COMMITTED,但 MySql 不是。例如:事务 A 在修改数据,事务 B 读取到了修改前数据,事务 A 提交了,事务 B 再次读取,得到修改后的数据,两次读取的数据不一致,所以称作“不可重复读”。事务 A 不会阻塞其他事务的读操作。
-
REPEATABLE READ(可重复读):解决了脏读的问题,保证了在同一个事务中多次读取同样的记录结果是一致的。但是会有另一个问题:“幻读”。即当前事务在读取某个范围的记录时,其他事务在该范围内插入了数据,当前事务再次读取时,会产生“幻行”。不过 MySql 的 InnoDB 存储引擎通过“多版本并发控制”(MVCC),采用“间隙锁策略”解决了幻读的问题。REPEATABLE READ 是 MySql 默认的隔离级别。
-
SERIALIZABLE(可串行化):最高的事务隔离级别,解决了幻读的问题,给读取的每一行数据加锁,可能导致大量的超时和锁争用问题。只有在非常需要确保数据的一致性和接受没有并发的情况下才会使用。
死锁
指多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致死循环。
START TRANSACTION;
UPDATE person_info SET person_name = 'Admin' WHERE id = 1;
UPDATE person_info SET person_name = 'Lisa' WHERE id = 2;
COMMIT;
START TRANSACTION;
UPDATE person_info SET person_name = 'Lisa' WHERE id = 2;
UPDATE person_info SET person_name = 'Admin' WHERE id = 1;
COMMIT;
如果凑巧两个事务都执行了第一条 UPDATE,同时锁定了该行数据,执行第二条时,发现数据互相被对方锁定,事务就会互相等待对方释放锁,陷入死循环。
InnoDB 处理死锁的方式:将持有最少行级的排它锁的事务进行回滚。
锁的行为和顺序和存储引擎息息相关。以同样的顺序执行语句,有些存储引擎会产生死锁,有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突,有些则是由于存储引擎的实现方式导致的。
MySql 中的事务
从 mysql-5.5.5 开始,InnoDB 作为默认存储引擎,InnoDB 是支持事务的。
MySql 默认采用自动提交(AUTOCOMMIT)模式,如果不是显式的开启一个事务,则每条 Sql 都会被当做一个事务执行提交操作。
SET AUTOCOMMIT
可以通过修改 AUTOCOMMIT 的值来启用/禁用 自动提交。SET AUTOCOMMIT = 0;禁用自动提交 SET AUTOCOMMIT = 1;启用自动提交注:修改只针对当前连接有效。
对于不支持事务的存储引擎,修改它没有任何作用和影响。
还有一些命令,在执行前会强制提交当前活动的事务。例如:数据定义语言(DDL)、LOCK TBLES 等…
Transaction Isolation Level
MySql 可以通过 SET Transaction Isolation Level 【隔离级别】来设置当前开启事务的隔离级别, 也可以在配置文件中设置整个数据库的事务隔离级别。
在事务中混合使用存储引擎
如果事务中涉及到不支持事务的存储引擎的表,那么回滚时,不支持事务的表是无法被撤销的。这将会导致数据不一致的问题,应该特别注意。
隐式和显示锁定
InnoDB 采用的是两阶段锁定协议。
隐式锁:在事务执行过程中,随时都有可能执行锁定,InnoDB 会根据事务隔离级别自动加锁,锁在提交或回滚后统一释放。
显示锁:除了隐式锁外,InnoDB 也支持通过语句来显式的加锁。
LOCK IN SHARE MODE
显式的添加 共享锁。会阻塞写锁,但不阻塞读锁。InnoDB 会给查询出来的行加行锁,其他线程只可读,不可写。
SELECT * FROM person LOCK IN SHARE MODE;
FOR UPDATE
显式的添加 排他锁。会阻塞写锁和读锁。
SELECT * FROM person FOR UPDATE;
LOCK TABLES
LOCK TABLES 【表名】【READ/WRITE】;
除了行锁,还可以添加表锁,表锁也分读锁和写锁。
READ
加了读锁的表,所有线程可读不可写。
LOCK TABLES person read;
SELECT * FROM person;
INSERT INTO person VALUES ('1','1');
-- [HY000][1099] Table 'person' was locked with a READ lock and can't be updated
如代码所示,INSERT 将会执行失败。
WRITE
加了写锁的表,当前线程可读可写,其他线程不可读不可写。其他线程的读写操作会被阻塞,只有当前线程释放锁,其他线程才可操作。
-- 线程A
LOCK TABLES person WRITE;
-- 线程B
SELECT * FROM person;
如代码所示,线程 B 的查询将会被阻塞。
UNLOCK TABLES
UNLOCK TABLES 可以释放表锁。需要注意的是,UNLOCK TABLES 只能释放当前线程拥有的锁,即线程 A 无法释放线程 B 加的锁。不要误以为 UNLOCK TABLES 会彻底解锁表,如果表是被其他线程锁住的,当前线程是解不开的。想来也是合理的,如果可以互相解锁,那锁还有什么意义呢?
多版本并发控制
MySQL 的大多数事务性存储引擎实现的都不是简单的行锁,基于提升并发性能的考虑,实现了多版本并发控制(MVCC)。
可以认为 MVCC 是行锁的一个变种,它在很多情况下避免了加锁的操作,减少了系统的开销。
InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏列的方式来实现:
-
行的创建时间 -
行的过期时间
存的并不是时间,而是系统版本号。每开启一个新的事务,版本号都会递增。
-
SELECT InnoDB 会根据两个条件筛选记录:1、创建时间早于当前事务版本的数据行 2、过期时间为 null 或者大于当前事务版本 这样可以确保事务读取到的行符合以下条件之一:1、行在事务开始前已经存在 2、行是当前事务写入的 3、行在事务开始之前未删除
-
INSERT InnoDB 为新插入的行保存创建时间。
-
DELETE InnoDB 为删除的行保存过期时间。
-
UPDATE InnoDB 为更新的行保存当前版本号作为创建时间,旧行保存过期时间。
MVCC 使得大多数读操作都可以不用加锁,写操作也只锁定必要的行。并发的性能更好,不足之处就是需要额外的空间保存隐藏的两列数据。
MVCC 只在 REPEATABLE READ(可重复读)和 READ COMMITTED(提交读)这两个隔离级别的事务中生效, 因为 READ COMMITTED(提交读)读取的总是最新的数据、 SERIALIZABLE(可串行化)会给每一行加锁 均无需并发控制。
存储引擎
MySQL 支持多种存储引擎, 可以通过 SHOW TABLE STATUS LIKE 【表名】;查看表使用哪种存储引擎。
MySQL5.5 开始将 InnoDB 设为默认的存储引擎。
InnoDB
MySQL 默认的事务型存储引擎,采用 MVCC 来支持高并发,实现了四个标准的事务隔离级别,默认的隔离级别是 REPEATABLE READ(可重复读),且通过“间隙锁策略”防止出现幻读,支持热备份。
MyISAM
MySQL5.1 之前的默认存储引擎,不支持事务和行锁,不支持崩溃后的安全恢复。
MyISAM 压缩表
如果表在创建并导入数据后,不会对其进行修改,那么可以考虑使用 MyISAM 压缩表。可以极大减少磁盘占用,查询时减少 I/O 操作提升性能。
MEMORY
MEMORY 存储引擎不会将数据保存到磁盘,只保存在内存中,重启后数据将丢失。但是因为基于内存,不需要进行磁盘 I/O,其性能比 InnoDB 要快一个数量级。
适合存放一些临时数据。
ARCHIVE
ARCHIVE 存储引擎只支持 INSERT 和 SELECT 操作,ARCHIVE 换缓存所有的写操作并使用 zlib 对插入的行进行压缩,比 MyISAM 的磁盘 I/O 更少,插入的性能更高,但是查询时会全表扫描,性能较低。
ARCHIVE 适合需要快速插入,但很少查询的业务需求,例如:日志保存,数据采集等…
转换表的引擎
有时需要修改数据库表使用的存储引擎,需要注意的是:一旦修改存储引擎,将会失去原存储引擎的所有特性。
修改表的存储引擎有三种方式:
ALTER TABLE
ALTER TABLE person ENGINE = InnoDB;
需要较长的时间,MySQL 将原表的数据逐行复制到一个新表中,并在原表上加读锁。
导出与导入
mysqldump 工具可以快速的将数据导出到文件,然后修改文件中创建表的引擎。
创建与查询
-- 基于person创建一张空表
CREATE TABLE person_copy LIKE person;
-- 修改person_copy的存储引擎
ALTER TABLE person_copy ENGINE = InnoDB;
-- 旧表数据插入到新表
INSERT person_copy SELECT * FROM person;
MySQL 的历史
感兴趣的同学可以去网络上了解,这里不再赘述。
E = mc^2
原文始发于微信公众号(程序员小潘):MySQL架构与历史
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/28610.html