MYSQL

MYSQL

mysql优化的过程就是遵循一些原则，让mysql优化器按照预想的方式运行，提高执行效率

MYSQL架构

mysql可以分为应用层、逻辑层、数据库引擎层、物理层
应用层：主要负责和客户端进行链接，响应客户端请求、返回数据等；
逻辑层：包括缓存、解析器、优化器等
数据库引擎层
物理层：负责文件存储、日志等

---

一、SQL语句的执行过程？

（1）客户端首先通过连接器进行身份验证和权限校验；
（2）执行语句时先查询缓存，若未命中缓存则通过解析器分析语句，检查语法规则等
（4）接着通过优化器对语法进行优化
（5）最后通过执行器调用存储引擎接口，返回数据。

二、自增主键

（1）使用自增主键在新插入行时顺序的排在原数据的下一行，mysql寻址和定位很快，避免了重新计算新行的位置做出的资源消耗；减少了数据页分裂和碎片的产生，提升了数据页面的填充率。

（2）如果是随机的键值，会造成数据页分裂重新计算移动数据，产生数据碎片；

2.索引

索引是对一列值或者多列值进行排序的一种结构，建立索引的初衷是为了提高查询的速度
覆盖索引：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需要的数据不必进行回表查询

3.索引的底层数据结构

B+树 和 Hash数据结构的优缺点：hash底层是hash 表实现，用在等值查询，可以快速定位，当出现大量hash冲突的时候查询效率下降，无法进行范围查询，无法用于分组查询、模糊查询，联合索引的最左匹配原则等；

使用B+树不用B树主要原因？
对B+树而言非叶子结点只存key，查询更稳定，增大了广度，一个节点对应磁盘空间中的内存页，相比于B树可以存储更多的索引节点因此B+树的出度更大，层高较低查询次数少；B+树的叶子结点存储相邻，可以很方便的进行范围查询；

4.索引的使用场景

（1）对于小表数据量较少，没有必要建立索引
（2）对与海量数据，从业务角度出发进行分库分表
（3）如果表的增删改等操作比较多而查询比较少，可以根据具体业务来看维护索引的成本
（4）不在where条件出现的字段没有必要加索引
（5）多个字段考虑联合索引
（6）字段值无重复考虑唯一索引

4.索引失效的场景

（1）条件中有or的；例如 select * from tb where a=1 or b = 3 ；
（2）在索引上进行计算会导致索引失效 select * from tb where a + 1 = 2；
（3）在索引上进行数据类型的隐式转换，会导致索引失效。例如字符串一定要加引号；
select * from tb where a = 1 不会用到索引，如果写成select * from tb where a = ‘1’ 可以用到索引。
（4） 在索引中使用函数会导致索引失效
（5）在使用like 查询时以‘%’开头会导致索引失效
（6）在索引上使用！，<> 会导致索引失效
（7）在索引上使用is null 、 is not null 判断时会导致索引失效；

5.最左匹配原则

从左往右匹配，例如我门建立联合索引（a，b，c） ；
select * from tb where a = 1 and b = 1 and c = 1 ； （a，b，c）
select * from tb where a = 1 and b => 1 and c = 1 ； （a，b）

6.mysql的事务

事务：事务由一系列的操作组成要么全部成功要么全部失败
事务的ACID 特性
A 原子性：一些列事务要么全部成功要么失败
C 一致性：事务的结果总是从一种状态变为另一种状态；（事务修改前后的数据总体保证一致性）
I隔离性：事务的结果只有被提交了才可以看到
D 持久性：事务一旦提交。对数据的修改是永久的

数据库的并发问题：
脏读：读到未提交的事务
不可重复读： 一个事务两次读取的内容不同
幻读：事务A按照一定条件读取数据，期间事务B插入了相同搜索条件的数据，事务A再次读取发现事务B新插入的数据，这种情况称为幻读（侧重新增或删除）

7.数据库的隔离级别

读未提交：直接读取数据不能解决任何并发问题
读已提交：解决了脏读问题
可重复读：解决了脏读、不可重复读问题
串行化：使用锁，读加共享锁，写加排他锁；串行化执行

8.mysql的主从复制

mysql提供主从复制功能，可以方便的实现数据的多处自动备份，不仅能增加数据库的安全性，还能进行读写分离，提升数据库的负载；

