MySQL概述（补充）

FINYIE

于 2022-06-13 05:06:58 发布

阅读量115

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/FINYIE/article/details/125252592

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

1. 原理和SQL

什么是事务？事务的基本特性ACID?

事务指的是满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback进行回滚。
事务的基本特性ACID:

A 原子性(atomicity) 指的是一个事务中的操作要么全部成功，要么全部失败。
C 一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另一个一致性的状态。比如A转账给B100块，假设中间SQL执行过程中系统奔溃A也不会损失100块，因为事务没有提交，修改就不会保存到数据库。
I 隔离性(isolation) 指的是一个事务的修改在最终提交前，对其他事务是不可见的。
D 持久性(durability) 指的是一旦事务提交，所做的修改就会永远保存到数据库中。

数据库中的并发一致性问题？

在并发条件下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

丢失修改
T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T2的修改
读脏数据
T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据。
不可重复读
T2 读取一个数据，T1对该数据做了修改，如果T2再次读取这个数据，此时读取的结果和第一次去读的结果不同
幻影读
T2 读取整行数据如 sum() avg() count()等，T1增加或删除某一行数据，此时T2每次的结果会不同
脏读是指读到未提交的数据。不可重复读和幻读都是读的过程中数据前后不一致，前者侧重修改，后者侧重增删。幻读是一种不可重复读的特殊情况，只是解决方案不同：不可重复读只要加行级锁，幻读需要加间隙锁

事务的隔离级别：

未提交读(read uncommitted) 事务中的修改，即使没有提交，对其他事务也是可见的
提交读(read committed) 一个事务只能读取已经提交的事务所做的修改。一个事务所做的修改在提交之前对其他事务不可见。
可重复读(repeatable read) 保证在同一个事务中多次读取同样的数据的结果是一样的。
可串行化(serializable) 强制事务串行执行

隔离级别	脏读	不可重复读	幻读
读未提交	y	y	y
提交读	n	y	y
可重复读	n	n	y
可串行化	n	n	n

ACID靠什么保证？

A 原子性由undo log 日志保证，记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C 一致性事务
I 隔离性由MVCC gap lock 保证
D 持久性由内存+redo log保证。mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

Buffer Pool, Redo Log Buffer 和 undo log， redo log，bin log概念以及关系？

Buffer Pool 是MySQL的一个非常重要的组件，因为针对数据库的增删该操作都是在Buffer Pool中完成的
undo log 记录的是数据操作前的样子
redo log 记录的是数据被操作后的样子 (redo log 是Innodb存储引擎特有)
bin log 记录的是整个操作记录(这个对于主从复制具有非常重要的意义)

从准备更新一条数据到事务的提交的流程描述？

myslq 更新一条数据到事务提交的流程

首先执行器根据MySQL的执行计划来查询数据，先是从缓存池中查询数据，如果没有就回去数据库中查询，如果查询到了就将其放到缓存池中
在数据被缓存到缓存池的同时，会写入undo log日志文件
更新的动作实在BufferPool中完成的，同时会将更新后的数据添加到redo log buffer 中
完成后就可以提交事务，在提交的同时会做以下三件事：
1）将redo log buffer 中的数据刷入到redo log文件中
2）将本次操作记录写入到bin log文件中
3）将bin log文件名字和更新内容在bin log中的为止记录到redo log中，同时在redo log最后添加commit标记

2. MySQL

myisam 和 innodb的区别？

myisam 是5.1版本之前的默认引擎，支持全文检索，压缩，空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据分开存储。
innodb 基于B+ Tree索引建立，和myisam相反它支持事务，外键，并且通过mvcc来支持高并发，索引和数据存储在一起

MySQL 的索引有哪些？

索引在存储引擎实现，不在服务器层实现，不同的存储引擎有不同的索引类型和实现。

B+ Tree索引
- 大多数MySQL存储引擎的默认索引类型
哈希索引
- 哈希索引能以O(1)时间进行查找，但失去了有序性
- innodb存储引擎有个特殊的功能：自适应哈希索引，当某个索引值被使用的非常频繁，会在B+Tree索引之上再创建一个哈希索引，这样就让B+Tree索引具有哈希索引的一些优点，比如快速的哈希查找
全文索引
- MySIAM存储引擎支持全文索引，用于查找文本中的关键字，而不知直接比较是否相等。查找条件使用match against 而不是普通的where
- 全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射
- InnoDB存储引擎在MySQL 5.6.4版本中也开始支持全文索引
空间数据索引
- Mysiam 存储引擎支持空间数据索引R-Tree，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效的使用任意维度来进行组合查询

什么是B+树？为什么B+树成为主要的SQL数据库的索引实现？

