MySQL体系结构与存储引擎

点击上方蓝色字体，选择“标星公众号”

优质文章，第一时间送达

  作者 |  Tse先生

来源 |  urlify.cn/q2Inyi

76套java从入门到精通实战课程分享

1、MySQL体系结构

MySQL的体系结构可以分为两层，MySQL Server层和存储引擎层。

在MySQL Server层中又包括连接层和SQL层，如图1-1所示。

应用程序通过接口( 如ODBC、JDBC)来连接MySQL。

最先连接处理的是连接层，连接层包括通信协议、线程处理、用户名密码认证三个部分。

通信协议负责检测客户端版本是否兼容MySQL服务端。

线程处理是指每一个连接请求都会分配一个对应的线程，

相当于一条SQL对应一个线程，一个线程对应一个逻辑 CPU，并会在多个逻辑CPU之间进行切换。

用户名密码认证验证创建的账号和密码，以及host主机授权是否可以连接到MySQL服务器。

SQL层包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。

　　　　　　　　　　图1-1 MySQL体系结构

权限判断可以审核用户有没有访问某个库、某个表，或者表里某行的权限。

查询缓存通过 Query Cache 进行操作，如果数据在 Query Cache 中，则直接返回结果给客户端。

查询解析器针对 SQL 语句进行解析，判断语法是否正确。预处理器对解析器无法解析的语义进行处理。

优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的 API 接口，通过存储引擎层访问数据。

存储引擎层也是 MySQL 数据库区别于其他数据库最核心的一点。 

2、query cache 详解

Query Cache在生产中建议关闭，因为它只能缓存静态数据信息，一旦数据发生变化， 经常读写，QueryCache就成了“鸡肋”。

一般像数据仓库之类的可能会考虑开启QueryCache。

这里再提及一句，MySQL 5.6之前版本的Query Cache默认是开启的，5.6 之后默认是关闭的。

如何彻底关闭Query Cache是我们需要关注的。

首先涉及query_ cache 的两个核心参数：

#查询缓存大小mysql> show variables like '%query_cache_size%';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 0       |
+------------------+---------+
1 row in set (0.00 sec)
#查询缓存类型
mysql> show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

有些人一直认为只需要把query_cache_size设置为0，就算关闭查询缓存了。

但实际上，我们最不能忽略的参数就是query_cache_type了。

要想彻底关闭Query Cache,必须一开始就把query_cache_type 关闭。

即便是启动后把query_cache_type 设置为off,也会影响数据库的TPS。

通过压力测试，我们来看两个核心参数在不同设置下TPS的对比情况。

这里介绍一款MySQL数据库的压力测试软件sysbench,用它来进行基准测试。

sysbench 是一个开源的、模块化的、跨平台的多线程性能测试工具，

可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试。

目前支持的数据库有MySQL、Oracle 和PostgreSQL。

 #sysbench安装说明：https://www.cnblogs.com/Sungeek/p/14669280.html 

2.1、第一种情况

我们来看一下在不同query_cache 参数设置下，TPS的性能对比情况。
第一种情况：

修改my.cnf参数是query_cache_type=0、 query_cache_size=0。

用sysbench构造100000数据，也就是准备阶段：

create database sbtest;
cd /mysql/app/sysbench
sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 prepare

#参数说明

oltp-tables-count：表总数，生产环境最少20张表以上

oltp-table-size：表大小，生产环境最少一千万条数据

threads：线程数

time：生产环境测试时间最少15分钟

report-interval：10秒生成一次报告

然后进入测试阶段，由于是测试环境，这里设置的线程数量不多，为10个，即num-threads=10。

测试出每秒处理的事务数为668.18

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的报告结果

2.2、第二种情况

第二种情况：是query_cache_type=1、 query_cache_size=0

测试出每秒处理的事务数为660.63

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的报告结果

2.3、第三种情况

第三种情况：把query_ cache_ type=0，是query_ cache_ size=1024000

测试出每秒处理的事务数为618.43

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的报告结果

2.4、第四种情况

第四种情况：把query_ cache_ type=1，是query_ cache_ size=1024000

测试出每秒处理的事务数为621.55

#总结：

四种情况下：TPS的值分别为668.18、660.63、 618.43、621.55

可见设置正确的query_cache的关闭方式有多么重要。

#MySQL8.0据说是废除了query_cache：https://blog.csdn.net/n88Lpo/article/details/108395640

3、MySQL存储引擎

MySQL数据库及其分支版本主要的存储引擎有InnoDB、MyISAM、Memory、blackhole、
TokuDB和MariaDB columnstore。

用得最多应该就是InnoDB、MyISAM、Memory

#主要的存储引擎的特性对比：

InnoDB和MyISAM是最主流的两个存储引擎，现在数据库版本默认的存储引擎是InnoDB，
并且MySQL 8.0宣布InnoDB存储数据字典，MyISAM彻底从MySQL数据库中剥离开，被废弃了。
但等用上新版本的时候，彻底上线前，还是有不少互联网公司依然在使用MyISAM存储引擎。
这里建议大家把线上MyISAM的存储引擎表全部转化成InnoDB表存储。

#innodb和myisam两者之间的主要区别：

可以看出InnoDB存储引擎的优势很明显。

MySQL被Oracle收购之后，也针对存储引擎层做了相应的改进与优化，Server层没有太大的变动，

主要优化的核心就是InnoDB存储引擎，所以我们今后的重心就放在InnoDB上面,研究它的体系结构。

#MySQL InnoDB存储引擎体系架构图

粉丝福利：Java从入门到入土学习路线图

👇👇👇

👆长按上方微信二维码 2 秒

感谢点赞支持下哈