这是我参与「第三届青训营 -后端场」笔记创作活动的的第5篇笔记

MySQL

关系型和非关系型数据库区别

关系型数据库优点

• 容易理解，采用关系模型组织数据；
• 可以保持数据的一致性；
• 数据更新开销小；
• 支持复杂查询(where子句查询)。

非关系型数据库优点

• 不需要经过SQL层解析，读写效率高；
• 基于键值对，数据扩展性好；
• 可支持多种类型数据的存储，如图片文档等。

什么是非关系型数据库

• 关系型数据库即NOSQL，采用键值对形式存储；
• 其读写性能高，易于扩展，如redis
• 适用于日志系统、地理位置系统等情况。

为什么要使用索引

• 通过创建唯一索引，保证数据库每行数据的唯一性；
• 可大大加快数据的检测速度；
• 帮助系统避免排序和临时表；
• 将随机IO变为顺序IO；
• 可加速表与表之间的连接。

MySQL是如何执行一条SQL的？

• 客户端请求；
• 连接器验证用户身份，给予权限；
• 查询缓存，若存在缓存直接返回，不存在则进行后续操作；
• 分析器对SQL进行词法分析和语法分析；
• 优化器执行SQL优化，选择最优的执行方案方法；
• 执行器检测用户是否有权限，若有则使用引擎提供的接口；
• 去引擎层获取数据返回。

MySQL内部构造

• 服务层

  涵盖MySQL大多数核心服务功能，以及所有内置函数

  • 连接器
  • 查询缓存
  • 分析器
  • 优化器
  • 执行器

• 存储引擎

  负责数据的存储和提取，其架构模式是插件式

Drop、Delete和Truncate的区别

• Delete用来删除表的全部或一部分数据，执行delete之后，用户需要提交或者回滚来执行删除活撤销阐述，会触发这个表上所有的delete触发器；
• Truncate删除表中所有数据，这个操作不能回滚也不能触发表上的触发器，比delete更快，占用空间更小；
• Drop用于从数据库中删除表，所有数据行、索引和权限都会被删除，所有DML触发器也不会被触发，此命令不能回滚；
• 若要删除一张表，用Drop；若要删除部分数据用delete；若要删除全部数据保留表需用Truncate。

具体解析

• delete语句执行删除的过程是每次从表中删除一行，同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作。truncate则是一次性从表中删除所有的数据并不把单独的操作记录记入日志保存，删除行是不能恢复的，而且在删除过程中不会激活与表有关的删除触发器，执行速度更快。
• 表和索引所占空间。当表被truncate后，这个表和索引所占的空间会恢复初始大小，而delete则不会减少表或索引所占用的空间，drop语句将表所占用的空间全部释放掉。
• 一般而言，drop>truncate>delete
• truncate只能针对table，而delelte是可以针对table喝view。
• delete语句为DML，这个操作会放在roll segment中，事务提交后才生效。若有相应的tigger，在执行的时候被触发。
• truncate和drop是DDL，操作立即生效，不能回滚。
• 在没有备份的情况下，慎用drop和truncate。
• truncate table 表名：速度快，效率高。

MySQL的优化有哪些

• 为搜索字段创建索引；
• 避免使用select *，列出需要查询的字段；
• 垂直分割分表；
• 选择正确的存储引擎。

数据库隔离系级别

• 未提交读：事务中发生了修改，即使没有提交，对于其他事务也是可见的。若事务发生了回滚，可能容易发生脏读、幻读或不可重复读。
• 提交读：对于一个事务从开始到提交之前，所做的任何修改其他事务都不可见，可以阻止脏读，但是幻读和不可重复读仍有可能发生。
• 重复读：就是对一个记录读取多次的记录是相同的，可以阻止脏读和不可重复读，但是幻读仍可能发生。
• 可串行化读：在并发情况下， 和串行化的读取结果是一致的，没什么不懂，可以防止脏读、幻读以及不可重复读。
• MySQL lnnoBD存储引擎默认支持的隔离级别是可重复读。

数据库索引选择B+树的主要原因是什么？

• 主要原因： 因为B+树只要遍历叶子节点就可以实现整棵树的遍历，而在数据库中，基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

其他原因

• 文件和数据库都需要较大的存储，无法全部存储在内存中，需要存储在磁盘里。而索引是为了数据的快速定位与查找，因此索引的结构组织要尽量减少查找过程中磁盘IO的存取次数，因此B+树更适合。
• 数据库利用局部性原理和磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次IO就可以完全载入，而红黑树这种结构高度较深，且无法利用局部性。
• B+树方便扫库。B+树支持range-query，查找速度更快，且效率更稳定。
• B+树的磁盘读写代价更低：内部结构没有指向关键字具体信息的指针，因此其内部节点相对于B树更小。

