MySQL的水平分表和垂直分表

水平分表和垂直分表 - SegmentFault 思否
水平分表：数据库的表的数据过多时，只用一张表会使得查询变慢，将数据拆分成多个相同表字段的数据表，数据分开存储，比如qq的用户表，分成qq0，qq1，qq2，qq3……，对于一个数据，通过取模来看这个数据会分配到哪个表里面，有点类似于分布式存储。
垂直分表：目的是尽量在一个16k的存储页中存储更多的数据，需要每个数据记录尽量小。将重要的字段（经常查询）放在一起，不重要的字段（存储较多的，不经常查询）放在一起，两者通过主键进行关联。

MySQL的三大日志

必须了解的mysql三大日志-binlog、redo log和undo log

binlog

  1. 什么是binlog：binlog中记录的是事务中存在的写操作，存在的原因就是为了方便数据恢复、主从复制
  2. binlog可以用于主从复制、数据恢复：对于主从复制，master将binlog发送给slave，slave根据binlog来同步复制数据。
  3. 只有在事务提交后才会写到内存中的binlog中，然后内存的binlog会根据预设的sync_binlog来确定刷盘时机

redo log

  1. 为什么要有redo log：一个问题是每次修改的记录如果都写入到磁盘的页中，效率低。有效的策略是把要修改的数据页记录下来，等积累到一定程度就对磁盘页进行刷盘。redo log记录的是哪个数据页的哪个记录被修改后更新值。
  2. 有内存中的redo log buffer和磁盘中的redo log file，大致写的过程是redo log buffer-> os buffer->通过调用fsync()写入到redo log file中。于是有了延时写，实时写实时刷、实时写延时刷
  3. redo log记录形式：
  4. redo log可以用于崩溃恢复：能解决崩溃恢复的原因是用磁盘中的LSN和内存中的LSN进行比对，磁盘中的LSN<内存中redo log buffer的LSN，则需要恢复checkpoint之后的数据。

redo log在崩溃恢复时恢复的是内存中的数据还是磁盘中的数据？

undo log

  1. 记录的是数据库操作的相反操作，比如insert操作就记录delete，update操作就记录相反的update操作。
  2. undo log可以实现事务的原子性，怎么实现的呢？就是通过记录操作的相反操作，一旦一个事务没有成功，回滚，就利用undo log来实现回滚操作。

image.png

SQL的事务日志是啥？

事务日志包括redo log 和 undo log，事务日志中的每个记录都是由一个日志序列号(LSN)唯一标识。

优化一条SQL的查询语句

执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
SQL优化(面试题) - 90后菜鸟- - 博客园
从索引上：

  1. 为经常查询的where、group by 字段建立索引，
  2. 尽量避免使用select * from，返回无用字段会降低查询效率，只返回需要用到的字段【可能需要的字段在联合索引中就能找到。不需要再进一步回表操作】
  3. or的使用注意：【若 or连接的是两个同字段的，则会走该字段的索引；若or连接的是两个不同字段的，但是这两个字段有联合索引，则会走联合索引；若or连接的是两个不同字段的，没有联合索引，则全表扫描】
  4. like时避免在开头采用模糊查询，而是确定开头位置，比如 where room like ‘%42%’会全表扫描，而 where room like ‘54%’则会走索引。
  5. 尽量避免在where部分的等式左侧进行表达式、函数操作，比如 where height/10=16，换成where height=10*16.

  6. in 或者not in 的使用注意：【MySql5.5版本中，优化器已经对in 和not in进行了优化，如果字段建立了索引，则走索引，没有建立索引，则仍需要全表扫描】

    MySQL的架构

    (98条消息) 【搞定MySQL数据库】第3篇：基础架构：一条SQL查询语句是如何执行的？_pcwl1206的博客-CSDN博客
    image.png

  7. 优化器：在表里有多个索引时，决定用哪个索引，或者在一个语句有多表关联(join)时，去决定各个表的连接顺序

    两阶段提交

    mysql之两阶段提交 - 郭慕荣 - 博客园

  8. 两阶段提交的目的就是为了保证redo log和binlog所记录的数据修改的一致性。

  9. 问题所在：binlog日志和redo log日志在写的时候会有一个先后顺序，不可能完全同步，一旦奔溃发生在两个日志之间，就会导致binlog和redo log所记录的事务不一致，造成主数据库和从数据库的数据不一致
  10. 解决办法：一个事务提交的时候，先写redo log，并且将该记录变成 prepare状态，然后再写binlog，binlog写入成功，再把redo log的prepare状态变成commit状态。通过标志位的形式来同步redo log和binlog的修改记录。

image.png

数据库的三范式

  1. 二范式：不能存在部分函数依赖，部分函数依赖就是说，在表(学号、课程、成绩)中，码”学号”没法完全决定非主属性”成绩”。成绩必须要学号+课程两个决定。
  2. 三范式：不能存在传递函数依赖，传递函数依赖就是表里面存在一个非主属性完全依赖于另一个非主属性。比如”学院Id 学院名称 学院主任“，学院主任完全依赖于学院名称。

  3. 三范式的缺点：表分的太细，会造成查询的时候麻烦，要连表查询。

    explain

  4. 分析查询语句的工具，可以查看SQL语句在优化器中的执行计划。如多表连接的顺序，采用哪个索引进行查询等等。

  5. 语法是explain 分析语句

    慢查询日志

  6. 开启的话，可以记录在执行SQL语句中，执行时间超过预定的阈值的那些SQL语句

  7. 用mysqldumpslow工具进行分析

