【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?-02

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: 【4月更文挑战第13天】该文介绍了几个数据库查询优化技巧。首先,创建覆盖索引如<A,B,C>能加速`select A,B,C from student where A=? and B=? and C=?`的执行。其次,为常用于排序的列建立索引,如在`id,update_time`上建索引,可避免数据排序,显著提高查询速度。优化`count(*)`可通过预估值或使用Redis记录总数,但需注意数据一致性问题。使用索引提示如FORCE INDEX可强制使用特定索引,但应谨慎。将`having`的非聚合条件移到`where`里可提升效率。最后,处理深度分页时

优化案例

覆盖索引

比如执行最多的语句是select A,B,C from student where A=? and B=? and C=?

可以直接考虑创建一个<A,B,C>组合索引。对于这个SQL来说,这个组合索引就是一个覆盖索引,避免了回表。

优化order by

查询一些数据后,都要求对数据做一定的排序,比如按update_time排序,需要将排序的列加入索引。

比如select * from student where id = 1 order by update_time这个语句,如果用户的数据比较多,这个语句执行的速度比较慢,可以在<id,update_time>上创建一个新的索引。因为在id确定以后,索引内的update_time就是有序的了,避免了再次排序的消耗,可以将查询时间从秒级降到10ms级

在所有排序场景里,都尽量使用索引来排序,这样能够有效减轻数据库的负担,加快响应速度。

优化count

select count(*)是一个很常用的计算总数的语句。但是InnoDB引擎并没有存储数据总数,就导致类似的语句执行起来特别慢。

优化count一般有两种思路

  1. 用估计值取代精确值:如果这个场景对数据的准确性不高的话,可以借助EXPLAIN返回的预估行数来拿到一个预估值。

  2. 如果需要精确值的话,可以考虑使用Redis之类的NoSQL来直接记录总数,或是直接有一个额外的表来记录总数。

    如果用Redis来维持总数的话,会涉及到数据一致性的问题。如果插入数据库失败,但是更新Redis的总数失败了怎么办?

    • 如果数据只是短时间不一致,而且这个不一致业务可以接受的话,可以考虑异步刷新Redis的总数

    • 使用Cancel之类的工具监听binlog,刷新Redis的总数

索引提示优化

在实际工作的时候,如果有的时候数据库的执行比较奇怪,不用索引或用了错误的索引,就可以考虑通过FORCE INDEX、USE INDEX 和 IGNORE INDEX 这些索引提示来纠正数据库的行为。但是这个本身并不是什么好的实践,还是要谨慎。

where替换having

一般来说,数据库都是先根据where条件找到候选的列,再根据having条件进行二次过滤。如果将having的部分条件提前到where里,就可以提前把不符合条件的数据过滤掉了。

规律:如果不是使用聚合函数来作为过滤条件,最好还是将过滤条件优先写到where里。

SQL执行顺序相关问题

深度分页问题优化分页的偏移量

有一些SQL在在不断执行中会产生极大的偏移量,比如文章分页,一页50条数据,当要拿101页的数据,需要写成LIMIT 5000,505000就是偏移量。实际执行的时候,数据库需要读出5050条数据,然后把前面的5000条都丢掉,只保留50条。

优化思路是使用小偏移量。比如在原先的查询语句加where id > max_id的条件,这个max_id就是上一批的最大ID,这样可以保证LIMIT的偏移量永远是0。很多时候因为测试环境数据量小,这种性能问题很难被发现,所有使用分页的查询都应该考虑引入类似的查询条件。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
10天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
11天前
|
存储 SQL API
探索后端开发:构建高效API与数据库交互
【10月更文挑战第36天】在数字化时代,后端开发是连接用户界面和数据存储的桥梁。本文深入探讨如何设计高效的API以及如何实现API与数据库之间的无缝交互,确保数据的一致性和高性能。我们将从基础概念出发,逐步深入到实战技巧,为读者提供一个清晰的后端开发路线图。
|
12天前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
30 4
|
11天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
|
11天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
26 1
|
13天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
29 4
|
11天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化(可选)。通过这些步骤,你可以成功安装并初步配置 SQL Server 2019,进行简单的数据库操作。
|
20天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
92 1
|
22天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
64 2
|
22天前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
16 2
下一篇
无影云桌面