《高性能MySQL》读书笔记1—MySQL架构

《高性能MySQL》读书笔记1—MySQL架构

目录

• 《高性能MySQL》读书笔记1—MySQL架构
  • 1. MySQL逻辑架构
    • 客户端/服务端通信协议
    • MySQL核心功能服务层
      • 查询缓存
      • 语法解析和预处理
      • 查询优化
      • 查询执行引擎
      • 返回结果给客户端
    • MySQL 执行过程总结
  • 2. 并发控制
    • 读写锁
      • 读锁
      • 写锁
    • 锁粒度
      • 表锁
      • 行级锁
  • 3. 事务
    • ACID
      • 原子性atomicity
      • 一致性 consistency
      • 隔离性 isolation
      • 持久性 durability
    • 隔离级别
      • READ UNCOMMITTED 未提交读
      • READ COMMITTED 提交读/不可重复读
      • REPEATABLE READ 可重复读
      • SERIALIZABLE 可串行化
    • 死锁
    • MySQL 中的事务
  • 4. 多版本并发控制 MVCC
    • InnoDB的 MVCC
  • 5. 存储引擎
    • InnoDB
    • MyISAM
    • 差距

1. MySQL逻辑架构

MySQL的逻辑架构分为三层:

• 客户端/服务端通信协议
• MySQL核心功能服务层
• 存储引擎

客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它

所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。*但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT 以及加上LIMIT限制的原因之一。

MySQL核心功能服务层

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

　　1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
　　2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

　　1. 多个小表代替一个大表，注意不要过度设计
　　2. 批量插入代替循环单条插入
　　3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
　　4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

语法解析和预处理

解析的目的是为了验证语法规则是否正确合法以及需要查询的数据库的表和字段是否存在。

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

MySQL 会解析查询并且创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引节结构等等

用户可以通过特殊的关键字 hint 来提示优化器，影响它的决策过程。

也可以通过特殊关键字 explain来请求优化器 解释 优化过程中的各个因素，让用户知道服务器是如何进行优化决策的

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

查询执行引擎

在完成解析和优化阶段后， MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作都是通过调用存储引擎实现的接口来完成， 这些接口被称为handler Api.

实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段就将结果返回给客户端。

即使查询不到数据，MySQL 依然会返回这个查询的相关信息，比如该查询影响到的行数，以及执行时间等等。

如果查询的缓存被打开，且该查询可以被缓存， 那么 MySQL 也会将结果存放到缓存中。

结果接返回给客户端是一个增量且逐步返回的过程，有可能在MySQL 生成第一条结果的时候，就开始向客户端住不返回结果集了。

这样服务端就无须存储太多结果而消耗过多的内存，也可以让客户端第一时间获得返回结果。

需要注意的是，结果集中的每一行都会以一个满足客户端/服务端通信协议的数据包进行发送，再通过 TCP 协议进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送。

MySQL 执行过程总结

　回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

　　1. 客户端向MySQL服务器发送一条查询请求
　　2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
　　3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
　　4. MySQL根据执行计划，调用存储引擎的API来执行查询
　　5. 将结果返回给客户端，同时缓存查询结果
　　6. 客户端接收返回结果

2. 并发控制

无奈轮何时何地，只要有多个查询需要再同一时刻修改数据，都会产生并发问题。

读写锁

解决这种问题的方法非常简单， 在处理并发读或者写的时候，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。

这两种锁通畅被称为共享锁和排它锁

又称为读锁和写锁。

读锁

读锁是共享的， 或者说是互相不阻塞的。

多个客户可以同一时间读取同一个资源 ，互补干扰。

写锁

写锁是排他的，也就是说同一个写锁会阻塞其他的写锁和读锁， 这是出于安全的考虑，只有这样才能保证在给定的时间里，只有一个用户能执行写入操作，并防止其他的用户读取正在写入的同一个资源

锁粒度

读写锁会对资源的并发性产生影响， 一种提高共享资源的并发性的问题就是让锁定的对象更具体，更有选择性。

尽量只锁定需要修改的部分数据，而不是所有的资源。

更理想的方式是，只对会修改的数据片 进行精确的锁定。任何时刻，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

加锁也同样需要消耗资源。

所得各种操作，包括获得锁，检查锁的状态是否已经解除，释放锁等等都会增加系统的开销。

如果系统花费大量的时间和资源来管理锁，而不是存储数据，那么系统的性能可能会因此受到影响。

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。

表锁

表锁是 MySQL 中最基本的所策略，并且是开销最小的策略。他会锁定整张表， 一个用户在对表进行写操作（插入、删除、更新等）前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁的时候，其他读取的用户才能获得读锁，读锁之间是相互不阻塞的。

写锁比读锁拥有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（反之，读锁不能插入到写锁的前面）。

大部分情况下，MySQL 不会使用表锁。但是在一些特殊场景下， 比如 alter table 之类的语句会使用到表锁，忽略存储引擎的锁机制

行级锁

行级锁可以最大程度的支持并发处理（同时也带来了最大的锁开销）。

3. 事务

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

事务内的语句要么全部执行成功，要么全部执行失败。

如果数据库引擎能够成功的对数据库应用该组全部的查询语句，那么就执行该组查询。

如果其中有任何一条语句因为崩溃或者其他原因无法执行， 那么所有的语句都不会执行。

ACID

一个兼容 ACID 的数据库系统，需要做很多复杂但是用户并没有察觉的工作，这才能确保 ACID 的实现。

