MySQL三大日志、 MVCC、锁、分库分表、主从复制、优化

本文主要是介绍MySQL三大日志、 MVCC、锁、分库分表、主从复制、优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL三大日志（binlog、redolog、undolog）

1、redo log(重做日志) 物理日志

（1）InnoDB存储引擎独有的，使MySQL崩溃后能恢复数据，保证数据的持久性和完整性

（2）一般情况下事务提交就会进行刷盘操作。

=刷盘策略：innodb_flush_log_at_trx_commit

0：每次事务提交时不进行刷盘操作

1：每次事务提交时都会进行刷盘操作（默认）

2：每次事务提交时都只会把redo log buffer内容写入文件系统缓存page cache

（3）记录某个数据页上的修改操作

（4）事务执行过程中 不断写入

2、bin log(归档日志) 逻辑日志（二进制文件）

（1）记录语句的原始逻辑，所有涉及更新数据的逻辑操作，顺序写

（2）属于MySQL Server层，保证数据库集群架构的数据一致性

（3）作用于 数据库数据备份、主备、主主、主从、集群

（4）事务提交时才写入

3、undo log(回滚日志)

（1）异常发生时，对已经执行的操作进行回滚，保证事务的原子性

（2）回滚时，恢复机制是通过undo log实现的

（3）所有事务的操作会先记录到undo log，再执行相关操作

（4）undo log先于疏忽持久化到磁盘上，数据库宕机后再启动，数据库能通过查询回滚日志来回滚之前未完成的事务。

4、事务是基于redo log（持久化、原子性）和undo log（原子性、一致性）实现的

MVCC 多版本控制

1、一致性非锁定读

（1）实现：加版本号或时间戳，查询时比对版本号

（2）读取快照数据，读取记录时其它事务加锁排他锁时，也是可以直接读取的

2、锁定读

（1）语句

select ...... lock in share mode

对数据加读锁，其它事务也可以加读锁（共享锁），不能加写锁（排他锁）

select ...... for update\insert\delete

对数据加写锁

（2）锁定读，读取的数据是最新版本的

3、多版本控制是对非锁定读的实现 MVCC

（1）通过保存数据在某个时间点的快照来实现的。

（2）实现原理：

innodb每行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本（快照）存放在undo log中

（3）好处：读不加锁、读写不冲突，保证了事务的隔离性

（4）MVCC: 只在read committed 和repeatable read 中

MySQL锁

1、锁粒度：

表级锁（MyISAM\InnoDB）、行级锁（InnoDB）

2、锁的类别：

=共享锁（读锁）：用户读取数据时，对数据加锁共享锁，可以同时添加多个共享锁

=排他锁（写锁）：用户写入数据时，对数据加锁排他锁，只可加一个，与其它锁互斥

3、乐观锁、悲观锁

数据库并发控制时，确保多个事务同时存取数据库中同一数据时不被破坏事务的隔离性、一致性和数据库的统一性。（乐观锁、悲观锁）

（1）乐观锁（多读场景，省去锁的开销）

=1、假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

= 2、修改数据时，通过添加version的方式进行事务锁定，使用版本号机制或CAS（compare and swap）算法实现

（2）悲观锁（多写场景，避免发生冲突）

=1、假设会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

=2、在查询数据时把事务锁起来，直到提交事务，使用数据库中的锁机制。

4、隔离级别和锁的关系

（1）read uncommitted

读未提交，读取数据不需要加共享锁，这样不会跟被修改的数据上的排他锁冲突

（2）read committed

读已提交，读操作要加共享锁，在语句执行完成后立即释放共享锁

（3）repeatable read

可重复读，读操作要加共享锁，在事务提交前不释放共享锁，必须得到事务执行完成后才能释放共享锁

（4）serializable

可序列化，锁定整个范围，一直持有锁，直到事务完成才释放。

分库分表

1、分表

（1）分表：把一个表的数据放到多个表中，访问时就访问一个表。

例：按照用户id分表，一个用户的数据放在一个表，操作这个用户就操作这个表就ok。

（2）什么情况下分表？

单表数据量很大，几千万的数据，会极大影响sql执行性能，就需要进行分表。

控制每个表的数据量在可控的范围内 例如：200万数据量一个表

2、分库

（1）分库：把一个库的数据拆分到多个库中，访问时就访问一个库。

（2）什么情况下分库？

高并发量访问数据库时，拆分为多个库，来支撑高并发量。

3、分库分表前后的区别

（1）分库分表前：

