精心整理的MYSQL面试题

PHP面试题	https://blog.youkuaiyun.com/hzbskak/article/details/120852249
REDIS面试题	https://blog.youkuaiyun.com/hzbskak/article/details/120852279
MYSQL面试题	https://blog.youkuaiyun.com/hzbskak/article/details/120852285
NGINX面试题	https://blog.youkuaiyun.com/hzbskak/article/details/120852271

1. MySQL 索引使用有哪些注意事项呢？

索引哪些情况下会失效

• 查询条件包含or 会导致索引失效

• 组合索引中，查询时要按照最左原则进行where条件判断

• like中 "%asdg"会导致索引失效

• 对索引进行函数操作或加减会导致索引失效

• *索引中使用!= 或者 not in 会失效

• *隐式转换也会失效 比如字段是int 却用where age=‘1’

• 不适用的场景

• 数据量少的情况下不建议用索引

• 离散低的不建议用索引如 sex 男女

• • 频繁更新的字段不建议加索引

*2. MySQL 遇到过死锁问题吗，你是如何解决的？

步骤：

• 查看死锁日志show engine innodb status;

• 找出死锁sql

• 分析sql加锁情况
• 模拟死锁案发
• 分析死锁日志
• 分析死锁结果

3. 日常工作中你是怎么优化SQL的？

• 加索引

1. 查询条件包含or 会导致索引失效
2. 组合索引中，查询时要按照最左原则进行where条件判断
3. like中 "%asdg"会导致索引失效
4. 对索引进行函数操作或加减会导致索引失效
5. 对索引进行函数操作或加减会导致索引失效
6. 索引中使用!= 或者 not in 会失效

• 数据处理多时要分批次进行
• 尽量避免使用2个表以上的join
• 满足三大范式
• 主从复制读写分离
• 分表分库
  -…

4. 分库分表的设计

• 水平分库：以字段为依据，将一个库中的数据拆分到多个库中
• 水平分表：以字段为依据，将一个表中的数据拆分到多个表中
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆到不同的库中
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表中

-分表分库常用中间件 sharding-jdbc 和 mycat

5. InnoDB与MyISAM的区别

// 事务 索引 锁

• I 支持事务，M不支持事物
• I 支持外键，M不支持外键
• I 支持MVCC，M不支持
• select count(*) from table 时 M快，因为他有一个变量存着表的总行数，I需要全表扫描
• I 支持表 行锁，M只支持表锁

6. 数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

7. 聚集索引与非聚集索引的区别

8. limit 1000000 加载很慢的话，你是怎么解决的呢？

• 如果id是连续的，可以返回上次查询的最大记录，再往下limit
• 在业务允许的情况是下限制页数 ，是否需要如此靠后的数据
• order by + 索引 （id为索引）

9. 如何选择合适的分布式主键方案呢？

• UUID
• 雪花算法
• Redis生成ID

10. 事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

读脏：一个事务对数据进行了修改但未提交，另一个事务也在访问这个数据并且读到了修改的数据
不可重复读：A事务多次读，B事务在A读取中修改了数据，可能造成结果不一致
幻读：一个事务在读取某一范围数据的时候，另一个事务在该范围内插入了新行，再次读取的时候与之前的数据不一致

1. 读未提交

• 这种事务隔离级别下，select语句不加锁，此时可能不一致的数据，读脏。是并发最高，一致性最差的隔离级别

2. 读提交 RC

• 可能会出现幻读

3. 可重复读 RR

• 无论读几次都不会读到重复的数据

4. 串行化

• 如果右未提交的事务正在修改某些行，所有读取这些行的select就会被堵塞住，一致性最好的，但并发性最差的隔离级别。

11. 在高并发情况下，如何做到安全的修改同一行数据？

1. 使用悲观锁

• for update ，本次事务提交之前，别的线程都无法修改这些记录

2. 使用乐观锁

• 有线程进来，先放过去修改，如果看到别的线程没修改过，就可以修改成功。如果别的线程修改过，就修改失败或者重试

12. 数据库的乐观锁和悲观锁

• 悲观锁 ，任何事务都不能对数据进行修改，只能等待锁被释放后才能执行
• 乐观锁，先把线程放进来，如果没有其他线程对数据进行修改则可以修改成功，否则修改失败或者重试。

13. SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？

1. show status 了解各种sql的执行频率
2. 通过慢日志定位那些执行效率低的sql语句
3. 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底层原理

21. 数据库中间件了解过吗，sharding jdbc，mycat？

• sharding-jdbc基于jdbc驱动，无需额外的proxy。Mycat基于proxy

22. MySQL的主从延迟，你怎么解决？

• 主从复制分了五个步骤进行：

1. 主库的更新写入到binlog
2. 从库发起连接，连接到主库
3. 主库创建一个binlog dump thread发送到从库，将binlog发送的从库
4. 从库创建一个I/O线程，读取主库传来的binlog内容写入到relay log
5. 从库创建一个sql线程，从relay log中读取内容，将内容写入到从库

