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

Tech

导读

      本文重点介绍了MySQL数据库性能优化的常见手段、底层架构实现原理、互联网医院建设过程中的几个典型案例；读者可以通过本文了解到日常开发中的注意事项、数据库性能优化的解决思路、如何规避隐藏规则带来的风险等几个方面的知识。

01 数据库性能优化的原因和措施

在今年的敏捷团队建设中，我通过Suite执行器实现了一键自动化单元测试。Juint除了Suite执行器还有哪些执行器呢？由此我的Runner探索之旅开始了！

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

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

图1 数据库性能优化的常见措施

02   MySql性能优化  

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。从设计稿出发，提升页面搭建效率，亟需解决的核心问题有数据来源：交易数据的来源，包含业务信息、联系人、数据接入协议

2.1 MySql底层架构

    
首先了解一下数据的底层架构，也有助于更好地优化。

图2 一次查询请求的执行过程

2.2 索引构建过程

    目前比较常用的是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，而一页的格式大概如下：

图3 MySql读取数据的内存格式

每一页都包括了以下内容：首先是页头、其次是页目录、还有用户数据区域。

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

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

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

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

图4 页间双向链表的示意图

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

图5 索引B-Tree

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

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

图6 按照业务字段形成的索引树

在这里向读者们推荐一个网站，可以可视化的查看一些算法原型：

目录：

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

B+树：

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

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

图7 MySQL官网上介绍的索引的叶子节点

关于索引结构的小结：

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

2.3 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）
•  重复索引：

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

2.4 执行计划

图8 执行计划

其中常用的是：

possible_keys: 可能用到的索引

key: 实际使用的索引

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

03   线上案例 

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。从设计稿出发，提升页面搭建效率，亟需解决的核心问题有：    

案例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%了，影响了部分线上功能和体验，遇到这种情况当时大部分人都比较紧张，下图为当时的数据库流量情况：

图9、图10 数据库流量情况

相关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>

当时的索引情况：

图11 索引情况

当时的执行计划：

图12 执行计划

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

案例3：

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

图13 CPU异常报警

当时的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. 新加索引之后，就分两种情况：

（1）加索引是在晚上，当前命中的行数比较少，由于当天晚上的时候待审核的处方确实很少，也就是rx_status=5的确实很少，查询优化器认为行数不多，排序不重要，因而就还是选择rx_status索引。

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

04   推荐用书   

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。

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

     《基于Oracle的SQL优化》本书从最底层的Oracle原理入手，由浅入深带领读者深入了解，此外每一步的操作均有详细配图，并结合了大量真实案例，阅读中不枯燥，阅读后有收获。

05   总结   

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。

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

      对于数据库性能优化需要一个完整的知识体系，从硬件到编码全方面的思考和实践，本文从数据库性能优化的意义入手，讲解了底层架构，并介绍了几个京东健康互联网医院在建设过程中的一些实际案例，希望这些知识和案例能够帮助读者在自己的系统建设中提升数据库性能、规避线上风险。

推荐阅读拜占庭将军问题和 Raft 共识算法讲解
交易履约订单中心实践
zookeeper的Leader选举源码解析
订单逆向履约系统的建模与PaaS化落地实践

求分享

求点赞

求在看