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的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

• 使用索引的注意事项
  （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中读取行记录的快照；这样读行就不需要等待锁资源，提高了并发；
当前读：读取最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
加锁读、插入、更新、删除等操作均属于当前读