B+ Tree：基于B Tree和叶子节点顺序访问指针进行实现，具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能。在B+Tree中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别是keyi和keyi+1，则该指针指向节点的所有key大于等于keyi且小于等于keyi+1
为什么是B+Tree?
- 为了减少磁盘读取次数，决定了树的高度不能高，所以必须是B-Tree
- 以页为单位读取使得一次I/O就能完全载入一个节点，且相邻的节点也能够被预先载入；所以数据放在叶子节点，本质是一个Page页
- 为了支持范围查询以及关联关系，页中数据需要有序，且页的尾部指向下个页的头部。
B+树索引可分为聚簇索引和非聚簇索引？
1）主索引是聚簇索引(也称聚集索引 clustered index)
2）辅助索引(有时也称非聚簇索引或二级索引，secondary index， non-clustered index)
主键索引的叶子节点保存真正的数据，辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询name = C 的数据，搜索过程：a)先在辅助索引中通过C查询最后找到主键id=9；b)在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取真正的数据。所以通过辅助索引进行检索，需要检索两次索引。
之所以这样设计，一个原因：如果在主键索引和辅助索引的叶子节点中都存放数据行指针，一旦数据发生迁移，则需要去重新组织维护所有的索引。

什么是覆盖索引和回表？

  覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，就称之为覆盖索引，不需要回表查询。
  判断一个查询是否是覆盖索引，只需要explain sql 语句看Extra的结果是否是Using index即可。

什么是MVCC?实现的原理？

什么是MVCC?
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般子啊数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
在mysql的Innodb引擎中就是指：在提交读rc和可重复读rr两种隔离级别下的事务对于select操作会访问版本链中的记录的过程。
这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。select可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。
MySQL的InnoDB引擎实现MVCC的3个基础点
1. 隐式字段
DB_ROW_ID 是数据库默认改行记录生成的唯一隐式主键；
DB_TRX_ID当前操作该记录的事务ID
DB_ROOL_PTR回滚指针，用于配合undo日志，指向上一个旧版本
2. undo log
不同的事务或者相同的事务对同一记录的修改，会导致该记录的undo log 成为一条记录版本线性表，即链表，undo log的链首就是最新的旧纪录，链尾就是最早的旧纪录。
3. ReadView
已提交读和可重复读的区别：生成ReadView的策略不同。
ReadView中使用一个列表存储系统中当前活跃着的读写事务，就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前的事务可见。
已提交读隔离级别下的事务在每次查询开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

MySQL 锁的类型？

两个维度：

共享锁s和排他锁x
- 读锁是共享的，可以通过lock in share mode 实现，这时候只能读不能写。
- 写锁是排他的，会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁
表锁和行锁
- 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如修改alter修改表结构的时候会锁表
- 行锁分为乐观锁和悲观锁
  - 悲观锁可以通过for update 实现
  - 乐观锁通过版本号实现

两个维度结合：

共享锁：shared locks
- 读锁：多个事务对于同一数据可以共享访问，不能操作修改
- 使用方法
  - 加锁 select * from table where id = 1 lock in share mode
  - 释锁 commit / rollback
排他锁：exclusive locks
- 写锁：事务获取了一个数据的x锁，其他事务就不能再获取改行的读锁和写锁(s锁 x锁) 只有获取了该排他锁的事务是可以对数据进行读取和修改
- 使用方法：
  - delete/update/insert 加锁
  - select * from table where … for update 加锁
  - commit/rollback 释锁
    在rr的隔离级别下，for update 、in share mode 加的是间隙锁gaplock/临键锁next key lock，可以防止幻读
意向共享锁(IS)
- 一个数据行加共享锁前必须先取得改变的is锁，意向共享锁之间是可以相互兼容的。意向排它锁(IX) 一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的。意向锁(IS、IX)是InnoDB引擎操作数据之前自动加的，不需要用户干预; 意义：当事务操作需要锁表时，只需判断意向锁是否存在，存在时则可快速返回该表不能启用表锁
- 意向共享锁：Intention Shared Locks
- 意向排他锁：Intention Exclusive Locks

数据量多大？如何分库分表？

分库分表分为垂直和水平两种方式，一般来说拆分的顺序是先垂直后水平

垂直分库
基于现在的微服务拆分，都属于垂直分库
垂直分表
通常按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
水平分表
首先根据业务场景来决定使用什么字段作为分表字段(sharding_key)
比如我们现在日订单1000万，我们大部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询支持到最近3个月的订单，超过3个月的做归档处理，那么3个月的数据量就是9亿，可以分1024张表，那么每张表的数据大概就在100万左右。比如用户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的表上了。
分表后的ID的唯一性？
设定步长：比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。
分布式ID: 自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
分表后非sharding_key的查询如何处理？
可以做一个mapping表，比如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过user_id去查询。
打宽表，一般而言，商户端对数据实时性要求并不是很高，比如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其他如es提供查询服务。
数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的。

MySQL 主从复制？

主要涉及三个线程：binlog 线程 I/O线程 SQL线程

binlog线程：负责将主服务器上的数据更改写入二进制文件中
I/O线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志中
SQL线程：负责读取中继日志并重放其中的SQL语句
全同步复制：
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回客户端，但是很显然这个方式性能会受到严重影响。
半同步复制：
和全同步不同的是，半同步复制的逻辑：从库写入日志成功后返回ack确认给主库，主库收到至少一个从库的确认就认为写操作完成。