Loading

高性能MYSQL LEC1 MYSQL架构

MYSQL并不完美，却足够灵活。

举个例子，MYSQL能适应高要求的环境，比如web类应用。同时，MYSQL既可以嵌入到应用程序中，也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、OLTP等各种应用类型。

That's why we need to learn its design. Only for pressing its performance under different circumstances. And who knows ? Maybe someday I will help build another one on the foundation of what I learn now.

1 MYSQL逻辑架构

MYSQL创新特性与逻辑架构

存储引擎的架构，这种架构设计将Querying Process以及Server Task和Data Storage and Data Retrieval分离。简而言之，分离查询任务层与数据存取层。

image-20220513150526919

  1. 第一层的服务并不是MYSQL独有的，大多数基于网络的C/S工具都有类似结构。比如连接处理、授权认证、安全等。

  2. 第二层包含MYSQL大部分核心功能，包括查询解析、分析、优化、缓存以及所有的内置函数（如时间、数学、加密函数）。所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。这一层通过API接口来和存储引擎通信，并以接口来屏蔽不同引擎间差异。

  3. 第三层包含存储引擎，它负责MYSQL中数据的存储与提取。和Linux下各个文件系统一样，每个存储引擎都有其优势和劣势。存储引擎API包含十几个底层函数，用于执行“开始事务”或“根据主键提取一行记录”等操作。但存储引擎不会解析sql[1]。不同存储引擎间不会互相通信，只会响应API通信。

MYSQL连接管理与安全性

  • 每个客户端连接都会在服务器进程中拥有一个线程。这个连接的查询只会在这个单独的线程中执行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁进程[2]。

  • 客户端连接到MYSQL服务器时，服务器需要对其认证。比如认证可以基于用户名、原始主机信息和密码。还能选择SSL方式连接，可以使用数字证书认证。

  • 连接成功后，服务器会继续检查客户端是否具有执行某个特定查询的权限。

MYSQL优化和执行

  • MYSQL会解析查询，并创建解析树，基于此进行优化，包括重写查询、决定表的读取顺序以及选择合适的索引等[3]。

    • 用户可以通过特殊关键字提示(hint)优化器，影响决策过程。
    • 也可以请求优化器解释(explain)优化过程的各个因素，展示服务器是如何进行优化决策的。并提供一个参考基准，便于用户重构查询和schema，修改相关配置。

  • 优化器不关心使用哪个存储引擎，但存储引擎对优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销，以及表数据的统计信息。比如，某些存储引擎的某种索引，可能对特定查询有优化。[4]why？这个不是建表就决定好用哪个吗

  • 对于SELECT操作，在解析查询前就会查Query Cache，如果能在其中找到对应查询，服务器就不会再执行解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。[5]

2 并发控制

多个查询在同一时刻对数据的修改，即为并发问题的本质。

并发可以通过加锁解决，但简单的加锁会降低程序性能，不利于高并发。

读写锁

也就是共享锁和排他锁

  1. 读锁共享，读锁间相互不阻塞

  2. 写锁排它，写锁阻塞其他写锁

  3. 读锁与写锁，互相阻塞

  4. mysql中，写锁优先级高于读锁。也就是，一个写锁请求可能会被插入到读锁队列的前面。why？好处呢，难道是读多写少，避免写饿死吗，但一些情况下，写多读少怎么办？

锁粒度

  • 一种提高共享资源并发度方式是，降低锁粒度。理想方式，只锁定需要修改的数据。锁的数据量越少，并发程度越高。

  • 问题是加锁也会提高系统开销。比如系统会花费大量时间来管理锁，包括获得锁、检查锁是否接触、释放锁等，而不是存取数据。

  • 所谓的锁策略，就算在锁的开销、并发性能和数据的安全性之间权衡。

    • 大多数db都是提供行级锁，并以各种复杂方式实现，来保证锁较多情况下性能。
    • MYSQL提供多种选择来针对不同场景，而非单一解决方案。每个存储引擎都可以实现自己的锁策略和锁粒度。

表锁

  • 依旧分读写锁。

  • 表锁自身开销最小，但并发性能会低。

  • 尽管存储引擎可以管理自己的锁，但MYSQL本身还是会使用各种有效的表锁来实现不同目的。比如，服务器会为如ALTER TABLE这样的操作使用表锁，而忽略存储引擎的锁机制。

