mysql优化参考(三)-select

  • 查询慢的原因
    • 网络&IO(集群)
    • CPU
    • IO - 临时表、文件排序,查询数据量
    • 上下文切换
    • 生成统计信息(Performance_Schema 、Profile等)
    • 锁等待时间 - 并发场景(表锁、行锁 | 读锁、写锁 | 【InnoDB-锁的对象:索引】共享锁、排它锁 | 【MyIsAM】共享锁、独占锁)
      • MyIsAM:共享锁、独占锁
      • InnoDB:共享锁、排它锁
      • 读写锁、间隙锁(对匹配行的左右加锁)、自增锁
  • 优化数据访问
    • 减少数据访问(业务不需要的数据不要去访问),例如不使用select *
    • 减少不需要访问的数据行,limit值多大可能导致需要全表扫描
    • 减少请求不需要的数据
      • 查询了不需要的数据
      • 多表关联查询出了所有列
      • 查询重复的数据(重复查询的数据建议缓存)
  • 执行过程的优化
    • 查询缓存:8之后去除
    • 查询优化处理
      • 语法解析器与预处理优化
      • 查询优化器
        • 数据查询页数统计(show status like 'last_query_cost')
          • 每个表或索引的页面数
          • 索引的基数,会影响索引的选择
          • 索引和数据行的长度(可以采用前缀部分组建索引)
          • 索引的分布情况
        • 选择正确的执行计划
          • 统计信息不正确导致执行计划选择错误
          • mysql默认采用成本估算,这个成本估算可能和实际执行效率相差甚远
          • 并发场景下查询问题
          • mysql不会考虑不受其监控的操作成本
        • 优化策略
          • 静态:根据执行计划调整,比如将type优化成const
          • 动态:与表的数据量或者索引的基数等等相关,会影响实际优化器的效果,这类的优化器比较不稳定,会随着数据量而变化
        • 优化类型
          • 尽量使用内连接代替外连接
          • 尽量使用简短的等价语句代替,比如mysql自己优化的方式join on中的连接字段就会自动传递到另一个关联表;比如使用using代替on或自动过滤掉一个另一个关联列;比如使用in会只有一次判断,而没有多次遍历判断
          • count、min、max等聚合函数如果命中索引,可以优化(比如min,直接取最左端;max,取最右端)
          • 如果子查询表只有一行,则会自动优化为常量值;mysql有很多类似的优化,比如恒定为true的表达式会替换成true,没有实际效果的表达式也会被消除
          • 索引覆盖,查询的列都建立在全值索引中
          • 子查询优化:尽量命中与主句相同的优化策略,比如命中索引覆盖也可应用在子查询中
        • 关联查询:优先使用内连接,内连接一般采用匹配原则,不会展示所有数据
        • 排序优化:
          • 避免文件排序,尽量命中索引优化,主键索引会更快
          • order by 主键ID,可以使用主键扫描
  • 优化特定类型的查询
    • count:
      • mysql中 count(1)和count(*)没差别
      • 可以使用近似值,比如统计好字段,计入缓存,缓存定时更新
    • 关联
      • 尽量使用外键关联,或者有索引的列进行关联
      • 尽量使用内连接代替外连接,一般不需要自行指定内连接的表顺序,mysql自动优化可能更好
      • 尽量在关联查询的order by中只使用一个表的列,使得索引可以被使用
    • 子查询
      • 尽量使用join代替,因为子查询通常意味着临时表
      • 可以将热点数据缓存起来,使用缓存来代替子查询(如先将in的子句id查询出来)
    • limit
      • 尽量使用覆盖查询
      • 可以使用索引字段order by
    • union
      • 尽量使用union all,避免自动加上distinct影响性能
    • 自定义变量:set varName:=value
      • 可以应用在排名查询(mysql 8之后支持开窗函数可以直接用于排名)
原文地址:https://www.cnblogs.com/gabin/p/13723349.html