1.事务的ACID特性

原子性(A)

一个事务要么全部提交成功,要么全部失败回滚,不能只执行其中的一部分操作,这就是事务的原子性

一致性(C)

保证事务只能把数据库从一个有效(正确)的状态“转移”到另一个有效(正确)的状态。

有效(正确)状态是指满足我们给数据库定义的一些规则(约束条件),就是当前的状态满足预定的约束就叫做正确的状态

隔离性(I)

事务的隔离性是指在并发环境中,并发的事务时相互隔离的,一个事务的执行不能被其他事务干扰。不同的事务并发操作相同的数据时,每个事务都有各自完成的数据空间,即一个事务内部的操作及使用的数据对其他并发事务时隔离的,并发执行的各个事务之间不能相互干扰。

隔离级别(由低到高):

  1. read uncommitted;(读未提交的)
  2. read committed;(读已经提交的)
  3. repeatable read;(可以重复读)
  4. serializable;(串行化)

持久性(D)

一旦事务提交,那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。–即使发生系统崩溃或机器宕机等故障,只要数据库能够重新启动,那么一定能够将其恢复到事务成功结束的状态

注意:

事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.

2.隔离级别

事务的并发问题

  1. 脏读: A事务读取B事务尚未提交的数据,此时如果B事务发生错误并执行回滚操作,那么A事务读取到的数据就是脏数据。
  2. 不可重复读:事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
  3. 幻读: 指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影”。

注意: 不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要next-key lock(行锁+间隙锁)

Read Uncommitted(读取未提交内容)

在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。

Read Committed(读取提交内容)

这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。

Repeatable Read(可重复读)

这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

Serializable(串行化)

这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

3.范式

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。

第二范式

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。 也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

若某关系R属于第一范式,且每一个非主属性完全函数依赖于任何一个候选码,则关系R属于第二范式。

第三范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖,即非主属性既不传递依赖于码,也不部分依赖于码。

4.聚集索引和非聚集索引、二级索引

聚簇索引的叶子节点就是数据节点,聚簇索引的顺序就是数据的物理存储顺序,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

聚集索引:InnoDB

  • 对于聚集索引,叶子结点即存储了真实的数据行。聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC)和余下的列,

非聚集索引:MYISAM

  • MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
  • 从MYISAM存储的物理文件我们能看出,MYISAM引擎的索引文件(.MYI)和数据文件(.MYD)是相互独立的。

二级索引:

  • 在InnoDb中,强制使用主键作为聚簇索引,B+ Tree叶子节点存储就是主键数据,而二级索引的叶子节点存储的是主键的键值;
  • 在MyISAM中,主键索引与二级索引没什么区别,都是存储的数据行对应的地址。

5.InnoDB为什么使用B+树做索引而不是使用B树

  1. B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。(一个节点代表一个数据块,就需要一次磁盘I/O,所以层数越少,磁盘I/O次数就越少)

  2. B+树的查询效率更加稳定:由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

  3. B+树适合范围查找: 由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。

6.为什么推荐自增id作为主键,而不是自建主键?

B+树为了维护索引的有序性,每插入或者删除更新一条记录的时候,都需要对索引进行更新。

  • 如果是自建索引,则很有可能在随机中间部分进行插入,这就可能造成页分裂,从而进行调整,这是会导致性能下降的,如果是大量的页分裂,就会导致性能急剧下降。
  • 如果使用的是自增id作为主键,由于插入的表中生成的id比索引中的id都大,所以它要么合成到已存在的结点(元素个数未满),要么放入新建的结点中,所以就不存在页分裂了。而且按这种方式存储就能提升页面的最大填充率,不会有页的浪费。

7.主键索引和普通索引,以及它们之间的区别

主键索引和非主键索引的区别是:非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引

8.索引下推

在联合索引中,在支持索引下推(ICP)优化后,MySQL会在取出索引回表的同时,判断是否可以进行where条件过滤再进行索引查询,也就是说提前执行where的部分过滤操作。

9.覆盖索引、联合索引

联合索引: 对多个字段同时建立的索引(有顺序,ABC,ACB是完全不同的两种联合索引。)

