本文主要讲解“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优化思路这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/71154.html