MySQL体系结构与存储引擎

DotNet开发跳槽

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两者之间的主要区别：