mysql数据库详解-最全的mysql数据库面试题

作为后端开发工程师，数据库是怎么也绕不过去的一个坎，相信你们在学校的时候也学过什么SQLserver或者其它数据库，最多的可能就是练习写写SQL语句类的东西。

MySQL作为当下最流行的关系型数据库之一，也是面试中的“热门”，面试官不仅会考察你对SQL语句的熟练程度，也会对它的一个底层原理做非常多的考察。只要你简历上写就必问，要是没写，也会问你会不会数据库。所以你自己体会它的重要性。

今天这节内容是对MySQL数据库在面试过程中遇到的一些高频问题做一个总结，可能你看完这篇文章就不需要专门去准备数据库相关的知识了。里面的内容大多是我在面试过程中遇到的，大家都知道近两年是非常非常卷的，一个岗位可能有无数个候选人在面试，如果你想脱颖而出，那么就需要非常强的基础作为你的踏板。OK，不浪费时间说这么多了，大家认真看吧。

一、数据库基础

1、数据库索引为什么要B+树

数据库的索引是使用B+树来实现的。那为什么要用B+树，为什么不用红黑树和B树？

B+树是一种特殊的平衡多路树，是B树的优化改进版本，它把所有的数据都存放在叶节点上，中间节点保存的是索引。这样一来相对于B树来说，减少了数据对中间节点的空间占用，使得中间节点可以存放更多的指针，使得树变得更矮，深度更小，从而减少查询的磁盘IO次数，提高查询效率。另一个是由于叶节点之间有指针连接，所以可以进行范围查询，方便区间访问。

而红黑树是二叉的，它的深度相对B+树来说更大，更大的深度意味着查找次数更多，更频繁的磁盘IO，所以红黑树更适合在内存中进行查找。

B+树更适合操作系统文件索引和数据索引的原因：

1. B+树磁盘读写代价更低，B+树的内部节点没有指向关键字具体信息的指针。因此内部节点相对于B-树更小，如果把所有同一内部节点的关键字放入同一块磁盘当中。盘所能容纳的关键字数量也就更多。一次性读入内存中需要查找的关键字也就越多，相对的IO读写次数就有所降低。
2. B+树查询效率更加稳定。由于非终结点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路径。所有的关键字查询路径长度都是相同的，导致了每一个数据查询的效率相当。

2、MYSQL的引擎对比

2.1、MySQL引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

MySQL存储引擎主要有：MyIsam、InnoDB、Memory、Blackhole、CSV、Performance_Schema、Archive、Federated、Mrg_Myisam。

但是最常用的是 InnoDB和Mylsam。

相关视频推荐

11个题目带你搞懂mysql高级查询

如何提升MySQL读写性能的方案详解

MySQL数据库的索引和事务

需要C/C++ Linux服务器架构师学习资料加qun 812855908 获取（资料包括 C/C++，Linux，golang技术，Nginx，ZeroMQ，MySQL，Redis，fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，TCP/IP，协程，DPDK，ffmpeg 等），免费分享

2.2、InnoDB

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据

库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

适用场景：

• 经常更新的表，适合处理多重并发的更新请求。
• 支持事务。
• 可以从灾难中恢复（通过bin-log日志等）。
• 外键约束。只有他支持外键。
• 支持自动增加列属性auto_increment。

索引结构：

• InnoDB也是B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。
• InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

2.3、Mylsam

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些。MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

适用场景：

• 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
• 不支持外键的表设计。
• 查询速度很快，如果数据库insert和update的操作比较多的话比较适用。
• 整天对表进行加锁的场景。
• MyISAM极度强调快速读取操作。
• MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

缺点：就是不能在表损坏后主动恢复数据。

索引结构：

MyISAM索引结构：MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

2.4、InnoDB和Mylsam的区别：

1.事务：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。

2.性能：MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。

3.行数保存：InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。

4.索引存储：对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持。

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

5.服务器数据备份：InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

6.锁的支持：MyISAM只支持表锁。InnoDB支持表锁、行锁。行锁大幅度提高了多用户并发操作的能力。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

3、MySQL的MVCC机制

MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

4、事务

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。事务是DBMS中最基础的单位，事务不可分割。

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。

1.原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2.一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3.隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。

不同的隔离级别：

