MySQL性能优化浅析及线上案例

mysql,性能,优化,浅析,案例 · 浏览次数 : 468

小编点评

## 浅析数据库性能优化 **引言** 数据库性能优化是一个复杂的大课题,需要付出代价和成本才能获得成功。如何在获得成功的同时节约成本也是一件难事。 **索引** 索引是数据库性能优化中最重要的方法。当索引被优化时,数据库可以从索引中快速找到数据行,从而提高效率。 **主键** 主键是数据库中最重要的数据列。当主键被优化时,数据库可以从索引中快速找到数据行,从而提高效率。 **二级索引** 二级索引是主键的二级索引。当二级索引被优化时,数据库可以从索引中快速找到数据行,从而提高效率。 **三级索引** 三级索引是二级索引的二级索引。当三级索引被优化时,数据库可以从索引中快速找到数据行,从而提高效率。 **索引策略** 索引策略是索引优化的策略。当索引策略被优化时,数据库可以从索引中快速找到数据行,从而提高效率。 **数据库性能优化方法** * 加索引 * 优化主键 * 加二级索引 * 加三级索引 *优化索引策略 **一些性能优化技巧** * 使用索引 * 使用主键 * 使用二级索引 * 使用三级索引 *优化索引策略 **一些性能优化工具** * MySQL索引工具 * MyIsIndex * InnoDB Index Optimizer **一些感悟** * 数据库性能优化是一个复杂的大课题,需要付出代价和成本才能获得成功。 * 在获得成功的同时节约成本也是一件难事。 * 只有真的在一线切身体会到当时的紧张和压力,才能对一件事情才能印象深刻。 * 反之也不能太过于强调代价,如果可以通过一些别人的分享就可以规避一些自己业务的问题和错误的代价也是好的。 * 归纳总结以上内容,生成内容时需要带简单的排版

正文

作者:京东健康 孟飞

1、 数据库性能优化的意义

业务发展初期,数据库中量一般都不高,也不太容易出一些性能问题或者出的问题也不大,但是当数据库的量级达到一定规模之后,如果缺失有效的预警、监控、处理等手段则会对用户的使用体验造成影响,严重的则会直接导致订单、金额直接受损,因而就需要时刻关注数据库的性能问题。

2、 性能优化的几个常见措施

数据库性能优化的常见手段有很多,比如添加索引、分库分表、优化连接池等,具体如下:

序号 类型 措施 说明
1 物理级别 提升硬件性能 将数据库安装到更高配置的服务器上会有立竿见影的效果,例如提高CPU配置、增加内存容量、采用固态硬盘等手段,在经费允许的范围可以尝试。
2 应用级别 连接池参数优化 我们大部分的应用都是使用连接池来托管数据库的连接,但是大部分都是默认的配置,因而配置好超时时长、连接池容量等参数就显得尤为重要。 1、 如果链接长时间被占用,新的请求无法获取到新的连接,就会影响到业务。 2、 如果连接数设置的过小,那么即使硬件资源没问题,也无法发挥其功效。之前公司做过一些压测,但就是死活不达标,最后发现是由于连接数太小。
3 单表级别 合理运用索引 如果数据量较大,但是又没有合适的索引,就会拖垮整个性能,但是索引是把双刃剑,并不是说索引越多越好,而是要根据业务的需要进行适当的添加和使用。 缺失索引、重复索引、冗余索引、失控索引这几类情况其实都是对系统很大的危害。
4 库表级别 分库分表 当数据量较大的时候,只使用索引就意义不大了,需要做好分库分表的操作,合理的利用好分区键,例如按照用户ID、订单ID、日期等维度进行分区,可以减少扫描范围。
5 监控级别 加强运维 针对线上的一些系统还需要进一步的加强监控,比如订阅一些慢SQL日志,找到比较糟糕的一些SQL,也可以利用业务内一些通用的工具,例如druid组件等。

3、 MySQL底层架构

首先了解一下数据的底层架构,也有助于我们做更好优化。

一次查询请求的执行过程

我们重点关注第二部分和第三部分,第二部分其实就是Server层,这层主要就是负责查询优化,制定出一些执行计划,然后调用存储引擎给我们提供的各种底层基础API,最终将数据返回给客户端。

4、MySQL索引构建过程

目前比较常用的是InnoDB存储引擎,本文讨论也是基于InnoDB引擎。我们一直说的加索引,那到底什么是索引、索引又是如何形成的呢、索引又如何应用呢?这个话题其实很大也很小,说大是因为他底层确实很复杂,说小是因为在大部分场景下程序员只需要添加索引就好,不太需要了解太底层原理,但是如果了解不透彻就会引发线上问题,因而本文平衡了大家的理解成本和知识深度,有一定底层原理介绍,但是又不会太过深入导致难以理解。

