mysql

最新推荐文章于 2023-02-06 17:18:26 发布

我家有个小祖宗@

最新推荐文章于 2023-02-06 17:18:26 发布

阅读量220

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/csdn_scusedzz/article/details/109217467

版权

笔记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Preview

Mysql主要版本与分支

Oracle、Mysql是世界市场占比最高的两种数据库

互联网早期的选型【IOE】：IBM的服务器、Oracle的数据库、EMC的存储设备，都是有钱的公司采用，例如银行、电信、石油、证券等

Mysql：互联网高速发展，互联网企业使用第一。

Oracle：互联网之外使用第一，传统软件行业使用。

1979年，Monty Widenius开发了一款存储引擎，并是ISAM引擎的前身
1999年~2000年，三人创建的了Mysql AB公司，引入了事务引擎BDB
2000年，将软件开源，并且重新整理ISAM，并重新命名为MyISAM引擎
2001年，引入InnoDB引擎，支持事务，行级锁。正式结合在4.0版本
2005年，发布5.0里程碑版本，加入游标，存储过程和触发器的支持
2008年~2009年，并购时代，Mysql AB被Sun收购，第二年Sun被Oracle收购
2010年，重新规划版本分为社区版（Commity）和企业版（Enterprise）收费的，默认引擎更换为InnoDB
2013年，对InnoDB进行改造，提供全文索引能力，使InnoDB适应各种场景
2015年10月，首个GA正式版本（5.7.9）发布
2018年，首个GA正式版（8.0.11）发布

Mysql应用架构演变

架构V1.0-单机单库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bSkE8GV0-1603335105917)(mysql01.jpg)]

瓶颈：
- 数据量太大的话，超出服务器的承受力
- 读写操作量太大，超出一台服务器的承受力
- 如果数据库服务器出现宕机，则整个应用都使用不了
架构V2.0-主从架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rH55Im2C-1603335105919)(mysql02.jpg)]

好处：
- 读写分离（主写，从读）
- 主库宕机，也可以切换到从库继续提供服务
瓶颈：
- 数据量太大，超出一台服务器承受
- 如果写操作太大，超出一台M服务器的承受能力
架构V3.0-分库分表（分片）

水平拆分来来解决V1.0和V2.0中遇到的写入瓶颈和存储瓶颈，可以将拆陈多个数据库实例DB，分表操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Aao1vKyq-1603335105920)(mysql03.png)]

水平拆分区别于垂直拆分，垂直拆分的结果，每一个实例都拥有全部的数据，而水平拆分后，任何实例只有全量的1/n的数据

瓶颈：
- 数据如何路由，这是一个关键问题，Hash拆分，List拆分等
- 路由策略，如何分片
- 如何保持数据的一致性
架构V4.0-云数据库

云数据库（云计算）由SaaS服务商提供，底层的实现细节（架构模型，配置，部署等等）由SaaS提供，开发并不关心，只需要调用服务接口即可；

MySql体系结构概述

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IdS2t0W7-1603335105922)(mysql04.png)]

一、网络连接层

客户端连接器（Client Connectors）：指能够和MySQL服务器建立连接的客户端。目前支持所有主流的服务端编程技术，例如常见的Java、C、Python、.NET等，它们通过各自的API技术与MySQL建立连接。

二、服务层（MySQL Server）

连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
系统管理和控制工具（Management Services & Utilities）:例如备份恢复、安全管理、集群管理等。
sql接口（SQL Interface）：用于接收客户端发送的各种SQL命令，并返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
解析器（Parser）：负责将请求的SQL解析生成一个”解析树“。然后根据一些MySQL的规则进一步检查解析树是否合法。

对sql进行解析，关键字，语法检查等，生成解析树
查询优化器（Optimizer）：当“解析树”通过解析语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎进行交互。

寻找最优的查询优化路径，检索路径，存储路径等；可以将sql语句进行优化
缓存（Cache & Buffer）：缓存机制是由一系列的小缓存组成。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就直接去查询缓存中取得数据。

三、存储引擎层（Pluggable Storage Engines）

执行计划引擎<----->存储引擎<----------->底层文件系统

