mysql整理

最新推荐文章于 2024-08-29 17:00:41 发布

swainnnn

最新推荐文章于 2024-08-29 17:00:41 发布

阅读量268

点赞数

分类专栏：知识点整理数据库文章标签： mysql

本文链接：https://blog.csdn.net/swain_yj/article/details/119304277

版权

知识点整理同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

MySQL相关知识点记录

1.mysql驱动：与mysql建立网络连接
2. InnoDB架构设计：
3.binlog: 归档日志属于mysql server自己的日志文件
4. 线上部署
5.压测：
6.Prometheus+Grafana监控平台
7.物理设计
8.在磁盘上如何存储？
9.数据库的多事务并发问题：
10.解决多事务并发问题：
11.锁：
12.数据页物理结构：
13 高可用
保证mysql高性能： 1.数据量不要太大 2.查询用上索引

1.mysql驱动：与mysql建立网络连接

数据库连接池
网络连接必须让线程处理
SQL接口： 负责处理接收到的sql语句
查询解析器： 让MYSQL看懂SQL语句
查询优化器： 选择最优查询路径
执行器： 根据执行计划调用存储引擎接口
调用存储引擎接口： 读取内存或磁盘数据

2. InnoDB架构设计：

update USER set name="zhangsan" where id=10;
缓冲池Buffer Pool： 若没有读取磁盘数据加载到缓冲池（同时加锁）
undo日志文件： 原来的值写入undo（便于回滚）
更新BufferPool中的缓存数据： 此时数据为脏数据
Redo Log Buffer：记录对数据做的修改（系统宕机）
提交事务RedoLogBuffer刷入RedoLog磁盘文件： innodb_flush_log_at_trx_commit

3.binlog: 归档日志属于mysql server自己的日志文件

提交事务的时候，同时会写入binlog
sync_binlog参数可以控制binlog的刷盘策略
binlog写入磁盘文件之后，接着就会完成最终的事务提交
本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置，都写入到redolog日志文件里去，同时在redo log日志文件里写入一个commit标记。
后台IO线程随机将内存更新后的脏数据刷回磁盘。

4. 线上部署

java程序 4核8G 每秒能抗500请求左右，看每个请求处理耗费时间
数据库 16核32G以上  每秒两三千请求  最好是采用SSD固态硬盘 大量的磁盘IO

5.压测：

CPU负载、磁盘IO负载、网络IO负载、内存负载，然后数据库能否每秒处理掉这1000个请求
IOPS：机器的随机IO并发处理的能力
吞吐量：机器的磁盘存储每秒可以读写多少字节的数据量
latency: 往磁盘里写入一条数据的延迟

压力测试服务器情况
1. top:
	load average: 0.15, 0.05, 0.01 CPU在1分钟、5分钟、15分钟内的负载情况
	Mem: 内存使用情况； 内存的使用率在80%以内，基本都还能接受

2. dstat命令 观察机器的磁盘IO情况
	dstat -d: read writ 每秒读取和写入数据情况
	dstat -r: read writ 随机磁盘读取,写入每秒钟多少次； 一般每秒在两三百次都是可以承受的
	dstat -n: recv send 是每秒钟网卡接收，发出的流量有多少kb

6.Prometheus+Grafana监控平台

Prometheus其实就是一个监控数据采集和存储系统
Grafana就是一个可视化的监控数据展示系统
node_exporter自动采集这台linux机器上的CPU、磁盘、内存、网络之类的各种监控数据

7.物理设计

数据页：多行数据组成一个数据页，读取数据时整个数据页放入BufferPool
缓存页：在BufferPool中与数据页对应
free链表：双向链表数据结构，每个节点就是一个空闲的缓存页的描述数据块的地址
flush链表: 修改过的缓存页加入到flush链表中,这些都是脏页，后续都是要flush刷新到磁盘上去的
lru链表: 缓存页不够冷数据数据淘汰
表空间、数据页，这些东西，都是物理上的概念，实际上在物理层面，你的表里的数据都放在一个表空间中，表空间是由一堆磁盘上的数据文件组成的，这些数据文件里都存放了你表里的数据，这些数据是由一个一个的数据页组织起来的
热数据的缓存预加载
LRU链表的热数据区域的访问规则被优化了一下，即你只有在热数据区域的后3/4部分的缓存页被访问了，才
会给你移动到链表头部去。

