1. MySQL架构与历史

2020-04-21  本文已影响0人  darcyaf

1.1 MySQL逻辑架构

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，而且只能使用单个CPU核心。服务器会负责缓存线程，因而不用每次都去创建或者销毁线程。当连接进来时，会做安全校验，用户名密码，特定操作的权限等

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构(解析树),然后对其进行各种优化，包括重写查询，决定表的读取顺序，索引的选择等。
对于SELECT语句，解析查询时，会先检查缓存(Query Cache),如果命中则不必执行查询解析，优化，执行等过程。

1.2 并发控制

1.2.1 读写锁

1.2.2 锁粒度

一种提高共享资源并发性的方式就是放锁定对象更有选择性。尽量值锁定需要修改的部分数据，而不是所有的资源。像Innodb就可以使用行级锁，锁的粒度越大，就越容易发生冲突。
所谓锁策略，其实就是在锁的开销和数据的安全性之间寻求平衡。加锁本身也需要消耗资源，粒度越细，锁就越难管理

1.3 事务

所谓事务就是一组原子性的SQL查询，要么都成功，要么都失败。

1.3.1 隔离级别

1.3.2 死锁

死锁是指两个或者多个事务在统一资源上互相占用，并请求对方锁定的资源。 从而导致恶性循环。
Innodb目前处理死锁的方法是: 将持有最少行级排它锁的事务进行回滚。

1.3.3 事务日志

事务日志可以帮助提高事务的效率。 使用事务日志，存储引擎在修改表的诗句只需要修改其内存拷贝，再把该修改行为记录到持久化到硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快的多。 事务日志持久化后可以在后台慢慢的将数据刷到磁盘。目前大多数存储引擎都是如此，我们称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并且持久化了，但数据本身还没写回磁盘，此时系统崩溃，存储引擎能够在重启后自动恢复这部分的数据。

1.3.4 MySQL中的事务

MySQL提供了，两种事务型的存储引擎: InnoDB 和NDB Cluster。

[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF

支持的隔离级别如下

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

另外也可以通过特定的语句进行显式锁定。

  1. select .. lock in share mode
  2. select ... for update

1.4 多版本并发控制

MVCC可以认为是行级锁的一个变种，但是在很多情况下避免了加锁操作，因此开销更低。
不同的存储引擎的MVCC实现是不一样的。典型的有乐观并发控制和悲观并发控制。

InnoDB中的实现

在innoDB中，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存了行的过期时间。这里存储的是系统版本号。
每开始一个事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较。
下面是一个在repeatable read隔离级别下的MVCC的具体操作。

保存这两个版本号，是的大多数读操作都不用加锁。这样设计是的读数据操作很简单，性能很好，不足的是每行记录都需要额外的存储空间，需要做更多的检查和维护工作。
MVCC只在repeatable read 和read committed 两个隔离级别下工作。

1.5 MySQL的存储引擎

在文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时MySQL会在子目录下创建一个和表同名的.frm文件保存表的定义。
可以使用show table status或者查看infomation_schema中对应的表显示表的相关信息。
1.5.1 InnoDB存储引擎
InnoDB采用MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是repeatable read，并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的聚簇索引对主键查询有很高的性能。　不过他的二级索引中必须包含主键列，如果主键列很大的话会导致所有的索引都会变大。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)

1.5.2 MyISAM存储引擎

提供了大量的特性，如全文索引，亚索，空间函数(GIS)等。
MyISAM不支持事务和行级锁，另外就崩溃后无法安全恢复。尽管如此，对于只读的数据，或者表比较小，可以忍受修复操作，则仍然可以使用

  1. 性能
    MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。但是有一些服务器级别的性能拓展限制，比如对索引键缓冲区(key cache)的mutex锁，MariaDB基于段的索引键缓冲区机制来避免该问题。
    但最典型的性能问题还是表锁的问题。

