mysql数据库面试题

数据库的三大范式

第一范式：确保数据库表字段的原子性。
第二范式：首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键，二是非主键列必须完全依赖于主键，而不能依赖于主键的一部分。
第三范式：首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键B，非主键列B依赖于主键的情况。

什么时候可以打破三大范式

为了性能考虑，避免大表链接，可以考虑冗余数据到一张表里面

事务的四大特征

事务特征ACID：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

事务隔离级别

脏读
脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读
不可重复读是指在一个事务内，多次读取同一个数据。
在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

隔离级别	脏读	不可重复读	幻读
读未提交（READ UNCOMITTED，RU）	√	√	√
读提交（READ COMMITTED，RC）	×	√	√
可重复读（REPEATABLE READ，RR）	×	×	√
SERIALIZABLE	×	×	×

索引的分类

主键索引：名为primary的唯一非空索引，不允许有空值。

普通索引：名为primary的唯一非空索引，不允许有空值。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,…);

组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

什么时候应该添加索引

经常查询的列，经常链接的列

什么时候不应该添加索引

不经常查询的列，不经常链接的列，列值是枚举类型

索引失效

1.使用范围查询或者条件比如!=, >, <,between and, in, or等
2.类型不一致，进行了隐式转化。
3.使用LIKE且左边有%
4.在索引列上进行了数学运算
5.联合索引的最左原理

什么是前缀索引

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

数据库为什么使用B+树而不是B树来实现索引

B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说I/O读写次数也就降低了

B+树的查询效率更加稳定。
B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

怎么优化sql查询语句

1、 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2、 用索引可以提高查询
3、 SELECT子句中避免使用*号，尽量全部大写SQL
4、 应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL
5、 where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描
6、 in 和 not in 也要慎用，否则会导致全表扫描

怎么排查慢sql问题

1.查看执行计划，看看有没有走索引。
2.记录sql执行耗时，并对sql仔细分析

MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：
1、 使用top 命令观察，确定是MySQLd导致还是其他原因。
2、 如果是MySQLd导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
3、 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：
1、 kill 掉这些线程(同时观察 cpu 使用率是否下降)，
2、 进行相应的调整(比如说加索引、改 sql、改内存参数)
3、 重新跑这些 SQL。

其他情况：
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

分库与分表的设计

分库分表方案，分库分表中间件，分库分表可能遇到的问题

「分库分表方案:」
1、 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2、 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
3、 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
4、 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

「常用的分库分表中间件：」
1、 sharding-jdbc（当当）
2、 Mycat
3、 TDDL（淘宝）
4、 Oceanus(58同城数据库中间件)
5、 vitess（谷歌开发的数据库中间件）
6、 Atlas(Qihoo 360)

「分库分表可能遇到的问题」
1、 事务问题：需要用分布式事务啦
2、 跨节点Join的问题：解决这一问题可以分两次查询实现
3、 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
4、 数据迁移，容量规划，扩容等问题
5、 ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID
6、 跨分片的排序分页问题（后台加大pagesize处理？）

分区

分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。

分区的好处
可以让单表存储更多的数据
分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度会很快
分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
可以备份和恢复单个分区

分区的限制和缺点
一个表最多只能有1024个分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
NULL值会使分区过滤无效
所有分区必须使用相同的存储引擎

MySql存储引擎的种类和区别

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

查询语句执行流程

首先检查权限，没有权限则返回错误
MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
两种执行方案，先查 id > 1 还是 name = ‘大彬’，优化器根据自己的优化算法选择执行效率最好的方案；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

更新语句执行过程

先查询到 id 为1的记录，有缓存会使用缓存。
拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。
执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。
更新完成。

MVCC 实现原理

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。

版本链

MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。
DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链
DB_ROLL_ID：主键，如果数据表没有主键，InnoDB会自动生成主键。
每条表记录大概是这样的：

name	age	DB_ROW_ID	DB_TRX_ID	DB_ROLL_PTR
大彬	18	1	a	12434134

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；
将该行原本的值拷贝到undo log，作为旧版本用于回滚；
修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

read View

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。
在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建read view的时机不同。
read committed：每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。
repeatable read：在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

read view的记录筛选方式
前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。

如果DATA_TRX_ID >= low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。

如果up_limit_id <= DATA_TRX_ID < low_limit_i：
i. 需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
ii. 如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
iii. 如果不存在，说明事务trx_id 已经提交了，这行记录是可见的。

总结：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

快照读和当前读

表记录有两种读取方式。

快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。
快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

参考

https://blog.csdn.net/Emily_ASL/article/details/122782130

https://www.cnblogs.com/souyunku/p/15633392.html