0%

MySQL知识总结

前言

  MySQL是常用的关系型数据库,MySQL作为企业中常用的数据库之一,了解MySQL并熟练掌握MySQL的使用是十分重要。现将MySQL知识总结记录下来,方便个人学习查阅。主要知识来源GitHub开源项目JavaGuide

基本概念

  1. 什么是数据库,数据库管理系统,数据库系统,数据库管理员?

    • 数据库 :数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。
    • 数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库的大型软件,通常用语用于建立、使用和维护数据库。
    • 数据库系统 : 数据库系统(Data Base System,简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。
    • 数据库管理员 : 数据库管理员(Database Administrator,简称 DBA)负责全面管理和控制数据库系统。
  2. 什么是关系型数据库?

    建立在关系模型基础上的数据库。关系模型表明了数据库所存数据之间的联系(一对一,一对多,多对多)。

    大部分关系型数据库使用SQL来操作数据库中的数据。常见关系型数据库MySQL(3306),Oracle,SQL server等。

存储引擎

  1. 查看MySQL存储引擎命令?

    1
    mysql> show engines;

    默认InnoDB,属于事务性引擎,支持事务

  2. MyISAM 和 InnoDB 的区别?

    1.是否支持行级锁

    MyISAM 只有表级锁(table-level locking),而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

    也就说,MyISAM 一锁就是锁住了整张表,这在并发写的情况下是多么滴憨憨啊!这也是为什么 InnoDB 在并发写的时候,性能更牛皮了!

    2.是否支持事务

    MyISAM 不提供事务支持。

    InnoDB 提供事务支持,具有提交(commit)和回滚(rollback)事务的能力。

    3.是否支持外键

    MyISAM 不支持,而 InnoDB 支持。

    🌈 拓展一下:

    一般我们也是不建议在数据库层面使用外键的,应用层面可以解决。不过,这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。

    4.是否支持数据库异常崩溃后的安全恢复

    MyISAM 不支持,而 InnoDB 支持。

    使用 InnoDB 的数据库在异常崩溃后,数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log

    🌈 拓展一下:

    • MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性,使用 undo log(回滚日志) 来保证事务的原子性
    • MySQL InnoDB 引擎通过 锁机制MVCC 等手段来保证事务的隔离性( 默认支持的隔离级别是 REPEATABLE-READ )。
    • 保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。

    5.是否支持 MVCC

    MyISAM 不支持,而 InnoDB 支持。

    讲真,这个对比有点废话,毕竟 MyISAM 连行级锁都不支持。

    MVCC 可以看作是行级锁的一个升级,可以有效减少加锁操作,提供性能。

  3. MyISAM 和 InnoDB 的选择问题?

    大多情况选择InnoDB,MyISAM不支持事务,崩溃恢复!

锁机制

  1. 什么是锁机制?

    锁是计算机协调多个进程或线程并发访问某一资源的一种机制。

    从性能上来分,锁分为乐观锁和悲观锁。

    从数据库操作的类型分,锁分读锁和写锁(都是悲观锁)。

    从操作数据库的粒度来说,分表锁和行锁

  2. 悲观锁?

    当要对数据库中的一条数据进行修改的时候,为了避免同时被其他人修改,最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制,在修改数据之前先锁定,再修改的方式被称之为悲观并发控制【Pessimistic Concurrency Control,缩写“PCC”,又名“悲观锁”】。

    悲观锁的实现:

    1. 传统的关系型数据库使用这种锁机制,比如行锁、表锁、读锁、写锁等,都是在操作之前先上锁。
    2. Java 里面的同步 synchronized关键字的实现。
  3. 乐观锁?

    乐观锁是相对悲观锁而言的,乐观锁假设数据一般情况不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果冲突,则返回给用户异常信息,让用户决定如何去做。乐观锁适用于读多写少的场景,这样可以提高程序的吞吐量。

    乐观锁的实现:

    1. CAS 实现:Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。
    2. 版本号控制:一般是在数据表中加上一个数据版本号 version 字段,表示数据被修改的次数。当数据被修改时,version 值会 +1。当线程 A 要更新数据时,在读取数据的同时也会读取 version 值,在提交更新时,若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新,否则重试更新操作,直到更新成功
  4. 读锁和写锁?

    读锁(共享锁,S锁 Shared):针对同一份数据,多个读操作可以同时进行并且不会相互影响

    写锁(排它锁,X锁 eXclusive):在当前写操作完成前,会阻断其他一切的锁

  5. 表级锁和行级锁对比?

    • 表级锁: MySQL 中锁定 粒度最大 的一种锁,对当前操作的整张表加锁,实现简单,资源消耗也比较少,加锁快,不会出现死锁。其锁定粒度最大,触发锁冲突的概率最高,并发度最低,MyISAM 和 InnoDB 引擎都支持表级锁。
    • 行级锁: MySQL 中锁定 粒度最小 的一种锁,只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
    • 考虑上述特点,表级锁使用与并发性不高,以查询为主,少量更新的应用,比如小型的web应用;而行级锁适用于高并发环境下,对事务完整性要求较高的系统,如在线事务处理系统。
  6. InnoDB 存储引擎锁的机制有哪些?

    • Record lock:记录锁,单个行记录上的锁
    • Gap lock:间隙锁,锁定一个范围,不包括记录本身
    • Next-key lock:record+gap 临键锁,锁定一个范围,包含记录本身

事务

  1. 什么是事务?

    逻辑上的一组操作,要不都执行,要不都不执行。

  2. 事务的四大特性ACID(关系型数据库事务都有这个特点)?

    • 原子性 Atomicity 事务是最小的执行单位,不能再分割。要不都执行,要不都不执行
    • 一致性 Consistency 执行事务前后,数据需要保持一致
    • 隔离性 Isolation 并发访问时,一个用户事务不能被其他事务所干扰
    • 持久性 Durability 事务提交之后,对数据库中的改变是持久的
  3. 常见的并发异常?

    • 脏读:一个事务读到了另外一个事务修改了但是没有提交的数据
    • 不可重复读:一个事务读多次同一数据,中途该数据被另外一个事务读取并修改,导致两次读到的数据不一致
    • 幻读:一个事务读多次同一数据,中途该数据被另外一个并发事务插入或者删除,导致随后读取的数据发生变化,像幻觉一样
    • 丢失修改:两个事务先后访问并修改数据,导致第一个修改的数据丢失
  4. 事务隔离级别?

    • READ-UNCOMMITTED 读取未提交

    • READ-COMMITTED 读取已提交,可避免脏读

    • REPEATABLE-READ 可重复读,多次读取数据一致,可避免脏读和不可重复读

    • SERIALIZABLE 可串行化,所有事务依次执行,可防止脏读,不可重复读,幻读

      隔离级别 脏读 不可重复读 幻读
      READ-UNCOMMITTED
      READ-COMMITTED ×
      REPEATABLE-READ × ×
      SERIALIZABLE × × ×
  5. MySQL默认隔离级别?

    MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)

