SQL之考点总结

1. SQL事务

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

2. 事务属性
   事务4大属性：

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

3. 创建事务
   T-SQL中管理事务的语句：

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

4. 事务分类:

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

5. 示例：张三转800元到李四帐户上。

    use stuDBgo#--创建帐户表bank--if exists(select* from sysobjects where name='bank')    drop table bankcreate table bank(    customerName char(10),    #--顾客姓名    currentMoney money        #--当前余额)go#添加约束，帐户不能少于元alter table bank add        constraint CK_currentMoney check(currentMoney>=1)#插入测试数据insert into bank(customerName,currentMoney)select '张三',1000 unionselect '李四',1select * from bankgo#使用事务use stuDBgo#恢复原来的数据#update bank set currentMoney=currentMoney-1000 where customerName='李'set nocount on    #不显示受影响的行数print '查看转帐事务前的余额'select * from bankgo#开始事务begin transactiondeclare @errorSum int    #--定义变量，用于累计事务执行过程中的错误#转帐update bank set currentMoney=currentMoney-800 where customerName='张三'set @errorSum=@errorSum+@@error    #累计是否有错误update bank set currentMoney=currentMoney+800 where customerName='李四'set @errorSum=@errorSum+@@error    #累计是否有错误print '查看转帐事务过程中的余额'select * from bank#根据是否有错误，确定事务是提交还是回滚if @errorSum>0    begin        print '交易失败，回滚事务.'        rollback transaction    endelse    begin        print '交易成功，提交事务，写入硬盘，永久保存！'        commit transaction    endgoprint '查看转帐后的余额'select * from bankgo

   ---

2. SQL事务中的隔离级别

1. 在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些是在事务内和事务间可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

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

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

   ---

3. MYSQL的锁机制

1. 概述
   
   1. MySQL有三种锁的级别：页级、表级、行级。
   2. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）
2. 锁的特点
   表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
   行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
   页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
3. 应用场景
   不同的锁粒度决定了不同引擎的应用场景，我们最常用的
   
   1. 表级锁的引擎是MyISAM和InnoDB。
   2. 行级引擎是InnoDB。
   3. 页级锁的引擎常用的是Berkeley DB。
      

      ---

1. MyISAM表

MyISAM存储引擎只支持表级锁，锁的模式有共享锁和排他锁。共享锁是他人可以读但不能写，排它锁则会阻塞他人的读写操作。MyISAM的读写之间，以及写写之间是串行的。
MyISAM在执行SQL语句时，会自动为SELECT语句加上共享锁，为UDI操作加上排它锁。MYSQL不支持锁升级，如果涉及到更新操作，需要在一开始就加上排它锁。

MyISAM的并发插入
在存储引擎中有一个系统变量concurrent_insert，专门控制其并发插入的行为
concurrent_insert=0时，不允许并发插入
concurrent_insert=1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），其允许在一个进程读表的同事，另一个进程从表尾插入记录，这也是MySQL的默认设置
concurrent_insert=2时，如果MyISAM表中没有空洞，允许在表尾并发插入记录

MyISAM的锁调度

在MyISAM存储引擎中，写的重要性要大于读，所以在操作队列中，即使写的操作在读的操作之后，也会让写先拿到排它锁，这也正是MyISAM不适合于大量写入操作的应用场景的原因，这样可能会导致读操作永远在阻塞中，永远在等待写操作的释放锁。当然，除了默认的设置，可以通过设置语句的优先级别来管理这个执行顺序

2. InnoDB

他与MyISAM的最大区别有两个方面，一个是支持事务，另一个是采用了行级锁
为了解决以上问题，产生了四个隔离级别：未提交读，提交读，可重复读（InnoDB事务默认使用），串行读，见上文。

锁模式

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

语句示例：
共享锁（S）：SELECT FROM table_name WHERE … LOCK IN SHARE MODE。
排他锁（X)：SELECT FROM table_name WHERE … FOR UPDATE。

行锁的实现方式：

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。如果不同的索引碰巧都落到了同一个行上，那么同样会阻塞。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的，这就要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的。
INSERT…SELECT…和 CREATE TABLE…SELECT…语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。

3. 什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。
第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
如果以上两种事务过多，那我们就可以考虑使用MyISAM引擎了

4. 死锁

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

尽量使用较低的隔离级别；
精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
选择合理的事务大小，小事务发生锁冲突的几率也更小；
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

4. 索引

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。
在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

create table mytable(    id  int not null,     username varchar(16) not null );

我们随机向里面插入了10000条记录，其中有一条：5555, admin。
在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
MySQL索引类型包括：
1. 普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
◆创建索引
sql
CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。
◆修改表结构
sql
ALTER mytable ADD INDEX [indexName] ON (username(length)) ;

◆创建表的时候直接指定
sql
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引的语法：
sql
DROP INDEX [indexName] ON mytable;

2. 唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
◆创建索引
sql
CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构
sql
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

◆创建表的时候直接指定
sql
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

3. 主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(    ID INT NOT NULL,     username VARCHAR(16) NOT NULL,    PRIMARY KEY(ID)   ); ```当然也可以用 ALTER 命令。**记住：一个表只能有一个主键。**4. 组合索引为了形象地对比单列索引和组合索引，为表添加多个字段：``` sqlCREATE TABLE mytable(     ID INT NOT NULL,       username VARCHAR(16) NOT NULL,      city VARCHAR(50) NOT NULL,     age INT NOT NULL ); ```为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age   usernname,city  usernname为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：<div class="se-preview-section-delimiter"></div>``` sqlSELECT FROM mytable WHREE username="admin" AND city="郑州"SELECT FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT FROM mytable WHREE age=20 AND city="郑州"SELECT FROM mytable WHREE city="郑州"

1. 建立索引的时机
   到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m  ON t.Name=m.username WHEREm.age=20 AND m.city='郑州';

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT FROM mytable WHERE username like 'admin%'

而下句就不会使用：

SELECT FROM mytable WHEREt Name like '%admin'

因此，在使用LIKE时应注意以上的区别。
6. 索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：
◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
7. 使用索引的注意事项**
使用索引时，有以下一些技巧和注意事项：
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
select from users where YEAR (adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>操作

索引原理与慢查优化技巧传送门