【MySQL进阶】事务、存储引擎、索引、SQL优化、锁

一、事务

1.概念 

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向 系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

例子：转账，要求扣钱和进账同时成功

默认MySQL的事务自动提交，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

2.事务操作

（1）方法1

当设置为手动提交之后，在执行任何DML语句时，都要在最后添加commit语句才能提交成功

如果出现异常，不要提交，而是需要执行rollback语句，将更改的部分数据进行恢复

（2）方法2

3.事务的四大特性

一致性指的是能量守恒

4.并发事务问题

（1）脏读（未提交）

（2）不可重复读（提交后）

（3）幻读

5.事务隔离级别

在最后一个隔离级别（级别最高，数据最安全，但性能最差）下，解决了三个并发事务问题

mysql中的默认隔离级别是repeatable read

二、存储引擎/表类型

1.MySQL体系结构

存储引擎控制数据存储方式，数据库服务器通过API与其交互

2.概念

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
创建表的时候可以指定存储引擎，不指定的话默认是InnoDB

3.InnoDB

InnoDB的逻辑存储结构：

 4.MyISAM

5.Memory

 6.区别

7.选择

存储引擎没有好坏之分，需要根据业务选择合适的，绝大多数还是选择InnoDB

三、索引

1.概念

索引是帮助MySQL高效获取数据的数据结构（有序），这些数据结构以某种方式引用（指向）数据，这种数据结构就是索引。使用索引可以快速找出在某个或多个列中有一特定值的行。

索引在存储引擎中实现，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

2.引入

不加索引时，要查询某一条数据，需要全表扫描

加上索引时，查询更加高效

优势：提高查询效率、提高排序效率

劣势：索引列占空间、降低更新表的速度

3.索引结构

索引是在存储引擎层实现的，不同的存储引擎有不同的结构：（如果没有特别指明，都是指的B+Tree索引）

（1）引入二叉树

红黑树：自平衡二叉查找树

（2）引入Btree（多路平衡查找树）

叶子结点和非叶子结点都会存放数据

中间元素向上分裂 

 （3）B+Tree结构

所有数据都会存到叶子结点，叶子结点形成了一个单向链表

中间元素向上分裂 的同时，要把此元素在叶子结点也保留，同时用单向链表

（4）Hash结构

（5）思考

4.索引分类

回表查询：如果通过姓名想查询到所在的这一行数据，需要先在二级索引中查到ID，再通过聚集索引查到一行数据，因此根据ID查效率更高

 5.索引语法

创建索引：

创建联合索引：多个字段同时加上索引，字段的顺序有讲究（使用最频繁的字段放在左侧）

删除索引：

6.性能分析

（1）SQL执行频率

 （2）慢查询日志

通过慢查询日志来定位执行效率比较低（超过预设的执行时间）的SQL语句，进而优化

（3）profiles查看耗时

如果有些简单的指令即使没有被记录到慢查询日志中，但是效率也很低，怎么查看具体用时明细？

查看每一条SQL的耗时情况：

查看一条语句各个阶段的耗时情况：

（4）explain

四、SQL优化

1.insert优化

2.大批量插入数据

通过导入本地文件（数据按一定规则组成）加载到表中

3.主键优化

页

五、锁

1.概念

2.分类

3.全局锁---实现数据备份

（1）使用场景

对整个数据库实例加锁，处于只读状态，后续的更新操作语句都将被阻塞

典型的使用场景是：做全库的逻辑备份，获取一致性视图，保证数据的完整性

注意：备份不要在mysql命令中执行，而是在windows中的命令行执行

（2）特点

（3）不加锁的备份

4.表级锁

（1）使用场景

（2）分类

（3）表锁

读锁不会阻塞其他客户端的读，但是会阻塞写

写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写

（4）元数据锁

（5）意向锁

当线程A加了行锁之后，如果线程B要加表锁，需要一行行检查数据是否加了锁

线程A在加了行锁之后再对整张表加意向锁，线程B通过看表锁与意向锁是否兼容来决定是否添加表锁，如果不兼容，就会阻塞，直到线程A提交释放锁

意向锁与表锁之间的兼容情况：

5.行级锁

（1）使用场景

（2）分类