单机部署，不能支撑高并发；数据库单机磁盘容量几乎满；单表数据量大，sql执行效率慢

（2）分库分表后：

多机部署，能支撑高并发；拆分多个库，数据库磁盘容量使用率降低；单表数据量减少，sql执行效率提升。

4、如何拆分（水平拆分和垂直拆分）

（1）水平拆分：把一个表的数据拆分到多个库的多个表里。

==表结构一致，数据不一致。每个库的表的结构一致

==所有库的表的数据等于原来的全部数据

==意义：将数据均匀放到多个库中，用多个库来支撑高并发，同时，使用多个库的存储容量来实现扩容。

（2）垂直拆分：把一个有很多字段的表拆分成多个表，或放到多个库中。

==表的结构不一致，数据页不一致

==将较少的、访问频率高的字段放到一个表里，将较多的、访问频率低的字段放到一个表里，能够提高性能。

MySQL读写分离、主从复制

1、MySQL主从复制

=(1)主从复制：使得数据可以从一个数据库服务器复制到其它服务器上，在复制数据时，一个服务器充当主服务器（master），其它服务器充当从服务器（slave）

=(2)复制异步进行，主从服务器断续连接的

=(3)通过配置文件，直到主从服务器和复制的数据库

=(4)目的：

===通过增加从服务器，提高数据库的性能，在主服务器执行写入和更新功能，从服务器提供读功能

===提高数据安全，数据复制到从服务器，从服务器可以终止复制进程，并和主服务器断开连接，达到备份而不破坏主服务器数据

===主服务器生成实时数据，从服务器进行分析，可以提高主服务器的性能

===数据备份，可以实现多地备份，保证数据安全

2、读写分离

=（1）基于主从复制的基础上

=（2）主库进行写入和更新，从库进行读取

3、主从复制的原理

3个线程间的关联 ： binlog线程、io线程、sql执行线程

2个文件：binlog 二进制文件 relay log中继文件

（1）主：

==binlog线程，记录下所有改变了数据库数据的语句，放进主服务器master上的binlog中。

==操作：master在每个事务完成前，将操作记录写入binlog文件中

（2）从：

==io线程，使用start slave后，负责从主服务器master上（io线程）拉取binlog二进制文件内容，（io线程）放进relay log（中继文件）

==操作：slave开启io线程，在master开启连接，进行binlog dump process，读取binlog文件，睡眠等待master新事务，再进行读取，同时，写入自身的relay log文件中

（3）从：

==sql线程，执行relay log中的语句，sql执行

==操作：从服务器内部，开启SQL线程读取relay log文件，并顺序执行该日志中的sql，使其保持跟主数据库数据一致。

4、主从复制延时问题解决

（1）半同步复制，解决主库数据丢失问题

=semi-sync复制，指主库写入binlog后，会强制此时立即同步数据到从库，从库写入自身relay log后，返回ack确认码，主库接收到至少一个ack便认为复制完成。

（2）并行复制，解决主从复制的延时问题

=指从库开启多个线程，并行读取relay log中不同库的日志，并行重放不同库的日志。

5、MySQL优化

（1）explain查看执行计划，查看索引的相关信息，通过索引进行优化

（2）大表数据查询，如何优化

==优化sql语句、添加索引

==添加缓存组件 例如redis

==主从复制，进行读写分离

==分库分表（垂直拆分和水平拆分）

（3）超大分页优化

==缓存优化，提前把预测高频率查到的数据放入缓存中

==子查询进行优化

（4）慢查询慢SQL的优化

==分析语句，看是否查询了多余的行

==explain分析执行计划，查看索引信息和判断是否命中索引

==是否数据量大，可以进行分库分表

（5）优化查询过程中的数据访问

==使用limit，解决查询不需要的数据

==指定列名，多表关联返回指定列

==避免使用select * , 因为select *会返回全部列，影响效率

==进行缓存，解决重复查询相同数据

==改变数据库表的结构，优化范式

（6）关联查询的优化

==确定on 或者using子句中是否有索引

==确保group by 和 order by只有一个表的列，这样才可能使用索引

（7）数据库cpu飙升500%,如何处理

步骤：==（1）使用top命令查看是否mysql的占用导致，如果不是找出高占用进程进行处理；

==（2）如果是，show processlist命令，查看session信息，查看高消耗sql，进行explain查看执行计划。

==（3）kill 这些线程，对SQL进行调整。

这篇关于MySQL三大日志、 MVCC、锁、分库分表、主从复制、优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！