sql调优

慢查询 SQL 治理方案

一、SQL 性能下降的原因

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

1. 索引失效
2. 查询数据库没做条数限制
3. 表的数据量过大
4. 数据库负载过高，读多写少，写操作效率极低
5. 数据库整体出现瓶颈，QPS 过高，磁盘、IO、网络、CPU、内存负载过高或者表很多数据量很大，存储空间都几近于耗尽
6. 深分页
   …

二、慢查询优化思路

1、索引失效

一般大家都知道给表加索引，没加索引的情况就得反省反省自己是否偷懒了，比较可能出现的情况是即便加了索引却仍然没有命中你期望走的索引。

确认你期望命中的索引是否比当前的好？

首先你得确认你期望命中的索引是最优解，否则按照你期望的方向优化很可能最后 SQL 越来越慢。

方法一

可以在 Archery 的 SQL 查询里的在线查询通过执行

 show index from 表名

来查看索引的信息，其中的 Cardinality 字段是索引的基数，通过基数可以间接知道索引的选择性，因为 selectivity = cardinality / total_rows。选择性越高，说明索引列中的值越分散，索引对查询结果的过滤能力就越强，查询性能也就越优秀。反之，如果选择性低，则说明索引列中的值很少变化，索引对查询结果的过滤能力就越差，查询性能也会受到影响。

对于字符串索引还可以通过

select count(distinct leftIndex(列名, 索引长度))/count(*)

来计算前缀索引的区分度，长度越短区分度越高是最好的。

方法二

改写你的 SQL 在里面使用 force index 使其强制走你期望的索引，配合 explain 查看其执行计划是否按照预期的方向改进，比如 rows 是否变少了，Extra 的情况是否改善了，type 是否优化了等信息。

怎么知道索引有没有命中？

你可以在 Archery 慢查询日志对慢 SQL 查询其 explain 执行计划，看 key 字段中是否包含你希望命中的索引，看 key_len 字段是否符合你期望命中索引的长度，以及查看 type 和 Extra 是否符合你预期的连接类型和相关说明。

怎么定位是什么原因导致索引失效呢？如何优化？

索引没生效的根本原因是优化器进行索引选择的时候根据成本计算得出走你期望走的索引不是最优解，所以没有选择走你的索引。
以下是常见的导致索引失效的情况，你可以一一排查对号入座：

联合索引、字符串索引不满足最左前缀原则

原因：
1）联合索引或者字符串索引不满足从左到右的字段匹配，因为索引是有序结构，有序才能使其利用二分查找定位数据，所以某个字段或者字段某个部分有序的前提是其左边的数据有序的情况下。基于此，一些情况就会打破这个原则，>、<、between、like 等范围查找都会打破最左前缀原则，范围查找之后的字段无法匹配索引；
2）或者你查询过滤的字段是从联合索引的非首个字段开始匹配；
3）字符串最常见的就是 like ‘%×××%’ 了。
优化：
1）将范围查找的字段在联合索引中尽可能往后放，使得索引能被利用的长度尽可能长。
2）查询过滤的字段需按照联合索引从左到右的顺序，不能跳过不匹配左边的字段。
3）字符串模糊查询可以考虑使用索引覆盖+延迟关联进行优化，把一个查询拆分成父子两个查询，子查询先针对模糊查询字段的二级索引进行全索引扫描（毕竟一般like ‘%×××%’ 会造成全表扫描，既然已经是谷底了怎么走都是向上，全索引扫描还是比全表扫描快很多的，因为其 b+ 树一般会小很多，explain 多出来的子查询的 type 会显示为 index），把符合条件字段对应的主键 id 筛选出来，再根据 id 进行回表到主键索引把完整行数据查出来过滤。这个方法前提是模糊查询过滤之后的数据不太多，否则回表过多一样很慢。如果条件资源充足的情况下，这种模糊查询可以考虑放到 ES 上做。

索引的列上进行计算或者使用了函数或者隐式类型转换

原因：
索引列上进行计算和使用函数不走索引是因为，比如 where count = 1 优化器是知道找 count 字段等于 1 的行数据的，如果你改成 where count - 1 = 1 或者 sum(count) = 1 优化器是不知道你这个计算表达式或者函数的结果是多少的，因为索引保存的是字段原始值，表达式函数千变万化 MySQL 底层不会把所有情况都耦合在代码中的，这样过于臃肿，所以很简单的拐弯都可能让优化器不认你的索引。隐式类型转换也是一样的道理，它的底层是使用了 cast 函数。
优化：
把 SQL 里面的计算抽取到代码中提取进行，不要放到 SQL 中进行操作就好了，如果放到 SQL 中操作也别放到索引列，例如：id 列是索引列，where id = “1” 改成 where id = cast(“1” as signed int) 这样就能走索引了。

