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之后支持开窗函数可以直接用于排名）