主从复制的流程：
(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启

binlog dump线程,该线程会去读取bin-log日志

(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。

(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。

9.使用Innodb的情况下，一条更新语句是怎么执行的？

update T set c = c +1 where id = 2

1.执行器首先查询到id=2的这一行。如果这行数据本身就在内存中，那么直接返回给执行器；否则从磁盘加载到内存，然后再返回。

2.执行器拿到数据执行c+1的操作并调用更新接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log中。此时redo处于preppare状态。

4.执行器生成这个操作的binlog 并把binlog写入磁盘。

5.执行器调用引擎的事务提交接口，将redolog改为commit状态。

10 、redo log 和 bin log

redlog是InnoDB引擎特有的。只记录该引擎表中的修改记录。binlog时mysql server层的会记录所有对数据库的修改。
redolog是物理日志，记录的是在具体某个数据页上做了哪些修改；
binlog是逻辑日志，记录的是这个语句的原始逻辑。
redolog更加关注事务的恢复。在重启mysql服务的时候，可以根据redo log进行重做。从而使事务具有持久性。binlog关注的是容灾备份的恢复。

11 、undo log 和 redo log

undo log 记录了事务执行之前的初始数据，用于事务回滚，关注的是事务的原子性；redo log 记录的是事务的重做日志，关注的是事务的持久性。

12、MVCC Muti-version-concurrency control（多版本并发控制）

MVCC 是用在RC（读已提交） RR（可重复读）的隔离级别实现方案。
ReadView （快照读）时才会使用MVCC （select 查询）；当前读会使用行锁或者间隙锁（update、delete执行之前读取数据为当前读）。ReadView在读已提交级别下，会在每次查询中都生成一个ReadView。而可重复读只在事务开始时生成一个ReadView，以后
每次查询都用这个Read View 以此来实现不同隔离级别。

undo log 会记录事务当前老版本数据，然后行记录中的回滚指针会指向老版本的位置，形成一条版本链。

13、数据库中的锁

按照锁的属性分类：共享锁和排他锁。
按照锁的粒度分类：表锁、行锁、记录锁、间隙锁
按照锁的状态分类：意向共享锁、意向排他锁、死锁

14、InnoDB中的引擎的行锁模式如何实现？

– A用户对id=1的记录进行加锁
select * from user where id=1 for update;
行锁的实现需要注意：

行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
两个事务不能锁同一个索引。
insert，delete，update在事务中都会自动默认加上排它锁。

间隙锁

间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读。
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

15、设计一个动态可扩所容的分库分表方案，应该要如何做

（1） 对大多数业务来说32库*32 表，对于大多数公司的业务场景基本上够用；
（2）路由规则设定：id mod 32 得到具体分库，id mod 32 得到具体分表；
（3）扩容的时候申请增加更多的数据库服务器。呈倍数扩容4-》8 -〉16
（4）业务逻辑修改配置，路由规则不变

16、分库分表以后如何设计分布式ID

（1）UUID ：结合机器网卡、当地时间以及一个随机数；缺点：无序、冗余
（2）使用redis生成 redis本身提供一些原子命令 incr 、incrby 这样的原子命令所以生成的ID是唯一有序的。但是增加了引入新组建维护成本。
（3）雪花算法 ：snowflake算法64bit 全局唯一ID 生成策略：
符号位 由于long类型在java中是带符号的，最高位位符号位。系统中使用的ID为正所以为0；
41位时间戳 ms：通过当前时间-起始时间戳，这里的起始时间是ID生成器开始使用的时间戳。
10位数据机器位：包括5位数据标识位和5位机器标识位；也就是说分布式系统中最多可以部署1024个节点。超过这个数量生成的ID就可能会有冲突
12位毫秒内的序列：支持每个节点每个毫秒最多生成4094个ID；
优点：按照时间有序，一般不会造成ID碰撞

17、如果表中有近千万的数据如何优化

分库分表 ，既然考虑分库分表就要考虑分布式事务以及跨节点join等问题

跨节点join

在拆分之前，系统中很多列表和详情页所需的数据是可以通过sql join来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库join的。那该怎么办呢？有以下几种解决方案：
（1）全局表

所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库join查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

（2）系统层组装
在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。不同模块的组件或者服务可以是通过id集合查询数据库或者缓存。

（3）ElasticSearch检索
将分库分表所有数据全量冗余到es中，将那些复杂的查询交给es处理。

2.分布式事务

大多数场景下我们的应用都只要操作单一的数据库，这种情况下的事务称为本地事务（Local Transaction）。本地事务的ACID特性是数据库直接提供支持。而分布式事务包括跨库事务以及分库分表事务。

CAP

C:Consistency，一致性。在分布式系统中的所有数据备份，在同一时刻具有同样的值，所有节点在同一时刻读取的数据都是最新的数据副本。
A:Availability，可用性，好的响应性能。完全的可用性指的是在任何故障模型下，服务都会在有限的时间内处理完成并进行响应。
P: Partition tolerance，分区容忍性。尽管网络上有部分消息丢失，但系统仍然可继续工作。

BASE

BASE理论是指，Basically Available（基本可用）、Soft-state（ 软状态/柔性事务）、Eventual Consistency（最终一致性）。是基于CAP定理演化而来，是对CAP中一致性和可用性权衡的结果。核心思想：即使无法做到强一致性，但每个业务根据自身的特点，采用适当的方式来使系统达到最终一致性。
1、基本可用 BA：（Basically Available ）： 指分布式系统在出现故障的时候，允许损失部分可用性，保证核心可用。但不等价于不可用。比如：搜索引擎0.5秒返回查询结果，但由于故障，2秒响应查询结果；网页访问过大时，部分用户提供降级服务等。简单来说就是基本可用。 2、软状态 S：（ Soft State）： 软状态是指允许系统存在中间状态，并且该中间状态不会影响系统整体可用性。即允许系统在不同节点间副本同步的时候存在延时。简单来说就是状态可以在一段时间内不同步。 3、最终一致性 E：（Eventually Consistent）： 系统中的所有数据副本经过一定时间后，最终能够达到一致的状态，不需要实时保证系统数据的强一致性。最终一致性是弱一致性的一种特殊情况。

刚柔事务

何谓刚柔事务？刚性事务它的事务是原子的，要么都成功要么都失败，也就是需要保障ACID理论，而柔性事务只需要保障数据最终一致即可，需要遵循BASE理论。
刚性事务满足ACID理论
柔性事务满足BASE理论（基本可用，最终一致）柔性事务解决方案：两阶段、补偿型、异步确保型、最大不理通知型。
金融类项目对柔性事务的使用场景比较多比如：支付宝、微信支付、银联支付等。

常见事务的解决方案类型

分布式事务几乎都是柔性事务，常见的有2PC/3PC、TCC（Try- confirm-Cancel）、MQ最终一致性解决方案。可以结合具体业务背景选择合适的解决方案。
2PC、3PC、TCC数据强一致性高，而MQ是最终数据一致性。

2PC和3PC的架构类似：都是协调者加上N个分布式节点。2PC：分为两个阶段准备阶段（prepare）和提交阶段（commit）当所有分布式阶段反馈prepare完成后，协调者发送commit各个分布式节点可以执行。但是2PC有明显的缺陷就是协调者单点故障。因此3PC加入了pre-commit阶段并引入了参与者和协调者超时机制避免了参与者无法与协调者通讯时出现无法释放资源问题。事务参与者迟迟未收到commit请求就会自动进行本地commit。

TCC（Try-Commit-Cancel）又称补偿事务。其核心思想是针对每个操作都要注册一个与其对应的确认和补偿机制。
Try阶段主要是对业务系统资源准备以及系统检测；confirm阶段是确认执行业务操作；cancel阶段取消执行业务操作。TCC的不足之处在于对业务的入侵性很高而且每个业务逻辑的分支都需要实现try、confirm、canel需要考虑系统故障、网络状态等不同失败原因实现不同的回滚策略。为了满足一致性的需求confirm和cancel接口必须实现幂等。

MQ 分布式事务
引入消息中间件，如果数据一致性要求不是太高，但是要求数据的最终一致性。事务的关键点就是本地事务的执行以及发送MQ消息。消费者系统订阅消息更新数据。

18、主从同步延迟的原因 （慢查询或锁表）

一个服务器开放N个链接给客户端，大量的并发更新操作，但是从服务器中读取binlog 线程仅有一个，当某个sql在从服务器上执行的时间稍长或者由于某个sql要进行锁表就会导致主服务器上大量sql积压，未被同步到从服务器中。这就导致了主从不一致，也就是主从延迟。

解决方案： 主库负责更新操作、sql查询优化、业务层面
数据库连接数调整、主库参数syncbinlog = 1 ，innodbflushlogattrxcommit=1