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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。

数据库优化

主要从下面的角度来进行

  • 硬件资源优化:增加机器配置

  • 操作系统优化:调整OS的某些设置

  • 服务器/引擎优化:比如调整事务隔离级别;调整InnoDB引擎的日志刷盘时机

  • SQL优化:针对SQL本身

SQL优化是最容易的一点优化,站在数据库的角度,SQL优化的目标是:

  1. 减少磁盘IO:尽量避免全表扫描、尽量使用索引、尽量使用覆盖索引

  2. 减少内存和CPU的消耗:尽量减少排序、分组和去重之类的操作

EXPLAIN命令

如果想知道优化后的效果,需要掌握一个工具,也就是EXPLAIN命令。用法是EXPLAIN my_sql,数据库就会返回一个执行计划。

比如select * from student where id = 1

执行计划比较关键的字段如下:

  1. type:查询到所需行需要的方式,从好到坏依次是system> const> eq_ref> ref> range> index> ALL

    • system/const可以理解为数据库只会返回一行数据,查询时间是固定的

    • eq_ref/ref可以理解为根据索引的值来查找

    • range 是索引范围扫描

    • index是索引全表扫描,扫描索引所在的B+树

    • ALL是全表扫描,如果表数据都在缓存就不需要磁盘IO

注意:索引的扫描里,索引如果不在缓存的话也需要磁盘IO。扫描二级索引比扫描全表(主键索引)的优势是二级索引一般只存储索引列和id,数据很小。性能从好到差分别是 根据主键取数据 > 根据普通索引取数据 > 索引全表扫描 > 全表扫描

  1. possible_keys:候选的索引

  2. key:实际使用的索引

  3. rows:扫描的行数

  4. filtered:查找到所需要的数据占rows的比例

设计索引列

  • 外键:一般用于关联、过滤数据,正常来说都会为表的外键创建索引

  • 频繁出现在where中的列,为了避免全表扫描

  • 频繁出现在order by的列,为了避免数据库在查询结构出来后再次排序

  • 频繁出现在关联查询的关联条件中的列。不过一般不建议使用关联查询

  • 区分度很高的列。比如每一行数据都不同的列,并且创建联合索引的时候,区分度很高的列应该尽可能放在左边。(最左匹配原则)

数据量大的表更改表结构

修改索引的时候,数据量大的表和数据量小的表修改索引的实际方案不一样。因为修改索引相当于表定义的变更,核心问题是数据库会加表锁,直到修改完成。如果这个表的数据很多,在执行加索引的命令时,整张表可能会被锁住几分钟甚至几小时。

所以数据量大的表结构变更是一件很复杂的事情,一般可以考虑的方案有三种:

  1. 停机变更:把整个业务停止,更新表结构。或是只把跟这个表有关的功能下线,不需要把整个服务或系统下线。

  2. 业务低峰期变更

  3. 创建新表,数据迁移

面试相关

准备一些SQL优化的案例

  • 维护的业务的所有表结构定义和索引定义,每个表上执行最频繁的三个SQL是否用到了索引

  • 之前的慢SQL是怎么发现、分析和优化的,记住SQL优化前后的执行时间

  • 深挖的一些方向

一些常见的问题 可以引导导SQL优化里

  • 是否做过性能优化

  • 接口的响应时间是多少 有没有优化的空间

  • 是否了解索引、用过索引

基本思路

基本思路就是将SQL优化作为全方面优化系统性能的一个措施。

xx系统是一个核心系统,对可用性和性能都有很高的要求,可用性要求是xxxx,性能要求是平均RT控制在100ms内。做了很多性能优化的事情,比如SQL优化。

根据SQL慢查询监控 -> 排查SQL问题 -> 通过EXPLAIN命令查看SQL的执行计划,看看有没有走索引、走了哪些索引、是否有内存排序、去重等操作 -> 尝试优化,包括改写SQL、修改/创建索引

弄清楚数据库和数据库所在的OS的参数的含义和值

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
16小时前
|
SQL 关系型数据库 MySQL
MySQL进阶-增删查改(全网最详细sql教学)-2
MySQL进阶-增删查改(全网最详细sql教学)
6 0
|
16小时前
|
SQL NoSQL 关系型数据库
MySQL进阶-增删查改(全网最详细sql教学)-1
MySQL进阶-增删查改(全网最详细sql教学)
6 0
|
18小时前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之sql读取mysql写入clickhouse,该如何操作
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
19小时前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之如何SQL同步数据到Oracle数据库中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
21小时前
|
SQL 消息中间件 关系型数据库
实时计算 Flink版产品使用合集之 sql采集mysql能拿到before的数据吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
SQL 关系型数据库 MySQL
MySQL中的12个SQL编写规范
SQL良好习惯提升查询清晰度、效率和安全性,包括使用EXPLAIN分析查询计划、DELETE/UPDATE时加LIMIT限制影响范围、为表和字段添加注释、关键字大写缩进、指定INSERT字段名、先测试后执行、表含主键及时间戳字段、Update/Delete需Where条件、用InnoDB引擎、避免SELECT *,选择UTF8字符集和规范索引命名。
MySQL中的12个SQL编写规范
|
1天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
14 2
|
1天前
|
存储 数据可视化 关系型数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
|
1天前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】SQL概述
【MySQL进阶之路 | 基础篇】SQL概述
|
6天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
82 0