分析MySQL优化思路

本文主要讲解“MySQL优化思路分析”，简单明了，易学易懂。请跟随边肖的思路，一起学习学习《MySQL优化思路解析》！

思考角度

数据库技术经历了三个阶段：人工管理阶段、文件系统阶段和数据库系统阶段。

早期在没有软件系统的情况下，某项业务的现实操作可以通过人工核算和人工管理口头协议来实现，这种方式存在时间较长，是一种相对低效的方案。下一阶段，随着计算机技术的发展，出现了用excel表格代替手工核算的文件系统阶段，在一定程度上提高了生产力。然后，在软件系统简单高效的数据库系统阶段，生产力再次提升，将现实世界中的具体问题抽象为数据，通过数据的流通和变化来表现现实世界的业务。在软件系统中，数据的存储一般由一个关系数据库和几个非关系数据库组成。

与系统业务有很强的联系，这就需要产品经理在设计业务时了解数据存储和查询的过程，在设计之初，明确业务变化会对数据库产生什么影响，是否需要引用新的技术栈。比如产品经理设计的一个业务就是对单个表量一百万的几个mysql表的数据进行统计分析和汇总。如果直接使用mysql多表查询，肯定会出现慢查询，导致msyql服务宕机。此时，解决方案要么是在产品方面妥协，要么是改变技术堆栈。

在系统架构和数据库方案上，需要选择更适合公司团队能力的方案。在系统前期，简单的数据库优化匹配钞票能力将是最具性价比的方案。但当mysql数据库钞票能力无能为力时，引入以关键功能为核心的软件服务将成为最具性价比的方案。遇到问题如何选择合适的方案，才是体现你价值的时候。

一个穷小子攀上富家女，但短暂的甜蜜无法与现实阶级的不平等抗衡。大团圆结局只存在于穷小子的幻想和琼瑶老师的电视剧里。

如何在有限的成本下提高数据存储的性能是本文的中心思想。

背景知识

相信你在日常工作中会经常接触到以下内容。我来简单总结一下。

00-1010关系数据库是由二维表及其关系组成的数据组织。它为软件提供事务数据一致性、数据持久性等功能，是软件系统的核心存储服务，是我们在开发和面试中最常接触的数据库。对于一些小的外包项目，一个mysql足以满足所有的业务需求。这是我们经常接触到的东西。它实际上充满了门道。我们将在下一章讨论门道。

优点：

事务

坚持

相对通用的SQL语言

问题

对硬盘的I/O要求非常高。

数据量大的聚合查询效率低

索引缺失

索引最左边的匹配原则使其不适合全文检索。

事务使用不当会导致锁阻塞。

横向扩张带来的问题很难处理。

关系型数据库

MySQL数据库作为一种关系型数据存储软件，有利也有弊。因此，当软件系统的数据量不断扩大，业务复杂度不断提高时，我们不能指望通过增强MySQL数据库的能力来解决所有问题。相反，我们使用其他存储软件，通过使用各种类型的NoSql来解决数据量不断扩大、业务复杂性不断增加的软件系统问题。

关系数据库是关系数据库在不同场景下的优化。并不意味着引入一些NoSql就万事大吉了。而是要充分了解市场上NoSQL的类型和应用难点，在合适的场景下选择合适的存储软件才是正确的做法。

非关系型数据库 - NoSql

在业务中，经常会有关于一些表的内容的查询，但是查询的结果是不变的。于是出现了基于Memcached和Redis的键值存储软件，广泛应用于系统的缓存模块中。Redis比Memcached有更多的数据结构和持久性，这使得它成为使用最广泛的KV NoSql。

00-1010全文搜索场景中，MySQLB树索引的查询优化，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

• 索引覆盖可以跳过生成中间结果集，直接输出查询结果

• ORDER字段需要有索引且与WHERE的条件且与输出内容均在同一个索引中

• 尽量减少额外的排序，指定where条件

• where 语句与ORDER BY语句组合满足最左前缀

• 最高效-索引覆盖(场景少，遇见机率不大)

• 分页查询

• 先想办法走索引覆盖

• 先查出所需要数据的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有原生的支持，建议在项目初期就考虑，越往后难度越大（优先级低）

软优化 - 写入更新删除

• 锁

• 自行google/baidu

• 表锁

• 元数据锁

• 自行google/baidu

• 自行google/baidu

• 自行google/baidu

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

• 全局锁

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

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

• 解决死锁

• 调整innodb_lock_wait_timeout参数

• 主动死锁检测：innodb_deadlock_detect

• 默认为50秒，即等待50秒还未获取锁，当前语句报错

• 如果等待时间过长，可以适当缩短此参数

• 发现死锁时回滚代价较小的事务

• 默认开启

• 参数配置

• 没必要情况下不开启事务

• 查询尽量放在事务外，减少锁的行数

• 避免事务时间过长，不要在事务中触发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了。

感谢各位的阅读，以上就是“分析MySQL优化思路”的内容了，经过本文的学习后，相信大家对分析MySQL优化思路这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！