最全MySql知识整理一

短命女 2022-11-16 13:44 238阅读 0赞

# MySQL 整理 #

## 数据库概念 ##

#### ACID ####

*  ACID是事物的四个特性。分别是 ***原⼦性***  
    (Atomicity)、***⼀致性***(\*Consistency)、***隔离性***  
    (Isolation)、***持久性***(Durability) 。
 *  **原⼦性**是指事物是⼀个不可分割的⼯作单位，事  
    物中的操作要么都发⽣，要么都不发⽣。最经典  
    的就是转账案例，我们把转⼊和转出当做⼀个事  
    物的话，就需要在SQL中显式指定开启事务。
 *  **⼀致性**是说数据库事务不能破坏关系数据的完整  
    性以及业务逻辑上的⼀致性 。我们可以从数据库  
    层⾯和业务层⾯两⽅⾯来保证，数据库层⾯我们  
    可以设置触发器，外键，表，⾏约束等来保证，  
    业务层⾯就是我们Java⼯程师的⼯作
 *  **隔离性**指的是多个事务并发访问时，事务之间是  
    隔离的，⼀个事务不应该影响其它事务运⾏效  
    果。 多个事务并发访问时，事务之间是隔离的，  
    ⼀个事务不应该影响其它事务运⾏效果。 这个点  
    ⼜引申出了下⾯两道题，以及后边的加锁和阻塞
 *  **持久性**意味着即使出现了任何事故⽐如断电等，  
    事务⼀旦提交，则持久化保存在数据库中，不会被回滚

#### 脏读，不可重复读和幻读 ####

*  **脏读：** 意味着⼀个事务读取了另⼀个事务未提交  
    的数据，⽽这个数据是有可能回滚的。即这个事  
    物读取的数据是不正确的
 *  **不可重复读**： 在数据库访问中，⼀个事务范围内  
    两个相同的查询却返回了不同数据。这是由于查  
    询时系统中其他事务修改的提交⽽引起的。即这  
    个事物在读的过程中被修改了
 *  **幻读**：当⼀个事物对整个table进⾏修改之后，第  
    ⼆个事物向表中插⼊了⼀⾏数据，此时第⼀个事  
    物发现了新插⼊的没有修改的数据⾏，好像发⽣  
    了幻觉⼀样

#### 数据库的隔离级别 ####

*  读未提交RU

1.  ⼀个事务还没提交时，它做的变更就能被别的  
    事务看到
2.  会出现幻读，不可重复读，脏读
3.  更新数据时加上⾏级共享锁，事物结束即释放

*  读已提交RC
    
    1.  ⼀个事务提交之后，它做的变更才会被其他事  
        务看到
    2.  会出现幻读，不可重复读，不会出现脏读
    3.  写数据加⾏级排他锁，这样写过程是⽆法读取  
        的，直到事务处理完毕才释放排他锁，给读的数据加⾏级共享锁，这样读的时候也是⽆法写的，  
        但是⼀旦读完该⾏就释放共享锁
    4.  MySQL会在SQL语句开始执⾏时创建⼀个视图
 *  可重复读RR
    
    1.  ⼀个事务执⾏过程中看到的数据，总是跟这个  
        事务在启动时看到的数据是⼀致的
    2.  会出现幻读，不会出现不可重复读、脏读
    3.  给写的数据假行级排他锁，事物结束释放，给读的数据加行级共享锁，事物结束后释放
    4.  MySQL会在事物开始时创建⼀个⼀致性视图(接  
        下⾯的MVCC)，事物结束时销毁
 *  可串⾏化S
    
     *  当出现读写锁冲突的时候，后访问的事务必须  
        等前⼀个事务执⾏完成，才能继续执⾏
     *  不会出现幻读，不可重复读，脏读
     *  事务读数据则加表级共享锁，事务写数据则加  
        表级排他锁
     *  不区分快照度与当前读

其中，Oracle和SQLServer都是读已提交，但MySQL默认的隔离级别是可重复读 ，这是⼀个MySQL5.0之前的上古遗留版本问题。当时的binlog只有STATEMENT格式，⽤RC会出现bug。

#### 三⼤范式 ####

*  第⼀范式： 所有字段值都是不可分解的原⼦值 。  
    例如有⼀个列是电话号码⼀个⼈可能有⼀个办公  
    电话⼀个移动电话。第⼀范式就需要拆开成两个  
    属性
 *  第⼆范式：⾮主属性完全函数依赖于候选键。如  
    PersonID，ProductID，ProductName，  
    PersonName可以看到，OrderID和ProductID是  
    联合主键，但是ProductName是依赖于  
    ProductID的，只依赖了部分主键，没有依赖全部  
    主键。需要拆分成三个表： PersonID,  
    PersonName , ProductID, ProductName  
    和 PersonID, ProductID
 *  第三范式： 每⼀列数据都和主键直接相关，⽽不  
    能间接相关，如OrderID，ProductID，ProductName，  
    OrderID是主键，但是ProductID依赖了OrderID，  
    ⽽ProductName依赖了ProductID，等于说是间  
    接依赖了OrderID，所以需要拆分为两个表：OrderID, ProductID 和 ProductID,ProductName
    
     *  **范式的优点**：因为相对来说有较少的重复数据，  
        范式化的更新操作要⽐反范式快。同时范式化需  
        要更少的distinct和order by
     *  **范式化缺点**：通常需要关联，不仅代价昂贵，也  
        可能会使的⼀些索引⽆效
 *  **常⽤的反范式⽅法：**
    
     *  复制：在两个表中根据实际业务情况存储部分  
        相同的字段列，即有利于查询，也不会把表搞的  
        太⼤
     *  缓存：对于需要多次join查询的表，可以在⼀个  
        表中加⼊⼀个缓存列，⽤来缓存所join表的部分  
        常⽤数据，如count等，我们需要实时更新该缓  
        存

