MySQL数据库应用MySQL数据库应用从入门到精通基础知识还原创建与开发

关注我，带你了解java

大家好，我是冰冰，前几天分享了MySQL从基础到高级的知识，现在接着分享一下MySQL面试中常见的问题！

本文目录：

事务的四大特性和实现原理？

事务的隔离级别？

数据库的三大范式？

索引相关：

1.索引的优缺点？

2.什么情况下需要建索引？什么情况下不需要？

3.索引的数据结构

4.Hash索引和B+树索引的区别？

5.什么是聚集索引？

6.什么是覆盖索引？

7.索引的设计原则？

8.索引使用有哪些注意事项？

一、事务的四大特性？

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

1.原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

2.一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

3.隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

4.持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务ACID特性的实现思想

1.原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

2.持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。

3.隔离性：通过锁以及MVCC,使事务相互隔离开。

4.一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

二、事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。

幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL数据库为我们提供的四种隔离级别：

Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。

Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。

Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

三、数据库的三大范式？

第一范式1NF：

确保数据库表字段的原子性。

比如字段 userInfo: 河北省 10010" ，依照第一范式必须拆分成 userInfo: 河北省 userTel:10010两个字段。

第二范式2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

可以拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF和3NF的区别？

2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。

3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

四、索引相关

1.索引的优缺点

优点：

加快数据查找的速度

为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

加快表与表之间的连接

缺点：

建立索引需要占用物理空间

会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

2.什么情况下需要建索引？什么情况下不需要？

什么情况下需要建索引？

经常用于查询的字段

经常用于连接的字段建立索引，可以加快连接的速度

经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不建索引？

where条件中用不到的字段不适合建立索引

表记录较少

需要经常增删改

参与列计算的列不适合建索引

区分度不高的字段不适合建立索引，如性别等

3.索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

4.Hash索引和B+树索引的区别？

哈希索引不支持排序，因为哈希表是无序的。

哈希索引不支持范围查找。

哈希索引不支持模糊查询及多列索引的最左前缀匹配。

因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

5.什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

6.什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。

7.索引的设计原则？

索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

8.索引使用有哪些注意事项？

可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则

索引哪些情况会失效

1.查询条件包含or，可能导致索引失效

2.如何字段类型是字符串，where时一定用引号括起来，否则索引失效

3.like通配符可能导致索引失效。

4.联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

5.在索引列上使用mysql的内置函数，索引失效。

6.对索引列运算（如，+、-、*、/），索引失效。

7.索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

8.索引字段上使用is null， is not null，可能导致索引失效。

9.左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

10.mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

1.数据量少的不适合加索引

2.更新比较频繁的也不适合加索引

3.区分度低的字段不适合加索引（如性别）

索引的一些潜规则

1.覆盖索引

2.回表

3.索引数据结构（B+树）

4.最左前缀原则

5.索引下推

星辰大海，永不止步

END