01|系统如何跟Mysql打交道

1、日常对mysql的了解

建库建表、增删改查、索引

2、mysql驱动

1）mysql-connector-java.jar 即java语言的mysql驱动。
2）mysql驱动，用来建立网络连接

3、数据库连接池

1）tomcat收到多个请求，采用多线程并发处理请求
2）如果多个线程去抢夺一个数据库链接，效率低下

3）tomcat每个线程访问数据库时，都会基于mysql驱动去建立一个数据库链接，然后执行sql语句，执行完再销毁这个数据库链接。如果是上百个线程并发，效率十分低下

4）数据库连接池，池子里有很多数据库连接，不同的连接去执行sql语句，执行后，不销毁这个数据库连接，放回池子，后续继续使用

5）mysql数据库的连接池
维护与系统之间的多个数据库连接。

02|为了执行SQL语句，MySQL用了什么样的架构设计

1、把mysql当个黑盒子一样执行sql语句

2、网络连接必须让线程来处理

网络连接必须分配一个线程，去监听请求及读取请求数据，比如从网络连接中读取和解析出来一条我们系统发送过去的SQL语句

3、SQL接口（SQL Interface）：负责处理接收到的SQL语句

4、查询解析器（Parser）：解析sql，让mysql能看懂sql语句

5、查询优化器（Optimizer）: 选择最优的查询路径

6、调用存储引擎接口，真正执行sql语句

数据存在内存和磁盘中，存储引擎按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据等等
例如：InnoDB、MyISAM、Memory

7、执行器：根据执行计划调用存储引擎的接口

03|InnoDB引擎

1、重要内存结构：缓冲池（Buffer Pool）

缓存数据，查询时先查缓冲池，没有的话，直接从磁盘加载到缓冲池里来，会对这行记录加独占锁

2、undo日志文件：记录回滚前的值，更新的数据可以回滚

执行一个更新语句，要是他在一个事务里的话，那么事务提交前可以进行回滚，undo日志文件记录更新前的值

3、更新缓存池中的缓存数据

1）从磁盘（比如数据是张三）加载到缓冲池
2）将更新前的旧值（张三）写入undo日志文件
3）更新缓存池中的数据为新值（李四）
4）此时内存和磁盘中的数据不一致，磁盘中的数据为脏数据

4、Redo Log Buffer : 内存中的一个缓冲区，存放redo日志

redo日志，记录对数据做了什么修改，在mysql宕机时，用来恢复更新过的数据

5、mysql宕机，缓冲池中的数据和 redo Log Buffer中的数据都会丢失，磁盘文件数据依然是原值，不影响使用

6、提交事务，将redo日志写入磁盘

1）配置 innodb_flush_log_at_trx_commit
2）值 = 0：提交事务，不会把redo写入磁盘。mysql宕机，内存和redo数据全部丢失
3）值 = 1：提交事务，把redo写入磁盘。事务提交成功，redo log必然在磁盘中。mysql宕机，可以根据磁盘上的redo日志来恢复之前的修改

4）值 = 2：提交事务，把redo写入磁盘文件对应的 OS Cache缓存中，而不是直接进入磁盘文件。大概1s后OS Cache再写入磁盘。如果还没有写入磁盘，就机器宕机，那么事务提交了，但是数据丢了
建议设置为1

04|binlog

1、binlog 归档日志，记录的偏逻辑性的日志

2、提交事务，也会把binlog日志写入到磁盘文件中

3、binlog日志刷盘策略

sync_binlog参数，默认值为0
值为0：进入os cache，然后入磁盘
值为1：直接入磁盘

4、写入binlog文件与位置写入commit标记

5、后台IO线程随机将内存更新后的脏数据刷回磁盘

05|生产经验：真实生产环境下的数据库机器配置如何规划

1、了解自己的数据库规划
如何规划生产环境下的数据库，设计压测方案，对数据库的压测，对数据库部署可视化监控系统
2、4核8G的机器部署普通java应用系统，每秒大致只能抗下几百的并发访问
3、高并发场景，数据库至少是8核16G以上的机器

06|生产经验：互联网公司的生产环境数据库是如何进行性能测试的

1、压测

基于一些工具模拟一个系统每秒发出1000个请求到数据库上去，观察一下他的CPU负载，磁盘IO负载，网络IO负载、内存复杂，然后数据库是否每秒能处理掉这1000个请求，还是只能处理500个。