就像锁的粒度一样，ACID 需要系统做更多额外的工作，会带来额外的资源开销，因此还是需要考虑到性能和成本的平衡

不过好在 MySQL 的数据库引擎已经替我们实现了这些。

原子性atomicity

一个事务必须被视为一个不可分割的最小工作单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。

对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性 consistency

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

一致性的实现是通过事务的原子性来进行保证的。

一个事务执行的过程中出现系统崩溃、错误等情况，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。

隔离性 isolation

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

之所以用“通常来说”，是因为不同的隔离级别下，隔离性并不是一定满足的

持久性 durability

一旦事务提交，事务所做的修改就会永久保存到数据库中。此时，即使系统崩溃，修改的数据也不会丢失。

隔离级别

READ UNCOMMITTED 未提交读

在 未提交读 级别下， 事务中的修改即使没有提交事务，也能给被其他事务给读取到。

事务可以读取到其他未提交的事务所做的修改，被称为脏读；

READ COMMITTED 提交读/不可重复读

提交读可以解决脏读问题，在提交读的隔离等级下， 事务开始时，只能 “看见” 已经提交的事务所做的修改。

换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

这个级别也被叫做不可重复读，因为在这个级别下，执行两次同样的查询操作，得到的结果可能不一样。

REPEATABLE READ 可重复读

可重复读解决的脏读的问题， 该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。

但是可重复读隔离级别没法解决“幻读”问题；所谓的幻读 ，指的是当某个事务在读取某个范围内的记录时，另一个是事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生 幻行。

幻读的问题，可以通过 MVCC 多版本并发控制 来解决。

MySQL InnoDB的默认隔离等级是 可重复读

SERIALIZABLE 可串行化

可串行化是最高的隔离级别， 强制事务串行执行，避免了前面说的幻读问题。

简单的来说，可串行化隔离登记下，会在读取的每一行数据上都加锁，所以会导致大量的超时和锁争抢问题。

一般不用这个隔离级别，除非是对数据的一致性有强要求且可以接受并发度低的情况下才会考虑。

死锁

死锁是指两个或者多个事务在同一资源商相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

当多个事务试图以不认同的顺序锁定资源时，就有可能会产生死锁。

多个事务同时锁定同一个资源的时候，也会产生死锁。

InnoDB 实现了死锁检测和死锁超时机制

当 InnoDB引擎检测到了死锁的循环依赖，会立即返回一个错误。

如果一个查询的时间到达了锁等待超时的最长设定时间后，会放弃锁请求。但是这种方式通常来说不是太好。

InnoDB目前处理的方式是将拥有最少排它锁的事务进行回滚。

MySQL 中的事务

InnoDB 引擎下，默认采用事务自动提交模式。

如果不是显示地开始一个事务，则每个查询都会被当做一个事务进行提交操作。

另外还有一些命令，再执行前会强制执行commit提交当前活动的事务， 比如 Alter table.

4. 多版本并发控制 MVCC

行级锁虽然简单，但是出于并发性能和资源开销的考虑，MySQL还同时实现了多版本并发控制。

可以认为 MVCC 是行级锁的一个变种，但是他在很多情况下都避免了加锁操作，因此开销更低。

MVCC 的视线是基于保存数据在某个时间点的快照来实现的。

也就是说，不管事务执行时间多久，每个事务看到的数据都是其事务开始的时刻的数据快照。

InnoDB的 MVCC

InnoDB 的 MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行创建时的版本号，一个保存了行的过期或者删除的版本号。

每当开始一个新的事务的时候，系统的版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

具体操作如下:

SELECT:

​ InnoDB会根据以下两个条件检查每行记录：

1. InnoDB 只查找版本早于当前事务版本的数据行，即行的系统版本号小于或者等于事务的系统版本号 。这样可以确保事务读取到的行，要么是在事务开始前已经存在的， 要么是事务自身插入或者修改的。
1. 同时需要保证 行的删除的版本号要么是未定义的，要么大于当前事务版本号。这样可以确保事物读取到的行，都是在事务开始前未被删除的。

只有符合以上两个条件的行才会被当做结果返回。

INSERT:

​ InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE:

​ InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE:

​ InnoDB 插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

通过维护额外两列的系统版本号，使得大多数的读操作都不用加锁，且只会读取到符合标准的行。

不足之处在于需要额外的存储空间以及一些额外的版本号维护工作。

MVCC 只在 可重复读 和 提交读 两个隔离级别下工作

未提交读每次都会读取到最新的数据，而串行化则每次都会对读取的行加锁

5. 存储引擎

InnoDB

InnoDB 是 MySQL 的默认事务型引擎。

InnoDB 的数据存储在表空间中。 InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别，其默认的隔离级别是 可重复读， 并且通过 间隙锁来防止幻读的出现。

间隙锁使得 InnoDB 不仅仅锁定查询涉及到的行，也会对索引中的间隙进行锁定，以防止幻影行的插入。

MyISAM

差距

1、 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2、 存储空间

MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。

InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、 可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4、 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、 表锁差异

MyISAM： 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB： 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、 全文索引

MySql全文索引

MyISAM：支持 FULLTEXT类型的全文索引

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、表的具体行数

MyISAM： 保存有表的总行数，如果select count() from table;会直接取出出该值。

InnoDB： 没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、CRUD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。

11、 外键

MyISAM：不支持

InnoDB：支持