由浅入深了解MySQL底层查询逻辑

我不是女神ヾ 2023-10-12 14:57 34阅读 0赞

## 1. MySQL架构逻辑设计 ##

### 1.1 MySQL的逻辑架构图 ###

![format_png][]

Mysql的架构总体分为四层：

*  **客户端**：各种语言都提供了连接mysql数据库的方法，比如**jdbc**、php、go等，可根据选择 的后端开发语言选择相应的方法或框架连接mysql
 *  **server层**：包括**连接器、查询缓存、分析器、优化器、执行器**等，涵盖mysql的大多数核心服务功能，以及所有的内置函数（例如日期、世家、数 学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
 *  **存储引擎层**：负责数据的存储和提取，是真正与底层物理文件打交道的组件。 数据本质是存储在磁盘上的，通过特定的存储引擎对数据进行有组织的存放并根据业务需要对数据进行提取。存储引擎的架构模式是插件式的，支持**Innodb，MyIASM、Memory**等多个存储引擎。现在最常用的存储引擎是Innodb，它从mysql5.5.5版本开始成为了默认存储引擎。
 *  **物理文件层**：存储数据库真正的表数据、日志等。物理文件包括：**redolog、undolog、binlog**、errorlog、querylog、slowlog、data、index等。
    
     *  **binlog**由**server**层产生，无论什么搜索引擎都有binlog；**redolog**只有**innodb**存储引擎产生。

### 1.2 什么是MySQL高性能? ###

什么是高性能，每个人都有不同的回答，如：“每秒查询次数”、“CPU利用率”、“可扩展性”。《高性能Mysql》中给出的定义是“响应时间”，它认为CPU利用率来作为高性能的指标是悖论，CPU利用率低恰恰说明性能差对cpu利用率不高，真正有效的是“**查询响应时间**”。

mysql的**高性能**主要体现在**写和读**上，在写的层面公司DBA已经进行了主从主备的高可用部署，同时对redolog、binlog等有很好的管理，基于我们目前对快速读的需求比较多，本文主要探讨怎么**高性能读？**

### 1.3 基本概念 ###

原理先行，实践为后，我们需要先明白mysql优化中常见的基本概念。

(1) 数据页

![format_png 1][]

为了避免一条一条读取磁盘数据，InnoDB采取**页**的方式，作为**磁盘**和**内存**之间**交互**的**基本单位**。一个页的大小一般是**16KB**。

页存储的过程：

（1）在一开始生成页的时候，**没有UserRecords部分**。

（2）当插入一条记录时，就会**从Free Space中申请**一个记录大小的空间，并将这个空间**划分到User Records部分**。

（3）当Free Space部分的空间全部都被User Records部分替代掉后，则这个页使用完了，如果再有新的记录插入，则需要去**申请新的页**了。

(2) 回表

众所周知Mysql默认使用的**InnoDB**数据库，引擎使用的数据结构是**B+树**，索引分类如果按照物理存储方式来看，分为**聚簇索引**和**非聚簇索引**。

聚簇索引：主键索引 ； 非聚簇索引：非主键索引

![format_png 2][]

### 1.4 缓存命中及LRU算法 ###

1.4.1 buffer pool缓冲池

上述我们讲到Mysql再查询时优先命中缓存，此处的缓存区叫做“**buffer pool(缓存池)** ”。

*  **buffer pool** 是数据库的一个内存组件，里面**缓存了磁盘上的真实数据**，**Java**系统对数据库的**增删改操作**，主要是这个内存数据结构中的**缓存数据执行**的。
 *  缓存和磁盘是对应的，都是使用页存储，缓存区叫**缓存页，也是16KB**
 *  缓存区的数据是从**磁盘中查询**，然后通过**LRU算法**存入的缓存区中的
 *  Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：**内存命中率**

buffer pool内部存储它是一个hash结构。优化器会通过这张表所对应的“**表空间+页号**”计算为key，然后通过value对应的缓冲页的控制块

缓冲池（buffer pool）它是有固定大小的，虽然我们一页是数据是16KB。但是数据页多了，难免会把缓冲池（buffer pool）撑满，此时通过**lru算法淘汰数据页**。其实也可以理解为lru存储的也是“**表空间+页号**”数据。

1.4.2 LRU算法

![format_png 3][]

**策略**：缓存区的数据更新是通过LRU算法更新，**LRU算法**的策略是：“**最近最少使用**”。

**结构**：在 InnoDB 实现上，按照 **5:3** 的比例把整个 LRU 链表分成了 young 区域和 old 区域。**前5/8处是年轻代，后3/8是老年代**。

**算法**：

