mysql执行原理 索引 锁简介
mysql走你~~
select * form table where id=？
一条mysql查询都会经历些什么呢？
image
- 客户端向MySQL服务器发送一条查询请求
- 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
- 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
- MySQL根据执行计划，调用存储引擎的API来执行查询
- 将结果返回给客户端，同时缓存查询结果
索引规则
- 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER
- BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
- 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
- 字符字段只建前缀索引
索引
索引（Index）是帮助MySQL高效获取数据的数据结构。本质上，索引是一种数据结构
先来聊聊b-tree(b树，不是b减树) b+tree
B-Tree
- 一个3阶的b-tree
image
为了定义b-tree 首先定义一个二元组[key,data],key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：
- d阶的b-tree
- 每个非叶子节点有(n-1)个key 和n个指针,其中 d<=n<=2d
- 叶节点的指针均为null，每个叶子节点最少含有一个key和两个指针，最多有2d-1个key和2d个指针。
- 所有的叶节点都在同一层，切深度就是数的高度。
查找
BTree_Search(node, key) {
if(node == null) return null;
foreach(node.key)
{
if(node.key[i] == key) return node.data[i];
if(node.key[i] > key) return BTree_Search(point[i]->node);
}
return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);
- 首先从根节点进行二分查找，找到则返回，否则对相应区间节点进行遍历查找。
- 对于一个度为d的t-tree，设有N个key,则，树高h的上限是logd((N+1)/2),索一个key，其查找结点个数的渐进复杂度为O(logdN).为啥？
B+Tree
带有顺序访问指针的B+Tree
image
- 与b-tree相比 不同点有
- 内节点不存储数据，只存储key，叶子节点不存储指针。
- 每个节点的指针上线不是2d 是2d+1
- 带顺序指针的好处，是为了提高区间访问的能力，例如找到15 就可以顺着指针一次性访问所有指针了。
局部性原理与磁盘预读
当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中
- 由于磁盘顺序读取效率很高（不需要寻道时间，只需要很少的旋转时间），因为对于局部性的程序来说，预读可以提高i/o效率。
- 页是计算机管理存储器的逻辑块（页的大小通常是4k）。主存和磁盘以页为单位交换数据。
- 预读的长度一般为页的整数倍。系统向磁盘发出读取信号，会找到数据其实位置，读取一页或多页到内存。
B-/+Tree索引的性能分析
- 将每个节点的大小设成一页，这样每个节点只需要一次I/O操作就可完全载入。为了达到这个目的，有如下技巧
- 每次新建节点时，直接申请一个页的空间。
- B-tree一次检索最多需要h-1次I/O（根节点常驻内存）
- 渐进复杂度是O(h)=O(logdN),实际应用中，d很大，通常超过100，所以h很小
- 所以！用B-Tree作为索引结构效率是非常高的
Mysql 索引实现
mysql有MyISAM和InnoDB两个存储引擎，分别讨论实现方式。
MyISAM索引实现
image
- MyISAM的索引文件仅仅保存数据记录的地址
- 主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复
- MyISAM中索引检索的算法：
首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
- MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现
- InnoDB的数据文件本身就是索引文件（这是和myisam重大区别），叶结点data域保存了完整的数据记录，这种索引叫做==聚集索引==
image
- 第二个不同是，InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
image
- 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：
-
首先检索辅助索引获得主键
-
然后用主键到主索引中检索获得记录。
-
所以InnoDB要求表必须有主键（MyISAM可以没有）
-
小优化
- 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
- 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
索引使用策略及优化
MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。下面讨论的是结构优化。
建索引的几大原则
- 最左前缀原理
- 理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，所以效果一样。
MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引。单列索引可以看成联合索引元素数为1的特例。
- 尽量选择区分度高的列作为索引，区分度公式count(distinct col)/count(*) 表示字段不重复比例
- 索引列不能参与计算
- 尽量扩展索引，而不是新建索引
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 未提交读 Read uncommitted | 可能 | 可能 | 可能 |
| 已提交读 Read commited | 不 | 可能 | 可能 |
| 可重复读（默认）Rrepeatable read | 不 | 不 | 可能 |
| 可串行化 Serializable | 不 | 不 | 不 |
- 未提交读：可以读取到其他会话中未提交事务修改的数据
- 提交读 ：只能读取已经提交的数据
- 可重复读：同一事务内的查询都是和事务开始那一刻保持一致的
- 脏读
- 一个事务正在修改数据，但是还没提交到数据库， 此时另一个事务访问并使用了这个数据。
- 不可重复读
- 一个事务两次访问数据期间，另一个事务修改了这个数据。导致两次读到的数据不一样。
- 幻读
- 第一个事务对表中所有的数据进行修改，另一个事务插入一条，那么第一个事务是感知不到这个变化的，好像发生幻觉一样。
myisam innodb
为什么选择innodb
- 支持事物的隔离级别
- 支持行级锁及外建约束
- 主键采用聚集索引
- 等
锁
参考文章
MyISAM 有表锁
Innodb行级锁，
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
- 使用数据版本（version）记录机制实现，是乐观锁最常用的实现方式
悲观锁：假设会发生并发冲突，屏蔽一切可能违反数据完成性的操作。
- 加锁 例如select *** For update可以添加行锁
Innodb锁类型包括
- 共享锁与独占锁
- 意向锁
- 记录锁 record
- 间隙锁 gap
- Next-Key Locks
- 插入意向锁
- 自增锁
- 空间索引断言锁
共享锁与独占锁 意向锁
实现了两种方式的行锁：
- 共享锁 S锁
- 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
- 也就是一个事务在读取数据行的时候，其他事务也可以读，但是不能修改。
- 排他锁 X
- 允许获取排它锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁
- 也就是一个事务在读取一个数据行的时候，其他事务连查都不行
设置共享锁 SELECT ** LOCK IN SHARE MODE;
设置排他锁 Select ** for update;
- 意向共享锁 IS
- 通知数据库接下来需要施加什么锁，并对表加锁
- 如果需要对A记录加锁，那么innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁
- 意向排他锁
- 同上
共享锁和意向共享锁 区别
- 共享锁和排他锁，系统会在特定条件下添加，也可以手动添加
- 意向都是系统自动添加和释放的
- 共享锁和排他锁，是锁的行记录
- 意向锁是锁表
Innodb 行锁实现方式
是通过索引上的索引项来实现的。这就意味着：
- 只有通过索引条件检索数据，Innodb才会使用行级锁，
- 否则，Innodb将会使用表锁。
这个地方要注意，不然的话会导致大量的锁冲突！！
行锁（ 记录锁 间隙锁 next key）
只有在可重复读及以上 才有gap和next key
record lock 记录锁
- 单个行记录上的锁，针对==索引记录==
- 记录锁总是锁定索引记录，即使表没有索引：
- innodb会创建隐式的索引，并使用这个索引实施记录锁
gap lock 间隙锁
-
在索引记录之间的间隙加锁，或者在某一条索引记录之前或之后加锁，并不包括索引记录本身。
-
一般针对非唯一索引而言
-
左开右闭区间
| id | v1 |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 4 |
| 5 | 5 |
| 7 | 7 |
| 10 | 9 |
表中v1字段值可以划分的区间为
（-∞,1）
（1,3）
（3,4）
（4,5）
（5,7）
（7,9）
（9, +∞）
加入要更新v1=7的数据行，也就是对v1加上间隙锁，区间是（5,7）（7,9）
session 1
select * from tem where v1=7 for update
//会在（5，5）（7，7）之间加间隙锁
//会在（7，7）（10，9）之间加间隙锁
session2
insert tmp
value(4,5) 成功
value(8,8) 阻塞
value(6,6) 阻塞
value(11,9) 成功
value(12,7) 会成功吗？
间隙锁2 条件范围的
- 当我们使用条件范围的检索数据，并请求共享或排他锁时，Innodb会给符合条件的已有数据的索引项加锁。
- 对于键值在条件范围内但是不存在的记录，叫做间隙（GAP）,innodb也会对这个间隙加锁。
- 例如 emp表有数据 1,2,***101,102
- select * from emp where empid>100
- 这个sql不仅会对101,102加锁，会对大于100的数字都加锁！！即使这些数字暂时不存在
目的：
- 一方面是为了防止幻读
- 例如另一个事务插入103，那么本事务如果再次执行上面sql，就会发生幻读！
- 另一方面，是为了满足恢复和复制的需要
问题：
- 会造成严重的锁等待
- 其他事务只能等待。所以，应尽量避免使用范围条件。
next key lock
- 就是record 和gap的结合，即锁住了记录本身，还有索引之间的间隙
什么时候使用表锁
绝大多数情况下，都应该使用行级锁。但是特殊情况，比如
- 事务需要更新大部分或者全部数据，表又比较大
- 如果使用行锁，事务执行效率低，而且造成其他事务长时间等待和锁冲突
- 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
- 避免死锁，减少数据库回滚带来的开销
关于死锁
死锁情况分析
常见sql加锁情况分析
死锁常见情况分析
定义：
- 多个进程在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力，他们会一直等待。
关键：
- 两个或多个session加锁顺序不一致。
发生死锁后，innodb一般可以自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。
但是并不能完全检测到死锁。可以通过设置锁等待超时时间来解决。
- 防止死锁拖垮数据库，而不是解决死锁问题。
死锁情况分析
- 用户访问A表之后访问B 另一用户，访问B之后访问A 互相等待释放锁，导致死锁
- 在一个不走索引的更新上，会导致全表加锁，多个的话，会导致死锁。
案例1
| 事务1 | 事务 2 |
|---|---|
| select * from user where id=1 for update | - |
| - | select * from user where id=2 for update |
| update user set name='' where id=2 由于被事务2加锁 只能阻塞 | |
| - | update user set name='' where id=1 由于被事务1加锁 只能阻塞 此时Innodb检测出死锁，事务回滚 |
| 事务2回滚 update继续执行 | |
| commit |
案例二
现在表中没有id=8 和id=9的数据
| 事务1 | 事务 2 |
|---|---|
| update user set name="" where id=8 会在>8的范围加间隙锁 | |
| .- | update user set name="" where id=9 会在>9的范围加间隙锁 间隙锁之间不冲突 |
| insert into user(id) value(8) 这个时候需要等待事务2释放间隙锁 | |
| .- | insert into user(id) value(9) 这个时候需要等待事务1释放间隙锁 |
解决办法 就是去掉上面的update语句
案例三
| 事务 1 | 事务 2 |
|---|---|
| update user set name="" where id<20 | update user set name="" where id>15 |
加锁顺序不一样，会导致死锁。
最后
任何数据库层面的优化都抵不上应用系统的优化。查询容易，优化不易，且写且珍惜
