mysql系统文件、锁机制、事务、索引、架构演变、分库分表

1. 系统文件

1.1. MySQL结构与访问过程：

MySQL = 客户端 + 服务端
　　客户端 = Connection(语言连接器例如：PHP-pdo，MySQLi)
　　服务端 = SQL层 + 存储引擎层
　　　　SQL层 = 链接/线程处理 + 查询缓存 + 分析器 + 优化器
　　　　存储引擎 = InnoDB + MariaDB + .....

Connection：这一块其实主要是其他语言的连接，并不属于MySQL本身；主要是其他语言对于MySQL的连接操作的工具比如PHP中的：pdo，mysqli或者 Navicat for MySQL
SQL层：功能主要包括权限判断，SQL解析功能和查询缓存处理等。

链接/线程处理：客户端通过连接/线程层来连接MySQL数据库，连接/线程层主要用来处理客户端的请求、身份验证和数据库安全性验证等。
查询缓存和查询分析器是SQL层的核心部分，其中主要涉及查询的解析、优化、缓存、以及所有内置的函数，存储过程，触发器，视图等功能。
优化器主要负责存储和获取所有存储在MySQL中的数据

1.2 MySQL物理文件类型

日志文件主要包括：

1. 错误日志(Error Log)、
2. 二进制日志(Binary Log)、
3. 事务日志(InnoDB redo Log & undo Log)、
4. 慢查询日志(Slow Query Log)、
5. 查询日志(Query Log)、

数据库文件：
1. '.frm'文件
2. '.MYD'文件
3. '.MYI'文件
4. '.ibd'文件和'.ibdata'文件

其他配置文件
1. 系统配置文件(my.ini或者my.cnf) (关注关注)
2. pid,err文件
3. socket文件

1.2.1. 日志解释
1. 错误日志(Error Log)：在mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql数据库的数据目录中。错误日志文件通常的名称为hostname.err。其中， hostname表示服务器主机名
2. 二进制日志(Binary Log)：二进制日志，也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括每条语句所执行的时间和消耗的资源，以及相关的事物信息。

3. 事务日志(InnoDB redo Log & undo Log)：事务的数据记录日志文件
4. 慢查询日志(Slow Query Log)：顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slowquery。
5. 查询日志(Query Log)：记录select的日志文件

1.2.2. 二进制日志
默认情况下二进制日志功能是没有开启的，启动可以配置log-bin[=file_name]开启

作用：
1. 以二进制形式记录更改数据库的SQL语句(insert, update, delete, create， drop, alter等)。
2. 用于MySQL主从复制。
3. 增量数据备份及恢复
添加配置在my.cnf或者my.ini因为bug必须添加server-id数值随意指定
server-id=1
log_bin=mysql-bin
binlog_format=MIXED -- 二进制日志格式
启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。

命令
-- 查看所有二进制文件信息
show binary logs;
-- 查看最新二进制文件
show master status;
-- 刷新日志
flush logs;
-- 查看二进制日志信息 语法格式：
SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
-- show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。 help show binlog events;
--获取帮助信息
show binlog events\G;
show binlog events in 'mysql-bin.000014'\G;

数据恢复：官网二进制文件恢复数据 https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

2. 锁机制
1. MyISAM 和 Memory 存储引擎使用的是表级锁,BDB 引擎使用的是页级锁，也支持表级锁。由于 BDB 引擎基本已经成为历史，因此就不再介绍了。
2. InnoDB 存储引擎既支持行级锁，也支持表级锁，默认情况下使用行级锁。
3. 所谓表级锁，它直接锁住的是一个表，开销小，加锁快，不会出现死锁的情况，锁定粒度大，发生锁冲突的概率更高，并发度最低。
4. 所谓行级锁，它直接锁住的是一条记录，开销大，加锁慢，发生锁冲突的概率较低，并发度很高。
5. 所谓页级锁，它是锁住的一个页面，它的开销介于表级锁和行级锁中间，也可能会出现死锁，锁定粒度也介于表级锁和行级锁中间，并发度也介于表级锁和行级锁中间。
6. 行级锁更适合大量按照索引条件并发更新少量不同的数据，同时还有并发查询的应用

看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;

