高赞分享：符合生产的MySQL优化思路

前言

写这篇文章的出发点是记录我在工作中处理数据的问题中累计的经验，写着写着发现每一个点都会衍生出其它的背景知识，如优化索引时需要对慢查询、Explain等相关功能有一定的了解，如引入Elasticsearch需要解决数据的同步，学习Elasticsearch的知识等等，由于文章的篇幅不可能把每一个点都像视频教程一样细细道来，只能以我有限的认知和对一些通用性的点进行归纳总结。即便是这样，文章的篇幅也已经很长了，大家如果对某一点有兴趣的话还请自行baidu/google单个细节的深入知识。

文章的篇幅较长，如有兴趣不妨品味一遍，希望没有浪费您的数十分钟。【推荐学习：《mysql视频教程》】

思考角度

数据库技术到目前共经历了人工管理阶段、文件系统阶段和数据库系统阶段。

在早期没有软件系统的时候，通过手工计帐和口头协议的人工管理阶段也能实现现实世界对某种业务运行，这种形式存在了相当长的时间，是效率相对低下的一种方案。往后的一个阶段，随着计算机技术的发展，出现了以excel表格代替手工计帐的文件系统阶段，一定程度的提高了生产力。再到软件系统以操作简单、效率高效的数据库系统阶段，实现了生产力的再次提升，把现实世界的具体问题抽象成了数据，通过数据的流转与变动来表代现实世界的业务。而在软件系统中，数据的存储一般由一个关系型数据库搭配多个非关系型数据库组建而成。

数据库跟系统业务是强关联的，这就要求产品经理的设计业务的时候要了解数据存储跟查询的流程，在设计之初就明确改业务对数据库会有什么影响跟是否需要引用新的技术栈。如产品经理设计的一个业务是对多张单表体积百万级的mysql表进行数据统计分析汇总，如果直接用mysql多表查询的话一定会产生慢查询从而导致msyql服务的宕机，这时解决方案便是要不产品端妥协，要不改变技术栈。

系统架构与数据库方案中要选择更合适公司团队能力的，在系统前期，简单的数据库优化配合钞能力会是最有性价比方案，但遇到mysql数据库钞能力也无能为力的时候，引入对关键功能为核心的软件服务就会成为最有性价比方案，如何在遇到问题时选择合适的方案，就是体现你价值的时候了。

一个穷小伙攀上一个富家女，短暂的甜蜜終敌不过现实阶级的不对等，美好的结局只存在于穷小伙的幻想与琼瑶老师的电视剧中。

如何在有限的成本中提升数据存储的性能，便是本文章于大家论讨的中心思想。

背景知识

相信大家的日常工作中会经常接触到以下内容，小弟就简单地总结一下吧。

关系型数据库

关系型数据库就是由二维表及其之间的联系所组成的一个数据组织,为软件提供事务数据一致性、数据持久化等功能，是软件系统的核心存储服务，是我们开发跟面试都是最常接触到的数据库，对于一些小型外包项目，一个mysql足以满足全部的业务需求了。就是一个我们经常接触到的东西，内里其实是充满了门道的，往后章节再细聊其中门道。
优点：

1. 事务
2. 持久化
3. 相对通用的SQL语言

问题

1. 对硬盘I/O要求非常高
2. 大数据量的聚合查询效率低
3. 索引不命中
4. 索引最左匹配原则导致不合适做全文检索
5. 事务使用不当会引起锁堵塞
6. 水平扩展后带来的种种问题难处理

非关系型数据库 – NoSql

MySQL数据库作为一种关系型数据的存储软件，有优点同时也有明显的缺点，因此通常在软件系统数据量不断扩大与业务复杂度不段提升的情况下，不能指望通过增强MySQL数据库的能力来解决全部的问题，用是引入其他存储软件，利用各类型的NoSql来解决软件系统数据量不断扩大与业务复杂度不段提升的问题。
关系型数据库是对关系型数据库的在不同场景的优化，不是意味着引入某种NoSql就万事大吉，而是充分了解市面上NoSQL的类型与应用难度，在合适的场景下选择合适的存储软件才是正确的做法。

