sql语句执行剖析

概要

本篇文章主要讲解sql语句在执行过程中，数据库为我们做了哪些操作。涉及数据库引擎，隔离机制，锁，索引等等这些基础概念要明白。列入：select * from user u where u.age>10 and u.age <10 and u.school = ‘BJ’ 在执行这条sql中数据库做的所有操作。（这里主要以mysql为基础）

数据引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

1. InnoDB存储引擎
   InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。InnoDB是默认的MySQL引擎。InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
   *（A）*InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合.
   *（B）*InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
   *（C）*InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
   *（D）*InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
   *（E）*InnoDB被用在众多需要高性能的大型数据库站点上
2. MyISAM存储引擎
   MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
   *（A）*大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
   *（B）*当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成。每个字符列可以有不同的字符集。有VARCHAR的表可以固定或动态记录长度。VARCHAR和CHAR列可以多达64KB
   *（C）*每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
   *（D）*最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
   （E） BLOB和TEXT列可以被索引，NULL被允许在索引的列中，可以把数据文件和索引文件放在不同目录，这个值占每个键的0~1个字节。所有数字键值以高字节优先被存储以允许一个更高的索引压缩
   （F） 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
3. MEMORY存储引擎
   MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。
   （A） MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。MEMORY存储引擎执行HASH和BTREE缩影
   （B） 可以在一个MEMORY表中有非唯一键值。MEMORY表使用一个固定的记录长度格式。MEMORY不支持BLOB或TEXT列。MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
   （C） MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
   （D） MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
   （E） 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
   
4. 引擎选择
   1，如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
   2，如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
   3，如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
   4，如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
   5，使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
5. 详情可以看此链接https://www.jianshu.com/p/4bb9f78b4f6d
6. 设置存储引擎
   1，通过修改MySQL配置文件实现
   -default-storage-engine=engine
   2，通过创建数据表的命令实现
   CREATE TABLE tp1(s1 VARCHAR(10))ENGINE=MyISAM;
   3，通过修改数据表的命令实现
   ALTER TABLE tp1 ENGINE=InnoDB;
   SHOW CREATE TABLE tp1;

隔离机制

说隔离机制前，需要了解数据库事务的4个特性

1. 原子性(Atomic): 事务中的多个操作，不可分割，要么都成功，要么都失败
2. 一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；
3. 隔离性(Isolation): 多个事务之间就像是串行执行一样，不相互影响;
   其中隔离性分为了四种：也就是我们说的隔离机制。
   （1），READ UNCOMMITTED：可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；
   （2），READ COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读（RC级别）；
   （3），REPEATABLE READ：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；（RR级别）
   （4），SERIALIZABLE: 幻读，不可重复读和脏读都不允许，所以serializable要求解决幻读；
4. 持久性(Durability): 事务提交后被持久化到永久存储.

其中牵扯到的几个概念：

1. 脏读）：可以读取未提交的数据。RC 要求解决脏读；
2. 不可重复读）：同一个事务中多次执行同一个select, 读取到的数据发生了改 变(被其它事务update并且提交)；
3. 可重复读）：同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准；
4. 幻读）：同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题；

其中要区分 不可重复读和幻读
不可重复读的重点是修改:同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了
幻读的重点在于新增或者删除:同样的条件的select, 第1次和第2次读出来的记录数不一样
从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：
对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读；
对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 gap lock。
而ANSI SQL标准没有从隔离程度进行定义，而是定义了事务的隔离级别，同时定义了不同事务隔离级别解决的三大并发问题：

• 默认隔离机制
  除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。
  但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。
  MySQL的实现：MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢？其实MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。
• MySQL 中RC和RR隔离级别的区别
  MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？我们该如何选择呢？为什么MySQL将RR作为默认的隔离级别呢？
  （1）显然 RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；
  （2）RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select … from s where 语句在s表上的锁也是不一样的。
  详情

锁

mysql的几种锁