• Read Uncommitted（读取未提交[添加中文释义]内容）：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
• Read Committed（读取提交内容）：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
• Repeated Read（可重复读）：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
• Serialization（可串行化）：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

4.持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

5、数据库的三大范式

1. 第一范式：当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式，即属性不可分。
2. 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式。
3. 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，即非主属性不传递依赖于键码。

6、数据库的四种隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

• ISOLATION_READ_UNCOMMITTED：这是事务最低的隔离级别，它充许令外一个事务可以看到这 个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻像读。
• ISOLATION_READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外 一个事务不能读取该事务未提交的数据
• ISOLATION_REPEATABLE_READ：这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻 像读。它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了避免下面的情况产生(不可重 复读)。
• ISOLATION_SERIALIZABLE：这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执 行。除了防止脏读，不可重复读外，还避免了幻像读。

第1级别：Read Uncommitted(读取未提交内容)

(1)所有事务都可以看到其他未提交事务的执行结果。

(2)本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。

(3)该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据。

第2级别：Read Committed(读取提交内容)

(1)这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。

(2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。

(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

导致这种情况的原因可能有：

• 有一个交叉的事务有新的commit，导致了数据的改变;
• 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit。

第3级别：Repeatable Read(可重读)

(1)这是MySQL的默认事务隔离级别。

(2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

(3)此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题。

第4级别：Serializable(可串行化)

(1)这是最高的隔离级别。

(2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。

(3)在这个级别，可能导致大量的超时现象和锁竞争。

7、数据库如何实现四种隔离级别

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

(1) 读未提交 ：select不加锁，可能出现读脏。

(2) 读提交(RC) ：普通select快照读，锁select /update /delete 会使用记录锁，可能出现不可重复读。

(3) 可重复读(RR) ：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录。

(4) 串行化 ：select隐式转化为select ... in share mode，会被update与delete互斥；

InnoDB默认的隔离级别是RR，用得最多的隔离级别是RC。

8、数据库乐观锁和悲观锁

8.1、悲观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

8.2、乐观锁

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁一般来说有以下2种方式：

• 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
• 使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

9、索引

9.1、从物理存储角度:动作描述

1. 聚集索引

聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

辅助索引中，叶结点的data域存放的是对应记录的主键的key。

对于建立辅助索引的表需要先根据辅助索引找到相应的主键，再根据主键在聚集索引中找到相应的记录集。

1. 非聚集索引

非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。

主键索引中，叶节点的data域存放的是数据记录的地址，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。（MYISAM采用此种索引方式）。

1. 区别

• 聚集索引表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而非聚集索引表数据写入的顺序是按写入时间顺序存储的。
• 聚簇索引索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

1. 适用情景

描述	·使用聚簇索引	使用非聚簇索引
列经常被分组排序	是	是
一个或极少不同的值	否	否
返回某范围内的数据	是	否
小数目的不同值	是	否
大数目的不同值	否	是
外键	是	是
主键	是	是
频繁更新的列	否	是
频繁修改索引列	否	是

9.2、从数据结构角度:

9.2.1、b+树索引

优点：

1. 单次请求涉及的磁盘IO次数少（出度d大，且非叶子节点不包含表数据，树的高度小）；
2. 查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；
3. 遍历效率高（从符合条件的某个叶子节点开始遍历即可）；

缺点：

B+树最大的性能问题在于会产生大量的随机IO，主要存在以下两种情况：

1. 主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；
2. 即使主键是有序递增的，大量写请求的分布仍是随机的；

9.2.2、hash索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值 ，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找， 只需一次哈希算法即可立刻定位到相应的位置 ，速度非常快。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于B-Tree 索引。

对比：

• Hash 索引仅仅能满足"=",和"<=>"等值查询，不能使用范围查询。
• Hash 索引无法被用来避免数据的排序操作。
• Hash 索引 不支持多列联合索引的最左匹配规则 ；
• Hash 索引在任何时候都不能避免表扫描。
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有 大量重复键值情况下 ，哈希索引的效率也是极低的，因为存在所谓的 哈希碰撞 问题。

9.3、从逻辑角度:

1. 主键索引：索引值必须唯一，不能为NULL，在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位。普通索引或者单列索引：最普通的索引，没有任何限制。
2. 多列索引（复合索引）：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。复合索引指多个字段 上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合 。
3. 唯一索引或者非唯一索引：与普通索引的不同的是，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
4. 组合索引：平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。在使用查询的时候遵循“最左前缀”：
1. 不按索引最左列开始查询不适用索引。例如对idnex( c1 , c2 , c3 ),使用where c2 = “aaa” and c3 = “bbb”不能使用索引 。
2. 查询中某个列有范围查询，则其右边的所有列都无法使用查询。例如对idnex( c1 , c2 , c3 )， where c1 = “xxx” and c2 like = “aa%” and c3 = “sss”查询只会使用索引的前两列，因为like是范围查询。
3. 不能跳过某个字段进行查询。

使用索引优点：

1. 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性 。
2. 建立索引可以大大提高检索的数据,以及减少表的检索行数 。
3. 在表连接的连接条件，可以加速表与表直接的相连 。
4. 在分组和排序字句进行数据检索,可以减少查询时间中分组和 排序时所消耗的时间(数据库的记录会重新排序) 。
5. 建立索引,在查询中使用索引，可以提高性能。

使用索引缺点：

1. 创建索引和维护索引会耗费时间,随着数据量的增加而增加 。
2. 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。
3. 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

10、drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

1. Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器。
2. Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。
3. Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

11、Sql的优化

1. sql尽量使用索引,而且查询要走索引
2. 对sql语句优化

子查询变成left join
limit 分布优化，先利用ID定位，再分页
or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）
不必要的排序
where代替having,having 检索完所有记录，才进行过滤
避免嵌套查询
对多个字段进行等值查询时，联合索引

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

12.1、关系型数据库

1. 优点
1. 容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解。
2. 使用方便：通用的SQL语言使得操作关系型数据库非常方便。
3. 易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率。
4. 支持SQL，可用于复杂的查询。
5. 支持事务 。
2. 缺点
1. 为了维护一致性所付出的巨大代价就是其读写性能比较差；
2. 固定的表结构；
3. 不支持高并发读写需求；
4. 不支持海量数据的高效率读写

12.2、非关系型数据库

1. 使用键值对存储数据；
2. 分布式；
3. 优点

• 无需经过sql层的解析，读写性能很高
• 基于键值对，数据没有耦合性，容易扩展
• 存储数据的格式：nosql的存储格式是key,value形式

4.缺点

• 不提供sql支持

13、脏读，不可重读，幻读

1.脏读：脏读是指一个事务在处理过程中读取了另一个还没提交的事务的数据。

比如A向B转账100，A的账户减少了100，而B的账户还没来得及修改，此时一个并发的事务访问到了B的账户，就是脏读 。

2.不可重复读：不可重复读是对于数据库中的某一个字段，一个事务多次查询却返回了不同的值，这是由于在查询的间隔中，该字段被另一个事务修改并提交了。

比如第一次查询自己的账户有1000元，此时另一个事务给A的账户增加了1000元，所以A再次读取他的账户得到了2000的结果，跟第一次读取的不一样。

不可重复读与脏读的不同之处在于，脏读是读取了另一个事务没有提交的脏数据，不可重复读是读取了已经提交的数据，实际上并不是一个异常现象。

3.幻读：事务多次读取同一个范围的时候，查询结果的记录数不一样，这是由于在查询的间隔中，另一个事务新增或删除了数据。

比如A公司一共有100个人，第一次查询总人数得到100条记录，此时另一个事务新增了一个人，所以下一次查询得到101条记录。

不可重复度和幻读的不同之处在于，幻读是多次读取的结果行数不同，不可重复度是读取结果的值不同。

避免不可重复读需要锁行，避免幻读则需要锁表。

脏读，不可重复读和幻读都是数据库的读一致性问题，是在并行的过程中出现的问题，必须采用一定的隔离级别解决。

二、数据库进阶

1、MySQL的主从复制

MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

MySQL主从复制的两种情况： 同步复制和异步复制 ，实际复制架构中大部分为异步复制。

复制的基本过程如下：

1. Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
2. Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。
3. Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。
4. Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

2、数据库水平切分与垂直切分

1. 垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性。垂直拆分：单表大数据量依然存在性能瓶颈。
2. 水平拆分，上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分。
3. 通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中。

3、数据库高并发的解决方案

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

4、什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程具有以下特点：

• 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率。
• 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码。
• 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小。
• 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

5、视图？游标？

1. 视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作。特别地，对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。
2. 游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
3. 在操作mysql的时候，我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或多行）。使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

6、超键 候选键 主键 外键

1. 超键： 在关系中能唯一标识元组(数据库中的一条记录)的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
2. 候选键： 是最小超键，即没有冗余元素的超键。
3. 主键： 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合，用户选作元组标识的一个侯选键称为主键。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
4. 外键： 在一个表中存在的另一个表的主键称此表的外键，外键主要是用来描述两个表的关系。

7、E-R图

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

E-R方法是“实体-联系方法”（Entity-Relationship Approach）的简称。它是描述现实世界概念结构模型的有效方法，是表示概念模型的一种方式，用矩形表示实体型，矩形框内写明实体名；用椭圆表示实体的属性，并用无向边将其与相应的实体型连接起来；用菱形表示实体型之间的联系，在菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1:1,1:n或m:n）。

在ER图中有如下四个成分：

矩形框： 表示实体，在框中记入实体名。

菱形框： 表示联系，在框中记入联系名。

椭圆形框： 表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。

连线： 实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1；对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

实体型(Entity)： 具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如学生张三丰、学生李寻欢都是实体。如果是弱实体的话，在矩形外面再套实线矩形。

属性(Attribute)： 实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如学生的姓名、学号、性别、都是属性。如果是多值属性的话，在椭圆形外面再套实线椭圆，如果是派生属性则用虚线椭圆表示。

联系(Relationship)： 联系也称关系，信息世界中反映实体内部或实体之间的联系。实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指不同实体集之间的联系。在E-R图中用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1 : 1，1 : n或m : n），比如老师给学生授课存在授课关系，学生选课存在选课关系。如果是弱实体的联系则在菱形外面再套菱形。

联系可分为以下 3 种类型：

• 一对一联系(1 ∶1)

例如，一个班级有一个班长，而每个班长只在一个班级任职，则班级与班长的联系是一对一的。

• 一对多联系(1 ∶N)

例如，某校教师与课程之间存在一对多的联系“教”，即每位教师可以教多门课程，但是每门课程只能由一位教师来教。

• 多对多联系(M ∶N)

例如，学生与课程间的联系(“学 ”)是多对多的，即一个学生可以学多门课程，而每门课程可以有多个学生来学。联系也可能有属性。例如，学生“ 学” 某门课程所取得的成绩，既不是学生的属性也不是课程的属性。由于“ 成绩” 既依赖于某名特定的学生又依赖于某门特定的课程，所以它是学生与课程之间的联系“ 学”的属性。

作图步骤：

1. 确定所有的实体集合。
2. 选择实体集应包含的属性。
3. 确定实体集之间的联系。
4. 确定实体集的关键字，用下划线在属性上表明关键字的属性组合。
5. 确定联系的类型，在用线将表示联系的菱形框联系到实体集时，在线旁注明联系的类型。

8、数据库的七种锁

1. 行锁（Record Locks） ：行锁一定是作用在索引上的。
2. 间隙锁（Gap Locks）：锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且 间隙锁是不互斥 的，即两个事务可以同时持有包含 共同间隙 的间隙锁。这里的 共同间隙 包括两种场景：其一是两个间隙锁的 间隙区间 完全一 样；其二是一个间隙锁包含的 间隙区间 是另一个间隙锁包含 间隙区间 的 子集 。间隙锁本质上是用于 阻止其他事务在该间隙内插入新记录 ，而 自身事务是允许在该间隙内插入数据的 。也就是说间隙锁的应用场景包括并发 读取 、并发 更新 、并发 删除 和并发 插入 。在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止 幻读 （读后写的场景）。因为这两种隔离级别下只会有 行锁 ，而不会有 间隙锁 。这也是为什么示例中要规定隔离级别为RR的原因。
3. 临键锁（Next-key Locks）：临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止 幻读 ；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止 幻读 。
4. 共享锁/排他锁（Shared and Exclusive Locks） ：共享锁/排他锁都只是 行锁 ，与 间隙锁 无关，这一点很重要，后面还会强调这一点。其中共享锁是一个事务并发 读取 某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一 个事务并发 更新或删除 某一行记录所需要持有的锁，比如select ... for update。不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是 行锁 ，也可能是 间隙锁 ，也可能是 临键锁 ，这取决于数据库的 隔离级别 以及查询的 数据是否存在 。关于这一点，后面分析场景一和场景二的时候还会提到。
5. 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks） ：意向共享锁/意向排他锁属于 表锁 ，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的 前置条件 。
6. 插入意向锁（Insert Intention Locks） ：插入意向锁是一种 特殊 的间隙锁，但不同于间隙锁的是，该锁只用于并发 插入 操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常 重要的差别 是：尽管 插入意向锁 也属于 间隙锁 ，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。这里我们再回顾一下共享锁和排他锁：共享锁用于 读取 操作，而排他锁是用于 更新 或 删除 操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。
7. 自增锁（Auto-inc Locks） ：增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。为了便于介绍innodb_autoinc_lock_mode参数，我们先将需要用到自增锁的Insert语句进行分类：

9、数据库高并发的解决方案

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

10、SQL语句的内部致性过程

• 连接器：客户端先通过连接器连接到 MySQL 服务器。
• 缓存：连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
• 分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 执行器：优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

11、为什么要分库分表

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

12、分布式事务

分布式事务用于在分布式系统中保证不同节点之间的数据一致性。分布式事务的实现有很多种，最具有代表性的是由 Oracle Tuxedo 系统提出的 XA 分布式事务协议。

XA协议包含两阶段提交（2PC）和三阶段提交（3PC）两种 ：

1. 2PC: 在XA分布式事务的 第一 阶段，作为事务协调者的节点会首先向所有的参与者节点发送Prepare请求。在接到Prepare请求之后，每一个参与者节点会各自执行与事务有关的数据更新，写入Undo Log和Redo Log。如果参与者执行成功，暂时不提交事务，而是向事务协调节点返回“完成”消息。

当事务协调者接到了所有参与者的返回消息，整个分布式事务将会进入第二阶段。

在XA分布式事务的 第二 阶段，如果事务协调节点在之前所收到都是正向返回，那么它将会向所有事务参与者发出Commit请求。

接到Commit请求之后，事务参与者节点会各自进行本地的事务提交，并释放锁资源。当本地事务完成。提交后，将会向事务协调者返回“完成”消息。

当事务协调者接收到所有事务参与者的“完成”反馈，整个分布式事务完成。

注意:

在XA的第一阶段，如果某个事务参与者反馈失败消息，说明该节点的本地事务执行不成功，必须回滚。于是在第二阶段，事务协调节点向所有的事务参与者发送Abort请求。接收到Abort请求之后，各个事务参与者节点需要在本地进行事务的回滚操作，回滚操作依照Undo Log来进行。

XA两阶段提交的不足

1.性能问题

XA协议遵循强一致性。在事务执行过程中，各个节点占用着数据库资源，只有当所有节点准备完毕，事务协调者才会通知提交，参与者提交后释放资源。这样的过程有着非常明显的性能问题。

2.协调者单点故障问题

事务协调者是整个XA模型的核心，一旦事务协调者节点挂掉，参与者收不到提交或是回滚通知，参与者会一直处于中间状态无法完成事务。

3.丢失消息导致的不一致问题。

在XA协议的第二个阶段，如果发生局部网络问题，一部分事务参与者收到了提交消息，另一部分事务参与者没收到提交消息，那么就导致了节点之间数据的不一致。

为了解决上诉问题，提供下面几重分布式事务解决方案

1.XA三阶段提交

XA三阶段提交在两阶段提交的基础上增加了CanCommit阶段，并且引入了超时机制。一旦事物参与者迟迟没有接到协调者的commit请求，会自动进行本地commit。这样有效解决了协调者单点故障的问题。但是性能问题和不一致的问题仍然没有根本解决。

2.MQ事务

利用消息中间件来异步完成事务的后一半更新，实现系统的最终一致性。这个方式避免了像XA协议那样的性能问题。

3.TCC事务

TCC事务是Try、Commit、Cancel三种指令的缩写，其逻辑模式类似于XA两阶段提交，但是实现方式是在代码层面来人为实现。