1 SQL查询优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: SQL语句优化对查询进行优化,要尽量避免全表扫描。在 where 或 order by 的列上加索引。尽量避免在 where 子语句中有 where num is null,这样不用索引,要全表扫描,可用 0 代替 null避免在 where 中用or!=,因为要全表扫描尽量避免在 where 中用 or,因为若一个字段有索引,一个没有,则要全表扫描like”%abc%”,全表扫描避免在 where 子语句中对字段进行函数操作,因为要全表扫描使用复合索引时,必须用到该索引的第一个字段,否则索引不被使用。

SQL语句优化

  • 对查询进行优化,要尽量避免全表扫描。在 where 或 order by 的列上加索引。
  • 尽量避免在 where 子语句中有 where num is null,这样不用索引,要全表扫描,可用 0 代替 null
  • 避免在 where 中用<>or!=,因为要全表扫描
  • 尽量避免在 where 中用 or,因为若一个字段有索引,一个没有,则要全表扫描
  • like”%abc%”,全表扫描
  • 避免在 where 子语句中对字段进行函数操作,因为要全表扫描
  • 使用复合索引时,必须用到该索引的第一个字段,否则索引不被使用。
  • 尽量避免在 where 子句使用 != 或 <> 操作符
    引擎将放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中对字段进行 null 值判断
    否则将导致引擎放弃使用索引而进行全表扫描
    如:select id from t where num is null
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=0
  • 很多时候用 exists 代替 in 是一个好的选择
  • 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

1. 获取有性能问题SQL的方法

  • 通过用户反馈(自然是不建议滴)
  • 通过慢查询日志(时间可能较长)
  • 实时获取

2.慢查询日志介绍

2.1 性能

开销较低,主要来自于磁盘I/O和存储日志所需磁盘空间,对于现代磁盘,主要问题就只在于所需的大量存储空间了

2.2 位置控制

  • slow_query_log 启动/停止记录慢查询日志(默认为off,手动配置文件on才能开启)


    img_7c87d5a6d59d748c0553585959f81141.png
    在运行的MySQL中,可通过set global启动

也可通过脚本定时控制

  • slow_query_log_file 指定慢查询日志的存储路径及文件(默认在数据目录)
    当然最好将日志/数据存储分开啦
  • long_query_time 指定记录慢查询日志SQL执行时间的阈值(默认单位s,可精确至ms)


    img_12434d118ea0a6e6a23cdc238e0c3afe.png

    默认值10s,通常改为0.001s即1ms较合适

  • log_queries_not_using_indexes 是否记录未使用索引的SQL

2.3常用日志分析工具

2.3.1 mysqldumpslow(MySQL官方自带)

img_31b5f4a34aa2d7b2fe72c6909c1b27fd.png

img_eff7e2bb5ae4c45c1aa2da7ca8c3eaa8.png

img_063b2d45e100ad56293aa5756d514c21.png

img_67823df1f3c2805c6a1ae67dd83dea29.png
实例查询

2.3.2 pt-query-digest(推荐使用)

img_d925b45086170062d06486cf98ddd6fe.png
生成日志服务器及日志文件名

img_09513241fcfecb1553b44d1d25aa679e.png
生成到slow.rep

执行vi slow.rep


img_a06967e01da3877aca4a61c9122b1109.png
提供信息远多于mysqldumpslow生成的

img_d6108fe92009a0939974e3b48d43ba0e.png

3. 实时获取

img_faf29208ab7aff515afc6f945bb5988a.png
通过此表

img_51ed0ebb1b1e95161a6d0df76a6c8bb5.png
实例

3.SQL的解析预处理及生成执行计划

3.1 查询速度为什么会慢

img_9790034dd0170a880bf95277e4d8daf9.png
MySQL服务器处理查询请求的整个过程

3.2 查询缓存对SQL性能的影响

img_b33abde91418741095df81f0a921fc85.png
Hash查找只能进行全值匹配

命中缓存,在返回结果前,MySQL会检查用户权限,查询无需被解析,看出缓存直接返回结果其实很不容易
如果缓存中结果正确的,每次缓存牵涉到表被更新,都要对缓存也进行刷新,如此即使是同一个sql语句即使对同一个表查询中不同不涉及的字段被更新,下次查询这个sql同样无法命中
此外每次在对缓存进行检查SQL是否命中时,都要对缓存加锁


img_291b264c0e324b21e1fe9b8e068e63ff.png
不建议使用查询缓存
  • query_cache_type 设置查询缓存是否可用(ON,OFF,DEMAND)


    img_33adca9da9a3d5bde2c75b7d2e7cb8da.png
  • query_cache_size 设置查询缓存的内存大小
  • query_cache_limit 设置查询缓存可用存储的最大值
    如果预先知道哦结果不会被缓存加上SQL_NO_CACHE可以提高效率
  • query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据(默认关闭)
  • query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