首先来做个实验:

创建一个表,目前是只有一个主键索引

CREATE TABLE t1(

a int NOT NULL,

b int DEFAULT NULL,

c int DEFAULT NULL,

d int DEFAULT NULL,

e varchar(20) DEFAULT NULL,

PRIMARYKEY(a)

)ENGINE=InnoDB

插入一些数据:

insert into test.t1 values(4,3,1,1,'d');

insert into test.t1 values(1,1,1,1,'a');

insert into test.t1 values(8,8,8,8,'h');

insert into test.t1 values(2,2,2,2,'b');

insert into test.t1 values(5,2,3,5,'e');

insert into test.t1 values(3,3,2,2,'c');

insert into test.t1 values(7,4,5,5,'g');

insert into test.t1 values(6,6,4,4,'f');

MYSQL从磁盘读取数据到内存是按照一页读取的,一页默认是16K,而一页的格式大概如下。

每一页都包括了这么几个内容,首先是页头、其次是页目录、还有用户数据区域。

1)刚才插入的几条数据就是放到这个用户数据区域的,这个是按照主键依次递增的单向链表。

2)页目录这个是用来指向具体的用户数据区域,因为当用户数据区域的数据变多的时候也就会形成分组,而页目录就会指向不同的分组,利用二分查找可以快速的定位数据。

当数据量变多的时候,那么这一页就装不下这么多数据,就要分裂页,而每页之间都会双向链接,最终形成一个双向链表。

页内的单向链表是为了查找快捷,而页间的双向链表是为了在做范围查询的时候提效,下图为示意图,其中其二页和第三页是复制的第一页,并不真实。

而如果数据还继续累加,光这几个页也不够了,那就逐步的形成了一棵树,也就是说索引B-Tree是随着数据的积累逐步构建出来的。

最下边的一层叫做叶子节点,上边的叫做内节点,而叶子节点中存储的是全量数据,这样的树就是聚簇索引。一直有同学的理解是说索引是单独一份而数据是一份,其实MySQL中有一个原则就是数据即索引、索引即数据,真实的数据本身就是存储在聚簇索引中的,所谓的回表就是回的聚簇索引

但是我们也不一定每次都按照主键来执行SQL语句,大部分情况下都是按照一些业务字段来,那就会形成别的索引树,例如,如果按照b,c,d来创建的索引就会长这样。

推荐1个网站,可以可视化的查看一些算法原型:

目录:

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+树

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

而在MySQL官网上介绍的索引的叶子节点是双向链表。

关于索引结构的小结:

对于B-Tree而言,叶子节点是没有链接的,而B+Tree索引是单向链表,但是MySQL在B+Tree的基础之上加以改进,形成了双向链表,双向的好处是在处理> <,between and等'范围查询'语法时可以得心应手。

5、MySQL索引的一些使用规范

1、 只为用于搜索、排序或分组的列创建索引。

重点关注where语句后边的情况

2、 当列中不重复值的个数在总记录条数中的占比很大时,才为列建立索引。

例如手机号、用户ID、班级等,但是比如一张全校学生表,每条记录是一名学生,where语句是查询所有’某学校‘的学生,那么其实也不会提高性能。

3、 索引列的类型尽量小。

无论是主键还是索引列都尽量选择小的,如果很大则会占据很大的索引空间。

4、 可以只为索引列前缀创建索引,减少索引占用的存储空间。

alter table single_table add index idx_key1(key1(10))

5、 尽量使用覆盖索引进行查询,以避免回表操作带来的性能损耗。

select key1 from single_table order by key1

6、 为了尽可能的少的让聚簇索引发生页面分裂的情况,建议让主键自增。

7、 定位并删除表中的冗余和重复索引。

冗余索引:

单列索引:(字段1)

联合索引:(字段1 字段2)

重复索引:

在一个字段上添加了普通索引、唯一索引、主键等多个索引

6、 执行计划

其中常用的是:

possible_keys: 可能用到的索引

key: 实际使用的索引

rows:预估的需要读取的记录条数

7、 线上案例

案例1:

在建设互联网医院系统中,问诊单表当时量级23万左右,其中有一个business_id字符串字段,这个字段用来记录外部订单的ID,并且在该字段上也加了索引,但是'根据该ID查询详情'的SQL语句却总是时好时坏,性能不稳定,快则10ms,慢则2秒左右,SQL大体如下:

select 字段1、字段2、字段3 from nethp_diag where business_Id = ?

因为business_id是记录第三方系统的订单ID,为了兼容不同的第三方系统,因而设计成了字符串类型,但如果传入的是一个数字类型是无法使用索引的,因为MySQL只能将字符串转数字,而不能将数字转字符串,由于外部的ID有的是数字有的是字符串,因而导致索引一会可以走到,一会走不到,最终导致了性能的不稳定。

案例2:

在某次大促的当天,突然接到DBA运维的报警,说数据库突然流量激增,CPU也打到100%了,影响了部分线上功能和体验,遇到这种情况当时大部分人都比较紧张,下图为当时的数据库流量情况:

相关SQL语句:

当时的索引情况

当时的执行计划

其实在patientId和doctor_pin两个字段上是有索引的,但是由于线上情况的改变,导致test判断没有进入,这样的通用查询导致这两个字段没有设置上,进而导致了数据库扫描的量激增,对数据库产生了很大压力。

案例3:

2020年某日上午收到数据库CPU异常报警,对线上有一定的影响,后续检查数据库CPU情况如下,从7点51分开始,CPU从8%瞬间达到99.92%,丝毫没有给程序员留任何情面。

当时的SQL语句:

select rx_id, rx_create_time from nethp_rx_info where rx_status = 5 and status = 1 and rx_product_type = 0 and (parent_rx_id = 0 or parent_rx_id is null) and business_type != 7 and vender_id = 8888 order by rx_create_time asc limit 1;

当时的索引情况:

PRIMARY KEY (id), UNIQUE KEY uniq_rx_id (rx_id), KEY idx_diag_id (diag_id), KEY idx_doctor_pin (doctor_pin) USING BTREE, KEY idx_rx_storeId (store_id), KEY idx_parent_rx_id (parent_rx_id) USING BTREE, KEY idx_rx_status (rx_status) USING BTREE, KEY idx_doctor_status_type (doctor_pin, rx_status, rx_type), KEY idx_business_store (business_type, store_id), KEY idx_doctor_pin_patientid (patient_id, doctor_pin) USING BTREE, KEY idx_rx_create_time (rx_create_time)

当时这张表量级2000多万,而当这条慢SQL执行较少的时候,数据库的CPU也就下来了,恢复到了49.91%,基本可以恢复线上业务,从而表象就是线上间歇性的一会可以开方一会不可以,这条SQL当时总共执行了230次,当时的CPU情况也是忽高忽低,伴随这条SQL语句的执行情况,从而最终证明CPU的飙升是由于这条慢SQL。当线上业务逻辑复杂的时候,你很难第一时间知道到底是由于那条SQL引起的,这个就需要对业务非常熟悉,对SQL很熟悉,否则就会白白浪费大量的排查时间。

最后的排查结果:

在头天晚上的时候添加了一条索引rx_create_time,当时没事,但是第二天却出了事故。

加索引前后走的索引不同,一个是走的rx_status(处方审核状态)单列索引,一个是走的rx_create_time(处方提交事件)单列索引,这个就要回到业务,因为处方状态是个枚举,且枚举范围不到10个,也就说线上29,000,000的数据量也就是被分成了不到10份,rx_status=5的值是其中一份,因而通过这个索引就可以命中很多行,这是业务规则,再套用MySQL的特性,主要是以下几条:

1、没加新索引rx_create_time的时候,由于order by后边没有索引,就看where条件中是否有合适的索引,查询选择器选定rx_status这个单列索引,而rx_status=5这个条件下限制的数据行在索引中是连续,即使需要的rx_id不在索引中,再回主键聚簇索引也来得及,由于order by后边没有索引,所以走磁盘级别的排序filesort,高峰积压的时候处方就1万到2万,跑到了100ms,白天低谷的时候几百单也就20ms。

2、新加索引之后,就分两种情况:

2.1、加索引是在晚上,当前命中的行数比较少,由于当天晚上的时候待审核的处方确实很少,也就是rx_status=5的确实很少,查询优化器感觉反正没多少行,排序不重要,因而就还是选择rx_status索引。

2.2、第二天白天,待审核的处方数量很多了(rx_status=5的数据量多了),当时可以命中几万数据,如果当前命中的行数比较多,查询优化器就开始算成本,感觉排序的成本会更高,那就优先保排序吧,所以就选择rx_create_time这个字段,但是这个索引树上没有别的索引字段的信息,没办法,几乎每条数据都要回表,进而引发了灾难。

8、 推荐用书

这本书以一种诙谐幽默的风格写了MySQL的一些运行机制,非常适合阅读,理解成本大幅降低。