视图和游标

• 视图是一种虚拟的表，通常有一个表或者多个表的行或列的子集，具有和物理表相同的功能；
• 游标是对查询出来的结果集作为一个单元来有效地处理。一般不使用游标，但是需要逐条处理数据时，游标较为重要。

MySQL中为什么要有事务回滚机制

• MySQL中恢复机制是通过回滚日志实现的，所有的事务进行的修改会先记录到这个回滚日志中，然后再对数据库中的对应进行写入，如果事务已经被提交了，就无法再次回滚了。
• 回滚可以在发生错误或者用户执行ROLLBACK的时候提供回滚相关的信息；
• 在系统发生崩溃、数据库进程被杀死后，当用户再次启动数据库进程时，能够立刻通过查询回滚日志将之前未完成的事物进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，这是我们需要先写日志后写数据库主要原因。

数据库引擎InnoBD与MyISAM的区别

InnoBD

• 是MySQL默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑其他存储引擎；
• 实现了四个标准的隔离界别，默认是可重复读；
• 主索引是聚簇索引，在索引中保留了数据，从而避免直接读取磁盘，对查询的性能提升帮助较大；
• 内部做了较多优化；
• 支持真正的线热备份。

MyISAM

• 设计简单，数据以紧密格式存储。对于只读数据或表比较下、可以容忍修复操作时，可以使用；
• 提供了大量特性，如压缩表、空间数据索引等；
• 不支持事务；
• 不支持行级锁，只能对整张表进行加锁，读取时会对所需要独到的所有表加共享锁。

总结

• 事务：InnoDB是事务性的，可以使用Commit和Rollback语句；
• 并发：MyISAM只支持表级锁；
• 外键：InnoDB支持外键；
• 备份：InnoDB支持在线热备份；
• 崩毁恢复：MyISAM崩溃会发生损坏的概率较大，恢复速度较慢；

数据库并发事务会带来哪些问题？

• 脏读：在第一个修改事务和读取事务进行时，若读取的事务已经读修改后的数据，但是后续为了满足一致性特性该事务做了回滚，则读取的事物得到的结果是脏数据。
• 幻读：一般是T1在某个范围内进行修改才做，而T2读取该范围导致数据是修改之前的，强调范围。
• 丢弃修改：两个写事务T1 T2同时对A=0进行递增修改，结果T2覆盖T1，导致最终结果是1不是2，事务被覆盖。
• 不可重复读：T2读取一个数据，然后T1对其进行修改，如果T2再次读到这个数据，此时读取的结果和第一次的不一样。

MySQL索引主要使用的两种数据结构是什么

• 哈希索引：底层使用哈希表，查询性能快，一般适用于单条数据查询的时候；
• B+树索引：将索引值按一定算法存入一个树形数据结构中，每次擦汗寻都是从树的入口开始，依次遍历node，获得叶子节点。

数据库为什么要分库或者分表？

• 目的：减少单库单表负担，提高查询性能，缩短查询时间。

• 分表：将压力分散到不同表上，不但可以提高性能、缩短时间，还可以缓解表锁的问题，可分为水平拆分和垂直拆分。

  • 水平分表：取模分表属于随即分表，时间维度分表属于连续分表。
  • 库内分表：仅解决单表数据过大问题，但是没有将其分散到不同的物理机上。

• 分库分表带来的问题：

  • 数据迁移与扩容问题：一般是先读取数据，再写入各表；
  • 分页与排序问题：将不同的分表数据进行排序并返回，汇总后再次排序，返回给用户。

MySQL四种索引类型

• 全文索引：仅MyISAM引擎支持，可以在create table、alter table、create index使用；
• 哈希：可以一次定位，在“=”和“in”条件下查询效率高，但是对于范围查询、排序和组合索引效率较低；
• B+树：最常用的索引类型；
• R树：仅支持geometry数据类型，使用较少。

视图的作用是什么？可以修改么？

• 视图是虚拟的表，与包含数据的表不一样，其只包含使用时动态检索数据的查询，不包含任何列或数据；
• 使用视图可以简化复杂的sql操作，隐藏具体细节，保护数据；
• 视图不能被索引，也不能有关的触发器或者默认值，如果视图内本身有order by则对视图再次order by将被覆盖。
• 视图主要用于简化索引，保护数据，大部分视图都不能更新。
• 对于某些视图如未使用联结子查询分组聚集函数Distinct Union等，可以对其更新。

为什么B+树比B树更适合实际应用操作系统的文件索引和数据库索引？

• B+树磁盘读写代价很低，查询效率更稳定；

• B+树只要遍历叶子节点就可以实现对整棵树的遍历，效率较高；

