——「高性能MYSQL」读书笔记（第十四章）

《高性能****MYSQL》

MYSQL经典书籍，常读常新。

十四章讲解的是应用层优化。对于用户能感受到的响应时间上，如果在提高 MySQL 性能上花费太多时间，容易使视野局限在 MySQL 本身。应用层（web 层）优化需要跟数据库优化同步进行。书上以 Apache 服务器和 php 语言来讲解，这里只摘录具备共性的部分。

这部分主要介绍 web 层常见的问题。书上没有最佳解决方案，更多的是让读者思考自己实践的过程中遇到这些问题多多思考

1. 系统占用资源情况

   1. cpu

   2. 内存

   3. 网络

   4. 磁盘

2. 获取数据的必要性

   1. 是否存在获取大量的数据，但实际上只需要其中一部分数据

3. 由数据库处理数据的必要性

   1. 用数据库进行复杂的字符串截取和正则匹配——应用

   2. 对结果数据进行统计操作——数据库

4. 查询过多

   1. 是否可以使用一个 sql （join，union）合并多个查询

5. 查询太复杂

   1. 查询时间过长导致锁竞争

   2. 大 sql 命中不了缓存

6. 建立连接的必要性

   1. 如果应用中可以命中缓存，就无需建立数据库连接

7. 无用查询过多

   1. 例如：ping、set 字符集

8. 是否启动连接池

   1. 连接池可以限制总连接数

   2. 带来了事务、临时表、连接配置的相互干扰

9. 长连接的必要性

   1. 网络慢创建连接开销大——创建长连接

   2. 查询少，连接频率高——创建短连接

10. 连接回收

    1. 长期占用连接影响其他进程创建连接

1. 使用缓存服务器

   1. 静态页面可以使用缓存服务器进行缓存，避免到 web 服务器

2. 使用负载均衡

   1. 通过负载均衡可以给 web 服务器做缓存，避免直接响应用户请求，拦截 ddos 攻击

3. 打开 gzip 压缩

4. 长距离不提供长连接

   1. 长连接会导致 web 服务器连接存活长，容易被客户端拖垮

5. 寻找最优的并发度

   1. cpu 密集型的应用最佳并发度等于 cpu 核数

   2. 很多时刻大部分连接都在等待 io 操作，并不需要占用 cpu，可以提高应用并发度

   3. 不能无限提高并发度，因为上下文切换也有消耗

1. 缓存类型

   1. 本地缓存

   2. 本地共享内存

   3. 分布式内存

      1. redis、memcache

   4. 磁盘缓存

2. 缓存控制策略

   1. TTL

      1. 缓存有过期时间，清扫线程进行清扫

   2. 写时失效

      1. 当对象更新时，同时更新缓存

      2. 增加写时负担

   3. 读时失效

      1. 当对象读取时判断，是否需要更新缓存

      2. 增加读时负担

3. 缓存分层

   1. 一次性读大对象

      1. 一致性——优势

      2. 可能带来浪费——劣势

      3. 更新需要整体更新——劣势

   2. 多次读取多个对象组合

      1. 缺少一致性——劣势

      2. 可以局部更新——优势

1. 不擅长存储大对象

2. 不擅长搜索

3. 不擅长图遍历

除了第一章节外，每个小组同学根据阅读内容，分析当前系统中存在的一些不合理的问题，分析并提出对应升级方案。

1. 背景

wfc-api 项目中，提供了查询用户待办接口，这接口提供查询的灵活度非常高，各种条件组合，还有全文检索的语句，这个 sql 在我们生产上慢查询很多。请看下面的 sql 片段

<select id="toDoTaskCount" resultType="java.lang.Integer" >
    select count(1) from (
    select t.taskinstance_id
    from fixflow_run_taskinstance t
    left join fixflow_run_taskidentitylink i on i.taskinstance_id=t.taskinstance_id
    left join fixflow_process_info fpi on fpi.process_key = t.processdefinition_key
    where t.end_time is null
    <include refid="common_where_if"/>
    <if test="vo.assigneeId != null and vo.assigneeId != ''">
        and  i.user_id=#{vo.assigneeId}
    </if>
    <if test="typeKeyList != null and typeKeyList != ''">
        and t.processdefinition_key in
        <foreach collection="typeKeyList" separator="," close=")" open="(" item="item">
            #{item}
        </foreach>
    </if>
    ...

<sql id="common_where_if">
    <if test="vo.subject != null and vo.subject != ''">
        and  t.querymsg like concat('%',#{vo.subject},'%')
    </if>
    <if test="vo.createTimeStart != null and vo.createTimeStart != ''">
        and  t.create_time &gt;=#{vo.createTimeStart}
    </if>
    <if test="vo.createTimeEnd != null and vo.createTimeEnd != ''">
        and  t.create_time &lt;= #{vo.createTimeEnd}
    </if>
    <if test="vo.endTimeStart != null and vo.endTimeStart != ''">
        and  t.end_time &gt;=#{vo.endTimeStart}
    </if>
    <if test="vo.endTimeEnd != null and vo.endTimeEnd != ''">
        and  t.end_time &lt;= #{vo.endTimeEnd}
    </if>
</sql>

1. 分析

• 这个 sql 包含着一个 like 语句，这个 like 语句并不是匹配前缀，而是任意匹配，这种匹配无法建立索引，mysql 进行字符串匹配，效率低下

• 这个 sql 组合条件非常多和自由，索引建立非常困难，稍微不注意，就会导致索引失效

1. 解决方案

• 承认 mysql 无法支撑全文检索和多维查询的需求

• 产品上做限制，拆解场景，将任意组合的 sql 细化成不同场景的 sql，并根据这些不同场景的 sql 创建索引，去掉全文检索的能力，避免大批量扫表

• 将全文检索和多维查询的需求，拆解到其他合适的数据引擎，比如 es。这也是现在正在建设的 bpm 任务中心提供的能力

1. 背景

wfc-api 项目中，流程图存储在数据库中，需要根据流程图解析出流程定义信息，后续流程处理等依赖于流程定义信息，对流程定义的使用比较高频，而流程定义的内容比较庞大，不适合每次从数据库获取，目前项目是将流程定义缓存到容器本地。

1. 分析

• 对于这种流程定义信息应该做缓存，如果缓存在redis，存在序列化的问题，对于超大对象的序列化和反序列化也比较耗费时间的，所以更适合存在本地缓存。

• 缓存在流程图重新发布时应该失效，本地缓存失效需要同时触发所有容器的缓存失效

• 服务每周发布时，都会导致所有容器的本地缓存失效，一定程度会增加发布后容器的开销

1. 目前解决方案

• 对于流程定义信息存在容器本地缓存

• 流程图重新发布时，通过mq触发广播消息清除所有容器对应缓存

• 针对服务每周发布时导致所有的本地缓存失效，目前没有较合适的解决方法

1. 更合适的解决方案（改造成本较高）

• 流程定义信息的大对象，拆解为若干细粒度的对象，数据库的存储也进行拆分，而不是整个流程图存一起

• 厘清若干细粒度对象哪些适合放在缓存，哪些仍查数据库，缓存尽量使用redis，缓存失效逻辑比较容易处理，服务发布也不会因为缓存大规模失效影响容器性能

• 不要将注意点总是放在 MySQL 上，应该从整个系统的角度来进行优化。大部分场景下，MySQL 并不是系统的瓶颈，我们遇到的大部分问题是忽略了自己系统的优化，而且过多地依赖了 MySQL。

• 使用缓存时要选择合适的缓存失效策略和缓存过期时间，结合业务的实际需求在一致性和高性能之间做一定的权衡。