#### 内连接和外连接 ####

内连接也叫⾃然连接，只有两个表相匹配的⾏才能  
在结果集中出现。返回的结果集选取两个表中所匹  
配的数据，舍弃不匹配的数据

select fieldlist from table1 [inner] join
    table2 on table1.column = table2.colum

*  内连接保证两个表中的所有⾏都满⾜条件，⽽外连  
    接则不然，外连接不仅仅包含符合连接条件的⾏，  
    ⽽且还包括左表（左外连接），右表（右外连  
    接），或者两个边表（全外连接）中的所有数据⾏

select fieldlist from table1 left/ right outer
    join table2 on table1.column =
    table2.column

### MySQL索引 ###

索引是⼀种数据结构，⽤于帮助我们在⼤量数据中  
快速定位到我们想要查找的数据。可以加快查的速  
度，但是会增加容量，降低增，删，改的速度

#### MySQL的索引类型，特点 ####

常⻅的MySQL索引结构有B-树索引，B+树索引，  
Hash索引和全⽂索引

*  B-Tree索引
    
     *  因为存储引擎不⽤进⾏全表扫描来获取数据，  
        直接从索引的根节点开始搜索，从⽽能加快访问  
        数据的速度
     *  B-Tree对索引是顺序组织存储的，很适合查找  
        范围数据
     *  适⽤于全键值、键值范围或者键前缀查找（根  
        据最左前缀查找
     *  限制：对于联合索引来说，如果不是从最左列  
        开始查找，则⽆法使⽤索引；不能跳过索引中的
 *  B+Tree索引
    
     *  是B-Tree索引的变种，现在主流的存储引擎都  
        不⽤单纯的B-Tree，⽽是其变种B+Tree或者T-Tree等等
     *  和B-Tree最主要的区别就是B+Tree的内节点不  
        存储data，只存储key，叶⼦节点不存储指针
 *  Hash索引
    
     *  基于Hash表实现，只有Memory存储引擎显式  
        ⽀持哈希索引
     *  适合等值查询，如 = 、 in() 、 <=> ，不⽀  
        持范围查询
     *  因为不是按照索引值顺序存储的，就不能像  
        B+Tree索引⼀样利⽤索引完成排序
     *  Hash索引在查询等值时⾮常快
     *  因为Hash索引始终索引的所有列的全部内容，  
        所以不⽀持部分索引列的匹配查找
     *  如果有⼤量重复键值得情况下，哈希索引的效  
        率会很低，因为存在哈希碰撞问题
     *  程序员可以在B+Tree索引的基础上创建⾃适应  
        Hash索引
 *  全文索引
    
     *  MyISAM和InnoDB都⽀持全⽂索引
     *  有三种模式：⾃然语⾔模式，布尔模式和查询  
        扩展模式
 *  R-Tree索引
    
     *  MyISAM⽀持R-Tree索引，这个和全⽂索引基  
        本不问

#### B+树索引和hash索引的区别 ####

*  B+树索引适合返回查找，⽽hash索引适合等值查  
    询
 *  hash索引⽆法利⽤索引完成排序，但是B+树索引  
    可以
 *  hash索引不⽀持多了联合索引的最左匹配规则，  
    但是B+树索引⽀持
 *  如果有⼤量重复键值的情况下，因为存在hash碰  
    撞，hash索引的效率会很低

#### B树和B+树的区别 ####

⼆叉树

*  任何节点的左⼦节点的键值都⼩于当前节点的键值，右⼦节点的键值都⼤于当前节点的键值
 *  平衡⼆叉树/AVL树
    
     *  当⼆叉树⾮常极端，变成⼀个链表后，它就没有了⼆叉树的相关优秀性质了。所以我们在insert节点的时候，需要不断的旋转，来使⼆叉树平衡，最终使得其查询效率最⾼。调整⼀共分为四种情况：LL，RR，LR，RL
 *  B-树
    
     *  因为数据库中⼤部分数据都存在于磁盘，但是IO⼀次磁盘的代价相对来说⽐较⼤，我们需要尽可能的减少AVL树的深度，即增加每个节点的数据量。这便是B-树的由来
     *  每⼀个节点称为⻚，也就是⼀个磁盘块。 B树相对于平衡⼆叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的⼦节点
 *  B+树
    
     *  是B-树的变形，相对于B-树来说，B+树最主要的不同之处就是其⾮叶⼦节点上是不存储数据的，数据全在叶⼦节点存储。这就意味着B+树⽐B-树更胖因为B+树索引的所有数据均存储在叶⼦节点，⽽且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。⽽B树因为数据分散在各个节点，要实现这⼀点是很不容易的

**小伙伴们，点赞支持一下哦！**  
![在这里插入图片描述][20210407132602972.png_pic_center]

[20210407132602972.png_pic_center]: /images/20221022/c860b9d568a7422895f986e81fb38359.png