行云部署成长之路--慢SQL优化之旅 | 京东云技术团队

加油吧

​当项目的SQL查询慢得像蜗牛爬行时，用户的耐心也在一点点被消耗，作为研发，我们可不想看到这样的事。这篇文章将结合行云部署项目的实践经验，带你走进SQL优化的奇妙世界，一起探索如何让那些龟速的查询飞起来！
序章：EXPLAIN - 揭开查询的神秘面纱

EXPLAIN命令是数据库管理员和SQL开发人员的一项强大工具，它可以帮助理解MySQL如何执行特定的查询。它显示了MySQL执行查询的详细信息，包括如何连接表以及连接的顺序，是否使用了索引，以及每个表的读取行数等。通过这些信息，你可以判断查询性能瓶颈，并对查询或表结构进行相应的优化。
使用EXPLAIN的常见列解释：

•id：查询的标识符，如果是复杂查询，会有多个id，数字越大，优先级越高。
•select_type：查询的类型，比如SIMPLE（简单的SELECT查询），SUBQUERY（子查询中的第一个SELECT），DERIVED（派生表的SELECT）等。
•table：显示这一行的数据是来自哪个表的。
•partitions：如果查询涉及分区表，这一列显示分区的信息。
•type：显示连接类型，这是MySQL如何查找表中行的重要信息。性能由高到低排列 system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
•possible_keys：显示MySQL可能使用哪些索引来优化查询。
•key：实际使用的索引。如果没有使用索引，值是NULL。
•key_len：使用的索引的长度。较短的索引通常更优，因为它们占用更少的空间。
•ref：显示索引查找使用了哪些列或者常量。
•rows：MySQL预估的返回请求数据需要扫描的行数。
•filtered：表示返回结果的行数占扫描行数的百分比。
•Extra：包含不适合在其他列中显示的额外信息，如“Using index”表示表示查询能够使用一个覆盖索引（Covering Index）来获取数据。
使用EXPLAIN的例子：

假设我们有一个简单的查询：

1. EXPLAIN SELECT * FROM users WHERE name ='zhangsan';

复制代码

这将返回一个表，显示上面提到的各种列的信息。如果你看到type列是ALL，这意味着MySQL正在进行全表扫描。如果possible_keys列指出了可以使用的索引，而key列是NULL，这意味着MySQL没有使用索引，这就是创建索引或者优化语句来提升查询速度的一个机会。
如何基于EXPLAIN的结果进行优化：

1.避免全表扫描：如果type列是ALL，考虑添加索引来减少扫描的行数。
2.使用正确的索引：possible_keys和key列可以帮助你知道可能使用哪些索引以及实际使用了哪些索引。如果没有使用索引，或者使用了不正确的索引，你可能需要重新考虑索引策略。
3.索引覆盖扫描：如果Extra列包含“Using index”，这意味着查询可以仅通过索引来获取数据，这通常是性能最好的查询之一。
4.优化子查询：如果select_type是SUBQUERY，你可能需要优化子查询。
5.减少读取的行数：rows列告诉你MySQL预计要扫描多少行来执行查询。减少这个数字通常会提高查询性能。
通过深入理解EXPLAIN的输出并据此进行调整索引和语句，可以显著提高查询的性能。不过需要注意的是EXPLAIN只是预测查询执行计划，并不总是100%准确，实际执行时可能会有所不同。因此，优化是一个迭代的过程，需要结合实际的查询执行结果来进行。
第一章：索引 - 数据库的速度之翼

想象一下，你是一个图书管理员，面前摆着成千上万的书籍，但是没有任何目录或索引，你要如何找到想要的书籍呢。这就是没有索引的数据库的真实写照。索引是优化查询的第一步，它能够让数据库引擎像猎鹰一样迅速地找到它的猎物——也就是你需要的数据。
1.1 索引的创建与运用

我们需要在经常参与查询的列上创建索引：

1. CREATE INDEX idx_column ON table_name(column_name);

复制代码

1.2 索引的选择与剪枝

索引也并不是越多越好，再美味的食物，吃太多也会消化不良。每个额外的索引都会增加数据插入和更新时的负担，并且有些索引会干扰到数据库对选择索引的判断，导致查询变慢。所以，选择正确的索引和定期“剪枝”不必要的索引是至关重要的。
以下几种情况都是不合适建立索引的：
1.在WHERE条件中用不到的字段不需要索引
2.列里基本上都是重复数据的最好不要创建索引，比如逻辑删除字段deleted，只有0或1两个值
3.已经创建了联合索引的情况下基本不需要再单独创建索引
正好在近几天的优化中碰到了类似的问题：
在workflow表中有联合索引idx_status_type(status, apply_type)和索引idx_remind_deploy(has_remind_deploy)
我们可以看到这个下面这个sql完全达不到预期，简单的查询时间却来到308ms

用explain看一下执行计划：可以看到，这里数据库选择的index_merge这种方式，而表里的has_remind_deploy只有0和1两个值，导致效率反而比只用idx_status_type降低

此时，考虑去掉索引idx_remind_deploy，强制索引idx_status_type后，果然速度变快

再看一下执行计划，type成为了ref。查询资料发现：index_merge查询时，当一个索引包含大量重复的值时，MySQL需要合并更多的行，这可能导致大量的随机I/O操作，因为它需要从不同的索引中检索和合并行。这种随机I/O通常比连续的I/O（如单个索引扫描）更慢

1.3 联合索引：如何实现1+1>2

当查询中需要根据两个或更多的列来检索数据时，联合索引显得尤为重要。它可以让数据库在多个列上同时进行高效的查找。注意，联合索引的第一项无需再单独建立索引：

