mysql-高级

1 架构

可插拔架构将查询与存储分离
连接层 - 服务层 - 引擎层 - 存储层
connections -> connection pool -> parser | optimizer | caches&buffers -> myisam | innodb -> file system
optimizer - 优化器

2 优化

执行 | 等待 时间长

• 查询语句优化
• 索引失效
  索引底层做了排序
  单值索引 复合索引
• 查询关联太多join
• 服务器调优及参数设置(缓冲\线程数等)

sql执行顺序

from
on
where
group by
having
select
distinct
order by
limit

几种join

几种join

索引

索引

排好序的快速查找数据结构,指向数据
索引影响查找和排序
逻辑删除为了业务留记录,并且索引失效(人肉运维,重建索引)
update操作需要重建索引
索引大以文件形式存磁盘
通常B树,单值索引B+树
复合索引优于单值索引
单表索引最好不要超过5个
唯一索引: 索引值必须唯一,但可以为空

检索原理

检索原理

3层的b+树可以表示百万级别的数据

需索引

主键自动建
频繁查询字段
外键字段
(频繁更新字段不适合建索引)
排序字段
统计或分组字段(分组必排序)

不要建索引

mysql 300w以后性能下降
高重复平均分布字段

性能分析

query optimizer
常见瓶颈: cpu io 硬件瓶颈

3 explain

explain + sql语句

explain结果解释

id | select_type | table | type | possible_keys | key | key_len | ref | rows

• id
  id相同,执行顺序自上而下
  如果是子查询,id值越大优先级越高
• select_type
  primary 包含子查询,最外层查询
  union result 合并的结果集
• table
  derived 衍生虚表
• type
  system > const > eq_ref > ref > range > index > ALL
  至少要达到range级别,最好ref
  system: 只一行,忽略
  const: where中主键或唯一索引
  eq_ref: 用到索引,只匹配一条
  ref: 非唯一性索引扫描
  range: 给定范围索引(between in > < )
  index: 全索引扫描
  ALL: 全表扫
• possible_keys
  可能应用的索引,不一定被实际使用
• key
  实际使用的索引
  NULL: 无索引或索引失效
  覆盖索引: 查询字段刚好与索引匹配
• key_len
  理论使用的索引字段长度,非实际长度
  精度越小越好
• ref
  显示索引的哪一列被使用
• rows
  估算读取行数
• Extra
  不在其他列显示但重要的额外信息
  using filesort:
  无法利用索引完成的排序
  联合索引 a,b,c order by c会产生filesort,需要优化
  using temprory:
  临时表创建消耗系统性能
  group by
  using index:
  覆盖索引,如果同时出现using where表明索引用来执行查找