（秋招/春招）软件开发/软件测试面试题——数据库部分

1.数据库多表查询

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

2.分组、排序、分页取前10条指令关键字

分组查询：group by;

排序：order by asc(默认升序) /desc（降序）

分页查询：

Limit [offset], size

offset代表的是起始的索引条目，默认从0开始

select * from table WHERE … LIMIT 10; #返回前10行

select * from table WHERE … LIMIT 0,10; #返回前10行

select * from table WHERE … LIMIT 10,20; #返回第11行开始的20行数据

3.事务是什么？单条SQL语句是事务吗？

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

事务是单个的工作单元。正在执行的sql 语句只要涉及对数据库数据操作就会有事务，会有自己的事务id。

4.事务的四大特性？

A原子性：是指事务是一个不可分割的工作单元，事务中的操作要么都发生，要么都不发生。
eg：张三给李四转钱，要么张三的钱减少李四的增多，要么两个人的钱都不变。

C一致性：是指事务前后数据的完整性要保持一致。
eg：本来 张三有1000元，李四有1000元，一共2000。张三给李四转账100元，成功：张三900元，李四1100元，一共2000元。

I隔离性：是指是指多个用户并发访问数据库的时候，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数要相互隔离。

D持久性：是指一个事务一旦提交，他对数据库中数据的改变就是就是永久性的。
eg：张三 1000 李四 1000 张三给李四转100 ，张三提交了，数据回滚不了了。

5.Myql中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

6.事务并发处理会产生的问题

丢失更新

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、 每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

脏读

一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据。

不可重复读

一个事务多次读取，结果不一样。

幻读

一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据。

7.数据库的隔离级别

读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读

不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象.

串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

MySQL默认的事务隔离级别为repeatable-read，MySQL 支持4种事务隔离级别。事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持。Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE。

SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异。MySQL中默认事务隔离级别是“可重复读”时，并不会锁住读取到的行。事务隔离级别：未提交读时，写数据只会锁住相应的行。事务隔离级别为：可重复读时，写数据会锁住整张表。事务隔离级别为：串行化时，读写数据都会锁住整张表。

8.说一下Mysql数据库常用的引擎

事务处理上方面：

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

锁级别：

MyISAM：只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

9.说一下 mysql 的锁

从数据操作的类型（读、写）分：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响；

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的颗粒度：

表锁；行锁

表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低。

对MyISAM的表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。

对MyISAM的表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读锁会阻塞写，但不会阻塞读，而写锁则会把读和写都阻塞。

行锁（偏写）：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

Innodb存储引擎由于现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于 MyISAM的表级锁定的当系统并发量较高的时候, Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是, Innodb的行级锁定同样也有其脆弱的面,当我们使用不当的时候,可能会让Innodb的整体性能表现不仅不能比MyISAM高甚至可能会更差.

10.数据库的索引有了解吗，它的优点及应用场景

索引是排好序的快速查找数据结构。

优势：

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本；

通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗。

劣势：

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的；

虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句。

分类：

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

复合索引：即一个索引包含多个列。

应用场景：

哪些情况需要创建索引？

主键自动建立唯一索引；

频繁作为查询的条件的字段应该创建索引；

查询中与其他表关联的字段，外键关系建立索引；

频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重IO负担；

Where条件里用不到的字段不创建索引；

单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）；

查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度；

查询中统计或者分组字段。

哪些情况不需要创建索引？

表记录太少；

经常增删改的表；

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

备注：因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，若是删，则会把索引中以这个字段为名的索引的子集删掉，所以，会对增删改的执行减缓速度，所以，若是这张表增删改多而查询较少的话，就不要创建索引了。更新太频繁地字段不适合创建索引。不会出现在where条件中的字段不该建立索引。

11.如何优化查询

i. 全值匹配我最爱

ii. 最佳左前缀法则，如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

iii. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

iv. 存储引擎不能使用索引中范围条件右边的列

v. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

vi. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

vii. is null,is not null 也无法使用索引

viii. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作。问题：解决like’%字符串%'索引不被使用的方法？？可以使用主键索引；使用覆盖索引，查询字段必须是建立覆盖索引字段；当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

ix. 字符串不加单引号索引失效

x. 少用or,用它连接时会索引失效

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；LIKE百分写最右,覆盖素引不写星；

不等空值还有or，索引失效要少用；VAR引号不可丢,SQL高级也不难!

12.索引有哪些数据结构，分别有什么优缺点

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;

B+树索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的IO访问;

那为什么大家不都用Hash索引而还要使用B+树索引呢？

Hash索引

Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样；

Hash索引无法被用来避免数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样；

Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；

Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据；

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

B+Tree索引

MySQL中，只有HEAP/MEMORY引擎才显示支持Hash索引。

常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

同理，哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

13.gtid方式的主从复制和普通方式的主从复制的区别（优点）

1）普通主从复制：

普通主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。

2） GTID 主从：

MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

基于gtid复制的好处

保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总是会被执行。

不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标。

故障转移到新的master的时候很方便，简化了很多任务。

很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了，那么一定是一致的。

14.基于gtid方式的主从数据库，如果从库挂了会发生什么？

系统会一直尝试去主从复制的一个操作，但是也有一个时间限制，一旦超出那个时间，系统会变为普通的主从复制。

15.第一范式、第二范式、第三范式

第一范式：每个属性不可再分，其实就是没有重复的列。

第二范式：如果关系先满足第一范式，非主键字段完全依赖于主键。

第三范式：如果关系满足第二范式，辅助字段之间不存在任何的依赖关系。