Mysql

一、Mysql的逻辑架构

Mysql知识点整理_数据

  • 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
  • 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
  • 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

相关问题：

1.一条SQL查询语句是如何执行的？

Mysql知识点整理_数据_02

（1）连接器：与mysql服务器建立连接，获取权限等操作。

（2）查询缓存：连接建立完成后，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。

ps:大多数情况下，查询往往弊大于利。只要对一个表有更新，这个表上所有的查询缓存就都会被清空，因此我们可能十分费劲的将结果保存起来，还没来得及使用就被一个更新给清空了。对于更新压力大的数据库来说，命中缓存的概率非常低。除非你的业务是一张静态表，很长时间才会更新一次，比如系统配置表。

Mysql8.0版本直接将查询缓存的整快功能删除掉了。

（3）分析器：如果没有命中查询缓存，就会对SQL语句进行解析，分析器首先会做词法分析，Mysql会识别出里面的字符串分别是什么，代表什么。然后再进行语法分析，根据词法分析的结果和语法规则，判断这个SQL语句是否满足Mysql语法。

（4）优化器：在开始执行sql之前，还需要经过优化器的处理。优化器会生成mysql认为最优的执行计划，在表里有多个索引时会决定使用哪个索引；在多表关联查询时决定各个表的连接顺序。

（5）执行器：执行器先检查权限，看看你是否有权限执行，如果没有就返回错误信息。执行器会向搜索引擎要数据，返回的结果如果设置了缓存还要将数据放置到缓存中

二、Mysql的存储引擎

1、InnoDB存储引擎（常见）

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况

2、MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)

4.InnoDB和MyISAM的区别：

（1）事务：InnoDB支持事务,而MyISAM不支持

（2）外键：InnoDB支持外键,而MyISAM不支持

（3）锁：MyISAM只支持表锁，即使操作一条数据记录也会锁住整个表，不适合高并发的操作；

InnoDB支持行锁，操作一条数据记录时只锁住一行，不会对其他行产生影响

（4）缓存：MyISAM只缓存索引，不缓存真实数据；

InnoDB不仅缓存索引还缓存真实数据，对内存的要求比较高，内存的大小对性能有决定性影响

（5）索引的实现不同：MyISAM和InnoDB都支持B+树索引，但是实现方式有所不同。

MyISAM: MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的。因此，MyISAM索引文件和数据文件是分离的**，**索引文件仅保存数据记录的地址。

1）主索引和辅助索引：在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB: InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

1）主索引：在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构**，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）辅助索引：InnoDB的所有辅助索引都引用主键作为data域。

聚簇索引和非聚簇索引：

聚簇索引:索引的叶结点就是数据结点

非聚簇索引：索引的叶结点数据域存放着数据记录的地址或者主键的值。

三、索引

1.索引简介：

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以简单理解为：一种排好序的快速查找数据结构。

2.索引的优缺点：

（1）优点：

  • 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
    (2)缺点：
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

3.索引的结构：

（1）B树索引:

B树是一种多路平衡查找树

（2）B+树索引：B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息；
  2. 所有叶子节点之间都有一个链指针；
  3. 数据记录都存放在叶子节点中

（3）Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

  • 本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。
  • 哈希索引也没办法利用索引完成排序
  • 不支持最左匹配原则
  • 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
  • 不支持范围查询

补充：

  • 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
  • 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
  • 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。
  • B+树：在B树的基础上，将非叶节点改造为不存储数据纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ObujD8O8-1592885407023)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592881941335.png)]
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2cCMLBJ7-1592885407026)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592880276740.png)]

4.索引的分类

（1）逻辑角度：

普通索引（单值索引）：每个索引只包含单个列，一个表可以有多个单列索引

复合索引（联合，多列索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀原则

唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

主键索引：主键索引时一种特殊的唯一索引。但是不允许为空值，可以被作为外键。执行的优先级最高，一个表只能有一个主键索引。

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（2）数据结构角度：

B+树索引

Hash索引

Full-Text

全文索引

R-Tree索引

（3）从物理存储角度

  • 聚集索引（clustered index）
  • 非聚集索引（non-clustered index），也叫辅助索引（secondary index）
    聚集索引和非聚集索引都是B+树结构

补充：了解下前缀索引：

前缀索引

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

我们是否可以建立一个区分度很高的前缀索引，达到优化和节约空间的目的呢？

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是www.aobing@mogu.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

5.建立索引的情况：

（1）哪些情况适合建立索引？

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段建立索引，即where后面的字段
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段，排序字段建立索引去访问将大大提高排序速度
  • 查询中分组或者统计的字段
  • 组合索引性价比更高（比单值索引），高并发条件下偏向组合索引