配置相关
innodb_buffer_pool_size = 2147483648 buffer_pool大小
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

8.在磁盘上如何存储？

innodb 一行数据在一个数据页（默认大小16kb）里存储，如果一个数据页里放不下一行数据，就会有行溢出问题，存放到多个数据页里去
一行数据在磁盘上的存储格式: 变长字段长度列表 NULL值列表头信息 column1=value1 column2=value2 … columnN=valueN
逆序存放这行数据里的变长字段的长度
NULL值是以二进制bit位来标记存储的
40个bit位的数据头用来描述这行数据的
前两个预留位无意义；delete_mask标记否被删除；min_rec_mask；
4个bit位是n_owned；13个bit位是heap_no记录堆里的位置；3个bit位record_type数据的类型;
16个bit的next_record指向他下一条数据的指针;
“jack NULL m NULL xx_school” : 0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school
进行字符集编码之后再存储的: 0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262
在实际存储一行数据的时候，会在他的真实数据部分，加入一些隐藏字段

我们平时创建的那些表都是有对应的表空间，每个表空间就是对应了磁盘上的数据文件，在表空间里有很多组数据区，一组数据区是256个数据区，每个数据区包含了64个数据页，是1mb
读写机制：一种是对redo log、binlog这种日志进行的磁盘顺序读写，一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写。

ulimit -HSn 65535 ： linux的文件句柄限制
redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值，具体修改的值是什么，他里面需要记录的就是表空间号+数据页号+偏移量+修改几个字节的值+具体的值

UndoLog
INSERT语句的undo log: 日志开始位置-主键的各列长度值-表id-UndoLog日志编号-UndoLog日志类型-结束位置

9.数据库的多事务并发问题：

脏写: 事务A和事务B同时更新一条数据，然后事务A用自己的undo回滚，B更新的值没了。。
脏读: 事务B查询了事务A修改过的数据做业务处理，但是此时事务A还没提交，此时事务A回滚。。
无论脏写还是脏读，都是因为一个事务去更新或者查询了另外一个还没提交的事务更新过的数据。
不可重复读： 事务A多次查询，事务B、事务C先后更新数据并提交，A多次查询值不一样。
幻读： 一个事务用一样的SQL多次查询，每次查询都会发现查到了一些之前没看到过的数。

10.解决多事务并发问题：

读未提交： 避免脏写，不允许两个事务在没提交的情况下更新同一数据
读已提交： 避免脏写脏读，事务没提交情况下修改的值别的事务读不到
可重复度： 不会发生脏写脏读和不可重复读，事务一旦开始查询同一行数据的值相同（默认级别）
串行化： 不允许事务并发执行，性能极差
MVCC机制：依托这个MVCC机制，就能让RR级别避免不可重复读和幻读
	undo log版本链：多个事务串行执行都会更新隐藏字段txr_id和roll_pointer，之前多个数据快照对应的undolog，会通过roll_pinter指针串联起来，形成一个重要的版本链
	ReadView机制：
		m_ids: 记录此时未提交的事务id
		min_trx_id: m_ids最小值
		max_trx_id: mysql要生成的下一个事务id
		creator_trx_id: 当前事务id
	RC： 每次查询重新生成ReadView
	RR： 一次事务只有一个ReadView
spring支持： @Transactional(isolation=Isolation.DEFAULT)

11.锁：

行锁：
	更新数据必然加独占锁，查询默认不加锁走MVCC机制，可以手动加共享锁(lock in share mode),共享锁与独占锁互斥，共共不互斥。手动加独占锁(for update).一般查询不会手动加锁用redis,zk代替。
表锁： 特别鸡肋 默认意向锁都不互斥
	LOCK TABLES xxx READ: 表级共享锁
	LOCK TABLES xxx WRITE: 表级独占锁

mysql性能抖动：
1. bufferPool满了，刷盘太慢
2. redoLog所有磁盘文件写满了，需要回到第一个文件覆写，对应缓存页刷盘。

12.数据页物理结构：

