mysql的索引与锁（高性能mysql必备）

ゞ浴缸里的玫瑰 2024-04-08 08:52 47阅读 0赞

今天再从两个方面谈一下mysql的索引和锁，这两个共同点就是 没有共同点，hhh开玩笑。  
对于数据库，我们关心的问题就是如何让它多存，快找，不死机，那么从下面的方案中，是否可以找到令你心仪的一种配合呢？

### NO.1 MYSQL索引 ###

#### 1 对MySQL InnoDB引擎的索引了解吗？ ####

使用索引可以加快查询速度，其实上就是将无序的数据变成有序（有序就能加快检索速度）在InnoDB引擎中，索引的底层数据结构是B+树

[在我的这篇文章通篇谈了一下数据库的种类以及结构，其中第八个标题谈到了存储引擎，第十三个标题谈到了索引，读者可以自行去阅读一些][Link 1]

#### 2为什么不使用红黑树或者B树呢？ ####

MySQL的数据是存储在硬盘的，在查询时一般是不能「一次性」把全部数据加载到内存中  
红黑树是「二叉查找树」的变种，一个Node节点只能存储一个Key和一个Value  
B和B+树跟红黑树不一样，它们算是「多路搜索树」，相较于「二叉搜索树」而言，一个Node节点可以存储的信息会更多，「多路搜索树」的高度会比「二叉搜索树」更低。  
了解了区别之后，其实就很容易发现，在数据不能一次加载至内存的场景下，数据需要被检索出来，选择B或B+树的理由就很充分了（一个Node节点存储信息更多（相较于二叉搜索树），树的高度更低，树的高度影响检索的速度）  
B+树相对于B树而言，它又有两种特性。  
一、B+树非叶子节点不存储数据，在相同的数据量下，B+树更加矮壮。（这个应该不用多解释了，数据都存储在叶子节点上，非叶子节点的存储能存储更多的索引，所以整棵树就更加矮壮）  
二、B+树叶子节点之间组成一个链表，方便于遍历查询（遍历操作在MySQL中比较常见）  
![在这里插入图片描述][31b68799929d490eae5b15788ecaef3e.png]

我们在MySQL InnoDB引擎下，每创建一个索引，相当于生成了一颗B+树。  
如果该索引是「聚集(聚簇)索引」，那当前B+树的叶子节点存储着「主键和当前行的数据」  
如果该索引是「非聚簇索引」，那当前B+树的叶子节点存储着「主键和当前索引列值」

[关于聚簇索引和非聚簇索引，我放在这里了，讲的很清晰][Link 2]

比如写了一句sql：select \* from user where id >=10，那只要定位到id为10的记录，然后在叶子节点之间通过遍历链表(叶子节点组成的链表)，即可找到往后的记录了。  
由于B树是会在非叶子节点也存储数据，要遍历的时候可能就得跨层检索，相对麻烦些。  
基于树的层级以及业务使用场景的特性，所以MySQL选择了B+树作为索引的底层数据结构。

#### 3 你知道什么叫做回表吗？ ####

所谓的回表其实就是，当我们使用索引查询数据时，检索出来的数据可能包含其他列，但走的索引树叶子节点只能查到当前列值以及主键ID，所以需要根据主键ID再去查一遍数据，得到SQL 所需的列  
举个例子，我这边建了给订单号ID建了个索引，但我的SQL 是：select orderId,orderName from orderdetail where orderId = 123  
SQL都订单ID索引，但在订单ID的索引树的叶子节点只有orderId和Id，而我们还想检索出orderName，所以MySQL 会拿到ID再去查出orderName给我们返回，这种操作就叫回表  
![在这里插入图片描述][8bea3c11732c4189817d883c86417ce1.png]想要避免回表，也可以使用覆盖索引（能使用就使用，因为避免了回表操作）。  
所谓的覆盖索引，实际上就是你想要查出的列刚好在叶子节点上都存在，比如我建了orderId和orderName联合索引，刚好我需要查询也是orderId和orderName，这些数据都存在索引树的叶子节点上，就不需要回表操作了。

#### 4 既然你也提到了联合索引，我想问下你了解最左匹配原则吗？ ####

嗯，说明这个概念，还是举例子比较容易说明  
如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d  
先匹配最左边的，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找，这就是最左匹配原则