https://item.jd.com/13009316.html

https://item.jd.com/10066181997303.html

9、一些感悟

关于数据库的性能优化其实是一个很复杂的大课题,很难通过一篇帖子讲的很全面和深刻,这也就是为什么我的标题是‘浅析’,程序员的成长一定是要付出代价和成本,因为只有真的在一线切身体会到当时的紧张和压力,对于一件事情才能印象深刻,但反之也不能太过于强调代价,如果可以通过一些别人的分享就可以规避一些自己业务的问题和错误的代价也是好的。

与MySQL性能优化浅析及线上案例相似的内容:

MySQL性能优化浅析及线上案例

关于数据库的性能优化其实是一个很复杂的大课题,很难通过一篇帖子讲的很全面和深刻,这也就是为什么我的标题是‘浅析’,程序员的成长一定是要付出代价和成本,因为只有真的在一线切身体会到当时的紧张和压力,对于一件事情才能印象深刻,但反之也不能太过于强调代价,如果可以通过一些别人的分享就可以规避一些自己业务的问题和错误的代价也是好的。

万字长文浅析配置对MySQL服务器的影响

有很多的服务器选项会影响这MySQL服务器的性能,比如内存中临时表的大小、排序缓冲区等。有些针对特定存储引擎(如InnoDB)的选项,也会对查询优化很有用。

[转帖]MySQL优化的5个维度

面试官如果问你:你会从哪些维度进行MySQL性能优化?你会怎么回答?所谓的性能优化,一般针对的是MySQL查询的优化。既然是优化查询,我们自然要先知道查询操作要经过哪些环节,然后思考可以在哪些环节进行优化。我之前写过一条SQL查询语句是如何执行的?,感兴趣的朋友可以阅读一下,我用其中的一张图展示查询

[转帖]【建议收藏】15755 字,讲透 MySQL 性能优化(包含 MySQL 架构、存储引擎、调优工具、SQL、索引、建议等等)

https://my.oschina.net/jiagoushi/blog/5593246 0. 目录 1)MySQL 总体架构介绍 2)MySQL 存储引擎调优 3)常用慢查询分析工具 4)如何定位不合理的 SQL 5)SQL 优化的一些建议 1 MySQL 总体架构介绍 1.1 MySQL 总体

测试环境治理之MYSQL索引优化篇

测试环境这个话题对于开发和测试同学一定不陌生,大家几乎每天都会接触。但是说到对测试环境的印象,却鲜有好评,下面就主要给大家分享一次实际工作中的Mysql性能优化实践,与大家共勉~

[转帖]Intel PAUSE指令变化如何影响MySQL的性能

https://zhuanlan.zhihu.com/p/581200704 导读 x86、arm指令都很多,无论是应用程序员还是数据库内核研发大多时候都不需要对这些指令深入理解,但是 Pause 指令和数据库操作太紧密了,本文通过一次非常有趣的性能优化来引入对 Pause 指令的理解,期望可以事半

高性能MySQL实战(三):性能优化 | 京东物流技术团队

这篇主要介绍对慢 SQL 优化的一些手段,而在讲解具体的优化措施之前,我想先对 EXPLAIN 进行介绍,它是我们在分析查询时必要的操作,理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读,在下文中规定类似 key1 的表示二级索引,key_part1 表示联合索引的第一部分,uni

数据库系列:MySQL慢查询分析和性能优化

1 背景 我们的业务服务随着功能规模扩大,用户量扩增,流量的不断的增长,经常会遇到一个问题,就是数据存储服务响应变慢。 导致数据库服务变慢的诱因很多,而RD最重要的工作之一就是找到问题并解决问题。 下面以MySQL为例子,我们从几个角度分析可能产生原因,并讨论解决的方案。 2 定位慢查询的原因并优化

[转帖]7600字带你学会 Redis 性能优化点, 建议收藏!

https://www.cnblogs.com/shoshana-kong/p/14040195.html 在一些网络服务的系统中,Redis 的性能,可能是比 MySQL 等硬盘数据库的性能更重要的课题。比如微博,把热点微博[1],最新的用户关系,都存储在 Redis 中,大量的查询击中 Redi

数据库系列16:MyISAM与InnoDB的索引对比

相关文章 数据库系列:MySQL慢查询分析和性能优化 数据库系列:MySQL索引优化总结(综合版) 数据库系列:高并发下的数据字段变更 数据库系列:覆盖索引和规避回表 数据库系列:数据库高可用及无损扩容 数据库系列:使用高区分度索引列提升性能 数据库系列:前缀索引和索引长度的取舍 数据库系列:MyS