MySQL架构与历史

逻辑架构

• 客户端

• server层：

连接器： 管理连接，权限验证

查询缓存： 命中则直接返回结果

分析器： 词法分析，语法分析

优化器： 执行计划生成，索引选择

执行器： 操作引擎，返回结果

• 存储引擎： 存储数据，提供读写接口

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因

连接管理与安全性

优化与执行

并发控制

读写锁

共享锁（shared lock）–读锁（read lock）

排他锁（exclusive lock）–写锁（write lock）

读锁是共享的，相互不阻塞；多个客户在同一时刻可以同时读取同一资源，互不干扰； 写锁是排他的，一个写锁会阻塞其他的写锁和读锁；

锁粒度

在给定的资源上，锁定的数据量越少，则系统的并发程度越高；

锁策略：在锁的开销和数据的安全性之间寻求平衡；

表锁（table lock）

最基本的锁策略，并且是开销最小的策略；

行锁（row lock）

可以最大程度的支持并发处理，只在存储引擎层实现； 服务层完全不了解存储引擎中的锁实现；

事务

ACID

• 原子性–atomicity

• 一致性–consistency

• 隔离性–isolation

• 持久性–durability

隔离级别

• 读未提交–Read uncommitted

• 读已提交–read committed

• 可重复读–repeatable read

• 串行化–serializable

死锁

数据库系统实现了各种死锁检测和死锁超时机制； 越复杂的系统，越能检测到死锁的循环依赖，并立即返回一个错误；

InnoDB目前处理死锁的方法是将持有最少行级排他锁的事务进行回滚；

事务日志

事务日志可以帮助提高事务的效率；

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，不用每次将修改的数据持久化到磁盘；

事务日志采用的是追加的方式，写日志的操作是磁盘上一小块区域内的顺序IO，所以会快很多；

事务日志持久后，内存中被修改的数据在后台可以慢慢刷回磁盘；即预写式日志（Write-Ahead-Logging），修改数据需要写两次磁盘；

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

MySQL中的事务

自动提交

MySQL默认采用自动提交模式；每个查询都被当作一个事务执行提交操作；

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果autocommit=0，所有查询都是在一个事务中；直到显示的执行commit或者rollback

在事务中混合使用存储引擎

MySQL服务器不管理事务，事务是由下层的存储引擎实现的；在同一个事务中使用多种存储引擎是不可靠的；

隐式和显示锁定

InnoDB采用的两阶段锁定协议；在事务执行过程中，随时可以执行锁定，锁只有在执行commit或者rollback的时候才会释放；

显示锁定：

select … lock in share mode; select … for update;

多版本并发控制

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

MVCC只有在RR和RC两个隔离级别下工作，其他两个隔离级别和MVCC不兼容；RU总是读取最新的行，serializable则对所有读取加锁；

RR隔离级别下，MVCC具体操作：

• select

检查条件： 1.版本号遭遇当前事务版本的数据行； 2.行的删除版本要么未定义，要么大于当前事务版本号； 符合以上条件的记录才返回作为查询结果；

• insert

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

• delete

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

• update

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

存储引擎

mysql> show table status ;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| t    | InnoDB |      10 | Dynamic    |    4 |          16384 |       65536 |               0 |        65536 |         0 |           NULL | 2019-06-17 21:28:24 | NULL        | NULL       | latin1_swedish_ci |     NULL | partitioned    |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

InnoDB

默认事务存储引擎，也是最重要、最广泛使用的存储引擎； InnoDB被设计用来处理大量的短期事务；

概览

InnoDB的数据存储在表空间（tablespace），表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成；

MySQL4.1以后，InnoDB将每个表的数据和索引存放在单独的文件中；

采用MVCC来支持高并发；

实现四个标准的隔离级别；默认级别是可重复读；

通过间隙锁（next-key locking）策略防止幻读的出现；间隙锁使得InnoDB不仅锁定查询涉及的行，还会对索引中的间隙锁定，以防止幻影行的插入；

基于聚簇索引建立；对主键查询有很高的性能；二级索引必须包含主键列；索引较多的话，主键应当尽可能小；

从磁盘读取数据时采用可预测性读；

自适应哈希索引：能够自动在内存中创建hash索引以加速读操作；

插入缓冲区：加速插入操作；

InnoDB通过一些机制和工具支持真正的热备份，如MySQL Enterprise Backup、XtraBackUp； 其他存储引擎不支持热备份；

MyISAM

MySQL5.1之前的默认存储引擎；

支持全文索引、压缩、空间函数（GIS）等，不支持事务和行级锁；

崩溃后无法安全恢复；

对于只读的数据，或者表比较小、可以忍受修复操作，则可以选择MyISAM存储引擎；

存储