行级锁

  • 依旧分读写锁
  • 行级锁自身开销最大，但并发性能最高。行级锁只在存储引擎层实现，而MYSQL服务层没有实现。众所周知，InnoDB和其他一些存储引擎实现了行级锁。服务器层完全不了解存储引擎层的锁实现。

3 事务

事务的本质是，原子性。

只要事务其中一条失败，那么所有的语句都不会执行。也就是说，要么全部成功，要么全部失败。全部成功后才能提交。

ACID

  1. 原子性（atomicity）：事务要么全部成功，要么全部失败。
  2. 一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。比如银行账户间转账，先从A减去200，再给B加200。如果执行第二条出错，就会产生不一致。
  3. 隔离性（isolation）：一个事务的修改在提交前，对其他事务不可见。比如A-200后事务还在执行，另一个事务查A的余额，则看到的还是A原来的余额。
  4. 持久性（durability）：一旦事务提交，所做的修改就会永久保存，即使系统崩溃。持久性比较模糊，不可能做到100%的持久性，如果数据库本身就能做到持久性，备份又怎么能增加持久性。备份的备份。
  • ACID在应用程序中，比如银行系统，实现非常困难。数据库来实现，也要做很多复杂工作来保证ACID。
  • 事务就像锁粒度，会增加系统开销。非事务型的存储引擎，可以获得更高的性能。

隔离级别

个人觉得，本质原因是事务执行需要一段时间，这段时间内，其他事务读取他执行范围的数据会出现问题。如果不需要时间，瞬间完成，就不需要隔离级别，不会产生脏读、不可重复读和幻读。

级别调整：SET TRANSACTION ISOLATION LEVEL

  1. read uncommitted（未提交读）：

    1. 事务开始时，能看见未提交的事务的修改。
    2. 读取到其他事务未提交的内容，被称为脏读。
    3. 脏读会带来许多问题，而且相比其他隔离级别，性能不会好太多，所以很少使用。

  2. read committed（已提交读）：

    1. 事务开始时，只能看见已提交的事务的修改。
    2. 事务内部两次执行相同的查询得到不同结果，因为内容被修改过，被称为不可重复读。
    3. 大多数db默认隔离级别都是这个，但mysql默认是可重复读。

  3. repeatable read（可重复读）

    1. 同一个事务多次读取相同记录的结果是一致的。
    2. 事务内部两次执行相同的查询，由于另一个事务在查询范围内新增一条记录，产生了幻行。两次不一致。InnoDB使用多版本并发控制（MVCC）来实现。
    3. mysql默认是可重复读

  4. serializable（可串行化）

    1. 强制事务串行执行，简单来说，就是事务会在读取到的每一行数据上都加锁，可能导致大量锁争用和超时问题。只要两个事务有相同的数据范围，就必须串行执行。
    2. 读取到的都加锁，它是唯一加锁的隔离级别。另外，加锁读还有另一个含义，在同一个事务中，如果你先查询数据，随后对相关数据进行插入或修改，那么在标准的SLELECT中不会给出足够的保护。在你查询期间另一个事务可以更新或者删除相同的行。这被称为加锁读的原因。
    3. 实际很少使用，只有需要数据强一致并接收无并发的情况下才有可能使用。
  • 怎么实现加锁读？InnoDB提供两种类型加锁读。
    • SELECT ... LOCK IN SHARE MODE
      • 给读到的每一行都加共享锁。其它的会话也可以读到这些行，但是它们不能修改这些行，直到你的事务提交。如果这些行被其它事务修改了但尚未提交，查询必须等待直到那个事务结束。
      • 加意向共享锁
    • SELECT ... FOR UPDATE。
      • 对于检索到的每一个索引记录，锁定这些行和与之关联的索引记录。
      • 加意向排它锁
隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
未提交读 Y Y Y N
已提交读 N Y Y N
可重复读 N N Y N
串行化 N N N Y