• 特点：

  • 所有关键字都出现在叶子节点的链表中(稠密索引)，且链表中的关键字恰好是有序的；
  • 不可能在非叶子节点命中；
  • 非叶子节点相当于是叶子节点的索引(稀疏索引)，叶子节点相当于是存储数据的数据层。

每天5W条以上的MySQL数据库，有哪些优化手段？

• 良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高数据；
• 选择适合的表字段数据类型和存储以前宁，适当添加索引；
• MySQL库主从读写分离；
• 找规律分表，减少表单中的数据量，提高查询速度；
• 添加缓存机制；
• 生成静态页面；
• 书写高效的sql。

什么时候需要建立数据库索引

• 频繁使用的、用以缩小查询范围的字段、需要排序的字段可以建立索引；
• 对于查询中很少设计的列或者重复值比较多的列不建议建立索引；
• 对于一些特殊的数据类型，不宜建立索引。

什么是覆盖索引

• 如果一个索引包含所有要查询的字段的值，就称之为覆盖索引。

数据库的主键、超键、候选键、外键是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键；
• 候选键：不包含多于属性的超键称为候选键。在候选键中，若再删除属性，就不是键了；
• 主键：用户选作元组标识的一个候选键程序主键；
• 外键：如果关系模式R中属性K是其他模式的主键，则k在模式R中称为外键。

数据库三大范式

第一范式

• 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库。
• 第一范式是指数据库表里的每一列都是不可分割的基本数据项，同一列中不能由多个值，即实体中的某个属性不能由多个值或者有重复的属性。
• 若出现重复的属性，就需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系，第一范式中表的每一行只包含一个实例的信息。
• 简单来说，第一范式就是无重复的列。

第二范式

• 第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式，第二范式要求数据库表中的每个实例或行可以被唯一的区分。
• 为实现区分通常为表加上一个列，以存储各个实例的唯一标识，这个唯一属性列被称为主关键字或主键、主码。第二范式要求实体的属性完全依赖于主关键字。
• 所谓完全依赖是指不能存在仅依赖主关键字一部分属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。
• 简单来说，第二范式是非主属性非部分依赖于主关键字。

第三范式

• 满足第三范式必须要先满足第二范式，第三范式要求一个数据库表中不包含已在其他表中已包含的非主关键字信息。
• 简单来说，第三范式就是属性不依赖于其他非主属性。

简单归纳：

• 第一范式：字段不可分；
• 第二范式：有主键，非主键字段依赖主键；
• 第三范式：非主键字段不能相互依赖。

解释：

• 第一范式：原子性。字段不可再分，否则不是关系数据库；
• 第二范式：唯一性。每个表只说明一个事务；
• 第三范式：每列都与主键有直接关系，不存在传递依赖。

事务四大特性：原子性、一致性、隔离性、永久性

• 原子性：一个事务中所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。要求写入的资料完全符合所有预设规则，包括其精确度、串联性以及后续数据库可以自发性完成预定的工作。
• 隔离性：数据库允许并发事务，隔离性可以放置多个事务并发执行时导致数据不一致。
• 持久性：事务处理结束后，对数据的修改是永久的，即使系统故障也不会丢失。

什么是聚合索引

• 聚簇索引就是按照拼音查询，非聚簇索引就是按照偏旁继续宁查询。
• 正文内容本身就是按照一定规则排列的目录称为“聚集索引”。

聚集索引和非聚集索引的区别是什么？

• 通过聚集索引可以查到需要查找的数据，通过非聚集索引可以查到记录对应的主键值，再用主键值通过聚集索引查找到需要的数据。
• 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
• 聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍是索引节点，只不过包含一个指向对应数据块的指针。

创建索引时需要注意什么？

• 非空字段，应该指定为NOT NULL，除非像存储NULL；
• 取值离散大的字段
• 索引字段越小越好；
• 唯一、不为空、经常被查询的字段适合键索引。

MySQL中CHAR和VARCHAR的区别有哪些？

• char的长度不可变，用空格填充到指定长度大小，varchar的长度可变。
• char的存储速度比varchar快；
• char的存储方式是英文字符占一个字节，中文占两个。varchar的都是两个。

MySQL有哪些索引？

• 普通索引：仅加速查询；
• 唯一索引：加速查询+列值唯一(可以有null)；
• 主键索引：加速查询+列值唯一(不可以有null)+表中只有一个；
• 组合索引：多列值组成的索引，专用于组合搜索，效率大于索引合并；
• 全文索引：对文本内容进行分词，进行搜索；
• 索引合并：使用多个单列索引组合搜索；
• 覆盖索引：select的数据列只用从索引中就能取得，不必读取数据行。即查询列要被所建的索引覆盖。
• 聚集索引：表数据和主键一起存储的。

索引这么多优点，为什么不对表中每一列创建一个索引？

