索引场景
1.mysql索引:
- 主键自动建立唯一索引
- 频繁查询字段应该建索引
- 频繁更新字段不要创建索引
- where里用不到的字段不要建索引
- 组合索引比单值索引更好
- 建索引还要考虑order by
- 查询中统计或分组(分组前提必排序)字段
2.不建索引:
- 经常增删改的表
- 数据重复且分布平均(数据差异不高)
- 索引选择性 = 一列中不同的值/一列中数据记录条数 越接近于1 建索引性能越好
- 表太小
3.性能分析
- mysql优化器
- MySQL瓶颈
- cpu
- io
- top, free, iostat, vmstat查看性能
- explain(执行计划)
- explain作用
- 表的加载顺序 看id
- 数据读取的操作类型 select_type
explain select * from tbl_emp emp left join tbl_dept dept on emp.deptId = dept.id;- 怎么用 explain + sql语句
- 怎么用 explain + sql语句
explain表头解释
id
- id相同 表示执行顺序相同的情况下 执行顺序从上到下
如下图emp, dept, id都是1, 执行顺序emp然后dept
- id不同, id大的先执行 t3 t1 t2表
- id有相同有不同 先走数字大的, 数字相同顺序执行
derived表示衍生, 2表示id=2
primary
- 最外层是primary, 最后加载的
- simple 简单的select 不包含子查询 union
- subquery 在select或者where列表包含的子查询
- derived from中包含子查询会被标记为衍生, 放在临时表里
- union 第二个select语句标记在union之后, 则被标记为union, 若union包含在from子句查询中,外层select会被标记为 derived
- union result 从union表获取结果的select
table
- 数据关于哪张表
type 访问类型排列
从好到差
- system > const > eq_ref > ref > range > index > all(全表扫描)
- system 只有一行记录(等于系统表)
- 只匹配一行数据,比较primary key或者unique(相当于常量)
explain (select tbl_dept.id from tbl_dept where id = 1);- eq_ref 唯一性索引扫描 (t1, t2比如t2是boss表, t2数据只有一个)
- ref (一对多 = )
- range(between , < , > , in) 有个范围
- index 全索引扫描,遍历了索引树, id是主键本来就建了索引
- range/ref级别就差不多可以了
possible_keys 和 keys
- possible 可能运用到表中的索引, 可能一个或多个 但不一定使用
- keys实际用的索引, 如果为null则没有用索引(查询中若用了覆盖索引, 则该索引仅出现在key列表中, select后面字段都在建好的索引内)
key_len
- 表示索引中使用的字节数,
- 显示的值为索引字段最大可能长度, 而非实际长度, 是根据表定义计算而不是检索
ref
- 第一个const 表示 t1.other_column = '' 常量
- 第二个test.t1.ID表示索引哪些列被引用(关联)
rows
- 大致估算每张表多少行被优化器查询 越少越好
建立索引前 ---- 建立索引后, 641行-143行
extra
- using filesort 产生文件内排序, 没有按照索引的排序, 而是mysql自己重新排序了
- using temporary 新建了内部的临时表, 常见于order by/ group by
- using index 表明用了覆盖索引
- using where where值总是false
- using join buffer join多了用到了buffer
- impossible where 不太可能
- distinct 很少用