死锁

  1. 多个事务在同一资源上相互占用，并请求锁定对方占用资源，造成恶性循环。多个事务以不同顺序锁定相同资源，互相等待，就会产生死锁。如下面两个事务，假如都执行了第一条update，并锁定该行。

    START TRANSACTION;
UPDATE A;
UPDATE B；
COMMIT;

START TRANSACTION;
UPDATE B;
UPDATE A；
COMMIT;

  2. 为了解决死锁，db实现了各种死锁检测和死锁超时的机制。

    1. 比如InnoDB就能检测出死锁的循环依赖，并立即返回错误。InnoDB目前处理死锁的方式是，将持有最少行级排他锁的事务回滚。（这是比较简单的死锁回滚方法）
    2. 死锁超时，就是查询时间达到锁等待时间就放弃锁请求，通常这种方式不太好。是我理解的，不管锁了，直接改的意思吗？

事务日志

  • 帮助提高事务的效率，类似输入井，缓冲区。修改数据需要写两次磁盘。

  1. 缓冲池可以帮助我们消除CPU和磁盘间的鸿沟。也被称作预写式日志。

    1. 使用事务日志，当有更新操作时，存储引擎只将数据在内存拷贝修改成更新后的值，但并不将数据的更新刷新磁盘，只是将更新操作的行为记录到硬盘上的事务日志中。因为事务日志的记录是采用文件追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头。并且仅仅记录数据更新行为的数据量也非常小，所以事务日志的记录是非常快的。
    2. 事务日志持久以后，对用户而言，数据的更新操作就完成了。内存中被修改的数据在后台可以慢慢的刷新到真正的硬盘数据库文件中。这样数据最终就被完整的存储好了。通过事务日志的方式更新数据，需要写两次磁盘，第一次是将更新行为记录到事务日志，第二次是真正的将更新后的数据记录到数据库文件中。

  2. 如果数据的修改行为已经记录到事务日志，但数据本身还没有写到数据库文件中，此时系统崩溃，存储引擎在重启之后会自动根据事务日志恢复这部分修改的数据。

    1. 缓冲池中的脏页会以一定的频率被刷入磁盘（check point机制）。事物日志会通过一个标记点来确定某个事物是否已将缓存中的数据写入数据文件。当db重启后，它会查看日志中最新的标记点，并将这个标记点后面的事务记录redo。

自动提交

  1. MYSQL默认自动提交（autocommit = 1）模式。
    1. autocommit = 1 ，如果不是显式的开启一个事务，每个查询都会被当作一个事务执行提交。
    2. autocommit = 0，所有查询都在一个事务里，直到显式执行了COMMIT或ROLLBACK。该事务结束，又开启一个新的事务。
    3. 修改autocommit，对非事务型的表，比如MYISAM或者内存表，无任何影响。相当于一直是autocommit = 1。
  2. 在DDL里，比如ALTER TABLE、LOCK TABLE等，执行前会强制COMMIT提交当前的活动事务。

存储引擎混用

  • 服务层不管事务，事务由存储引擎实现。如果混合使用了事务型和非事务型的表（比如MYISAM表和InnoDB表），在正常提交不会出现问题，如果需要回滚，非事务型的表的变更无法撤销，这种情况很难修复。

显式和隐式锁定

  1. 隐式锁定：
    1. InnoDB采用两阶段锁定协议。两段锁协议是指每个事务的执行可以分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。
      1. 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
      2. 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
      3. 两段封锁法可以这样来实现：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块所有的锁同一时刻被释放。 在对任何数据进行读、写操作之前，要申请并获得对该数据的封锁。每个事务中，所有的封锁请求先于所有的解锁请求。可以证明若并发执行的所有事务均遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的
  2. 显式锁定：
    1. InnoDB支持特定语句来显式锁定，这些语句不符合SQL规范[6]
      1. SELECT ... LOCK IN SHARE MODE
      2. SELECT ... FOR UPDATE
    2. MYSQL也支持LOCK TABLES[7]和UNLOCK TABLES语句，这是在服务器层实现，跟存储引擎无关。它们有特定用途，不能代替事务。如果应用要用到事务，就应该选择事务型存储引擎。

