[TOC]

说明：以下文字来源网络各相关面试题目收集

数据库的三范式？

第一范式（1NF）：字段具有原子性,不可再分。(所有关系型数据库系统都满足第一范式数据库表中的字段都是单一属性的，不可再分)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 >所以第三范式具有如下特征： »1. 每一列只有一个值 »2. 每一行都能区分。 »3. 每一个表都不包含其他表已经包含的非主关键字信息。

MySQL

建表

如果一个表有一列定义为 TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

Datetime 和 Timestamp 都是可以精确到秒的时间类型，但是 Datetime 占用 8 个字节，而 Timestamp 占用 4 个字节。

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB -

TINYBLOB
BLOB
MEDIUMBLOB 和
LONGBLOB

它们只能在所能容纳价值的最大长度上有所不同。

TEXT 是一个不区分大小写的 BLOB。四种 TEXT 类型

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

它们对应于四种 BLOB 类型，并具有相同的最大长度和存储要求。

BLOB 和 TEXT 类型之间的唯一区别

BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

NOW（）和 CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE（）仅显示当前年份，月份和日期。

mysql 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 Mysql 实现为同样的类型，这在 SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定；点击这里有一套最全阿里面试题总结。

例如：
salary DECIMAL(9,2)

字符串

mysql 中 varchar 与 char 的区别以及 varchar(50)中的 50 代表的涵义？

varchar 与 char 的区别: char 是一种固定长度的类型，varchar 则是一种可变长度的类型.
varchar(50)中 50 的涵义 : 最多存放 50 个字节
int（20）中 20 的涵义: int(M)中的 M indicates the maximumdisplay width (最大显示宽度)for integer types. The maximum legal display width is 255.

表中有大字段 X（例如：text 类型），且字段 X 不会经常更新，以读为为主，将该字段拆成子表好处是什么？

如果字段里面有大字段（text,blob)类型的，而且这些字段的访问并不多，这时候放在一起就变成缺点了。 MYSQL 数据库的记录存储是按行存储的，数据块大小又是固定的（16K），每条记录越小，相同的块存储的记录就越多。此时应该把大字段拆走，这样应付大部分小字段的查询时，就能提高效率。当需要查询大字段时，此时的关联查询是不可避免的，但也是值得的。拆分开后，对字段的 UPDAE 就要 UPDATE 多个表了

查询

[SELECT *] 和[SELECT 全部字段]的 2 种写法有何优缺点?

前者要解析数据字典，后者不需要
结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。
表字段改名，前者不需要修改，后者需要改
后者可以建立索引进行优化，前者无法优化
后者的可读性比前者要高

HAVNG 子句和 WHERE 的异同点?

语法上：where 用表中列名，having 用 select 结果别名
影响结果范围：where 从表读出数据的行数，having 返回客户端的行数
索引：where 可以使用索引，having 不能使用索引，只能在临时结果集操作
where 后面不能使用聚集函数，having 是专门使用聚集函数的。

LIKE 和 REGEXP 操作有什么区别？

LIKE 和 REGEXP 运算符用于表示 ^ 和％。

SELECT * FROM <tablename> WHERE * REGEXP "^b";
SELECT * FROM <tablename> WHERE * LIKE "%b";

Mysql 查询是否区分大小写？

不区分，但是我们在部署的时候还是统一的好，因为Linux服务器是区分大小写和window不同。

Mysql 如何优化 DISTINCT？

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用。

如何显示前 50 行？

SELECT * FROM LIMIT 0,50;

触发器

MySQL 表中允许有多少个 TRIGGERS？

在 MySQL 表中允许有六个触发器，如下：

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE

常用函数

什么是通用 SQL 函数？

也就是mysql自带的函数和java的jdk一样。

CONCAT(A, B) - 并为一个字段。连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合

FORMAT(X, D)- 格式化数字 X 到 D 有效数字。

CURRDATE(), CURRTIME()- 返回当前日期或时间。

NOW（） - 将当前日期和时间作为一个值返回。

MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） - 从日期值中提取给定数据。

HOUR（），MINUTE（），SECOND（） - 从时间值中提取给定数据。