1. X锁(排他锁) : 与其他X锁和S锁互斥
2. S锁(共享锁): 与X锁互斥 当一个事物获得S锁 别的事物可以继续获得S锁 但是不能加X锁 X锁与X锁和S锁互斥
3. IX(意向排他锁) IX是表级的　mysql引擎自动控制 在获得X锁之前 会先获得IX锁 IX只会与表级的S,X锁互斥. 当mysql对表级进行加锁(X或者S)的时候不用一行一行对数据判断是否加了X锁 直接根据是否有IX锁来进行判断，提高了效率
4. IS(意向共享锁) IS是锁是表级的 mysql引擎自动控制 在获得S锁之前会先获得IS锁 IS锁只会与表级X锁互斥 当mysql锁对表级进行加X锁是 不用一行一行对数据判断是否加了S锁 直接判断是否存在表级的IS意向锁
5. gap(锁) 间隙锁 用于在指定索引位置区间加锁 (只会在插入是互斥)
   
   这个时候gap锁就有[无穷小,10]，[10,20]，[20,30],[30,40][40,无穷大]
   如果在RR模式下delete table where age=20 将不能插入10~20之间 20~30到之间的值gap锁 只会在insert的时候互斥 (可以理解为gap在非Insert获取的都是共享锁 在Insert时获取的是排他锁)
6. next-key : 行锁和gap锁的组合

索引

• 索引方法

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
（1）FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
（2） HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
（3）BTREE
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
（4）RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找

• 索引类型
  普通索引：仅加速查询
  唯一索引：加速查询 + 列值唯一（可以有null）
  主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
  组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  全文索引：对文本的内容进行分词，进行搜索

索引合并，使用多个单列索引组合搜索。覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

说明：
聚簇索引：在innodb下 数据的存储顺序跟索引的存储存储顺序是一样 聚簇索引的页节点就指向数据，每个表都会有一个聚簇索引 默认在主键上 如果没有找到将会在表中的唯一非空的列上加上聚簇索引 如果没有mysql将自动维护一个隐式的列作为聚簇索引
二级索引(非聚簇索引)：二级索引的 页节点 存储的是聚簇索引 当查询一条非聚簇索引的列 会先根据索引找到聚簇索引 再根据聚簇索引查找数据

还是上面表格：
RC模式：
delete table where id=1

如果id列是主键
将会在id为1的的聚簇索引上加上X锁

如果id列为唯一索引
将会在唯一索引上加上X锁 同时根据唯一索引找到聚簇索引 并加锁 为什么在唯一索引上面加了X锁还要在聚簇索引上加锁 因为如果这个时候另外一个事物根据聚簇索引更新数据 将感知不到锁
比如上面表 age为聚簇索引 delete table where id=1; id=1的索引将加上锁 这个但是聚簇索引age没有加上 update table id=2 where age=20 这个时候 age=20 获取锁成功能将能修改成功
上面的解释但是会有疑惑 既然加锁都加载聚簇索引上为什么还要多此一举的在非聚簇索引列上加锁(个人理解 判断锁互斥时 直接根据条件的索引 而不用再次根据索引找到聚簇索引)

如果id列非唯一索引
跟唯一索引加锁机制一样

如果id列无索引
将在走聚簇索引 全表扫描不管是否满足条件的数据都会加上X锁 但是RC模式有优化 不满足条件的加锁之后又会释放

RR模式:
如果id列是主键
将会在满足条件的聚簇索引上加上X锁

如果id列为唯一索引
在id列上加上X锁同时在 对应的聚簇索引加上X锁

如果id列非唯一索引
将在id列上加上next-key锁 同时在聚簇索引加上x锁

如果id列无索引
将在走聚簇索引 全表扫描不管是否满足条件的数据都会加上next-key锁 跟RC模式不同的是将不会释放
详情

• 使用索引的注意事项
  （1）索引不会包含有NULL值的列
  只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  （2）使用短索引
  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  （3）索引列排序
  MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  （4）like语句操作
  一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
  （5）不要在列上进行运算
  复制代码 代码如下:
  select * from users where YEAR(adddate)<2007;
  将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
  复制代码 代码如下:
  select * from users where adddate<‘2007-01-01’;
  （6）不使用NOT IN和<>操作

详情可以看https://www.cnblogs.com/caicz/p/11009507.html

执行加锁分析

上面的基本知识看完后，下面才算文章的主题，sql的执行加锁分析

MVCC多版本并发控制：一般有两种实现方式，本文所讲的InnoDB采用的是后者:
快照读：读取历史版本，从undo log中读取行记录的快照；这样读行就不需要等待锁资源，提高了并发；
当前读：读取最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
加锁读、插入、更新、删除等操作均属于当前读