sql调优

慢查询 SQL 治理方案

一、SQL 性能下降的原因

在对 SQL 进行分析之前，需要明确可能导致 SQL 执行性能下降的原因进行分析，执行性能下降可以体现在很多方面：

查询语句写的烂
索引没加好
表数据过大
数据库连接数不够
查询的数据量过大
被其他慢sql拖累
硬件配置过低
…

二、慢查询优化思路

优先选择优化高并发执行的 SQL，因为高并发的 SQL 发生问题带来后果更严重。
定位优化对象的性能瓶颈（在优化之前了解性能瓶颈在哪，IO、CPU、网络带宽）。
明确优化目标。
从 explain 执行计划入手。
永远用小的结果集驱动大的结果集。
尽可能在索引中完成排序。
只获取自己需要的列，不要使用 select *。
只使用最有效的过滤条件。
尽可能避免复杂的 join 和子查询，可以将相关的连接组装操作放到业务代码中进行。
合理设计并利用索引。
考虑加一层缓存。
如果业务允许，数据不太重要可以考虑写脚本删除历史数据，或者对数据进行归档存进历史表中。
数据量模糊查询或者特别复杂查询可以考虑 ES。
单表数据过大，数据库连接不够可以考虑分库分表。
如果业务允许，比如凌晨跑的定时脚本，造成的慢 SQL 可以忽略。
优化硬件配置，比如将固态硬盘换成 SSD。
…

1、索引

数据库的查询性能变慢，一般我们第一时间想到的就是增加索引或者优化索引。

什么情况需要添加索引？

以下情况我们可以考虑在表的字段上增加索引优化性能：

查询频繁、更新较少的字段。
经常排序、分组的字段。
用于连接的字段。

怎么对索引进行优化？

如果已经建立了索引查询性能还是慢，这个时候就需要考虑优化索引：

如果是单字段索引可以根据查询条件更改索引字段为过滤性更强的字段。
如果查询条件包含多个字段，可以考虑将单字段索引修改成联合索引增强过滤性。
索引列最好设置为 NOT NULL。
唯一性太差的字段谨慎建单索引，比如性别字段，回表过多。
在 JOIN、EXIST、IN 的小表驱动大表中给被驱动的大表连接匹配的字段建立索引。

索引覆盖

如果索引增加一两个字段可以利用上索引覆盖可以考虑加上，减少大量回表造成的开销。
like '%xxx’ 很可能会导致索引失效，可以通过索引覆盖对其优化。

优化排序

只查询用于排序的索引字段，可以利用索引进行排序，最左原则。
排序字段在多个索引中，无法使用索引排序。
只查询用于排序的索引字段和主键，可以利用索引进行排序。
查询主键之外的没有添加索引的字段，不会利用索引排序。
where 条件是范围查询时，会使 order by 索引失效。
对同一联合索引字段排序的升降序不一致，无法利用索引排序。

最左前缀

联合索引需符合最左前缀原则，过滤性强的字段往左边放，在范围查询的字段后的字段无法匹配索引。
对于字符串的查询需要符合最左前缀原则，
使用前缀索引。（count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度）

避免索引失效

明明建了索引了，结果 explain 却发现索引并未命中。
需要注意以下几种情况：

不满足最左前缀原则，联合索引或者字符串索引不满足从左到右的字段匹配。（索引覆盖可能还会走索引，全索引扫描要比全表扫描快）
索引的列上进行计算或者使用了函数或者隐式类型转换。
where 的 or 字段里面有一个是非索引列。
索引的唯一性太差，比如性别字段。
is null, is not null, != 也无法使用索引。

核心重点就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引抗下剩余20%的非典型查询，保证你99%以上的查询都能充分利用索引，就能保证你的查询速度和性能！

2、缓存

既然数据库的查询如此缓慢，我们可以考虑减轻它的压力，不必要的时候就不去查询数据库，而是去查询缓存。

何时考虑引入缓存？

读取频繁，数据能接受一定延迟或者更新不那么频繁，就可以把数据放到分布式或者本地缓存中提升读取性能，减少数据库的查询压力。

缓存方案简介

数据一致性
缓存数据与数据库数据的一致性是非常重要的。在更新数据库数据时，需要及时更新缓存，或者设置合适的过期时间，以确保下次访问时可以回源获取最新的数据，回源数据的过程也需要考虑并发安全等。

