MySQL之逻辑架构【一】

日常开发过程中需要经常跟数据库打交道，很多业务都和数据库表的息息相关，因为最常用的是MySQL，所以特地总结这一系列跟MySQL相关的文章方便以后自己查阅和复习。

首先整体了解下MySQL逻辑架构图：

从图中看到：MySQL整体逻辑架构分为Server层和存储引擎层。

一、Server层：

Server层涵盖了MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），以及存储过程、触发器、视图等存储引擎的实现也在这一层来实现。

• 连接器：负责跟客户端建立连接、获取权限、维持和管理连接；
• 分析器：SQL词法分析，SQL语法分析；
• 优化器：索引选择，选择一个执行效率高的生成执行计划；
• 执行器：操作引擎，返回执行结果；
• …
• 查询缓存：执行SQL语句之前，先查缓存，缓存结果可能是以key-value的方式存储的，key是查询的语句，value是查询的结果。

这其中跟开发关系最紧密的就是优化器里的执行计划，要分析一个SQL的执行效率就要看执行计划，根据执行计划优化SQL，使其能达到高效查询的目的。一条查询语句需要经过MySQL查询优化器的各种基于成本和规则优化后，生成一个所谓的执行计划。这个执行计划主要展示具体执行查询的方式，比如多表连接的顺序，表里包含多少个索引，每个表采用什么访问方式来具体执行查询等。

二、存储引擎层：

负责数据的存储和提取，是一种插件式的架构方式。MySQL支持很多存储引擎，比如：InnoDB、MyISAM、Memory、NDB Cluster，其中InnoDB和NDB Cluster 存储引擎提供了事务处理能力。MySQL5.5.5之后默认存储引擎是InnoDB，之前是MyISAM，另外MySQL的存储引擎是针对表的，而不是针对整个数据库的目前最常用的是MyISAM和InnoDB。

MyISAM不支持事务，用的表级锁；InnoDB支持事务，用的行级锁，被设置用来处理大量短期事务，短期事务大部分情况是正常提交的，很少会回滚。

InnoDB特点：
采用多版本并发控制（MVCC，MultiVersion Concurrency Control）来支持高并发。并且实现了四个标准的隔离级别，通过间隙锁next-key locking策略防止幻读的出现。

InnoDB引擎的表基于聚簇索引建立，聚簇索引对主键查询有很高的性能。不过它的二级索引secondary index非主键索引中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽量小。另外InnoDB的存储格式是平台独立的。

InnoDB做了很多优化，比如：磁盘读取数据方式采用的可预测性预读、自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。

InnoDB通过一些机制和工具支持真正的热备份，MySQL的其它存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM特点和应用场景：
MyISAM是MySQL5.1及之前的版本的默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行级锁，对于只读数据，或者表比较小、可以容忍修复操作，依然可以使用。

MyISAM采用的是表锁。读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这称为并发插入。

MyISAM表可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作很慢。

对于MyISAM表，即使是Blob和Text等大字段，也可以基于其前500个字符创建索引，MyISAM也支持全文索引，这是一种基于分词创建的索引，也可以支持复杂的查询。

如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

另外可以查看数据库当前支持的存储引擎：
show table status from ‘db_name’ where name=‘table_name’；
查询结果表中Engine字段指示存储引擎类型。

因为操作数据库一般都是通过Navicat，所以就不通过命令行的方式进行操作了，可以通过选项进行查看和选择存储引擎：

在MySQL中，主要有四种类型的索引：B-Tree、Hash、FullText和R-Tree。

B-Tree索引是很多数据库管理系统中最主要的索引类型，主要是因为B-Tree索引的存储结构在数据库的检索中有非常优异的表现。其中InnoDB使用的是B+Tree，是在B-Tree基础上做了很小的改造。