MySQL架构组成、逻辑模块组成

一、mysql server 系统架构

逻辑模块组成：

Mysql逻辑结构可以看成是二层架构，第一层通常叫做SQL Layer，在mysql数据库系统处理底层数据之前的所有工作都在这一层完成的，包括权限判断，sql解析，执行计划优化，query cache的处理等等。第二层是存储引擎层，通常叫做StorageEngine Layer，是底层数据存取操作实现部分，由多种存储引擎共同组成。

如下图：一张简单的Mysql架构示意图，MySQL的基本架构：

二、mysql存储引擎

从mysql5.1开始，MYSQL AB对其结构体系做了较大的改造，并引入了新的概念：插件式存储引擎体系结构。也就是完全可以将一个新的存储引擎加载到一个正在运行的mysql中，而不影响mysql的正常运行。

插件式存储引擎主要包括MyISAM，Innodb，NDB，Cluster，Maria，Falcon，Memory，Archive等，其中使用最广泛的是MyISAM和Innodb两种存储引擎。

1、MyISAM存储引擎简介：

（1）mysql5.1之前的默认存储引擎。

（2）MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。（存放表结构定义信息的.frm文件；存放表的数据的.MYD文件；存放索引数据的.MYI文件）

（3）MyISAM支持三种类型的索引：

B-Tree索引：所有的索引节点都按照balance tree的数据结构来存储，所有的索引数据节点都在叶节点。

R-Tree索引：用于为存储空间和多维数据的字段做索引。

Full-text索引：就是全文索引，它的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。

注：以上三种索引类型中，最经常用到的就是B-Tree索引，B-Tree索引有一个较大的限制，就是参与一个索引的所有字段的长度之和不能超过1000字节。

（4）不支持事务

（5）只有表锁

（6）造成表损坏的因素：

Mysqld正在写入该表时，被kill掉

主机宕机

硬盘硬件故障

MyISAM存储引擎的bug

（7）虽然MyISAM的表的数据都存放在.MYD文件中，但是每个文件的存放格式可能并不一样，因为MyISAM的数据存放格式分为静态（FIXED）固定长度、动态（DYNAMIC）可变长度以及压缩（COMPRESSED）这三种格式。

当然三种格式中是否压缩完全由自己选择，可以在创建表时通ROW_FORMAT来指定，也可以通过myisampack工具来进行压缩，默认是不压缩的。在非压缩情况下，是静态还是动态，就和我们表中字段的定义相关了。只要表中有可变长度类型的字段存在，那么该表就肯定是DYNAMIC格式的，如果没有任何可变长度的字段，则为FIXED格式，当然，也可以通过alter table命令，强行将一个带有VARCHAR类型的字段的DYNAMIC的表转换为FIXED，但是原VARCHAR字段类型会被自动转换成CHAR类型。相反如果将FIXED转换为DYNAMIC，也会将CHAR类型字段转换为VARCHAR类型。

注：如何根据表的记录数量估算占用的磁盘空间

首先先算一个表中一行有多少字节。

然后根据数据库中的表每天增加多少行记录，就能算出每天要增加多少硬盘空间，这样就可根据数据量估算规划多大的空间。

例如在数据库test中创建一张tb1表：

查看tb1的表结构：

tb1表的一个行有168个字节

如果每天增加10000条记录，大约需要10000*168/1024/1024=1.6MB

这样就可以根据每天增加的记录数，合理规划好磁盘空间了。

MyISAM存储引擎的某个表文件出错之后，仅影响到该表，而不会影响到其它表，更不会影响到其他数据库。

三、Innodb存储引擎

Innodb的特点：

1、支持事务

2、锁定机制的改进，实现了行锁

3、实现外键

4、Innodb存储虽然也有.frm文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。

5、Innodb的物理结构分为两大部分：

（1）数据文件（表数据和索引数据）

存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。在Innodb中，存在了表空间这样的概念。Innodb的表空间分为两种形式。一种是共享表空间，也就是所有表和索引数据被存放在同一个表空间中，通过innodb_data_file_path来指定，增加数据文件需要停机重启。另外一种是独享表空间，也是每个表的数据和索引被存放在一个单独的.ibd文件中。