Key-Value型

在业务中会存在经常对某些表的内容进行查询，但查询的结果绝大数是不变的，所以出现了以Memcached、Redis为主的Key-value存储软件，广泛应用在系统中的缓存模块。Redis比Memcached多多的数据结构与持久化让其成为KV型NoSql中应用最广的。

搜索型

全文搜索的场景下，MySQLB+树索引的查询优化，like查询是无法命中索引的，每一次like关键字查询都是一次全表扫描，在几万条数据量的表还算可以支撑，但数据最一在就会产生慢查询，要是业务代码写得不好在事务中调用了Like查询就会产生读锁。以倒排索引为核心的ElasticSearch为能完美地满足全文搜索的场景，同时ElasticSearch对海量数据支持也十分好，文档与生态也很好，ElasticSearch是搜索型的代表产品。

文档型

文档型NoSql指的是将半结构化数据存储为文档的一种NoSql，文档型NoSql通常以JSON或者XML格式存储数据，因此文档型NoSql是没有Schema的，由于没有Schema的特性，我们可以随意地存储与读取数据，因此文档型NoSql的出现是解决关系型数据库表结构扩展不方便的问题的。笔者没有使用过

列式

对于一定规模的企业，业务上会经常涉及到一些实时且灵活的数据汇总，这种业务不太合适用提前计算的方案来解决，那怕是能用提前计算汇总的方案写出了业务，但随着汇总的数量据增加的时候，对汇总数据做最后一步累加也会慢慢变得很慢，那列式NoSql就是这种场景下的产物，大数据时代最具代表性的技术之一了，常见的有HBase，但HBase的应用是十分重的，往往需要一整套Hadoop生态来运行，笔者公司用的是阿里云的AnalyticDB，一个兼容MySql查询语句的列式存储软件。利用汇总+列式存储软件的强大查询能力，足以支持各种实时且灵活的数据汇总务业。

案例

以2021年为时间节点来看，大多数的系统的初期都是以以下方案为起点的，接下来我会在这个案例中慢慢做一些调整。

硬件升级所带来的收益是越往后越收益越低，在时间、人员紧张的时候这是最快的优化方案。软件优化所带来的收益是越往后越收益越高，但越往后所要求技术人员的水平也越高，在时间、人员允许的情况下是最有性价比的优化方案。硬件与软件的优化不是互斥的，在需要的时候两者同时可接近MYSQL性能的上限。

硬优化-钞能力

• 阶段一

  • 提高磁盘I/O,尽量拿用SSD磁盘 （质的提升）
  • 提高内存 ,增加查询缓存空间
  • 增加CPU核心数，增加执行线程

• 阶段二

  • 自建mysql更换为服务商mysql服务
  • 开启自带读写分离功能

• 阶段三

  • 服务商mysql服务更换为云原生分布式数据库
  • 开启自带读写分离功能
  • 开启自带分表功能

软优化 – 查询 – OLTP

OLTP主要用来记录某类业务事件的发生，如用户行为，当行为产生后，系统会记录是用户在何时何地做了何事，这样的一行（或多行）数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功，像常见的业务系统系统都属于OLTP，而使用的数据库都为带事务的数据库，如MySlq、Oracle等。对OLTP来说，提升查询的速度、服务稳定就是优化的核心

• 慢查询
  • 通过慢查询日志发现有效率问题的SQL
• 问题sql排查方向
  • 索引设计有问题
  • SQL语句有问题
  • 数据库选错索引
  • 单表体积大
• Explain具体分析
  • 查看sql执行较率
  • 查看索引命中情况 （重点）