将表存储在两个文件中，数据文件：.MYD,索引文件：.MYI;

特性

• 加锁与并发

对整张表加锁；读取加共享锁；写入加排他锁；支持并发插入，即读取查询的同时插入新的记录；

• 修复

可以手工或者自动执行检查和修复；

检查表的错误： check table …;

修复： repair table …;

如果MySQL服务器已经关闭，检查和修复： myisamchk

• 索引特性

即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引；

支持全文索引；这是一种基于分词创建的索引，可以支持复杂的查询；

• 延迟更新索引

MyISAM压缩表

如果表在创建并导入数据以后，不会进行修改操作，适合采用MyISAM压缩表；

性能

数据以紧密格式存储，在某些场景下的性能很好；

最典型的问题是表锁的问题；

内建的其他引擎

• Archive引擎

只支持INSERT和SELECT操作； 适合日志和数据采集类应用； 支持行级锁和专用的缓冲区，可以实现高并发的插入；

• Blackhole引擎

• CSV引擎

• Federated引擎

• Memory引擎

在生产上，不建议使用普通内存表。

由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

InnoDB 表性能还不错，而且数据安全也有保障。而内存表由于不支持行锁，更新语句会阻塞查询，性能也未必就如想象中那么好。

• Merge引擎

• NDB引擎

第三方存储引擎

• OLTP类引擎

• 面向列的存储引擎

• 社区存储引擎

选择合适的引擎

转换表的引擎

1.alter table … engine=InnoDB;

2.mysqldump导出导入

3.创建与查询

create table new_table like old_table;
alter table new_table engine=InnoDB;
insert into new_table select * from old_table;

分批处理：

start tarnsaction;
insert into new_table select * from old_table wher id between a and y;
commit;

MySQL时间线

查看版本号：

status

status;
--------------
mysql  Ver 8.0.18 for osx10.14 on x86_64 (Homebrew)

Connection id:		8
Current database:	test
Current user:		root@localhost
SSL:			Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.11 MySQL Community Server - GPL
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Uptime:			1 day 2 hours 7 min 54 sec

Threads: 2  Questions: 25  Slow queries: 0  Opens: 135  Flush tables: 2  Open tables: 110  Queries per second avg: 0.000
--------------

select version();

+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

1995年，MySQL1.0发布，仅供内部使用；

1996年，MySQL3.11.1发布，直接跳过了MySQL 2.x版本。

1999年，MySQL AB公司成立。同年，发布MySQL3.23，该版本集成了Berkeley DB存储引擎。 该引擎由Sleepycat公司开发，支持事务。 在集成该引擎的过程中，对源码进行了改造，为后续可插拔式存储引擎架构奠定了基础。

2000年，ISAM升级为MyISAM存储引擎。同年，MySQL基于GPL协议开放源码。

v3.23(2001)

开始获得广泛应用；

引入InnoDB，需要手工编译；

全文索引和复制；

复制是MySQL成为互联网应用的数据库系统的关键特性；

v4.0(2003)

支持新语法，比如UNION和多表DELETE；

重写了复制，在备库使用了两个线程来实现复制，避免了之前一个线程做所有复制工作的模式下任务切换导致的问题；

InnoDB成为标准配置，包括行级锁、外键等特性；

引入查询缓存；

支持通过SSL连接；

v4.1(2005)

引入更多语法，比如子查询、INSERT ON DUPLICATE KEY UPDATE；

UTF-8字符集；

支持新的二进制协议和prepared语句；

v5.0(2006)

企业级特性：视图、触发器、存储过程、存储引擎；

老的ISAM引擎彻底删除，引入新的Federated等引擎；

v5.1(2008)

SUN收购MySQL AB以后发布的首个版本；

分区、基于行的复制；

plugin API（包括可插拔存储引擎的API）

v5.5(2010)

Oracle收购SUN之后的首个版本；

改善性能、扩展性、复制、分区，对Windows的支持；

InnoDB成为默认存储引擎；

PERFMANCE_SCHEMA库，包含一些可测量的性能指标的增强；

复制、认证和审计API；

半同步复制；

v5.6(2013)

MySQL 5.6是MySQL历史上一个里程碑式的版本，这也是目前生产上应用得最广泛的版本；

复制优化；

mysqlbinlog可远程备份binlog；

对TIME, DATETIME和TIMESTAMP进行了重构，可支持小数秒。DATETIME的空间需求也从之前的8个字节减少到5个字节。

Online DDL。ALTER操作不再阻塞DML；

可传输表空间（transportable tablespaces）；

统计信息的持久化。避免主从之间或数据库重启后，同一个SQL的执行计划有差异；

全文索引；

InnoDB Memcached plugin；

EXPLAIN可用来查看DELETE，INSERT，REPLACE，UPDATE等DML操作的执行计划，在此之前，只支持SELECT操作；

