项目上线后我是如何通过慢查询和索引让系统快起来的

简介: 最近对mysql的操作比较多一些,主要是项目上线以后,难免会有一些数据上的问题。开始的时候还主要由后端来处理,后面数据问题确实比较多,于是我就找后端要来服务器的账号密码,连上数据库顺便来看看数据的问题。

image.png


大家好,我是 那个曾经的少年回来了。10年前我也曾经年轻过,如今已步入被淘汰的年龄,但现在幡然醒悟,所以活在当下,每天努力一点点,来看看2024年的时候自己会是什么样子吧,2024年的前端又会是什么样子,而2024年的中国乃至全球又会变成什么样子,如果你也有想法,那还不赶紧行动起来。期待是美好的,但是更重要的是要为美好而为之奋斗付诸于行动。


1、前言


最近对mysql的操作比较多一些,主要是项目上线以后,难免会有一些数据上的问题。开始的时候还主要由后端来处理,后面数据问题确实比较多,于是我就找后端要来服务器的账号密码,连上数据库顺便来看看数据的问题。


image.png


周五使用人数达到了高峰,总共有5300人在使用,今天截图的时候是周六人数略有减少。


image.png


这是三个表数据比较大的表,目前大致运行两周的时间就已经很大了。


image.png


这是数据量最多的一张表,大致已经410W条记录了。


算是一个小小的系统,不算大,但是从目前数据量的增加来看,慢慢的数据量可能会越来越大。


2、mysql 索引


最开始项目刚上线的时候,因为没有数据,所以根本没什么感觉,突然某一天,就感觉到接口的响应时间明显的变慢了。但其实后端并没有什么线上的经验,所以我借机就要来了服务器的账号密码。基本上除了主键以外,没有加任何的索引。打到数据库上的查询就实打实的有一些慢了,(虽然这里使用了一主四从),四个从库相当于都是用来做查询使用的,但是在没有索引的情况下,真的有点慢了。我跟后端稍作沟通,我就准备直接在正式环境添加数据库表的索引了。


这是平常小程序里接口的返回时间记录。而且有时候根据访问人数的不同,偶尔有时候会到三秒到四秒。


image.png


3、打开慢查询记录开关


那么能否通过专业的工具去查看呢?首先我做的第一件事情便是,查看一下mysql的慢查询是否有打开,好家伙,还不错,竟然打开了。如果没开启可以开启一下:


// 查看慢查询日志是否开启  on为开启  off为关闭 默认是关闭的
show variables like 'slow_query_log';
// 设置是否开启慢查询日期记录
set global slow_query_log = on;    #开启
set global slow_query_log = off;   #关闭
// 查看慢查询的阈值(默认是10秒)
show variables like 'long_query_time';
// 如果想修改慢查询的阈值
// 阈值设置为1秒
set global long_query_time = 1;   
// 查看慢查询日志文件路径
show variables like 'slow_query_log_file';


如果慢查询记录log没有打开,可以参考一下这篇文章:juejin.cn/post/716761…


4、通过mysqldumpslow 查询慢查询sql


下面是常用的几个查询慢SQL的脚本语句


// 得到返回记录集最多的10条SQL:
mysqldumpslow -s r -t  10 /var/lib/mysql/slow.log
// 得到访问次数最多的10条SQL:
mysqldumpslow -s r -t  10 /data/mysql/slow.log
// 得到按照时间排序的前10条里面含有左连接的SQL:
mysqldumpslow -s t -t 100 -g "left join" /var/lib/mysql/slow.log
// 也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log | more //分页显示


执行后结果如下所示,一目了然


image.png


可以查看到第一个sql 平均耗时2.94s,这个sql不论在哪里使用都会感觉慢了。所以这个时候查看sql以后,可以使用explain + sql 在mysql客户端执行,查看执行计划


image.png


可以查看返回结果,我平常观察最多的几个字段便是 type、 rows、Extra、等字段。


如果你想详细了解 explain的执行计划,你可以访问如下链接来重点阅读: juejin.cn/post/716359…