这种问题，注意了。划重点。问你出现问题，寻找解决方案的时候，一定要对症下药，也就是说这个问题你可以这样考虑，什么情况下导致的主从延迟。

1. 如果主库和从库服务器配置不一样，从库的差点，那么就可能导致延迟时间加长。这时候，换成相同的服务器配置服务器即可。
2. 从库压力太大了。一般主从了，从库基本用来查询，比如可能运营或者开发者自己都在从库上进行一系列的 sql 操作。那简单呗。多配几个从库，分摊压力，一主多从。
3. 大事务。比如 delete 这种语句 不 limit 限制一下，如果数据量过大，导致主库运行时都花费了长时间，再同步到从库，这个时间间隔过长。

23. 说一下大表查询的优化方案

分表分库，sql优化+索引 加缓存 ，主从复制读写分离
同16

24. 什么是数据库连接池?为什么需要数据库连接池呢?

25. 一条SQL语句在MySQL中如何执行的？

26. InnoDB引擎中的索引策略，了解过吗？

27. 数据库存储日期格式时，如何考虑时区转换问题？

• datetime 类型适用于数据原始的创建时间，修改其他字段值时，datetime 字段不会改变
• timestamp 类型会记录数据最后一次修改的时间，且自动更新

28. 一条sql执行过长的时间，你如何优化，从哪些方面入手？

• 是否用到了多表和子查询，优化sql结构，比如除去冗余字段
• 优化索引结构，是否可以适当增加索引
• 分表分库
• explain分析sql语句
• 主从复制 读写分离
• 查看mysql 慢日志

29. Blob和text有什么区别？

30. MySQL里记录货币用什么字段类型比较好？

decimal / numeric

31. InnoDB有哪几种锁？

1. 共享锁和排他锁

• 如果没有指定某行，两个都是行锁否则就是表锁
• share： for share 允许多个事务同时读同一记录，互不干扰，但不允许加其他锁
• exclusive： for update 只有一个事务可以写数据

2. 意向锁 Intention

• 未来某个时间要加 share/ exclusive 锁
• 是一个表级别的锁

3. 记录锁 Record

• 封锁索引记录 for update 防止其他事务插入更新删除

4. 间隙锁 gap

• 封锁索引记录中的间隔，防止其他事务在间隔中插入数据，导致不可重复读

5. 插入意向锁 insert intention

• 在行插入之前由插入操作设置的一种间隙锁

6. 自增锁 auto-inc

-AUTO-INC锁是一种特殊的表级锁，由插入到具有自动增量列的表中的事务使用。

7. 下一个键锁（临界锁） Next-Key

32. Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

1. B+支持范围搜索
2. B+支持联合索引的最左原则
3. B+支持order by
4. B+支持like模糊
5. hash等值查询效率高

33. mysql 的内连接、左连接、右连接有什么区别？

• 内连接，只返回匹配的结果集
• 左连接，返回左表的所有行，即时右表没有匹配的记录
• 右连接，返回右表的所有行，即时左表没有匹配的记录

34. 什么是内连接、外连接、交叉连接、笛卡尔积呢？

• 内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
• 外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。
• 交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称为：笛卡尔积。

35. 说一下数据库的三大范式

1. 数据库表的每一列都是不可拆分的
2. 在1的基础上 每一列都与主键直接相关
3. 在2的基础上，每一列数据都与主键直接相关

36. mysql有关权限的表有哪几个呢？

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

37. 主从复制binlog格式有哪几种？有什么区别？

38. Mysql主从复制方式？有什么区别？

39. InnoDB内存结构包含四大核心组件

40. 索引有哪些优缺点？

• 唯一索引可以保证数据库表中每一行数据的唯一性

• 索引可以加快查询速度，减少查询时间

• 创建索引和维护索引需要时间

• 表中数据进行增删改时，索引也要动态的维护

41. 索引有哪几种类型？

1. 主键索引：数据列不允许重复 不允许为空 只能有一个主键
2. 唯一索引：数据列不允许重复 允许为空 可以创建多个索引
3. 普通索引： 允许为空 可以创建多个索引
4. 全文索引：可以对文本的内容进行分词，搜索
5. 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
6. 组合索引：多个列组成一个索引，用于组合搜索

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. 非聚簇索引一定会回表查询吗？

48. 组合索引是什么？为什么需要注意组合索引中的顺序？

• 组合索引：由多个列创建的索引。索引策略是最左原则

49. 什么是死锁？怎么解决？

• 死锁是指两个或以上的事务占用同一资源并请求锁定对方资源，从而导致形成恶性循环的现象

• 死锁的四个必要条件：互斥，请求和保持，环路等待，不剥夺。解决思路是切断环路

50. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

• zabbix，lepus

mysql 分区有哪几种

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

索引底层是什么数据结构？

B + 树。

为什么用的是 B + 树，不能使用红黑树或者其他的？

可以使用红黑树。但是这样的话可能会造成树的高度过高，意味着相同查询下，会进行更多的磁盘 I/O，影响性能，而 B+ 树可以保持树的高度不至于过高