3.3 执行计划

img_cf9b11e16c3396153ab00c83e15884fe.png

img_47894b72d5ef44793790b09af8c948c4.png

img_3ebf40f974a4883144bd9db1a26bdc98.png

3.3.1 可能造成MySQL生成错误的执行计划的原因

img_c4520f452e96e487b95f9d7b26abbe0a.png

img_dca57d5176428ad3633ad079fa606c7c.png

img_37e4f0af07ad4b1a78b73a2d7202c060.png

3.3.2 MySQL优化器可优化的SQL类型

  • 重定义表的关联顺序


    img_cdb40ab703465108dc2c5812b45b8ab6.png
  • 将外连接转化为内连接
    如当有where条件和库表结构等会重写优化
  • 对一些过滤规则进行等价变换


    img_bea64374a040938ce11bd888a9ef4f19.png
  • 优化count(),min(),max()等聚合函数
    优化器会使用B+索引和列是否为null来优化
    所以直接选最左或者最右的记录即得min,max
    由此会在查询计划中看到如下信息


    img_5010fe40433c7fb17754b827e0d01663.png

    img_f7de1ae6cc6a2dc2cacf472f1a10277a.png
  • 将一个表达式转化为常数表达式
  • 等价变换规则
  • 子查询优化
    可能转为关联查询,减少表的查询次数
  • 提前终止查询
    发现已经满足查询条件时立即终止,特例如limit子句
    发现不成立条件,立即返回null


    img_3f532ec395cb85c0d62a1eb33f125dbc.png
    film table

    img_5a7cfa0d6c59572f50b559e8a8cc801c.png
    由于id定义为无符号类型,所以直接终止了查询,并无读取任何数据
  • 对in()条件进行优化
    对in列表的元素先进行排序,再通过二分查找确定

3.4 确定查询处理各个阶段所消耗的时间

3.4.1使用profile

set profile = 1;
启动profiel,这是一个会话级别的配置

  • 执行查询
  • show profiles;查看每一个查询所消耗的总时间的信息
  • show profile for query N;查询每个阶段所消耗的时间N为queryId
    img_c4c7820831a25b3f8170d40f29c01dc8.png

    img_ecb9f04cc6d66a70c94e162cf95ab927.png
    当执行 `show profile for query N;`后

    若想查看CPU信息执行以下
    show profile cpu for query 1;
    img_23f41ad80e466eb196e4c5ef8a113bdd.png

    img_a97cdd6f982438db19e3cfed098c6f01.png
    但是已经不被推荐使用

3.4.2使用performance_schema(MySQL5.6后默认开启)

img_850debf0dd49954054803cdc690f8969.png
启动监控项

img_d0d887f84966adfd8689dbef758101a1.png

img_ef3e927aaac7bad879cd29a5158f4927.png
上述执行结果

4 对特定SQL的查询优化

img_d6d4463fa399a556f2785296651f1574.png

img_c1f5e9d0dbf873ce1382658f990ec53b.png
一个存储过程实例

4.1如何修改大表的结构

img_872824afa553f1136e4cfc3e6d33c3a9.png

img_47c4673ee2c15dab33f51003bdc36cf1.png
主从方式

img_a01331d3b556e7197c19108a97cd9ec6.png
减少主从延迟,操作有工具加减单

img_cebd68ec72e2f21077e2771546859c91.png

img_347fccd4df522270b8a69492528675d2.png
数据示例表

img_20182f0163472c4d7e3c37a4964adb9e.png
alt语句

img_d0e9e751702fd08d290ea4b098a46972.png

img_60c8d082c4acfcee2f6a4c1459d67a6d.png
执行过程

img_3ae7415a06e460d5513dd6e21a3a0777.png
验证表确实被修改

4.2 如何优化not in和<>查询

img_5c79141e702ff42a14422538a964d69c.png
下面为优化sql

4.3 使用汇总表优化查询

img_6ce6bbad957f05bf54d1d1b217c3dfc2.png

img_b430e42c94effa4e62305b75220c2708.png
汇总表

img_478519a679c078f510bf0f02f7088e0b.png
显示每个商品评论数
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
1892
分享
相关文章
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
595 2
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
106 11
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
173 0
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
SQL慢查询优化策略
在数据库管理和应用开发中,SQL查询的性能优化至关重要。慢查询优化不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将详细介绍针对SQL慢查询的优化策略。
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化

热门文章

最新文章