2022年最新mysql面试题附答案，建议收藏
1. MySQL 索引使用有哪些注意事项呢？
索引哪些情况下会失效
查询条件包含or 会导致索引失效
组合索引中，查询时要按照最左原则进行where条件判断
like中 “%asdg"会导致索引失效
对索引进行函数操作或加减会导致索引失效
*索引中使用!= 或者 not in 会失效
*隐式转换也会失效 比如字段是int 却用where age=‘1’
不适用的场景
数据量少的情况下不建议用索引
离散低的不建议用索引如 sex 男女
频繁更新的字段不建议加索引
2. MySQL 遇到过死锁问题吗，你是如何解决的？
步骤：
查看死锁日志show engine innodb status;
找出死锁sql
分析sql加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果
3. 日常工作中你是怎么优化SQL的？
加索引
查询条件包含or 会导致索引失效
组合索引中，查询时要按照最左原则进行where条件判断
like中 “%asdg"会导致索引失效
对索引进行函数操作或加减会导致索引失效
对索引进行函数操作或加减会导致索引失效
索引中使用!= 或者 not in 会失效
数据处理多时要分批次进行
尽量避免使用2个表以上的join
满足三大范式
主从复制读写分离
分表分库 -…
4. 分库分表的设计
应该是先按业务垂直分库 再到按业务垂直分表 然后就是 按字段水平分表
水平分库：以字段为依据，按照range、hash策略，将一个库中的数据拆分到多个库中
水平分表：以字段为依据，按照range、hash策略，将一个表中的数据拆分到多个表中
垂直分库：以表为依据，按照业务归属不同，将不同的表拆到不同的库中
垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表中
-分表分库常用中间件 sharding-jdbc 和 mycat
5. InnoDB与MyISAM的区别
// 事务 索引 锁
I 支持事务，M不支持事物
I 支持外键，M不支持外键
I 支持MVCC，M不支持
select count(*) from table 时 M快，因为他有一个变量存着表的总行数，I需要全表扫描
I 支持表 行锁，M只支持表锁
6. 数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
1）当数据量大时，树的高度会比较高（树的高度决定着它的IO操作次数，IO操作耗时大），查询会比较慢。
2）每个磁盘块（节点/页）保存的数据太小
7. 聚集索引与非聚集索引的区别
一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
8. limit 1000000 加载很慢的话，你是怎么解决的呢？
如果id是连续的，可以返回上次查询的最大记录，再往下limit
在业务允许的情况是下限制页数 ，是否需要如此靠后的数据
order by + 索引 （id为索引）
9. 如何选择合适的分布式主键方案呢？
UUID
雪花算法
Redis生成ID
10. 事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
读脏：一个事务对数据进行了修改但未提交，另一个事务也在访问这个数据并且读到了修改的数据
不可重复读：A事务多次读，B事务在A读取中修改了数据，可能造成结果不一致
幻读：一个事务在读取某一范围数据的时候，另一个事务在该范围内插入了新行，再次读取的时候与之前的数据不一致
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
读未提交
这种事务隔离级别下，select语句不加锁，此时可能不一致的数据，读脏。是并发最高，一致性最差的隔离级别
读提交 RC
可能会出现幻读
可重复读 RR
无论读几次都不会读到重复的数据
串行化
如果有未提交的事务正在修改某些行，所有读取这些行的select就会被堵塞住，一致性最好的，但并发性最差的隔离级别。
11. 在高并发情况下，如何做到安全的修改同一行数据？
使用悲观锁
for update ，本次事务提交之前，别的线程都无法修改这些记录
使用乐观锁
有线程进来，先放过去修改，如果看到别的线程没修改过，就可以修改成功。如果别的线程修改过，就修改失败或者重试
12. 数据库的乐观锁和悲观锁
悲观锁 ，任何事务都不能对数据进行修改，只能等待锁被释放后才能执行
乐观锁，先把线程放进来，如果没有其他线程对数据进行修改则可以修改成功，否则修改失败或者重试。
13. SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？
show status 了解各种sql的执行频率
通过慢日志定位那些执行效率低的sql语句
explain分析低效的sql执行计划
14. select for update有什么含义， 锁表还是锁行还是其他？
是悲观锁，用索引是行锁，没有是表锁
15. MySQL事务得四大特性以及实现原理
原子性：要么全部执行，全部不执行
一致性：事务开始前后，数据不会被破坏
隔离性：事务与事务之间互相隔离，不会冲突
持久性：会永久的保存到数据库中
16. 如果某个表有近千万数据，CRUD比较慢，如何优化？
优化sql语句，加索引，适当反三范式，适当冗余
可以将一部分常用的数据存到redis中
主从复制，读写分离
水平分库分表，垂直分库分表
17. 如何写sql能够有效的使用到复合索引？
注意查询sql条件的顺序，确保最左匹配原则有效
18. mysql中in 和exists的区别
如果子查询的表数据比主查询中的少，适合用in。如果子查询的表数据比主查询中的多，适合用exists
19. 数据库自增主键可能遇到什么问题？
使用自增主键进行分表分库时，会造成主键重复问题，可以使用UUID。
自增主键可能会用完
20. MVCC底层原理
MVCC只在RC和RR下才有效。
? 在每个表的后面有三个隐藏字段 行ID、事务ID、回滚指针
? ReadView由未提交的事务ID数组和已提交的最大事务ID组成
ReadView策略不同：
? RC下每执行一次select都会重新生成一个ReadView。
? RR下只会在第一次select执行时生成，后续沿用第一次的ReadView
版本链：
? 在版本链中拿出最上面的事务ID开始逐个进行对比
? 有一个最小事务ID和最大事务ID。分别在已提交事务和未提交事务区间边缘。中间夹着未提交和已提交事务
? 当这个版本的事务ID比最小事务小时，则是可见的。
? 当这个版本的事务ID比最大事务大时，则是不可见的。
? 落在中间时，有两种情况
? 第一种：在未提交事务中，是不可见的
? 第二种：在已提交事务中，是可见的
21. 数据库中间件了解过吗，sharding jdbc，mycat？
sharding-jdbc基于jdbc驱动，无需额外的proxy。Mycat基于proxy
22. MySQL的主从延迟，你怎么解决？
主从复制分了五个步骤进行：
主库的更新写入到binlog
从库发起连接，连接到主库
主库创建一个binlog dump thread发送到从库，将binlog发送的从库
从库创建一个I/O线程，读取主库传来的binlog内容写入到relay log
从库创建一个sql线程，从relay log中读取内容，将内容写入到从库
这种问题，注意了。划重点。问你出现问题，寻找解决方案的时候，一定要对症下药，也就是说这个问题你可以这样考虑，什么情况下导致的主从延迟。
如果主库和从库服务器配置不一样，从库的差点，那么就可能导致延迟时间加长。这时候，换成相同的服务器配置服务器即可。
从库压力太大了。一般主从了，从库基本用来查询，比如可能运营或者开发者自己都在从库上进行一系列的 sql 操作。那简单呗。多配几个从库，分摊压力，一主多从。
大事务。比如 delete 这种语句 不 limit 限制一下，如果数据量过大，导致主库运行时都花费了长时间，再同步到从库，这个时间间隔过长。
23. 说一下大表查询的优化方案
分表分库，sql优化+索引 加缓存 ，主从复制读写分离 同16
24. 什么是数据库连接池?为什么需要数据库连接池呢?
数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法
资源重用
提高响应速度
统一管理，避免数据库链接泄漏
25. 一条SQL语句在MySQL中如何执行的？
先连接到数据库
查询缓存，大多数情况下不建议用缓存，因为缓存失效很频繁
分析器，如果没有命中缓存，就需要对语句及逆行解析
优化器，经过优化器进行处理，优化器是在表里面有多个索引时，决定使用哪个索引，或者一个语句有多表关联时。
执行器，判断表是否有查询权限，如果有，则根据表的引擎定义，去使用这个引擎提供的接口
26. InnoDB引擎中的索引策略，了解过吗？
覆盖索引：
? 在普通索引树中可以得到查询的结果，不需要在回到主键索引树中再次搜索
? 如果select * from table where age between 12 and 25，这样会回表再查一次
? select id from table where age between 12 and 25，就不会进行回表。减少了搜索次数，叫做覆盖索引
最左前缀：
? 原则就是经常用的列有优先
? 离散型高的优先
? 宽度小的列优先
索引下推：
? 在索引遍历过程中，会对索引包含的字段先判断，直接过滤掉不满足条件的记录。减少回表次数
27. 数据库存储日期格式时，如何考虑时区转换问题？
datetime 类型适用于数据原始的创建时间，修改其他字段值时，datetime 字段不会改变
timestamp 类型会记录数据最后一次修改的时间，且自动更新
28. 一条sql执行过长的时间，你如何优化，从哪些方面入手？
是否用到了多表和子查询，优化sql结构，比如除去冗余字段
优化索引结构，是否可以适当增加索引
分表分库
explain分析sql语句
主从复制 读写分离
查看mysql 慢日志
29. Blob和text有什么区别？
Blob用于存储二进制数据，text用于存储大字符串数据
Blob被当作二进制字符串数据，没有字符集，并且排序和比较基于列值中的字节数值
text被当作非二进制字符串，有一个字符集，并根据字符集的排序规则对值进行排序和比较
30. MySQL里记录货币用什么字段类型比较好？
decimal / numeric
31. InnoDB有哪几种锁？
常见锁问题：
行锁超时：
SQL优化，有效利用索引减少SQL执行时间
及时提交事务，避免长事务占用锁资源不释放
代码优化，同一事务中，将执行时间长的sql放到最后
表锁超时：
1）尽量避免表级锁操作；
2）高并发情况下导致的表锁等待可考虑相关SQL优化
3）DDL变更操作尽量在业务低峰执行；
4）DDL执行期间，关注数据库会话运行情况，避免DDL变更相关表涉及的长事务/大查询阻塞DDL操作峰进行
尽量
共享锁和排他锁
如果没有指定某行，两个都是行锁否则就是表锁
share： for share 允许多个事务同时读同一记录，互不干扰，但不允许加其他锁
exclusive： for update 只有一个事务可以写数据
意向锁 Intention
未来某个时间要加 share/ exclusive 锁
是一个表级别的锁
记录锁 Record
封锁索引记录 for update 防止其他事务插入更新删除
间隙锁 gap
封锁索引记录中的间隔，防止其他事务在间隔中插入数据，RR级别下导致不可重复读
插入意向锁 insert intention
在行插入之前由插入操作设置的一种间隙锁
自增锁 auto-inc
-AUTO-INC锁是一种特殊的表级锁，由插入到具有自动增量列的表中的事务使用。
下一个键锁（临界锁） Next-Key
临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。
32. Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？
B+支持范围搜索
B+支持联合索引的最左原则
B+支持order by
B+支持like模糊
hash等值查询效率高
33. mysql 的内连接、左连接、右连接有什么区别？
内连接，只返回匹配的结果集
左连接，返回左表的所有行，即时右表没有匹配的记录
右连接，返回右表的所有行，即时左表没有匹配的记录
34. 什么是内连接、外连接、交叉连接、笛卡尔积呢？
内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。
交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称为：笛卡尔积。
35. 说一下数据库的三大范式
数据库表的每一列都是不可拆分的
在1的基础上 每一列都与主键直接相关
在2的基础上，每一列数据都与主键直接相关
36. mysql有关权限的表有哪几个呢？
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。 db权限表：记录各个帐号在各个数据库上的操作权限。 table_priv权限表：记录数据表级的操作权限。 columns_priv权限表：记录数据列级的操作权限。 host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
37. 主从复制binlog格式有哪几种？有什么区别？
STATEMENT，基于语句的日志记录，把所有写操作的sql语句写入 binlog （默认）
ROW，基于行的日志记录，把每一行的改变写入binlog
MIXED，混合模式
38. Mysql主从复制方式？有什么区别？
STATEMENT，基于语句的日志记录，把所有写操作的sql语句写入 binlog （默认）
ROW，基于行的日志记录，把每一行的改变写入binlog
MIXED，混合模式
39. InnoDB内存结构包含四大核心组件
（1）缓冲池(Buffer Pool)；
（2）写缓冲(Change Buffer)；
（3）自适应哈希索引(Adaptive Hash Index)；
（4）日志缓冲(Log Buffer)；
40. 索引有哪些优缺点？
唯一索引可以保证数据库表中每一行数据的唯一性
索引可以加快查询速度，减少查询时间
创建索引和维护索引需要时间
表中数据进行增删改时，索引也要动态的维护
41. 索引有哪几种类型？
主键索引：数据列不允许重复 不允许为空 只能有一个主键
唯一索引：数据列不允许重复 允许为空 可以创建多个索引
普通索引： 允许为空 可以创建多个索引
全文索引：可以对文本的内容进行分词，搜索
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
组合索引：多个列组成一个索引，用于组合搜索
42. 创建索引的三种方式
create index index_name on table table_name(column)
alter table table_name add index index_name(column)43. 百万级别或以上的数据，你是如何删除的？
先删除表中的索引，在删除数据，在创建索引
44. 覆盖索引、回表等这些，了解过吗？
覆盖索引：查询列要被所创建的索引覆盖，这样查询数据时不会去查询数据列
回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。
45. B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？
当查询使用聚簇索引时，不需要回表查询
46. 何时使用聚簇索引与非聚簇索引
聚簇索引
47. 非聚簇索引一定会回表查询吗？
不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询
举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。
48. 组合索引是什么？为什么需要注意组合索引中的顺序？
组合索引：由多个列创建的索引。索引策略是最左原则
49. 什么是死锁？怎么解决？
有P1P2两个进程，都需要A和B两个资源，现在P1持有A等待B，P2持有B等待A，现在两个都等待另一个资源而不肯释放资源，就会无限等待，导致死锁
1：尽量避免同时锁定两个资源
2: 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源.
死锁是指两个或以上的事务占用同一资源并请求锁定对方资源，从而导致形成恶性循环的现象
死锁的四个必要条件：互斥，请求和保持，环路等待，不剥夺。解决思路是切断环路
50. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？
zabbix，lepus
mysql 分区有哪几种
RANGE：属于一个给定的连续区间的列值被放入分区。
LIST：和RANGE分区相似，只是分区列的值是离散的。
HASH：将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。
KEY：和HASH相似，KEY分区使用数据库提供的函数进行分区。
51.索引底层是什么数据结构？
B + 树。
为什么用的是 B + 树，不能使用红黑树或者其他的？
可以使用红黑树。但是这样的话可能会造成树的高度过高，意味着相同查询下，会进行更多的磁盘 I/O，影响性能，而 B+ 树可以保持树的高度不至于过高
52.一条查询sql以主键查和二级索引查区别
1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。
2、如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，
这个过程也被称为回表。
53.说一说Mysql最左前缀匹配
建立联合索引时会遵循最左前缀匹配的原则
对abc三列建立联合索引
实际是建立了a ab abc 三个索引
对量大的数据表，可以减少开销
54.现在有3张表，1个主表，2个副表，主表数据大概在1W左右，副表大概在1000W和2000W左右，怎么优化？
首先，确保每条数据不会产生null值，然后合理建立索引，合理写SQL，避免select *之类的方式，用什么就取什么。
Mysql给一个大表加一列_MySQL 大表添加一列的实现
55.5.6之前, 加触发器自动完成. 5.6及之后, 使用在线DDL语句
CREATE TRIGGER person_trigger_update AFTER UPDATE ON 原有表 FOR EACH ROW
BEGIN
SET @x = "trigger UPDATE";
REPLACE INTO 新表 SELECT
*
FROM
原有表
WHERE
新表.id = 原有表.id;
END IF;
END;
ALTER TABLE 你的表 ADD COLUMN 新列 char(128), ALGORITHM=INSTANT, LOCK=NONE;
https://blog.csdn.net/weixin_31842775/article/details/11435102956.怎么加读写锁
LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。
LOCK TABLE my_table_name WRITe; 用写锁锁表，会阻塞其他事务读和写。
57.B-树、B+树、B*树都是什么
? B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；
? 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
? B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
? B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；
58.MYSQL和 MONGO区别
数据库 MongoDB MySQL
数据库模型 非关系型 关系型
存储方式 以类JSON的文档的格式存储虚拟内存+持久化 不同引擎有不同的存储方式
查询语句 MongoDB查询方式（类似JavaScript的函数） SQL语句
架构特点 可以通过副本集，以及分片来实现高可用（在副本集中，当主库遇到问题，无法继续提供服务时，副本集将选举一个新的主库继续提供服务， 高可用和集群架构拥有十分高的扩展性） 常见有单点，M-S，MHA,MMM,Cluster等架构方式
数据处理方式 基于内存，将热数据存放在物理内存中，从而达到高速读写 不同引擎有自己的特点处理海量数据效率先住变慢
成熟度 新兴数据库，成熟度较低 成熟度高
广泛度 NoSQL数据库中，比较完善且开源，使用人数在不断增长 开源数据库，市场份额不断增长
事务性 仅支持单文档事务操作，弱一致性（本身没有带事务机制，需要在MongoDB中实现事务机制，需要通过一个额外的表，从逻辑上自行实现事务） 支持事务操作
占用空间 占用空间大 占用空间小
join操作 MongoDB没有join MySQL支持join
Mysql占用cpu过高
show processlist语句，查找cpu占用最高的SQL语句，优化该SQL，比如适当建立某字段的索引
打开慢查询日志，将那些执行时间过长且占用资源过多的SQL拿来进行explain分析
考虑定时优化文件及索引
考虑是否是锁问题
如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。
59.Inoodb快照读和当前读
快照读：读取的是记录的可见版本（有可能是历史版本），不加锁。
当前读：读取的是记录的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发修改这条记录。
快照读实现：mvcc
当前读实现：主要靠加锁（行记录锁+间隙锁）实现，保证其他事务不会再并发修改这条记录。
转：
https://4927525.github.io/post/mst/mysql/
相关文章