2、首先看数据库能抗多大压力、再看java系统的压测

3、QPS TPS

1）QPS : Query Per Second，每秒处理多少请求
2）TPS：Transaction Per Second ，每秒处理多少事务量

4、IO相关的压测性能指标

1）IOPS：机器的随机IO并发处理的能力。比如200 IOPS ，每秒执行200个随机的IO读写请求
IOPS 指标太低，会导致内存的脏数据刷回磁盘的效率低
2）吞吐量：机器的磁盘存储每秒可以读写多少字节的数据量
每秒把redo log之类的日志多少写入到磁盘里面去
3）latency：往磁盘里写入一条数据的延迟
延迟越低，性能越高

5、压测相关的性能指标

1）CPU负载
2）网络负载
3）内存负载

07|生产经验：如何对生产环境中的数据库进行360度无死角压测

linux机器或者windows上装linux虚拟机，然后装一个mysql数据库，使用sysbench工具尝试数据库压测

08| 生产经验：在数据库的压测过程中，如何360度无死角观察机器性能

09|生产经验：如何为生产环境中的数据库部署监控系统

prometheus ：监控数据采集和存储系统，可以利用监控数据采集组件，从指定的mysql数据库中采集需要的监控数据。他自己有一个时序库，把采集到的监控数据放在自己的时序库中，本质是存储在磁盘文件里

grafana：是一个可视化的监控数据展示系统，可以把prometheus采集到的数据展示成报表，直观看到mysql监控情况

10|生产经验：如何为数据库的监控系统部署可视化报表系统

11|从数据的增删改开始讲起，回顾一下Buffer Pool在数据库里的地位

buffer pool就是数据库的一个内存组件，里面缓存了磁盘上的真实数据，我们所做的增删改查，主要是对buffer pool里面的数据操作

12|Buffer Pool这个内存数据结构到底长个什么样子

1、配置buffer pool的大小

[server]
innodb_buffer_pool_size = 214748…

2、数据页：mysql中抽象出来的数据单位

1）数据库核心数据模型：表+字段+行
2）磁盘文件中有很多数据页，每个数据页有很多行数据

3）更新一行数据：数据库找到这行数据所在的数据页，然后从磁盘里把这行数据所在的数据页加载到buffer pool中
buffer pool存放的是一个个数据页

3、磁盘上的数据页和buffer pool中的数据页如何对应起来

1）磁盘，默认数据页的大小是16K，一页包含了16KB数据
2）buffer pool ，数据页通常叫缓存页，跟磁盘上一一对应，一页都是16KB

4、缓存页对应的描述信息是什么

1）每个缓存页一般都有一个描述信息
2）描述信息：数据页所属的表空间、数据页的编号、缓存页在pool 中的地址 等
3）描述信息本身也是一块数据，每个缓存页的描述信息放在最前面，缓存页在后面

13|从磁盘读取数据页到Buffer Pool的时候，free链表有什么用

1、数据库启动，如何初始化buffer pool

1）数据库一启动，会根据buffer pool 大小，稍微加大一些，去操作系统去申请一块内存区域，作为buffer pool的缓存区域
2）内存区域申请完毕，按照默认的缓存页（16k）和描述数据（800字节）大小，在buffer pool中划分出来一个个数据页和对应的数据描述
3）起初缓存页都是空的，后期对数据库进行crud时，才会把数据从磁盘中取出来放在buffer pool的缓存页中

2、如何判断哪些缓存页是空闲的

1）用free链表，存储了 空闲缓存页的描述数据的地址
2）free链表：数据结构-双向链表，每个节点存储空闲的缓存页的描述数据的地址
3）free链表有一个基础节点，引用链表的头节点和尾节点，存储了链表中有多少个描述数据块的节点

3、如何将磁盘上的页读取到buffer pool 中的缓存页中去

1）从free链表取出来一个描述数据块，然后就可以获取到这个描述数据对应的空闲缓存页
2）把磁盘的数据页读取到对应的缓存页中
3）把描述数据写入到缓存页的描述数据块中（比如数据页所属的表空间）
4）描述数据块从free链表里面去除

4、如何判断数据页有没有被缓存

1）判断数据页有没有被缓存，如果没缓存走以上逻辑，已缓存直接使用
2）如何判断：用数据库中的一个哈希表数据结构
3）哈希表数据结构：
key : 表空间号+数据页号
value：缓存页地址