• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

最全MySql知识整理 一

互联网 diligentman 2周前 (04-08) 7次浏览

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索引
  • 全文索引
    • MyISAMInnoDB都⽀持全⽂索引
    • 有三种模式:⾃然语⾔模式,布尔模式和查询
      扩展模式
  • 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树因为数据分散在各个节点,要实现这⼀点是很不容易的

小伙伴们,点赞支持一下哦!
最全MySql知识整理 一


程序员灯塔
转载请注明原文链接:最全MySql知识整理 一
喜欢 (0)