mysql进阶整理

1. 事务

1.1 事务简介：

1.2 事务的4大特写ACID：

1.3 并发事务问题：

附加描述：

• 不可重复读：在同一个事务里面，同一条查询语句查到结果不一样，默认情况下，应该同一事务中查询的结果是一致的。

• 幻读：（id没有索引，有索引会重新间隙锁，也可防止幻读）有两个事务，比如，A事务查询并没有发现id=3的数据，B事务插入id=3的数据并提交，这个时候A事务插入id=3数据，发现无法插入，于是又去查询id=3的数据，结果还是没有查到。这就是幻读。

• 串行读可解决幻读：比如启动AB两个事务，A事务去查询id=3数据，没有查到，B事务去插入id=3的数据，将会无法插入，只有A事务结束后，B事务才能去执行插入操作。串行读可以理解为锁。

1.4 事务的隔离级别：

2. 存储引擎

2.1 mysql体系结构：

2.2 存储引擎的简介：

show engines 查看支持的mysql引擎

2.2.1 InnoDB引擎特点：

2.2.2 MyISAM引擎的特点：

2.2.3 Memory引擎的特点：

2.2.4 存储引擎的选择：

3. 索引

3.1 索引介绍：

3.2 索引类型：

3.3 各种树的结构：

3.3.1 二叉树

3.3.2 B-tree

附加解释：

• 5个指针是4个key中，key与key之间的范围

• B-tree，每个节点最多4个key，如果达到5个key，将会把中间那个key向上分裂

3.3.3 B+tree

附加解释：

• B+tree所有的数据都在叶子节点，非叶子结点只作为索引作用

• 阶可以自己选择，当阶满了，中间的key向上分裂，同时所有的key都在叶子节点可以找到

• 叶子节点是单向链表

mysql中的B+tree：

附加解释：叶子节点是双向链表

3.3.4 hash索引

3.4 索引面试题：

3.5 索引分类：

3.5.1 聚集索引（聚簇索引）和二级索引：

附加解释：

• 二级索引会出现回文查询，回表查询指定就是：二级索引找到对应row的id，再到聚集索引中进行查找row

select * from table where name = “arm”;的查找过程

3.5.2 面试思考题：

查找id的效率高。查找name还得通过回表查询，才能查到具体的row

第二题、

3.6 索引语法：

3.7 sql性能分析：

3.8 索引的使用原则（索引失效的原因）：

附加解释：

• abc为联合索引， abc完整索引（都出现的时候和顺序无关），ab损失c索引，ac损失bc索引，a损失bc索引，bc无索引，c、b都是无索引

附加解释：执行以上sql，第一条损失status这个索引，第二条可以使用完整的联合索引（范围查询的时候使用>= 、<= 可以避免索引损失）

附加解释：第一条sql不会导致索引失效，剩下两条会导致

3.9 sql提示

附加解释：use index--建议使用的索引，ignore index--不使用的索引，force index--必须使用的索引

3.10 覆盖索引

3.11 前缀索引

查询流程

附加解释：前缀索引设定后，可能索引不是唯一的，所以当回表查询后，还得比较email是否是一致的，如果不一致，辅助索引继续查找下一位继续回表。

3.12 单列、联合索引

3.12.1 联合索引的情况

3.13 索引设计原则

4. sql优化

4.1 插入优化

4.2 主键优化

4.2.1 主键顺序插入

4.2.2 乱序插入

附加解释：乱序插入的时候，如果都是满的，会找到对应的页的中间位置提取到另一个新页中，然后再重新排序。

4.2.3 页合并

4.2.4 主键设计原则

4.3 order by 优化

order by优化总结：

4.4 group by优化

4.5 limit优化

4.6 count优化

4.7 update优化

附加解释：

• mysql中update、delete、insert会加入行级排他锁；

• 共享锁（s锁），读锁，只可读，不可修改数据。开启事务的时候，会开启；

• 排他锁（x锁），写锁，只给一个事务使用，串行化这个隔离级别就是就是用了排他锁；

5. 锁

5.1 锁的介绍

5.2 mysql锁

5.2.1 mysql锁分类

5.2.2 全局锁

• 语法

• 全局锁备份可能存在的问题与解决：

5.2.3 表级锁

• 表锁

• 元数据锁

附加解释：可以理解为开启事务后，操控该表都会触发读/写锁，但这些都是共享锁，不会发生阻塞（要>=可重复读的隔离级别）。如果要alter表结构，则会触发exclusive排他锁，该锁和其他锁都为互斥，如果有其他锁存在将会发生阻塞，等待其他锁的事务提交完成后，才会执行。

• 意向锁

附加解释：当执行update的时候，会对行进行行锁，如果这个时候另外一个事务过来要上一个表锁，将需要全文查看当前表的行锁情况（性能不佳）；于是，在执行update的时候，除了发生行数锁，还要意向锁，这样另外一个事务如果要上表锁的时候，只需看意向锁即可（提高了上锁性能）

5.2.4 行级锁

• 行锁

自动添加的锁

• 间隙锁/临键锁

附加解释：

①第1条解释，id为唯一索引，比如对id=17进行update，因为没有找到，所以自动在16-18之间增加一个间隙锁，其他事务在这个id范围内也插入不进来。

②第2条的解释，会生成两个间隙锁，比如查找age为18 select * from table wehre id = 18 lock in share mode（普通查询不会自动加锁）。为了防止幻读，在16-18、18-29之间，加入了间隙锁。

③第3条解释，会生成临建锁，比如select * from table where id >= 29 lock in share mode。对29加行锁，29-98之间加临建锁，98-+无穷之间加临建锁。

6. InnoDB存储引擎

6.1 逻辑存储结构

6.2 内存结构

6.2.1 缓冲池

6.2.2 更改缓冲区

6.2.3 自适应hash索引

6.2.4 日志缓冲区

6.3 磁盘结构

6.4 后台线程

6.5 事务原理

6.5.1 redo log

6.5.2 undo log

6.6 MVCC

6.6.1 隐藏字段

6.6.2 MVCC undo log

6.6.3 readView

附加解释：在读已提交的隔离级别下

①第一个查询，快照查询的是事务2执行提交后的结果

②第二个查询，快照查询的说事务3执行提交后的结果

附加解释：可重复读隔离级别的时候，只会复用第一次快照查询的结果

7.mysql主从结构

7.1 概念

7.2 原理

总的来说，就是主库做的操作会记录到binlog的二进制文件里，然后从库去读取binlog到relaylog这个重做日志上面，然后进行重做，实现主从复制。