where 的 or 字段里面有一个是非索引列

原因：
or 里的一个字段不是索引列就得针对该索引列去进行全表扫描获取完整行数据来查看其该列字段是否满足，因为 or 的语义是只要有一个满足。
优化：
or 的字段能都加上索引就都加上，不能就别使用 or，考虑换成其他语法。

回表过多，常见于 select *、范围查询

原因：
查询了索引以外的字段，或者查询索引过滤剩下的数据过多，大量回表使得 MySQL 认为把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描得了，这样还就扫描一个索引而已。当 MySQL 发现通过索引扫描的行记录数超过全表的 10%-30% 时，优化器可能会放弃走索引，自动变成全表扫描。
优化：
SQL 里指定你仅仅需要的几个字段，不要搞一个 select * 把所有字段都拿出来，甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引。即使真的要回表到聚簇索引，那你也尽可能用 limit、where 之类的语句限定一下回表到聚簇索引的次数，就从索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。

not in 和 not exists

原因：
想象一下你手里有一本电话号码簿，上面按照姓名的字母顺序进行了排序。现在你要找出不在电话号码簿中的人的信息。如果你使用的是IN或EXISTS方式，你会从电话号码簿中按顺序逐个查找每个人，直到找到匹配的人或者遍历完整个电话号码簿。这样的话，你可以很快地找到匹配的人，并且基于字母顺序的排序，你也可以很好地利用索引。

但是，如果你使用的是NOT IN或NOT EXISTS方式，情况就不同了。你需要找到电话号码簿中不存在的人。如果你还是按顺序逐个查找每个人，那么当你找到一个匹配的人时，你就不能确定是否已经遍历完了整个电话号码簿，因为你只能确认这个人存在，而无法确认其他人不存在。所以你需要遍历整个电话号码簿，然后挨个对比查找的人，这样才能确定哪些人不存在。

换句话说，NOT IN和NOT EXISTS需要完整地遍历整个数据集，而无法利用索引进行快速查找。这就像你在电话号码簿中逐个查找人，而不是按姓名的字母顺序来查找。因此，这两种方式通常会导致查询性能下降，无法充分利用索引带来的优势。
优化：
使用 LEFT JOIN，可以使用 LEFT JOIN 操作来查找主表中不存在于子表中的行，然后根据需要进行过滤。例如，以下查询语句：

SELECT *
FROM table_a
WHERE a NOT IN (SELECT b FROM table_b)

可以优化为：

SELECT a.*
FROM table_a AS a
LEFT JOIN table_b AS b ON a.a = b.b
WHERE b.b IS NULL

这里使用了 LEFT JOIN 来连接两个表，并在 WHERE 子句中过滤出不匹配的行。

order by 失效

原因：
1）排序字段在多个索引中，无法使用索引排序。
2）查询主键之外的没有添加索引的字段，不会利用索引排序。
3）where 条件是范围查询时，范围查询字段会使其后字段的 order by 索引失效。
4）对同一联合索引字段排序的升降序不一致，无法利用索引排序。
优化：
避免出现上述情况即可。

大数据量复杂查询——ES 优化

当遇到慢 SQL 查询的情况时，索引也没法很好满足时可以考虑使用 Elasticsearch（ES）来进行治理。

定位可以使用 ES 治理的场景

数据量很大且复杂查询条件：当数据量庞大且查询条件较为复杂，如模糊匹配（例如使用LIKE ‘%×××%’）等情况时，ES可以更高效地处理这些查询需求。
非精确的数据检索：如果需要进行非精确的数据检索，如查询包含特定关键词的商品标题，MySQL的LIKE查询无法命中索引，而ES利用倒排索引能够有效地处理这类查询。
实时近似实时的查询需求：ES具备接近实时的查询能力，适用于需要快速响应的场景，如全文检索、日志分析和监控分析等。

慢 SQL 常见原因

全表扫描：当没有合适的索引或索引失效时，MySQL可能会执行全表扫描，导致查询变慢。
多个查询条件导致索引失效：MySQL在执行查询时只会选择成本最低的索引，如果查询条件涉及多个字段，且没有符合最左前缀原则的索引，可能导致索引失效。
数据量过大：MySQL的架构天生不适合海量数据查询，当数据量增大时，查询速度会明显下降。

