MySQL锁与索引

怼烎@ 2022-01-10 09:15 246阅读 0赞

一、关系型数据库

关系型数据库：**架构、索引、锁、语法、理论范式**。

二、如何设计一个数据库：

首先因为数据库是用来存储数据的，所以我们需要有一个存储模块（存储文件系统），我们将数据存储在磁盘硬盘上之后，需要用程序来对数据进行管理，所以此时需要有个程序实例来管理存储模块。程序实例当中需要有存储管理模块（以块或者页来表示）此外还需要使用缓存机制来优化我们的数据库提升效率。还需要SQL解析模块、日志管理模块。也需要权限划分模块。也需要容灾机制。接下来为了提升数据库的查询速度和处理并发我们需要索引管理和锁管理模块。

三、索引模块

**A**、为什么要使用索引。

如果我们使用最简单的方式来进行数据查找，也就是进行全表扫描，将数据全部或者分批次的加载到内存当中，然后逐个块或者页进行轮询，如果数据量少的话 还是可以的，但是数据量大的话 会很慢。所以需要使用索引提升查询速率。

B、什么样的信息能成为索引。

**主键、唯一键、普通键**。

**C**、索引的数据结构。

如建立**二叉查找树**进行二分查找，建立**B-Tree**结构进行查找，建立**B+-Tree**结构进行查找，建立**Hash**结构进行查找。

二叉查找树的时间复杂度是O(logn)最坏的情况下时间复杂度是O(n)但是影响程序运行的瓶颈是磁盘IO，**二叉树的节点最多只能有两个孩子，而作为索引的时候由于数据量大，所以树的深度会很深，检索速度也会慢很多**。所以需要**想办法将树变的矮一些，也就是让每个节点存储的数据多一些**。所以我们考虑到B-Tree（根节点至少包括两个孩子，树中每个节点最多含有m个孩子m>=2,除根节点和叶节点外，其他每个节点至少有ceil( m/2 )个孩子 --- ceil函数式取上限。所有叶子节点都位于同一层。即叶子节点的高度都是一样的。）只有遵守这些就可以使得每个节点可以存储更多的信息，让树的高度更矮，就会减少磁盘的IO次数。

BTree 的 每个节点存储着二元组(是一个 【key(是键值信息比如id)，data(该id对应这一行的数据)】)和指针(指向下一个节点的指针)。

B-Tree结构中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

【B-Tree中如果一个节点的data数据量过大那么会影响一个页中key的数量，key的数量少了 ，要查询到信息。自然要进行更多次的IO，所以效率还是会受到影响】

B+Tree 的每个**非**节点只包含包含key【键值信息】和指针；叶子节点包含data数据【所有的数据都放在叶子节点】以及链指针【用来在所有的叶子节点中进行遍历】。

[详情参考该文章][Link 1]

**B+-Tree是B树的变体 ，其定义基本与B树相同**，除了：

**非叶子节点的子树指针与关键字个数相同**（B树是比关键字少一个），非叶子节点的子树指针P\[i\],指向关键字值\[K\[i\],K\[i+1\]\]的子树。非叶子节点仅用来索引，数据都保存在叶子节点中，**所有叶子节点均有一个链指针指向下一个叶子节点**（也就是说一旦定位到一个叶子节点的数据，就可以横向的跨子树去检索数据）。综上所述**B+-Tree 更适合用来做存储索引**。

**大致分析如下**：

B+-Tree的磁盘读写代价更低（因为子节点能存储的关键字更多了）

**B+-Tree的查询效率更加稳定**（因为非叶子节点都是用来做索引的数据都在叶子节点中，所以要查找数据都要走一条根节点到叶子节点的路）是稳定的**O(logn)**

**B+-Tree更有利于对数据库的扫描**（B树在提高了磁盘IO性能时并没有解决元素遍历的效率底下的问题，而B+-Tree只需要遍历叶子节点就可以解决对全部关键字的扫描）

有些数据库还支持Hash索引Hash索引的查询效率理论上高于B+-Tree ，但是他有他的缺点：

基本能满足 =、in不能使用范围查询。

无法被用来避免数据的排序操作（因为Hash索引存放的是经过Hash运算的值并不能保证和Hash运算之前的一模一样）

不能利用部分索引查询(因为hash索引是将组合索引一起进行hash运算索引不能部分索引查询。)

不能避免表扫描（因为不同索引建存在相同hash值所以即使取出某个满足 Hash键值的那些数据也无法直接从hash索引中完成查询还是要通过访问bucket中的实际数据进行相应的比较，所以不能避免表扫描）

遇到大量Hash值相等的情况后性能并不一定就会比B树索引高（就是因为bucket有可能变成线性存储结构。）

**D**、密集索引和稀疏索引的区别。

**密集索引文件中的每个搜索码值都对应一个索引值**（叶子节点保存的不仅仅是键值还有同一行其他列的信息，由于密集索引决定了表的物理排列顺序所以一个表只能有一个密集索引）

稀疏索引文件中只为索引码的某些值建立索引项（叶子节点只保存了键位信息以及该行数据的地址 ）

MySQL中有两种存储引擎：MyISAM和Innodb