（2）哪些情况不适合建立索引？

  • 表记录太少（建立索引时需要耗费物理空间的，如果表记录太少，建立索引得不偿失）
  • 频繁增删改的字段和字段（因为每次更新不单单是更新了记录还会更新索引文件）
  • where条件里用不到的字段或者说过滤性差的字段
  • 数据重复且分布平均的表字段**，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为它建立索引就没太大的实际效果。（性别）

四、锁

1.概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则

2.锁的分类：

（1）从对数据操作的类型分类：

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

（2）从对数据操作的粒度分类：

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

（3）乐观锁和悲观锁：

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制或者时间戳实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，**悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。**比如 for update

3.MyISAM表锁：

MyISAM 的表锁有两种模式：

(1) 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；只有当读锁释放后,才会执行其它进程的写操作

(2) 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；只有当写锁释放后,才会执行其它进程的读写操作

简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞,

4.InnoDB锁：

（1）InnoDB行锁：

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

（2）InnoDB表锁：

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

（3）InnoDB行锁的实现方式：

  • 记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
SELECT * FROM table WHERE id = 1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
  • 间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。
    InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
    对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
    间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

  • 临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)
    Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
    对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

5.死锁：

死锁产生：

  • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

  • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

  • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
  • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
  • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
  • 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
  • 改变事务隔离级别

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

五、事务

1.事务的基本要素

⑴ 原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

2.隔离级别

  • READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别

读数据一致性

脏读

不可重复读

幻读

读未提交（read-uncommitted）

最低级别，只能保证不读取物理上损坏的数据

是

是

是

读已提交（read-committed）

语句级

否

是

是

可重复读（repeatable-read）

事务级

否

否

是

串行化（serializable）

最高级别，事务级

否

否

否

3.并发下的问题

  • 更新丢失（Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
  • 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  • 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  • 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

幻读和不可重复读的区别：

  • 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
  • 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

并发事务处理带来的问题的解决办法：

  • “更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
  • “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：
  • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

RR级别下，Mysql通过MVCC和间隙锁来解决幻读。

官方文档：在 RR 级别下，如果查询条件能使用上唯一索引，或者是一个唯一的查询条件，那么仅加行锁，如果是一个范围查询，那么就会给这个范围加上 gap 锁或者 next-key锁 (行锁+gap锁)。**

  • 其实 就是 通过 快照读（伪MVCC模式） + undo log + read view
  • TRX_ID(事务 ID), ROLL_PTR(回滚指针)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FGDA5Iw7-1592885407030)(E:/%E7%A8%8B%E5%BA%8F%E5%91%98%E4%BD%9C%E4%B8%9A/JAVA%E8%AF%BE%E7%A8%8B/%E5%B0%8F%E7%BA%B8%E4%BA%BA2019%E7%A7%8B%E6%8B%9B%E5%87%86%E5%A4%87%E8%B5%84%E6%96%99/%E5%B0%8F%E7%BA%B8%E4%BA%BA2019%E7%A7%8B%E6%8B%9B%E5%87%86%E5%A4%87/MySQL/images/112.png)]

  • next-key锁（行锁 加上 gap锁）
  • gap 出现的场景
  • 不走索引（会上锁所有的gap）
  • where 命中不全（5,7,9 只命中了 5,7） 那么会在 5 - 9 之家加上 gap锁

4.MVCC

MVCC 多版本并发控制

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下MVCC如何工作：

  • SELECT
    InnoDB会根据以下两个条件检查每行记录：
    只有符合上述两个条件的才会被查询出来
  • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
  • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除
  • INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识
  • UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

5.事务日志

事务日志

InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。

事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。

InnoDB 假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB 用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。

InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

  • 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
  • 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
  • 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
  • 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

6.如何实现事务的ACID

事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现ACID特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

❝

事务是如何通过日志来实现的，说得越深入越好。

事务日志包括：重做日志redo和回滚日志undo

  • redo log（重做日志） 实现持久化和原子性
    在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
    在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。
  • undo log（回滚日志） 实现一致性
    undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
    Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

❝

又引出个问题：你知道MySQL 有多少种日志吗？

  • 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
  • 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志：记录对数据库执行更改的所有操作。
  • 中继日志：中继日志也是二进制日志，用来给slave 库恢复
  • 事务日志：重做日志redo和回滚日志undo

❝

分布式事务相关问题，可能还会问到 2PC、3PC，，，

MySQL对分布式事务的支持

分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下 InnoDB 对分布式事务的支持。

MySQL 从 5.0.3 InnoDB 存储引擎开始支持XA协议的分布式事务。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。

Mysql知识点整理_数据库_03

如图，MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

  • 应用程序：定义了事务的边界，指定需要做哪些事务；
  • 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
  • 事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

  • 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
  • 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

