👆 这是第 336 篇不掺水的原创,想要了解更多,请戳下方卡片关注我们吧~
Mysql 的架构总体分为四层:
什么是高性能,每个人都有不同的回答,如:“每秒查询次数”、“ CPU 利用率”、“可扩展性”。《高性能Mysql》中给出的定义是“响应时间”,它认为 CPU利用率来作为高性能的指标是悖论,CPU 利用率低恰恰说明性能差对 CPU 利用率不高,真正有效的是“查询响应时间”。
mysql 的高性能主要体现在写和读上,在写的层面公司 DBA 已经进行了主从主备的高可用部署,同时对 redolog、binlog 等有很好的管理,基于我们目前对快速读的需求比较多,本文主要探讨怎么高性能读?
原理先行,实践为后,我们需要先明白 mysql 优化中常见的基本概念。
为了避免一条一条读取磁盘数据,InnoDB 采取页的方式,作为磁盘和内存之间交互的基本单位。一个页的大小一般是16KB。
页存储的过程:
(1)在一开始生成页的时候,没有 UserRecords 部分。
(2)当插入一条记录时,就会从 Free Space 中申请一个记录大小的空间,并将这个空间划分到 User Records 部分。
(3)当 Free Space 部分的空间全部都被 User Records 部分替代掉后,则这个页使用完了,如果再有新的记录插入,则需要去申请新的页了。
众所周知 Mysql 默认使用的 InnoDB 数据库,引擎使用的数据结构是 B+ 树,索引分类如果按照物理存储方式来看,分为聚簇索引和非聚簇索引。
聚簇索引:主键索引 ;非聚簇索引:非主键索引
上述我们讲到 Mysql 再查询时优先命中缓存,此处的缓存区叫做“ buffer pool (缓存池)”。
buffer pool 内部存储它是一个 hash 结构。优化器会通过这张表所对应的“表空间+页号”计算为 key,然后通过 value 对应的缓冲页的控制块
缓冲池(buffer pool)它是有固定大小的,虽然我们一页是数据是16KB。但是数据页多了,难免会把缓冲池(buffer pool)撑满,此时通过lru算法淘汰数据页。其实也可以理解为lru存储的也是“表空间+页号”数据。
策略:缓存区的数据更新是通过LRU算法更新,LRU算法的策略是:“最近最少使用”。
结构:在 InnoDB 实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。前5/8处是年轻代,后3/8是老年代。
算法:
根据上面的LRU算法,如果全表扫描,全表扫描的数据不会被写入缓存 young 区被命中。
扫描过程中,需要新插入的数据页,都被放到 old 区域 ; 一个数据页里面有多条记录,这个数据页会被多次访问到,但由于是顺序扫描,这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒,因此还是会被保留在 old 区域; 再继续扫描后续的数据,之前的这个数据页之后也不会再被访问到,于是始终没有机会移到链表头部(也就是 young 区域),很快就会被淘汰出去。
(1)哈希表,数组+链表的行式,这种结构适合用于等值查询的场景 (2)有序数组,单从查询效率上来说,查询快更新慢,所以有序数组只适合用于静态存储引擎 (3)搜索树,平衡二叉树查询和更新的时间复杂度都是 O(log(n)),InnoDB引擎使用的是 B+ 树;(4)数据库技术发展到今天,跳表、LSM 树等数据结构也被用于引擎设计中
1)“B+树”+“数据页”的视角了解索引的查询逻辑,才能更清晰了解查询过程。
(2)B+树的时间复杂度:O(logN),上述我们想查到6,只需要查询二次即可。
(3) 通过上述查找过程,我们看出,页数越少,层级约浅,越好查找。所以索引创建规则中:“索引越小越好(即索引要有辨识度)”
按照物理存储方式,索引可以分为:聚簇索引(主键索引)和非聚簇索引(二级索引)。
所以二级索引查询非索引字段时,需要回表。
覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。
优点:避免回表,覆盖索引特权很大,可以无视最左匹配原则
缺点:需要创建大量复合索引,建议单表索引数量低于 5 个
最左前缀可以是联合索引的最左N个字段也可以是字符串索引的最左N个字符。
此处需要注意的是,如"where name like ‘张 %’",一般来讲like是不走索引的,但是索引支持字符最左匹配,所以,匹配了字符走了索引
创建复合索引时,需要注意顺序,存在 (a,b) 则不需要额外建立 a 索引,节约空间
(name, age),select * from table where name like 'zhan%' and age = 10 and male = 1,这个用到了什么索引?是否比单索引更快一点?
根据最左匹配,虽然 age 索引失效了,但是会起到过滤作用,无需回表过滤查询age=10 的,直接从二级索引中取值了,这个过程叫做“索引下沉”。
你是否对多个字段想一起模糊查询,只要有一个字段匹配,即展示结果?
这个可以使用全文索引实现,全文索引是 FultLeXT 类型的索引,只能用于InnoDB 或 MyISAM 表,只能为 CHAR、VARCHAR、TEXT 列创建,MySQL5.7.6中支持了中文。
索引的最终选择是优化器的职责,优化器选择的依据是:扫描行数、临时表、排序
我们从 Mysql 查询链路的架构设计以及存储查询的物理逻辑分析,得到如上的逻辑图,从而在日常 Mysql 查询中,我们应当考虑创建合适的索引,从而提升mysql 查询的性能。
如果你觉得这篇内容对你挺有启发,我想邀请你帮我两件小事
1.点个「在看」,让更多人也能看到这篇内容(点了「在看」,bug -1 😊)
招贤纳士
政采云技术团队(Zero),Base 杭州,一个富有激情和技术匠心精神的成长型团队。规模 500 人左右,在日常业务开发之外,还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践,推动并落地了一系列的内部技术产品,持续探索技术的新边界。此外,团队还纷纷投身社区建设,目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。
如果你想改变一直被事折腾,希望开始折腾事;如果你想改变一直被告诫需要多些想法,却无从破局;如果你想改变你有能力去做成那个结果,却不需要你;如果你想改变你想做成的事需要一个团队去支撑,但没你带人的位置;如果你想改变本来悟性不错,但总是有那一层窗户纸的模糊……如果你相信相信的力量,相信平凡人能成就非凡事,相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程,亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程,我觉得我们该聊聊。任何时间,等着你写点什么,发给 zcy-tc@cai-inc.com