文章详细对比了InnoDB和MyISAM存储引擎的特点，探讨了事务的ACID特性、隔离级别以及可能出现的问题。同时，介绍了MySQL的四种隔离级别，分析了脏读、不可重复读和幻读的概念。此外，还讨论了数据库事务的粒度，如乐观锁和悲观锁，并概述了SELECT语句的执行顺序。文章还涉及了MySQL主从同步的原理，数据库性能优化策略，以及索引的类型和数据结构。
目录
一、InnoDB与MyISAM 存储引擎的特点
| InnonDB | MyISAM |
|---|---|
| 支持事务处理，具有原子性 | 不支持事务，回滚将造成不完全回滚，不具有原子性 |
| 支持外键 | 不支持外键 |
| 支持行锁 | |
| 不支持FULLTEXT(全文索引)不过这个区别仅限于5.6以前的mysql | 支持全文搜索 |
| 不保存表的具体行数，扫描表来计算有多少行 | 保存表的具体行数，不带where时，直接返回保存的行数 |
| 对于AUTO_INCREMENT（自增）类型的字段，必须包含只有该字段的索引 | AUTO_INCREMENT类型字段可以和其他字段⼀起建⽴联合索 |
| DELETE（删除）表时，是一行一行删除的 | DELETE表时先drop表，然后再重建表 |
| InnoDB把数据和索引存放在表空间里 | MyISAM表被存放在三个文件，frm ⽂件存放表格定义。 数据⽂件是MYD (MYData) 。 索引⽂件是MYI (MYIndex)引伸 |
| 跨平台可直接拷贝使用 | 跨平台很难直接拷贝 |
| 表格很难被压缩 | 表格可以被压缩 |
如何选择：
MyISAM：读多，写少。对原子性要求低，并且MyISAM恢复速度快，可以直接用备份覆盖恢复
InnoDB：读少，写多，尤其是并发写入高的时候
二、事务的四大特征（ACID）
原子性
事务是一组不可分割的操作单元，要么全部完成，要么不完成，不可能停止在中间环节，如果执行不成功，就会进行回滚，到最初的状态
一致性
在事务的开始和结束数据的完整性必须一致（例如在金额存取的时候数据库的值是根据操作进行增加的减少的）
隔离性
多个用户进行串行化的操作时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据相互隔离
持久性
在事务提交后，该事务对数据库中的数据进行改变就是永久性的
三、MySQL的隔离级别
隔离级别：
用来限定事务内哪些改变可见、哪些改变不可见。
低级别的隔离级别一般支持更高的并发处理，并且拥有更低的系统开销。
越高级的隔离级别，安全性会越好，但是性能越低。
Read uncmmitted（ 读取未提交内容）
所有事务都可以看到未提交事务的执行结果
问题：会产生脏读 ，安全性最低，一般不使用
Read Committed （读取提交内容）
大部分数据库的默认隔离级别，只能看见已经提交的事务所做的改变，一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非提交
问题：可能会产生 不可重复读，意味着用户运行相同的sql语句，可能会看到不同的结果
Repeatable Read （可重复读）
mysql默认的隔离级别，解决了不可重复读的问题。它保证同一个事务的多个实例在并发读取事务时，会看到相同的数据行，InnDB默认级别
问题：可能会产生幻读
Serializable （可串行化）
完全串行化读取，每次读都需要获取表级共享锁，读写相互都会阻塞
问题：这是这几种隔离级别中效率最低的
脏读、不可重复读、幻读
脏读
一个事务读取到了未提交的事务执行过程的数据
多个事务同时操作一个数据时，当其它事务还未提交时，当前事务读取到其他事务未提交的数据，导致读取的数据并不是最终持久化之后的数据
不可重复读
同一个事务中相同的查询语句看到的结果是不一样的，这是在事务执行过程中，数据被其他事务提交修改了
一个事务执行过程中，另一个事务提交并修改了当前事务正在读取的数据，导致相同的语句读出的结果不同
幻读
是事务非独立执行时发生的一种现象
事务1 对表的某一列为1的值进行批量修改使其变更为数值2，并进行提交
同时
事务2 在这张表插入一条值为1的数据，并进行提交
事务1 在修改完数据后进行查看，会在修改完成后还看到一个1的数值，其实这值是事务2提交插入的
幻读、不可重复读比较
相同点：幻读和不可重复读都是读取了另一个已经提交的事务（这点与脏读不同）
不同点：不可重复读 查询的是同一个数据项，幻读 是针对一批数据整体（比如数据的个数）
四、数据库事务的几种粒度
数据库锁机制可分为多种力度的： 表 、 页 、行
粒度越大，开销越小，加锁越快，但是实现并发处理的能力就越差，发生锁冲突的概率就越大
数据库中的锁大致分为两个大类 悲观锁、乐观锁，乐观锁一般是通过程序实现的锁机制，mysql提供的锁一般都是悲观锁
乐观锁
锁很乐观，认为每次拿数据时都不会修改，具体是给表增加一个版本号的字段，在执行update操作时比较版本号是否与当前数据库版本号一致，如果一致则更新，不一致则拒绝
悲观锁
锁有点悲观主义，认为每次拿数据时都会被修改。读取数据时就会上锁，直到update完成才释放锁
表锁
MySQL中粒度最大的锁，为当前操作的整张表加锁。MyISAM和InnoDB都支持表级锁
行锁
MySQL中粒度最小的锁，针对当前指定的行加锁，减少数据库操作的冲突。InnoDB默认采用行锁
五、select语句执行顺序
where -> group by -> having ->select ->order by
六、MySQL主从同步原理
Reply 回放，执行sql语句
执行步骤
-
master服务器将数据的改变记录二进制binary log 日志，当master上的数据发生改变时，则将其改变写入二进制日志中
-
slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个IO Thread请求master二进制事件
-
同时主节点为每个IO线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，同时节点将启动sql线程从中继日志中读取二进制日志，在本地重放，使得数据和主节点保持一致，最后IO Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒
整个过程会有两个线程被创建：IO线程，SQL线程
IO会从主库请求binary log ,并将得到的 binary log(日志数据)写到本地的relay-log(中继日志)文件中
主库会生成一个log dump线程，用来给从IO线程传binary log
SQL线程，会读取relay log文件中的日志，并解析成sql语句逐一执行
七、优化数据库性能
- 选择合适的数据库引擎，合理使用索引
- 分页获取数据，获取需要的字段（不要使用*）
- 优化业务逻辑，减少数据库IO
- 部署主从服务器
- 分库分表
- 升级硬件
八、聚簇和非聚簇索引的区别
都是B+树的数据结构
聚簇索引：将数据存储与索引放在一块，索引结构的叶子节点保存了行数据（主键索引）
非聚簇索引：将数据与索引分开存储，索引结构的叶子结点指向了数据对应的位置
在innoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引
如 复合索引，前缀索引，唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值
辅助索引访问数据总是需要二次查找
九、InnoDB中索引的分类
主键索引
设置主键后数据库会自动建立索引，并且主键索引列的值不能为null
普通索引（单列索引）
即一个索引包含单个列，一个表可以有多个单列索引
唯一索引
索引列的值必须唯一，但是允许空值（只能有一个null）
复合索引
一个索引包含多个列,其中使用复合索引需要遵循最左前缀原则
最左前缀原则：
如果需要通过复合索引查询就必须提供该索引时最前面的字段
例如：
索引列： a、b、c组成复合索引
使用该索引时必须提供a字段的条件，条件不一定需要按照顺序来进行编写，数据库会在执行时进行匹配
十、MySQL索引的数据结构
索引的数据结构和存储引擎有关系，在MySQL中使用较多的索引 有hash索引、B+Tree索引
B+Tree索引
平衡多叉树，根节点到叶子结点高度差不超过1，同层级节点间有指针相连，基于索引的顺序扫描时，可以利用双向指针快速左右移动，效率很高。因此广泛已用于数据库
hash索引
hash索引就是采用一定的哈希算法把键值换算成新的哈希值，检索是不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次哈希算法立即定位到相应位置，速度很快
总结：
在绝大多数需求为单表等值查询的时候，可以选用哈希索引，查询性能最快（哈希索引在进行范围查询时性能不佳），
其余大部分场景，建议使用B+Tree索引
十一、MySQL慢查询调优
- 查看是否利用索引，如果没有则使用索引，如果有索引查看是否为最优解
- 避免使用select * ,根据需求进行分字段查询
- 字段尽量使用not null
- 拆分大的delete和insert语句，delete和insert会锁表
- 表中数据过多，需要进行分库分表
- 检查服务器性能的影响
十二、SQL什么情况下不走索引
- select*可能导致不走索引
- 索引列有函数运算不走索引，可以在索引建立一个函数的索引
- !=或者 < > 可能导致不走索引
- 空值会导致不走索引，因为hashset不能存空值
十三、InnoDB实现事务大致流程
这里展示的是执行update时事务的流程
十四、Union和UnionALL的区别
Union：是对两个结果集进行并集操作，不包括重复的行，同时进行默认的排序
UnionAll：是对两个结果集进行并集操作，包括重复行，不进行排序
十五、mysql的内连接、左连接、右连接区别
内连接（inner join） 是将所有符合条件的列都进行展示，左连接（left join）根据条件左边表数据全部显示出来，右边的表显示出符合条件的数据，右连接（right left）的数据展示就是与左连接相反是以右表为主体展示数据
十六、数据库的三大范式
第一范式
确保每一列都保持原子性
第二范式
确保表中的每一列都和主键相关
第三范式
确保每列都和主键列直接相关，而不是间接关联
十七、验证mysql的索引是否满足需求
使用explain查看sql是如何执行查询语句的
explain select * from tableName
十八、MVCC（多版本并发控制）实现原理
RC（读已提交）、RR（可重复度）基于MVCC进行并发事务控制， mvcc是基于“数据版本”对并发事务进行访问
先介绍：
undolog：回滚日志
readView(读视图)：快照读SQL执行时MVCC提取数据的依据
快照读、当前读
快照读：普通的select 查询SQL语句
当前读： 指代执行（insert、update、delete）语句进行数据读取的方式（行锁+间隙锁）
readView
ReadView是一个数据结构
m_ids:当前活跃的事务编号
min_trx_id:最小活跃事务编号
max_trx_id:预分配事务编号，当前最大事务编号+1
creator_trx_id:ReadView创建者的事务编号
版本链访问规则：
1.当时事务ID = create_trx_id吗？ 则读取该事务修改提交后的数据 ： 不等于进入判断2
2.判断trx_id(当前数据事务ID) < min_trx_id ? 则表示数据已提交可以访问
3.trx_id > max_trx_id ? 说明该事物是在ReadView生成以后才开启，不允许访问
4.判断 min_trx_id <= trx_id <= max_trx_id,如果符合区间，与m_ids中的数据作对比，如果不存在于m_ids则表示数据已经被提交，可以访问
RC、RR对比
读已提交（RC）：在每次执行快照读时生成ReadView
可重复读（RR）：在同一个事务中，仅在第一次执行快照读的时候生成ReadView,后续快照读复用
产生幻读的原因
在RR隔离级别 连续多次快照读，ReadView会产生复用，没有幻读问题
特例：当两次快照读之间存在当前读，ReadView会重新生成，导致幻读
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