1. CREATE INDEX idx_column1_column2 ON your_table (column1, column2);

复制代码

联合索引需要注意：联合索引一般遵循最左匹配原则，例如

1. CREATE INDEX idx_sys_app_group ON groups (system_name, app_name, group_name);

复制代码

1. #优化前 963ms
2. select * from groups where app_name = 'testApp' and group_name = 'testGroup';

复制代码

1. #优化后 42ms
2. select * from groups where system_name= 'test' and app_name = 'testApp' and group_name = 'testGroup';

复制代码

由此可以看出，当查询group_name时，必须带上联合索引的前两个列一起查询，也就是最左匹配原则，如果直接从联合索引的第二个字段开始查询的话，可能会走全表扫描，要小心这种1+1 976797 order by id limit 100;[/code]

对于需要跳页的大分页的数据，考虑不用一次查出所有数据，可以先查出主键id，再根据id列表查询详情

1. #优化前 1840ms
2. SELECT app_name,group_name,COUNT(*) FROM groups GROUP BY CONCAT(app_name,'-',group_name);

复制代码

2.4 EXPLAIN的妙用，分析sql执行计划，选择最佳索引

明明app_name和wf_version都有索引，数据量也不是很大，为啥执行时间这么慢呢

用explain看下执行计划，发现用到了wf_version索引，但是由于需要判空会扫描572353行

优化一下sql语句，使索引能够走到app_name，查询速度来到了50ms

再看下查询计划，发现走app_name索引的话只需要扫描289行就可以了

查询的时候，最好能让索引落在能够筛掉最多数据的列上
2.5 JOIN和IN怎么都不走索引？编码集搞的鬼

不知道大家有没有遇到过join或者in的查询，明明应该走索引的情况下，数据库却一直宁愿全表扫描也不走索引，正好最近排查了一个类似问题，在这里分享一下。
下面的查询中，workflow和workflow_scale_down_pod表中都有apply_number这个索引，关联查询的时候明明只返回一条数据速度却非常慢，这里选择join查询进行演示，可以看到，在两表都有索引的情况下只返回一条数据也耗时1900ms

于是分析一下执行计划，发现右表workflow根本没走索引！甚至用上强制索引也不选择索引：

难道是mysql又在抽什么风了？更改语句，用apply_number筛选右表，强制走索引，发现扫描行数也大有问题，明明左表中只有一条数据，右表却扫描了771034行，能看出来只有like的部分走了索引

后面经过一段时间查找资料发现可能是编码集问题导致索引失效，于是排查两表的编码集，发现确实不一样，workflow用的是utf8而workflow_scale_down_pod用的是utf8mb4

转换一下编码后再join，分析一下执行计划，看样子终于对了

执行一下看看所需时间，发现来到了9ms，真是可喜可贺

2.6 VARCHAR类型不走索引

与2.5类似，在表字段为varchar类型，存储的数据是数字时，直接用int类型查也会导致不走索引，需要加上引号用String类型来查询
第三章：数据库设计 - 优化的基石

上面我们说完了查询方面的优化，接下来说一下对表整体的优化。设想你的数据库表是一座精心设计的高效工厂，每个表都是一个生产线，它们的设计直接影响着整个工厂的产出效率。垂直分表和水平分表是两种让生产线更高效的设计策略。
3.1 垂直分表：各司其职

垂直分表就像是对工厂的生产线进行专业化改造，将一个多功能生产线拆分成几个高度专业化的小团队，每个团队都只负责一部分任务。这样可以减少每次查询加载的数据量，从而提高效率。举个常用的例子：概览表和详情表，一般情况下用户只需要知道概览就可以了，当需要看某一条数据的具体情况时，再通过概览关联的详情id单独去查详情表

1. #优化后 42ms
2. SELECT app_name,group_name,COUNT(*) FROM groups GROUP BY app_name,group_name;

复制代码

3.2 水平分表：各得其所

水平分表，像是将一个超负荷的生产线拆分成几个并行的小生产线，每条线都在做相同的事情，但只处理一部分产品。这样可以大大减轻每条生产线的压力，提高整体的处理能力。根据一定的规则将原表拆成几个表结构相同的表，查询时根据一定的路由规则分配到对应的表里，让每个表的数据都不会过于臃肿

1. #优化前 431ms
2. select * from groups group by app_name having app_name like 'jdos%';

复制代码

3.3 数据归档：轻装前行

数据归档和水平分表类似，是将基本不可能用到的数据移到备份表中，对数据库来一次“断舍离”。举个例子：现在数据库表删除数据时基本上都是逻辑删除，当表里的数据非常多，而且被删除的数据和还存在的数据差不多的时候，就可以考虑将逻辑删除的数据移到备份表中，这样不仅缩小了表的数据量，还可以在查询的时候去掉对逻辑删除字段的筛选，查询更快人一步。
结语：持续的优化之路

优化SQL查询是一个动态且持续的过程，它要求我们不断地进行监控、评估和调整。每一次微小的调优都有可能使数据库的查询速度显著提升。现在，你已经了解了优化的相关知识，准备好了吗？是时候启动引擎，让你的数据库和行云部署一样起飞了！
讨论：欢迎分享

大家在SQL优化方面还遇到过哪些有趣或棘手的场景呢？请在评论区畅所欲言，让我们一起学习、探讨和解决这些问题。相信大家的经验会为大家带来启发和帮助，让我们共同进步，成为SQL优化的高手！
同时，如果你有任何关于数据库优化的问题，也可以在评论区提问，我们也会尽力为大家解答。让我们互相学习，共创美好未来！
作者：京东科技 孙航
来源：京东云开发者社区 转载请注明来源
​
来源:https://www.cnblogs.com/Jcloud/p/17952038
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！