MYSQL常见问题总结

MYSQL常见问题总结

---

目录

1. 数据库的三范式是什么？
2. 一张自增表里面总共有7条数据，删除了最后2条数据，重启MySQL数据，又插入了一条数据，此时id是几？
3. 如何获取当前数据库版本号？
4. 说一下ACID是什么？
5. char 和 varchar 的区别是什么？
6. float 和 double 的区别是什么？
7. mysql 的内连接，左连接，右连接有什么区别？
8. mysql 索引是怎么实现的？
9. 怎么验证 mysql 的索引是否满足需求？
10. 说一下数据库的事务隔离？
11. 说一下 mysql 常用的引擎？
12. 说一下 mysql 的行锁和表锁？
13. 说一下乐观锁和悲观锁？
14. mysql 问题排查都有哪些手段？
15. 如何做 mysql 的性能优化？

---

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

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

具体介绍见博客：https://www.cnblogs.com/wsg25/p/9615100.html

---

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

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

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

---

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

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

---

4. 说一下ACID是什么？

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

---

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

• char(n)：固定长度类型，比如订阅char(10)，当你输入"abc"三个字符的时候，它们占的空间还是10个字节，其他7个字节是空字节。

char优点：效率高；
char缺点：占用空间；
适用场景：存储密码的md5值，固定长度的，适用char非常合适。

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

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

---

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

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

---

7. mysql 的内连接，左连接，右连接有什么区别？

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

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

---

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

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

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

---

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

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

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

---

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

MySQL 的事务隔离是在 MySQL.ini配置文件里添加的，在文件的最后添加

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITED、READ-COMMINTTED、REPEATABLE-READ、SERIALIZABLE.

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

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

不可重复读：表示一个事务范围内两个相同的查询却返回了不同数据。

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

---

不可重复读和幻读的区别：

幻读的重点在于新增或者删除，不可重复读的重点是修改

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert和delete的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

---

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

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

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

---

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

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

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

---

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

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

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

两种锁的使用场景
从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

---

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

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

---

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

• 为搜索字段创建索引。
• 避免使用 select * ，列出需要查询的字段。
• 垂直分割分表
• 选择正确的存储引擎。