MySQL详解（一）：Mysql架构介绍

骑猪看日落 2022-05-18 05:27 358阅读 0赞

# **1.MySQL架构** #

MySQL服务器是由SQL层和存储引擎层构成。

SQL层主要功能包括权限判断、SQL解析功能和查询缓存处理等；

存储引擎层（Storage Engine Layer）：完成底层数据库数据存储操作。

## **1.1 MySQL各个模块特点：** ##

（1）客户端连接通过**连接****/****线程**处理层来连接MySQL数据库，**连接****/****线程**处理主要是用来处理客户端的请求、身份验证和数据库的安全验证等；

（2）**查询缓存**和**查询分析器**是SQL层的核心部分，其中主要涉及**查询**的解析、优化、缓存，以及所有内置的函数、存储过程、触发器，视图等功能；

（3）**优化器**主要负责存储和获取所有存储在MySQL中的数据；

以上三层统称为MySQL数据库的SQL层；

## **1.2 MySQL物理文件的组成** ##

参考博客：[http://blog.csdn.net/zhengwei125/article/details/52596208][http_blog.csdn.net_zhengwei125_article_details_52596208]

### **1.日志文件** ###

（1）错误日志：Error Log

记录了mysql运行过程中较为严重的错误信息，以及启动和关闭的信息。

默认命名hostname.err

可以在参数文件中加上 \--log-error\[=file\_name\]修改目录名和文件名

flush logs 命令： 备份旧日志文件并生成新的日志文件。备份文件名以'.old'结尾

（2）二进制日志：Binary Log & Binary Log Index

二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源；

binlog,在参数文件中添加"--bin-log\[=file\_name\]"打开binlog

binlog:记录的是操作mysql的所有修改数据库的操作。

binlog附加选项参数

--max\_binlog\_size : binlog的日志文件大小，一个日志满了后会重新写入一个文件。

--binlog-do-db=db\_name : 对某个db\_name记录bin-log日志

--binlog-ignore-db=dbname : 与\--binlog-do-db=db\_name 完全相反，忽略db\_name的binlog日志

mysql-bin.index 文件(binary log index)记录所有的Binary Log 的绝对路径

（3）查询日志：Query Log

默认的查询日志文件是hostname.log。查询日志记录所有的查询操作，包括所有的select信息，体积比较庞大，开启之后对性能有较大的影响，可以通过“--log\[=file\_name\]”选项开启。如果需要跟踪某些特殊的SQL性能问题，可以短暂的打开该功能；

（4）慢查询日志：Slow Query Log

慢查询日志是指所有SQL执行的时间超过long\_query\_time变量的语句和达到min\_examined\_row\_limit条距离的语句。用户可以针对这部分语句性能调优。慢查询日志通过设置--log-show\_queries\[=filename\]选项开启后，将记录日志所在的路径和名称。

MySQL系统默认的慢查询日志的文件名是hostname-slow.log，默认目录也是data目录。慢查询日志可以采用mysqldumpslow命令对慢查询日志进行分析；

慢查询格式为文本，记录了执行时间，消化时间，执行用户，链接主机信息等

（5）InnoDB引擎在线Redo日志：InnoDB redo Log

InnoDB引擎在线Redo日志记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大的加强了事务的安全性；

InnoDB在线Redo日志默认存放在data目录下面，可以通过设置innodb\_log\_group\_home\_dir选项来更改日志的存放位置，通过innodb\_log\_files\_in\_group选项来设置日志的数量；

### **2.数据文件** ###

数据库会在data目录下面建立一个以数据库为名字的文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样，例如：MyISAM引擎使用“.MYD”作为拓展名，InnoDB引擎使用“.ibd”作为扩展名。CVS引擎使用“.csv”扩展名

**（1****）.frm****文件**

**不论是什么存储引擎，每个表都会有一个以表名命名的.frm****文件。**所有的.frm文件都存放在所属数据库的文件夹下面。该文件主要存储与表相关的数据信息，主要包括**表结构的定义信息**。当数据库崩溃的时候，用户可以通过该文件来恢复数据表结构；

**（2****）.MYD****文件**

.myd文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个.MYD文件与之对应，同样存放于所属数据库的文件夹下，和".frm"文件在一起；

**（3****）.MYI****文件**

.myi文件也是专属于MyISAM存储引擎的，主要存放MyISAM表索引相关信息。对于MyISAM存储来说，可以被cache的内容主要来源于.myi文件中。每一个MyISAM表对应一个.MYI文件.

.MYI文件中主要用来存储表数据文件中任何索引的数据树；

**（4****）.ibd****文件和.ibdata****文件**

这两种文件都是存放innodb的数据文件，之所以有两种文件存放innodb的数据(其中主要包括索引信息)，是因为innodb的数据存储方式能通过配置来决定是使用共享表空间存放数据，还是独享表空间存储数据。

独享表空间存储使用.idb文件存储。且每个表一个单独.idb文件；如果用共享表空间的方式存放数据，则会使用ibdata文件存放，所有表共同使用一个或者多个ibdata文件。

**ibdata**文件可通过 innodb\_data\_home\_dir 和 innodb\_data\_file\_path 两个参数共同配置组成；

innodb\_data\_home\_dir：**配置数据文件存放的总目录；**

innodb\_data\_file\_path ：**配置每一个文件的路径以及文件名称；**

如果需要添加新的ibdata文件，则需要在innodb\_data\_file\_path参数后面配置，然后重新启动服务器才能够生效；

也可以不用 innodb\_data\_home\_dir 直接在innodb\_data\_file\_path 中使用绝对路径innodb\_data\_file\_path可以一次配置多个ibdata文件。可以指定大小，自动扩展，但是innodb限制了仅仅只有最后一个ibdata文件能够配置成自动扩展类型。当我们需要添加新的ibdata文件的时候只能添加在 innodb\_data\_file\_path 配置的最后，而且必须重启才能完成ibdata的添加工作。不过我们使用独享表空间存储方式的话，就不会有这样的问题，但是用裸设备，每个表一个裸设备，可能造成裸设备数量非常大，而且不容易控制大小，实现比较困难。

### **3.其他文件** ###

1.  系统配置文件：

MySQL系统配置文件Linux下：my.cnf，window下为my.ini

1.  pid文件：

pid file 是mysqld应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，

1.  存放自己的进程id.socket文件：

socket file文件也是在Unix/Linux 环境下才有的，用户在Unix/Linux 环境下客户端链接可以不通过tcp/ip 网络而直接用unix socket 来链接Mysql

MySQL服务器启动之后，socket文件自动生成，该文件主要用来连接客户端；

# **2.MySQL各逻辑块简介** #

MySQL逻辑架构采用SQL层和存储引擎层分离的方式，真正的实现了数据存储和逻辑业务的分离，MySQL的SQL层从宏观上可以分为三层，事实上SQL层包含了很多的子模块：

## **2.1 SQL层的子模块** ##

*  **初始化模块**

初始化模块就是在数据库启动的时候，对整个数据库做的一些初始化操作，例如，各种系统环境变量的初始化，各种缓存、存储引擎初始化设置等；

MySQL系统参数可以通过mysqld--verbose -help命令来查看当前系统所有参数的设置。

*  **核心API**

MySQL数据库核心API主要实现了数据库底层操作的优化功能，其中主要包括io操作、格式化输出、高性能存储数据结果算法的优化，字符串的处理，其中最重要的是内存管理；

*  **网络交互模块**

MySQL底层相互交互的模块抽象出接口，对外提供可以接收和发送数据的API接口，其他模块需要交互的时候，可以通过API接口调用；

*  **服务器客户端交互协议模块**

MySQL服务器采用C/S的形式访问数据库，数据连接使用MySQL C/S交互协议模块，实现了客户端和服务器端交互过程中所需要的一些独特的协议，这些协议都是建立在现有的网络协议之上；

*  **用户模块**

用户模块的主要功能是用于控制用户登录连接的权限和用户的授权管理；

*  **访问控制模块**

访问控制模块主要用于监控用户的每一个操作。访问控制模块实现的功能就是根据用户模块中不同的用户授权，以及根据其数据库的各种约束来控制用户对数据的访问。用户模块和访问控制模块结合起来，就组成了MySQL数据库的**权限管理功能**；

*  **连接管理、连接线程和线程管理**

连接管理模块负责监听MySQL Server的各种请求，根据不同的请求，然后转发到线程管理模块，每个客户请求都会被数据库自动分配一个独立的线程为其单独服务，而连接线程的主要工作就是负责MySQL Server与客户端通信，线程管理模块负责管理这些生成的线程；

*  **转发模块**

客户端连接MySQL之后会发送一些查询语句，在MySQL Server里面，连接线程接收到客户端的一个请求之后，会直接将查询转发到各个对应的处理模块。转发模块主要就是根据查询语句语法分析，然后转发给不同的模块处理；

*  **缓存模块**

查询缓存模块的主要功能是将客户端的请求返回的结果集到缓存中，与查询的一个HASH值对应。在查询的基表发生任何数据变化之后，MySQL会自动的将其查询的缓存失效；在读写比例非常高的应用系统中，查询缓存对性能的提高是非常显著的；

*  **优化器模块**

这个模块主要是将客户端发送的查询请求，在之前算法的基础上分析，计算出一个最优的查询策略，优化之后会提高查询访问的速度，最后根据其最优策略返回查询语句；

*  **表变更管理模块**

表变更管理模块主要负责完成DML和DDL查询，例如，insert、update、delete、create table、alter table 等语句的处理

*  **表维护模块**

表维护模块主要用于检测表的状态，分析，优化表结构，以及修复表。

*  **系统状态管理模块**

在客户端请求系统状态的时候，系统状态模块主要负责将各种状态的数据返回给用户。最常用的一些查询状态命令包括show status，show variables等，都是通过这个模块负责返回的；

*  **表管理器**

表管理器主要就是维护系统生成的表文件。例如，MYISAM存储引擎类型表生成的是frm文件、MYD文件以及MYI文件，表管理器的工作就是维护这些文件，将各个表结构的信息缓存起来，另外该模块还管理表级别的锁；

*  **日志记录模块**

日志记录模块主要负责整个数据库逻辑层的日志文件，其中包含错误日志，二进制日志，以及慢查询日志等；

*  **复制模块**

复制模块分为Master模块和Slave模块两部分。Master模块主要负责复制环境中读取Master端的binary日志，以及Slave端的I/O线程交互等工作。Slave模块主要有两个线程，一个负责从Master请求和接收binary日志，并写入本地I/O线程；另一个从relay log 读取日志事件，然后解析成可以在Slave端执行的命令，然后交给Slave端的SQL线程；

*  **存储引擎接口模块**

MySQL实现了其数据库底层存储引擎的插件式管理，将各种数据处理高度抽象化；

# **3.MySQL各逻辑块协调工作** #

MySQL各逻辑模块之间的协调工作；

[http://www.cnblogs.com/fhhk/p/5870533.html][http_www.cnblogs.com_fhhk_p_5870533.html]

DML、DDL、DCL的区别；

[http://blog.csdn.net/level\_level/article/details/4248685][http_blog.csdn.net_level_level_article_details_4248685]

数据库语言的分类：

[http://blog.csdn.net/tomatofly/article/details/5949070][http_blog.csdn.net_tomatofly_article_details_5949070]

MySQL启动之后，MySQL的**初始化模块**就从系统配置文件中读取系统参数和命令参数，并按照参数初始化整个系统，同时存储引擎也会启动，当初始化工作结束之后，**连接管理模块**会监听并接收客户端的程序，连接管理模块会将连接请求转发给**线程管理模块**去请求一个连接线程；

**线程模块**接收到请求之后会调用用户模块进行授权检查，通过检查之后，会检测线程池中是否有空间连接线程。如果有，就取出跟客户端连接上；如果没有，则建立一个新的线程与客户端建立连接；

MySQL数据库中的请求有两种，一种是需要命令解析和分发模块解析才能执行请求的操作，另一种是不需要转发就可以直接执行的命令。此时，如果初始阶段开启日志功能，这时候日志模块将请求记录日志，不管是哪种请求，都会记录到日志；

Query类型的请求，会将控制权交给Query解析器，Query解析器会检查是否是select类型的查询；如果是，则启动查询缓存模块，此时会查询缓存中已经存在的结果集。如果存在，则将缓存中的数据返回给连接线程模块，之后连接线程会将数据传递到客户端；如果没有缓存，或者不是一个可以缓存的查询，此时查询解析器进行相应的处理，通过查询分发器给相关的处理模块；

如果**解析器**是DML/DDL，则交给变更管理模块；如果是一些检查，修复类的查询，则交给表维护模块去处理；如果是一条没有被缓存的查询语句，则交给查询优化器模块。实际上表变更管理器又分为小的模块，例如：inser处理器、delete处理器、update处理器、create处理器，以及alter处理器这些小模块来负责不同的DML和DDL。总之，查询优化器，表变更模块、表维护模块、复制模块、状态模块都是根据命令解析器的结果不同而分发给不同类型的模块。

当一条命令执行完成之后，控制权都会还给**连接线程模块**，在上面各个模块处理过程中，各个模块都依赖于整个MySQL的**核心****API****模块**，比如内存管理，文件I/O，字符串处理等；

# **4.MySQL存储引擎的概述** #

MySQL采用插件的方式，将存储引擎直接加载到正在运行的MySQL中，这还少MySQL数据库的一个重要的特性；

【MySQL常用存储引擎的对比情况】

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#5b9bd5;border-color:#cce8cf;width:74.1pt;"> 特点 </td> 
 <td style="background-color:#5b9bd5;width:57.6pt;"> InnoDB </td> 
 <td style="background-color:#5b9bd5;width:65.95pt;"> MyISAM </td> 
 <td style="background-color:#5b9bd5;width:65.95pt;"> MEMORY </td> 
 <td style="background-color:#5b9bd5;width:65.95pt;"> MERGE </td> 
 <td style="background-color:#5b9bd5;width:65.95pt;"> NDB </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 存储限制 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 64TB </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 有 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 有 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 没有 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 有 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 事务安全 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 锁机制 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 行锁 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 表锁 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 表锁 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 表锁 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 行锁 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> B树索引 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 哈希索引 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 全文索引 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 集群索引 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#adccea;width:65.95pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 数据索引 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 索引缓存 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 数据可压缩 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 空间使用 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 高 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 低 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> N/A </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 低 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 低 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 内存使用 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> 高 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 低 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 中等 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 低 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> 高 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;width:74.1pt;"> 批量插入速度 </td> 
 <td style="background-color:#adccea;width:57.6pt;"> 低 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 高 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 高 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 高 </td> 
 <td style="background-color:#adccea;width:65.95pt;"> 高 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#5b9bd5;border-color:#cce8cf;width:74.1pt;"> 支持外键 </td> 
 <td style="background-color:#d6e6f4;width:57.6pt;"> 支持 </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 <td style="background-color:#d6e6f4;width:65.95pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

## **4.1 MySQL各种存储引擎的特性：** ##

数据存储不管使用哪种存储引擎，所有的存储数据都被记录到.frm文件中，该文件记录了存储的数据，以及表的一些属性值。值得注意的是，不管是使用哪种数据存储引擎都使用了高速缓存，数据库读取.frm文件信息之后会将表的信息缓存起来，提高了服务器下次读取数据的速度；

### **【MyISAM存储引擎】** ###

**1****、MyISAM****文件格式**

MyISAM在磁盘上存储了三个文件，文件名和对应的表明是一致的；

frm文件：存储表的定义数据；

MYD文件：存放表具体的记录数据；

MYI文件：存储索引；

**2****、MyISAM****适用场景：**

应用场景：往往对于不需要完整的事务，主要以查询和增加记录为主的应用采用MyISAM存储引擎。MyISAM存储引擎只缓存索引，对数据文件采用操作系统缓存，如果索引数据超过系统所分配的缓存空间的时候，也会采取操作系统来缓存索引；

**3****、MyISAM****文件修复：**

可以使用CHECK TABLE 语句检测MyISAM表的状态，然后使用REPAIR TABLE语句修复损坏的表；

可以使用myisamchk工具修复损坏的表。使用myisamchk可以检测MyISAM类型表的健康状态和修复甚至优化表的存储。

格式：

myisamchk \[options\] tables\[.MYI\]

options（参数含义）：

\-c 表示用来检测表可能存在的错误

\-d 表示打印出表的一些信息；

\-e 表示彻底的检查表；

\-i 表示打印有关被检查表的信息统计；

\-k \# 一般与-r一起使用，表示仅仅更新头\#个索引，较高编号的索引被撤销。撤销的索引会通过myisamchk -r 被重新激活；

\-q 与-r可以使得修复的速度更快

\-r 表示可以秀谷几乎所有的损坏的问题；

\-o 相比-r恢复表的能力慢一些，但是可以处理一些-r不能处理的问题；

\-u 解开一个用myisamchk压缩的表

\-w 表示如果表被锁定，将会发生等待；

在Linux平台下， 使用myisamchk检测某个表，如果有用户同时也在操作这张表，即便这张表是没有问题的，也很有可能提示该表已经被损坏，所以在检测数据库表的时候，应该停止mysqld服务。如果不想停掉mysqld服务，至少应该做一个mysqladmin flush-tables操作；

**4****、MyISAM****表的存储格式**

MyISAM类型的表支持三种不同存储类型格式的表，分别如下：

（1）静态（固定长度的）表

（2）动态可变长度表

（3）压缩表

其中静态表和动态表数据存储根据其表中数据列的类型自动的选择，静态表是默认的存储格式，压缩表只能通过myisampack工具创建

静态表中的字段都是固定的非变长度的字段，这样每一个记录都是固定的长度的，这种存储方式的优势在于存储的速度非常快，容易缓存，表发生损毁之后容易恢复，缺点就是所占用的空间往往要比动态的表多；

动态表可以支持动态可变长度，字符型的列长是可变的，除了小于4个字符的，动态可变长字符通常比静态固定格式需要更少的存储空间，由于采用动态可变长度存储，所以出错的时候也比静态格式恢复更加的困难，因为行变化如果很大的话，会被分为碎片，这个时候可以使用myisamchk-ei获取表的统计信息，并使用myisamchk-r来进行修复；

MyISAM存储压缩表需要的磁盘空间最小，数据库系统提供了myisampack工具可以用来压缩MyISAM表，每行单独压缩，每列的压缩也不一样；

### **【InnoDB存储引擎】** ###

InnoDB的写的处理相对于MyISAM效率低一些，InnoDB牺牲了存储和查询的效率，支持事务安全，支持自增长列，对事务安全的支持，这是InnoDB成为MySQL最为流行的存储引擎之一的重要原因；

**《支持事务》**

MySQL支持对InnoDB存储事务控制，实现了SQL92标准定义的4个级别（read uncommitted，repeatable read，read committed，serializable）

MySQL通过commit、rollback、set autocommit、start transaction等语法支持本地事务；

**《自动增长列》**

InnoDB 表的自动增长列需要在列的后面添加auto\_increment属性，表在添加数据的过程中，可以插入空值，该列可以自动增加数据；

举例：实现自动增长的列：

create table authors(

id integer primary key auto\_increment,

name varchar(10)

)engine=innodb default charset=gbk;

插入数据：

insert into authors(name) values(‘ivan’),(‘susan’),(‘shark’);

**《外键约束》**

InnoDB实现了外键这一数据库重要功能，从数据库性能上来讲，数据库外键降低了数据库查询的效率，数据表之间的耦合更加的紧密，但是对于不少用户来讲，采用外键约束可能是最低成本的选择方式。MySQL支持外键的存储引擎只有InnoDB。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候会添加对应的索引；

创建sclass主表，id作为主表主键索引，st表示子表，其中class\_id作为外键对应sclass表的id的值；

DROP TABLE IF EXISTS \`sclass\`;

CREATE TABLE \`sclass\` (

\`id\` int(11) NOT NULL AUTO\_INCREMENT,

\`cname\` varchar(20) NOT NULL,

\`last\_update\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,

PRIMARY KEY (\`id\`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

创建数据表st，并添加外键约束，

DROP TABLE IF EXISTS \`st\`;

CREATE TABLE \`st\` (

\`id\` int(11) NOT NULL AUTO\_INCREMENT,

\`sname\` varchar(20) NOT NULL,

\`class\_id\` int(11) NOT NULL,

\`last\_updatetime\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,

PRIMARY KEY (\`id\`),

KEY \`class\_id\` (\`class\_id\`),

CONSTRAINT \`st\_ibfk\_1\` FOREIGN KEY (\`class\_id\`) REFERENCES \`sclass\` (\`id\`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

对于上面创建的两个表，在删除操作的时候，如果是删除主表的数据，子表对应的记录不会被删除；如果是更新主表，子表对应的记录会更新；

在物理存储方面，InnoDB有自己独特的存储方式，数据也是存放在.frm文件里面，但是表数据和索引数据时存放在一起的。InnoDB的存储表和索引有以下两种方式：

（1）使用共享表空间存储，也就是所有表和索引数据存放在同一个表空间中，数据和索引在innodb\_data\_home\_dir和innodb\_data\_file\_path定义的表空间中，可以使用一个或者多个数据文件。

（2）使用多表存储空间，这种存储方式创建的表结构存放在.frm文件中，但是每个表的数据和索引被存放在一个单独的.idb文件中。如果是分区表，则每个分区对应单独的.idb文件，文件名称是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的路径，这样的好处在于可以将表的读取操作平均分布到若干个磁盘分区文件上，提高数据访问的效率。

如果要使用多表空间存储方式，需要设置innodb\_file\_pre\_table参数。该参数可以修改InnoDB为独立表空间模式，每个数据库的表都会生成一个数据空间。

可以使用如下方式查看多表存储空间模式是否已经开启：

mysql>show variables like ‘%per\_table%’;

在设置innodb\_file\_pre\_table参数之前，需要先关闭数据库，然后在my.cnf文件中设置或者添加该参数innodb\_file\_pre\_table=1，并重启数据库才能生效。

使用多表空间特性的表，可以比较方便的进行备份和恢复操作，但是直接复制.idb文件是不行的，可以通过如下的命令：

ALTER TABLE table\_name DISCARD TABLESPACE;

ALTER TABLE table\_name IMPORT TABLESPACE;

InnoDB在功能跟MyISAM存储引擎有很大的不同，在参数配置上，InnoDB也是单独处理的，InnoDB所有的参数基本上都是加了前缀“innodb\_”。

如果想屏蔽InnoDB存储引擎，在my.ini配置文件中，将skip\_innodb参数前的\#去除，这样就无法创建InnoDB类型的表了；

### **【MEMORY存储引擎】** ###

MEMORY 存储引擎通过采用内存中的内容来创建表。每个MEMORY表实际上和一个磁盘文件关联起来。文件名采用“表名.frm”的格式。MEMORY类型的表访问速度非常快，因为数据来源于内存空间。MEMORY存储引擎默认使用HASH索引，虽然MEMORY类型的表访问速度非常快，但是一旦数据库发生故障关闭，内存中的数据就会发生丢失；

MEMORY表的空间以小块来分配。创建MEMORY表的时候，可以通过添加一个索引指定是HASH索引或者BTREE索引；

指定HASH索引：index using hash(id)

删除HASH索引：drop index id on table\_name

修改索引为btree索引

create index id\_index using btree on table\_name(id)

查看索引命令：

show index from table\_name \\g

MEMEORY 表内容存储在内存中，如果一个内部表变得很大，服务器自动的把它转换成为一个磁盘表。尺寸限制由temp\_table\_size系统变量的值来确定。每个MEMORY表容量的大小可以通过设置max\_heap\_table\_size变量的值来控制。max\_heap\_table\_size系统变量默认的值为16MB也可以在CREATE TABLE语句中指定一个MAX\_ROWS表选项，参数MAX\_ROWS指定表的最大行数。

create table tabel\_memory(

id int primary key

)engine=memory max\_rows=10000;

在MySQL服务器启动的时候可以使用init-file选项，可以把INSERT INTO … SELECT或者LOAD DATA INFILE 这样的语句放入到这个文件中，从而从数据源中状态数据表。

MEMORY类型的存储引擎主要用于内容变化不频繁的代码表，对MEMORY存储引擎的表更新操作数据不会写入到磁盘中。

### **【MERGE存储引擎】** ###

MERGE 存储引擎是一组MyISAM表的组合，将一组结构相同的MyISAM表组合成一个逻辑单元，通常也叫做MRG\_MYISAM存储引擎。MERGE表本身没有数据，对于MERGE类型表的插入操作，是通过INSERT\_METHOD子句完成，可以使用FIRST或者LAST值，可以使其数据增加到第一个表，或者最后一个表上。其实上述操作实际上是对内部MyISAM表进行操作，所以在创建MERGE表的时候，MySQL只会生成两个较小的文件，一个是.frm的文件，用于存放数据，还有一个.MRG文件，用于存放MERGE表的名称，包括MERGE表由那些表组成；

### **【BerkeleyDB存储引擎】** ###

BerkeleyDB存储引擎不是MySQL开发的存储引擎，是由Sleepcat Software开发的事务性存储引擎，简称为BDB。

创建BDB表会生成两个数据文件，文件的名字用表名来开头，扩展名表示其不同的文件类型，一个.frm文件存储表元数据，另一个.db文件包含数据和索引内容。另外，BDB为了实现事务安全也有自己的redo日志，可以通过参数设置日志存放的位置，在锁定机制方面，BDB和MEMORY存储引擎一样，实现页级锁定；

创建一个BDB表，可以使用ENGINE或者TYPE表选项来创建；

由于BDB存储引擎实现了事务安全，BDB在每次启动的时候，都会做一次检查操作，并将所有的redo日志清空；

# **5. MySQL工具** #

MySQL数据库管理系统提供了许多命令行工具，这些工具可以用来管理MySQL服务器、对数据库进行访问控制、管理MySQL用户以及数据库备份和恢复工具等。而且MySQL提供图形化的管理工具，这使得对数据库的操作变得更加的简单。

## **1.MySQL服务端使用工具程序如下：** ##

（1）mysqld：

SQL后台程序（即MySQL服务器进程）。该程序必须运行之后，客户端才能通过链接服务器来访问数据库；

（2）mysqld\_safe：

服务器启动脚本。在Unix和NetWare中推荐使用mysqld\_safe来启动mysqld服务器。mysqld\_safe增加了一些安全特性，例如当出现错误的时候重启服务器并向错误日志文件写入运行时间信息；

（3）mysql.server：

服务器启动脚本。该脚本用于使用包含为特定级别的、运行启动服务脚本的。运行目录的系统。它调用mysqld\_safe来启动MySQL服务器；

（4）mysqld\_multi：

服务器启动脚本，可以启动或者停止系统安装的多个服务器。

（5）myisamchk：

用来描述、检查、优化和维护MyISAM表的实用工具；

（7）mysqlbug：

MySQL缺陷报告脚本，它可以用来向MySQL邮件系统发送缺陷报告。

（8）mysql\_install\_db：

该脚本用默认权限创建MySQL授权表。通常只是在系统上首次安装MySQL的时候执行一次；

## **2.MySQL客户端使用工具程序如下：** ##

（1）myisampack：

压缩MyISAM表以产生更小的只读表的一个工具；

（2）mysql：

交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具；

（3）mysqlaccess：

检查访问主机名、用户名和数据库组合的权限的脚本。

（4）mysqladmin：

执行管理操作的客户程序，例如创建或者删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件。mysqladmin还可以用来检索版本、进程，以及服务器的状态信息；

（5）mysqlbinlog：

从二进制日志读取语句的工具。在二进制日志文件中包含执行过的语句，可用来帮助系统从崩溃中恢复；

（6）mysqlcheck：

检查、修复、分析以及优化表的表维护客户程序；

（7）mysqldump：

将MySQL数据库转储到一个文件（例如SQL语句或者tab分割的文本文件的客户程序）

（8）mysqlhotcopy：

当服务器正在运行的时候，快速备份MyISAM或ISAM表的工具；

（9）mysql import：

使用LOAD DATA INFILE将文本文件导入相关表的客户程序；

（10）mysqlshow：

显示数据库、表、列以及索引相关信息的客户程序；

（11）perror：

显示系统或者MySQL错误代码含义的工具；

## **【MySQL客户端连接工具——mysql】** ##

在数据库管理工具中，经常使用MySQL客户端连接工具，语法如下

mysql \[options\] \[database\]

连接选项的参数如下：

\-user：简称为-u，指定数据库用户名

\-password：简称为-p，指定登陆密码

\-host：简称-h，指定远程MySQL服务器的ip地址

\-port：简称为-p，指定连接的端口

查看当前连接的用户：

select current\_user();

MySQL的字符集选项可以在/etc/my.cnf中配置，也可以在MySQL命令中手工指定客户端字符集，操作命令如下：

mysql –u root –p --default-character-set=gbk

## **【MyISAM表压缩工具——myisampack】** ##

使用myisampack工具可以压缩MyISAM表，压缩之后的表将占用更少的磁盘存储空间，三是压缩之后的表是一个只读表，通常情况下，myisampack可以将数据文件压缩到40%——70%左右，当以后使用表的时候，解压缩之后的信息会读入到内存之中；

使用myisampack的语法如下：

myisampack \[options\] filename …

## **【MySQL管理工具——mysqladmin】** ##

mysqladmin是一个执行管理操作的客户程序。可以用它来检查服务器的配置和当前的状态，创建并删除数据库等等；

使用mysqladmin -u root -p shutdown 关闭数据库

使用mysqladmin创建数据库

mysqladmin -u root -p create table\_name

## **【MySQL日志管理工具——mysqlbinlog】** ##

MySQL服务器生成的日志是以二进制文件格式存储在磁盘空间傻瓜，mysqlbinlog工具的主要作用就是用来查看服务器上的日志文件的，mysqlbinlog的使用格式如下：

Shell> mysqlbinlog \[options\]  log-files

步骤：

查看日志信息：

mysql>system ls -ltr mysql-bin\*

执行reset master命令删除日志文件：

mysql> reset master;

往测试表中插入一条数据，再删除表中所有的数据；

mysql>insert into emp values(4,’kk’);

mysql>delete from emp;

使用mysqlbinlog工具查询日志文件：

mysqlbinlog mysql-bin.000001

**mysqlbinlog** **日志管理工具的参数选项的用法：**

（1）-d参数：

日志文件只列出指定数据库的相关操作。

（2）--start-datetime参数：指定从某个日期开始的相关数据操作。

（3）--stop-datetime参数：指定从某个日期结束的相关数据操作；

## **【MyISAM表维护工具——mysqlcheck】** ##

mysqlcheck的功能类似myisamchk，但其工作时间不同。主要的差别是当mysql服务器在运行的时候必须使用mysqlcheck，而myisamchk应用于服务器没有运行时。使用mysqlcheck的好处是不需要停止服务器来检查或者修复表，另外使用myisamchk修复失败时是不可逆的。

有三种方式可以调用mysqlcheck，命令如下：

mysqlcheck\[options\] db\_name \[tables\]

mysqlcheck\[options\] -- database DB1 \[DB2 DB3…\]

mysqlcheck\[options\] --all--database

options参数最常用的有以下几个选项：

\-all-databases：

简称为-A，用于检测所有的数据库。

\-analyze：

简称为-a,用于分析指定的数据库。

\-auto-repair：

如果检测的数据表发生损坏，将会在所有表检测完之后自动修复它；

\-check：

简称-c,该参数检测表的错误。

\-repair：

简称-r，用来修复任何错误，除非唯一键不是唯一的；

\-optimize：

简称-o，用于优化指定的数据库；

# **推荐工具MySQL Workbench** #

MySQL Workbench是下一代可视化数据库设计软件，MySQL Workbench为数据库管理员和开发人员提供了一整套可视化数据库操作环境，主要功能有：

1.  数据库设计和模型建立；
2.  SQL开发（取代MySQL Query Browser）
3.  数据库管理（取代MySQL Administrator）

随书源码下载地址：[https://pan.baidu.com/s/1ntu60IH][https_pan.baidu.com_s_1ntu60IH]

[http_blog.csdn.net_zhengwei125_article_details_52596208]: http://blog.csdn.net/zhengwei125/article/details/52596208
[http_www.cnblogs.com_fhhk_p_5870533.html]: http://www.cnblogs.com/fhhk/p/5870533.html
[http_blog.csdn.net_level_level_article_details_4248685]: http://blog.csdn.net/level_level/article/details/4248685
[http_blog.csdn.net_tomatofly_article_details_5949070]: http://blog.csdn.net/tomatofly/article/details/5949070
[https_pan.baidu.com_s_1ntu60IH]: https://pan.baidu.com/s/1ntu60IH