MySQL优化实战学习笔记（一）

1 Mysql与系统的交互

1.1 Mysql驱动

Mysql驱动的功能：在底层建立网络连接。才能够帮助我们发送请求给数据库服务器。

1.2 数据库连接池

当我们多线程的处理请求的时候，显然不可能只用一个数据库连接，然后不停的建立连接、删除连接。效率太差了。

因此需要构建一个数据库连接池，在这个连接池子中有很多个数据库连接。

当sql语句执行结束后，不要销毁这个连接，而是放回数据库连接池中，下次再用

2 Mysql的架构设计

2.1 监听和读取数据的线程

MySQL中有一个线程专门负责：从网络连接中读取和解析数据

2.2 接受SQL语句的SQL接口

MYSQL提供了一个接口：SQL Interface

这个接口专门执行接收到的SQL语句。

2.3 解析SQL语句的SQL解析器

SQL解析器就是按照SQL的语法规则进行解析，理解这一句SQL语句到底要干嘛。

2.4 选择最优查询路径的查询优化器

在理解SQL要干啥以后，查询优化器会给出一个最优的查询路径方案。

查询优化器会根据SQL语句生成查询路径树，然后从树中找到最优的查询路径。

2.5 调用存储引擎执行器

执行器会根据由查询优化器得出的执行方案，不停的调用存储引擎的各种接口去完成SQL语句的执行计划。

2.6 执行SQL语句的数据库存储引擎

存储引擎会根据SQL的查询方案，分别调用内存和磁盘，进行查询、更新等复杂操作。

MYSQL的架构设计中，SQL接口、SQL解析器、SQL查询优化器都是通用的，但是存储引擎可以有很多选择。

3 初步了解InnoDB

3.1 缓冲池：Buffer Pool

缓冲池是一个由InnoDB存放在内存中的组件，里面缓存了很多数据。

3.1.1 初步查询

假设要查询id=10

• 如果数据已经在缓冲池中，则直接加载。
• 如果数据不在缓冲池中，则从磁盘中加载到缓冲池，并且添加独占锁。

3.1.2 undo日志文件

为了满足日后回滚的需要。会把写入数据的旧值，写入undo日志文件

3.1.3 更新缓冲池

更新内存中的BufferPool的数据。

这时候的数据是脏数据，因为BufferPool中的数据已经修改，但是磁盘中的数据没有被修改。

3.1.4 Redo Log Buffer

为了防止此时宕机，导致修改丢失。必须把对于内存中BufferPool的修改内容，拷贝一份，写在redo日志中。

所谓redo日子主要记录了，你对于数据做了什么修改，类似于：“id=10这行记录修改了name字段的值为XXX”

3.1.5 写回redo日志

当想要提交事务时，mysql会根据一定策略，把redo日志文件写回磁盘。

这个策略是通过：innodb_flush_log_at_trx_commit配置

• 参数为0：提交事务时不会写回redo日志
• 参数为1：只有写回成功，才会提交事务成功。常用这个方法，最为稳妥
• 参数为2：将redo日志写回OSCache中，稍后（可能1s）写回磁盘

3.1.6 写回binlog日志

binlog日志是归档日志，是mysqlServer自己的日志文件。redo日志文件是InnoDB的日志文件。

当我们提交策略时，同时还会把binlog日志写回磁盘文件。

binlog日志文件同样存储了对于数据更新的操作。

binlog可以通过sync_binlog参数控制策略：

• 默认参数0：写入OSCache
• 参数1：写回磁盘

3.1.7 完成事物条件的条件

redo文件中会保存：binlog文件名和位置，同时在redo文件中写入一个commit标记。

redo文件中的commit标记的意义：

• 确保redo中有本次更新的日志文件
• 确保binlog中有本次更新的日志文件
• 确保redo和binlog中的关于本次更新的日志文件完全相同

3.1.8 后台IO线程随机将内存更新后的脏数据刷入磁盘

MySQL后台会有一个IO线程，在未来的某个时间里，把BufferPool中的数据刷会磁盘文件中。

4 生产环境下数据库机器配置

4.1 低并发、低流量、操作频率低、用户量小

虽然可能的业务逻辑复杂，但是并不需要关心数据库的机器配置，随便选一个。

4.2 机器配置与并发

• 4核心8G内存：每秒500并发。很迷你自己玩玩。
• 8核心16G内存：每秒1500并发。起步，一般般，容易出现压力问题
• 16核心32G内存：每秒3000并发。常用，比较推荐。

5 数据库压力测试

申请到一个数据库以后，应该在实际开发之前就对这个数据库进行压力测试。
主要原因如下：

• Java系统测试和数据库测试是两回事。
• 首先必须知道数据库的极限，才能以这个极限去设计Java系统

QPS：Query Per Second 和 TPS：Transaction Per Second

QPS简单理解为，每秒数据库可以处理多少个sql语句。
TPS简单理解为，每秒数据库可以处理的事物数量。这个比较常用。

5.1 IO测试相关的指标

IOPS

IOPS指的是机器的随机IO并发处理能力，代表每秒钟可以执行多少个随机IO请求。

一个很关键的指标。主要决定你内存中的脏数据写回磁盘的效率

吞吐量

机器磁盘存储每秒可以读写多少字节的数据量。

关键指标。关乎到每秒可以把多少的redo log等日志文件写入磁盘。

latency

重要的指标。往磁盘里面写入一条数据的延时。

延迟越低，数据库性能越高，执行每个SQL语句和事物的速度会越快。

5.2 一些其他的次重要的指标

CPU负载

简而言之，看看你CPU能不能发出足够多的QPS。

网络负载

简而言之，看看你的网络带宽情况支持不支持大流量的传输数据。

内存负载

简而言之，看看你内存还够不够。