MySQL整体认识
一、MySQL逻辑架构
图1.1 MySQL服务器逻辑架构
整体架构分为三层，最上层负责如连接处理、授权认证、安全等问题；第二层集中了大多数核心功能，包括查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等；第三层包含了存储引擎，存储引擎负责MySQL中数据的存储和提取。存储引擎不会去解析SQL(InnoDB例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能)，不同存储引擎之间也不会相互通信，只是简单地响应上层服务器的请求。
1.1 连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或销毁线程。在MySQL5.5或更新的版本中提供了一个API，支持线程池插件，可以使用池中少量的线程来服务大量的连接。
当客户端连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字(SSL)的方式连接，还可以使用X.509证书认证。一旦连接成功，服务器会继续验证该客户是否具有执行某个特定权限的查询。
1.2 优化与执行
MySQL会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。也可以请求优化器解释(explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，是应用尽可能高校运行。
优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
对于select语句，在解析查询之前，服务器会先检查查询缓存(Query Cache)，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
二、 MySQL存储引擎
在文件系统中，MySQL将每个数据库(也可以称之为schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个mydata表，MySQL会在mydata.frm文件中保存该表的定义。因为MySQL使用文件系统的目录和文件来保存数据库的表和定义，大小写敏感性和具体的平台密切相关。在Windows中，大小写是不敏感的，而在类UNIX中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但在表的定义则是在MySQL服务层同一处理的。
可以使用 show table status命令(5.0以后也可以查询INFORMATION_SCHEMA中对应的表)显示表的相关信息。
1 示例1： 2 mysql> show table status like 'test'\G 3 *************************** 1. row *************************** 4 -- 5 Name: test 6 Engine: InnoDB 7 Version: 10 8 Row_format: Dynamic 9 Rows: 13 10 Avg_row_length: 1260 11 Data_length: 16384 12 Max_data_length: 0 13 Index_length: 0 14 Data_free: 0 15 Auto_increment: NULL 16 Create_time: 2020-10-06 11:30:57 17 Update_time: 2020-10-11 11:01:00 18 Check_time: NULL 19 Collation: utf8_general_ci 20 Checksum: NULL 21 Create_options: 22 Comment: 23 1 row in set (0.01 sec) 24 -- 25
信息说明：
|
Name |
表名 |
Max_data_length |
表数据的最大容量，该值与存储引擎有关 |
Check_time |
使用CHECK TABLE命令或者相关工具最后一次检查表的时间 |
|
Engine |
存储引擎，旧版本中该列叫Type |
Index_length |
索引的大小(以字节为单位) |
Collation |
表的默认字符集和字符列排序规则 |
|
Row_format |
行的格式(如MyISAM有三个选项：Dynamic，Fix ed、Compressed，Dynamic的行长度是可变的) |
Data_free |
已分配但目前没有使用的空间 |
Checksum |
如果启用，保存的是整个表的实时校验和 |
|
Rows |
表中的行数，对于InnoDB该值是估计值 |
Auto_increment |
下一个AUTO_INCREMENT的值 |
Create_options |
创建表时指定的其他选项； |
|
Avg_row_length |
平均每行包含的字节数 |
Create_time |
表的创建时间 |
Comment |
额外信息，对于InnoDB保存的是InnoDB表空间的剩余空间信息 |
|
Data_length |
表数据的大小(以字节为单位) |
Update_time |
表数据的最后修改时间 |
2.1 InnoDB存储引擎
- InnoDB是MySQL的默认事务型引擎，是使用最广泛最重要的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交，很少被回滚。
- InnoDB的数据存储在表空间(tablespace)中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。
- InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别。默认级别是RR，并通过间隙锁(next-key locking)策略防止幻读的出现。
- InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的二级索引中必须包含主键列，所以主键列很大的话，其他的所有索引都会很大。因此若索引很多的话，主键应尽量的小。InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Intel平台复制到PowerPC平台。
- InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作缓冲区等。
- InnoDB通过一些机制和工具支持真正的热备份，MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
2.2 MyISAM存储引擎
- 在5.1级之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
- MyISAM将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态(长度固定)行。MySQL会根据表的定义来决定采用何种行格式。MyISAM可以可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
- MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对整张表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录(concurrent insert)。
- 对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是很慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行repair table mytable进行修复。如果MySQL服务器已经关闭，也可以通诺myisamchk命令工具进行检查和修复。
- 对于MyISAM表，即使是BLOB和TXET等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
- 创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区(in-memory key buffer)，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。但这种方式在数据库或者主机崩溃的时候回造成索引的损坏，需要执行修复操作。
- 如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许更适合采用MyISAM压缩表。压缩表是不能进行修改的。压缩表可以极大地减少磁盘空间占用(除非先解压缩，修改数据后在压缩)。压缩表可以极大地减少磁盘空间占用，因此也可以减少I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
2.3 Memory存储引擎
如果需要快速访问数据，并且这些数据不会被修改，重启以后丢失也没关系，那么可以该表将非常有用。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都在内存中。Memory表的结构在重启以后还会保留，但数据会丢失。
Mrmory作用场景：
- 用于查找(lookup)或者映射(mapping)表，例如将邮编和地址名映射的表
- 用于缓存周期性聚集数据的结果
- 用于保存数据分析中产生的数据
Memory表支持Hash索引，因此查找操作非常快。尽管如此，但还是无法取代传统的基于磁盘的表。Memory表是表级锁，因此并发写入性能低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了varchar列，实际存储也会转换成char列，这可能导致部分内存的浪费。不过现在有些缺陷已经解决了。
如果MySQL在执行查询过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表。如果中间结果太大超出了Memory表的限制，或者含有BLOB或者TEXT字段，则临时表会转换成MyISAM表。但Memory表与临时表是两个概念，临时表是指使用CREATE TEMPORARY TABLE语句创建的表，它可以使用任何存储引擎。
2.4 NDB集群引擎
MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群。
除此之外，MySQL还有Merge引擎、Federated引擎、CSV引擎、Blackhole引擎、Archive引擎以及一些第三方存储引擎。