• 索引需要动态维护，会降低数据的维护速度；
• 索引需要占用物理空间，若建立族索引，需要的空间更大；
• 创建索引和维护索引都需要耗费时间。

索引如何提高查询速度的？

• 将无序的数据变成相对有序的数据。

使用索引的注意事项

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在经常使用where的子句的列上面创建索引，加快条件的判断速度；
• 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描；
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询的时间；
• 避免where字句中对字段施加函数，这样会造成无法命中索引；
• 在中到大型表索引都是有效地，但是特大型表维护开销较大，不适合建索引，可建立逻辑索引；
• 在经常用到连续的列上，这些列主要是有一些外键，可以加快连接的速度；
• 与业务无关时多使用逻辑主键；
• 删除长期未使用的索引，不使用的索引的存在会造成不必要的浪费；
• 在使用limit offset查询缓存时，可以借助索引来提高性能。

B+树无限增加树的路数是不是可以有更好的查找效率？

• 不可以。
• 这样会形成一个无序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量太大，不一定能一次性加载到内存中。
• 有序数组没办法一次性加载进内存，这个时候B+树的存储威力就出来了。

数据库的表锁和行锁

表锁

• 不会出现死锁，但是发生锁冲突几率高，并发低。
• MyISAM在执行查询语句前，会自动给所有涉及的表加读锁，执行增删改操作前，会自动给涉及的表加写锁。
• MySQL的表级锁有两种模式，表共享读锁和表独占写锁。
• MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会让查询很难得到锁，造成永远阻塞。

行锁

• 行锁必须有索引才能实现，否则会自动锁全表，就不是行锁了；

• 两个事务不能锁同一个索引；

• insert、delete、update在事务中都会自动默认加上排它锁。

• 行锁适用的场景：

  • A用户消费，service层先查询该用户的帐户余额，若余额足够则进行后续的扣款操作，此时应该对该记录加锁。

SQL语法中内连接、自连接、外连接和交叉连接的区别是什么？

• 内连接：只有两个元素表相匹配的才能在结果集中显示；

• 外连接：

  • 左外连接：左边为驱动表，驱动表的数据全部显示，匹配表的不匹配不会显示；
  • 右外连接：右边为驱动表，驱动表的数据全部显示，匹配表的不匹配不会显示；
  • 全外连接：连接的表中不匹配的数据全部会显示出来。

• 交叉连接：笛卡尔效应，现实的结果是链接表数的乘积。

有哪些数据库优化手段？

• 范式优化：比如消除冗余节省空间；
• 反范式优化：比如适当增加冗余，减少join；
• 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句；
• 读写分离：主库负责写，从库负责读；
• 拆分表：分区将数据在物理上分隔开，不同的分区数据可以指定保存在不同磁盘上的数据文件里。

数据库拆分树数据表有哪些？

案例：以产品表、订单表和用户表为例：

垂直拆分

• 可解决表与表之间的IO竞争关系；
• 但是无法解决表单中数据量增长出现的压力
• 方案是将产品表和用户表放到一个server上，订单表单独放到一个server上。

水平拆分

• 可以解决表中数据量增长的压力；

• 但是无法解决表与表之间的IO争夺；

• 方案：

  • 用户表通过性别拆分称男用户和女用户表；
  • 订单表拆分成已完成订单和未完成订单；
  • 产品表可以将未完成订单上放一个server

数据库为什么不适用hash表

• 利用hash需要将数据全部加载到内存中，因此若数据量太大，会导致内存消耗严重；
• 对于唯一查找hash更快的，但是数据库中进场需要查询多条数据，B+树的有效性更重要。

数据库如何保持一致性？

• 从数据库层面，数据库通过原子性、隔离性和持久性来保持一致性。
• 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据。

数据库如何保持原子性？

• 主要是利用Innodb的undo log，即回滚日志。当事务执行失败，则需要回滚，保证回到修改之前的样子。

数据库如何保证持久性？

• 利用Innodb的redo log，即重写日志。当数据修改的时候，不仅在内存中操作，还会在重写日志中记录，当事务提交的时候，会将redo log日志进行出刷盘。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

数据库的该并发怎么解决？

• 在web服务器框架中加入缓存，在服务器与数据库层加入缓存层，将高频访问的数据存入缓存中。
• 增加数据库索引，进而提高查询速度。
• 主从读写分离，让主服务器负责写，从服务器负责读。
• 将数据库进行拆分，使数据库的表尽可能小，提高查询速度。
• 使用分布式架构，分散计算压力。

什么是数据库(Database, DB)

• 数据库就是将数据大量保存起来，通过计算机加工而成的可以进行高效访问的数据集合；

• 用来管理数据库的计算机系统称为数据库管理系统；
• 数据库可分为层次数据库、关系数据库、面向对象数据库；XML数据库；键值对数据库。

\