mysql

一般来说，事务需要满足4个条件（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）,mysql默认自动提交事务

原子性

事务的原子性，要么全部完成，要么完全不起作用

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性

执行事务前后，数据保持一致，多个事务对同一个数据库读取的结果是相同的

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，数据库中各个事务相互独立

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性

一个事务被提交后，改变是持久的

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

##事务的隔离级别

1. READ-UNCOMMITTED(读取未提交)

   最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

2. READ-COMMITTED(读取已提交)

   允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

3. REPEATABLE-READ(可重复读)

   对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

4. SERIALIZABLE(可串行化)

   最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

InnoDB默认是可重复读级别的

• ① 脏读:

• 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

• ② 不可重复读:

• 是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

• ③ 幻读:

• 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样，幻读是数据行记录变多了或者少了。

简单点总结下他们的区别：脏读是指读取了未修改完的记录，不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致，而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题。

==丢失修改（Lost to modify）==指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

数据库锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

• 乐观锁：通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。
• -悲观锁：通常用于数据竞争激烈的场景，每次操作都会锁定数据。
  要锁定数据需要一定的锁策略来配合。
• 表锁：锁定整张表，开销最小，但是会加剧锁竞争。
• 行锁：锁定行级别，开销最大，但是可以最大程度的支持并发。

但是MySql的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

数据库索引分类：

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

mysql 主从复制原理及步骤

mysql主从是异步复制过程

1 master开启bin-log功能，日志文件用于记录数据库的读写增删；
2 需要开启3个线程，master IO线程，slave开启 IO线程 SQL线程；3 Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。
4 MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。
5 slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。
6 slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

数据库设计三大范式

1 ．第一范式(确保每列保持原子性) 列不可再分

2．第二范式(确保表中的每列都和主键相关) 属性完全依赖于主键

3．第三范式(确保每列都和主键列直接相关,而不是间接相关 属性不依赖于其它非主属性 属性直接依赖于主键

聚簇索引和非聚簇索引的区别

**聚簇索引：**将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

**非聚簇索引：**将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

mysql的优化：

1. 数据库表设计优化

2. 1. 所有表必须使用Innodb存储引擎
   2. 字符集统一使用utf-8
   3. 字段加注释
   4. 控制单表数据量的大小，建议控制在500w内
   5. 冷热数据分离，减小表宽度
   6. 禁止存储图片，文件等二进制数据
   7. 三大范式

3. 数据库字段设计优化

4. 1. 优先选择符合存储条件最小的数据类型，能用tinyint的就不用int，能用varchar的不用text
   2. 避免使用text，blob数据类型
   3. 尽可能把所有列定义为not null

5. 索引设计优化

6. 1. 限制每张表的索引数量，单张表不超过5个
   2. 禁止给每一列都建立单独索引
   3. 每个表必须有一个主键
   4. 频繁查询的字段建立索引

7. 编写sql优化

8. 1. 建议预编译语句进行数据库操作
   2. 避免数据类型的隐式转换
   3. 充分利用表上已经存在的索引
   4. 不要使用select * 查询，要使用select <字段列表> 查询
   5. 避免使用子查询，把子查询优化成join操作
   6. 避免使用join关联太多得表
   7. 对同一列进行or判断时，使用in代替or
   8. where从句中禁止对列进行函数转换和计算
   9. 在明显不会有重复值时使用union all而不是union
   10. 拆分复杂的大SQL为多个小SQL
   11. 使用 group by 分组查询是，默认分组后，还会排序，可能会降低速度，在 group by 后面增加 order by null 就可以防止排序.

Mvcc

Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了MVCC.实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。即为：不管需要执行多长时间，每个事务看到的数据都是一致的。

不同的存储引擎的MVCC实现不同，典型的有乐观并发控制和悲观并发控制。

innodb的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个是行的创建时间，一个保存行的过期时间。存储的是系统版本号，不是真实的时间。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

在REPEATABLE READ隔离级别下，MVCC具体操作：

SELECT

innodb会根据以下两个条件检查每行记录：

a.innodb只查找版本号早于当前事务版本的数据行，<=当前事务版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的

b.行的删除版本要么未定义，要么大于当前的事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

INSERT

INNODB为新插入的每一行保存当前系统版本号作为行版本号

DELETE

innodb为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

innodb为插入一行新纪录，保存当前系统版本号为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

MVCC只在repeatable read和read committed两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。

为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

innodb为插入一行新纪录，保存当前系统版本号为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

MVCC只在repeatable read和read committed两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。