缓存击穿
当某个热点数据突然失效，且有大量并发请求同时访问该数据时，可能导致大量请求直接打到数据库上，引起数据库压力剧增。可以采用互斥锁、预先加载等方式来避免缓存击穿。

缓存雪崩
当多个缓存数据同时失效，导致大量请求直接打到数据库上，从而引起数据库压力剧增。可以通过设置不同的失效时间、使用分布式缓存、限流等方式来避免缓存雪崩。

缓存穿透
查询一个不存在的数据，导致每次请求都会直接访问后端系统，从而引起后端系统负载过高。为了避免缓存穿透，可以采用布隆过滤器、缓存空对象等方式。

缓存大小和淘汰策略
需要考虑缓存的大小限制以及缓存淘汰策略，以确保缓存系统不会占用过多内存或存储资源，同时能保证高命中率。

缓存访问压力
缓存系统本身也可能成为性能瓶颈，需要考虑合理配置缓存服务器的数量、性能以及负载均衡策略，以满足高并发访问需求。

热点数据处理
对于热点数据，需要考虑缓存预热、预加载等策略，以及合适的缓存失效策略，确保热点数据的及时更新。

3、数据清理

何时考虑数据归档？

当表的数据出现冷热现象的时候，可以对数据进行冷热隔离，把更新和查询都不频繁的数据放到历史表或者是离线数仓里，减少表中的数据量来提升效率，这个方案也叫数据归档。

数据归档方案简介

确定归档策略：

定义归档的目的和需求：明确为什么需要进行数据归档以及归档后的数据如何使用。
确定归档的数据范围：根据业务需求，确定需要归档的数据类型、表或者文件。
设置归档频率：确定归档操作的时间间隔，例如每日、每周或每月执行一次归档任务。
确定数据保留期限：根据法规要求和业务需求，定义归档数据需要保留的时间期限。

备份归档数据：

在执行归档操作之前，对即将归档的数据进行备份，确保数据的安全性。
使用数据库备份工具或者文件系统级别的备份工具来创建备份副本。
将备份数据存储在可靠的位置，确保能够在需要时进行恢复。

创建归档表或文件：

根据归档的数据类型，创建相应的归档表或者文件。如果是数据库数据归档，可以在同一数据库实例中的不同 schema，或者在独立的归档数据库中创建归档表。
归档表的结构应该与原始表保持一致，可以添加额外的字段来标记数据的归档信息。
如果是文件归档，可以根据需要创建独立的文件夹或者存储位置来存放归档数据。

编写归档脚本或程序：

根据归档策略，编写相应的归档脚本或者程序来执行归档操作。
脚本或程序应包括以下主要步骤：
1）查询待归档的数据：根据归档策略，编写查询语句或者逻辑来获取待归档的数据。
2）将数据插入到归档表中：根据归档策略，使用 INSERT 语句或者逻辑将数据插入到归档表中。可以使用批量插入的方式来提高效率。
3）删除原始表中的归档数据：在将数据插入到归档表之后，使用 DELETE 语句或者逻辑来删除原始表中的归档数据。
4）更新归档标记字段：在完成归档操作后，更新原始表中的归档标记字段，以便后续查询和管理。

定时执行归档任务：

根据归档策略，设置定时任务来执行归档操作。可以使用数据库自带的定时任务功能（如 MySQL 的事件调度器），也可以使用操作系统的定时任务工具（如 crontab）来定期执行归档脚本或程序。
确保在执行归档操作期间，对数据库和系统资源进行监控，以确保归档操作不会对其他业务造成负面影响。

监控归档进度：

在执行归档操作期间，监控数据库的性能指标，例如 CPU、内存和磁盘使用率等，以确保归档操作不会对数据库的正常运行产生过大影响。
使用数据库监控工具或者日志记录来跟踪归档操作的进度和性能表现，及时发现并解决潜在问题。

清理归档数据：

根据归档策略中定义的数据保留期限，定期进行归档数据的清理工作。
可以根据删除标记字段或者数据的时间戳来判断是否需要清理归档数据。
清理操作可以是物理删除，也可以是将数据迁移到长期存储设施，如磁带库或云存储。

