事务
四大特性
- 原子性
事务的原子性，要么全部完成，要么完全不起作用
- 隔离性
并发访问数据库时，一个用户的事务不被其他事务所干扰，数据库中各个事务相互独立
- 持久性
一个事务被提交后，改变是持久的
- 一致性
执行事务前后，数据保持一致，多个事务对同一个数据库读取的结果是相同的
mysql中关于事务的语句：
start trunsaction --开启事务
savepoint 保留点 --创建保留点(即：还原点)
rollback to 保留点 --回滚
commit --提交
并发事务带来哪些问题
- 脏读（Dirty read）:
当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- 丢失修改（Lost to modify）
指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
- 不可重复读（Unrepeatableread）
指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- 幻读（Phantom read）
幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
⚠️ 不可重复度和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除
事务的隔离级别有哪些
- READ-UNCOMMITTED(读取未提交)
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
| 隔离级别 | 脏读 | 不可重复读 | 幻影读 |
|---|---|---|---|
| 读取未提交 | ✅ | ✅ | ✅ |
| 读取已提交 | ❌ | ✅ | ✅ |
| 可重复读 | ❌ | ❌ | ✅ |
| 可串行化 | ❌. | ❌ | .❌ |
事务分类
- 扁平化事务
在扁平事务中，所有的操作都在同一层次，这也是我们平时使用最多的一种事务。它的主要限制是不能提交或者回滚事务的某一部分，要么都成功，要么都回滚。
- 带保存点的扁平事务
为了解决第一种事务的弊端，就有了第二种带保存点的扁平事务。它允许事务在执行过程中回滚到较早的状态，而不是全部回滚。通过在事务中插入保存点，当操作失败后，可以选择回滚到最近的保存点处。
- 链事务
可以看做是第二种事务的变种。它在事务提交时，会将必要的上下文隐式传递给下一个事务，当事务失败时就可以回滚到最近的事务。不过，链事务只能回滚到最近的保存点，而带保存点的扁平化事务是可以回滚到任意的保存点。
- 嵌套事务
由顶层事务和子事务构成，类似于树的结构。一般顶层事务负责逻辑管理，子事务负责具体的工作，子事务可以提交，但真正提交要等到父事务提交，如果上层事务回滚，那么所有的子事务都会回滚。
- 分布式事务
是指分布式环境中的扁平化事务。
存储引擎
- InnoDB 存储引擎
InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB 是默认的 MySQL引擎。
- MyISAM 存储引擎
MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。
- MEMORY 存储引擎
MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。
- NDB 存储引擎
DB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC，但它是 Share Nothing 的架构，因此能提供更高级别的高可用性和可扩展性。NDB 的特点是数据全部放在内存中，因此通过主键查找非常快。关于 NDB，有一个问题需要注意，它的连接(join)操作是在 MySQL 数据库层完成，不是在存储引擎层完成，这意味着，复杂的 join 操作需要巨大的网络开销，查询速度会很慢。
MyISAM和InnoDB的比较
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 事务支持 | MyISAM不支持事务；MyISAM是非事务安全型的 | InnoDB支持。InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性能。 |
| 存储结构 | MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 | 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | 可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表 | 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 | 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump |
| AUTO_INCREMENT | 可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增 | InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。 |
| 表锁差异 | 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据 | 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。 |
| 全文索引 | 支持(FULLTEXT类型的)全文索引 | 不支持(FULLTEXT类型的)全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。 |
| 表主键 | 允许没有任何索引和主键的表存在，索引都是保存行的地址。 | 如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。 |
| 表的具体行数 | 保存有表的总行数，如果select count(*) from table;会直接取出出该值。 | 没有保存表的总行数(只能遍历)，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。 |
| CURD操作 | 如果执行大量的SELECT，MyISAM是更好的选择。 | 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。 |
| 外键 | 不支持 | 支持 |
锁
- 表级锁：开销小，加锁快，不会出现死锁，锁粒度大（整张表），并发度低，发生锁竞争概率大
- 行级锁：开销大，加锁慢，会出现死锁，锁粒度最小（一行数据），并发度高，发生锁竞争概率小
- 页面锁：开销、加锁速度、锁粒度、并发度都介于表级锁和行级锁之间，会出现死锁。
对于查询操作远大于修改、插入操作的表而言，采用表级锁更加合适。行级锁更加适合有大量按索引条件查询并发更新少量不同数据，同时又有并发查询的应用。
索引
MySQL索引类型
- 唯一索引
就是索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的id必须保证唯一，如果重复插入相同的账户id时会MySQL返回异常。
- 主键索引
是一种特殊的唯一索引，但是它不允许出现空值。
- 普通索引
与唯一索引不同，它允许索引列中存在相同的值。例如学生的成绩表，各个学科的分数是允许重复的，就可以使用普通索引。
- 联合索引
就是由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引，联合索引是对多个列字段按顺序共同组成一个索引。应用联合索引时需要注意最左原则，就是Where查询条件中的字段必须与索引字段从左到右进行匹配。比如，一个用户信息表，用姓名和年龄组成了联合索引，如果查询条件是姓名等于张三，那么满足最左原则；如果查询条件是年龄大于20，由于索引中最左的字段是姓名不是年龄，所以不能使用这个索引。
- 全文索引
MyISAM引擎中实现了这个索引，在5.6版本后InnoDB引擎也支持了全文索引，并且在5.7.6版本后支持了中文索引。全文索引只能在CHAR,VARCHAR,TEXT类型字段上使用，底层使用倒排索引实现。要注意对于大数据量的表，生成全文索引会非常消耗时间也非常消耗磁盘空间。
索引实现
- B+树实现
b+树比较适合用作’>‘或’<'这样的范围查询，是MySQL中最常使用的一种索引实现。
- R-tree
是一种用于处理多维数据的数据结构，可以对地理数据进行空间索引。不过实际业务场景中使用的比较少。
- Hash
是使用散列表来对数据进行索引，Hash方式不像Btree那样需要多次查询才能定位到记录，因此Hash索引的效率高于B-tree，但是不支持范围查找和排序等功能.实际使用的也比较少。
- FullText
就是我们前面提到的全文索引，是一种记录关键字与对应文档关系的倒排索引。
MySQL调优
- 表结构和索引的优化
1、要在设计表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。
2、要为字段选择合适的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构
3、可以将字段多的表分解成多个表，必要时增加中间表进行关联
4、是设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在5表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度
5、要擅用索引，比如为经常作为查询条件的字段创建索引、创建联合索引时要根据最左原则考虑索引的复用能力，不要重复创建索引；要为保证数据不能重复的字段创建唯一索引等等。不过要注意索引对插入、更新等写操作是有代价的，不要滥用索引。比如像性别这样唯一很差的字段就不适合建立索引；
6、列字段尽量设置为Not Null，MySQL难以对使用Null的列进行查询优化，允许Null会使索引、索引统计和值更加复杂。允许Null值的列需要更多的存储空间，还需要MySQL内部进行特殊处理。
- SQL语句进行优化的原则
1、要找的最需要优化的SQL语句。要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询MySQL的慢查询日志来发现需要进行优化的SQL语句；
2、要学会利用MySQL提供的分析工具。例如使用Explain来分析语句的执行计划，看看是否使用了索引，使用了哪个索引，扫描了多少记录，是否使用文件排序等等。或者利用Profile命令来分析某个语句执行过程中各个分步的耗时；
3、要注意使用查询语句是要避免使用Select *，而是应该指定具体需要获取的字段。原因一是可以避免查询出不需要使用的字段，二是可以避免查询列字段的元信息；
4、是尽量使用Prepared Statements，一个是性能更好，另一个是可以防止SQL注入；
5、是尽量使用索引扫描来进行排序，也就是尽量在有索引的字段上进行排序操作。
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