使用 ES 进行优化

建立倒排索引：ES采用倒排索引的方式建立索引，能够高效地处理全文检索和非精确的数据检索。对于需要模糊匹配的查询条件，ES可以更快速地定位到满足条件的文档。

分布式扩展能力：ES是分布式的搜索分析引擎，支持水平扩展，通过简单的配置即可实现节点的横向扩容，轻松处理PB级别的数据。将查询压力分散到多个节点，提高查询效率。

高可用性和容灾性能：ES通过主备节点和故障自动探测与恢复机制，保障了高可用性。新节点加入后，自动迁移部分分片，实现负载均衡。ES的分布式架构设计天生适合海量数据查询，能有效应对数据量过大的情况。

示例

对于模糊匹配查询，ES 可以使用 match 或 term 查询实现全文检索，相比 MySQL 的 LIKE 查询更高效。
如果需要查询特定关键词的商品标题，ES 可以使用 match 查询，并利用分词插件进行分词处理，使得查询更准确和高效。
对于大数据量的查询，ES 可以通过增加节点来实现横向扩容，并使用分片机制将数据分布到不同的节点上，提高查询性能。
总之，ES 作为一款分布式搜索分析引擎，具备强大的查询能力和扩展性，能够优化慢 SQL 查询的问题。根据具体的场景和需求，通过建立倒排索引、分布式扩展以及保障高可用性和容灾性能等方式，可以有效地利用 ES 来提升查询效率和性能。

其他

这里要说的其他，可以总结为第十五种索引失效的情况：MySQL 优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。

针对这种情况，发现问题时再定点排查即可。

2、查询数据库没做条数限制

定位

通过 explain 查看 SQL 的执行计划发现扫描行数 rows 和 filter 计算之后得到返回的数据很多，或者通过 Archery 上 SQL 自带的返回行数信息可以看出一些 SQL 查询返回的数据量很大，比如几万条或者更多。

原因

没做好查询的数据量评估，没做条数限制比如分页、分批处理，导致查询扫描的行数过多，同时查出的所有数据还要解析拷贝到内存，几个类似的 SQL 执行下来很可能就把数据库的磁盘 IO、网络带宽、CPU、内存等资源耗尽了。

优化

加上分页的条件或者分批的条件，limit 和 offset，比如每次只取 5000 条行数据，直到取不够 5000 条数据为止；日期类范围查询可以按月或者合适的时间间隔进行分割去获取数据，减少每次查询返回的数据量。

3、表的数据量过大

定位

通过

select count(*) from `表明`

可以查看表的数据量

原因

表数据过多，查询时的磁盘 IO 就更频繁，需要查询更多的磁盘页。

优化

数据清理

判断是否可以清理数据？
当表的数据量巨大且这些数据的需求量很小甚至没有的时候，可以考虑直接把数据迁移到历史表中或者删除。
如何迁移数据？
可以通过写定时脚本，每天凌晨系统空闲的时候分批将数据迁移到新的表中，直至将所有需要迁移的数据迁移完毕。
如何删除数据？
在执行删除操作之前，务必仔细评估删除数据对业务的影响，确保不会误删重要数据。另外，删除操作可能导致数据库事务日志膨胀，需要在合适的时间段进行操作，以减少对其他业务的干扰。如果删除数据的表中存在约束关系，需要确保删除操作不会破坏约束关系。

1. 备份数据：

在执行删除操作之前，务必对即将删除的数据进行备份。这样可以在意外情况下恢复数据。

2. 确定删除条件：

仔细审查业务需求，确定要删除的数据范围和条件。通常可以使用 SQL 的 DELETE 语句结合 WHERE 子句来指定删除条件，以确保只删除目标数据。

3. 分批删除数据：

不能一次性删除所有数据，因为数据量太大可能会引发超时，而是应该根据ID分批删除，例如每次删除500条数据。

第一步查询一年前主键最大值和最小值，这是我们需要删除的数据范围：

SELECT
MIN(id) AS minId, 
MAX(id) AS maxId 
FROM biz_table 
WHERE create_time < DATE_SUB(now(),INTERVAL 1 YEAR)

第二步删除数据时不能一次性全部删掉，因为很可能会超时，我们可以通过代码动态更新endId进行批量删除操作：