覆盖索引: SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。 sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中(联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引。

10.为什么用 B+ 树做索引而不用哈希表做索引?

  1. 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置,这样的话,如果我们要进行模糊查找的话,显然哈希表这种结构是不支持的,只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
  2. 如果我们要进行范围查找,例如查找ID为100 ~ 400的人,哈希表同样不支持,只能遍历全表。
  3. 索引字段通过哈希映射成哈希码,如果很多字段都刚好映射到相同值的哈希码的话,那么形成的索引结构将会是一条很长的链表,这样的话,查找的时间就会大大增加。

11.SQL注入漏洞产生的原因,如何防止?

原因:

  1. 程序编写者在处理应用程序和数据库交互时,使用字符串拼接的方式构造SQL语句。
  2. Web应用程序的开发人员对用户所输入的数据或cookie等内容不进行过滤或验证(即存在注入点)就直接传输给数据库,就可能导致拼接的SQL被执行,获取对数据库的信息以及提权,发生SQL注入攻击。

防止措施:

  1. 分级管理
    • 对用户进行分级管理,严格控制用户的权限,对于普通用户,禁止给予数据库建立、删除、修改等相关权限,只有系统管理员才具有增、删、改、查的权限。
  2. 采用预编译,用set方法传值(简单又有效),而不用字符串拼接的方法;
  3. 使用正则表达式过滤传入的参数(or,union等);
  4. 字符串过滤(insert,delete,select等)
  5. 避免直接向用户显示数据库错误,攻击者可以使用这些错误消息来获取有关数据库的信息。

12.explain之type

结果值从好到坏:
system > const > eq_ref > ref > range > index > ALL

一般来说, 我们需要保证查询至少达到 range 级别, 最好达到ref 。

13.为什么MyISAM会比Innodb的查询速度快

INNODB在做SELECT的时候,要维护的东西比MYISAM引擎多很多:

  1. INNODB要缓存数据块,MYISAM只缓存索引块, 这中间还有换进换出的减少;
  2. innodb寻址要映射到块,再到行,MYISAM记录的直接是文件的OFFSET,定位比INNODB要快
  3. INNODB还需要维护MVCC一致;虽然你的场景没有,但他还是需要去检查和维护MVCC (Multi-Version Concurrency Control)多版本并发控制

14.缓存数据库和远程数据库怎么实现同步?

  • 读的时候,先读缓存,缓存没有的话,就读数据库,然后取出数据后放入缓存,同时返回响应。
  • 更新的时候,先更新数据库,然后再删除缓存。

了解更多→ https://zhuanlan.zhihu.com/p/147028497

15.mysql innodb引擎什么时候表锁,什么时候行锁?

InnoDB基于索引的行锁

  • InnoDB行锁是通过索引上的索引项来实现的,这一点MySQL与Oracle不同,后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者:只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁
  • 在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。 在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。

行锁变表锁的情况:

  • 导致这个的原因第一是sql语句写法问题,没有合理构建和使用索引。如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。
  • 第二个原因是mysql的优化器,有时优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁

16.行锁、间隙锁和next-key锁

行锁

单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。

间隙锁

在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。(范围查询的时候会用,等值查询的时候不需要)

next-key锁

next-key锁其实包含了记录锁和间隙锁,即锁定一个范围,并且锁定记录本身,InnoDB默认加锁方式是next-key 锁。

注意

  • 间隙锁和next-key锁是Innodb在可重复读隔离级别下为了解决幻读问题时引入的锁机制
  • 行锁是为了解决不可重复读(针对更新)的问题,而间隙锁是为了解决幻读(针对插入和删除)的问题

17.mysql日志

在 MySQL 中,有 4 种不同的日志,分别是错误日志、二进制日志
(BINLOG 日志)、查询日志和慢查询日志

(1)错误日志

错误日志是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何
严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。

(2)二进制日志

二进制日志(BINLOG)记录了所有的 增删改操作,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制, 就是通过该binlog实现的。

从底层原理来说,binlog有三个模式:

  1. statement
    • 该日志格式在日志文件中记录的都是SQL语句(statement),每一条对数据进行修改的SQL都会记录在日志文件中。主从复制的时候,从库(slave)会将日志解析为原文本,并在从库重新执行一次。
  2. row
    • 该日志格式在日志文件中记录的是每一行的数据变更,而不是记录SQL语句。主从复制的时候,从库对相同的数据进行修改
  3. mixed
    • 这是目前MySQL默认的日志格式,即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT,但是在一些特殊情况下采用ROW来进行记录。MIXED格式能尽量利用两种模式的优点,而避开他们的缺点。

(3)查询日志

查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。

(4)慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志。long_query_time 默认为 10 秒,最小为 0, 精度可以到微
秒。

18.mysql复制

复制是指将主数据库的DDL(数据定义语言)和DML(数据操纵语言)操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。

mysql的主从复制流程:


流程简述:

Master执行完增删改操作后都会记录binlog日志,当需要同步的时候会主动通知slave节点,slave收到通知后使用IO线程主动去master读取binlog写入relay日志(中转日志),然后使SQL线程完成对relay日志的解析然后入库操作,完成同步。

19.事务还没执行完数据库挂了,重启的时候会发生什么?

在innoDB的存储引擎中,事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时,事务中的操作,都会先写入存储引擎的日志缓冲中,在事务提交之前,这些缓冲的日志都需要提前刷新到磁盘上持久化,这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。

当事务提交之后,在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机,那么当系统重启进行恢复时,就可以根据redo log中记录的日志,把数据库恢复到崩溃前的一个状态。未完成的事务,可以继续提交,也可以选择回滚,这基于恢复的策略而定。

20.undo日志和redo日志分别是干嘛的?

redo日志

redo log重做日志是InnDB存储引擎层的,用来保证事务安全。在事务提交之前,每个修改操作都会记录在redo日志中,并被刷新到磁盘中持久化,保存的是物理日志。防止发生故障的时间点,有脏页未写入磁盘,在重启mysql的时候,根据redo log进行重做从而达到事务的持久性

在系统启动的时候,就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间,它们的Redo Log按语句的执行顺序,依次交替的记录在一起。

undo日志

undo log主要为事务的回滚服务。在事务执行的过程中,除了记录redo log,还会记录一定量的undo log。undo log记录了数据在每个操作前的状态,如果事务执行过程中需要回滚,就可以根据undo log进行回滚操作。单个事务的回滚,只会回滚当前事务做的操作,并不会影响到其他的事务做的操作。

redo log其实保障的是事务的持久性和一致性,而undo log则保障了事务的原子性。

21.mysql的MVCC的实现原理

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突做到即使有读写冲突时,也能做到不加锁,非阻塞并发读。 而这个读指的就是快照读, 而非当前读,当前读实际上是一种加锁的操作,是悲观锁的实现

MVCC 提供了时点(point in time)一致性视图。MVCC 并发控制下的读事务一般使用时间戳或者事务 ID去标记当前读的数据库的状态(版本),读取这个版本的数据。读、写事务相互隔离,不需要加锁。读写并存的时候,写操作会根据目前数据库的状态,创建一个新版本,并发的读则依旧访问旧版本的数据。

MVCC(Multiversion concurrency control) 就是 同一份数据临时保留多版本的一种方式,使得读写操作没有冲突,进而实现并发控制

22.RC,RR级别下的InnoDB快照读有什么不同?

快照读:读取的是记录数据的可见版本(可能是过期的数据),不用加锁,用MVCC解决脏读、不可重复读、幻读的问题;

当前读: 读取的是记录数据的最新版本,并且当前读返回的记录都会加上锁,保证其他事务不会再并发的修改这条记录,使用行锁+间隙锁解决脏读、不可重复读和幻读的问题

正是Read View生成时机的不同,从而造成RC,RR级别下快照读的结果的不同

在RC隔离级别下,是每个快照读都会生成并获取最新的Read View;而在RR隔离级别下,则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

什么是ReadVIew?

ReadVIew是记录当前系统中的所有的活跃事务的一个列表,这个数据结构中存储了当前Read View中最大的事务ID及最小的事务ID。

Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID,还会有两个属性up_limit_id(记录trx_list列表中事务ID最小的ID),low_limit_id(记录快照读那刻系统尚未分配的下一个事务ID(目前已出现过的事务ID的最大值+1))

参考:

https://zhuanlan.zhihu.com/p/64576887

https://www.cnblogs.com/yulibostu/articles/11580136.html

23.视图的作用?

视图是一个虚拟表,是sql的查询结果,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据,在使用视图时动态生成。视图的数据变化会影响到基表,基表的数据变化也会影响到视图[insert update delete ]

作用:

  1. 提高了重用性,对于频繁查询的数据可以生成一个视图;
  2. 对数据库重构,却不影响程序的运行,例如拆表的时候;
  3. 提高了安全性能。可以对不同的用户,设定不同的视图。
  4. 让数据更加清晰。想要什么样的数据,就创建什么样的视图。

24.存储引擎的选择

  • InnoDB : 是Mysql的默认存储引擎,用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高
    的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包含很多的更新、删除操作

    那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定, 还
    可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统,
    InnoDB是最合适的选择。
  • MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发
    性要求不是很高
    ,那么选择这个存储引擎是非常合适的。

25.索引的优势和劣势

优点:

  1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。
  3. 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

缺点:

  1. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  2. 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
  3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的更新速度。

26.什么时候需要创建索引,什么时候不需要创建索引?

什么时候需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
  • 查询中统计或者分组的字段;

什么时候不需要创建索引

  • 频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件
  • where条件里用不到的字段,不创建索引;
  • 表记录太少,不需要创建索引;
  • 经常增删改的表;
  • 数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。

27.数据库主库和从库不一致,常见有这么几种优化方案?

  1. 业务可以接受,系统不优化
  2. 强制读主,高可用主库,用缓存提高读性能
  3. 在cache里记录哪些记录发生过写请求,来选择读主还是读从。缓存可以记录哪个库,哪个表,哪个主键三个信息刚发生过写操作,这条记录的超时时间,设置为“主从同步时延”

命令集合

  • show processlis:查看当前的状态
  • 强制走索引的方式来查询
    • select * from t force index(a) where c < 100 and c < 100000;
  • 查询索引的基数和实际是否符合
    • show index from t;
  • 重新统计索引的基数
    • analyze table t;

—————————————-如有错误,欢迎指正!—————————————-

评论