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

1. MySQL架构逻辑设计

1.1 MySQL的逻辑架构图

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) 数据页

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

页存储的过程：

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

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

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

(2) 回表

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

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

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算法

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

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

算法：

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

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

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

2. 索引优化

2.1 索引结构

2.1.1 常见的索引结构模型

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

2.1.2 B+ tree

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. 总结

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