测试和验证：

完成归档操作后，进行全面的测试和验证，确保归档操作达到了预期的效果，并且没有引起其他问题。
进行性能测试、功能测试和回归测试等，以验证归档数据的完整性和可用性。

何时考虑数据删除？

当表的数据量巨大且都是无用数据的时候，可以考虑直接把数据删除。

数据删除方案简介

在执行删除操作之前，务必仔细评估删除数据对业务的影响，确保不会误删重要数据。另外，删除操作可能导致数据库事务日志膨胀，需要在合适的时间段进行操作，以减少对其他业务的干扰。如果删除数据的表中存在约束关系，需要确保删除操作不会破坏约束关系。

备份数据：在执行删除操作之前，务必对即将删除的数据进行备份。这样可以在意外情况下恢复数据。备份可以通过数据库备份工具或手动备份完成。如果数据量较大，可以考虑采用增量备份的方式，减少备份耗时。
确定删除条件：仔细审查业务需求，确定要删除的数据范围和条件。通常可以使用 SQL 的 DELETE 语句结合 WHERE 子句来指定删除条件，以确保只删除目标数据。还可以使用 ORDER BY 和 LIMIT 子句来控制删除的顺序和数量。例如：

 DELETE FROM your_table WHERE condition ORDER BY id LIMIT 1000;

上述 SQL 语句将按照id升序删除满足条件的前 1000 条数据。如果需要按照其他字段排序，可以将 ORDER BY 后面的字段改成目标字段。

分批删除数据：如果需要删除大量数据，建议采用分批删除的方式，以避免一次性删除操作对数据库性能造成严重影响。可以结合使用 LIMIT 子句限制每次删除的行数，例如每次删除 1000 条数据。同时，在每个批次之间需要留出足够的时间来让数据库处理其他请求，以避免锁定其他事务。
监控删除进度：在执行删除操作期间，应该监控数据库的性能指标，确保删除操作不会对其他业务产生负面影响。特别是在生产环境中，需要格外谨慎。可以通过数据库性能监测工具、日志记录等方式进行监控。如果发现删除操作对性能造成了较大影响，可以考虑暂停删除操作并进行优化。
清理垃圾数据：删除数据后，可能需要进行一些清理工作，例如重新构建索引、更新统计信息、释放空间等，以确保数据库性能得到有效提升。可以通过数据库维护工具完成这些操作，也可以手动执行相应的 SQL 语句。
测试和验证：删除数据后，务必进行全面的测试和验证，确保删除操作达到了预期的优化效果，并且未引起其他问题。测试和验证可以包括性能测试、功能测试、回归测试等等。

4、ES

何时考虑引入ES？

当数据量很大，搜索条件比较复杂比如模糊配置 like ‘%×××’ 等情况出现或者搜索条件灵活多变，SQL 查询比较慢的时候，此时就可以考虑使用 ES 来代替 MySQL 执行检索功能。

MySQL 架构天生不适合海量数据查询，它只适合海量数据存储，但无法应对海量数据下各种复杂条件的查询，有人说加索引不是可以避免全表扫描，提升查询速度吗，为啥说它不适合海量数据查询呢，有两个原因：

加索引确实可以提升查询速度，但在 MySQL 中加多个索引最终在执行 SQL 的时候它只会选择成本最低的那个索引，如果没有索引满足搜索条件，就会触发全表扫描，而且即便你使用了组合索引，也要符合最左前缀原则才能命中索引，但在海量数据多种查询条件下很有可能不符合最左前缀原则而导致索引失效，而且我们知道存储都是需要成本的，如果你针对每一种情况都加索引，以 innoDB 为例，每加一个索引，就会创建一颗 B+ 树，如果是海量数据，将会增加很大的存储成本，之前就有人反馈说他们公司的某个表实际内容的大小才 10G, 而索引大小却有 30G！这是多么巨大的成本！所以千万不要觉得索引建得越多越好。并且索引越多数据库的写入性能也会收到影响。
有些查询条件是 MySQL 加索引都解决不了的，比如我要查询商品中所有 title 带有「格力空调」的关键词，如果你用 MySQL 写，会写出如下代码

SELECT * FROM product WHERE title like '%格力空调%'

