【数据库】SQL之重点知识点总结

SQL之重点知识点总结

事务概念

事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，
这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。
在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。
这特别适用于多用户同时操作的数据通信系统。
eg:订票、银行、保险公司以及证券交易系统等。

事务属性

事务4大属性(ACID特性)：

1. 原子性(Atomicity):事务是一个完整的操作。
2. 一致性（Consistency）：当事务完成时，数据必须处于一致状态。
3. 隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
4. 持久性(Durability):事务完成后，它对于系统的影响是永久性的。

创建事务

T-SQL中管理事务的语句：

1. 开始事务: begin transaction
2. 提交事务：commit transaction
3. 回滚事务: rollback transaction

事务分类:

1. 显式事务:用begin transaction明确指定事务的开始。
2. 隐性事务：打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。
3. 自动提交事务：SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。

SQL事务中的隔离级别

1. 未提交读（Read uncommitted）：在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的。
   事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，
   从性能上来说，未提交读不会比其他的级别好太多，但是缺乏其他级别的很多好处，在实际应用中一般很少使用。
2. 提交读（Read committed）：大多数数据库系统的默认隔离级别都是提交读（但Mysql不是）。
   提交读满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。
   换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
   这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
3. 可重复读（Repeatable read）：可重复读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。
   但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom read）问题。
   所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务中又在该范围插入了新的记录，
   当之前的事务再次读取该范围的记录时，会产生幻行（Phantom row）。可重复读是MySQL的默认事务隔离级别。
4. 可串行化（Serializable）：可串行化是最高的隔离级别。它通过强制事务串行执行，避免了前面所说的幻读问题。
   简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。
   实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。

事务的并发处理会带来几个问题

1.不可重复读，A事务在第一次读和第二次读之间，如果B对数据进行的修改，则两次读取的数据会不一致
2.更新丢失，A和B同时操作一个数据，最后执行完毕的会覆盖前一个的执行结果
3.脏读，A事务添加了数据但并未提交，B读到了这条数据后A回滚了，就会导致脏读（很形象）
4.幻读，A事务第二次读取数据之前，B数据提交了满足条件的数据，这种现象就叫幻读

MySQL的锁机制

• 概述 概述

1. MySQL有三种锁的级别：页级、表级、行级。
2. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking

• 锁的特点
  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

• 应用场景

1. 表级锁的引擎是MyISAM和InnoDB。
2. 行级引擎是InnoDB。
3. 页级锁的引擎常用的是Berkeley DB

MySQL中myisam与innodb的区别，至少5点

1. InnoDB支持事物，而MyISAM不支持事物
2. InnoDB支持行级锁，而MyISAM支持表级锁
3. InnoDB支持MVCC, 而MyISAM不支持
4. InnoDB支持外键，而MyISAM不支持
5. InnoDB不支持全文索引，而MyISAM支持。

innodb引擎的四大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

myisam与innodb2者select count(*)哪个更快，为什么

myisam更快，因为myisam内部维护了一个计数器，可以直接调取。