以下是最近学习MySQL的一些笔记,推荐一起阅读:
MySQL逻辑架构介绍
MySQL性能分析
MySQL索引优化
MySQL查询截取分析
MySQL锁机制
MySQL主从赋值
MySQL性能分析
MySQL Query Optimizer
MySQL自带的优化器
MySQL常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- I/O:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 硬件性能:使用top free iostat 和 vmstat来查看系统状态性能
EXPLAIN语句
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的,从而可以分析语句或者是表结构的性能瓶颈
EXPLAIN + SQL语句
EXPLAIN SELECT * FROM table;
执行一条EXPLAIN语句的结果:
id字段
表的读取顺序
- id相同,执行顺序由上到下
- id不同,代表子查询,id序号会递增,id值越大优先级越高,先被执行
- 如果每个id都有自己的2345…子查询,严格按照id顺序从大到小执行,然后同级id从上到下顺序执行
- id
- 1 A
- 2 B
- 1 C
- 2 D
- 3 E
执行顺序:E B D A C
select_type字段
查询类型
- SIMPLE:简单SELECT查询,查询中不包含子查询或者UNION
- PRIMARY:子查询最外层被标记为PRIMARY
- SUBQUERY:子查询
- DERIVED:在FROM列表重包含的子查询被标记为DERIVED(衍生查询),MySQL会递归执行这些子查询,把结果放在临时表里面
- UNION:如果第二个SELECT出现在UNION之后,则被标记为UNION;如果UNION包含在FROM子句的子查询中,外层SELECT会被标记为DERIVED
- UNION RESULT:从UNION表获取结果的SELECT
table字段
这一行数据对应的表
type字段
访问类型(有多重值)
从最好到最差依次排列:
system > const > eq_ref > ref > range > index > ALL
保证查询至少达到range级别,最好达到ref级别
- system:表只有一行记录,这是const类型的特例
- const:表示通过索引1次就找到了,const用于代表primary key或者unique索引,因为只匹配一行数据,所以很快。如将主键用在where条件中,MySQL就能将该查询转换为一个const类型
- eq_ref:唯一性索引扫描,对于每个索引建,表中只有一条记录与之匹配。常见于主键或者唯一索引扫描
用到了索引,但是查询出来只有一条记录 - ref:非唯一性索引扫描,返回匹配某个单独值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而找到了多个符合条件的行,所以属于 查找和扫描的混合体 - range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引
一般是where条件中初见了between和大于小于号in等查询 - index:Full Index Scan,全索引扫描,index类型只遍历了索引树,仍然比ALL快
虽然all和index都是读全表,但是idnex是从索引中读取的,而all是完全读取磁盘 - ALL:全表扫描
possible_keys和key字段
判断是否使用到了索引,在多个索引竞争之下,最终用到了哪个索引
- possible_keys:可能用到的索引
- key:实际使用的索引,如果为NULL则没有使用索引;查询中如果使用了覆盖索引,则该索引仅出现在key列表中
key_len字段
表示查询字段使用的字节长度
显示的值是索引字段的最大可能长度,这是计算出来的值,不一定准确
ref字段
显示索引的哪一列被使用了,如果可能的话是一个常数。哪些列或者常量被用于查找索引列上的值
row字段
根据表的统计信息和索引选用情况,大致计算得到的查询行数,越小越好
Extra字段
包含一些额外信息
- Using filesort:文件内排序,说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序。额外进行了排序,需要优化
- Using temporary:使用了临时表保存中间结果,需要优化,常见于order by和group by
- Using index:表示相应的select操作使用了覆盖索引,避免访问了表的数据行
- 同时出现using where,表明索引被用来执行索引键值的查找
- 没有同时出现using where,表明索引被用来读取数据
- Using where:使用了where
- Using join buffer:使用了join buffer
- impossible where:where子句的结果总是为false,不能获取任何结果
- select tables optimized away:在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成了优化
- distinct:优化distinct操作,在找到第一匹配的元组后即停止找相同值的动作