DATEDIFF（A，B） - 确定两个日期之间的差异，通常用于计算年龄

SUBTIMES（A，B） - 确定两次之间的差异。

FROMDAYS（INT） - 将整数天数转换为日期值。

数学函数

Abs（num）求绝对值
floor（num）向下取整
ceil（num）向上取整

字符串函数

insert (s1,index,length,s2) 替换函数
upper（str），ucase（str）将字母改为大写
lower（str），lcase（str）将字母改为小写
left（str，length）返回 str 字符串的前 length 个字符
right（str，length）返回 str 字符串的后 length 个字符
substring（str，index，length）返回 str 字符串从 index 位开始长度为length 个字符（index 从 1 开始）
reverse（str）将 str 字符串倒序输出

日期函数

curdate（）、current_date( ) 获取当前日期
curtime（）、current_time( ) 获取当前日期
now（）获取当前日期和时间
datediff（d1、d2）d1 和 d2 之间的天数差
adddate（date，num）返回 date 日期开始，之后 num 天的日期
subdate（date，num）返回 date 日期开始，之前 num 天的日期

聚合函数

Count（字段）根据某个字段统计总记录数（当前数据库保存到多少条数据）
sum（字段）计算某个字段的数值总和
avg（字段）计算某个字段的数值的平均值
Max（字段）、min（字段）求某个字段最大或最小值

MySQL 中有哪几种锁？

MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

MySQL 数据优化

有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。
表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等

优化数据类型

避免使用 NULL，NULL 需要特殊处理, 大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。

仅可能使用更小的字段，MySQL 从磁盘读取数据后是存储到内存中的，然后使用 cpu 周期和磁盘 I/O 读取它，这意味着越小的数据类型占用的空间越小.

小心字符集转换

客户端或应用程序使用的字符集可能和表本身的字符集不一样，这需要 MySQL 在运行过程中隐含地进行转换，此外，要确定字符集如 UTF- 8 是否支持多字节字符，因此它们需要更多的存储空间。

_优化 count(mycol) 和 count()*

优化子查询

遇到子查询时，MySQL 查询优化引擎并不是总是最有效的，这就是为什么经常将子查询转换为连接查询的原因了，优化器已经能够正确处理连接查询了，当然要注意的一点是，确保连接表 (第二个表) 的连接列是有索引的，在第一个表上 MySQL 通常会相对于第二个表的查询子集进行一次全表扫描，这是嵌套循环算法的一部分。

优化 UNION

在跨多个不同的数据库时使用 UNION 是一个有趣的优化方法， UNION 从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序。

UNION ALL 可以大大加快速度，如果你已经知道你的数据不会包括重复行，或者你不在乎是否会出现重复的行，在这两种情况下使用UNION ALL 更适合。此外，还可以在应用程序逻辑中采用某些方法避免出现重复的行，这样 UNION ALL 和 UNION 返回的结果都是一样的，但 UNION ALL 不会进行排序。

数据库备份

必须要在未登录状态下

导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

导出一个数据库结构

mysqldump -u dbuser -p -d --add-drop-table dbname >d:/dbname_db.sql

-d 没有数据 --add-drop-table 在每个 create 语句之前增加一个 drop table

删除

truncate delete drop 的区别

drop(DDL 语句)：是不可逆操作，会将表所占用空间全部释放掉；

truncate(DDL 语句)：只针对于删除表的操作，在删除过程中不会激活与表有关的删除触发器并且不会把删除记录放在日志中；当表被 truncate 后，这个表和索引会恢复到初始大小；

delete(DML 语句)：可以删除表也可以删除行，但是删除记录会被计入日志保存，而且表空间大小不会恢复到原来；

执行速度：drop>truncate>delete。

索引

请简述常用的索引有哪些种类?

普通索引: 即针对数据库表创建索引
唯一索引: 与普通索引类似，不同的就是：MySQL 数据库索引列的值必须唯一，但允许有空值
主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
组合索引: 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。

以及在 mysql 数据库中索引的工作机制是什么？

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树。

mysql 的复制原理以及流程

