MYSQL 优化

MYSQL 优化思维导图：包含索引问题，最常用的放在最前面，无where顺序无关，常规优化，union/union result/dependent union/dependent union等等

MYSQL 优化

• 索引问题
  • 复合索引
    • 最常用的放在最前面，无where顺序无关
• 常规优化
  • explain
    • select_type
      • simple，没有union或子查询
      • primary，嵌套中最外层 [ˈpraɪmeri]
      • union/union result/dependent union/dependent union
      • derived 衍生，导出，子查询产生临时表 [di`raivd]
      • subquery 子查询第一个select语句
    • type
      • const，最多一条，system 特例仅有一条
      • eq_ref，一种索引访问，仅返回1条，且索引为unique，primary
      • ref，返回多条，索引的是最左前缀，可以用于=<>
      • ref_or_null, 针对null
      • index_merger，索引合并优化
        • 5.1以后支持的，一个表可以使用多个索引
      • index intersect
      • unique_subquery，替换in子查询
      • index_subquery 替换in子查询
      • range，用index检索范围的行
      • index，全扫描索引
      • ALL
    • Extra
      • Using filesort ，没有用索引次序读到排序
      • Using temporary
      • Not exists
      • Using index
      • Using index condition
      • Using where
    • possible_keys
      • key 实际使用的索引
      • key_len，key的长度
      • ref 使用哪个列与key一起选择行
  • profile
• 思路
  • 周期性变化还是偶尔
  • 检查mysql的io和cpu利用比例
• engine
  • Myisam
  • InnoDB
    • 锁
• 查询缓存
• join优化
  • 确保on和using字句有索引，考虑关联顺序
  • 索引where和排序冲突的时候，改成关联表方式或者子查询
  • mysql5.6之前的一般需要改子查询为关联表
  • 走索引不一定都快，区分度不够的索引可用考虑用复合索引
  • 多值 max，改为group后max
  • join 时要考虑类型一致，char型要考虑字符集一致，时间类型没走索引，要注意隐世转换
  • select in 会被改写为exists，造成查询缓慢，可改成关联join查询
  • 驱动表的选择，认清关联字段索引，可用straight_join验证猜想
  • query cache并不适合更新，插入，删除非常大的应用
  • group by，order by 只涉及表中一个列
  • order by +limit 将limit，order by内嵌，限制join数量和保证index排序
  • join时 尽量将条件内嵌，先过滤后join。
  • 知道仅有一条时，用limit 1 限制
  • not in或not exits 可转换为外连接或等值连接
  • 某些情况下 or 可用用union all 或union 代替
• 排序优化
  • 单表查询，若file sort 则可以用强制使用索引排序，或者用自关联方式
  • 需要where+sort 或者多个sort 做联合索引，并考虑加上排序
  • sort buffer
  • 与group by 连用时 用order by null 禁用排序
  • 别名 若和字段重复也会影响排序优化
  • 索引长度导致不能优化
  • 相关参数
    • A. sort_buffer_size 排序缓存。
    • B. read_rnd_buffer_size 第二次排序缓存。
    • C. max_length_for_sort_data 带普通列的最大排序约束。
  • file sort过程-普通&双路排序
    • 取出字段a和每行的物理id（tid），然后在sort_buffer_size 中排序
    • 根据排序好的tid从磁盘中拿到行数据，放到read_rnd_buffer_size中
  • file sort过程-冗余单路排序
    • 区别是第一次是拿到所有相关数据而不只是TID，所以不用二次去磁盘取
  • file sort 优化
    • 增加相关参数大小
    • 增强tempdir 指向的文件系统io
• limit优化
  • 当与order by 字段联合使用时，若排序字段有重复，需要增加排序列，否则数据会错