这样的话无法命中任何索引，会触发全表扫描，而且你不能指望所有人都能输对他想要的商品，是人就会犯错误，我们经常会犯类似把「格力空调」记成「格空调」的错误，那么 SQL 语句就会变成：

SELECT * FROM product WHERE title like '%格空调%'

这种情况下就算你触发了全表扫描也无法查询到任何商品，综上所述，MySQL 的查询确实能力有限。

ES 简介

与其说上面列的这些点是 MySQL 的不足，倒不如说 MySQL 本身就不是为海量数据查询而设计的，MySQL是关系型数据库，它在表现数据间的关系还是很可以的，而检索数据的能力就相对贫瘠，尤其是非精准的数据检索。术业有专攻，海量数据查询还得用专门的搜索引擎，这其中 ES 是其中当之无愧的王者，它是分布式的搜索分析引擎，可以提供针对 PB 数据的近实时查询，广泛用在全文检索、日志分析、监控分析等场景。

它主要有以下三个特点：

轻松支持各种复杂的查询条件: 它是分布式实时文件存储，会把每一个字段都编入索引（倒排索引），利用高效的倒排索引，以及自定义打分、排序能力与丰富的分词插件等，能实现任意复杂查询条件下的全文检索需求。
可扩展性强：天然支持分布式存储，通过极其简单的配置实现几百上千台服务器的分布式横向扩容，轻松处理 PB 级别的结构化或非结构化数据。
高可用，容灾性能好：通过使用主备节点，以及故障的自动探测与恢复，有力地保障了高可用。

那么 ES 中的索引为何如此高效，能在海量数据下达到秒级的效果呢？它采用了多种优化手段，最主要的原因是它采用了一种叫做倒排索引的方式来生成索引，避免了全文档扫描，那么什么是倒排索引呢，通过文档来查找关键词等数据的我们称为正排索引，返之，通过关键词来查找文档的形式我们称之为倒排索引。
在这里插入图片描述
要在其中找到含有 comming 的文档，如果要正排索引，那么要把每个文档的内容拿出来查找是否有此单词，毫无疑问这样的话会导致全表扫描，那么用倒排索引会怎么查找呢，它首先会将每个文档内容进行分词，小写化等，然后建立每个分词与包含有此分词的文档之前的映射关系，如果有多个文档包含此分词，那么就会按重要程度即文档的权重将文档进行排序，于是我们可以得到如下关系：
在这里插入图片描述
这样的话我们我要查找所有带有 comming 的文档，就只需查一次，而且这种情况下查询多个单词性能也是很好的，只要查询多个条件对应的文档列表，再取交集即可，极大地提升了查询效率。

除了倒排索引外，ES 的分布式架构也天然适合海量数据查询，来看下 ES 的架构：
在这里插入图片描述
一个 ES 集群由多个 node 节点组成，每个 index 是以分片（Shard，index 子集）的数据存在于多个 node 节点上的，这样的话当一个查询请求进来，分别在各个 node 查询相应的结果并整合后即可，将查询压力分散到多个节点，避免了单个节点 CPU，磁盘，内存等处理能力的不足。

另外当新节点加入后，会自动迁移部分分片至新节点，实现负载均衡，这个功能是 ES 自动完成的，对比一个下 MySQL 的分库分表需要开发人员引入 Mycat 等中间件并指定分库分表规则等繁琐的流程是不是一个巨大的进步？这也就意味着 ES 有非常强大的水平扩展的能力，集群可轻松扩展致几百上千个节点，轻松支持 PB 级的数据查询。

当然 ES 的强大不止于此，它还采用了比如主备分片提升搜索吞率，使用节点故障探测，Raft 选主机制等提升了容灾能力等等，这些不是本文重点，读者可自行查阅，总之经过上面的简单总结大家只需要明白一点：ES 的分布式架构设计天生支持海量数据查询。

5、读写分离

什么是读写分离？

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

何时考虑读写分离？

如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步。可以减少数据库压力，提高性能。

为什么要读写分离呢？

因为数据库的“写”（写10000条数据到 MySQL 可能要3分钟）操作是比较耗时的。
但是数据库的“读”（从 MySQL 读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。
一台主、多台从，主提供写操作，从提供读操作，读取压力大的时候可以考虑增加从库提升读取性能。