5、直接添加索引


我简单可以总结为如下:


  • 1、join 后看表关联的字段


  • 2、where 后看查询条件的字段


  • 3、group by order by 后的 分组条件和排序条件


在有条件的时候,上述地方能加索引就加索引,但是通常一张表添加五个索引就算比较多的,因为如果一张表索引过多在其他地方,比如存储、添加、删除的时候都会重新整理索引,成本消耗会很大。


image.png


目前来说这种简单粗暴的方式,在几百万数据的量级完全解决了我的问题,这里展示了我随便找的一张表,里面添加了四个索引,这里完全可以用四个字段的普通索引即可,我这里当时为了验证联合索引或者叫复合索引就没改了,目前来看效果还是嘎嘎的香,随着数据量的增加我猜测索引会有调整。


6、重置慢查询日志


假如我们优化完毕了,正式环境重新部署了,我们想查看一下效果,比如想去查询一下慢查询的日志记录,但是之前的日志记录还在,这个时候我们应该怎么办呢?


// 通过rm直接删除慢查询日志记录文件
rm slow.log
// 然后记得要重置慢查询才会开启继续登录
// 在 mysql所在的linux服务器上执行
mysqladmin -uroot -p flush-logs slow
//或者在mysql数据库中执行
mysql> FLUSH LOGS;


重置后可查看slow.log是否重新生成。


7、注意事项


  • 尽量禁止使用 select * 进行查询:减少IO和传递压力等


  • 查询条件的类型尽量与数据库里的类型一致:不一致可能导致索引失效


  • group by 后如果不想排序 可以在后面添加order by null


  • 查询计划中尽量避免全表扫描


  • 每张表都要设置主键,因为不设置mysql会自动帮我们设置


  • 主键最好不要用GUID,尽量自增ID(GUID插入时时无序的)


  • 明确只返回一条记录的sql 可以加上limit 1


  • 联合索引(复合索引)查询时要注意查询字段的顺序


  • 如果可以尽量给字段设置默认值,不要为null空值,null在一定程度上会造成索引失效


  • like 模糊查询尽量不要以% 开头,因为会造成索引失效


  • 一个sql关联的表不要过多(通常最多三到五个)


  • 多表查询时一定先以小表查询,再来查询大表,也就是小表驱动大表


  • 尽量少用or会造成索引失效,有些时候可以使用union all替换


  • 当然还有其他的,暂时在项目使用就这么多


8、总结


一种情况时找到具体接口中使用的sql,如果很慢进行优化sql或者添加索引,另外一种时通过mysql工具查找到记录的慢查询sql,可以直接根据表结构进行添加索引,如果很复杂,而且简单的增加索引无法提速,可能要根据具体业务进行分析调整再添加索引。总之索引的使用在大部分情况下是非常有效的。 通过explain 查看sql执行计划,进行优化索引和表设计,因为在某些情况合理的表结构默认值设置、或者表关联字段设置,都能有效的避免全表扫描。 总之不要怂,加错了索引,大不了花点时间删除就好了。


我的个人博客:vue.tuokecat.com/blog


我的个人github:github.com/aehyok


我的前端项目:pnpm + monorepo + qiankun + vue3 + vite3 + 工具库、组件库 + 工程化 + 自动化


不断完善中,整体框架都有了


在线预览:vue.tuokecat.com


