MySQL

1.什么是MySQL

MySQL 是⼀种关系型数据库，在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且⽅便扩展。阿⾥巴巴数据库系统也大量⽤到了 MySQL，因此它的稳定性是有保障的。MySQL是开放源代码的， 因此任何⼈都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进⾏修 改。MySQL的默认端⼝号是3306.

2.存储引擎

存储引擎： 数据文件在物理磁盘上不同的组织形式，主要包括InnoDB、MyISAM、MEMORY。

查看MySQL提供的所有存储引擎

mysql> show engines;

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

MySQL 当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB ⽀持事务.

InnoDB ：MySQL的默认存储引擎

索引结构为b+树、自适应hash（需要时自动优化为hash索引）。

索引与数据在一个文件

支持事务、外键、行级锁

MyISAM ：

索引结构为b+树

索引和数据在不同的文件

不支持事务、行级锁，崩溃后⽆法安全恢复

MEMORY ：
索引结构为哈希

MyISAM和InnoDB两者的对比：

1. 是否支持行级锁: MyISAM 只有表级锁(table-level locking)，⽽InnoDB 支持行级锁(row level locking)和表级锁，默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities) 的事务安全(transaction-safe (ACID compliant))型表。
3. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
4. 是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC比单纯的加锁更⾼效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤ 乐观 (optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。
5. InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚簇索引，而 MyISAM 是非聚簇索引。

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最⾼，并发度最低， MyISAM和 InnoDB引擎都⽀持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的⼀种锁，只针对当前操作的⾏进⾏加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁

3.索引

索引是对数据库表中一列或多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.

B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.

索引的几种类型或分类？

从物理结构上可以分为聚集索引和非聚集索引两类：

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；
• 非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

Hash 和 B+ 树索引的区别？

Hash
1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。

3）Hash 任何时候都避免不了回表查询数据.

4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+ Tree

1）B+ 树本质是一棵查找树，自然支持范围查询和排序。

2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

索引结构为什么选择b+树（其他索引结构的特性）

• hash索引

hash算法容易出现冲突

hashmap不能访问文件，只能从内存取数据，所以利用hash存储需要将数据全都放到内存，耗费内存空间

hash查找是根据key值定位数据位置，只适用于等值查询，不适合范围查询

• 二叉搜索树bst

按顺序插入会退化成链表

• 二叉平衡树avl

为解决二叉搜索树的问题，可通过旋转，使最高子树与最低子树高度不超过1。
查询快了，但插入慢，需要频繁旋转

• 红黑树

最高子树最高是最低子树的两倍，根结点是黑色，插入结点是红色，插入时保证每个分支黑色结点个树相同，同时不能有连续两个红色结点，否则会通过旋转和变色保证满足条件。
红黑树是一种弱平衡二叉树，旋转次数小于平衡二叉树，但高度一般高于平衡二叉树，还是会出现深度很高的情况，影响io效率。

• B树

二叉树或红黑树一个节点只放一个值，树的深度过深会影响磁盘io速度。

而b树是一种平衡多叉树，每个节点以16k的大小存放多个值，降低了树的高度，减少了io次数。

B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

• B+树

B+树相比B树，非叶子结点只存储索引，不存储数据，使每个磁盘块可以存储更多索引，比b树存储更多数据，树的高度更低，所有数据都有序存在叶子结点.

B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，更适用于范围查找。B 树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

B+ 树减少了 IO 次数。
由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

B+ 树查询效率更稳定
由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。

B+ 树更加适合范围查找
B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。
————————————————
ref:https://blog.csdn.net/adminpd/article/details/122910606

索引何时会失效：

最左匹配原则：
顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。

再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。
最左匹配的原理

上图可以看出 a 是有顺序的（1、1、2、2、3、3），而 b 的值是没有顺序的（1、2、1、4、1、2）。所以 b = 2 这种查询条件无法利用索引。

同时我们还可以发现在 a 值相等的情况下（a = 1），b 又是顺序排列的，所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

ref:https://blog.csdn.net/xiaoqiangyonghu/article/details/107950623

根据索引的匹配列前缀规则，用**%a**便会失效

当索引中有多个列，即联合索引，此时遇到范围查找(>、<、between、like)剩下的索引就会失效。例如（a，b）联合索引，条件为a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

根据最左匹配原则，当索引中有多个列时（即联合索引），存储数据时将按照定义索引时列的顺序排序。例如ALTER TABLE tb_student ADD INDEX test_index(name,job);就先按照name排序，当name相等时再按照job排序。如果是where name = 1 and job = 2 或 where job = 1 and name = 2【查询引擎会自动优化为联合索引的顺序】 或 where name = 1都可以使用索引，而 where job = 1 不会使用索引。

用or时，如果组合索引包含全部查询的列会用索引，否则索引失效

4.事务

事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。

事物的四大特性(ACID）：

• 原⼦性（Atomicity）： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部 完成，要么完全不起作⽤；
• ⼀致性（Consistency）： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是 相同的；
• 隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务 之间数据库是独⽴的；
• 持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据 库发⽣故障也不应该对其有任何影响。

并发事务带来哪些问题？

在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀ 数据进⾏操作）。并发虽然是必须的，但可能会导致以下的问题：

• 脏读（Dirty read）: 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交 到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没 有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是 不正确的。
• 丢失修改（Lost to modify）: 指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据， 那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修 改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取 A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在⼀个事务内多次读同⼀数据。在这个事务还没有结束 时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修 改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不 ⼀样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接 着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了 ⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

事务隔离级别：

SQL 标准定义了四个隔离级别：

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

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）

5.MySQL与Oracle的区别

MySQL免费，Oracle付费

MySQL自动提交事务，Oracle需要手动commit提价

MySQL对于字符类型支持char和varchar，Oracle支持CHAR，NCHAR，VARCHAR2和NVARCHAR2

MySQL事务默认隔离级别REPEATABLE READ，Oracle默认隔离级别READ COMMITTED，且都支持SERIALIZABLE

• READ COMMITTED(提交读)，大多数数据库系统的默认隔离级别（MySQL不是）。一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。也叫不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。

• REPEATABLE READ(可重复读)，（MySQL的默认事务隔离级别），解决了脏读问题。保证了在同一个事务中多次读取同样记录的结果是一致的。

• SERIALIZABLE(可串行化)，最高的隔离级别。它通过强制事务串行执行，避免了幻读问题（幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行【与不可重复读的区别就在于换行重点为新增或删除，不可重复读重点为修改】）。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据一致且可以接受没有并发的情况下使用该级别。

6.MySQL主备同步

MySQL主备同步就是指一台服务器充当数据库主服务器，另一台或多态服务器充当从数据库服务器。主数据库对数据修改的操作记录到bin log日志，从服务器通过binlog日志执行更新，保证主备同步。

同步方式：
1.基于语句的复制 ：主库把sql语句写入到bin log中，完成复制

2.基于行数据的复制：主库把每一行数据变化的信息作为事件，写入到bin log，完成复制

3.混合复制：上面两个结合体，默认用语句复制，出问题时候自动切换成行数据复制

线程如何工作：
1.Master 数据库只要发生变化，立马记录到Binary log 日志文件中

2.Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志

3.Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。

4.Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据