存储引擎是针对表来定制的，可以在建表时候指定存储引擎。

负责与底层文件系统交互

MyISAM存储引擎
InnoDB存储引擎

四、系统文件层（File System）

该层负责将数据库的数据和日志存储在系统的文件系统里，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件、数据文件、配置文件、pid文件、socket文件等。

日志文件
- 错误日志（Error Log）
  
  默认开启的，如果想查看日志，则可以通过命令查询日志文件的路径。
```
show variables like '%log_err%';
```
- 通用日志查询（General Query Log）
  
  记录一般的查询日志，默认关闭的，可以通过下面命令，进行开启
```
show variables like '%general%'; --模糊查询
```
- 二进制日志（binary log）
  
  记录了对MySQL数据库的所有执行的更改操作，并且记录了语句发生的时间，执行的时长等；但是不记录select、show等不修改数据库的sql，主要用于数据库的恢复和主从数据库的复制
```
show variables like '%log_bin%';-- 是否开启
show variables like '%binlog%';-- 参数查看
show binary logs; -- 查看日志文件
```
- 慢查询日志（Show query log）
  
  记录所有执行时间超时的查询SQL，默认是10秒。
```
show variables like '%slow_query%';-- 参数查询 ，是否开启，日志文件名
show variables like '%long_query_time%';-- 默认超时时间查询
```
配置文件

用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
数据文件
- db.opt文件：记录这个数据库默认使用的字符集和校验规则
- frm文件：存储与表相关的元数据（meta）信息，包括表结构定义，每一个表都会有一个frm文件。
- MYD文件：MyISAM存储引擎专用，存放MyISAM表的数据（data），每一个表都会有一个.MYD文件。
- MYI文件：MyISAM存储引擎专用，存放MyISAMb表的索引相关信息，每一个表都会对应有一个.MYI文件。
- ibd和IBDATA文件：
  
  存放InnoDB的数据文件（包括索引）。InnoDB存储方式有两种，一种是独享表空间，另一种是共享表空间
  
  独享表空间使用.ibd文件来存放数据（索引），且每一个表对应一个.ibd文件
  
  共享表空间使用.ibdata文件，所有表共同使用一个（或多个，自行配置）.ibdata文件
- ibdata1文件：系统表空间数据文件，存储表元数据，Undo日志等。
- ib_logfile0、ib_logfile1文件：Redo log日志文件。
pid文件

pid文件是mysql在Unix/Linux环境下的进程文件，存放着进程的id
socket文件

socket文件也是在Unix/Linux环境下才有的，用户在Unix/Linux环境下可以不通过TCP/IP网络而直接使用Unix Socket来连接MySQL。

InnoDB存储结构之Undo Log日志

一、Undo log

Undo：以撤销为目的，返回某个指定某个状态。

Undo Log：数据库事务开始之前，会将要修改的记录放到Undo日志里，当事务回滚或者数据库奔溃时，可以利用Undo日志，撤销未提交事务对数据库产生的影响。

Undo Log产生和销毁：Undo Log在事务开始前产生；

MySQL分库分表实战

背景描述

单机数据库

刚开始请求和用户访问都不多。
主从架构

随着用户的增加，考虑到系统的高可用和越来越多的用户请求，开始使用数据库主从架构
分库分表

当用户量级和业务进一步增加，写请求越来越多，开始使用分库分表

遇到的问题

用户的请求量太大

单服务器TPS、内存、IO都会有上限，需要将请求打散到多个服务器
单库数据量太大

单个数据库处理能力有限；单库所在服务器的磁盘空间有限；单库上的操作IO有瓶颈
单表数据量太大

查询、插入、更新操作会慢，在加字段、加索引、机器迁移都会产生高负载

分库分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

分库分表包括分库和分表两个部分，在生产中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。

对不同的量级进行拆分

垂直拆分
- 垂直分表
  
  垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段。
表中字段太多且包含大字段的时候，在查询时对数据的IO，内存会受到影响，同时更新操作时，产生的Binlog会很大，Mysql在主从同步时也会有延迟的风险，因此可以通过垂直分表解决，解决单一表数据量大的问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-81Q2ppb4-1603335105925)(mysql06.png)]
垂直分库

