mysql索引和锁

InnoDB的索引结构
在InnoDB中是通过⼀种多路搜索树——B+树实现索引结构的。在B+树中是只有叶⼦结点会存储数据，⽽且所有叶⼦结点会形成
⼀个链表。⽽在InnoDB中维护的是⼀个双向链表。
你可能会有⼀个疑问，为什么使⽤ B+树 ⽽不使⽤⼆叉树或者B树？
⾸先，我们知道访问磁盘需要访问到指定块中，⽽访问指定块是需要 盘⽚旋转 和 磁臂移动 的，这是⼀个⽐较耗时的过程，如
果增加树⾼那么就意味着你需要进⾏更多次的磁盘访问，所以会采⽤n叉树。
⽽使⽤B+树是因为如果使⽤B树在进⾏⼀个范围查找的时候每次都会进⾏重新检索，⽽在B+树中可以充分利⽤叶⼦结点的链表。
在建表的时候你可能会添加多个索引，⽽ InnDB 会为每个索引建⽴⼀个 B+树 进⾏存储索引。
⽐如这个时候我们建⽴了⼀个简单的测试表
create table test(
id int primary key,
a int not null,
name varchar,
index(a)
)engine = InnoDB;
这个时候 InnDB 就会为我们建⽴两个 B+索引树
⼀个是 主键 的 聚簇索引，另⼀个是 普通索引 的 辅助索引，这⾥我直接贴上 MySQL浅谈（索引、锁） 这篇⽂章上⾯的贴图(因为
我懒不想画图了。。。)
可以看到在辅助索引上⾯的叶⼦节点的值只是存了主键的值，⽽在主键的聚簇索引上的叶⼦节点才是存上了整条记录的值。
回表
所以这⾥就会引申出⼀个概念叫回表，⽐如这个时候我们进⾏⼀个查询操作
select name from test where a = 30;
我们知道因为条件 MySQL 是会⾛ a 的索引的，但是 a 索引上并没有存储 name 的值，此时我们就需要拿到相应 a 上的主键值，
然后通过这个主键值去⾛ 聚簇索引 最终拿到其中的name值，这个过程就叫回表。
我们来总结⼀下回表是什么？MySQL在辅助索引上找到对应的主键值并通过主键值在聚簇索引上查找所要的数据就叫回表。
索引维护
我们知道索引是需要占⽤空间的，索引虽能提升我们的查询速度但是也是不能滥⽤。
⽐如我们在⽤⼾表⾥⽤⾝份证号做主键，那么每个⼆级索引的叶⼦节点占⽤约20个字节，⽽如果⽤整型做主键，则只要4个字
节，如果是⻓整型（bigint）则是8个字节。也就是说如果我⽤整型后⾯维护了4个g的索引列表，那么⽤⾝份证将会是20个g。
所以我们可以通过缩减索引的⼤⼩来减少索引所占空间。
当然B+树为了维护索引的有序性会在删除，插⼊的时候进⾏⼀些必要的维护(在InnoDB中删除会将节点标记为“可复⽤”以减少
对结构的变动)。
⽐如在增加⼀个节点的时候可能会遇到数据⻚满了的情况，这个时候就需要做⻚的分裂，这是⼀个⽐较耗时的⼯作，⽽且⻚的分
裂还会导致数据⻚的利⽤率变低，⽐如原来存放三个数据的数据⻚再次添加⼀个数据的时候需要做⻚分裂，这个时候就会将现有
的四个数据分配到两个数据⻚中，这样就减少了数据⻚利⽤率。
覆盖索引
上⾯提到了 回表，⽽有时候我们查辅助索引的时候就已经满⾜了我们需要查的数据，这个时候 InnoDB 就会进⾏⼀个叫 覆盖索引
的操作来提升效率，减少回表。
⽐如这个时候我们进⾏⼀个 select 操作
select id from test where a = 1;
这个时候很明显我们⾛了 a 的索引直接能获取到 id 的值，这个时候就不需要进⾏回表，我们这个时候就使⽤了 覆盖索引。
简单来说 覆盖索引 就是当我们⾛辅助索引的时候能获取到我们所需要的数据的时候不需要再次进⾏回表操作的操作。
联合索引
这个时候我们新建⼀个学⽣表
CREATE TABLE `stu` (
`id` int(11) NOT NULL,
`class` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_name` (`class`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
我们使⽤ class(班级号) 和 name 做⼀个 联合索引，你可能会问这个联合索引有什么⽤呢？我们可以结合着上⾯的 覆盖索引 去理
解，⽐如这个时候我们有⼀个需求，我们需要通过班级号去找对应的学⽣姓名 。
select name from stu where class = 102;
这个时候我们就可以直接在 辅助索引 上查找到学⽣姓名⽽不需要再次回表。
总的来说，设计好索引，充分利⽤覆盖索引能很⼤提升检索速度。
最左前缀原则
这个是以 联合索引 作为基础的，是⼀种联合索引的匹配规则。
这个时候，我们将上⾯的需求稍微变动⼀下，这时我们有个学⽣迟到，但是他在⻔卫记录信息的时候只写了⾃⼰的名字张三⽽没
有写班级，所以我们需要通过学⽣姓名去查找相应的班级号。
select class from stu where name = '张三';
这个时候我们就不会⾛我们的联合索引了，⽽是进⾏了全表扫描。
为什么？因为 最左匹配原则。我们可以画⼀张简单的图来理解⼀下。
我们可以看到整个索引设计就是这么设计的，所以我们需要查找的时候也需要遵循着这个规则，如果我们直接使⽤name，那么
InnoDB是不知道我们需要⼲什么的。
当然最左匹配原则还有这些规则
全值匹配的时候优化器会改变顺序，也就是说你全值匹配时的顺序和原先的联合索引顺序不⼀致没有关系，优化器会帮你调
好。
索引匹配从最左边的地⽅开始，如果没有则会进⾏全表扫描，⽐如你设计了⼀个(a,b,c)的联合索引，然后你可以使⽤(a),
(a,b),(a,b,c) ⽽你使⽤ (b),(b,c),(c)就⽤不到索引了。
遇到范围匹配会取消索引。⽐如这个时候你进⾏⼀个这样的 select 操作
select * from stu where class > 100 and name = '张三';
这个时候 InnoDB 就会放弃索引⽽进⾏全表扫描，因为这个时候 InnoDB 会不知道怎么进⾏遍历索引，所以进⾏全表扫描。
索引下推
我给你挖了个坑。刚刚的操作在 MySQL5.6 版本以前是需要进⾏回表的，但是5.6之后的版本做了⼀个叫 索引下推 的优化。
select * from stu where class > 100 and name = '张三';
如何优化的呢？因为刚刚的最左匹配原则我们放弃了索引，后⾯我们紧接着会通过回表进⾏判断 name，这个时候我们所要做的
操作应该是这样的
但是有了索引下推之后就变成这样了，此时 "李四" 和 "⼩明" 这两个不会再进⾏回表。
因为这⾥匹配了后⾯的name = 张三，也就是说，如果最左匹配原则因为范围查询终⽌了，InnoDB还是会索引下推来优化性能。
⼀些最佳实践
哪些情况需要创建索引？
频繁作为查询条件的字段应创建索引。
多表关联查询的时候，关联字段应该创建索引。
查询中的排序字段，应该创建索引。
统计或者分组字段需要创建索引。
哪些情况不需要创建索引
表记录少。
经常增删改查的表。
频繁更新的字段。
where 条件使⽤不⾼的字段。
字段很⼤的时候。
其他
尽量选择区分度⾼的列作为索引。
不要对索引进⾏⼀些函数操作，还应注意隐式的类型转换和字符编码转换。
尽可能的扩展索引，不要新建⽴索引。⽐如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引
即可。
多考虑覆盖索引，索引下推，最左匹配。
锁
全局锁
MySQL提供了⼀个加全局读锁的⽅法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，
可以使⽤这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结
构等）和更新类事务的提交语句。
⼀般会在进⾏ 全库逻辑备份 的时候使⽤，这样就能确保 其他线程不能对该数据库做更新操作。
在 MVCC 中提供了获取 ⼀致性视图 的操作使得备份变得⾮常简单，如果想了解 MVCC 可以参考
https://juejin.im/post/5da8493ae51d4524b25add55
表锁
MDL(Meta Data Lock)元数据锁
MDL锁⽤来保证只有⼀个线程能对该表进⾏表结构更改。
怎么说呢？MDL分为 MDL写锁 和 MDL读锁，加锁规则是这样的
当线程对⼀个表进⾏ CRUD 操作的时候会加 MDL读锁
当线程对⼀个表进⾏ 表结构更改 操作的时候会加 MDL写锁
写锁和读锁，写锁和写锁互斥，读锁之间不互斥
lock tables xxx read/write;
这是给⼀个表设置读锁和写锁的命令，如果在某个线程A中执⾏lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写
t2的语句都会被阻塞。同时，线程A在执⾏unlock tables之前，也只能执⾏读t1、读写t2的操作。连写t1都不允许，⾃然也不能
访问其他表。
这种表锁是⼀种处理并发的⽅式，但是在InnoDB中常⽤的是⾏锁。
⾏锁
我们知道在5.5版本以前 MySQL 的默认存储引擎是 MyISAM，⽽ MyISAM 和 InnoDB 最⼤的区别就是两个
事务
⾏锁
其中⾏锁是我们今天的主题，如果不了解事务可以去补习⼀下。
其实⾏锁就是两个锁，你可以理解为 写锁(排他锁 X锁)和读锁(共享锁 S锁)
共享锁（S锁）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。也叫做读锁：读锁是共享的，多个客⼾
可以同时读取同⼀个资源，但不允许其他客⼾修改。
排他锁（X锁)：允许获得排他锁的事务更新数据，阻⽌其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁：写锁
是排他的，写锁会阻塞其他的写锁和读锁。
⽽⾏锁还会引起⼀个⼀个很头疼的问题，那就是死锁。
如果事务A对⾏100加了写锁，事务B对⾏101加了写锁，此时事务A想要修改⾏101⽽事务B⼜想修改⾏100，这样占有且等待就导
致了死锁问题，⽽⾯对死锁问题就只有检测和预防了。
next-key锁
MVCC 和⾏锁是⽆法解决 幻读 问题的，这个时候 InnoDB 使⽤了 ⼀个叫 GAP锁(间隙锁) 的东西，它配合 ⾏锁 形成了 next-key
锁，解决了幻读的问题。
但是因为它的加锁规则，⼜导致了扩⼤了⼀些加锁范围从⽽减少数据库并发能⼒。具体的加锁规则如下：
加锁的基本单位是next-key lock 就是⾏锁和GAP锁结合。
查找过程中访问到的对象就会加锁。
索引上的等值查询，给唯⼀索引加锁的时候，next-key lock退化为⾏锁。
索引上的等值查询，向右遍历时且最后⼀个值不满⾜等值条件的时候，next-key lock退化为间隙锁。
唯⼀索引上的范围查询会访问到不满⾜条件的第⼀个值为⽌。
MVCC 解决幻读的思路⽐较复杂，这⾥就不做过多的验证。
总结
对于 MySQL 的索引来说，我给了很多最佳实践，其实这些最佳实践都是从原理来的，⽽ InnoDB 其实就是⼀个改进版的 B+树，
还有存储索引的结构。弄懂了这些你就会得⼼应⼿起来。
⽽对于 MySQL 的锁，主要就是在⾏锁⽅⾯，InnoDB 其实就是使⽤了 ⾏锁，MVCC还有next-key锁来实现事务并发控制的。
⽽对于MySQL中最重要的其实就是 锁和索引 了，因为内容太多这篇⽂章仅仅做⼀些介绍和简单的分析，如果想深⼊了解可以查
看相应的⽂章