2020 MySQL常问面试题

2020 MySQL常问面试题

数据库的三范式是什么？

第一范式： 确保每列保持原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式： 需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键
第三范式： 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

事务以及四大特性ACID ？

事务（Transaction）：访问并可能更新数据库中各种数据项的一个程序执行单元（unit），它通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起。当在数据库中更改数据成功时，在事务中更改的数据便会提交，不再改变。否则，事务就取消或者回滚，更改无效。
举个例子来说，张三给李四转了1000元钱，那么在数据库操作时，就要先把张三的账户减去1000元，再把李四的账户加上1000元，两部分操作放在一起，才是一个完整的转账过程，也可称之为事务。

（1）Atomicity（原子性）： 一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

（2）Consistency（一致性）： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

（3）Isolation（隔离性）： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

（4）Durability（持久性）： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

说一下数据库的常见的事务隔离级别？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：
transaction-isolation = REPEATABLE-READ
可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

附：
（1）不可重复读 ：一个事务对同一行数据重复读取两次，但是却得到了不同的结果

（2）脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

（3）幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，
这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

说一下 MySQL 常用的引擎？

（1）InnoDB 引擎：mysql 5.1 后默认的数据库引擎，提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。
MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，
它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

（2）MyIASM 引擎： 不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。
不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。
所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

说一下乐观锁和悲观锁？（关于拿数据的会不会认为别人是否修改）

乐观锁： 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁： 每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

乐观锁的实现： version 字段
数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

悲观锁的实现：

select ....for update  eg:select  *  from t_goods where id=1 for update;  

e.g当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。

MySQL 的内连接、左连接、右连接有什么区别？

内连接：inner join；左连接：left join；右连接：right join。
内连接：是把匹配的关联数据显示出来；
左连接：是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

char 和 varchar 的区别是什么？

char(n) ： 固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
chat 优点：效率高；
缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ： 可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

float 和 double 的区别是什么？

float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

MySQL 索引是怎么实现的？

通常使用 B 树及其变种 B+树；

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

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

CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
       ……，
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);

EG:创建普通索引
 create table table_index
(   id int primary key auto_increment,
    name varchar(20),
    index index_name (name)
);

索引的目的是什么？索引对数据库系统的负面影响是什么？为数据表建立索引的原则有哪些？什么情况下不宜建立索引？

目的：
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接
附： 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

原则：
在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引

什么情况下不宜建立索引：
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

MySQL数据库什么情况下设置了索引但无法使用？

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询是以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
查看索引的使用情况：

show status like ‘Handler_read%’

怎么验证 MySQL 的索引是否满足需求？

使用== explain== 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：explain select * from table where type=1。

MySQL 问题排查都有哪些手段？

（1）使用 show processlist 命令查看当前所有连接信息。
（2）使用 explain 命令查询 SQL 语句执行计划。
（3）开启慢查询日志，查看慢查询的 SQL。

如何做 MySQL 的性能优化？

（1）为搜索字段创建索引。
（2）避免使用 select *，列出需要查询的字段。
（3）尽可能的使用 NOT NULL（全表扫描）
（3）垂直分割分表（ 如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中，这种方式称为垂直分割.）
（4）选择正确的存储引擎

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

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

为什么要尽量设定一个主键?

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的 ID 列作为主键.设定了主键 之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全

MySQL中保存钱，应该使用什么数据类型？并说明原因

一般用decimal，长度18，保存2位小数

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

该题目是MySQL引擎实现事务与否：
表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。不支持事务。
¹

---

1. 编写不易，如果有帮助到的话，可以关注，点赞和收藏哦~ ↩︎