网站建设需要的资质,wordpress谁开发的,dede酒业企业网站模板,vue做公司网站慢查询 SQL 治理方案
一、SQL 性能下降的原因
在对 SQL 进行分析之前#xff0c;需要明确可能导致 SQL 执行性能下降的原因进行分析#xff0c;执行性能下降可以体现在很多方面#xff1a;
查询语句写的烂索引没加好表数据过大数据库连接数不够查询的数据量过大被其他慢s…慢查询 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 是以分片Shardindex 子集的数据存在于多个 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年的增长。