4 多版本并发控制

  1. MVCC是行级锁的变种，但很多情况下避免了加锁操作，因此开销更低。大都实现了非阻塞的读操作，写操作也只锁定必要的行。

    1. 大部分事务型存储引擎都是通过MVCC实现读已提交与可重复读，而非加锁。且MVCC只在这两个隔离级别下工作。
    2. 其他两个隔离级别和MVCC不兼容。因为未提交读总是读取最新的数据行，而不是符合当前事务版本要求的数据行。而串行化则会对所有读取的行加锁。

  2. MVCC的实现核心，是通过保存数据在某一个时间点的快照实现的。

    1. 一旦开始，同一个事务内部看到的数据是一致的。
    2. 根据事务开始时间的不同，不同事务对同一张表同一时刻看到的数据可能是不一样的。

  3. MVCC实现方式，有乐观和悲观并发控制两种。InnoDB的MVCC就是一种乐观实现方式。以下是其简化版行为。因为有特例，实际上还得再来三个隐藏字段控制。

    1. InnoDB的MVCC，是通过在每行记录后保存两个隐藏的列来实现的（用户不可见）。一个列保存行创建的系统版本号，一个列保存行过期（删除）的系统版本号。好处是，保存这两个额外的版本号，使大多数读操作不用加锁。坏处是，每行记录需要额外的存储空间，有更多的行检查和维护工作。

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

      下面看下在RR下，MVCC的操作
SELECT
InnoDB会根据以下两个条件检查每行记录：
（1）InnoDB只查找版本早于当前事务版本的数据行（行的系统版本号小于或者等于事务的系统版本号），这样可以确保事务读取到的行，要么是在事务开始之前已经存在的，要么是事务自身插入或者修改过的；
（2）行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取到的行，在事务开启之前未被删除。
INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB将更新后的列作为新的行插入数据表，并保存当前系统版本号作为该行的行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

5 MYSQL的存储引擎

文件存储

文件系统中，MYSQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MYSQL会在子目录下创建一个和表同名的.frm文件保存表的定义。可以使用show table status like '' \G 或查看information_shema中对应的表查看表的相关信息。

image-20220513211120292

InnoDB存储引擎

  1. 首选InnoDB：

    1. InnoDB是MYSQL的默认事务型引擎。它被设计成用来处理大量短期(short-lived)事务，短期事务大部分正常提交，很少被回滚。
    2. InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储中也很流行。除非有特别原因需要使用其他的存储引擎，否则应该优先考虑InnoDB。

  2. InnoDB数据存储在表空间（tablespace）里，表空间是InnoDB管理的一个黑盒子，由一系列的数据组成。在4.1以后版本，InnoDB可以将每个表的数据和索引放在单独的文件中。

  3. InnoDB部分特性：

    1. 支持事务。
    2. InnoDB使用间隙锁来实现串行化，解决幻读。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻行的插入。
    3. InnoDB基于聚簇索引实现，其索引结构和其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。因此，若表上的索引较多的话，主键应当尽可能小。
    4. InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区。
    5. 作为事务型存储，InnoDB通过一些工具和机制支持真正的热备份。其他的存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入。

MYISAM存储引擎

  1. MYISAM特性：
    1. 不支持事务和行级锁。MYISAM对整张表加锁，而不是针对行。也分读写锁。支持并发插入，在表有读取查询的同时，也可用往表中插入新的记录。
    2. MYISAM的缺陷有崩溃后无法安全恢复。
    3. 支持修复。对于MYISAM表，MYSQL可以手工或者自动检查和修复操作，但这里并非是崩溃后安全修复或事务修复。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable进行修复。
    4. 索引特性。对于MYISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MYISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
    5. MYISAM压缩表。适合创建并导入数据以后，不再进行修改操作的表。压缩表不能修改，除非先将表接触压缩，修改数据，然后再次压缩。
      1. 压缩表可以极大地减少磁盘空间占用，因此可以极大地减少磁盘I/O, 从而提升查询性能。压缩表也支持索引，但索引也是只读的。大部分场景，读取压缩表数据时解压带来的开销影响并不大，而减少I/O带来的好处则大的多。
      2. 压缩时表中的记录是独立压缩的，所以读取单行时不需要解压整张表。
    6. 性能。MYISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。MYISAM最典型的缺陷是表锁问题。
  2. MYISAM存储：MYISAM会把表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。
    1. MYISAM表可以动态或者静态（长度固定）行。MYSQL会根据表的定义来决定采用何种行格式
    2. MYISAM表可以存储的行记录数，一般受限于可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统的单个文件的最大尺寸。

