一：我们的系统采用数据库连接池的方式去并发访问数据库，然后数据库自己其实也会维护一个连接池，其中管理了各种系统跟这台数据库服务器建立的所有连接
常见的数据库连接池有DBCP,C3P0,Druid
常见的mysql 存储引擎 innodb,mylsam
InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。
MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。
线程-> sql接口-> 查询解析器-> 查询优化器-> 执行器-> 存储引擎->
线程：监听网络连接，监听请求以及读取请求数据，从网络连接中读取和解析出来一条我们的系统发送过去的SQL语句
SQL接口：负责处理接收到的SQL语句（当MySQL工作线程接收到SQL语句后，就会转交给SQL接口去执行）
查询解析器：让MySQL能看懂SQL语句（比如：select id,name,age from users where id=1，（1）我们现在要从users 表里查询数据 （2）查询 id 字段的值等于1的那行数据 （3）对查出来的那行数据要提取里面的 id，name，age 三个字段）
总而言之：按照既定的SQL语法，对我们按照SQL语法规则编写的SQL语句进行解析，然后理解这个SQL语句要什么
查询优化器：选择最优的查询路径（直接定位users表中‘id’字段等于1的一行数据，然后查出来那行数据的‘id,name,age’三个字段的值就可以了先把‘users’表中的每一行数据的‘id,name,age’三个字段的值都查出来，然后从这批数据里过滤出来‘id’字段等于1的那行数据的‘id,name,age’三个字段）
执行器：根据优化器执行计划调用存储引擎的接口，然后不停调用存储引擎各种接口去完成SQL语句的执行计划，更新或提取数据出来
存储引擎：帮助我们去访问内存以及磁盘上的数据
InnoDB的重要内存结构：缓冲池（Buffer Pool）
缓冲池：会缓存很多数据
undo日志文件：让你更新的数据可以回滚（把要更新前的’值’及’id’写入undo日志文件中去）
脏数据：缓冲池里的数据与磁盘上的同一id的值不一样
redo日志：记录下来你对数据做了什么修改（例如id=10 这行记录修改了name字段值为’李四’）属于innodb特有的一个东西
提交事务的时候将redo日志写入磁盘中:innodb_flush_log_at_trx_commit
当此参数设置为0 - 提交事务不会把redo log buffer里的数据刷入磁盘文件，
当此参数设置为1 - 提交事务必须把redo log 从内存刷入到磁盘
当此参数位置为2 - 提交事务先把redo日志写入磁盘文件对应的os cache缓存里去，一秒后才刷入磁盘里去
一般建议将redo日志刷盘策略设置为1，保证事务提交之后，数据绝对不能丢失
可能会丢失数据的时机
一：当链路在 redo log buffer ，系统宕机，会导致数据丢失
二：还没提交事务，MySQL宕机了，会导致内存里的Buffer Pool中修改的数据丢失，及redo log buffer中的redo日志也丢失
三：redo日志在os cache时，MySQL宕机，数据也会丢失
MySQL binlog:归档日志，偏向于逻辑性的日志，类似与 “对users表中的id=10的一行数据做了更新操作，更新以后的值是什么”，属于mysql server自己的日志文件（将本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置，都写入到redo log日志文件里去，同时在redo log日志文件里写入一个commit标记）
binlog日志的刷盘策略分析：sync_binlog 默认值为0
当此参数设置为0 - 先进入os cache内存缓存
当此参数设置为1 - 强制在提交事务时，把binlog直接写入到磁盘文件里去
事务的成功与否全凭 commif标记是否在redo日志里
最后等合适时机，IO线程自然还是会把这个修改后的数据刷到磁盘上的数据文件里去的
服务器配置参考：
4核8G 每秒抗下500左右并发访问量
8核16G每秒一两千并发请求没问题
16核32G每秒两三千，甚至三四千的并发请求也都是可以的
专有名词解释：
QPS —query Per Second 数据库每秒可以处理多少个请求（sql语句），一次请求=一个sql语句
TPS — Transaction Per Second 数据库每秒会处理多少次事务提交或者回滚
IOPS — 机器的随机IO并发处理的能力，比如机器可以达到200 IOPS，意思就是说每秒可以执行200个随机IO读写请求
吞吐量 — 这个指的是机器的磁盘存储每秒可以读写多少字节的数据量
一般普通磁盘的顺序写入的吞吐量每秒都可以达到200MB
latency — 这个指标说的是往磁盘里写入一条数据的延迟
例写一条日志到磁盘文件里，延迟1ms，还是100ms对你的数据库SQL语句执行性能是有影响的。
CPU负载、网络负载、内存负载
可以用sysbench工具对mysql数据库进行压力测试
生产环境数据库的可视化监控平台，我们会基于Prometheus+Grafana来搭建
数据行、数据页、extent、extent分组、表空间、磁盘空间
一个数据页16KB
一个数据区（extent）有64 x 16KB个数据页 = 1MB
一个extent分组有256个extent
描述数据相当于缓存页大小的5%左右，也就是每个描述数据大概是800个字节左右的大小，假设buffer pool大小是128MB，实际上有130多MB，因为需存放每个缓存页的描述数据
free链表
描述 ：双向链表数据结构，每个节点就是一个空闲的缓存页的描述数据块的地址
结构：每个描述块里都有两个指针，一个是free_pre，一个是free_next，分别指向自己的上一个free链表的节点，以及下一个free链表的节点，
拥有一个基础节点40字节大小，存放free链表的头节点和尾节点的地址，及free当前有多少节点
运行时：从free链表中获得空闲的描述数据块-》接着得到缓存页-》将磁盘上的数据页读取到对应的缓存页里去-》同时把相关的一些描述数据写入缓存页的描述数据块里去比如这个数据页所属的表空间之类的信息-》从free链表移除描述数据块
每次读取一个数据页到缓存，都会在哈希表中写入key-value
数据页缓存哈希表 — key(表空间号+数据页号) ， value(缓存页的地址)
用法：判断缓存页是否已被缓存
flush链表
描述：通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表
结构：每个描述块里都有两个指针，一个是flush_pre，一个是flush_next，分别指向自己的上一个free链表的节点，以及下一个free链表的节点，
LRU链表（Least Recentle Used）：基于冷热数据分离的lru链表设计
预读加载机制，select 查询大表加载进来的数据
热数据占链表63%，冷数据占链表37%
访问了热数据1/4之后的数据才会挪到热链表头部
即100个数据里，访问了25之后的75个数据会被挪到头部，访问1-25则不会被挪
与redis联动（在电商系统的大商品）：
可基于热数据的缓存预加载
可能导致频繁被访问的缓存页被淘汰的场景：
1.预加载机制：
一：innodb_read_ahead_threshold 默认值56，意思如果顺序访问了一个区里的多个数据页（参数值）就会触发预读机制，把下一个相邻区中的素有缓存页都加载到缓存里去
二：innodb_random_read_ahead 默认值OFF ，如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据都是比较频繁会被访问，此时会触发预读机制，把这个区的其他数据页都加载到缓存里去
2.select 查询全表扫描加载进来的数据 （SELECT * FROM USERS ）
冷数据区域设置： innodb_old_blocks_pct 默认值37%
冷热分离重要参数设置：inndb_old_blocks_time 默认值1000毫秒 （1秒后还被访问才会被放到热区域的表头部）
刷盘机制：
一：free链表无可用区域时，那么便会将冷区域及flush链表中修改成功的数据页陆续刷入磁盘
二：定时刷盘策略：定时把冷数据区域尾部的一些缓存页刷入磁盘（flush、lru）
free、flush、lru动态运行效果
不停加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动
MySQL：
MySQL生产优化经验：多个Buffer Pool优化提高并发访问能力
Mysql默认规则是，如果你给buffer pool分配的内存小于1GB那么最多分配一个buffer pool
服务端配置：
[server]
innodb_buffer_pool_size = 8589934592 8GB内存
innodo_buffer_pool_instances = 4 4个Buffer Pool 每个2GB
每个Buffer Pool负责管理一部分的缓存页和描述数据块，多个chunk共享一个Buffer Pool里的free,flush,lru等链表
chunk：
意义：基于chunk机制，支持运行期间动态调整buffer pool的大小
buffer pool = 多个chunk 组成
例：2GB buffer pool = 128MBchunk x 16个
MySQL物理数据存储格式：一行数据在磁盘上是如何存储的
行溢出：跨行、跨数据页存储数据
数据页的构成：下图
文件头 —38个字节
数据页头 — 56个字节
最大记录 和 最小记录 —各占据26个字节
数据行、空闲区域、数据页目录 大小是不固定的
文件尾部占据8个字节
空数据页是没有数据行那个区域的
redo
** 描述：对表空间xx中的数据页xx中的偏移量为xxxx的地方更新了数据xxx
结构：日志类型（MLOG_1BYTE），表空间ID，数据页号，数据页中的偏移量，具体修改的数据
redo log block
描述：用来存放多个单行日志的
结构：512字节，redo log block 512字节分为3个部分，一个是12字节的header块头，一个是496字节的body块体，一个是4字节的trailer块尾
位置：**跟redo log buffer类似，是申请出来的一片连续空间，然后里面划分出了N多个空的redo log block
innodb_log_buffer_size 指定redo log buffer 大小 默认值16MB
先把redo log 写到redo log block 满了512字节后，继续写下一个redo log block，直到buffer 里所有block满了，就追加到redo log日志文件里去
同一个事务里的增删改操作，会有多个redo log，这多个redo log 为一组（或同一个block存储，或跨多个block存）
同组redo log -》 未知名区域 -》redo log block
**redo log 刷入磁盘机制：
一：如果写入redo log buffer 的日志已经占据了redo log buffer 总容量的一半了，也就是超过了8MB的redo log 在缓存里了，此时就会把他们刷入到磁盘文件里去
二：一个事务提交的时候，必须把他的那些redo log 所在的redo log block 都刷入到磁盘文件里去，只有这样，当事务提交之后，他修改的数据绝对不会丢失，因为redo log里有重做日志，随时恢复事务做的修改
三：后台线程定时刷新，有一个后台线程每隔1秒就会把redo log buffer 里的redo log block刷到磁盘文件里去
四：MySQL关闭的时候，redo log block都会刷入到磁盘里去
redo log 参数**：
** innodb_log_group_home_dir 设置存放目录
innodb_log_file_size 指定每个redo log 文件的大小默认值48MB
innodb_log_files_in_group 指定日志文件数量
undo log
描述：回滚日志，根据回滚日志去恢复缓存页里被更新的数据
结构**：这条日志的开始位置
主键的各列长度和值
表id
undo log 日志编号
undo log 日志类型
这条日志的结束位置
insert 类型 —- TRX_UNDO_INSERT_REC
事务隔离级别
脏写：（都是写数据）就是两个事务没提交的状况下，都修改同一条数据，结果一个事务回滚了，把另外一个事务修改的值也给撤销了，所谓脏写就是两个事务没提交状态下修改同一个值。
脏读：（一个写一个查）事务A正在执行中，事务B执行修改了x值，事务A读到x值，这时事务B回滚。
不可重复读：（一个查三个写）事务A正在执行，事务B执行并提交x值，事务A读到x值。事务C执行并提交xx值，事务A读到xx值。
幻读**：（查多条数据，插入数据）事务A查数据，第一次查10条，这时事务B插入了几条数据并提交，接着事务A第二次查询，查出来12条数据，宛若出现幻觉。
4种事务隔离级别
read uncommitted(读未提交) 不允许发生脏写
read committed（读已提交） 不允许发生脏写、脏读
repeatable（可重复读） 不允许发生脏写、脏读、不可重复读
read serializable(串行化) 不允许发生脏写、脏读、不可重复读、幻读
Spring里隔离级别设置**
@Transtional(isolation=isolation.DEFAULT)
@Transtional(isolation=isolation.READE_COMMITTED)
@Transtional(isolation=isolation.REPETABLE_READ)
@Transtional(isolation=isolation.SERIALIZABLE)
RC级别 ReadView机制
MySQL锁机制：
**独占锁：更新一行数据默认加独占锁
共享锁：查询数据可以手动加共享锁（select * from table lock in share mode）
独占锁和共享锁是互斥的，在有独占锁的情况下，查可以基于review机制去查（不用加锁）
查询操作加互斥锁（select from table for update）,只能等这次事务更新了数据并提交，其它事务才能更新数据
建议：通过redis、zookeeper用分布式锁实现复杂业务下的锁机制*
表级别锁：下图
手动加锁：
性能抖动优化**：
把innodb_io_capacity 设置为SSD固态硬盘的IOPS，让他刷缓存页尽量块，同时设置
innodb_flush_neighbors为0，让他每次别刷临近缓存页，减少要刷缓存页的数量，提高刷缓存页性能
若有收获，就点个赞吧
0 人点赞