分区表的增强，包括最大可用分区数增加至8192，支持分区和非分区表之间的数据交换，操作时显式指定分区；

Redo Log总大小的限制从之前的4G扩展至512G；

Undo Log可保存在独立表空间中，因其是随机IO，更适合放到SSD中。但仍然不支持空间的自动回收；

可dump和load Buffer pool的状态，避免数据库重启后需要较长的预热时间；

InnoDB内部的性能提升，包括拆分kernel mutex，引入独立的刷新线程，可设置多个purge线程；

优化器性能提升，引入了ICP，MRR，BKA等特性，针对子查询进行了优化；

v5.7(2015)

新特性

组复制;

InnoDB Cluster;

多源复制;

增强半同步（AFTER_SYNC）;

基于WRITESET的并行复制;

在线开启GTID复制;

在线设置复制过滤规则;

在线修改Buffer pool的大小;

在同一长度编码字节内，修改VARCHAR的大小只需修改表的元数据，无需创建临时表;

可设置NUMA架构的内存分配策略（innodb_numa_interleave）;

透明页压缩（Transparent Page Compression）;

UNDO表空间的自动回收;

查询优化器的重构和增强;

可查看当前正在执行的SQL的执行计划（EXPLAIN FOR CONNECTION）;

引入了查询改写插件（Query Rewrite Plugin），可在服务端对查询进行改写;

EXPLAIN FORMAT=JSON会显示成本信息，这样可直观的比较两种执行计划的优劣;

引入了虚拟列，类似于Oracle中的函数索引;

新实例不再默认创建test数据库及匿名用户;

引入ALTER USER命令，可用来修改用户密码，密码的过期策略，及锁定用户等;

mysql.user表中存储密码的字段从password修改为authentication_string;

表空间加密;

优化了Performance Schema，其内存使用减少;

Performance Schema引入了众多instrumentation。常用的有Memory usage instrumentation，可用来查看MySQL的内存使用情况，Metadata Locking Instrumentation，可用来查看MDL的持有情况，Stage Progress instrumentation，可用来查看Online DDL的进度;

同一触发事件（INSERT，DELETE，UPDATE），同一触发时间（BEFORE，AFTER），允许创建多个触发器。在此之前，只允许创建一个触发器;

InnoDB原生支持分区表，在此之前，是通过ha_partition接口来实现的;

分区表支持可传输表空间特性;

集成了SYS数据库，简化了MySQL的管理及异常问题的定位;

原生支持JSON类型，并引入了众多JSON函数;

引入了新的逻辑备份工具-mysqlpump，支持表级别的多线程备份;

引入了新的客户端工具-mysqlsh，其支持三种语言：JavaScript, Python and SQL。两种API：X DevAPI，AdminAPI，其中，前者可将MySQL作为文档型数据库进行操作，后者用于管理InnoDB Cluster;

mysql_install_db被mysqld –initialize代替，用来进行实例的初始化;

原生支持systemd;

引入了super_read_only选项;

可设置SELECT操作的超时时长（max_execution_time）;

可通过SHUTDOWN命令关闭MySQL实例;

引入了innodb_deadlock_detect选项，在高并发场景下，可使用该选项来关闭死锁检测;

引入了Optimizer Hints，可在语句级别控制优化器的行为，如是否开启ICP，MRR等，在此之前，只有Index Hints;

GIS的增强，包括使用Boost.Geometry替代之前的GIS算法，InnoDB开始支持空间索引;

删除的特性：

Deprecated特性：

http://www.thecompletelistoffeatures.com/

v8.0(2018)

新特性

去掉了查询缓存

移除PASSWORD()函数。这就意味着无法通过“SET PASSWORD … = PASSWORD(‘auth_string’) ”命令修改用户密码

Data dictionary; 引入了原生的，基于InnoDB的数据字典。数据字典表位于mysql库中，对用户不可见，同mysql库的其它系统表一样，保存在数据目录下的mysql.ibd文件中。不再置于mysql目录下

Atomic data definition statements (Atomic DDL).;

Upgrade procedure;

Security and account management;

Resource management;

Table encryption management;

InnoDB enhancements;

Character set support;

JSON enhancements;

Data type support;

Optimizer;

开发模式

GA(Generally Available)通常可用的版本

在Oracle定期发布的新里程开发版本中，会包含即将在下一个GA版本发布的新特性；

遵循GPL开源协议；

Oracle为付费用户单独提供一些服务插件；

总结

如果能理解MySQL在存储引擎和服务层之间处理查询时如何通过API来回交互，就能理解MySQL的核心基础架构的精髓；

MySQL最初基于ISAM构建（后来被MyISAM取代），其后陆续添加了更多的存储引擎和事务支持；

refs