开发者社区> javaedge> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

1 SQL查询优化

简介: 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
显示每个商品评论数

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL慢查询与优化
MySQL的慢查询,全名慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中应时间超过阈值的语句。
128 0
sql 查询优化小计
    好久没更博了,偷偷的抽时间写一下。     早上开始working的时候,发现一个页面加载很慢,经排查是昨天写的一条联合查询的sql导致的。于是着手优化!     首先想到的是在join的时候,减少表体积之后再进行关联,于是有了下面这种写法: (原sql) a join b on a.
727 0
SQL优化常用方法25
用索引提高效率
885 0
SQL优化常用方法11
用TRUNCATE替代DELETE
689 0
SQL优化常用方法13
计算记录条数
694 0
SQL优化常用方法14
用Where子句替换HAVING子句
823 0
SQL优化常用方法9
整合简单,无关联的数据库访问
908 0
SQL优化常用方法8
使用DECODE函数来减少处理时间
783 0
SQL优化常用方法5
WHERE子句中的连接顺序.
1041 0
+关注
javaedge
关注公众号:JavaEdge,后台回复面试,领取更多大厂求职资源。曾在百度、携程、华为等大厂搬砖,专注Java生态各种中间件原理、框架源码、微服务、中台等架构设计及落地实战,只生产硬核干货!
2303
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载