网站服务器多少钱一年,wordpress提示框插件,外贸网站分类,安论坛网站建设当项目的SQL查询慢得像蜗牛爬行时#xff0c;用户的耐心也在一点点被消耗#xff0c;作为研发#xff0c;我们可不想看到这样的事。这篇文章将结合行云部署项目的实践经验#xff0c;带你走进SQL优化的奇妙世界#xff0c;一起探索如何让那些龟速的查询飞起来#xff01;…当项目的SQL查询慢得像蜗牛爬行时用户的耐心也在一点点被消耗作为研发我们可不想看到这样的事。这篇文章将结合行云部署项目的实践经验带你走进SQL优化的奇妙世界一起探索如何让那些龟速的查询飞起来
序章EXPLAIN - 揭开查询的神秘面纱
EXPLAIN命令是数据库管理员和SQL开发人员的一项强大工具它可以帮助理解MySQL如何执行特定的查询。它显示了MySQL执行查询的详细信息包括如何连接表以及连接的顺序是否使用了索引以及每个表的读取行数等。通过这些信息你可以判断查询性能瓶颈并对查询或表结构进行相应的优化。
使用EXPLAIN的常见列解释
•id查询的标识符如果是复杂查询会有多个id数字越大优先级越高。
•select_type查询的类型比如SIMPLE简单的SELECT查询SUBQUERY子查询中的第一个SELECTDERIVED派生表的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显示索引查找使用了哪些列或者常量。
•rowsMySQL预估的返回请求数据需要扫描的行数。
•filtered表示返回结果的行数占扫描行数的百分比。
•Extra包含不适合在其他列中显示的额外信息如“Using index”表示表示查询能够使用一个覆盖索引Covering Index来获取数据。
使用EXPLAIN的例子
假设我们有一个简单的查询
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 索引的创建与运用
我们需要在经常参与查询的列上创建索引
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 联合索引如何实现112
当查询中需要根据两个或更多的列来检索数据时联合索引显得尤为重要。它可以让数据库在多个列上同时进行高效的查找。注意联合索引的第一项无需再单独建立索引
CREATE INDEX idx_column1_column2 ON your_table (column1, column2);联合索引需要注意联合索引一般遵循最左匹配原则例如
CREATE INDEX idx_sys_app_group ON groups (system_name, app_name, group_name);#优化前 963ms
select * from groups where app_name testApp and group_name testGroup;#优化后 42ms
select * from groups where system_name test and app_name testApp and group_name testGroup;由此可以看出当查询group_name时必须带上联合索引的前两个列一起查询也就是最左匹配原则如果直接从联合索引的第二个字段开始查询的话可能会走全表扫描要小心这种112的情况
想要避免这种情况的话不使用SELECT * 或许是一个不错的方法
使用SELECT * 时可以看到查询走的全表扫描 如果只用app_name和group_name这俩创建了联合索引的列进行查询的话就可以走索引啦 第二章查询重写 - 用巧妙的笔触画出高效SQL
2.1 别让数据库“吃撑”告别SELECT *享受轻盈查询
在日常写代码的途中最好能够避免使用SELECT *在餐厅点餐时我们也不会把菜单上的菜都来一份使用SELECT *就像是点了一份满汉全席而你却只想吃其中几道。请明确告诉数据库你需要的数据以减轻它的负担。
如果表数据量很大又需要查所有数据的情况下可以先查出对应数据的主键id列表再根据id列表查询
2.2 给 GROUP BY 和 ORDER BY 减负
在使用GROUP BY或ORDER BY时请先确保涉及的列已经建立索引。此外避免在其中使用复杂的表达式或函数会影响查询速度。
#优化前 1840ms
SELECT app_name,group_name,COUNT(*) FROM groups GROUP BY CONCAT(app_name,-,group_name);#优化后 42ms
SELECT app_name,group_name,COUNT(*) FROM groups GROUP BY app_name,group_name;在使用group by分组时最好先用where条件过滤掉不需要的数据后再分组而不是分组后再用having筛选
#优化前 431ms
select * from groups group by app_name having app_name like jdos%;#优化后 122ms
select * from groups where app_name like jdos% group by app_name;2.3 大分页查询的优化赢在起跑线上
在处理大分页查询时使用传统的LIMIT OFFSET方法会先扫描offsetlimit行然后再丢弃掉前offset行再返回需要的limit行数据。而基于游标的分页则是将起跑线置于终点附近通过使用上一页最后一条记录的ID来避免OFFSET可以大幅提高分页的效率不过这种方式只适合滚动加载或者迭代查询的情况在需要跳页查询的情况下基本不太能使用。
#优化前 563ms
SELECT * from groups order by id limit 300000,100;#优化后 78ms
SELECT * from groups where id 976797 order by id limit 100;对于需要跳页的大分页的数据考虑不用一次查出所有数据可以先查出主键id再根据id列表查询详情
#优化后 72ms
SELECT id from groups order by id limit 300000,100;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单独去查详情表
-- 原始表
CREATE TABLE task (id INT,name VARCHAR(100),operator VARCHAR(64), detail VARCHAR(2000)
);-- 垂直分表
CREATE TABLE task (id INT,name VARCHAR(100),operator VARCHAR(64), detail_id INT
);CREATE TABLE task_detail (id INT,detail VARCHAR(2000)
);
3.2 水平分表各得其所
水平分表像是将一个超负荷的生产线拆分成几个并行的小生产线每条线都在做相同的事情但只处理一部分产品。这样可以大大减轻每条生产线的压力提高整体的处理能力。根据一定的规则将原表拆成几个表结构相同的表查询时根据一定的路由规则分配到对应的表里让每个表的数据都不会过于臃肿
-- 原始表
CREATE TABLE task (id INT,name VARCHAR(100),operator VARCHAR(64), detail VARCHAR(2000)
);-- 水平分表 按年份分表
CREATE TABLE task_2022 (id INT,name VARCHAR(100),operator VARCHAR(64), detail VARCHAR(2000)
);CREATE TABLE task_2023 (id INT,name VARCHAR(100),operator VARCHAR(64), detail VARCHAR(2000)
);
3.3 数据归档轻装前行
数据归档和水平分表类似是将基本不可能用到的数据移到备份表中对数据库来一次“断舍离”。举个例子现在数据库表删除数据时基本上都是逻辑删除当表里的数据非常多而且被删除的数据和还存在的数据差不多的时候就可以考虑将逻辑删除的数据移到备份表中这样不仅缩小了表的数据量还可以在查询的时候去掉对逻辑删除字段的筛选查询更快人一步。
结语持续的优化之路
优化SQL查询是一个动态且持续的过程它要求我们不断地进行监控、评估和调整。每一次微小的调优都有可能使数据库的查询速度显著提升。现在你已经了解了优化的相关知识准备好了吗是时候启动引擎让你的数据库和行云部署一样起飞了
讨论欢迎分享
大家在SQL优化方面还遇到过哪些有趣或棘手的场景呢请在评论区畅所欲言让我们一起学习、探讨和解决这些问题。相信大家的经验会为大家带来启发和帮助让我们共同进步成为SQL优化的高手
同时如果你有任何关于数据库优化的问题也可以在评论区提问我们也会尽力为大家解答。让我们互相学习共创美好未来
作者京东科技 孙航
来源京东云开发者社区 转载请注明来源