例如，用户-简历-职位

业务切割的足够独立，数据库也会按照业务切分，保证业务数据的隔离，大大提升了数据库的吞吐性能；

解决了单一库带来的IO瓶颈，每个库独立在不同的服务器上，这样就不会因为硬件资源而带来性能瓶颈；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EAGJVJH1-1603335105927)(mysql05.png)]

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

它带来的提升是：
- 解决业务层面的耦合，业务清晰
- 能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈
  
  垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。
水平拆分
- 水平分表
  
  水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
  
  以拉勾的订单表为例子，订单就相当于拉勾的简历投递成功，相当于下单成功；
  
  可以理解在同一个数据库里的数据切片（将表里的数据进行切片）
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KNkywVg6-1603335105928)(mysql07.png)]
  
  水平分表的原则：
  - range
    - 时间：按年、月、日
    - 地域：按照省或市
    - 大小：从0到1000000一个表
  - Hash
    - 用户ID取模
- 水平分库
  
  水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。
  
  将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表（每个服务器的是1/n）,只是表中的数据集合不同。水平分库可以有效缓解单机和单库带来的性能瓶颈，突破IO，连接数，硬件资源等瓶颈，如下图：
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qMFQCD8l-1603335105930)(mysql08.png)]
  
  水平分库原则：不跨库，不夸表，保证同一类的数据都在一个服务器上面。
  
  水平分库在垂直分库基础上再进行水平方向拓展。
  
  水平分表作为水平分库的补充优化

总结

垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。

垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。

水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。

水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

参考分库分表的CSDN博客连接：https://blog.csdn.net/weixin_44062339/article/details/100491744

存在的问题

主键选择：
- UUID：本地生成，不依赖于数据库
- SNOWFLAKE：百度的UidGenerator、美团的Leaf、基于SNOWFLAKE算法实现
数据一致性:
- 强一致性：XA协议
- 最终一致性：TCC、saga、Seata
数据库扩容：
- 成倍增加数据节点，实现平滑扩容
- 成倍扩容后，表中部分数据请求已被路由到其他节点上面、可以清理掉
业务层改造：
- 基于服务层代理的方式：MyCat、Sharding-Proxy、Mysql Proxy
- 基于应用层的方式（在代码当中通过配置解决）：Sharding-jdbc
分库后面临的问题：
- 事务问题
- 跨库夸表join问题
  - 全局表（字典表）：基础数据/配置数据，所有库拷贝一份
  - 字段冗余：可以使用字段冗余（不容易改变且少量的），就不用join进行跨库夸表查询了
  - 系统层组装：分别查询各个库的数据，进行组装
- 额外的数据库管理负担和数据运算压力：维护成本高

ShardingSphere项目

1.1 ShardingSphere

Apache ShardingSphere是一款开源分布式数据库中间件组成的生态圈。它有Sharding-JDBC，Sharding-Proxy和Sharding-Sidecar（规划中）这三款相互独立的产品构成。它们均提供标准化的数据分片，分布式事务和数据库治理功能，可适用于Java同构，异构语言，容器，云原生等各种多样化的应用场景。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tSEy72bA-1603335105931)(mysql09.png)]

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式场景下利用关系型数据库的计算和存储能力，而非实现一个全新的关系型数据库。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tsC5XBwC-1603335105933)(mysql10.png)]

Sharding-JDBC：被定位为轻量级Java框架，在Java的JDBC层提供额外的服务，以jar包的形式使用。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iJdnRXxi-1603335105934)(mysql11.png)]
Sharding-Proxy：被定义为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CufJ4UdR-1603335105936)(mysql12.png)]
Sharding-Sidecar：被定位为Kubernets或Mesos的云原生数据库代理，以DaemonSet形式代理所有对数据库的访问。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WaLf75XF-1603335105937)(mysql13.png)]

三者区别

	Sharding-JDBC	Sharding-Proxy	Sharding-Sidecar
数据库	任意	MySQL	MySQL
连接消耗数	高	低	高
异构语言	仅Java	任意	任意
性能	损耗低	损耗略高	损耗低
无中心化	是	否	是
静态入口	无	有	无