MySQL知识点归纳总结（一）

1、事务的四大特性？

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

1. 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
2. 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两 人之间转账之后无论成功还是失败，它们的账户总和还是1000。
3. 隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
4. 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库 系统遇到故障的情况下也不会丢失提交事务的操作。

数据库中是如何实现原子性的？

undo log

undo log称之为回滚日志，每条数据的变化（insert/update/delete）都会产生一条记录，并且日志持久化到磁盘，undo log用来记录数据修改前的信息，比如说要插入一条记录，那么undo log就会记录一条删除该信息的语句，这样需要回滚的时候undo log就会执行删除之前插入的那条记录，达到没有修改前的状态，更新一个记录也会生成一条sql记录更新前的字段状态，从而实现了原子性。

数据库中是如何保证一致性的？

靠其他三条特性共同保证的。

数据库中是如何保证隔离性的？

MVCC机制

数据库中是如何保证持久性的？

Redolog

2、数据库的三大范式

第一范式1NF

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

比如字段 userInfo : 山东省 1318162008’ ，依照第一范式必须拆分成 userInfo : 山东省 userTel : 1318162008 两个字段。

第二范式2NF

依赖于全部的主键，而不是一部分主键。

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主 键，而不能只依赖于主键的一部分。 举个例子。假定选课关系表为StudentCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，主键为(学号, 课 程名称)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余 （学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记 录）等问题。 可以拆分成三个表：学生：Student(学号, 姓名, 年龄)；课程：Course(课程名称, 学分)；选课关系： StudentCourseRelation(学号, 课程名称, 成绩)。

第三范式3NF

直接依赖于主键，不能传递依赖。

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。 假定学生关系表为Student(学号, 姓名, 年龄, 学院id, 学院地点, 学院电话)，主键为"学号"，其中学院id依 赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。 可以把学生关系表分为如下两个表：学生：(学号, 姓名, 年龄, 学院id)；学院：(学院,id 地点, 电话)。

2NF和3NF的区别？

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

3、事务隔离级别和存在的问题有哪些？

问题：脏读、不可重复读、幻读。

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

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

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

四种隔离级别：

1. Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。
2. Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
3. Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读 取数据时，会看到同样的数据行，解决了不可重复读的问题。
4. Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

4、为什么使用B+树索引，其他索引不好吗？

B+树的查询效率更加稳定，任何关键字的查找必须走一条到根节点的路径。所有查询路径长度相同，导致每一个数据查询的效率相当。

B+树所有数据都存在叶子节点，范围扫描更加方便。B树需要进行中序遍历。

B+树的节点只存储key，其他信息存放在页中，这就使得索引中可以存放更多的节点，减少IO的支出。

B树数据不仅仅存在叶子中。

哈希的分布没有规律，范围查找不合适，性能不稳定，无法排序等等。

红黑树也存在这个问题，范围查找等不合适，性能不稳定。和B树有差不多的缺点。

5、索引有什么分类？

聚簇索引/主键索引：

​ 主键形成的索引，如果没有规定主键也会有一个隐藏字段作为主键生成这个索引，默认就会生成这个索引。

​ 叶子节点中存放的是整张表的数据，这也就应了我们经常说的回表查询。

二级索引：

1. 普通索引：正常的索引
2. 唯一索引：这种索引允许null的，它的主要作用是为了保证数据唯一性，一张表可以创建多个唯一索引
3. 联合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段 时，索引才会被使用，使用组合索引时遵循最左前缀原则。
4. 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索 引。
5. 前缀索引：有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。 前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

6、最左匹配原则是什么？

这是原则在创建联合索引时使用，主要是索引的生效方式。当遇到范围查询时会停止匹配，后面的字段不会使用索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。

对(a,b,c,d)建立索引，查询条件为 a = 1 and b = 2 and c > 3 and d = 4 ，那么，a,b,c三个字段能 用到索引，而d就匹配不到。因为遇到了范围查询！

7、什么是覆盖索引？

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

不是所有类型的索引都可以成为覆盖索引。

覆盖索引要存储索引列的值，而哈希索引、全文索引不存储 索引列的值，所以MySQL只能使用b+树索引做覆盖索引。

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

8、常见的存储引擎有哪些？

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优 化，如能够自动在内存中创建自适应hash索引，以加速读操作。 优点：支持事务和崩溃修复能力。InnoDB引入了行级锁和外键约束。 缺点：占用的数据空间相对较大。 适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。 MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。 优点：访问速度快。 缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。 适用场景：对事务完整性没有要求；只读的数据，或者表比较小，可以忍受修复repair操作。

MEMORY存储引擎

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。 MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

9、索引失效的场景有哪些？

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。