精华内容
下载资源
问答
  • 数据库面试知识点汇总 1.简述关系型与非关系型数据库的区别? 关系型数据库是依据关系模型来创建的数据库,所谓关系模型就是“一对一”、“一对多”、“对多对”等。常见的关系型数据库有Oracle、MySQL、SQL Server...

    数据库面试知识点汇总

    1.简述关系型与非关系型数据库的区别?

    1. 关系型数据库是依据关系模型来创建的数据库,所谓关系模型就是“一对一”、“一对多”、“对多对”等。常见的关系型数据库有Oracle、MySQL、SQL Server等。
    2. 非关系型数据库主要基于“非关系型模型”,其中非关系型模型有:列模型、键值对模型、文档类模型。比如redis属于键值对模型。

    关系型数据库的优点:

    1. 易于维护:都是使用表结构,格式一致。
    2. 使用方便:SQL语言通用,可用于复杂查询。
    3. 复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询。

    关系型数据库的缺点:

    1. 读写性能比较差,尤其是海量数据的高效率读写。
    2. 固定的表结构,灵活度稍欠。
    3. 高并发读写需求,传统关系型数据库来说,硬盘I/O是一个很大的瓶颈。

    非关系型数据库的优点:

    1. 格式灵活:存储数据的格式可以是key,value形式、文档形式、图片形式等,使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。
    2. 速度快:nosql可使用硬盘或者随机存储器作为载体,关系型数据库只能使用硬盘。
    3. 高拓展性
    4. 成本低:nosql数据库部署简单,基本都是开源软件。

    非关系型数据库的缺点:

    1. 不提供sql支持,学习和使用成本较高。
    2. 无事务处理。
    3. 数据结构相对复杂,复杂查询方面稍欠。

    2.简述为什么需要使用索引?

    1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
    2. 可以加快数据的检索速度,是创建索引的主要原因。
    3. 减少磁盘IO,可以直接定位。
    4. 通过使用索引,可以在查询过程中,使用优化隐藏器,提高系统的性能
    5. 加速表与表的连接,特别是在实现数据的参考完整性方面特别有意义。

    缺点:

    1. 创建索引和维护索引需要耗费时间,时间随着数据量的增加而增加。
    2. 索引需要占用物理空间,特别是聚簇索引,需要较大的空间。
    3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    3.简述数据库索引采用B+树不采用B树的原因?

    1. B+树更便于遍历:由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。
    2. B+树的磁盘读写代价更低:B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。 数据存放的更加紧密,具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
    3. B+树的查询效率更加稳定:由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
    4. B+树更适合基于范围的查询 :B树在提高了IO性能的同时并没有解决元素遍历的效率低下的问题,正是为了解决这个问题,B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作或者说效率太低。

    4.简述MySQL索引有哪些类型?

    1. 普通索引:最基本的索引,没有任何限制。
    2. 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。
    3. 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。
    4. 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。
    5. 全文索引:对文本的内容进行分词,进行搜索。

    5.简述什么是聚簇索引及其优缺点?

    1. 聚簇索引并不是单独的索引类型,而是一种数据存储方式。
    2. B+树索引分为聚簇索引和非聚簇索引,主键索引就是聚簇索引的一种,非聚簇索引有复合索引、前缀索引、唯一索引。
    3. 在innodb存储引擎中,表数据本身就是按B+树组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚簇索引的叶子节点成为数据页。
    4. Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。
    5. 非聚簇索引又称为辅助索引,InnoDB访问数据需要两次查找,辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
    6. Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。一张表可有多个二级索引。

    优点:

    1. 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快。
    2. 聚簇索引对于主键的排序查找和范围查找速度非常快。

    缺点:

    1. 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键。
    2. 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
    3. 二级索引访问要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

    6.简述InnoDB与MyISAM实现索引方式的区别?

    1. 首先两者都是用的是B+树索引,但二者的实现方式不同。
    2. 对于主键索引,InnoDB中叶子节点保存了完整的数据记录,而MyISAM中索引文件与数据文件是分离的,叶子节点上的索引文件仅保存了数据记录的地址.
    3. 对于辅助索引,InnoDB中辅助索引会对主键进行存储,查找时,先通过辅助索引的B+树在叶子节点获取对应的主键,然后使用主键在主索引B+树上检索操作,最终得到行数据;MyISAM中要求主索引是唯一的,而辅助索引可以是重复的,主索引与辅助索引没有任何区别,因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    7.简述什么是聚簇索引与非聚簇索引?

    1. 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据。
    2. 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,MyISAM通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因。

    8.主键索引是聚集索引还是非聚集索引?

    1. 聚集索引决定了数据库的物理存储结构,而主键只是确定表格逻辑组织方式。这两者不可混淆!
    2. 在InnoDB下主键索引是聚集索引,在MyISAM下主键索引是非聚集索引。

    9.简述InnoDB为什么使用自增id作为主键?

    1. MySQL底层使用是使用数据页为单位来存储数据的,一个数据页大小默认为16K,当数据页满了,就会申请新的数据页进行存储数据。
    2. 如果主键为自增 id 的话,mysql 在写满一个数据页的时候,直接申请另一个新数据页接着写就可以了。
    3. 如果主键是非自增 id,为了确保索引有序,mysql 就需要将每次插入的数据都放到合适的位置上。当往一个快满或已满的数据页中插入数据时,新插入的数据会将数据页写满,mysql 需要申请新的数据页,并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂,这个大量移动数据的过程是会严重影响插入效率。

    10.简述为什么主键越小越好?

    1. 主键占用空间越大,每个页存储的主键个数越少,路树就越少,B+树的深度会边长,导致IO次数会变多。
    2. 辅助索引的叶子节点上保存的是主键 id 的值,如果主键 id 占空间较大的话,那将会成倍增加 mysql 空间占用大小。

    11.简述数据库执行查询请求的过程?

    1. 客户端请求:建立TCP连接。
    2. 使用连接器进行连接管理:此时服务端会对客户端发来数据携带的主机信息、用户名、密码等信息进行验证,如果认证失败就会拒绝连接。(连接器)
      注: 当客户端连接服务端进程后,服务端进程会为其创建进程专门用于交互,当断开连接后,服务端不会立即进行销毁,而是会进行缓存,用于下次新的连接,这样可以不用频繁的创建和销毁线程,节省开销。
    3. 缓存查询:服务端会对之前的请求结果进行缓存,若存在缓存直接返回,否则继续执行下一步。维护缓存的代价较大,因此在8.0版本后已删除缓存。
    4. 语法解析:由于目前为止还未对文本解析,此时会对文本进行词法分析、语法分析、语义分析等过程,真正开始解析。(分析器)
    5. 查询优化:主要对执行的sql优化选择最优的执行方案。(优化器)
    6. 存储引擎:执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口。然后去引擎层获取数据返回,若开启查询缓存则会缓存查询结果。(执行器)

    12.简述脏读、幻读、不可重复读的定义?

    1. 脏读:指当一个事务正在访问数据,并且对数据进行了修改,而这种数据还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。因为这个数据还没有提交那么另外一个事务读取到的这个数据我们称之为脏数据。
    2. 不可重复读:指在一个事务内,多次读同一数据。在这个事务还没有执行结束,另外一个事务也访问该同一数据,那么在第一个事务中的两次读取数据之间,由于第二个事务的修改第一个事务两次读到的数据可能是不一样的,这样就发生了在一个事务内两次连续读到的数据是不一样的,这种情况被称为是不可重复读。
    3. 幻读:一个事务先后读取一个范围的记录,但两次读取的纪录数不同,我们称之为幻象读。(两次执行同一条 select 语句会出现不同的结果,第二次读会增加一数据行,并没有说这两次执行是在同一个事务中)

    13.简述数据库的隔离级别?

    1. 读未提交:指一个事务读取另一个事务未提交的修改。,会发生脏读、不可重复读、幻读。
    2. 读提交:指只能读到已经提交的内容。会发生不可重复读和幻读。是SQL Server和Oracle的默认隔离级别。
    3. 可重复读:指当数据被读取时,不可进行update操作,保证多次读取的记录是相同的,解决了不可重复读的问题。但幻读是insert导致,不会避免。可重复读是MySQL的默认隔离级别。
    4. 可串行化读:这是数据库最高的隔离级别,这种级别下,事务“串行化顺序执行”,也就是一个一个排队执行。这种级别下,“脏读”、“不可重复读”、“幻读”都可以被避免,但是执行效率奇差,性能开销也最大,所以基本没人会用。

    14.简述MySQL可以从哪些方面做到性能优化?

    1. 为搜索字段创建索引。
    2. 避免使用 Select *,列出需要查询的字段。
    3. 垂直分割分表,水平分割是分割记录,以一条记录/行为单位。垂直分割则是以列为单位,将列分割出去。
    4. 选择正确的搜索引擎。
    5. 实现数据库的主从同步,实现读写分离。
    6. 添加合适的缓存机制,维护代价高。
    7. 对冷热数据进行均分,减少单个库的压力,使整体性能达到更优。

    15.简述MySQL为什么需要事务回滚机制?

    1. 在MySQL中事务回滚通过日志完成,所有事务进行的修改都会先记录到回滚日志中,然后再对数据库中的对应行进行写入。当事务被提交后就无法回滚了。

      回滚日志的作用:

      1. 能够在发生错误或用户执行rollback时提供回滚的相关信息。
      2. 在整个系统发生崩溃、数据库进程直接被杀死后,当用户再次启动数据库进程时,还能够立刻通过查询回滚日志将之前未完成的事务进行回滚,这也就需要回滚日志必须先于数据持久化到磁盘上,是我们需要先写日志后写数据库的主要原因。

    16.简述MySQL引擎InnoDB和MyISAM的区别?

    InnoDB:

    1. 是MySQL默认的事务型存储引擎,只有当需要它不支持的特性时,才会考虑使用其它的存储引擎。
    2. 实现了四个标准的隔离级别,其中默认为可重复读,在可重复读的隔离级别下,通过MVCC(多版本并发控制协议)+ 间隙锁(Next-key Locking)防止幻读。
    3. 主索引为聚簇索引,在索引中保存数据,从而避免直接读取磁盘,因此对查询性能有很大的提升。
    4. 支持真正的在线热备份。其它存储引擎不支持在线热备份。
    5. 支持行级锁,通过给索引项加锁来实现,即只有通过索引条件检索数据,才会使用行级锁,否则使用表锁。
    6. InnoDB不会对表中行的总量进行预先统计,每次count需要遍历计算。

    MyISAM:

    1. 设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。
    2. 提供了大量的特性,包括压缩表、空间数据索引等。
    3. 不支持事务。
    4. 不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。
    5. 会实时保存数据库表中的总行数,计算快。

    总结:

    1. 事务:MyISAM不支持,InnoDB支持。
    2. 锁级别: MyISAM 表级锁,InnoDB 行级锁及外键约束。
    3. MyISAM存储表的总行数;InnoDB不存储总行数。
    4. MyISAM采用非聚集索引,B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引,B+树叶子存储数据。
    5. 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。

    使用场景:

    1. MyISAM适合:插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择,没有事务。
    2. InnoDB适合:可靠性要求比较高,或者要求事务; 表更新和查询都相当的频繁, 大量的INSERT或UPDATE。

    17.数据库分库分表的原因?

    1. 分库分表的目的在于减少数据库单库单表的负担,提高查询性能,缩短查询时间。
    2. 分库分表分别水平切分和垂直切分。
    3. 垂直切分:分为垂直分库和垂直分表,其中垂直分库是指根据业务的耦合度,将关联度较低的不同表存储于不同的库中,类似于大系统拆分为小系统;垂直分表是指基于数据库表中的列,将不常用的列进行划分成新表,可以使单个表中的数据量变少减少跨页,使得单个页中字段更多,使内存能够加载更多的数据,提高命中率,减少磁盘IO,提高性能。
    4. 水平切分:水平切分是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。但只是库内分表,仅仅是解决了单表数据过大的问题,并没有把单表的数据分散到不同的物理机上,因此并不能减轻 MySQL 服务器的压力,仍然存在同一个物理机上的资源竞争和瓶颈,包括 CPU、内存、磁盘 IO、网络带宽等。
    5. 使用哪种方式分库分表需要依据情况而定,比如数据库是因为表太多而造成海量数据,并且项目的各项业务逻辑划分清晰、低耦合,那么规则简单明了、容易实施的垂直切分必是首选。而如果数据库中的表并不多,但单表的数据量很大、或数据热度很高,这种情况之下就应该选择水平切分,水平切分比垂直切分要复杂一些,它将原本逻辑上属于一体的数据进行了物理分割,除了在分割时要对分割的粒度做好评估,考虑数据平均和负载平均,后期也将对项目人员及应用程序产生额外的数据管理负担。

    18.简述什么是覆盖索引?

    1. 如果一个索引包含所有需要查询的字段的值,我们就称 之为“覆盖索引”。
    2. 对于InnoDB存储引擎来说,如果不是主键索引,那么辅助索引的叶子节点存储的是主键+辅助索引的列值,然后还需要进行回表操作。
    3. 这样的话,会降低查询速度,因此,若使用辅助索引查询时,若查询得到的值和需要查询的结果列值时对应的(或者覆盖),则可以一直接使用其结果,不需要进行回表操作。

    19.简述三大范式的特点?

    1. 第一范式:指数据库表中的每一列都是不可分割的基本数据项,同一列中不能有多个值相同,即无重复的列。
    2. 第二范式:满足第一范式,还要求数据库表中的每个实例或行必须被唯一标识,满足实体的属性完全依赖于主关键字。
    3. 第三范式:满足第二范式,还要求数据库表中不包含其他表中的非主关键字信息,即两个表中不存在相同的非主关键字信息,否则会造成数据冗余。

    20.简述聚簇索引与非聚簇索引(辅助)的区别?

    1. 聚簇索引的叶子节点存放的是主键值和数据行,支持覆盖索引;非聚簇索引的叶子节点存放的是主键值或指向数据行的指针。
    2. 聚集索引就是以主键创建的索引,非聚集索引就是以非主键创建的索引。
    3. 由于节子节点(数据页)只能按照一颗B+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引。

    21.简述事务的四大特性?

    1. 原子性:指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须完全应用到数据库,如果操作失败则不能对数据库有任何影响。
    2. 一致性:指事务开始前和结束后,数据库的完整性约束没有被破坏。
    3. 隔离性:指当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
    4. 持久性:指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

    22.简述创建索引的注意事项?

    1. 非空字段:索引字段不能有NULL,如果有NULL值将不会包含在索引中。
    2. 索引字段越小越好:数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。 唯一、不为空、经常被查询的字段 的字段适合建索引。
    3. 取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高。
    4. 限制创建索引的数量:对于存在大量更新操作的表,索引一般不超过3个。

    23.为什么索引的数量不能太多?

    1. 当对表中的数据进行增加、删除、修改时,同时需要动态维护索引,降低了整体的维护速度。
    2. 索引需要占据物理空间,如果要建立聚簇索引,那么需要的空间就会更大,因为会将数据存储于叶子节点。
    3. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    24.简述数据库的行级锁与表锁?
    表锁:

    1. 不会出现死锁,发生锁的冲突几率高,并发性低。
    2. 存储引擎在进行SQL数据读写请求前,会对涉及到的表进行加锁。
    3. 其中锁分为共享读锁和独占写锁:读锁会阻塞写,写锁会阻塞读和写。

    行级锁:

    1. 会出现死锁,发生锁的冲突几率低,并发性高。
    2. InnoDB引擎支持行锁,与Oracle不同,MySQL的行锁是通过索引加载的,也就是说,行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。

    行级锁注意事项:

    1. 行级锁必须有索引才能实现,否则会自动锁全表,那就不是行锁了。
    2. 两个事务不能锁同一个索引。
    3. insert,delete,update在事务中都会自动默认加上排它锁。

    行锁的适用场景:

    1. 避免不可重复读的场景。

    25.简述为什么MySQL索引使用B+树而不用hash表和B树?

    1. 利用Hash需要把数据全部加载到内存中,如果数据量大,是一件很消耗内存的事,而采用B+树,是基于按照节点分段加载,由此减少内存消耗。
    2. 和业务场景有段,对于唯一查找(查找一个值),Hash确实更快,但数据库中经常查询多条数据,这时候由于B+数据的有序性,与叶子节点又有链表相连,他的查询效率会比Hash快的多。
    3. b+树的非叶子节点不保存数据,只保存子树的临界值(最大或者最小),所以同样大小的节点,b+树相对于b树能够有更多的分支,使得这棵树更加矮胖,查询时做的IO操作次数也更少。

    26.简述B树的结构及特点?
    对于一个M阶的B树有以下特征:

    1. 第一任何非叶子节点最多只有M个儿子,且M > 2;
    2. 根节点的儿子数量范围为[2,M];
    3. 除根节点 以外的非叶子节点的儿子数量为[M/2,M],向上取整,M/2是由于当某个节点达到M个节点信息时,会进行拆分为两个叶子节点。
    4. 非叶子节点的关键字数量 = 叶子数量 - 1;
    5. 所有的叶子节点位于同一层;
    6. k个关键字把节点拆成k+1段,分别指向k+1个儿子,同时满足查找树的大小关系;
    7. 每个节点除了存储索引外还保存该索引对应数据的地址;

    27.简述B+树的结构及特点?
    对于一个M阶的B树有以下特征:

    1. 有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
    2. 所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接,形成一个有序的链表。
    3. 所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。
    4. 由于每个节点只存索引,因此每个页存储的数据变多,可减少IO次数。

    28.数据库如何保证事务的ACID特性?

    1. 原子性:使用innodb的undo log(回滚日志),undo log记录了回滚需要的信息,当事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。
    2. 隔离性: 使用悲观锁和乐观锁对事务处理。
    3. 持久性:使用innodb的redo log(重写日志), 记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置);当做数据修改的时候,不仅在内存中操作,还会在redo log中记录这次操作。当事务提交的时候,会将redo log日志进行刷盘(redo log一部分在内存中,一部分在磁盘上)。当数据库宕机重启的时候,会将redo log中的内容恢复到数据库中,再根据undo log和binlog内容决定回滚数据还是提交数据。
    4. 一致性:通过原子性、隔离性、持久性来保证一致性。

    29.简述使用redo的好处?

    1. redo log体积小,毕竟只记录了哪一页修改了啥,因此体积小,刷盘快。
    2. redo log是一直往末尾进行追加,属于顺序IO。效率显然比随机IO来的快。

    30.如何解决数据库高并发问题?

    1. 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层,将高频访问的数据存入缓存中,减少数据库的读取负担。
    2. 增加数据库索引,进而提高查询速度。(不过索引太多会导致速度变慢,并且数据库的写入会导致索引的更新,也会导致速度变慢)
    3. 主从读写分离,让主服务器负责写,从服务器负责读。
    4. 将数据库进行拆分,使得数据库的表尽可能小,提高查询的速度。
    5. 使用分布式架构,分散计算压力。

    31.简述联合索引的最左匹配原则?

    1. MySQL建立联合索引时遵循最左匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
    2. 对于联合索引而言构造B+树,会依次从左往右按顺序比较进行键值插入,遇到范围查找就会停止,剩下的字段就会失效无法使用索引。

    32.简述SQL语句的优先级顺序?

    1. from:需要从哪个数据表检索数据
    2. join:联合多表查询返回记录时,并生成一张临时表
    3. on:在生成临时表时使用的条件
    4. where:过滤表中数据的条件
    5. group by:如何将上面过滤出的数据分组
    6. having:对上面已经分组的数据进行过滤的条件
    7. select:查看结果集中的哪个列,或列的计算结果
    8. order by :按照什么样的顺序来查看返回的数据

    33.简述索引失效的情况?

    1. like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
    2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效。
    3. 组合索引,不是使用第一列索引,索引失效。
    4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
    5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。
    6. 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
    7. 对索引字段进行计算操作、字段上使用函数。
    8. 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。

    如何检查索引是否失效:

    1. 可以使用explain命令加在要分析的sql语句前面,在执行结果中查看key这一列的值,如果为NULL,说明没有使用索引。

    34.简述什么情况下不应该创建索引?

    1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
    2. 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
    3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
    4. 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。
    展开全文
  • 数据库常见面试题(附答案) 1.事务四大特性 原子性,要么执行,要么不执行 隔离性,所有操作全部执行完以前其它会话不能看到过程 一致性,事务前后,数据总额一致 持久性,一旦事务提交,对数据的改变就是永久的 ...

    数据库常见面试题(附答案)

    1.事务四大特性
    原子性,要么执行,要么不执行

    隔离性,所有操作全部执行完以前其它会话不能看到过程
    一致性,事务前后,数据总额一致
    持久性,一旦事务提交,对数据的改变就是永久的

    2.数据库隔离级别

    多个事务读可能会道理以下问题
    脏读:事务B读取事务A还没有提交的数据
    不可重复读:,一行被检索两次,并且该行中的值在不同的读取之间不同时
    幻读:当在事务处理过程中执行两个相同的查询,并且第二个查询返回的行集合与第一个查询不同时 这两个区别在于,不可重复读重点在一行,幻读的重点
    ,返回 的集合不一样

    示例图,Id =1这一行
    在这里插入图片描述

    幻读,返回的集合不一样
    在这里插入图片描述
    隔离级别总结
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LpKc9pki-1590216797436)(http://note.youdao.com/yws/res/6317/WEBRESOURCEf94460a854aff8aab476a4504e60b26a)]

    3.MYSQL的两种存储引擎区别(事务、锁级别等等),各自的适用场景
    引擎 特性
    MYISAM 不支持外键,表锁,插入数据时,锁定整个表,查表总行数时,不需要全表扫描
    INNODB 支持外键,行锁,查表总行数时,全表扫描
    4.索引有B+索引和hash索引
    索引 区别
    Hash hash索引,等值查询效率高,不能排序,不能进行范围查询
    B+ 数据有序,范围查询
    5.聚集索引和非聚集索引
    索引 区别
    聚集索引 数据按索引顺序存储,中子结点存储真实的物理数据
    非聚集索引 存储指向真正数据行的指针
    6.索引的优缺点,什么时候使用索引,什么时候不能使用索引
    索引最大的好处是提高查询速度,
    缺点是更新数据时效率低,因为要同时更新索引
    对数据进行频繁查询进建立索引,如果要频繁更改数据不建议使用索引。
    7.InnoDB索引和MyISAM索引的区别
    一是主索引的区别,InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
    二是辅助索引的区别:InnoDB的辅助索引data域存储相应记录主
    8.索引的底层实现(B+树,为何不采用红黑树,B树)重点
    树 区别
    红黑树 增加,删除,红黑树会进行频繁的调整,来保证红黑树的性质,浪费时间
    B树也就是B-树 B树,查询性能不稳定,查询结果高度不致,每个结点保存指向真实数据的指针,相比B+树每一层每屋存储的元素更多,显得更高一点。
    B+树 B+树相比较于另外两种树,显得更矮更宽,查询层次更浅
    9.B+树的实现
    一个m阶的B+树具有如下几个特征:
    1.有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
    2.所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
    3.所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素
    10.为什么使用B+Tree
    索引查找过程中就要产生磁盘I/O消耗,主要看IO次数,和磁盘存取原理有关。
    根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理,
    将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入
    局部性原理与磁盘预读
    11.Sql的优化
    1.sql尽量使用索引,而且查询要走索引

    2.对sql语句优化

    子查询变成left join
    limit 分布优化,先利用ID定位,再分页
    or条件优化,多个or条件可以用union all对结果进行合并(union all结果可能重复)
    不必要的排序
    where代替having,having 检索完所有记录,才进行过滤
    避免嵌套查询
    对多个字段进行等值查询时,联合索引
    12.索引最左前缀问题
    如果对三个字段建立联合索引,如果第二个字段没有使用索引,第三个字段也使用不到索引了
    13.索引分类,索引失效条件
    索引类型 概念
    普通索引 最基本的索引,没有任何限制
    唯一索引 与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
    主键索引 它是一种特殊的唯一索引,不允许有空值。
    全文索引 针对较大的数据,生成全文索引很耗时好空间。
    组合索引 为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则
    失效条件
    条件是or,如果还想让or条件生效,给or每个字段加个索引
    like查询,以%开发
    内部函数
    对索引列进行计算
    is null不会用,is not null 会用
    14.数据库的主从复制
    复制方式 操作
    异步复制 默认异步复制,容易造成主库数据和从库不一致,一个数据库为Master,一个数据库为slave,通过Binlog日志,slave两个线程,一个线程去读master binlog日志,写到自己的中继日志一个线程解析日志,执行sql,master启动一个线程,给slave传递binlog日志
    半同步复制 只有把master发送的binlog日志写到slave的中继日志,这时主库,才返回操作完成的反馈,性能有一定降低
    并行操作 slave 多个线程去请求binlog日志
    15.long_query怎么解决
    设置参数,开启慢日志功能,得到耗时超过一定时间的sql
    16.varchar和char的使用场景
    类型 使用场景
    varchar 字符长度经常变的
    char 用字符长度固定的
    17.数据库连接池的作用
    维护一定数量的连接,减少创建连接的时间
    更快的响应时间
    统一的管理
    19.分库分表,主从复制,读写分离
    读写分离,读从库,写主库
    spring配置两个数据库,通过AOP(面向切面编程),在写或读方法前面进行判断得到动态切换数据源。
    20.数据库三范式
    级别 概念
    1NF 属性不可分
    2NF 非主键属性,完全依赖于主键属性
    3NF 非主键属性无传递依赖
    21.关系型数据库和非关系型数据库区别
    关系型数据库

    优点

    1、容易理解:二维表结构是非常贴近逻辑世界一个概念,关系模型相对网状、层次等其他模型来说更容易理解;

    2、使用方便:通用的SQL语言使得操作关系型数据库非常方便;
    3、易于维护:丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率;
    4、支持SQL,可用于复杂的查询。
    5.支持事务

    缺点
    1、为了维护一致性所付出的巨大代价就是其读写性能比较差;
    2、固定的表结构;
    3、不支持高并发读写需求;
    4、不支持海量数据的高效率读写
    非关系型数据库

    1、使用键值对存储数据;
    2、分布式;
    优点
    无需经过sql层的解析,读写性能很高
    基于键值对,数据没有耦合性,容易扩展
    存储数据的格式:nosql的存储格式是key,value形式
    缺点
    不提供sql支持
    22.数据库中join的left join , inner join, cross join
    1.以A,B两张表为例
    A left join B
    选出A的所有记录,B表中没有的以null 代替
    right join 同理

    2.inner join
    A,B有交集的记录

    3.cross join (笛卡尔积)
    A中的每一条记录和B中的每一条记录生成一条记录
    例如A中有4条,B中有4条,cross join 就有16条记录
    23.有哪些锁,select时怎么加排它锁
    锁 概念
    乐观锁 自己实现,通过版本号
    悲观锁 共享锁,多个事务,只能读不能写,加 lock in share mode
    排它锁 一个事务,只能写,for update
    行锁 作用于数据行
    表锁 作于用表
    24.死锁怎么解决
    找到进程号,kill 进程
    25.最左匹配原则
    最左匹配原则是针对索引的
    举例来说:两个字段(name,age)建立联合索引,如果where age=12这样的话,是没有利用到索引的,
    这里我们可以简单的理解为先是对name字段的值排序,然后对age的数据排序,如果直接查age的话,这时就没有利用到索引了,
    查询条件where name=‘xxx’ and age=xx 这时的话,就利用到索引了,再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗,
    按照正常的原则来讲是不会利用到的,但是优化器会进行优化,把位置交换下。这个sql也能利用到索引了
    之前的排版有问题

    展开全文
  • 该楼层疑似违规已被系统折叠隐藏此楼查看此楼数据库在笔试的时候会让你写,但是在面试的时候,面试官不会说出题目给你,让你口头回答这道这么做,有时候他可能会问删除用什么命名或者啥的,虽然基础,但是真的有...

    该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

    数据库在笔试题的时候会让你写,但是在面试的时候,面试官不会说出题目给你,让你口头回答这道题这么做,有时候他可能会问删除用什么命名或者啥的,虽然基础,但是真的有可能会问到,今天来看看数据库一些基础的。希望对大家有所帮助。觉得不错的话就点赞哦,转发就更好了

    1、SQL的表连接方式有哪些?

    SQL中连接按结果集分为:内连接,外连接,交叉连接

    内连接:inner join on,两表都满足的组合。内连接分为等值连接,不等连接,自然连接。

    等值连接:两表中相同的列都会出现在结果集中。

    自然连接:两表中具体相同列表的列会合并为同一列出现在结果集中。

    外连接:分为左(外)连接,右(外)连接,全连接

    左(外)连接:A left (outer) join B,以A表为基础,A表的全部数据,B表有的组合,没有的为。

    右(外)连接:A right(outer) join B,以B表为基础,B表的全部数据,A表有的组合,没有的位null。

    全连接:A full (outer) join 两表相同的组合在一起,A表有,B表没有的数据(显示为null),同样B表有,A表没有的显示为null。

    交叉连接:cross join,就是笛卡尔乘积。

    2、三范式

    1NF:表中的字段都是单一属性,不再可分。

    2NF:在1NF的基础上,表中所有的非主属性都必须完全依赖于任意一组候选键,不能仅依赖于候选键中的某个属性。

    3NF:在2NF的基础上,表中所有的属性都不依赖其他非主属性。

    简单的说就是:1NF表示每个属性不可分割,2NF表示非主属性不存在对主键的部分依赖,3NF表示不存在非主属性对主键的依赖传递。

    3、表的操作

    表的创建:create table 表名 (列名1 类型 约束,列2 类型 约束…)

    表的删除:drop table 表名

    表的更改(结构的更改,不是记录的更新):alter table 表名 add|drop 列名|约

    束名

    插入记录:insert into 表名…values…

    更新记录:update 表名 set 列名=值 where 条件

    删除记录:delete from 表名 where 条件

    4、数据的完整性

    数据完整性指的是存储在数据库中的数据的一致性和准确性。

    完整性分类:

    (1 )实体完整性:主键值必须唯一且非空。(主键约束)

    (2)引用完整性(也叫参照完整性):外键要么为空,要么引用主表中存在的记录。(外键约束)。

    (3)用户自定义完整性:针对某一具体关系数据库中的约束条件。

    5、SQL的查询优化

    (1)从表连接的角度优化:尽量使用内连接,因为内连接是两表都满足的行的组合,而外连接是以其中一个表的全部为基准。

    (2)尽量使用存储过程代替临时写SQL语句:因为存储过程是预先编译好的SQL语句的集合,这样可以减少编译时间。

    (3)从索引的角度优化:对那些常用的查询字段简历索引,这样查询时值进行索引扫描,不读取数据块。

    (4)还有一些常用的select优化技巧:

    (5)A、只查询那些需要访问的字段,来代替select*

    B、将过滤记录越多的where语句向前移:在一个SQL语句中,如果一个where条件过滤的数据库记录越多,定位越准确,则该where条件越应该前移。

    6、索引的作用,聚集索引与非聚集索引的区别

    索引是一个数据库对象,使用索引,可以是数据库程序无须对整个数据进行扫描,就可以在其中找到目标数据,从而提高查找效率。索引的底层采用的是B树。

    聚集索引:根据记录的key再表中排序数据行。

    非聚集索引:独立于记录的结构,非聚集所以包含的key,且每个键值项都有指向该简直的数据行的指针。

    聚集索引与非聚集索引的区别:

    (1)聚集索引的物理存储按索引排序,非聚集所以的物理存储不按索引排序。

    (2) 聚集索引插入,更新数据的速度比非聚集索引慢,单查询速度更快。

    (3) 聚集索引的叶级结点保存的是时间的数据项,而非聚集结点的叶级结点保存的是指向数据项的指针。

    (4)一个表只能有一个聚集索引(因为只有一种排序方式),但可以有多个非聚集索引。

    7、存储过程与函数的区别

    (1)函数有返回值,存储过程没有返回值。

    (2) 因为存储过程没有返回值,所以不能将存储过程的执行结果赋值给变量;函数有返回值类型,调用函数时,可以将函数的执行结果赋值给变量。也就是说,函数可以在select语句中使用,而存储过程则不能。

    展开全文
  • MySQL数据库常见面试题总结

    万次阅读 多人点赞 2021-03-01 02:42:40
    1、数据库中的范式: (1)第一范式(1NF):指表的列不可再分,数据库中表的每一列都是不可分割的基本数据项,同一列中不能有多个值; (2)第二范式(2NF):在 1NF 的基础上,还包含两部分的内容:一是表必须有...

    1、数据库的常用范式:

    • 第一范式(1NF):指表的列不可再分,数据库中表的每一列都是不可分割的基本数据项,同一列中不能有多个值;
    • 第二范式(2NF):在 1NF 的基础上,还包含两部分的内容:一是表必须有一个主键;二是表中非主键列必须完全依赖于主键,不能只依赖于主键的一部分;
    • 第三范式(3NF):在 2NF 的基础上,消除非主键列对主键的传递依赖,非主键列必须直接依赖于主键。
    • BC范式(BCNF):在 3NF 的基础上,消除主属性对于码部分的传递依赖

    2、SQL语句的执行过程:

    2.1、客户端的数据库驱动与数据库连接池:

    (1)客户端与数据库进行通信前,通过数据库驱动与MySQL建立连接,建立完成之后,就发送SQL语句

    (2)为了减少频繁创建和销毁连接造成系统性能的下降,通过数据库连接池维护一定数量的连接线程,当需要进行连接时,就直接从连接池中获取,使用完毕之后,再归还给连接池。常见的数据库连接池有 Druid、C3P0、DBCP

    2.2、MySQL架构的Server层的执行过程:

    (1)连接器:主要负责跟客户端建立连接、获取权限、维持和管理连接

    (2)查询缓存:优先在缓存中进行查询,如果查到了则直接返回,如果缓存中查询不到,在去数据库中查询。

    MySQL缓存是默认关闭的,也就是说不推荐使用缓存,并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的:

    • 先说下缓存中数据存储格式:key(sql语句)- value(数据值),所以如果SQL语句(key)只要存在一点不同之处就会直接进行数据库查询了;
    • 由于表中的数据不是一成不变的,大多数是经常变化的,而当数据库中的数据变化了,那么相应的与此表相关的缓存数据就需要移除掉;

    (3)解析器/分析器:分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析,最终得到抽象语法树,然后再使用预处理器对抽象语法树进行语义校验,判断抽象语法树中的表是否存在,如果存在的话,在接着判断select投影列字段是否在表中存在等。

    (4)优化器:主要将SQL经过词法解析、语法解析后得到的语法树,通过数据字典和统计信息的内容,再经过一系列运算 ,最终得出一个执行计划,包括选择使用哪个索引

    在分析是否走索引查询时,是通过进行动态数据采样统计分析出来;只要是统计分析出来的,那就可能会存在分析错误的情况,所以在SQL执行不走索引时,也要考虑到这方面的因素

    (5)执行器:根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据,完成SQL的执行。

    2.3、Innodb存储引擎的执行过程:

    • (1)首先MySQL执行器根据 执行计划 调用存储引擎的API查询数据
    • (2)存储引擎先从缓存池buffer pool中查询数据,如果没有就会去磁盘中查询,如果查询到了就将其放到缓存池中
    • (3)在数据加载到 Buffer Pool 的同时,会将这条数据的原始记录保存到 undo 日志文件中
    • (4)innodb 会在 Buffer Pool 中执行更新操作
    • (5)更新后的数据会记录在 redo log buffer 中
    • (6)提交事务在提交的同时会做以下三件事
    • (7)(第一件事)将redo log buffer中的数据刷入到redo log文件中
    • (8)(第二件事)将本次操作记录写入到 bin log文件中
    • (9)(第三件事)将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中,同时在 redo log 最后添加 commit 标记
    • (10)使用一个后台线程,它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中,这样就将内存和数据库的数据保持统一了

     详细内容请阅读这篇文章:MySQL数据库:SQL语句的执行过程_张维鹏的博客-CSDN博客_mysql数据库怎么执行sql语句

    3、常用的存储引擎?InnoDB与MyISAM的区别?

    存储引擎是对底层物理数据执行实际操作的组件,为Server服务层提供各种操作数据的API。常用的存储引擎有InnoDB、MyISAM、Memory。这里我们主要介绍InnoDB 与 MyISAM 的区别:

    (1)事务:MyISAM不支持事务,InnoDB支持事务

    (2)锁级别:MyISAM只支持表级锁,InnoDB支持行级锁和表级锁,默认使用行级锁,但是行锁只有通过索引查询数据才会使用,否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况,但是表级锁不存在死锁

    (3)主键和外键:MyISAM 允许没有任何索引和主键的表存在,不支持外键。InnoDB的主键不能为空且支持主键自增长,如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键,支持外键完整性约束

    (4)索引结构:MyISAM 和 InnoDB 都是使用B+树索引,MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址,而是保存该行的所有数据内容,而辅助索引的Data域保存的则是主索引的值。

    由于InnoDB的辅助索引保存的是主键索引的值,所以使用辅助索引需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。这也是为什么不建议使用过长的字段作为主键的原因:由于辅助索引包含主键列,所以,如果主键使用过长的字段,将会导致其他辅助索变得更大,所以争取尽量把主键定义得小一些。

    (5)全文索引:MyISAM支持全文索引,InnoDB在5.6版本之前不支持全文索引,5.6版本及之后的版本开始支持全文索引

    (6)表的具体行数:

    • ① MyISAM:保存有表的总行数,如果使用 select count() from table 会直接取出出该值,不需要进行全表扫描。
    • ② InnoDB:没有保存表的总行数,如果使用 select count() from table 需要会遍历整个表,消耗相当大。

    (7)存储结构:

    • ① MyISAM会在磁盘上存储成三个文件:.frm文件存储表定义,.MYD文件存储数据,.MYI文件存储索引。
    • ② InnoDB:把数据和索引存放在表空间里面,所有的表都保存在同一个数据文件中,InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    (8)存储空间:

    • ① MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
    • ② InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    (9)适用场景:

    • ① 如果需要提供回滚、崩溃恢复能力的ACID事务能力,并要求实现行锁级别并发控制,InnoDB是一个好的选择;
    • ② 如果数据表主要用来查询记录,读操作远远多于写操作且不需要数据库事务的支持,则MyISAM引擎能提供较高的处理效率;

    备注:在mysql8.0版本中已经废弃了MyISAM存储引擎

    4、事务的ACID与实现原理?

    数据库的事务是并发控制的基本单位,是指逻辑上的一组操作,要么全部执行,要么全部不执行。

    4.1、事务的ACID:

    • (1)原子性:事务是一个不可分割的工作单元,事务里的操作要么都成功,要么都失败,如果事务执行失败,则需要进行回滚。
    • (2)隔离性:事务的所操作的数据在提交之前,对其他事务的可见程度。
    • (3)持久性:一旦事务提交,它对数据库中数据的改变就是永久的。
    • (4)一致性:事务不能破坏数据的完整性和业务的一致性。例如在转账时,不管事务成功还是失败,双方钱的总额不变。

    4.2、ACID的实现原理:

    4.2.1、原子性:原子性是通过MySQL的回滚日志undo log实现的:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

    4.2.2、隔离性:

    (1)事务的隔离级别:

    为保证在并发环境下读取数据的完整性和一致性,数据库提供了四种事务隔离级别,隔离级别越高,越能保证数据的完整性和一致性,但对高并发性能影响也越大,执行效率越低。(四种隔离级别从上往下依次升高)

    • 读未提交:允许事务在执行过程中,读取其他事务尚未提交的数据;
    • 读已提交:允许事务在执行过程中读取其他事务已经提交的数据;
    • 可重复读(默认级别):在同一个事务内,任意时刻的查询结果都是一致的;
    • 读序列化:所有事务逐个依次执行,每次读都需要获取表级共享锁,读写会相互阻塞。

    (2)事务的并发问题:

    如果不考虑事务的隔离性,在事务并发的环境下,可能存在问题有:

    • 更新丢失:两个或多个事务操作相同的数据,然后基于选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题:最后的更新覆盖了其他事务所做的更新。
    • 脏读:指事务A正在访问数据,并且对数据进行了修改(事务未提交),这时,事务B也使用这个数据,后来事务A撤销回滚,并把修改后的数据恢复原值,B读到的数据就与数据库中的数据不一致,即B读到的数据是脏数据。
    • 不可重复读:在一个事务内,多次读取同一个数据,但是由于另一个事务在此期间对这个数据做了修改并提交,导致前后读取到的数据不一致;
    • 幻读:在一个事务中,先后两次进行读取相同的数据(一般是范围查询),但由于另一个事务新增或者删除了数据,导致前后两次结果不一致。

    不同的事务隔离级别,在并发环境会存在不同的并发问题:

    (3)事务隔离性的实现原理:

    Innodb事务的隔离级别是由MVVC和锁机制实现的:

    ① MVCC(Multi-Version Concurrency Control,多版本并发控制)是 MySQL 的 InnoDB 存储引擎实现事务隔离级别的一种具体方式,用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行,无需使用 MVCC。读序列化隔离级别需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。

    MVCC是通过在每行记录后面保存两个隐藏的列来实现的,一个保存了行的事务ID,一个保存了行的回滚段指针。每开始一个新的事务,都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中,而回滚段的指针有该行记录上的所有版本数据,在undo log回滚日志中通过链表形式组织,也就是说该值实际指向undo log中该行的历史记录链表。

    在并发访问数据库时,对正在事务中的数据做MVCC多版本的管理,以避免写操作阻塞读操作,并且可以通过比较版本解决快照读方式的幻读问题,但对于当前读的幻读,MVCC并不能解决,需要通过临键锁来解决。

    ② 锁机制:

    MySQL锁机制的基本工作原理就是:事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据;在该事务操作期间,这部分的数据是锁定,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。

    • 排它锁解决脏读
    • 共享锁解决不可重复读
    • 临键锁解决幻读

    4.2.3、持久性:

    持久性的依靠redo log日志实现在执行SQL时会保存已执行的SQL语句到一个redo log文件,但是为了提高效率,将数据写入到redo log之前,会先写入到内存中的redo log buffer缓存区中。写入过程如下:当向数据库写入数据时,执行过程会首先写入redo log buffer,redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中,这一过程称为刷盘(即redo log buffer写日志到磁盘的redo log file中 )。

    redo log buffer的使用可以大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时redo log buffer中修改的数据在内存还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了确保事务的持久性,在当事务提交时,会调用fsync接口对redo log进行刷盘 ,刷新频率由 innodb_flush_log_at_trx_commit变量来控制的:

    • 0:表示不刷入磁盘;
    • 1:事务每次提交的时候,就把缓冲池中的数据刷新到磁盘中;
    • 2:提交事务的时候,把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去,而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

    4.2.4、一致性:

    一致性指的是事务不能破坏数据的完整性和业务的一致性 :

    • 数据的完整性: 实体完整性、列完整性(如字段的类型、大小、长度要符合要求)、外键约束等

    • 业务的一致性:例如在银行转账时,不管事务成功还是失败,双方钱的总额不变。

    该部分详情可参考这篇博客:MySQL数据库:事务和ACID实现原理_张维鹏的博客-CSDN博客

    5、数据库中的锁机制?

    当数据库中多个事务并发存取同一数据的时候,若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。MySQL锁机制的基本工作原理就是,事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据;在该事务操作期间,这部分的数据是锁定,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。

    按照不同的分类方式,锁的种类可以分为以下几种:

    • 按锁的粒度划分:表级锁、行级锁、页级锁; 
    • 按锁的类型划分:共享(锁S锁)、排他锁(X锁);
    • 按锁的使用策略划分:乐观锁、悲观锁;

    5.1、表级锁、行级锁、页级锁:

    • 表级锁:最大粒度的锁级别,发生锁冲突的概率最高,并发度最低,但开销小,加锁快,不会出现死锁;
    • 行级锁:最小粒度的所级别,发生锁冲突的概率最小,并发度最高,但开销大,加锁慢,会发生死锁;
    • 页级锁:锁粒度界于表级锁和行级锁之间,对表级锁和行级锁的折中,并发度一般。开销和加锁时间也界于表锁和行锁之间,会出现死锁;

    不同的存储引擎支持不同的锁机制:

    • InnoDB存储引擎支持行级锁和表级锁,默认情况下使用行级锁,但只有通过索引进行查询数据,才使用行级锁,否就使用表级锁。
    • MyISAM和MEMORY存储引擎采用的是表级锁;
    • BDB存储引擎使用的是页面锁,但也支持表级锁;

    5.2、InnoDB的行锁:

    InnoDB的行锁有两种类型:

    • 共享锁(S锁、读锁):多个事务可以对同一数据行共享一把S锁,但只能进行读不能修改;
    • 排它锁(X锁、写锁):一个事务获取排它锁之后,可以对锁定范围内的数据行执行写操作,在锁定期间,其他事务不能再获取这部分数据行的锁(共享锁、排它锁),只允许获取到排它锁的事务进行更新数据。

    对于update,delete,insert 操作,InnoDB会自动给涉及的数据行加排他锁;对于普通SELECT语句,InnoDB不会加任何锁。

    5.3、InnoDB的表锁与意向锁:

    因为InnoDB引擎允许行锁和表锁共存,实现多粒度的锁机制,但是表锁和行锁虽然锁定范围不同,但是会相互冲突。当你要加表锁时,势必要先遍历该表的所有记录,判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式,MySQL引入了意向锁,来检测表锁和行锁的冲突。

    意向锁也是表级锁,分为读意向锁(IS锁)和写意向锁(IX锁)。当事务要在记录上加上行锁时,则先在表上加上对应的意向锁。之后事务如果想进行锁表,只要先判断是否有意向锁存在,存在时则可快速返回该表不能启用表锁,否则就需要等待,提高效率。

    5.4、InnoDB行锁的实现与临键锁:

    InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据,才能使用行锁,否则将使用表锁。

    在InnoDB中,为了解决幻读的现象,引入了临键锁(next-key)。根据索引,划分为一个个左开右闭的区间。当进行范围查询的时候,若命中索引且能够检索到数据,则锁住记录所在的区间和它的下一个区间。其实,临键锁(Next-Key) = 记录锁(Record Locks) + 间隙锁(Gap Locks)

    • 间隙锁:当使用范围查询而不是精准查询进行检索数据,并请求共享或排它锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙(GAP)。
    • 记录锁:当使用唯一索引,且记录存在的精准查询时,使用记录锁

    5.5、利用锁机制解决并发问题:

    • X锁解决脏读
    • S锁解决不可重复读
    • 临键锁解决幻读

    InnoDB存储引擎锁机制的详细内容和MyISAM存储引擎的锁机制的详细内容可以阅读这篇文章:MySQL数据库:锁机制_张维鹏的博客-CSDN博客_数据库中的锁机制

    6、MySQL索引的实现原理:

    索引本质上就是一种通过减少查询需要遍历行数,加快查询性能的数据结构,避免数据库进行全表扫描,好比书的目录,让你更快的找到内容。(一个表最多16个索引)

    6.1、索引的优缺点:

    (1)索引的优点:

    • 减少查询需要检索的行数,加快查询速度,避免进行全表扫描,这也是创建索引的最主要的原因。
    • 如果索引的数据结构是B+树,在使用分组和排序时,可以显著减少查询中分组和排序的时间。
    • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    (2)索引的缺点:

    • 当对表中的数据进行增加、删除和修改时,索引也要进行更新,维护的耗时随着数据量的增加而增加。
    • 索引需要占用物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    6.2、索引的使用场景:

    (1)在哪些列上面创建索引:

    • WHERE子句中经常出现的列上面创建索引,加快条件的判断速度。
    • 按范围存取的列或者在group by或order by中使用的列,因为索引已经排序,这样可以利用索引加快排序查询时间。
    • 经常用于连接的列上,这些列主要是一些外键,可以加快连接的速度;
    • 作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

    (2)不在哪些列建索引?

    • 区分度不高的列。由于这些列的取值很少,例如性别,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
    • 在查询中很少的列不应该创建索引。由于这些列很少使用到,但增加了索引,反而降低了系统的维护速度和增大了空间需求。
    • 当添加索引造成修改成本的提高 远远大于 检索性能的提高时,不应该创建索引。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。
    • 定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大,要么取值很少。

    6.3、 索引的分类:

    (1)普通索引、唯一索引、主键索引、全文索引、组合索引。

    • 普通索引:最基本的索引,没有任何限制
    • 唯一索引:但索引列的值必须唯一,允许有空值,可以有多个NULL值。如果是组合索引,则列值的组合必须唯一。
    • 主键索引:一种特殊的唯一索引,不允许有空值。
    • 全文索引:全文索引仅可用于 MyISAM 表,并只支持从CHAR、VARCHAR或TEXT类型,用于替代效率较低的like 模糊匹配操作,而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
    • 组合索引:主要是为了提高mysql效率,创建组合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    (2)聚簇索引与非聚簇索引:

    如果按数据存储的物理顺序与索引值的顺序分类,可以将索引分为聚簇索引与非聚簇索引两类:

    • 聚簇索引:表中数据存储的物理顺序与索引值的顺序一致,一个基本表最多只能有一个聚簇索引,更新聚簇索引列上的数据时,往往导致表中记录的物理顺序的变更,代价较大,因此对于经常更新的列不宜建立聚簇索引
    • 非聚簇索引:表中数据的物理顺序与索引值的顺序不一致的索引组织,一个基本表可以有多个聚簇索引。

    6.4、索引的数据结构:

    常见的索引的数据结构有:B+Tree、Hash索引。

    (1)Hash索引:MySQL中只有Memory存储引擎支持hash索引,是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来,因此查询效率非常高,可以一次定位。

    hash索引的缺点:

    • Hash索引仅能满足等值的查询,不能满足范围查询、排序。因为数据在经过Hash算法后,其大小关系就可能发生变化。
    • 当创建组合索引时,不能只使用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值,所以对单独列数据计算Hash值是没有意义的。
    • 当发生Hash碰撞时,Hash索引不能避免表数据的扫描。因为仅仅比较Hash值是不够的,需要比较实际的值以判定是否符合要求。

    (2)B+Tree索引:B+Tree是mysql使用最频繁的一个索引数据结构,是Innodb和Myisam存储引擎模式的索引类型。B+Tree索引在查找时需要从根节点到叶节点进行多次IO操作,在查询速度比不上Hash索引,但是更适合排序等操作。

    B+Tree索引的优点:

    • 页内节点不存储内容,每次IO可以读取更多的行,大大减少磁盘I/O读取次数
    • 带顺序访问指针的B+Tree:B+Tree所有索引数据都存储在叶子结点上,并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针,这样做是为了提高区间查询效率。

     6.5、为什么使用B+Tree作为索引:

    索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,磁盘I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。

    (1)局部性原理与程序预读:

    由于磁盘本身存取就比主存慢很多,再加上机械运动耗费,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

    由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。预读的长度一般为页的整倍数。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

    (2)B+Tree索引的性能分析:

    上文说过一般使用磁盘I/O次数评价索引结构的优劣。我们先从B树分析,B树检索一次最多需要访问h个节点,同时,数据库巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,即每次新建节点时,直接申请一个页的空间,这样就保证一个节点在物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,这样就实现了每个节点只需要一次I/O就可以完全载入。B树中一次检索最多需要h-1次I/O(根节点常驻内存),时间复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小。综上所述,用B树作为索引结构效率是非常高的。

    而红黑树这种结构,虽然时间复杂度也为O(h),但是h明显要深的多,并且由于逻辑上很近的节点,在物理上可能很远,无法利用局部性,所以IO效率明显比B树差很多。

    另外,B+Tree更适合作为索引的数据结构,原因和内节点出度d有关。从上面分析可以看到,d越大索引的性能越好,而出度d的上限取决于节点内key和data的大小,由于B+Tree内节点去掉了data域,因此可以拥有更大的出度,磁盘IO的次数也就更少了。

    (3)B+树索引 和 B树索引 的对比?

    根据B-Tree 和 B+Tree的结构,我们可以发现B+树相比于B树,在文件系统或者数据库系统当中,更有优势,原因如下:

    • (1)B+树有利于对数据库的扫描:B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题,而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描,所以范围查询、排序等操作,B+树有着更高的性能。
    • (2)B+树的磁盘IO代价更低:B+树的内部结点的data域并没有存储数据,因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多,相对来说I/O读写次数也就降低了。
    • (3)B+树的查询效率更加稳定:由于B+树的内部结点只是叶子结点中关键字的索引,并不存储数据。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    (4)MySQL的 InnoDB 和 MyISAM 存储引擎中B+Tree索引的实现?

    MyISAM和InnoDB都是使用B+树索引,MyISAM的主键索引和辅助索引的Data域都是保存行的地址,但是InnoDB的主键索引保存的不是行的地址,而是保存该行的所有所有数据,而辅助索引的Data域保存的则是主索引的值。

    索引的长度限制:

    • 对于 Innodb 的组合索引,如果各个列中的长度超过767字节的,则会对超过767字节的列取前缀索引;对于 Innodb 的单列索引,如果列的长度超过767的,则取前缀索引(取前255字符)
    • 对于 MyISAM 的组合索引,所创建的索引长度和不能超过1000 bytes,否则会报错,创建失败;对于 MyISAM 的单列索引,最大长度也不能超过1000,否则会报警,但是创建成功,最终创建的是前缀索引(取前333个字符)

    索引的实现原理详细阅读:https://blog.csdn.net/a745233700/article/details/80798181

    7、SQL优化和索引优化、表结构优化:

    (1)MySQL的SQL优化和索引优化:https://blog.csdn.net/a745233700/article/details/84455241

    (2)MySQL的表结构优化:https://blog.csdn.net/a745233700/article/details/84405087

    8、数据库参数优化:

    MySQL属于 IO 密集型的应用程序,主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。所以对于MySQL数据库的参数优化上,主要针对减少磁盘IO的参数做优化:比如使用 query_cache_size 调整查询缓存的大小,使用 innodb_buffer_pool_size 调整缓冲区的大小;

    MySQL的参数优化:https://blog.csdn.net/a745233700/article/details/114506553

    9、explain的执行计划:

    执行计划是SQL语句经过查询分析器后得到的 抽象语法树 和 相关表的统计信息 作出的一个查询方案,这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果,所以可能会存在分析错误的情况,也就是存在执行计划并不是最优的情况。通过explain关键字知道MySQL是如何执行SQL查询语句的,分析select 语句的性能瓶颈,从而改进我们的查询,explain的结果如下:

    重要的有id、type、key、key_len、rows、extra:

    (1)id:id列可以理解为SQL执行顺序的标识,有几个select 就有几个id。

    • id值不同:id值越大优先级越高,越先被执行;
    • id值相同:从上往下依次执行;
    • id列为null:表示这是一个结果集,不需要使用它来进行查询。

    (2)select_type:查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询;

    (3)table:表示 explain 的一行正在访问哪个表

    (4)type:访问类型,即MySQL决定如何查找表中的行。依次从好到差:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,除了all之外,其他的 type 类型都可以使用到索引,除了 index_merge 之外,其他的type只可以用到一个索引。一般要求type为 ref 级别,范围查找需要达到 range 级别。

    • system:表中只有一条数据匹配(等于系统表),可以看成 const 类型的特例
    • const:通过索引一次就找到了,表示使用主键索引或者唯一索引
    • eq_ref:主键或者唯一索引中的字段被用于连接使用,只会返回一行匹配的数据
    • ref:普通索引扫描,可能返回多个符合查询条件的行。
    • fulltext:全文索引检索,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引。
    • ref_or_null:与ref方法类似,只是增加了null值的比较。
    • index_merge:表示查询使用了两个以上的索引,索引合并的优化方法,最后取交集或者并集,常见and ,or的条件使用了不同的索引。
    • unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值;
    • index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
    • range:索引范围扫描,常见于使用>,<,between ,in ,like等运算符的查询中。
    • index:索引全表扫描,把索引树从头到尾扫描一遍;
    • all:遍历全表以找到匹配的行(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
    • NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引

    (5)possible_keys:查询时可能使用到的索引

    (6)key:实际使用哪个索引来优化对该表的访问

    (7)key_len:实际上用于优化查询的索引长度,即索引中使用的字节数。通过这个值,可以计算出一个多列索引里实际使用了索引的哪写字段。

    (8)ref:显示哪个字段或者常量与key一起被使用

    (9)rows:根据表统计信息及索引选用情况,大致估算此处查询需要读取的行数,不是精确值。

    (10)extra:其他的一些额外信息

    • using index:使用覆盖索引
    • using index condition:查询的列未被索引覆盖,where筛选条件使用了索引
    • using temporary:用临时表保存中间结果,常用于 group by 和 order by 操作中,通常是因为 group by 的列上没有索引,也有可能是因为同时有group by和order by,但group by和order by的列又不一样,一般看到它说明查询需要优化了
    • using filesort:MySQL有两种方式对查询结果进行排序,一种是使用索引,另一种是filesort(基于快排实现的外部排序,性能比较差),当数据量很大时,这将是一个CPU密集型的过程,所以可以通过建立合适的索引来优化排序的性能

    对explain执行计划详请感兴趣的读者可以阅读这篇文章:https://blog.csdn.net/a745233700/article/details/84335453

    10、MySQL的主从复制:

    10.1、MySQL主从复制的原理:

    Slave从Master获取binlog二进制日志文件,然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作,通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的,因此Slave和Master之间的数据有可能存在延迟的现象,只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成:

    • (1)Slave SQL thread线程:创建用于读取relay log中继日志并执行日志中包含的更新,位于slave端
    • (2)Slave I/O thread线程:读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中,位于slave端:
    • (3)Binlog dump thread线程(也称为IO线程):将bin-log二进制日志中的内容发送到slave服务器,位于master端

    注意:如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程,而每个从服务器上各自有两个线程;

    10.2、主从复制流程:

    • (1)master服务器在执行SQL语句之后,记录在binlog二进制文件中;
    • (2)slave端的IO线程连接上master端,并请求从指定bin log日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。
    • (3)master端在接收到来自slave端的IO线程请求后,通知负责复制进程的IO线程,根据slave端IO线程的请求信息,读取指定binlog日志指定pos节点位置之后的日志信息,然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外,还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
    • (4)slave端的IO线程在接收到master端IO返回的信息后,将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端,并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中(该文件存slave端),以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步;
    • (5)slave端的SQL线程在检测到relay log文件中新增内容后,就马上解析该relay log文件中的内容,然后还原成在master端真实执行的那些SQL语句,再按顺序依次执行这些SQL语句,从而到达master端和slave端的数据一致性;

    10.3、主从复制的好处:

    • (1)读写分离,通过动态增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上执行读功能。
    • (2)提高数据安全,因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据。
    • (3)在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能。

    10.4、MySQL支持的复制类型及其优缺点:

    binlog日志文件有两种格式,一种是Statement-Based(基于语句的复制),另一种是Row-Based(基于行的复制)。默认格式为Statement-Based,如果想改变其格式在开启服务的时候使用 -binlog-format 选项,其具体命令如下:

    mysqld_safe –user=msyql –binlog-format=格式 &

    (1)基于语句的复制(Statement-Based):在主服务器上执行的SQL语句,在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时,会自动选着基于行的复制。 

    优点:

    • ① 因为记录的SQL语句,所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件,但这些事件包含的情况只是对数据库进行改变的操作,例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
    • ② binlog日志文件记录了所有的改变数据库的语句,所以此文件可以作为数据库的审核依据。

    缺点:

    • ① 不安全,不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如:对于 delete 或者 update 语句,如果使用了 limit 但是并没有 order by ,这就属于非确定性的语句,就不会被记录。
    • ② 对于没有索引条件的update,insert……select 语句,必须锁定更多的数据,降低了数据库的性能。

    (2)基于行的复制(Row-Based):把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从mysql5.0开始支持;

    优点:

    • ① 所有的改变都会被复制,这是最安全的复制方式;
    • ② 对于 update、insert……select等语句锁定更少的行;

    缺点:

    • ① 不能通过binlog日志文件查看什么语句执行了,也无从知道在从服务器上接收到什么语句,我们只能看到什么数据改变。
    • ② 因为记录的是数据,所以说binlog日志文件占用的存储空间要比Statement-based大。
    • ③ 对于数据量大的操作其花费的时间有更长。

    (3)混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

    有关主从复制更详细的内容,请阅读这篇文章:https://blog.csdn.net/a745233700/article/details/85256818

    11、读写分离:

    11.1、实现原理:

    读写分离解决的是,数据库的写操作,影响了查询的效率,适用于读远大于写的场景。读写分离的实现基础是主从复制,主数据库利用主从复制将自身数据的改变同步到从数据库集群中,然后主数据库负责处理写操作(当然也可以执行读操作),从数据库负责处理读操作,不能执行写操作。并可以根据压力情况,部署多个从数据库提高读操作的速度,减少主数据库的压力,提高系统总体的性能。

    11.2、读写分离提高性能的原因:

    • (1)增加物理服务器,负荷分摊;
    • (2)主从只负责各自的写和读,极大程度的缓解X锁和S锁争用;
    • (3)从库可配置MyISAM引擎,提升查询性能以及节约系统开销;
    • (4)主从复制另外一大功能是增加冗余,提高可用性,当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务。

    11.3、Mysql读写分写的实现方式:

    • (1)基于程序代码内部实现:在代码中根据select 、insert进行路由分类。优点是性能较好,因为程序在代码中实现,不需要增加额外的硬件开支,缺点是需要开发人员来实现,运维人员无从下手。
    • (2)基于中间代理层实现:代理一般介于应用服务器和数据库服务器之间,代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库,有以下代表性的代理层。

    12、分库分表:垂直分表、垂直分库、水平分表、水平分库

    读写分离解决的是数据库读写操作的压力,但是没有分散数据库的存储压力,利用分库分表可以解决数据库的储存瓶颈,并提升数据库的查询效率。

    12.1、垂直拆分:

    (1)垂直分表:将一个表按照字段分成多个表,每个表存储其中一部分字段。一般会将常用的字段放到一个表中,将不常用的字段放到另一个表中。

    优点:

    • (1)避免IO竞争减少锁表的概率。因为大的字段效率更低,第一,大字段占用的空间更大,单页内存储的行数变少,会使得IO操作增多;第二数据量大,需要的读取时间长。

    • (2)可以更好地提升热门数据的查询效率。

    (2)垂直分库:按照业务模块的不同,将表拆分到不同的数据库中,适合业务之间的耦合度非常低、业务逻辑清晰的系统。

    优点:

    • 降低业务中的耦合,方便对不同的业务进行分级管理
    • 可以提升IO、数据库连接数、解决单机硬件存储资源的瓶颈问题

    (3)垂直拆分(分库、分表)的缺点:

    • 主键出现冗余,需要管理冗余列
    • 事务的处理变得复杂
    • 仍然存在单表数据量过大的问题

    12.2、水平拆分:

    (1)水平分表:在同一个数据库内,把同一个表的数据按照一定规则拆分到多个表中。

    优点:

    • 解决了单表数据量过大的问题
    • 避免IO竞争并减少锁表的概率

    (2)水平分库:把同一个表的数据按照一定规则拆分到不同的数据库中,不同的数据库可以放到不同的服务器上。

    优点:

    • 解决了单库大数据量的瓶颈问题
    • IO冲突减少,锁的竞争减少,某个数据库出现问题不影响其他数据库,提高了系统的稳定性和可用性

    (3)水平拆分(分表、分库)的缺点:

    • 分片事务一致性难以解决
    • 跨节点JOIN性能差,逻辑会变得复杂
    • 数据扩展难度大,不易维护

    12.3、分库分表存在的问题的解决:

    (1)事务的问题:

    ① 方案一:使用分布式事务:

    • 优点:由数据库管理,简单有效。
    • 缺点:性能代价高,特别是shard越来越多。

    ② 方案二:程序与数据库共同控制实现,原理就是将一个跨多个数据库的分布式事务分解成多个仅存在于单一数据库上面的小事务,并交由应用程序来总体控制各个小事务。

    • 优点:性能上有优势;
    • 缺点:需要在应用程序在事务上做灵活控制。如果使用了spring的事务管理,改动起来会面临一定的困难。

    (2)跨节点 Join 的问题:

    解决该问题的普遍做法是分两次查询实现:在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

    (3)跨节点count,order by,group by,分页和聚合函数问题:

    由于这类问题都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作,解决方案:与解决跨节点join问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个结点的查询可以并行执行,因此速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题。

    12.4、分库分表后,ID键如何处理?

    分库分表后不能每个表的ID都是从1开始,所以需要一个全局ID,设置全局ID主要有以下几种方法:

    (1)UUID:

    • 优点:本地生成ID,不需要远程调用,全局唯一不重复。
    • 缺点:占用空间大,不适合作为索引。

    (2)数据库自增ID:在分库分表表后使用数据库自增ID,需要一个专门用于生成主键的库,每次服务接收到请求,先向这个库中插入一条没有意义的数据,获取一个数据库自增的ID,利用这个ID去分库分表中写数据。

    • 优点:简单易实现。
    • 缺点:在高并发下存在瓶颈。

    (3)Redis生成ID:

    • 优点:不依赖数据库,性能比较好。
    • 缺点:引入新的组件会使得系统复杂度增加

    (4)Twitter的snowflake算法:是一个64位的long型的ID,其中有1bit是不用的,41bit作为毫秒数,10bit作为工作机器ID,12bit作为序列号。

    • 1bit:第一个bit默认为0,因为二进制中第一个bit为1的话为负数,但是ID不能为负数.
    • 41bit:表示的是时间戳,单位是毫秒。
    • 10bit:记录工作机器ID,其中5个bit表示机房ID,5个bit表示机器ID。
    • 12bit:用来记录同一毫秒内产生的不同ID。

    (5)美团的Leaf分布式ID生成系统,美团点评分布式ID生成系统

    13、分区:

    分区就是将表的数据按照特定规则存放在不同的区域,也就是将表的数据文件分割成多个小块,在查询数据的时候,只要知道数据数据存储在哪些区域,然后直接在对应的区域进行查询,不需要对表数据进行全部的查询,提高查询的性能。同时,如果表数据特别大,一个磁盘磁盘放不下时,我们也可以将数据分配到不同的磁盘去,解决存储瓶颈的问题,利用多个磁盘,也能够提高磁盘的IO效率,提高数据库的性能。在使用分区表时,需要注意分区字段必须放在主键或者唯一索引中、每个表最大分区数为1024;常见的分区类型有:Range分区、List分区、Hash分区、Key分区,

    • (1)Range分区:按照连续的区间范围进行分区
    • (2)List分区:按照给定的集合中的值进行选择分区。
    • (3)Hash分区:基于用户定义的表达式的返回值进行分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
    • (4)Key分区:类似于按照HASH分区,区别在于Key分区只支持计算一列或多列,且key分区的哈希函数是由 MySQL 服务器提供。

    (1)表分区的优点:

    ① 可伸缩性:

    • 将分区分在不同磁盘,可以解决单磁盘容量瓶颈问题,存储更多的数据,也能解决单磁盘的IO瓶颈问题。

    ② 提升数据库的性能:

    • 减少数据库检索时需要遍历的数据量,在查询时只需要在数据对应的分区进行查询。
    • 避免Innodb的单个索引的互斥访问限制
    • 对于聚合函数,例如sum()和count(),可以在每个分区进行并行处理,最终只需要统计所有分区得到的结果

    ③ 方便对数据进行运维管理:

    • 方便管理,对于失去保存意义的数据,通过删除对应的分区,达到快速删除的作用。比如删除某一时间的历史数据,直接执行truncate,或者直接drop整个分区,这比detele删除效率更高;
    • 在某些场景下,单个分区表的备份很恢复会更有效率。

    14、主键一般用自增ID还是UUID?

    (1)自增ID:

    使用自增ID的好处:

    • 字段长度较 UUID 会小很多。
    • 数据库自动编号,按顺序存放,利于检索
    • 无需担心主键重复问题

    使用自增ID的缺点:

    • 因为是自增,在某些业务场景下,容易被其他人查到业务量。
    • 发生数据迁移时,或者表合并时会非常麻烦
    • 在高并发的场景下,竞争自增锁会降低数据库的吞吐能力

    (2)UUID:通用唯一标识码,UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

    使用UUID的优点:

    • 唯一标识,不用考虑重复问题,在数据拆分、合并时也能达到全局的唯一性。
    • 可以在应用层生成,提高数据库的吞吐能力。
    • 无需担心业务量泄露的问题。

    使用UUID的缺点:

    • 因为UUID是随机生成的,所以会发生随机IO,影响插入速度,并且会造成硬盘的使用率较低。
    • UUID占用空间较大,建立的索引越多,造成的影响越大。
    • UUID之间比较大小较自增ID慢不少,影响查询速度。

    一般情况下,MySQL推荐使用自增ID,因为在MySQL的 InnoDB 存储引擎中,主键索引是聚簇索引,主键索引的B+树的叶子节点按照顺序存储了主键值及数据,如果主键索引是自增ID,只需要按顺序往后排列即可,如果是UUID,ID是随机生成的,在数据插入时会造成大量的数据移动,产生大量的内存碎片,造成插入性能的下降。

    15、视图View:

    视图是从一个或者多个表(或视图)导出的表,其内容由查询定义。视图是一个虚拟表,数据库中只存储视图的定义,不存储视图对应的数据,在对视图的数据进行操作时,系统根据视图的定义去操作相应的基本表。可以说,视图是在基本表之上建立的表,它的结构和内容都来自基本表,依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

    (1)视图的优点:

    • 简化了操作,把经常使用的数据定义为视图
    • 安全性,用户只能查询和修改能看到的数据
    • 逻辑上的独立性,屏蔽了真实表的结构带来的影响

    (2)视图的缺点:

    • 性能差,数据库必须把对视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,数据库也要把它变成一个复杂的结合体,需要花费一定的时间。

    16、存储过程Procedure:

    SQL语句需要先编译然后执行,而存储过程就是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户通过制定存储过程的名字并给定参数来调用它。

    用程序也可以实现操作数据库的复杂逻辑,那为什么需要存储过程呢?主要是因为使用程序调用API执行,其效率相对较慢,应用程序需通过引擎把SQL语句交给MYSQL引擎来执行,那还不如直接让MySQL负责它最精通最能够完成的工作。

    存储过程的优点:

    • (1)标准组件式编程:存储过程创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。并且DBA可以随时对存储过程进行修改,对应用程序源代码毫无影响。
    • (2)更快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的,在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
    • (3)增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判杂的断和较复运算。
    • (4)减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
    • (5)作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

    17、触发器Trigger:

    触发器是与表有关的数据库对象,当触发器所在表上出现指定事件并满足定义条件的时候,将执行触发器中定义的语句集合。触发器的特性可以应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程,不同的是存储过程要用call来调用,而触发器不需要使用call,也不需要手工调用,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
     

    18、游标Cursor:

    游标,就是游动的标识,可以充当指针的作用,使用游标可以遍历查询数据库返回的结果集中的所有记录,但是每次只能提取一条记录,即每次只能指向并取出一行的数据,以便进行相应的操作。当你没有使用游标的时候,相当于别人一下给你所有的东西让你拿走;用了游标之后,相当于别人一件一件的给你,这时你可以先看看这个东西好不好,再自己进行选择。


    相关阅读:

    Spring常见面试题总结

    SpringMVC常见面试题总结

    Mybatis常见面试题总结

    MySQL常见面试题总结

    Redis常见面试题总结

    RabbitMQ消息队列常见面试题总结

    ElasticSearch搜索引擎常见面试题总结

    计算机网络常见面试题总结

    操作系统常见面试题总结

    Java基础、集合、多线程常见面试题总结

    Java虚拟机常见面试题总结

    Java常见设计模式总结

    海量数据处理的方法总结

    展开全文
  • MySQL数据库常见面试题 小编这里带大家理一下数据库中的一些需要掌握的面试题集, 可能讲的不是很详细,可以先知道有这么个东西再去查找详细资料 如由错误还望大佬们指出,小编即可修改完善 下面开始顺藤摸瓜逐步...
  • 数据库常见面试题

    2021-01-07 16:02:37
    数据库设计,牢记数据库设计的要点,面试必备 要使用varchar 不要使用char (varchar是可变字符串) varchar长度尽量使用2的n次方 便于查询,便于使用 字段不能为NULL 尽量使用int类型 字段设计不宜过多 命名规范...
  • 数据库-常见面试题汇总

    万次阅读 2021-03-04 17:51:08
    还有其他锁,见下图: 图片来源:技术面试之:五问乐观锁悲观锁_哔哩哔哩_bilibili X/排他/互斥锁 如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了。 S/读/共享锁 用于所有的只读数据操作。共享锁...
  • 数据库试题阅读指南文章目录1.事务四大特性2.数据库隔离级别3.MYSQL的两种存储引擎区别(事务、锁级别等等),各自的适用场景4.索引有B+索引和hash索引5.聚集索引和非聚集索引6.索引的优缺点,什么时候使用索引,什么...
  • 1. 如何使用SELECT语句找到你正在运行的服务器的版本并打印出当前数据库的名称?答:下面的语句的结果会显示服务器的版本和当前的数据库名称mysql> SELECT VERSION(), DATABASE(); +-------------------------+---...
  • 面试准备:数据库常见面试题汇总

    千次阅读 2020-12-29 12:11:53
    简单解释数据库三范式?2.不满足数据库三范式可能会出现什么情况?3.解释脏读、不可重复读,幻读,更新丢失4. Mysql提供了哪几种事务隔离级别?5.MySQL中varchar与char的区别?6.MySQL中int(11)与int(3)的区别?7....
  • 数据库常见面试题(一)——数据库架构和索引模块1. 数据库架构问题1.1 如何设计一个关系型数据库?2. 索引模块问题2.1 为什么要使用索引?问题2.2 什么样的信息能成为索引?问题2.1 索引的数据结构?扩展一:可以...
  • 给大家准备了一些关于MySQL数据库的经典面试题,可以多参考多学习。 用一句话介绍什么是MySQL? MySQL是一个开源的关系型数据管理系统,用于存取数据、查询、更新和管理数据。 对MySQL数据库去重的关键字是什么?...
  • 数据库常见面试题 什么是事务: 数据库事务是指作为单个逻辑工作单元执行的一系列操作(sql语句),这些操作要么全部执行,要么全部不执行。 事务的四大特性 原子性: 事务中的所有操作是不可再分割的原子单位,...
  • 上期我们讲了索引,MyISAM和InnoDB的选择等相关的数据库面试题目,小伙伴们掌握得怎么样了?这期树懒君决定分享一下分库分表方面的面试题目,这是一个很经典的面试问题哦~ 首先,要知道分库分表是两回事儿,大家可...
  • 数据库工程师常见面试题(文章转载自乐字节) 问题 1:为什么 group by 和 order by 会使查询变慢? 问题 2:delete、truncate 和 drop 的区别? 问题 3:简述数据库的设计过程。 问题 4:插入记录时可以不指定...
  • 事务:是一系列的数据库操作,是数据库应用的基本逻辑单位。事务性质:原子性、  原子性。即不可分割性,事务要么全部被执行,要么就全部不被执行。  一致性或可串性。事务的执行使得数据库从一种正确状态转换...
  • SQL数据库面试题以及答案(50题)

    千次阅读 2021-01-21 23:05:05
    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:学生ID,数据库,企业管理,英语,有效课程数,有效平均分: select sid as 学生id, (SELECT score...
  • MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 MySQL 数据库里,由 MySQL_install_db 脚本初始化。这些权限表分别 user,db,table_priv,columns_priv 和 host。 24、列的字符串类型可以是什么?...
  • 2020字节跳动数据库面试题及答案

    千次阅读 2021-01-13 11:41:08
    第二部分NoSQL部分已发布:2020字节跳动数据库面试题及答案(二)—— NoSQL部分 文章目录 数据库三范式 分别说一下范式和反范式的优缺点 Mysql 数据库索引。B+ 树和 B 树的区别 为什么 B+ 树比 B 树更适合...
  • 本期小树懒来给大家总结一下缓存和数据库一致性相关的面试题,让大家在面试时不再“无话可说”。 一、什么是缓存? 存储速度不同。缓存是将低速存储的结果暂时存储在高速存储中的技术。 ...
  • 目录面试总结问题汇总与答案整理(仅供参考)1. 数据库中的事务是什么2. 并发环境下,事务会带来哪些问题([并发事务引起的脏读、丢失修改、不可重复读、幻读等问题 ]...
  • 数据库备份并打包传递到远程服务器192.168.1.1的/backup目录下 [root@tiejiang ~]# mysqldump -u root -p database > database.sql ;tar -czvf database.tar.gz database.sql ; rsync -avP ./...
  • 面经 - 数据库基础面试题

    热门讨论 2021-05-26 19:05:10
    特别对于初级开发者,面试可能不会去问框架相关知识,但是绝对不会不去考察数据 库知识,这里收集一些常见类型的SQL语句,无论对于平常开发还是准备面试,都会有助益。 基础表结构 student(sno,sname,sage,...
  • Oracle精选面试题1. 查询员工表所有数据, 并说明使用*的缺点答案:select * from emp;使用*的缺点有:查询出了不必要的列;效率上不如直接指定列名。2. 查询职位(JOB)为'PRESIDENT'的员工的工资答案:select * from ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 69,609
精华内容 27,843
关键字:

数据库常见面试题