按顺序一页一页存放，相邻两页双向链表相互引用。数据行组成单向链表；每个数据页会有页目录，根据数据行的主键从小到大存储的目录。有索引根据页目录二分查找，没有索引根据链表全表扫描。
索引目录：每个数据页的页号和数据页里最小主键值组成的目录（主键索引）
数据量大，索引数据在数据页中存储叫索引页，一个索引页不够分裂多层索引页，组成B+树结构。
聚簇索引（主键索引）：在B+树索引数据结构里，叶子节点就是数据页自己本身。InnoDB存储引擎中数据就在聚簇索引里，聚簇索引就包含了数据。
主键外其它字段索引（二级索引）：创建B+树，叶子节点存储主键和对应字段的值，再根据主键查找数据位置。
索引太多：1.耗费磁盘空间 2.增删改时需要维护各个索引的有序性，页挪动。
联合索引：等值匹配（全是等号肯定会用索引）；最左匹配规则；最左前缀匹配原则（like）;范围查找规则（><）
order by 也可以用索引，联合索引升序降序要一致。
group by 也可以用索引。

索引创建规则：
1. 针对sql中的where, order by, group by条件创建
2. 索引字段基数与前缀索引
3. SQL索引字段不要用函数
索引使用规则：
你where语句里有等值匹配，还有范围匹配，此时必须是先让联合索引最左侧开始的多个字段使用等值匹配，接着最后一个字段是范围匹配。
第一个范围查询之后的其他范围查询是用不上索引的.
mysql执行计划Explain:
id、select_type、table、partitions、type
type:
CONST: 是直接通过索引定位到数据，速度极快(主键或唯一索引)
ref: 普通索引定位到数据，数据超快
ref_or_null: 索引等值和null值查询
rang: 索引范围查询
index: 只要遍历二级索引就可以拿到你想要的数据，不需要回源到聚簇索引
all: 全表扫描
possible_keys: 跟type结合起来的,哪些索引是可供选择;
key，就是在possible_keys⾥实际选择的那个索引;
key_len就是索引的⻓度。
rows: 预估读取到的数据量
filtered: 经过搜索条件过滤之后的剩余数据的百分⽐
extre:

多表查询：嵌套循环关联 for() { for(){ for(){} } } 每个表都要建好索引
inner join: 两个表数据必须完全对应上才返回（连接条件可以放在where语句中）
outer join: 连接条件一般放在on字句里
left join: 左侧数据全返回
right join: 右侧数据全返回

SQL调优利器： profiling工具，会展示sql执行时的各种耗时

13 高可用

主从复制：
binlog记录所有增删改操作
1. 从库IO线程与主库建立TCP连接
2. 主库IO dump线程通过tcp连接传输binlog日志
3. 从库IO线程读取binlog到本地relay日志
4. 从库另一个线程读取relay日志，日志重做

半同步复制： 从库日志重做成功返回给主库，主库在提交事务并响应给客户端；
安装半同步复制插件开启半同步复制功能：
数据延迟：
	原因：主库多线程写，从库单线程读
	监控工具：perconatoolkit工具集中的pt-heartbeat
	解决： 从库多线程拉取binlog, 5.7后支持从库并行复制：slave_parallel_workers>0; slave_parallel_type=LOGICAL_CLOCK

读写分离： mycat / sharding-sphere
分库分表：按业务id分库分表，建立索引映射表同时进行分库分表，数据同步到ES做复制搜索。
分页问题： 1.通过索引映射表解决 2.数据放ES查询（推荐） 3.中间件mycat(不建议)

保证mysql高性能： 1.数据量不要太大 2.查询用上索引

swainnnn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql整理

MySQL相关知识点记录1.mysql驱动：与mysql建立网络连接2. InnoDB架构设计：3.binlog: 归档日志属于mysql server自己的日志文件4. 线上部署5.压测：6.Prometheus+Grafana监控平台7.物理设计8.在磁盘上如何存储？9.数据库的多事务并发问题：10.解决多事务并发问题：11.锁：12.数据页物理结构：13 高可用保证mysql高性能： 1.数据量不要太大 2.查询用上索引1.mysql驱动：与mysql建立网络连接数据库连接池网络连接必须让线程
复制链接

扫一扫