MYSQL内建的其他存储引擎

CSV引擎
  1. CSV引擎可以将csv文件作为mysql的表处理，尽管不支持索引
  2. 把CSV文件复制到mysql数据目录下，就可以在mysql中打开使用；同样，如果把数据写入到一个CSV引擎表，外部程序可立即从表数据文件读取csv格式数据。因此，CSV引擎作为一种数据交换机制，非常有用。
Memory引擎
  1. Memory存储引擎，也称HEAP存储引擎，所有的数据都保存在内存中,如果MySQL服务重启，数据将会丢失,但是表的结构会保存下来。
  2. 功能特点：
    1. 比MYISAM表快一个数量级，因为数据都保存在内存，无需磁盘I/O
    2. 支持HASH索引和BTree索引(默认HASH索引)
    3. 所有字段都为固定长度，例如varchar(10)=char(10)。不支持BLOB和TEXT等大字段。
    4. Memory存储引擎使用表级锁，并发写入性能较低。
  3. 适用场景：
    1. 快速访问，数据不修改，容忍重启丢失
    2. 用于查找表或映射表
    3. 用于缓存周期性聚合数据结果
    4. 用于保存数据分析中产生的中间数据
  4. Memory表和临时表不是一回事：
    1. 临时表是适用CREATE TEMPORARY TABLE 语句创建的表，可以使用任何存储引擎。
    2. 临时表只在单个连接中可见，连接断开，临时表不复存在
选择合适引擎的考虑点
  1. 是否需要事务
  2. 是否需要备份以及在线热备份
  3. 是否需要崩溃恢复
  4. 特有特性
  • 日志型应用，对插入速度要求高，还有只读表，MYISAM就很适合
  • 订单表，必然要求事务，还有外键，InnoDB适合。
  • MYISAM不一定比InnoDB快，在很多已知场景，尤其是使用到聚簇索引，或需要访问的数据都可以放入内存的应用，InnoDB的速度都是MYISAM望尘莫及的。

表引擎切换

  1. AlTER TABLE mytable ENGINE = InnoDB

    1. 需要执行很长时间，MYSQL会按行将数据从原表复制到一张新表中，复制期间可能会消耗掉系统所有的I/O算力。所以，在繁忙表上这种操作要小心。
    2. 替换表的存储引擎，将失去原引擎特性。比如，将InnoDB表转成MYISAM表，再转回来，表上所有外键将会丢失

  2. 导入与导出

    1. 使用mysqldump将数据导出到文件，修改CREATE TABLE的ENGINE，然后导入

  3. 创建与查询

    1. 第三种方法综合了第一种的方便和第二种的安全

    2. 先创建一个新的存储引擎的表，然后利用INSERT……SELECT来导数据。如果有必要，可以对原表加锁，以确保新表与原表一致。

      CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;

    3. 数据量不大，这样很ok；数据量大，分批处理，针对每一段数据执行事务提交操作，并防止大事务产生过多的undo。可以用主键字段分批。

      START TRANSACTION;
InSERT INTO innodb_table SELECT * FROM myisam_table WHERE id between x and y;
COMMIT:

Reference

  1. InnoDB例外，它会解析外键定义，因为MYSQL服务器没实现这个功能 ↩︎

  2. MYSQL5.5及其以上版本提供了一个API，支持线程池插件，可以用池中少量进程服务大量的连接 ↩︎

  3. LEC6会讨论更多优化器细节 ↩︎

  4. 关于索引和SCHEMA的优化看LEC4、5 ↩︎

  5. LEC7讨论了缓存的相关内容 ↩︎

  6. 这些锁定提示经常被滥用，实际上应当避免使用。LEC6有更详细讨论 ↩︎

  7. LOCK TABLES和事务之间相互影响，情况会变得非常复杂，建议不要使用。 ↩︎

posted @ 2022-05-13 19:01  iterationjia  阅读(55)  评论(0)  编辑  收藏  举报