DELETE FROM biz_table 
WHERE id >= #{minId}
AND id <= #{maxId}
AND id <= #{endId}
LIMIT 500

同时，在每个批次之间需要留出足够的时间来让数据库处理其他请求，以避免锁定其他事务。

4. 监控删除进度：

在执行删除操作期间，应该监控数据库的性能指标，确保删除操作不会对其他业务产生负面影响。特别是在生产环境中，需要格外谨慎。可以通过数据库性能监测工具、日志记录等方式进行监控。如果发现删除操作对性能造成了较大影响，可以考虑暂停删除操作并进行优化。

5. 清理垃圾数据：

删除数据后，可能需要进行一些清理工作，例如重新构建索引、更新统计信息、释放空间等，以确保数据库性能得到有效提升。可以通过数据库维护工具完成这些操作，也可以手动执行相应的 SQL 语句。

6. 测试和验证：

删除数据后，务必进行全面的测试和验证，确保删除操作达到了预期的优化效果，并且未引起其他问题。测试和验证可以包括性能测试、功能测试、回归测试等等。
删除过于缓慢怎么优化？
切割好要输出的数据，根据情况在系统空闲的时候比如凌晨，通过跑定时脚本去删除数据，只要保证时间间隔内删除的数据比业务新增的数据多就能保证数据是在减少的，根据具体情况进行分析，确定删除数据的频率和每次删除数据的量，保证不会影响到正常业务。

分表

分表指的是在数据库数量不变的情况下对表进行拆分。
比如我们将 SPU 表从一张拆成四张。
水平拆分
将一个大的数据表按照某种规则进行拆分，将不同的数据行分散到多个小表中存储。
竖直拆分
将一个大的表按照字段或功能模块进行拆分，将不同的字段或模块划分到不同的数据表中存储。

没分之前单表数据量大，SQL 越跑越慢；分了之后单表数据量减少，SQL 效率提升。

4、数据库负载过高，读多写少，写操作效率极低

定位

可以通过 Archery 查看数据库的读操作执行的 SQL 特别多，而写操作执行的 SQL 较少出现但是却非常地耗时。

原因

在业务系统中，当面临大量的读操作，且读操作对数据库的压力增大，造成写操作的效率降低。

优化

读写分离

如果读操作对一致性要求没那么高的话可以考虑采用读写分离进行优化，把读操作和写操作分开，让所有读的请求落到专门负责读的数据库上，所有写的操作落到专门负责写的数据库上，写库的数据同步到读库上，这样保证所有的数据修改都可以在读取时，从读库获得。

增加从库

当读请求的压力越来越大读操作执行的 SQL 越来越慢的时候，可以通过增加从库来提升读操作的性能。

5、数据库整体出现瓶颈，硬件资源几乎耗尽

定位

数据库的 QPS 过高，磁盘、IO、网络、CPU、内存负载过高或者表很多数据量很大，存储空间都几近于耗尽，而且通过其他优化手段都无法很好地提升数据库的性能了。

原因

单台机器的资源有限，如果大量的请求频繁地打到同一台机器上则会使其资源消耗很大。

优化

分库

分库指的是在表数量不变的情况下对数据库进行拆分，将不同的数据表分散到多个数据库中存储，每个数据库独立管理一部分数据。
比如我们一个库里面放了两张表，一张 SPU，一张 SKU，我们将两张表拆到不同的库里面去。
并发支撑：没分之前，单机部署，扛不住高并发；分了之后，承受的并发增加了多倍。
磁盘使用：没分之前单机磁盘容量几乎撑满；分了之后，磁盘使用率大大降低。

6、深分页

定位

通过 SQL 语句比较容易看出。

原因

limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。这里需要回表100010次，大量的时间都在回表这个上面。

优化

索引覆盖+延迟关联

此优化方案和上文提到的字符串的 like ‘%×××%’ 的优化方案很像，方案核心思路是事先知道要从哪个主键ID开始，减少回表的次数。
拆分出一个子查询利用索引覆盖先根据查询条件全索引扫描查出符合条件的数据行主键ID，这个子查询的执行计划的 type 会显示为 index 表示全索引扫描，Extra 显示为 using index 表示索引覆盖，然后主查询再根据符合条件的行 ID 关联到主键索引上查出完整数据，这样就能减少无用的回表次数。

标签记录

不需要跨页查询的情况下可以使用此方法。标记一下上次查询到哪一条了，把 ID 记下来，下次再来查的时候，从该条数据的 ID 开始往下扫描。类似书签的作用。