MySql模块（十七）

Java八股文综合常见面试题及答案Java八股文综合常见面试题及答案_java面试题-优快云博客

MySql （如有发现不足之处欢迎指教，相互学习，只为自己学习的同时，方便大家）

164.数据库的三范式是什么？

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

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

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

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

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

167. 说一下 ACID 是什么？

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

168. char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

169. float 和 double 的区别是什么？

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

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

内连接关键字：inner join；左连接：left join；右连接：right join。

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

171. mysql 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

172. 怎么验证 mysql 的索引是否满足需求？

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

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

173. 说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

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

不可重复读 ：是指在一个事务内，多次读同一数据。

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

174. 说一下 mysql 常用的引擎？

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

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

175. 说一下 mysql 的行锁和表锁？

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

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

176. 说一下乐观锁和悲观锁？

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

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

177. mysql 问题排查都有哪些手段？

1. 日志分析

• 错误日志（Error Log）：记录 MySQL 启动、运行和关闭过程中的错误信息。

  • 位置：log_error 参数指定。

  • 使用：查看错误日志，定位启动失败或运行中的错误。

• 慢查询日志（Slow Query Log）：记录执行时间超过指定阈值的查询。

  • 启用：设置 slow_query_log 和 long_query_time。

  • 使用：分析慢查询日志，优化查询性能。

• 通用查询日志（General Query Log）：记录所有执行的 SQL 语句。

  • 启用：设置 general_log。

  • 使用：分析所有查询，定位问题 SQL。

• 二进制日志（Binary Log）：记录所有更改数据的 SQL 语句。

  • 启用：设置 log_bin。

  • 使用：用于数据恢复和主从复制。

2. 性能监控

• Performance Schema：提供详细的性能监控数据。

  • 使用：查询 performance_schema 数据库中的表，分析性能瓶颈。

• Information Schema：提供数据库元数据信息。

  • 使用：查询 information_schema 数据库中的表，获取表、索引、锁等信息。

• SHOW STATUS：查看服务器状态变量。

  • 使用：执行 SHOW GLOBAL STATUS，分析服务器状态。

• SHOW PROCESSLIST：查看当前连接的线程和执行的查询。

  • 使用：执行 SHOW PROCESSLIST，分析当前活动连接和查询。

3. 查询优化

• EXPLAIN：分析查询执行计划。

  • 使用：在查询前加 EXPLAIN，查看查询的执行计划，优化查询。

• EXPLAIN ANALYZE：实际执行查询并分析执行计划。

  • 使用：在查询前加 EXPLAIN ANALYZE，获取实际执行数据。

4. 锁和事务分析

• InnoDB 锁信息：查看 InnoDB 锁信息。

  • 使用：查询 information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS。

• 事务信息：查看当前事务信息。

  • 使用：查询 information_schema.INNODB_TRX。

5. 配置检查

• SHOW VARIABLES：查看服务器配置变量。

  • 使用：执行 SHOW VARIABLES，检查配置参数。

• 配置文件检查：检查 MySQL 配置文件（如 my.cnf 或 my.ini）。

  • 使用：确保配置参数合理，避免配置错误。

6. 资源使用分析

• CPU 和内存使用：监控服务器 CPU 和内存使用情况。

  • 使用：使用 top、htop、vmstat 等工具。

• 磁盘 I/O：监控磁盘 I/O 使用情况。

  • 使用：使用 iostat、iotop 等工具。

• 网络流量：监控网络流量。

  • 使用：使用 iftop、nload 等工具。

7. 复制和主从同步

• 复制状态：查看主从复制状态。

  • 使用：执行 SHOW SLAVE STATUS，检查复制状态和延迟。

• 二进制日志分析：分析二进制日志，定位复制问题。

  • 使用：使用 mysqlbinlog 工具分析二进制日志。

8. 备份和恢复

• 备份检查：检查备份是否完整和可用。

  • 使用：定期测试备份恢复，确保备份有效。

• 恢复测试：测试数据恢复过程。

  • 使用：模拟数据丢失，测试恢复过程。

9. 第三方工具

• 监控工具：使用第三方监控工具（如 Zabbix、Prometheus、Grafana）。

  • 使用：监控 MySQL 性能指标，设置告警。

• 分析工具：使用第三方分析工具（如 pt-query-digest、Percona Toolkit）。

  • 使用：分析慢查询日志，优化查询性能。

178. 如何做 mysql 的性能优化？     

1. 数据库设计优化

• 规范化与反规范化：根据需求平衡规范化和反规范化，减少冗余数据，提升查询效率。

• 索引优化：为常用查询条件创建索引，避免全表扫描。注意索引不宜过多，以免影响写性能。

• 数据类型选择：选择合适的数据类型，避免使用过大或不匹配的类型。

2. 查询优化

• 优化查询语句：避免使用 SELECT *，只选择需要的列。使用 EXPLAIN 分析查询执行计划。

• 避免复杂查询：将复杂查询拆分为多个简单查询，减少锁争用和资源消耗。

• 使用 LIMIT：限制返回的行数，减少数据传输量。

3. 索引优化

• 创建复合索引：根据查询条件创建复合索引，注意顺序。

• 避免冗余索引：定期检查并删除未使用的索引。

• 使用覆盖索引：确保查询所需的所有列都在索引中，避免回表。

4. 配置优化

• 调整缓冲区大小：增加 innodb_buffer_pool_size 以提升 InnoDB 性能。

• 优化连接数：调整 max_connections 避免连接过多导致资源耗尽。

• 调整查询缓存：根据需求启用或禁用查询缓存，避免缓存失效带来的开销。

5. 硬件优化

• 使用 SSD：提升磁盘 I/O 性能。

• 增加内存：提升缓冲区和缓存性能。

• 优化网络：确保数据库服务器与客户端之间的低延迟和高带宽连接。

6. 分区和分表

• 分区表：对大表进行分区，提升查询性能和管理效率。

• 分表：将大表拆分为多个小表，减少单表数据量。

7. 锁优化

• 减少锁争用：使用行级锁（InnoDB）代替表级锁（MyISAM）。

• 事务优化：缩短事务时间，减少锁持有时间。

8. 复制和读写分离

• 主从复制：通过主从复制分散读负载，提升读取性能。

• 读写分离：将写操作集中在主库，读操作分散到从库。

9. 定期维护

• 优化表：定期使用 OPTIMIZE TABLE 整理表碎片。

• 分析表：使用 ANALYZE TABLE 更新表统计信息，帮助优化器生成更好的执行计划。

• 备份和恢复：定期备份数据，确保灾难恢复能力。

10. 监控和调优

• 监控工具：使用性能监控工具（如 Performance Schema、慢查询日志）识别瓶颈。

• 慢查询日志：启用慢查询日志，分析并优化慢查询。

• 定期审查：定期审查数据库性能和配置，持续优化。

11. 使用缓存

• 查询缓存：根据需求启用查询缓存，避免频繁查询数据库。

• 应用层缓存：使用 Redis、Memcached 等缓存常用数据，减少数据库访问。

12. 优化存储引擎

• 选择合适的存储引擎：根据需求选择 InnoDB（支持事务、行级锁）或 MyISAM（读密集型）。

• 调整存储引擎参数：根据负载调整存储引擎参数，如 innodb_flush_log_at_trx_commit。