关于mysql:MySQL性能优化浅析及线上案例

作者：京东衰弱 孟飞

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

业务倒退初期，数据库中量个别都不高，也不太容易出一些性能问题或者出的问题也不大，然而当数据库的量级达到肯定规模之后，如果缺失无效的预警、监控、解决等伎俩则会对用户的应用体验造成影响，重大的则会间接导致订单、金额间接受损，因此就须要时刻关注数据库的性能问题。

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

数据库性能优化的常见伎俩有很多，比方增加索引、分库分表、优化连接池等，具体如下：

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/~gal…

B+ 树

https://www.cs.usfca.edu/~gal…

而在 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 语句：

<!– 统计医患下过来 24 小时内开的电子病历总数 –>

<select id=”getCountByDPAndTime” resultType=”integer”>

select count(1)

from jdhe_medical_record

where status = 1 and is_test = #{isTest,jdbcType=INTEGER} and electric_medical_record_status in (2,3)

<if test=”patientId != null”>

and patient_id = #{patientId,jdbcType=BIGINT}

</if>

<if test=”doctorPin != null”>

and doctor_pin = #{doctorPin,jdbcType=VARCHAR}

</if>

and created >#{dateStart,jdbcType=TIMESTAMP};

</select>

过后的索引状况

过后的执行打算

其实在 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….

https://item.jd.com/100661819…

9、一些感悟

对于数据库的性能优化其实是一个很简单的大课题，很难通过一篇帖子讲的很全面和粗浅，这也就是为什么我的题目是‘浅析’，程序员的成长肯定是要付出代价和老本，因为只有真的在一线切身体会到过后的缓和和压力，对于一件事件能力印象粗浅，但反之也不能太过于强调代价，如果能够通过一些他人的分享就能够躲避一些本人业务的问题和谬误的代价也是好的。