注：共享表空间是必须存在的，因为Innodb的undo信息和其他一些元数据信息都是存放在共享表空间里面的。共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的。

（2）日志文件

Innodb的日志文件和Oracle的redo日志比较类似，同样可以设置多个日志组（最少2个），同样采用轮循策略来顺序的写入。

由于Innodb是事物的存储引擎，有redo日志（事务日志）的存在，有checkpoint机制的保护，Innodb完全可以通过redo日志将数据库Crash时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。

四、MyISAM和Innodb的区别

1、MyISAM不支持事务，而Innodb支持。Innodb的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条sql语句显示存放在begin和commit之间，组成一个事务去提交。

2、Innodb支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。

3、Innodb支持外键，MyISAM不支持

4、Innodb不支持全文索引，而MyISAM不支持。

五、Mysql自带工具的使用

1、mysql命令

mysql命令是用的最多的一个命令工具，为用户提供一个命令行接口来操作管理mysql服务器。

语法格式：

Usage：mysql [OPTIONS] [database]

例1：

通过binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够。

例2：

通过脚本创建数据库、表及对表进行增、删、改、查操作。

脚本内容如下：

创建test用户可以在指定的源登录

测试test用户可以连接mysql数据库

授予脚本执行权限并执行脚本：

#chmod +x /root/mysql1.sh

1）如果在连接时使用“-E，--vertical”参数，登录后的所有查询结果将以纵列显示。效果和query后加”\G”一样。

2）“-H，--html”与“-x，--xml”，在启用这两个参数后，select出来的结构都会按照“html”与“xml”格式来输出，在有些场合下，需要导出报表文件的时候是非常方便的。

3）“--prompt=name”参数提供了自定义提示信息的办法，可以通过配置显示登入的主机地址，登录用户名，当前时间，当前数据库schema，mysql server的一些信息等等。

提示符解释：

\u表示用户名，\h表示主机名，\d表示当前数据库，\r小时（12小时制），\m分钟，、\s秒，

4）”--tee=name”将所用输入和输出内容都记录进文件。

Mysql其他参数选项可以通过mysql官方参考手册查阅，也可以通过执行“mysql --help”或man mysql得到帮助信息。

2、mysqladmin

Usage：mysqladmin [OPTIONS] command command ...

Mysqladmin提供的功能都是与mysql管理相关的各种功能。如MySQL server状态检查，各种统计信息的flush，创建/删除数据库，关闭mysqlserver等等。

（1）ping命令可以很容易检测mysql server是否还能正常提供服务。

注：地址192.168.56.11是mysql server的ip。MySQL server的防火墙要允许3306/tcp通信。在mysql server上创建授权用户：

#mysql -u root -p -e “grant all on *.* to ‘test’@’192.168.56.%’ identified by ‘123456’”

（2）status命令可以获取当前mysql server的几个基本的状态值：

（3）processlist获取当前数据库的连接线程信息：

简单的mysql监控脚本：

拓展知识：

六、mysqlslap性能测试mysql两种存储引擎

mysqlslap是mysql自带的基准测试工具，优点：查询数据，语法简单，灵活容易使用。该工具可以模拟多个客户端同时并发的向服务器发出查询更新，新出了性能测试数据而且提供了多种引擎的性能比较。

Mysqlslap的常用选项：

查看mysql数据库默认最大连接数：

Mysql5.7.13默认是151，在my.cnf[mysqld]下添加max_connections=1024，并重启mysqld

查看mysql默认使用的存储引擎。

用自带的sql脚本测试：

Mysqlslap测试工具生产CSV格式数据文件并转换成图标形式。

将a.csv拷贝到windows主机上，打开并生成图表，如下图：

用自定义的sql脚本测试：

脚本内容如下：

注：通过mysqlslap工具对mysql server进行压力测试，可以通过--concurrency，--number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。

如果mysqlslap工具输出结果为Segmentation fault(core dumped)基本表示超出mysql server的负载。