六、数据库优化

1.性能分析：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RY3avtJT-1592885407050)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592795065556.png)]

（1）explain关键字：

a.是什么？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kIAt8LVA-1592885407055)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592796227576.png)]

b.作用：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xeuUcZe3-1592885407058)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592796256548.png)]

c.执行计划包含信息：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KOUi8mz7-1592885407061)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592797064595.png)]

d.关注哪些字段：

  • id: select 查询的序列号，包含一组数字，表示查询中执行的select字句或者操作表的顺序。
    三种情况：
  1. id 相同：执行顺序由上至下
  2. id 不同：值越大越先执行
  3. id 相同 也 不同：值越大越先执行，相同的执行顺序由上至下
  • select_type:查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OPEw9tOe-1592885407065)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592798101562.png)]
  • table: 显示这行数据时属于哪张表的
  • partitions: 代表分区中的命中情况，非分区表为NULL
  • type: 显示此次查询用了哪种类型
    从最好到最差依次为：
    system>const>eq_ref>ref>range>index>all
    一般来说最好达到 range 最好是 ref！
    type详解：
  1. system：表只有一行记录，这是const类型的特例，平时不会出现，可以忽略不计
  2. const：常量查询，表示通过索引一次就找到了
  3. eq_ref：唯一性索引扫描，常见于主键或者唯一索引
  4. ref：非唯一性索引扫描，返回匹配某个值的所有行（本质上也是索引查询），他可能会查出多个行与之匹配；
  5. range：范围查询
  6. index：使用了索引但是没用索引进行过滤，一般是使用了覆盖索引或者利用索引进行了排序分组的情况。
  7. all：全表扫描
  • possible_keys :显示可能应用在这张表中的索引，一个或多个查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  • key :实际使用到的索引，如果没有到索引，其值为NULL。如果查询使用了覆盖索引，则key中的索引和select查询的字段重叠。
  • key_len :表示索引中使用到的字节数，可以通过该列得到使用索引列的长度。key_len可以帮助我们检查是否充分利用上了索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JxMxF7nW-1592885407068)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592871398569.png)]

  • ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aOQyKQPM-1592885407070)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592871838707.png)]

  • rows ：需要读取的行数。（根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

**

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o6Isw8SS-1592885407072)(E:/%E7%A8%8B%E5%BA%8F%E5%91%98%E4%BD%9C%E4%B8%9A/JAVA%E8%AF%BE%E7%A8%8B/%E5%B0%8F%E7%BA%B8%E4%BA%BA2019%E7%A7%8B%E6%8B%9B%E5%87%86%E5%A4%87%E8%B5%84%E6%96%99/%E5%B0%8F%E7%BA%B8%E4%BA%BA2019%E7%A7%8B%E6%8B%9B%E5%87%86%E5%A4%87/MySQL/images/32.png)]

  • Extra（包含不适合在其他列中显示但十分重要的额外信息）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jYQxOnUx-1592885407075)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592872052219.png)]

（2）慢查询日志：

a.是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中**响应时间超过阀值**的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10S以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

b.如何使用？

查看开启状态

SHOW VARIABLES LIKE '%slow_query_log%'

开启慢查询日志

  • 临时配置：
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;

也可set文件位置，系统会默认给一个缺省文件host_name-slow.log

使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。

  • 永久配置
    修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入两个配置参数
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3

注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time=2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。

可以用 select sleep(4) 验证是否成功开启。

在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具mysqldumpslow。

通过 mysqldumpslow --help 查看操作帮助信息

  • 得到返回记录集最多的10个SQL
    mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
  • 得到访问次数最多的10个SQL
    mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
  • 得到按照时间排序的前10条里面含有左连接的查询语句
    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
  • 也可以和管道配合使用
    mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志