2.1. 行级锁
1. 行级锁本身与表级锁的实现差别就很大，而事务的引入也带来了很多新问题，尤其是事务的隔离性，与锁机制息息相关。
2. 对于事务的基本操作，对于不同隔离级别可能引发的问题，像脏读、不可重复读等问题。
3. 数据库实现事务隔离的方式，基本可以分为两种： (1)在操纵数据之前，先对其加锁，防止其他事务对数据进行修改。这就需要各个事务串行操作才可以实现。 (2)不加任何锁，通过生成一系列特定请求时间点的一致性数据快照，并通过这个快照来提供一致性读取
4. 上面的第二种方式就是数据多版本并发控制，也就是多版本数据库，一般简称为 MVCC 或者 MCC，它是 Multi Version Concurrency Control 的简写。
5. 数据库的事务隔离越严格，并发的副作用就越小，当然付出的代价也就越大，因为事务隔离机制实质上是使得事务在一定程度上”串行化”,这与并行是矛盾的

共享锁语句
select * from table_name lock in share mode;

排他锁语句
select * from table_name for update;

2.2. 死锁

2.2.1. 为什么会死锁
官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

2.3.2. 死锁如何处理死锁如何处理
等待，直到超时（innodb_lock_wait_timeout=50s）。
发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测
死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。
事务回滚
检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

2.3.3. 对于锁的建议

收集死锁信息：

利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
调试阶段开启 innodb_print_all_deadlocks，收集所有死锁日志。

减少死锁：

使用事务，不使用 lock tables 。
保证没有长事务。
操作完之后立即提交事务，特别是在交互式命令行中。
如果在用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，尝试降低隔离级别。
修改多个表或者多个行的时候，将修改的顺序保持一致。
创建索引，可以使创建的锁更少。
最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。
如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表

3. 事务

3.1. 事务的概念
事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永远更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复病史应用程序更加可靠。一个逻辑共奏单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性：
1. 原子性(A)
对于数据修改，要么全部都执行，要么全都不执行。
2. 隔离性(C)
在所有的操作没有执行完毕之前，其他会话不能够看到中间改变的过程
3. 一致性(I)
事务发生前和发生后，根据数据的规则，总额应该匹配
4. 持久性(D)
事务一旦被提交，其结果就是永久性的，系统崩溃也不会影响
3.2. 事务的执行流程
事务的特点：日志先行
事务注意点：事务不能自动回滚数据
事务的执行流程：

Innodb的一条事务日志共经历4个阶段：
1）创建阶段：事务创建一条日志；
2）日志刷盘：日志写入到磁盘上的日志文件； （ib_logfile 里 面 ）
3 ） 数 据 刷 盘 ： 日 志 对 应 的 脏 页 数 据 写 入 到 磁 盘 上 的 数 据 文 件 ；
4 ） 写 CKP ： 日 志 被 当 作 Checkpoint 写 入 日 志 文 件 ； （ ib_data里面）

4. 索引

4.0. 索引结构
平衡二叉树；

4.1. 索引类型
4.1.1. 主键索引和普通索引：
这是只类型方面的区别
innodb存储引擎 =》主键索引与普通区别就在于：主键索引找的时候数据在磁盘的存储位置，而普通索引查找的是主键索引的位置
myisam存储引擎 =》主键索引与普通索引查找都是数据在磁盘空间的存储位置
4.1.2. 单索引单索引与与联合索引：联合索引：
这是只定义方面的区别
单索引: 就是一个字段简历的索引
联合索引：就是多个字段建立的索引（实际工作推荐使用联合索引）
4.1.3. 覆盖索引覆盖索引与与回表回表
这是使用层面的介绍
覆盖索引：就是查询的sql所有的字段都是在索引上
回表：就是sql获取的字段在普通索引上有写没有，就需要去查询主键索引因此存在回表问题（需要关注，数据量查询 30% 以内）
4.2. 扩展
innodb的行锁实现原理：基于索引定位数据 -》锁住数据

5. 架构演变
5.0. 单体架构的问题
1. 数据库读写瓶颈问题；2. 数据备份的问题； 3. 容灾处理问题；4. 数据量剧增读写问题
5.1. 主从复制
主从MySQL好处：
1. 性能问题--不一定提高；
2. 数据冗余
**注意：**主从无法提高数据库的读写速度
MySOL支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器访问量比较大，可以通过复制数据，然后在从同各器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题。