MVVC

  1. 什么是mvvc?

    英文全称为Multi-Version Concurrency Control,翻译为中文即 多版本并发控制。某种程度上就是乐观锁的一种实现方式。在Java编程中,如果把乐观锁看成一个接口,MVCC便是这个接口的一个实现类而已。

  2. MVVC机制的实现?

    MVCC机制的实现是通过read-view机制与undo日志版本链对比,来使得不同的事务会根据数据版本链进行比对来读取同一条数据在版本链上的不同版本数据,进行数据的隔离。下面通过一张图来详细描述MVCC的实现机制。

    首先我们介绍一下日志版本链,日志版本链是指一行数据被多个事务依次修改后,每次事务修改完,mysql都会保留修改前的数据的undo回滚日志,同时使用了两个隐藏字段trx_id(事务号)和roll_pointer(回滚指针)把这些回滚日志串联起来形成一个版本链。具体的如图所示:

    图中每一行代表一个回滚日志,每个日志末尾都有一个roll_pointer指向上一条日志。

    图片

    当事务开启时,执行任何查询sql都会生成当前事务的一致性视图read-view,生成的视图在事务结束之前都不会变化,这个视图由查询时所有未提交事务id数组和已创建的最大事务id组成。

    图片

    在我们当前的日志版本链内,查询了一条数据,创建了一个对应的read-view,那么它应该是这样,[80,81,82],202 ,数组内最小的id为min_id,最大的事务id(max_id)是202。

    具体的版本链比对规则:

    1. 如果trx_id<min_id , 表示这个版本是已提交的事务产生的,可见

    2. 如果trx_id >max_id,表示是由生成read-view之后的事务版本生成的,不可见

    3. 如果trx_id落在min_id和max_id之间,分为两种情况:

      1. 如果trx_id在read-view的数组内,不可见
      2. 如果trx_id不在read-view的数组内,可见

    PS:事务的trx_id生成规则是依次递增的,所以后面生成的trx_id一定比前面的大。事务的真正启动,是在执行第一个修改操作数据库的语句开始的,查询语句不生成事务id。

    最后,再提供一张图方便大家理解:

    图片

