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

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

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

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

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

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

1. 刚才插入的几条数据就是放到这个用户数据区域的，这个是按照主键依次递增的单向链表。
2. 页目录这个是用来指向具体的用户数据区域，因为当用户数据区域的数据变多的时候也就会形成分组，而页目录就会指向不同的分组，利用二分查找可以快速的定位数据。

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



关于索引结构的小结：

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

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
   重复索引：在一个字段上添加了普通索引、唯一索引、主键等多个索引

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

  <!--统计医患下过去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>



    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`)

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这个字段，但是这个索引树上没有别的索引字段的信息，没办法，几乎每条数据都要回表，进而引发了灾难。