数据库面试题（*****）

1.连接查询（必会）

1.左外连接
（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
2.右外连接
（右外连接）以右表为基准进行查询,右表数据会全部显示出来,右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;

2.聚合函数（必会）

分类 | COUNT：统计行数量 | | | | SUM：获取单个列的求和 | | | | AVG：计算某个列的平均值 | | | | MAX：计算列的最大值 | | | | MIN：计算列的最小值 | |

3.SQL关键字(必会)

|

1.分页 | MySQL的分页关键词limit
SELECT FROM student3 LIMIT 2,6; 查询学生表中数据，从第三条开始显示，显示6条 | | | —- | —- | —- | | 2.分组 | MySQL的分组关键字：group by
SELECT sex, count() FROM student3 GROUP BY sex; | | | 3. 去重 | 去重关键字：distinct
select DISTINCT NAME FROM student3; | |

4.SQL Select 语句完整的执行顺序: (必会)

5.数据库三范式(必会)

6.存储引擎 （高薪常问）*

7.数据库事务（必会）*

事务的ACID四大特性：

事务的隔离级别：

8.索引介绍（高薪必问）*

索引的分类（必会）：

全文索引 | 老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。 | | | | 聚簇索引 | 索引结构和数据一起存放的索引。主键索引属于聚簇索引。查询快，但是更新代价大且依赖有序的数据 | | | | 非聚簇索引 | 索引结构和数据分开存放的索引。更新代价小，但是可能会有二次查询， | | | | 覆盖索引 | 一个索引包含所需要查询的字段的值。 | | |

索引的底层实现原理（高薪常问）：*
1）MySQL存在哪些索引？

2）请简单谈谈B+Tree底层原理
根节点：没有父节点的节点。
子节点：有父节点和子节点的节点。
叶子节点：有父节点，但是没有子节点的节点。
数据结构：二叉树。
索引的本质也是一张表。

BTree的实现原理：

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：
● 树中每个节点最多包含m个孩子。
● 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
● 若根节点不是叶子节点，则至少有两个孩子。
● 所有的叶子节点都在同一层。
● 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1
以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。
插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。
演变过程如下：
1). 插入前4个字母 C N G A
2). 插入H，n>4，中间元素G字母向上分裂到新的节点
3). 插入E，K，Q不需要分裂
4). 插入M，中间元素M字母向上分裂到父节点G
5). 插入F，W，L，T不需要分裂
6). 插入Z，中间元素T向上分裂到父节点中
7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂
8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂
到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+Tree和BTree的区别：
1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
3). 所有的非叶子节点都可以看作是key的索引部分。
MySql索引数据结构对经典的B+Tree进行了优化, 在原B+Tree的基础上, 增加了一个指向相邻叶子节点的链表指针（可以叫双向链表）, 就形成了带有顺序指针的B+Tree, 提高区间访问的性能。

索引创建语法：**create index idx_表名_列名 on 表名（列名）；**

9.MySql优化(高薪常问)*

索引设计原则：

SQL优化的步骤：*
定位和分析问题：
1）查看SQL的执行频率：**通过 show [session|global] status 命令来定位；**session（当前连接）global（自数据库上次启动至今），Com_xxx 表示每个 xxx 语句执行的次数
Com : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb : 这几个参数只是针对InnoDB 存储引擎的。
2）定位慢查询的SQL：通过慢查询日志和**show processlist**命令（推荐）；

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据.

3）分析SQL效率低的原因：使用**explain关键字**

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。
4）使用**show profile命令**分析SQL语句，分析时间都耗费到哪里了。
tip：Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
5）通过trace分析优化器的执行计划，进一步了解SQL优化器的为毛选择A不选择B；

如何避免索引失效？*

如何进行SQL优化？*
情形一：大批量插入数据
a.主键顺序插入。把导入的数据按照主键的顺序排列；（推荐）
b.关闭唯一性校验。插入前执行**set UNIQUE_CHECKS = 0**，导入结束一定要恢复。
c.手动提交事务。插入前关闭自动提交事务，导入结束再开启。

情形二：优化insert语句
a.如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗
b.在事务中进行数据插入。
c.数据有序插入。

情形三：优化order by语句
a.第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
b.第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。（推荐）

情形四：优化group by语句
由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。总结：和order by优化一样的方案

情形五：优化嵌套查询
使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中，即使用子查询。特殊情形下可以join可能效率更高。

情形六：优化or条件
对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。建议使用union替换or，union语句的type值是ref，or语句的type值是rang，差距非常明显。union联合查询（有去重效果），union all（不去重），可用来消除笛卡尔积

情形七：优化分页查询
a.在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。（推荐使用）
b.该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

合理的数据库设计：
1.根据数据库三范式来进行表结构的设计。
注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：
A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。
C：分割表。
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
D: 字段设计
1. 表的字段尽可能用NOT NULL
2. 字段长度固定的表查询会更快
3. 把数据库的大表按时间或一些标志分成小表

冗余场景：一般数据改动的可能性少，而查询多的场景会使用冗余，例如淘宝的店铺名称，淘宝商家中心会有这个字段，可能里面的商家论坛也有，再假设聚划算这种独立的大业务自己也存一份，再来个垂直频道电器城的后台管理也独立存一份，这种场景是由于对查询性能要求高产生的，所以必须要冗余，在业务的取舍上，肯定是对让用户更快看到信息，那么不可避免的是带来维护成本的增加，对于数据一致性问题，只要做到最终一致就可以了，分布式的CAP原则的实际应用基本都是通过牺牲数据一致性(C)来保证高可用(A)和高可靠(P)， 因为这种场景大部分都是可以接受短暂的数据不一致的，对业务的影响及其微小。
在餐掌柜的项目我们遵循的原则：
项目全部采用逻辑关联，没有采用主外键约束
尽可能少使用多表关联查询。冗余是为了效率，减少join
尽可能服务独立化，查询单表化，例如：查询用户信息又需要用户的头像，处理的原则是调用【用户服务】和【附件服务】在dubbo层组装数据

10.数据库锁(高薪常问)*

11.线上如果出现死锁如何解决？

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。在下面的例子中，由于两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。
2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁（行锁），而不应先申请共享锁（表锁），更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
4. 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
5. 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键冲突出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或者在遇到主键重复错误时，总是执行ROLLBACK释放获得的排他锁

   数据库的MVCC机制工作过程？

   undo_log多版本链
   ReadView“快照读”：SQL执行时，MVCC提取数据的依据。是一个数据结构，包含4个字段：m_ids(活跃的事务编号集合)、min_trx_id(最小活跃事务编号)、max_trx_id(预分配事务编号)、creator_trx_id(创建者的事务编号)
   读已提交（RC）：每一次执行快照读时生成ReadView
   
   可重复读（RR）：仅在第一次执行快照读时生成ReadView，后续快照读复用。RR级别下使用MVCC不能完全解决幻读的问题。在连续多次快照读，ReadView会产生复用，没有幻读问题。但是当两次快照读之间存在当前读，ReadView会重新生成，导致产生幻读