（3）Show Profile 分析查询：

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量,Show Profile`命令查看执行状态。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rsFr0kjM-1592885407076)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592875226747.png)]

2.SQL调优：

（1）建立合适的索引：

（2）关联查询优化：

  • 保证被驱动表的join字段已经被索引
  • left join 时，选择小表作为驱动表，大表作为被驱动表。
  • inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
  • 子查询尽量不要放在被驱动表，有可能使用不到索引。
  • 能直接多表关联的尽量多表关联，少用子查询

（3）子查询优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gIoImE33-1592885407079)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592699770007.png)]

  • 尽量不要使用 not in 和not exists ,用left join on XXX where xx is NULL来替代
  • 永远小表驱动大表！！！
    select * from emp e where e.deptno in (select deptno from dept d);（in 里面是小表）
    select * from emp e where exists (select 1 from dept d where e.deptno = d.deptno);
    （exists 里面是大表）

（4）排序分组优化：

1）order by 优化：

  • order by 默认是ASC升序排列，要避免order by排序的字段升序排列和降序排列混合，要么全升序要么全降序，否则会导致索引失效。
  • ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
    order by 满足以下两种情况会使用useing index：
==1、order by 语句使用索引最左前列==
==2、使用 where 字句 和 order by 字句条件列组合满足索引最左前列。where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
  • 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
    order by 双路算法：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。
    读取两次 排序一次 读取数据一次
    order by 单路算法：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。
    是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
    存在问题：有可能单路排序算法一次拿不出数据，那么就还比双路排序更消耗IO，效率更慢！
    在sort_buffer中，单路排序要比双路排序占很多空间，因为单路排序把所有的字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能读取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再次排序…从而多次I/O。偷鸡不成蚀把米。
    **一次拿不完，需要多拿几次。。。**
    比如：内存就是2M，一次查1000条数据刚好，也就是最大1000条数据，但是一次要查5000条，那么不够了，照这样需要查5次刚好，如果把2M改为10M，那么就刚好了

优化策略：

①order by 时select * 不要用，这是一个大忌，要写出只需要query 的字段即可。

a：当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法—单路排序，否则用老算法—多路排序。假设只需要查10个字段，但是SELECT *会查80个字段，那么就容易把sort_buffer缓冲区用满。
b：两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size大小。

②尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率。当然要根据系统能力去提高，因为这个参数是针对每个进程的。

③尝试提高 max_length_for_sort_data

提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

2）group by 优化：

group by实质是先排序后进行分组，遵照索引建的最佳左前缀

where高于having，能写在where限定的条件就不要去having限定了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rkLuhFfR-1592885407083)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592752594072.png)]

（5）使用覆盖索引：

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

七、主从复制

1.主从复制原理：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gs9UAXEl-1592885407086)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592875366271.png)]

2.基本原则:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DOmJ34d2-1592885407092)(C:\Users\悟空\AppData\Roaming\Typora\typora-user-images\1592875332875.png)]

3.最大问题：延时

八、分区分库分表

1.MySQL分区

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

能干嘛

  • 逻辑数据分割
  • 提高单一的写和读应用速度
  • 提高分区范围读查询的速度
  • 分割数据能够有多个不同的物理文件路径
  • 高效的保存历史数据

怎么玩

首先查看当前数据库是否支持分区

  • MySQL5.6以及之前版本：
SHOW VARIABLES LIKE '%partition%';
  • MySQL5.6：
show plugins;

分区类型及操作

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。
    按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。
    range 来分，好处在于说，扩容的时候很简单。
  • LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
    hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

  • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
  • 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
  • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

❝

随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

❝

说说分库与分表的设计

2.MySQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

  • 垂直拆分
    垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
  • 水平拆分(数据分片)
    单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。
    水平分割的几种方法：
  • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
  • 还可根据时间放入不同的表，比如：article_201601，article_201602。
  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
  • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

Mysql知识点整理_数据库_04

3.MySQL分库

❝

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

❝

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

  • 减少增量数据写入时的锁对查询的影响
  • 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

❝

配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道

其他问题：

1.三个范式

  • 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
  • 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
  • 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y

2.百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

  1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
  2. 然后删除其中无用数据（此过程需要不到两分钟）
  3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
  4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

参考：https://mp.weixin.qq.com/s?__biz=MzU2NDg0OTgyMA==&mid=2247487317&idx=2&sn=b5f85fd93dbde2381e552496d22afc91&chksm=fc45f2a6cb327bb07a044ad0ca604842c6f2950b5a4d3957424a7933cdf465422c50ca6cc15d&mpshare=1&srcid=&sharer_sharetime=1592802227021&sharer_shareid=7b5238f8850a1b0a3b2ded6213a4410e&from=singlemessage&scene=1&subscene=10000&clicktime=1592806746&enterid=1592806746&ascene=1&devicetype=android-29&version=27000f3f&nettype=WIFI&abtest_cookie=AAACAA%3D%3D&lang=zh_CN&exportkey=A8yXwZfjiOVk2QUvkgve56w%3D&pass_ticket=nHh2RIvQHp7J%2FEcQwXhs7BQ%2FxqbRwiJbdm5QDjJO31c%3D&wx_header=1

https://mp.weixin.qq.com/s/e0CqJG2-PCDgKLjQfh02tw

https://mp.weixin.qq.com/s?__biz=MzI4Njg5MDA5NA==&mid=2247484721&idx=1&sn=410dea1863ba823bec802769e1e6fe8a&chksm=ebd74430dca0cd265a9a91dcb2059e368f43a25f3de578c9dbb105e1fba0947e1fd0b9c2f4ef&token=1676899695&lang=zh_CN###rd