5.2. 集群

MySQL集群实则是在主从的基础上进行扩展，如双主双从的架构，再利用Keepalived进行MySQL集群负载均衡与故障转移；

5.3. 分库分表

5.3.1. 分库分表的指标点分库分表的指标点
字段<200（一般会控制在20~30个左右）,表 < 100, 数据文件大小<2G,单表记录数<500w(目前实际上很多是1000w-2000w才分表)
其实如上的指标点均不是准确的判断标准，这还需要自己的项目根据自己的数据增长和系统的io瓶颈而来做评估
最为核心的因素是数据库瓶颈：IO瓶颈和CPU瓶颈
不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用
1. IO瓶颈
第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。
第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。
mysql io 定位工具：https://yq.aliyun.com/articles/603735?utm_content=m_1000003799
2. CPU瓶颈
第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。
第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。
5.3.2. 分库分表的类型：分库分表的类型：
水平分表：

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
规则：hash，range，取模，list
结果：

* 每个表的结构都一样；
* 每个表的数据都不一样，没有交集；
* 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：一次SQL查询优化原理分析

分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

垂直分表：

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
结果：
* 每个表的结构都不一样；
* 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
* 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机
读IO，产生IO瓶颈。
分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少
了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。
但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

2. 分库：

水平分库：

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
结果：
* 每个库的结构都一样；
* 每个库的数据都不一样，没有交集；
* 所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

垂直分库：

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
结果：
* 每个库的结构都不一样；
* 每个库的数据也不一样，没有交集；
* 所有库的并集是全量数据；
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可
以将相关的表拆到单独的库中，甚至可以服务化。

项目中的方式：

融合

6. 分库分表

以对订单表为例分表
1. 根据用户id查询 -》涉及分页
2. 根据商家-》日期排序查询
分表的原则：
1. 数据尽可能平均拆分
2. 尽可能的减少事务边界所谓的事务边界指单个SQL语句在后端数据库上同时执行的数量。因为事务边界越大，系统的锁冲突概率就越高，系统越难以扩展，整体性能越低。（边界是指一次性需要查询的数据库有最
大值）

3. 异构索引表尽量降低全表扫描频率这个是针对假如数据是按订单id进行分拆，当用户想查看自己的订单时，针对这种场景，常采用异构索引表来解决，即采用异步机制将原表内的每一次创建或更新，都换另一个维
度保存一份完整的数据表或索引表。本质上这是互联网公司很多时候采用的一个解决思路：拿空间换时间。
订单表的结构

首先关于订单表如何选择拆分？1. 根据订单id分，2. 根据user_id分
1. 如果是按照订单ID取模的方式，比如按64取模，则可以保证主订单数据以及相关的子订单、订单详情数据平均落人到后端的64个数据库中，原则上很好地满足了数据尽可能平均拆分的原则。
2. 通过采用买家用户ID哈希取模的方式，比如也是按64取模，技术上则也能保证订单数据拆分到后端的64个数据库中，但这里就会出现一一个业务场景中带来的一一个问题，就是如果有些卖家是交易量非常大的(这样
的群体不在少数)，那这些卖家产生的订单数据量(特别是订单详情表的数据量)会比其他卖家要多出不少，也就是会出现数据不平均的现象，最终导致这些卖家的订单数据所在的数据库会相对其他数据库提早进人到
数据归档(为了避免在线交易数据库的数据的增大带来数据库性能问题，淘宝将3个月内的订单数据保存进在线交易数据库中，超过3个月的订单会归档到后端专门的归档数据库)。
》如根据订单id分表
根据订单ID进行取模查询：但是查询的时候如果用户要查询所有的订单肯定是有问题的，再比如商家查询....肯定少不了全表的扫描；

这对于这个问题可以采用异构索引表的方式来解决；

所谓的异构索引表实际就是一张实体表，而其次关于商家我们也可以进行这样的思路建立一个索引表处理

posted on 2021-01-24 22:54 believe-CHEN 阅读(254) 评论(0) 收藏举报