MySQL数据库的体系结构详解，MyISAM和InnoDB存储引擎

MySQL是单进程多线程数据库

它分为三层：

第一层：网络连接层

 连接与线程处理，比如连接处理、授权认证、安全等。（通信协议，线程，验证）

mysql的连接管理方式：

Mysql-Server同时支持3种连接管理方式，包括No-Threads，One-Thread-Per-Connection和Pool-Threads。

No-Threads：表示处理连接使用主线程处理，不额外创建线程，这种方式主要用于调试；

One-Thread-Per-Connection：是线程池出现以前最常用的方式，为每一个连接创建一个线程服务；

Pool-Threads：则是线程池方式。

第二层：SQL处理层，主要有SQL Interface、Parser、Optimizer、Cache和Buffer

SQL处理层功能：解析器,授权,优化器,查询执行,查询高速缓存,查询日志记录，跨存储引擎功能

1.解析器：解析SQL语法，形成语法树

2.授权:SQL的权限验证  *.*对于指定的库和表

3.优化器:CBO（基于成本的优化）,根据统计信息--> SQL改写 --->执行计划(即选哪种算法执行)

sql层处理数据流程：

用户传入sql-----查询缓存（命中缓存可直接返回结果）----解析器（生成sql解析树）----预处理器（可能sql等价改写）-----查询优化器（生成sql执行计划）----查询执行引擎----结果返回给用户。

SQL层的组成以及功能：

SQL接口：（SQL Interface）

 功能：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

解析器：（Parser）--生成sql解析树

SQL命令传递到解析器的时候会被解析器验证和解析（进行语义和语法的分析，分解成数据结构，如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的 ），生成sql解析树。解析器是由Lex和YACC实现的，是一个很长的脚本

查询优化器：（Optimizer） --生成执行计划

 SQL语句在查询之前会使用查询优化器对查询进行优化，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果，即执行计划。查询优化器使用选取-投影-联接策略生成执行计划。

选取-投影-联接：

用一个例子就可以理解： select uid,name from user where gender = 1;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。

将这两个查询条件联接起来生成最终查询结果。

查询缓存功能（Cache和Buffer）:（建议关闭）

当执行sql的时候,sql第一次被执行,然后再次执行的时候如果相同的sql,可以不进行解析，直接返回结果,提高查询效率.

关闭查询缓存：query_cache_type = 0    query_cache_size = 0

局限性比较大，任何查询结果有变更，都需要进行更新，对于mysql性能影响比较严重,整个更新过程的锁颗粒度的比较高,还持有全局锁，效率很低.

建议:是否使用查询缓存,不用.(在mysql8.0里没了查询缓存功能.)

问题：如何计算和提高查询缓存命中率？

第三层：存储引擎层，负责MySQL中数据的存储和提取

功能：存储引擎，也称为表类型，真正的负责了MySQL中数据的存储和提取，储存引擎层由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。不同的存储引擎采用不同的技术（存储机制、索引机制、锁定机制）存储数据。MySQL的存储引擎是插件式的，也就是说，用户可以随时切换MySQL的存储引擎：针对表或针对库都可（通过SQL语句命令）。MySQL集合了多种引擎：MyISAM、InnoDB、BDB、Merge、Memory等，默认的是InnoDB。

存引擎层说明：

1、根据上层获取数据的方法（执行计划），将数据提取出来。

2、重新再交给SQL层。

3、是MYSQL数据库的核心，关系到数据库性能。

4、存储引擎是基于表的，而不是数据库。

 

MyISAM存储引擎

MySQL5.5前默认使用的存储引擎

它是MySQL系统表，临时表（查询优化器为方便查较大量数据建立的）使用的存储引擎

并发性和锁级别：读写互斥，并发支持不好，只读还可以接受

表损坏修复：会造成数据丢失

MyISAM表由 三个文件来存储，myIsam.frm（存储表结构信息），myIsam.MYD（存储表数据信息），myIsam.MYI

支持索引：全文索引，前缀索引，

不支持事务

只读类应用，共享锁，并发性好

可压缩，空间类应用，使用空间函数

InnoDB

5.5.8后成为MySQL默认存储引擎

特点：

事务型存储引擎

完全支持事务的ACID特性

使用表空间进行数据存储，默认会为每个表建立独立表空间 innodb_file_per_table

InnoDB存储的文件

tableName.frm  记录表结构

tableName.ibd  存储数据

系统表空间和独立表空间要如何选择：

系统表空间无法简单的收缩文件大小

独立表空间可以通过optimize table命令收缩系统文件

系统表空间会产生IO瓶颈

独立表空间可以同时向多个文件刷新数据

建议InnoDB使用独立表空间，而且在MySQL5.6以后默认使用独立表空间