查询优化

查询慢的原因

网络 CPU IO 上下文切换 系统调用 生成统计信息 锁等待

优化数据访问

查询性能低下的主要原因是访问的数据太多 需要避免检索、传输大量数据

访问了太多行和列

如果是由于访问太多行,则首先应避免写出这种SQL,MySQL会直接返回客户端所查询的全部数据,所以可以使用LIMIT来进行限制

访问了太多的列,则应该审视是否真的需要这些列,其次可以通过覆盖索引扫描的方式来进行优化。

MySQL扫描了过多的数据行

通过检查慢日志记录可以找出扫描行数过多的查询。

也可以通过EXPLAIN语句列出结果rows属性,理想情况下扫描的行和返回的行数量一致。

如果要对扫描行数与返回行数量相差较多的查询进行优化,主要可以通过

  1. 覆盖索引扫描的方式
  2. 改变表结构 使用单独的汇总表之类的
  3. 重写掉这个复杂的查询

重构查询

对于一些操作数据量大的SQL,如果可以将其拆分成几个小SQL,在应用层进行处理,那可以把服务器的压力分摊到各个时间点中。

如可以对一些关联查询进行分解:

  1. 可以利用缓存
  2. 降低锁争用
  3. 应用层处理拥有较高的可扩展性
  4. ...

执行过程优化

屏幕截图 2021-03-16 152127

查询优化处理

  • 计算的依据

每个表或者索引的页面个数 索引的基数 索引和数据行的长度 索引的分布情况

很多情况下mysql会选择错误的执行计划

  • 优化策略

    • 静态优化 直接对解析树进行分析,并完成优化
    • 动态优化 动态优化与查询的上下文有关,也可能跟取值、索引对应的行数有关
  • 优化类型

    • 重新定义关联表的顺序
    • 将外连接转化成内连接,内连接的效率要高于外连接
    • 使用等价变换规则,mysql可以使用一些等价变化来简化并规划表达式
    • 优化count(),min(),max()
    • 预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行处理
    • 索引覆盖扫描,当索引中的列包含所有查询中需要使用的列的时候,可以使用覆盖索引
    • 子查询优化
    • 等值传播
  • 排序优化

  • 优化特定类型查询

    • count 查询
      • 只有没有任何where条件的count(*)才是比较快的
      • 不需要完全精确的值,可以参考使用近似值来代替,比如可以使用explain来获取近似的值
      • 实际操作的时候可以考虑使用索引覆盖扫描,或者增加汇总表,或者增加外部缓存系统
    • 关联查询
      • 确保on或者using子句中的列上有索引
      • 确保任何的groupby和order by中的表达式只涉及到一个表中的列
    • 子查询优化
      • 使用关联查询替代
    • limit 分页优化
      • 优化此类查询的最简单的办法就是尽可能地使用覆盖索引,而不是查询所有的列
    • 优化union查询
      • 除非确实需要服务器消除重复的行,否则一定要使用union all
    • 使用用户自定义变量

使用索引

索引的原理:

屏幕截图 2020-08-27 093235

MySQL 使用索引的方式:

  • WHERE ORDER BY GROUP PY子句
  • 对于使用MIN MAX函数的查询直接使用索引就可完成
  • 对于某些查询 只使用索引的数据就可返回 无需回表查询

索引的代价:

  • 降低了大部分写操作的速度
  • 占用磁盘空间

挑选索引:

  • 用于搜索 排序 分组的列
  • 列的基数(列的值不重复的个数)越高 索引效果越好
  • 索引尽量选择较小的数据类型
    • IO 操作更快
    • 降低存储空间需求 可以在缓存中缓存更多数据 加快速度
  • 字符串索引指定前缀长度
    • 大多数字符串前n个字符就足以是唯一的 当成索引
  • 最左索引
    • 对于(a,b,c)这种类型的复合索引 利用其排列顺序进行操作 能有效利用索引
  • 不要过多的索引
  • 保持参与比较的索引类型匹配
    • 散列 B+树

查询优化程序

EXPLAIN SELECT * FROM person WHERE FALSE

有助于优化程序对索引充分利用:

  • 分析表 ANALYZE TABLE
    • 生成键值分析
  • 使用 EXPLAIN 验证哪些索引会被使用到
  • 必要时给予 EXPLAIN提示
    • 表名后面加上 FORE INDEX, USE INDEX, IGNORE INDEX
    • STRAIGHT_JOIN 要求按特定顺序使用表
  • 比较的列数据类型相同
  • 索引列不要参与运算
  • LIKE 语句开始位置不要使用通配符
  • 将子查询转换为连接
  • 尝试查收的各种替代形式
  • 避免过多类型的自动转换

数据类型高效查询

  • 多用数字运算 少用字符串运算
    • ENUM SET
  • 优先使用较小数据类型
    • 加快操作速度
    • 节省存储空间
  • 数据列声明NOT NULL
    • 避免 MySQL 运行时检查 NULL
  • 考虑使用ENUM
  • 输出MySQL对数据类型的建议
    • SELECT * FROM tb PROCEDURE ANALYSE()
  • 整理表碎片
    • OPTIMIZE TABLE tb
    • 某些存储引擎不支持 使用mysqldump导出再导入来整理
  • 使用BLOB TEXT存储非结构化数据
    • 注意删除更新时留下的碎片
    • 避免过大
    • 抽离到一张独立的表
  • 合成索引
    • 计算一个散列值存放到一个列

表存储格式高效查询

MyISAM:

  • 默认使用固定长度的行
    • 当某个列长度可变时 则行也会变成可变
    • 固定长度的行比变长行处理速度比较快

MEMORY:

  • 使用都是固定长度的行

InnoDB:

  • 默认情况是COMPACT行格式
  • 对于包含重复数据表 使用 COMPRESSED航格式 占用空间较少
  • 带有TEXT 或 BLOB 使用 DYNAMIC
CREATE TABLE tb (...) ROW_FORMAT = xxx;

高效加载数据

  • LOAD DATA 比 INSERT 效率更高
  • 数据加载时磁盘IO操作越少 效率越高

调度 锁定 并发

调度策略:

  • 写入优先级比读取优先级高
  • 写入操作一次只能执行一个 写入操作时公平的
  • 可以同时处理多个对同一个表的读取

InnoDB: 行级锁 更精细 并发度更高

MyISAM: 表级锁 不会出现死锁问题

results matching " "

No results matching " "

results matching " "

No results matching " "