1.5.3 内建的其他存储引擎

  1. Memory引擎
    如果需要快速的访问数据，并且这些数据不会被修改，重启后丢失也没关系，那么就用memory表。能比MyISAM快上一个数量级，因为所有的数据都保存在内存中。
    应用场景:

它是表级锁，因此并发写入的性能比较低。不支持blob或者text类型的列，并且每行的长度是固定的，所以即使制定了varchar列，实际上也会转为char，导致了部分内存的浪费。
MySQL在执行查询过程中需要使用临时表来保存中间结果，内部使用的临时表就是memory表。如果中间结果太大，超出了momery表的限制，或者含有blob或text字段，则临时表会转换为MyISAM表。
临时表是指使用create temporary table语句创建的表，它可以使用任何存储引擎。

1.5.5 选择引擎

大部分情况下InnoDB都是正确的选择,一句话就是，除非需要使用到InnoDB不具备的特性，而且没有其他办法代替，否则都应该优先选择InnoDB。
例如，如果要用到全文索引，优先考虑InnoDB加上Sphinx的组合，而不是直接用MyISAM。
除非万不得已，否则尽量避免混合使用多种存储索引。
选择的标准:

日志型应用

如果要将日志记录到MySQL，对数据的插入速度有很高的要求。可以尝试使用MyISAM或者Archive存储引擎,因为开销低，插入非常快。
但是在对记录的数据做分析报表时，SQL可能会导致插入效率变低。
一种解决方案是复制数据到备库，然后在备库上做耗时sql等查询。
另一种方案是在日志记录表上加入年月信息，水平分表。

只读或者大部分情况下只读的表

如果不介意崩溃恢复的问题的话，可以选用MyISAM。
不要轻易相信MyISAM比InnoDB快之类的经验之谈，现如今来说其实InnoDB的性能已经差不多了，尤其是可以使用聚簇索引，或者需要访问的数据都能放入内存时，速度让MyISAM望尘莫及。所以建议还是使用InnoDB，MyISAM在随着应用压力上升容易导致锁竞争崩溃等问题会陆续出现。

订单处理

如果涉及订单处理，那么支持事务就是必要选项。半完成的订单是无法用来吸引用户的。

大数据量

当数据量过大时，就需要建立数据仓库了，可以使用infobright(数据量千万时查询性能仍然很高，但是不能支持高并发)。
此外现在也有很多诸如elasticsearch,clickhouse,hbase等产品，更适合做这种大数据下的处理。

1.5.6 转换表的引擎

alter table

alter table可以修改表的引擎

alter table mytable engine=InnoDB;

上述语法可以改变存储引擎，但是会执行很长时间。MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同事会在原表上加读锁。所以在繁忙的表上执行这个操作时要特别小心。
如果转换表的存储引擎，将会失去和原引擎相关的所有特性。例如如果将一张表从InnoDB转换为MyISAM在转回InnoDB，原InnoDB表上的外键将会丢失。

导入与导出

为了更好的控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中create table中的存储引擎选项，同时修改表名(mysqldump默认会自动在create table 前加上drop table语句)。

创建与查询

使用insert into ...select语法来导数据:

create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDB;
insert into innodb_table select * from myisam_table

数据量不大时这样做工作的很好，如果数据量很大，可以考虑做分批处理，针对每一段数据执行事务提交操作。
假设有主键id,则可以重复执行以下语句:

start transaction;
insert into innodb_table select * from myisam_table
      where id between min_id and max_id;
commit;

如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。
Percona Toolkit 提供了online-schema-change的工具，可以比较简单的执行上述过程，避免手工误操作。

1.6 总结

本章主要讲了乐观锁和悲观锁，事务的ACID，事务的几种隔离级别，InnoDB用MVCC(多版本并发控制)实现了行级锁，用间隙锁(next-key lock)来防止幻读的出现。

上一篇 下一篇

猜你喜欢

热点阅读