1.  新读取的数据会被存入**old区域**，下一个新数据进来会在**old区前插**，原数据以此后移；
2.  如果数据在old区域存在时间**超过1s**,就会**前移**到**首位**，进入**young**区域，其他数据**依次超过1s前移**；
3.  对**young区**数据访问，**被访问数据会前移到首位**

根据上面的LRU算法，如果全表扫描，**全表扫描的数据不会被写入缓存young区被命中**。

> 1.  扫描过程中，需要新插入的数据页，都被放到 old 区域 ;
> 2.  一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；
> 3.  再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。

## 2. 索引优化 ##

### 2.1 索引结构 ###

2.1.1 常见的索引结构模型

（1）**哈希表**，数组+链表的行式，这种结构适合用于**等值查询**的场景 （2）**有序数组**，单从查询效率上来说，**查询快更新慢**，所以有序数组只适合用于**静态存储引擎** （3）**搜索树**，平衡二叉树查询和更新的时间复杂度都是O(log(n))，InnoDB引擎使用的是**B+树**； （4）数据库技术发展到今天，**跳表、LSM 树**等数据结构也被用于引擎设计中

2.1.2 B+ tree

![format_png 4][]

1）“B+树”+“数据页”的视角了解索引的查询逻辑，才能更清晰了解查询过程。

*  只有**叶子节点**（最底层的节点）才**存放了数据**，**非叶子节点**（其他上层节）仅用来**存放目录页**作为索引。
 *  非叶子节点分为不同层次，通过**分层**来**降低**每一层的**搜索量**；
 *  **所有节点**按照索引键大小排序，构成一个**双向链表**，便于**范围查询**；

（2）**B+树的时间复杂度**：**O(logN)** ,上述我们想查到6，只需要查询二次即可。

(3) 通过上述查找过程，我们看出，页数越少，层级约浅，越好查找。所以索引创建规则中：“**索引越小越好（即索引要有辨识度）** ”

### 2.2 索引优化 ###

2.2.1 索引分类

按照**物理存储方式**，索引可以分为：**聚簇索引(主键索引)** 和**非聚簇索引(二级索引)** 。

*  **聚簇索引**的叶子节点存放的是**实际数据**，所有完整的用户记录都存放在聚簇索引的叶子节点；
 *  **二级索引**的叶子节点存放的是**主键值**，而不是实际数据。

所以**二级索引查询非索引字段时，需要回表。**

2.2.2 各索引优缺点

*  **覆盖索引**

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说**查询列要被所建的索引覆盖。**

**优点**：**避免回表**，**覆盖索引特权很大，可以无视最左匹配原则**

**缺点**：需要创建大量复合索引，建议单表索引数量低于5个

*  **最左前缀原则**

最左前缀可以是联合索引的**最左N个字段**也可以是字符串索引的**最左N个字符。**

> 此处需要注意的是，如"where name like ‘张 %’"，一般来讲like是不走索引的，但是索引支持字符最左匹配，所以，匹配了字符走了索引

创建复合索引时，需要注意顺序，存在(a,b)则不需要额外建立a索引，节约空间

*  **索引下沉（MYSQL5.6）**

> (name, age)，select \* from table where name like 'zhan%' and age = 10 and male = 1，这个用到了什么索引？是否比单索引更快一点？

根据最左匹配，虽然age 引失效了，但是会起到**过滤作用**，无需回表过滤查询age=10的，直接从二级索引中取值了，这个过程叫做\*\*“索引下沉”。

*  **全文索引（MYSQL5.7.6）**

> 你是否对多个字段想一起模糊查询，只要有一个字段匹配，即展示结果？

这个可以使用全文索引实现，**全文索引**是**FultLeXT**类型的索引，只能用于**InnoDB或MyISAM**表,只能为**CHAR**、**VARCHAR**、**TEXT**列创建，**MySQL5.7.6中支持了中文。**

**索引的最终选择是优化器的职责**，优化器选择的依据是：**扫描行数、临时表、排序**

## 3. 总结 ##

![format_png 5][]

我们从Mysql查询链路的架构设计以及存储查询的物理逻辑分析，得到如上的逻辑图，从而在日常mysql查询中，我们应当考虑创建合适的索引，从而提升mysql查询的性能。

[format_png]: https://img-blog.csdnimg.cn/img_convert/2a47fbdf47e647ce0edea08abd04e87e.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/9781a1b9be57c672b3f50734a76045b9.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/8114ef6b98efdd9f0e68021abcc138c5.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/c5d8f16de17ce4feb5a94eba5dc1ba04.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/15277615d8d6067c3ef33266e323d595.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/a2a0ae8cce9956fe4d206dde81919e0b.webp?x-oss-process=image/format,png