四种特性ACID

  1. 一致性：数据从一个合法性状态变换成另一个合法性状态，合法性状态是指这个事务结束后得到数据要满足现实中的约束（对数据中数据间的状态约束）比如转账时，要保证金额>=0
  2. 隔离性：等价于多线程对共享资源的操作。一个事务的执行不能被其他事务干扰，事务之间相互隔离，使用的数据也相互隔离。
  3. 持久性：一个事务一旦commit，那么它对于数据库中相关记录的修改就是永久的。不能说因为宕机而使得修改丢失。这个持久性可以通过事务日志(redo log)和binlog来实现
  4. 原子性：事务中的一系列操作要么全部成功，要么全部失败

一个例子说明事务的四种特性：张三给李四转账100，对应张三的账号-100，李四的账户+100，

  1. 原子性是指这两个修改语句要么同时成功，要么同时失败；
  2. 一致性是指在转完账后，仍要满足账户的合法性，不能说张三的账户出现负数；
  3. 持久性是指一旦这笔转账成功，那在数据库中的修改就是永久的，不能因为数据库突然宕机而使得这笔转账消失。
  4. 隔离性是指在操作转账的过程中，其他关于张三或者李四的账户的操作的事务都不能干扰该事务

事务间存在的四种冲突

快速理解脏读、不可重复读、幻读和MVCC - 云+社区 - 腾讯云
脏写（写写冲突）：两个事务都写，会出现什么样的问题？事务A先写未提交，随后事务B写并提交，A出现回滚，则B所写的会被回滚。在事务的操作过程中出现问题。
脏读（读写冲突）：事务A修改一个数据的值，但是还未提交，事务B读到了这个修改后的值，并结束事务，结果A又回滚了。（事务读到了另一个事务修改后的值，并且另一个事务出现回滚，读出的数据是脏数据）
不可重复读（读写读冲突）：
image.png
幻读(读增读冲突)：突然增加一行。

SQL的四种隔离级别

隔离是指不同事务间的关系，是为了解决既要使得数据库支持并发性，又要保证事务间尽可能地不冲突。隔离级别是指针对上述的四个问题，所提出的各种级别。

读未提交（read_uncommitted）：只能解决脏写问题，其他三个问题都没法解决。
读已提交（read_committed）：能解决脏读问题，解决的办法就是读到的是写事务已经提交的数据
可重复读（repeatable_read）：可以解决脏读、不可重复读的问题（解决不可重复读的方式就是让其可重复读）
串行化（serializable）：直接串行化

MySQL默认采用的隔离级别

Repeatable_read可重复读的隔离级别，不允许出现脏读、不可重复读，但是允许出现幻读情况。

MVCC-多版本并发控制(Multiversion Concurrency Control)

是解决读写问题用更好的方式处理读-写冲突，无锁并发技术，是应对RC和RR隔离级别
MVCC的整体过程是什么样的？

  1. MVCC的实现原理是依赖于记录中的三个部分：隐藏字段、undolog、read view
  2. 隐藏字段：修改事务ID，用来记录最新一次修改的事务ID。回滚指针-记录这条记录的上一个版本，行ID-记录隐藏的主键
  3. undolog：历史记录存在undolog中
  4. read view：和select查询语句伴随生成，保留在生成时当前的活跃事务，活跃事务的最小值，和下一个要活跃的事务。

针对读语句，保证其读到的是已提交的数据，如何保证?
能够读取数据的情况：当前事务查询的数据的修改者ID要小于read view中的活跃ID集的最小值 || 等于该事务的ID||在活跃集ID范围内，且不等于活跃集ID中的任何一个。目的是保证事务已经提交。【比如去账房查交易或者修改记录，我去的时候，会先看看哪些事务正在进行，我要读的那个记录，要么是我自己修改的，要么是别人修改已经提交的】。

为什么要用到undo log?

undo log里面记录了

MVCC和隔离级别的关系：

当前读：对于读已提交级别的事务，查询事务中每个select中都会生成一个read view，保证读到的一定是已经提交过的值。（我每次看一下都会更新自己的查帐）
快照读：对于可重复读级别的事务，则是查询事务开始时的第一个select才会生成一个read view，保证读到的一定是事务刚开始时的数据库快照。（比如我只看我进账房查帐的那一瞬间的帐）
MVCC的快照读是能解决幻读问题，当前读不能，当前读只能通过行锁实现

表锁和行锁、间隙锁

天呐，怎么这么多锁，行锁、表锁、间隙锁，崩溃了_哔哩哔哩_bilibili

  1. 行锁：A事务在执行过程中修改表中的某一行的值，这个事务还没提交，B事务对于该行只能读到原始数据。如果A和B事务都要修改某一行的数据，则先修改而未提交的事务会对该行进行加锁。
  2. 表锁：A事务中修改的update…..where a=1 or b=1，当索引失效，升级为全表查找，没法定位到某一行时，就会把整个表锁住，其他事务只有等到A事务提交完后才能操作表

  3. 间隙锁：A事务中修改的是update… where a>1 and a<8 ，如果数据表中只有1,3,5,7。另一个事务是新增一个a=4，也不能顺利执行。

    表锁、行锁、间隙锁和四种隔离级别的关系

    间隙锁能解决幻读的问题，间隙锁会锁住中间部分，防止两次读的表不一致。

    悲观锁和乐观锁

  4. 悲观锁：全程锁，在查看旧牌子前就已经被锁住，也即操作系统悲观的认为一旦让两个线程同时访问资源，就会造成异常。

  5. 乐观锁：通过CAS实现，Compare and Swap，是操作系统底层实现的一种原子操作，线程在访问一个资源时，率先对牌子进行修改，通过old value和new value来进行记录比较，其他线程则会自旋等待一段时间