![在这里插入图片描述][e5cfbb50741944c08b32b617506394e2.png]

#### 5那假设我不用MySQL自增的主键，你觉得会有什么问题呢？ ####

首先主键得保证它的唯一性和空间尽可能短吧，这两块是需要考虑的。  
另外，由于索引的特性（有序），如果生成像uuid类似的主键，那插入的的性能是比自增的要差的  
因为生成的uuid，在插入时有可能需要移动磁盘块（比如，块内的空间在当前时刻已经存储满了，但新生成的uuid需要插入已满的块内，就需要移动块的数据）  
![在这里插入图片描述][426609ed9c9f4498ab53ba00b6b405fe.png]

### NO2 锁 ###

#### 一、 对MySQL的锁的了解 ####

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。  
就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

#### 二、 锁的四种隔离级别 ####

![在这里插入图片描述][38aa83b7429148419c4158531ec18859.png]4种事务隔离级别从上往下，级别越⾼，并发性越差，安全性就越来越⾼。 ⼀般数据默认级别是读以提交或可重复读  
[具体的场景可以看一下我的第 12条写的关于事务方面][Link 1]

#### 三、按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法 ####

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。  
**MyISAM和InnoDB存储引擎使用的锁：**  
MyISAM采用表级锁(table-level locking)。  
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。  
行级锁，表级锁和页级锁对比  
**行级锁**：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。  
**特点**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。  
**表级锁**：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。  
**特点**：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。  
**页级锁**：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。  
**特点**：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

#### 三、 从锁的类别上分MySQL都有哪些锁呢？像上面那样子进行锁定岂不是有点阻碍并发效率了 ####

从锁的类别上来讲，有共享锁和排他锁。  
**共享锁:** 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

**排他锁:** 又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

#### 数据库的乐观锁和悲观锁是什么？怎么实现的？ ####

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。  
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过Version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

#### **两种锁的使用场景** ####

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行Retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

本文参考：  
https://mp.weixin.qq.com/s?\_\_biz=MzAwNDA2OTM1Ng==&mid=2453159788&idx=1&sn=1f8608915c0cd5b9ae038866f2ce9bfb&chksm=8cfd1defbb8a94f9a8e7e31790572c837775576da53dd1e2b25efa96c1d7614f95f69372ae39&mpshare=1&scene=23&srcid=0811WAekBiQBmYAsONMWA2kZ&sharer\_sharetime=1660190193004&sharer\_shareid=5e1f720976235b1e81fd0d6731dbec3a\#rd

https://mp.weixin.qq.com/s?\_\_biz=MzAwNDA2OTM1Ng==&mid=2453159788&idx=1&sn=1f8608915c0cd5b9ae038866f2ce9bfb&chksm=8cfd1defbb8a94f9a8e7e31790572c837775576da53dd1e2b25efa96c1d7614f95f69372ae39&mpshare=1&scene=23&srcid=0811WAekBiQBmYAsONMWA2kZ&sharer\_sharetime=1660190193004&sharer\_shareid=5e1f720976235b1e81fd0d6731dbec3a\#rd

[Link 1]: https://blog.csdn.net/delete_bug/article/details/119342721?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166046978016782395399908%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=166046978016782395399908&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-119342721-null-null.nonecase&utm_term=B%2b&spm=1018.2226.3001.4450
[31b68799929d490eae5b15788ecaef3e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/112a240d7f8e4b1bb35b459581b5e26e.png
[Link 2]: https://blog.csdn.net/m0_51212267/article/details/124073029?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166047069716782391826778%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=166047069716782391826778&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-2-124073029-null-null.142%5Ev40%5Epc_rank_34,185%5Ev2%5Econtrol&utm_term=%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E5%92%8C%E9%9D%9E%E5%B1%80%E4%BF%83%E7%B4%A2%E5%BC%95&spm=1018.2226.3001.4187
[8bea3c11732c4189817d883c86417ce1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4e164ae57d7e4046a258889dbb95ac27.png
[e5cfbb50741944c08b32b617506394e2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f2205b9e86d94d0789eb92d02c3a6e13.png
[426609ed9c9f4498ab53ba00b6b405fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/319eecf597b84b4782897c306423e138.png
[38aa83b7429148419c4158531ec18859.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/9de416f1277f460e8fb118b655e0ec28.png