数据库面试高频知识点

1.窗口函数

1.窗口函数语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank(), dense_rank(), row_number()等，括号内不加任何东西。

2） 聚合函数，如sum，avg(), count(), max(), min()等，括号内要加列。

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

2.窗口函数有以下功能：

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

3.注意事项

窗口函数原则上只能写在select子句中

4.窗口函数使用场景

1）业务需求“在每组内排名”，比如：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

2、连接join

2.1 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

2.2 左连接

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

2.3 右连接

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

2.4 全连接

全连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

 

2、mysql底层（B+树）

3、mysql查询优化的策略（最左前缀等等）

 

数据库三大范式

1、第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。简而言之，第一范式就是无重复的域。

2、第二范式（2NF）：确保表中的每列都和主键相关

在第一范式（1NF）的基础上建立，且要求数据库表中的每个实例或记录必须可以被唯一地区分。

3、第三范式（3NF）：确保每列都和主键列直接相关,而不是间接相关

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。即第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。即属性不依赖于其它非主属性，属性直接依赖于主键。

 

4、sql 索引

索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度。

索引的作用

- 提高查询效率

- 消除数据分组、排序

- 避免“回表”查询（索引覆盖）

- 优化聚合查询

- 用于多表JOIN关联查询

- 利用唯一性约束，保证数据唯一性

- InnDB行锁实现

索引的副作用

- 增加I/O成本

- 增加磁盘空间

- 不合适的索引或索引过多，会降低增删改的效率

索引的分类

数据的存储方式

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

聚集索引，索引顺序和数据库表的物理排列顺序是一样的；

非聚集索引，索引顺序和数据库表的排列顺序是不一样的。

 

建立索引的原则：

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引 

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

 

待增加