• mysql优化器
  • 优化器选取索引时，会参考索引的基数(Cardinality)
  • 基数是MySQL自动维护且估算出来的，不一定完成准确
  • 索引不命中或用错索引就是优化器这一步出了问题
  • analyze 可以重新统计索引信息并重算基数
• 强制索引
  • force 关键字可以强制使用索引，在业务代码上强制指定index
• 覆盖索引 – 最理想的命中索引
  • 覆盖索引指的是，查询语句从执行到返回结果均使用同一个索引(唯一、普通、联合索引等)
  • 覆盖索引可以有交减少回表查询
  • 若数据的查询不只使用了一个索引，则不是覆盖索引
  • 可以通过优化SQL语句或优化联合索引，来使用覆盖索引
• count() 函数
  • count(非索引字段) – 无法使用覆盖索引，理论上最慢
  • count(索引字段) – 可以覆盖索引，依然需要每次判断字段是否为null
  • count(主键) – 同上
  • count(1) – 只有扫描索引树，没有解析数据行的过程，理论更快,但还是会判读1是否为null
  • count(* ) – MySQL专门优化了count(*)函数直接返回索引树中数据的个数，最优
• ORDER BY
  • 尽量减少额外的排序，指定where条件
  • where 语句与ORDER BY语句组合满足最左前缀
  • 最高效-索引覆盖(场景少，遇见机率不大)
    • 索引覆盖可以跳过生成中间结果集，直接输出查询结果
    • ORDER字段需要有索引且与WHERE的条件且与输出内容均在同一个索引中
• 分页查询
  • 先想办法走索引覆盖
  • 先查出所需要数据的id,回表得到最终结果集
• 索引下推
  • KEY store_id_guide_id (store_id,guide_id) USING BTREE
  • select * from table where store_id in (1,2) and guide_id = 3;
  • MySQL5.6之前，需要先拿用索引查询store_id in (1,2),再全部加表验证film_id = 3
  • MySQL5.6之后，如果索引中可以判读，直接使用索引过滤
• 松散索引扫描
  • KEY store_id_guide_id (store_id,guide_id) USING BTREE
  • select film_id from table where guide_id = 3
  • MySQL8.0新特性
  • 松散索引扫描可以打破”左侧原则”，解决带头大哥丢失的问题
  • 效率低于联合索引
• 函数操作
  • 对索引字段进行函数操作，优化器会放弃索引
  • 这种情况可能包函：时间函数，字符串转为数字，字符编码转换
  • 优化使用服务端逻辑来代替mysql函数
• 单表体积过大
  • 升级mysql，不同的mysql软件能承载的单表体积是不同的，我以目前的经验看，阿里云polardb集群版单表2亿的情况下查询命中索引是没有问题的（优先级高）
  • 数据结算 – 如流水类的数据可以按某个时间点来结算得到一个最新值，已结算流水转到备份表 （优先级中）
  • 数据冷热分离 – 不能做结算的数据跟据查询的频次做区分，频次低的转移到另外的表中查询，业务上区分好查询的入口 （优先级中）
  • 分布式数据库分表 – 开启分布式数据库带单的分表功能，分布式数据库组件管理对分表后的插入、查询（优先级中）
  • 代码实现分表 – 按一定的规则把单表拆分到多张表，在PHP、GO的大多数框架ORM中分拆后需要对框架ORM做一定的修改，JAVA中的ORM有原生的支持，建议在项目初期就考虑，越往后难度越大（优先级低）

软优化 – 写入更新删除

• 锁

  • 按照粒度分，MySQL锁可以分为全局锁、表级锁、行锁

  • 全局锁

    • 自行google/baidu

  • 表级锁分为表锁（数据锁）和元数据锁

    • 表锁
      • 自行google/baidu
    • 元数据锁
      • 自行google/baidu

  • 行锁会锁住数据行，分为共享锁和独占锁

    • 自行google/baidu

