1.MySQL面试题总结

MySQL事务与索引深度解析

最新推荐文章于 2025-12-12 17:07:47 发布

原创最新推荐文章于 2025-12-12 17:07:47 发布 · 52 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文详细介绍了MySQL的事务处理，包括ACID特性、事务隔离级别以及常见问题。同时，深入探讨了索引的概念、作用、类型，如B+树、最左匹配原则、聚簇与非聚簇索引等。此外，还提到了存储引擎如InnoDB和MyISAM的特性对比，以及SQL优化和数据库架构的相关知识。

MySQL总结的差不多了，先把这些给掌握了，再看更加深入的

一.事务(transaction)(事务：一组操作，要么全都执行，要么全不执行)

1.事务的四大特性：ACID “愿意离舅”

原子性(Atomicity)：一个事务的全部操作要么全部成功，要么全部失败回滚(要么全都执行，要么全不执行)

一致性(Consistency)：事务执行前后，数据保持一致。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。(数据一致)

隔离性(Isolation)：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。(隔离级别)(多个事务并发执行)

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

2.事务的隔离级别

(1)几个概念：脏读，不可重复读，幻读

脏读：一个事务读取到了其他未提交事务的执行结果

不可重复读：一个事务前后两次读取到的数据不一致。这是因为在查询间隔，另一个事务修改了数据。关注重点是：修改

幻读：一个事务前后两次读取到的数据记录数不一致。关注重点是：增加或删除

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。幻读和不可重复读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除

(2)有4中隔离级别：(读未，读已，可重复，串行化)

Read uncommitted(读未提交)：一个事务可以看到其他未提交事务的执行结果

Read committed(读已提交)：一个事务只能看到其他已提交事务的执行结果。可避免脏读的发生

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

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

二.约束

主键，外键，唯一，默认值，非空

三.索引

1.定义：索引就相当于key-value的key(索引相当于音序表，给字段排序了)

2.索引的作用
(1)B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升

(2)(表user(id，name，age，sex))

什么情况下需要建索引：(用于查询，用于连接，用于排序)

经常用于查询的字段，可以加快查询的速度
经常用于连接的字段(如外键)建立索引，可以加快连接的速度
经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询的速度

什么情况下不建索引？(记录少，where条件表达式中用不到的字段(不咋查找的字段)，经常增删改，区分度不高的字段，参与列计算的列)

1. 表记录较少
2. where条件中用不到的字段不适合建立索引
3. 需要经常增删改
4. 区分度不高的字段不适合建立索引，如性别
5. 参与列计算的列不适合建立索引

3.B+树

(1)B树：平衡多路查找树。其实就是二叉查找树的扩展情况

(2)B树和B+树的树结构

4.索引实例
5.索引分类+约束分类

(1)约束分类：主键，外键，非空，唯一，默认

(2)MySQL目前主要有以下几种索引类型：(主键，唯一，普通，组合，全文)
　　　　1.普通索引：最基本的索引，它没有任何限制
　　　　2.唯一索引：索引字段的值必须唯一。但是允许有空值
　　　　3.主键索引：索引字段的值唯一非空
　　　　4.组合索引：索引由多个字段组合创建
　　　　5.全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

6.最左匹配(最左前缀原则)

索引存在B+树中，所以自动有序

(1)如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引

(2)对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序(当a相等时，会对b进行比较排序)。直接执行b = 2这种查询条件没有办法利用索引。

7.聚簇索引 / 非聚簇索引

(1)InnoDB用聚簇索引，MyISAM用非聚簇索引

(2)聚簇索引：叶子节点中直接存储记录

　　非聚簇索引：叶子节点存储的是指向记录的指针

8.覆盖索引

9.索引失效

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
判断索引列是否不等于某个值时
对索引列进行运算
使用or连接的条件，如果左边的字段有索引，右边的字段没有索引，那么左边的索引会失效

10.SQL调优技巧

(1)使用连接代替子查询

(2)使用索引

(3)选择合适的字段属性。将表中的字段宽度设置的尽可能小，数据库中的表越小，执行查询就越快

(4)尽量把字段设置成NOT NULL。这样在将来执行查询的时候，数据库不用去比较NULL值

11.MySQL慢查询优化

(1)分析语句：首先分析语句，看看是否包含了额外的数据，可能是查询了多余的行并抛弃掉了，也可能是加了结果中不需要的列，要对SQL语句进行分析和重写。
(2)分析索引：分析优化器中索引的使用情况，要修改语句使得更可能的命中索引。比如使用组合索引的时候符合最左前缀匹配原则。not in,not like都不会走索引，可以优化为in.
(3)分库分表：如果对语句的优化已经无法执行，可以考虑表中的数据是否太大，如果是的话可以横向和纵向的切表。

四.存储引擎

1.InnoDB

(1)

优点：支持事务和崩溃修复能力。InnoDB引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

2.MyISAM

(1)

优点：访问速度快。

缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；只读的数据，或者表比较小，可以忍受修复repair操作。

3.MEMORY

(1)

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。哈希索引使用拉链法来处理哈希冲突

4.MyISAM和InnoDB区别

1. 是否支持行级锁 : MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁来实现；各数据库中MVCC实现并不统一。
5. MyISAM不支持聚集索引，InnoDB支持聚集索引。

myisam引擎主键索引和其他索引区别不大，叶子节点都包含索引值和行指针。 innodb引擎二级索引叶子存储的是索引值和主键值(不是行指针)，这样可以减少行移动和数据页分裂时二级索引的维护工作。

五.MVCC(多版本并发控制)

六.分库分表

垂直划分，水平划分

七.MySQL架构

***

1.` `这个符号的使用是防止别名和关键字冲突