作者：京东健康 孟飞
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个网站，能够可视化的检查一些算法原型：
目录：
www.cs.usfca.edu/~galles/vis…
B+树
www.cs.usfca.edu/~galles/vis…
而在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句子：
select count(1) from jdhe_medical_record where status = 1 and is_test = #{isTest,jdbcType=INTEGER} and electric_medical_record_status in (2,3) and patient_id = #{patientId,jdbcType=BIGINT} and doctor_pin = #{doctorPin,jdbcType=VARCHAR} and created >#{dateStart,jdbcType=TIMESTAMP};其时的索引状况
其时的履行计划
其实在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的一些运行机制，十分适合阅读，了解本钱大幅下降。
item.jd.com/13009316.ht…
item.jd.com/10066181997…
9、一些感悟
关于数据库的功用优化其实是一个很杂乱的大课题，很难经过一篇帖子讲的很全面和深入，这也便是为什么我的标题是‘浅析’，程序员的成长必定是要付出价值和本钱，由于只有真的在一线切身体会到其时的紧张和压力，关于一件工作才干形象深入，但反之也不能太过于强调价值，假如能够经过一些他人的分享就能够规避一些自己事务的问题和错误的价值也是好的。