**MyIsAm不管是主键索引、唯一键索引、普通索引都是稀疏索引**。

**InnoDB有且仅有一个密集索引**：

若一个主键被定义，该主键则作为密集索引。

若没有主键定义，该表的第一个唯一非空索引则作为密集索引。

若不满足以上条件，innoDB内部会生成一个隐藏主键（密集索引）

非主键索引存储相关键位和其对应的主键值，包含两次查找。

**索引模块衍生出来的问题**：

**1、如何定位并优化慢SQL**

根据慢日志定位慢查询SQL，使用Explain等工具分析SQL，修改SQL或者尽量让SQL走索引。

首先使用 show variables like '%query% ';可以查询相关的慢查询的东西

show status like ' %slow\_queries%'；可以查询慢查询得数量。

show\_query\_log 、show\_query\_log\_file、long\_query\_time ,

使用explain 进行分析：id表示sql的执行顺序  需要注意的是type和Extra

type表示找到数据行的方式从最优到最差-system-const-eq\_ref-ref-fulltext-ref\_or\_null-index-merge-unique-subquery-index\_subquery-range-index-all index  和all 表示语句走的是全表扫描时需要优化的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70][]

**2、联合索引的最左匹配的原则的成因**

大致意思是：有两个列a b组成的联合索引   where  a=？ and b=？ 和where a=？ 多可以走索引，但是where b= ？ 就不会走索引。

因为B+树是按照从左到右的顺序来建立搜索树的如果没有a ，由于a是第一个比较因子，没有的话无法确定下一步查找方向。

mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配，=和in是可以乱序的因为mysql的查询优化器是可以帮助我们优化的。

建立联合所有（a，b，c）只要含有a 都是可以走联合索引的，还有就是使用like关键字 ”a%“是走索引的 像”%“、”&b“ 这种的都是不走联合索引的。

**3、索引是建立的越多越好吗？**

数据量小的表不需要建立索引，建立索引会增加额外的索引开销。

数据变更需要维护索引，因此更多的索引意味着更多的维护成本，

更多的索引也意味着更多的存储空间。

四、锁模块

1、MyISAM与InnoDB关于锁方面的区别是什么？

MyISAM默认用的是表级锁，不支持行级锁，InnoDB默认用的是行级锁，也支持表级锁，

MyISAM在select时会给表加上一个表级别的读锁，在增删改时会给表加上一个表级别的写锁，当读锁未被释放的时候，其他的session想对同一张表加上写锁会被阻塞，直到所有的读锁都被释放。

使用lock  tables 表名 read 显示的给表加上读锁。 使用unlock tables进行读锁的释放。

上了读锁之后可以在上读锁，不可以上排它锁，也就是写锁。

先上写锁 在上读锁或者写锁 是不可能的（因为写锁是排他锁）我们可以使用for update 上排它锁 其实就是悲观锁。

读锁是共享锁（即在进行范围查询的时候，我们依然能对表里的数据进行读操作），写锁是排他锁。

InnoDB 默认是行锁（select做了优化是不加锁的所以测试需要显示加读锁。）

我们使用lock in share  mode

用表级锁的时候只要操作表里的元素就会给表上锁所以表级锁和索引无关，除主键索引以外的 其他键只要sql 用到了索引涉及到的行都会加上共享锁或者排他锁。如果没有用到索引

**MyISAM 适合的场景**：

a、频繁执行全表count语句。b、对数据进行增删改的频率不高，查询非常频繁。c、适合没有事务的场景。

**InnoDB适合的场景**：

a、数据增删改查都相当频繁  b、可靠性要求比较高，要求支持事务。

**数据库锁分类**：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70 1][]

2、数据库事务的四大特性。

原子性、一致性、隔离性、持久性。

3、事务隔离级别以及各级别下的并发访问问题。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70 2][]

InnoDB可重复读隔离级别下如何避免幻读。

表象：快照读（非阻塞读）--伪MVCC

内在：next-key锁（行锁+gap锁）

![20190620171832960.png][]

当前读 ：就是读取记录的当前最新版本并且读取后还要保证其他并发事务不能修改当前记录

快照读：不加锁的非阻塞读，select

4、InnoDB可重复读隔离级别下如何避免幻读。

快照读是通过MVVC(多版本控制)和undo log来实现的,快照读的数据取决于select执行的时机。

5、RC、RR级别下的InnoDB的非阻塞读如何实现。

Gap锁会用在非唯一索引或者不走索引的当前读中。

非唯一索引锁 6-9 9-11  不走索引  全锁。

[MVCC 详解的好文章][MVCC]

[Link 1]: http://blog.csdn.net/u013235478/article/details/50625677
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70]: /images/20220110/81077a6b86f144d092ad3c8d165282e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70 1]: /images/20220110/ebde52d4ca4d4267ba2a003e36c708ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lhbmd6YWlMZUhlSGU_size_16_color_FFFFFF_t_70 2]: /images/20220110/5e46455d7087422190dd50d70fc64c85.png
[20190620171832960.png]: /images/20220110/4dd0393fa9be46b8b511d91273de7c5e.png
[MVCC]: https://segmentfault.com/a/1190000012650596