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

云潮

这是第336篇不掺水的，想要了解更多，请戳下方卡片关注我们吧～1. MySQL 架构逻辑设计1.1 MySQL 的逻辑架构图Mysql 的架构总体分为四层：客户端：各种语言都提供了连接mysql数据库的方法，比如jdbc、php、go等，可根据选择 的后端开发语言选择相应的方法或框架连接 mysqlserver 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖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是老年代。算法：新读取的数据会被存入old 区域，下一个新数据进来会在old 区前插，原数据以此后移；如果数据在 old 区域存在时间超过1s,就会前移到首位，进入young区域，其他数据依次超过1s前移；对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+ tree1）“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 查询的性能。看完两件事如果你觉得这篇内容对你挺有启发，我想邀请你帮我两件小事1.点个「在看」，让更多人也能看到这篇内容（点了「在看」，bug -1 ）2.关注公众号「政采云技术」，持续为你推送精选好文招贤纳士政采云技术团队（Zero），Base 杭州，一个富有激情和技术匠心精神的成长型团队。规模 500 人左右，在日常业务开发之外，还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践，推动并落地了一系列的内部技术产品，持续探索技术的新边界。此外，团队还纷纷投身社区建设，目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。如果你想改变一直被事折腾，希望开始折腾事；如果你想改变一直被告诫需要多些想法，却无从破局；如果你想改变你有能力去做成那个结果，却不需要你；如果你想改变你想做成的事需要一个团队去支撑，但没你带人的位置；如果你想改变本来悟性不错，但总是有那一层窗户纸的模糊……如果你相信相信的力量，相信平凡人能成就非凡事，相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程，亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程，我觉得我们该聊聊。任何时间，等着你写点什么，发给 zcy-tc@cai-inc.com