• 解决死锁

  • 参数配置
    • 调整innodb_lock_wait_timeout参数
      • 默认为50秒，即等待50秒还未获取锁，当前语句报错
      • 如果等待时间过长，可以适当缩短此参数
    • 主动死锁检测：innodb_deadlock_detect
      • 发现死锁时回滚代价较小的事务
      • 默认开启
  • 没必要情况下不开启事务
  • 查询尽量放在事务外，减少锁的行数
  • 避免事务时间过长，不要在事务中触发http请求
  • 主动查看事务状态

    show  processlist;SELECT * FROM information_schema.INNODB_TRX; //长事务SELECT * FROM information_schema.INNODB_LOCKs; //查看锁SELECT * FROM information_schema.INNODB_LOCK_waits; //查看阻塞事务

搜索业务

• 搜索行数10万以下 – mysql硬扛
  • 提升mysql的cpu、io、内存硬件
• 搜索行数10万以上 – 引入Elasticsearch

Elasticsearch的倒排索引，适合做全文搜索，但数据构结的灵活性差。

• 数据同步
  • 业务代码变动数据时同时同步到Elasticsearch
  • Canel订阅mysql日志触发同步
• Elasticsearch-index
  • 由具有相同字段的文档列表组成 – 类比为mysql的table
  • 字段类型一旦设定后，禁止修改，允许新增字段
  • 具体方法自行google/baidu
• Elasticsearch-Document
  • 用户存储在es中的数据文档 – 类比为mysql的行
  • 由 元数据 与 Json Object 组成
  • 元数据 与 Json Object详情自行google/baidu
• Elasticsearch-分词器
  • 自行google/baidu
• Elasticsearch-倒排索引 （重点）
  • 自行google/baidu
• Elasticsearch-聚合分析
  • 自行google/baidu

统计业务 -OLAP

OLAP是相对于OLTP事务处理场景而然用来对数据的决策分析，是一种运用在大数据分析上的离线数仓思路，不是具体的技术栈,当你的方案能体现OLAP分析处理的思路的话，那该方案就是OLAP了。

早期数据仓库构建主要指的是把企业的业务数据库如ERP、CRM、SCM等数据按照决策分析的要求建模并汇总到数据仓库引擎中，其应用以报表为主，目的是支持管理层和业务人员决策（中长期策略型决策）。随着IT技术走向互联网、移动化，数据源变得越来越丰富，在原来业务数据库的基础上出现了非结构化数据，比如网站log，IoT设备数据，APP埋点数据等，这些数据量比以往结构化的数据大了几个量级。

无论OLAP面对的业务如何变化，都离不开以下的步骤：确定分析领域->同步业务数据到运算库->数据清洗建模->同步到数据仓库->对外暴露

其中计算源数据库是为专门给数据清洗用的，目的是避免数据清洗时影响业务数据库的性能。通过将计算源数据库的数据按业务、维度清洗，增加数据易用性和复用性，得到最终的实时明细数据，落盘到数据仓库，再由数据仓库提供最后的决策分析数据。

DEMO方案

生产方案

每个环节的软件都是可用相同功能的软件替换的，用团队最有把握的软件实现方案，那该方案就是OLAP了。

总结

优化要遵循脚踏实地，一步步地做能力沉淀，多轮迭代，不可一蹴而就。基于自己的基础、业务场景和未来的发展预期来多轮迭代。

迭代的原则是先把单个软件服务通过软优化与硬优化提升软件的效率，当优化成本低于收益时，站在未来的发展预期参考市面上成熟的方案，跟据方案按需地引入新的软件进行组合式创新，切忌盲目照搬，有机地融合才能达到1+1>2、2+1>3的效果，当引用的软件遇到瓶颈时再反复这个过程。

谢谢您看到这里，以上便是文章的所有内容，内容中所提出的优化点与方案不一定是最优解，是个人工作中的最佳实践，有不同见解欢迎谈论交流。