Mysql 内建的复制功能是构建大型，高性能应用程序的基础。将 Mysql 的数据分布到多个系统上去，这种分布的机制，是通过将 Mysql 的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。 * 复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

过程如下

主服务器把更新记录到二进制日志文件中。
从服务器把主服务器的二进制日志拷贝到自己的中继日志（replay log）中。
从服务器重做中继日志中的时间，把更新应用到自己的数据库上。

搜索引擎

mysql 中 myisam 与 innodb 的区别？

事务支持

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

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

InnoDB 支持行级锁，而 MyISAM 支持表级锁. » 用户在操作myisam 表时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。
InnoDB 支持 MVCC, 而 MyISAM 不支持
InnoDB 支持外键，而 MyISAM 不支持
表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

InnoDB 不支持全文索引，而 MyISAM 支持。
可移植性、备份及恢复

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

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

存储结构

MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成（或称实现）的？

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！默认是InnoDB

事物

MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

Read Uncommitted（读取未提交内容） » 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容） » 这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。
**Repeatable Read（可重读） » 这是 MySQL 的默认事务隔离级别，**它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control 间隙锁）机制解决了该问题。注：其实多版本只是解决不可重复读问题，而加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题。
Serializable（可串行化） » 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

MySQL事务和锁的关系

原文链接：https://blog.csdn.net/weixin_40784198/article/details/81462010

用一句话去概括事务：将对数据库进行的多个操作封装成单个，只有在所有操作都成功的情况下才真正实现变动，否则数据原封不动。用一句话概括锁：一个让数据不能被修改的功能。而他们之间的关系可以定义为：锁是实现事务其中一个特性的机制。

事务是一个针对数据质量产生的概念。在高并发或者数据库出现突然断电的情况下，事务所包含的特性就能应对可能会出现的差错。事务有4个特性：Atomic + Consistent + Isolated + Durable (ACID)。Atomic是原子性也就是之前提到的“要么都成功，要么都原封不动的概念”。它的设定是来保证一个事务在执行上不能被拆分。Consistent是一致性，如果出现突然断电，事务的原则能保证没有数据更新会执行到一半而导致和其他数据不统一。Isolated是隔离性，其指的是事务之间是否能意识到对方的存在。这也取决于事务隔离的级别。所以隔离性本身是有自己的弹性在的。Durable持久性，表示数据保存后会持续存在。

在这4大特性里，数据库的锁只跟事务特性里的隔离性有关。所谓事物之间是否能“意识”到对方的存在就是每个事务是否能对另一事务针对的数据做出更改。所以可不可以更改必然更所有关（锁：一个让数据不能被修改的功能）。

数据一致性

主从数据库不一致如何解决

场景描述，对于主从库，读写分离，如果主从库更新同步有时差，就会导致主从库数据的不一致

忽略这个数据不一致，在数据一致性要求不高的业务下，未必需要时时一致性
强制读主库，使用一个高可用的主库，数据库读写都在主库，添加一个缓存，提升数据读取的性能。
选择性读主库，添加一个缓存，用来记录必须读主库的数据，将哪个库，哪个表，哪个主键，作为缓存的 key,设置缓存失效的时间为主从库同步的时间，如果缓存当中有这个数据，直接读取主库，如果缓存当中没有这个主键，就到对应的从库中读取。

配置文件

Mysql的连接数多少

通常，mysql的最大连接数默认是100, 最大可以达到16384

这种方式说来很简单，只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能，一个是在安装目录下（这是比较理想的情况），另一种是在数据文件的目录下安装的时候如果没有人为改变目录的话，一般就在C:/ProgramData/MySQL往下的目录下。

MYSQL 数据表在什么情况下容易损坏？

服务器突然断电导致数据文件损坏。
强制关机，没有先关闭 mysql 服务等。

数据库执行过程

640

如何控制 HEAP 表的最大尺寸？

我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的(推荐使用 UNSIGNED)，并设置上自动增加的 AUTO_INCREMENT 标志。Heal 表的大小可通过称为 max_heap_table_size 的 Mysql 配置变量来控制。

MySQL 性能优化的 21 个最佳实践

「真诚赞赏，手留余香」