github源码:github.com/aehyok/vue-…

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
4月前
|
人工智能 自然语言处理 Java
Trae 3.0月活破500万:但为什么说Java开发者需要的不是代码补全,而是工程交付?
trae月活突破500万,,Solo Mode 3.0实现无人值守全流程编程。这是AI编程工具的一个里程碑。Trae 3.0的定位从"代码补全"升级到"全流程代理"——自然语言输入需求,AI自动完成编码、测试、部署全链路。它甚至可以在夜间和周末自主运行,批量处理多个项目。
|
4月前
|
数据采集 机器学习/深度学习 监控
跨境选品秘籍:API如何帮你分析全球价格差?
本文探讨如何利用API技术实现全球商品价格差的自动化分析,助力跨境电商精准选品。通过实时抓取多国平台价格数据,结合汇率、税费与库存等维度,识别利润蓝海、优化定价与供应链,并支持动态预警与可视化决策。数据驱动,让选品从“凭经验”迈向“看趋势”。(239字)
|
运维 小程序 BI
私域直播系统APP/小程序开发与搭建全流程详解
私域直播正在成为企业增长的新引擎,但如何选择靠谱的开发公司、以及APP与小程序该如何决策,成为关键问题。本文从行业视角出发,系统解析私域直播系统的核心价值、开发公司筛选标准,以及完整的系统搭建流程,帮助企业避坑、降本增效,实现直播带货的长期增长与私域沉淀。
|
7月前
|
数据可视化 机器人 Windows
Adams 2024安装与模型库配置教程 Windows版:License配置+自定义路径+27500端口指南
Adams 2024 是集建模、求解与可视化于一体的多体动力学仿真平台,支持机械、机电、流体等多领域系统级仿真,广泛应用于汽车、航天与机器人等领域。(239字)
|
6月前
|
关系型数据库 分布式数据库 数据库
RAG 不一定要外挂,数据库里就能原生实现
PolarSearch是PolarDB内置的智能搜索引擎,将全文检索、向量检索、结构化查询与大模型调用深度集成于同一数据库内核,实现RAG全流程“入库即向量化、查询即生成”,显著提升准确性、实时性与数据一致性,大幅降低架构复杂度与运维成本。
435 0
|
7月前
|
弹性计算 关系型数据库 数据库
阿里云老用户云服务器最新优惠政策完整解析
2026年,阿里云对老用户关怀体系进行全面升级,推出覆盖服务器生命周期的专属优惠政策,包括续费同价、阶梯折扣、弹性升级折扣、迁云补贴以及ECS与RDS组合优惠等,帮助老用户在稳定使用云服务的同时实现成本优化与业务升级。本文从续费、升级、迁移补贴、组合优惠四个维度,对2026年老用户专属政策进行系统梳理与说明。
644 1
|
7月前
|
前端开发 JavaScript
前端组件库——Tailwind CSS知识点大全(二)
教程来源 http://vbzcj.cn/category/tianqiyubao.html Tailwind CSS核心概念涵盖间距(0.25rem倍数)、多梯度颜色、移动优先响应式、交互状态变体、暗色模式(v4默认启用)、Flex/Grid布局及排版工具类;支持CSS变量主题定制与任意值扩展,兼顾一致性与灵活性。
|
9月前
|
人工智能 开发工具 开发者
2026年,让.NET再次伟大
2026年,“让.NET再次伟大”倡议推动.NET SDK预装入Windows及主流Linux——以单文件(.cs)即运行为核心,统一工具链、降低开发门槛、赋能AI原生应用,重塑开发者体验与软件分发范式。(239字)
625 5
|
前端开发 搜索推荐 JavaScript
如何通过DIY.JS快速构建出一个DIY手机壳、T恤的应用?
DIY.JS 是一款基于原生 Canvas 的业务级图形库,专注于商品定制的图形交互功能,帮助开发者轻松实现个性化设计。适用于 T 恤、手机壳等多种商品场景。它自带丰富功能,无需从零构建,快速集成到项目中。通过创建舞台、添加模型、定义 DIY 区域和添加素材四个步骤即可完成基础用法。支持在线演示体验,文档详细,易上手。
858 57
|
缓存 Dubbo Java
理解的Java中SPI机制
本文深入解析了JDK提供的Java SPI(Service Provider Interface)机制,这是一种基于接口编程、策略模式与配置文件组合实现的动态加载机制,核心在于解耦。文章通过具体示例介绍了SPI的使用方法,包括定义接口、创建配置文件及加载实现类的过程,并分析了其原理与优缺点。SPI适用于框架扩展或替换场景,如JDBC驱动加载、SLF4J日志实现等,但存在加载效率低和线程安全问题。
969 7
理解的Java中SPI机制