6、分库分表

什么是分库分表？

在这里插入图片描述
分表指的是在数据库数量不变的情况下对表进行拆分。
比如我们将 SPU 表从一张拆成四张。

分库指的是在表数量不变的情况下对数据库进行拆分。
比如我们一个库里面放了两张表，一张 SPU，一张 SKU，我们将两张表拆到不同的库里面去。

在这里插入图片描述
分库分表指的是数据库的数量还有表的数据都发生变更。
比如我们一个数据库里面有一张 SPU 表，我们把它拆成四张表，并且放到两个数据库里面。

拆分方式

在这里插入图片描述
表层面：
水平拆分指的是表结构不发生变更的情况下将一张表的数据拆分成多张表，因为当一张表的数据量越来越大的时候这张表的查询跟写入性能会越来越差，通过拆成多张表使每张表数据量变小，从而提供更好的读写性能。

垂直拆分指的是将一张表的字段拆分到多张表中，一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。

例如我们将 pic 字段单独拆分出来，剩下的三个字段还保留在原表里面，但是随着业务发展我们发现 pic 字段可能会越来越大，从而影响我们商品信息的查询，这个时候我们可以将 pic 字段单独拆分出去，一般都需要 id 关联到原来那张表上。

库层面：
库层面水平垂直拆分也是一样的，意义在于将数据均匀放更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。

为什么需要分库分表？

单台 MySQL 的硬件资源是有限的，随着我们业务发展，我们的请求量和数据量都会不断增加，数据库的压力会越来越大，到了某一时刻数据库的读写性能开始出现下降，数据库就会成为我们请求链路中的一个瓶颈，此时可能需要我们对数据库进行优化，在业务初期我们可能使用一些诸如增加优化索引、读写分离、增加从库的手段优化，随着数据量的增加，这些手段的效果可能变得越来越小，此时可能就需要分库分表来进行优化，对数据进行切分，将单库单表的数据控制在一个合理的范围内，以保证数据库提供一个高效的读写能力。

分库分表的优点

并发支撑：没分之前，单机部署，扛不住高并发；分了之后，承受的并发增加了多倍。
磁盘使用：没分之前单机磁盘容量几乎撑满；分了之后，磁盘使用率大大降低。
SQL 性能：没分之前单表数据量大，SQL 越跑越慢；分了之后单表数据量减少，SQL 效率提升。

何时进行分库分表？

数据库出现瓶颈通常有两个维度，第一个是单表出现瓶颈，一般是单表的数据量大导致表的读写性能都慢；另外一种数据库整体都出现瓶颈，一般表现为 QPS 过高，导致磁盘、IO、网络、CPU、内存负载过高或者表很多数据量很大，存储空间都几近于耗尽。

只出现表维度瓶颈就只分表，只出现库维度瓶颈就只分库，表和库维度的瓶颈都出现就既分库又分表。

分库分表需注意

分库分表的方案设计需要考虑分表字段、分表算法、全局ID的选择，还会带来跨表查询、跨表事务的问题，而且分库分表之后基本上也就告别了分页查询和排序，当然这些问题也有办法解决，不过还是强烈建议不到万不得已不要上分库分表，可以先尝试基本优化，建好索引、减少多表join、适量的字段冗余其实大多数情况是能扛得住的，其次是可以想办法减少数据库的压力，比如上一层缓存，把能接受延迟或者更新不那么频繁的放到分布式或者本地缓存中，第三是冷热数据的隔离，把更新和查询都不频繁的数据放到历史表或者是离线数仓里，减少表中的数据量来提升效率，这个方案也叫数据归档；第四个是考虑上分布式数据库。
总之就是优化、缓存、归档先尝试一遍都搞不定且没有预算上分布式数据库的话再考虑分库分表。

总的来说就是数据库读写性能出现瓶颈，通过其他手段已经没办法很好解决了，这是最终的解决手段，优先使用其他手段。数据达到千万级别就分库分表是不准确的，需要结合情况分析，千万这个数字只是一个参考。分库分表应当以未来3-5年的业务情况评估，不能只以当前数据业务量评估，否则可能会出现频繁的分库分表，因为分库分表的代价是比较大的，所以最好是充分评估，能支撑未来3-5年的增长。