一、索引
1.1索引的基本知识
索引的优点： 1. 天生排序。2. 快速查找。
索引的缺点： 1. 占用空间。2. 降低更新表的速度。
注意点： 小表使用全表扫描更快，中大表才使用索引。超级大表索引基本无效。
索引从实现上说，分成 2 种：聚集索引和辅助索引（也叫二级索引、非聚集索引或非聚簇索引）
- MySql 将数据按照页来存储，默认一页为 16kb，当你在查询时，不会只加载某一条数据，而是将这个数据所在的页都加载到 pageCache 中
- 每个数据页可以组成一个双向链表
- 而每个数据页中的记录又可以组成一个单向链表
- 每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
- 以其他列(非主键) 作为搜索条件，只能从最小记录开始依次遍历单链表中的每条记录。
1.2聚集和非聚集索引
- InnoDB才有聚集索引，简单说来就是InnoDB以主键创建的索引叫做聚集索引，如果没有定义主键，Mysql会选择一个唯一索引来代替，如果也么有这样的索引，则会隐式定义一个6字节的row_id作为聚集索引。
- 非聚集索引就是以非主键创建的索引（MyISAM的索引都是非聚集索引）
区别：
- 聚集索引在叶子节点存储的是表中的数据
- 非聚集索引在叶子节点存储的是主键和索引列（MyISAM的叶子节点存储的是主键和对应数据的物理地址，即索引和数据是分开存储的）
- 使用非聚集索引查询出数据时，拿到叶子上的主键再去聚集索引上查到想要查找的数据。(拿到主键再查找这个过程叫做回表。MyISAM不存在回表，直接拿到数据的地址去数据文件中取数据)
1.3覆盖索引
- 我们前面知道了，如果不是聚集索引，叶子节点存储的是索引值+主键
- 最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
- 覆盖索引指一个查询语句的执行只用从索引中就能够取得，不做回表操作！
那什么情况下使用覆盖索引呢？比如说：
- 现在我创建了联合索引(k1,k2,k3)，在查询数据的时候：select k1, k2, k3 from table where k1 = 'v1' and k2 > 2
- 很明显地知道，我们上边的查询是走索引的，并且要查询出的列在叶子节点都存在，所以不用再回表查询了，这就是通过覆盖索引。
1.4联合索引最左匹配原则
- 索引可以简单如一个列(k1)，也可以复杂如多个列(k1, k2, k3, k4)，即联合索引。
- 如果是联合索引，那么索引key也由多个列组成，同时索引只能用于查找key是否存在 （相等）， 范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
- MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，因此列的排列顺序决定了可命中索引的列数。
如下列子：
- 有索引(k1, k2, k3)，查询条件 where k1 = 1 and k2 > 2 and k3 = 3，则会在每个节点依次命中k1、k2，无法命中k3。(原因很简单：索引命中只能是相等的情况，不能是范围匹配)
1.5索引下推
- Mysql在5.6引入了索引下推优化，默认开启，使用 SET optimizer_switch = ‘index_condition_pushdown=off’ ，可以将其关闭。
例子和解释如下：
- table表中有一个联合索引（k1, k2, k3）我们执行查询语句
SELECT * FROM table WHERE k1 = 'v1' AND k2 LIKE '%v2%' AND k4 LIKE '%v4%'
- 如果没有使用索引下推技术，则MySQL只会命中索引k1='v1'，然后再回表基于k2 LIKE '%v2%'和k4 LIKE '%v4%'来判断数据是否符合条件。
- 如果使用了索引下推技术，则MYSQL首先会返回符合k1='v1’的索引，然后根据k2 LIKE '%v2%'筛选出符合条件的索引（这里得到的索引数肯定比不使用索引下推技术的索引数少，这样就减少了回表次数），最后再回表基于k4 LIKE '%v4%'来判断符合条件的数据。
- 所以有了索引下推优化技术后，可以在有like条件或范围查询的情况下，减少回表次数。
1.6查询优化器
一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。
优化过程大致如下：
- 根据搜索条件，找出所有可能使用的索引
- 计算全表扫描的代价
- 计算使用不同索引执行查询的代价
- 对比各种执行方案的代价，找出成本最低的那一个
1.7索引总结
索引在数据库中是一个非常重要的知识点！上面谈的其实就是索引最基本的东西，要创建出好的索引要顾及到很多的方面：
- 最左前缀匹配原则。这是非常重要的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。
- 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
- 索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2020-04-10' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2020-04-10')。
- 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
- 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。
二、锁
2.1为什么需要学习数据库锁知识
不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)
定心丸：即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了
- 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
- MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预
只会在某些特定的场景下才需要手动加锁，学习数据库锁知识就是为了:
- 能让我们在特定的场景下派得上用场
- 更好把控自己写的程序
- 构建自己的知识库体系！在面试的时候不虚
2.2表锁介绍
首先，从锁的粒度可分为三大类：
- 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
- 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
- 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
- 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
- 最大程度的支持并发，同时也带来了最大的锁开销。
- 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
- 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。
- 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
不同的存储引擎支持的锁粒度是不一样的：
- InnoDB行锁和表锁都支持
- MyISAM和MEMORY只支持表锁
- BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁
- 也就是说，InnoDB的行锁是基于索引的
表锁又分为两种模式：
- 表读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
- 表写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。可以设置改变读锁和写锁的优先级：
- 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
- 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
有一点值得注意：
- MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式，在MyISAM中它默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。
- 但是InnoDB存储引擎是不支持的！
2.3行锁介绍
我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：
- InnoDB支持行锁
- InnoDB支持事务
我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的！
InnoDB实现了以下两种类型的行锁
- 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
- 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
- 也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁
- 意向锁也是数据库隐式帮我们做了，不需要程序员操心
2.4MVCC多版本控制
多版本并发控制 MVCC，是行级锁的一个变种，通过保存数据在某个时间节点的快照(snapshot)，类似实现了行级锁。由此不同事务对同一表，同一时刻看到的数据可能是不一样的。 实现上通过在不同的数据行后增加创建日期版本号和删除日期版本号，且版本号不断递增，进而实现了数据快照。
在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)
MVCC实现的读写不阻塞正如其名：多版本并发控制--->通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。
事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节
2.4.1一致性非锁定读（快照读）
- 在事务隔离级别提交读（RC）和可重复读（RR）下，InnoDB存储引擎使用非锁定的一致性读
- RC模式（Read committed）下，每次读取都是当前最新的快照
- RR模式（Repeatable read）下，每次读取都是当前事务开始时的快照，即使被修改了，也只会读取当前事务版本的数据
2.4.2一致性锁定读 （当前读）
- 隔离级别为未提交读(Read uncommitted）时读取都是当前读
- SELECT…FOR UPDATE （加写锁）
- SELECT…LOCK IN SHARE MODE （加读锁）
2.5乐观锁和悲观锁
- 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
- 悲观锁是数据库层面加锁，都会阻塞去等待锁
2.5.1悲观锁
我们使用悲观锁的话其实很简单(手动加行锁就行了)：
select * from xxxx for update
在select 语句后边加了 for update相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.
2.5.2乐观锁
乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现： 具体过程是这样的：
张三select * from table --->会查询出记录出来，同时会有一个version字段
| id | name | version |
|---|---|---|
| 1 | zhangsan | 1 |
李四select * from table --->会查询出记录出来，同时会有一个version字段
| id | name | version |
|---|---|---|
| 1 | zhangsan | 1 |
李四对这条记录做修改：update table set name=lisi, version=version+1 where id=#{id} and version=#{version}，判断之前查询到的version与现在的数据的version进行比较，同时会更新version字段
此时数据库记录如下：
| id | name | version |
|---|---|---|
| 1 | lisi | 2 |
张三也对这条记录修改：update a set name=wangwu,version=version+1 where id=#{id} and version=#{version}，但失败了！因为当前数据库中的版本跟查询出来的版本不一致！
2.6死锁
并发的问题就少不了死锁，在MySQL中同样会存在死锁的问题。
但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了，但死锁是无法完全避免的，可以通过以下的经验参考，来尽可能少遇到死锁：
- 以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
- 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
- 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
- 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大
参考资料：
