了解MySQL（超详细的MySQL工作原理 体系结构）

• 1.MySQL体系结构
• 2.MySQL内存结构
• 3.MySQL文件结构
• 4.innodb体系结构一、了解MySQL前你需要知道的引擎是什么:MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。当我们理解了引擎这个概念，自然而然就知道引擎层的作用就提供各种不同引擎给你选择，然后用你选出来的引擎去处理sql语句二、MySQL体系结构MySQL 最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理 （Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种 处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储 的方式。

由图，可以看出MySQL最上层是连接组件。下面服务器是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。

• 用户：进行数据库连接的人。
• 支持接口：是第三方语言提供和数据库连接的接口，常见的有jdbc,odbc,c的标准api函数等等。
• 管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql复制、集群等（见图）
• 连接层：提供与用户的连接服务，用于验证登录服务。

—> 连接池：由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

• 服务层：完成大多数的核心服务功能。有sql接口，解析器parser,优化器optimizer,查询缓存 cache/buffer 。

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

​ —>解析器: SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。其功能是：

a.将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

​ b.如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

​ —>优化器：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。举一个例子：

select * from users where uname='admin';

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

​ 2.这个select查询先根据*进行属性投影，而不是将属性全部取出以后再进行过滤。（投影）

​ 3.将这两个查询条件联接起来生成最终查询结果。(联接)

​ —>缓存器： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

​ 简而言之， 服务层执行过程：sql语句通过sql接口，服务器如果缓存cache有命中查询结果，直接读取数据。如果没有命中查询结果，由解析器进行sql语句的解析，预处理，经过优化器进行优化后提交给引擎层。通俗地说—>服务层告诉引擎层要做什么。

• 引擎层: 提供各种存储引擎，真正的负责MySQL中数据的存储和提取。常见有innodb myisam 。innodb支持全文索引，事务（高并发），行锁，myisam 性能优先。 mysql中查询引擎语句:show engines.通俗地说—>引擎层会转发服务层解析出来的sql语句告诉存储层要做什么(增删改查)并且告诉存储层要以何种方式做(innodb myisam等等)。
• 存储层： 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。SQL的执行流程：数据库通常不会被单独使用，而是由其它编程语言通过SQL支持接口调用MySQL。由MySQL处理并返回执行结果。首先，其它编程语言通过SQL支持接口调用MySQL，MySQL收到请求后，会将该请求暂时放在连接池，并由管理服务与工具进行管理。当该请求从等待队列进入到处理队列时，管理器会将该请求传给SQL接口，SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的数据进行对比，如果匹配则通过缓存直接返回处理结果；否则，去文件系统查询：由SQL接口传给后面的解析器，解析器会判断SQL语句是否正确，若正确则将其转化为数据结构。解析器处理完毕后，便将处理后的请求传给优化器控制器，它会产生多种执行计划，最终数据库会选择最优的方案去执行。确定最优执行计划后，SQL语句交由存储引擎处理，存储引擎将会到文件系统中取得相应的数据，并原路返回。在我们专业老师的pdf资料里面每一层也讲得很细 见下

创建新表时如果不指定存储引擎，那么系统就会使用默认存储引擎，MySQL5.5 之前的 默认存储引擎是 MyISAM，5.5 之后改为了 InnoDB。 MySQL 中同一个数据库，不同的表格可以选择不同的存储引擎。

• MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性 没有要求或者以 SELECT、INSERT 为主的应用。每个 MyISAM 在磁盘上存储成 三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)
• InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空 间以保存数据和索引。InnoDB：所有的表都保存在同一个数据文件中，InnoDB 表的大小只受限于操作系统文件的大小限制。Myisam 只缓存索引，不缓存真实数据；Innodb 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
• MEMORY 存储引擎使用存在于内存中的内容来创建表。MEMORY 类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是 一旦服务关闭，表中的数据就会丢失。主要用于那些内容变化不频繁的代码表或者作为统计操作的中间结果表。三、MySQL内存结构MySQL中内存大致分为：全局内存（Global buffer）、线程内存（Thread buffer） 两大部分。

全局内存：缓冲池里面有数据缓存、索引缓存、锁信息、插入缓存等等。此外还有重做日志缓存、额外的内存池。线程内存：Master Thread、IO Thread、Purage Thread、Page Cleaner Thread。

CheckPoint技术：缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。因此跟新或者删除的时候直接操作的是内存的数据，先写入重做日志，然后再修改内存池里面的数据，最后定时刷新到磁盘上。

四、MySQL文件结构

1. 参数文件：启动MySQL实例的时候，指定一些初始化参数，比如：缓冲池大小、数据库文件路径、用户名密码等。my.cnf读取优先级是从左自右的顺序,但是当默认读取路径都有配置文件时,最后读取的参数的值，会覆盖前面读取的参数的值。/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf~/.my.cnf
2. 日志文件：比如：错误日志、二进制日志、慢查询日志、查询日志等等。例如：通过show variables like "error_log"来查看错误日志存放内容。
3. socket文件：当用UNIX域套接字方式进行连接的时候需要的文件。
4. pid文件：MySQL实例的进程ID文件。
5. 表结构文件：用来存放MySQL表结构定义文件。.frm后缀命名的文件都是表结构文件，和存储引擎类型无关。所有的表都会生成一个.frm文件；
6. 存储引擎文件：存储引擎正在存储了记录和索引等数据。

表空间可以在逻辑上管理多个数据文件，而这些数据文件又可以分布在不同磁盘中这就使得一个表的数据、索引等信息可以被记录在多个磁盘中。

（1）共享表空间：共享表空间文件以.ibdata*来命名； 共享表空间下，innodb所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。共享表空间主要存放double write、undo log（undo log没有独立的表空间，需要存放在共享表空间）

（2）独立表空间：每个表拥有自己独立的表空间用来存储数据和索引。

（3）查看数据库是否启用独立表空间：show variables like ‘innodb_file_per_table’;查看，innodb_file_per_table=ON,表示启用了独立表空间；

（4）使用独立表空间的优点：a.如果使用软链接将大表分配到不同的分区上，易于管理数据文件b.易于监控解决IO资源使用的问题；c.易于修复和恢复损坏的数据；d.相互独立的，不会影响其他innodb表；e.导出导入只针对单个表，而不是整个共享表空间；f.解决单个文件大小的限制；g.对于大量的delete操作，更易于回收磁盘空间；h.碎片较少，易于整理optimize table；i.易于安全审计；j.易于备份如果在innodb表已创建后设置innodb_file_per_table，那么数据将不会迁移到单独的表空间上，而是续集使用之前的共享表空间。只有新创建的表才会分离到自己的表空间文件。

（5）共享表空间的数据文件配置：innodb_data_file_path参数：设置innoDB共享表空间数据文件的名字和大小，例如innodb_data_file_path=ibdata1:12M:autoextend（初始大小12M，不足自增）innodb_data_home_dir参数：innodb引擎的共享表空间数据文件的存放目录目前主要是使用独立表空间，但是共享表空间也是需要的，共享表空间主要存放double write、undo log等。

五、InnoDB表存储结构

表空间∶表空间可看做是InnoDB存储引擎逻辑结构的最高层。

段 ：表空间由各个段组成，常见的段有数据段、索引段、回滚段等。

区 ：由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB。页:每页16kb，且不能更改。常见的页类型有∶数据页、Undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

行 ：InnoDB存储引擎是面向行的(row-oriented)，每页最多允许存放7992行数据。

a.每页=16Kb（页类型：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页）

b.区=64个连续的页=64*16Kb=1MB

作者：#YF#

来源：http://22j.co/cPZR