索引

  1. 什么是索引,有什么用?

    索引是一种快速查询和检索数据的数据结构,作用相当于目录的作用。常见的索引有:B树,B+树和 Hash。

  2. 索引的优缺点?

    • 优点:加快数据检索速度,创建唯一性索引,可以保证每一行数据的唯一性
    • 缺点:创建维护索引需要时间,同时占用一定空间。
  3. Hash?

    哈希表是键值对的集合,通过键(key)即可快速取出对应的值(value),因此哈希表可以快速检索数据(接近 O(1))。

    为何能够通过 key 快速取出 value呢? 原因在于 哈希算法(也叫散列算法)。通过哈希算法,我们可以快速找到 value 对应的 index,找到了 index 也就找到了对应的 value。

  4. MySQL中不使用Hash作为索引数据结构的原因?

    1.Hash 冲突问题 :多个不同的 key 最后得到的 index 相同。

    2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点: 假如我们要对表中的数据进行排序或者进行范围查询,那 Hash 索引可就不行了。

  5. B 树& B+树 ?

    B 树也称 B-树,全称为 多路平衡查找树 ,B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced (平衡)的意思。

    目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

    B 树& B+树两者有何异同呢?

    • B 树的所有节点既存放键(key) 也存放 数据(data),而 B+树只有叶子节点存放 key 和 data,其他内节点只存放 key。

    • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

    • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找,可能还没有到达叶子节点,检索就结束了。而 B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。

      img

  6. 索引类型?

    • 主键索引 数据表的主键列使用的就是主键索引。
    • 二级索引(辅助索引) 二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
  7. 聚集索引?

    聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

    优点:查询速度快,整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。

    缺点:依赖有序的数据,更新代价大。

  8. 非聚集索引?

    非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

    优点:
    更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的

    缺点:
    跟聚集索引一样,非聚集索引也依赖于有序的数据
    可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

  9. 创建索引的注意事项?

    1.选择合适的字段创建索引:

    • 不为 NULL 的字段 :索引字段的数据应该尽量不为 NULL,因为对于数据为 NULL 的字段,数据库较难优化。如果字段频繁被查询,但又避免不了为 NULL,建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
    • 被频繁查询的字段 :我们创建索引的字段应该是查询操作非常频繁的字段。
    • 被作为条件查询的字段 :被作为 WHERE 条件查询的字段,应该被考虑建立索引。
    • 频繁需要排序的字段 :索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。
    • 被经常频繁用于连接的字段 :经常用于连接的字段可能是一些外键列,对于外键列并不一定要建立外键,只是说该列涉及到表与表的关系。对于频繁被连接查询的字段,可以考虑建立索引,提高多表连接查询的效率。

    2.被频繁更新的字段应该慎重建立索引。

    虽然索引能带来查询上的效率,但是维护索引的成本也是不小的。 如果一个字段不被经常查询,反而被经常修改,那么就更不应该在这种字段上建立索引了。

    3.尽可能的考虑建立联合索引而不是单列索引。

    因为索引是需要占用磁盘空间的,可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,且修改索引时,耗费的时间也是较多的。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升。

    4.注意避免冗余索引

    冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。如(name,city )和(name )这两个索引就是冗余索引,能够命中前者的查询肯定是能够命中后者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。

    5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

    前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引带替普通索引。

语句执行流程

  • MySQL 主要分为 Server 层和引擎层,Server 层主要包括连接器、查询缓存、分析器、优化器、执行器,同时还有一个日志模块(binlog),这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  • 引擎层是插件式的,目前主要包括,MyISAM,InnoDB,Memory 等。
  • 查询语句的执行流程如下:权限校验(如果命中缓存)—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
    • 先判断你有没有权限
    • 查询缓存中是否有数据(MySQL 8.0 之后移除)
    • 分析查询语句,词条分析(提取其中关键字)+ 语法分析(判断是否符合语法)
    • 优化器确定执行的方案,据自己的优化算法进行选择执行效率最好的一个方案(不一定最好)
    • 进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口,返回引擎的执行结果。
  • 更新语句执行流程如下:分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)
    • update tb_student A set A.age=’19’ where A.name=’ 张三 ‘;
    • 先查询到张三这一条数据,如果有缓存,也是会用到缓存。
    • 然后拿到查询的语句,把 age 改为 19,然后调用引擎 API 接口,写入这一行数据,InnoDB 引擎把数据保存在内存中,同时记录 redo log,此时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,随时可以提交。
    • 执行器收到通知后记录 binlog,然后调用引擎接口,提交 redo log 为提交状态。
    • 更新完成。

存储时间

  1. MySQL中使用什么存储时间好?

    切记不要用字符串存储时间,占用空间大,日期效率低

    每种方式都有各自的优势,根据实际场景才是王道。下面再对这三种方式做一个简单的对比,以供大家实际开发中选择正确的存放时间的数据类型:

常用日期存储方式

易混点

  1. drop , truncate , delete 之间的区别?
    • drop(丢弃数据): drop table 表名 ,删表
    • truncate (清空数据) : truncate table 表名 ,清空表的数据
    • delete(删除数据) : delete from 表名 where 列名=值,删除具体的某一行数据
    • drop和truncate属于DDL语言,数据定义语言,delete属于DML语言,数据操作语言
  2. ……

参考资料

本文主要知识来源于网络,仅作为个人学习使用

参考来源:

https://github.com/Snailclimb/JavaGuide

https://www.jianshu.com/p/d2ac26ca6525

https://mp.weixin.qq.com/s/7KblQbpWXTW3I9MHWRvnkg

-------------本文结束感谢您的阅读-------------
您的支持将成为我创作的动力!