为了使我们的系统能更快的运行, 能承受更高的负载, 我们毫无疑问都需要对MySQL进行优化. 优化的方式分为以下几种:
添加索引
查看执行计划
- id: id若相同,执行顺序由上向下. id不同(有子查询), id越大,优先级越高
- select_type: 查询类型
- simple(无连接,无子查询)
- primary(有子查询或union操作), 外层的为primary
- union
- dependent union
- union result
- subquery
- dependent subquery
- derived
- table
type: 依次从好到差
- system,表中只有一行数据或是空表
- const,使用唯一索引或者主键
- eq_ref,连接查询, 连接字段为主键或者唯一索引
- ref, 针对非唯一索引, 使用等值查询或者是使用了最左前缀规则索引的查询
- fulltext, 全文索引,优先级高于普通索引
- ref_or_null, 类似ref,但增加了null值的比较
- unique_subquery, 用于where查询的in查询,子查询返回不重复的唯一值
- index_subquery,
- range, 索引范围扫描>, <, is null, between, in, like等
- index_merge,
- index, 索引全匹配扫描
- all.
除all之外,其他的type都可以使用到索引, 最少要使用到range级别
possible_keys, 此次查询中可能选用的索引
key, 真正使用到的索引
key_len, 使用到索引的长度(如果使用了一个索引,那么长度即为该字段的长度), 注:排序和分组即使用到了索引也不会计算到key_len中.
ref, 若是常数等值查询,这里会显示const; 连接查询显示驱动表的关键字段; 如果内部有隐式转换, 此处显示func
rows, 扫描的行数, 不是精确值(因为InnoDB使用了MVCC)
extra, 一些其他重要的额外信息, distinct, no table used, using filesort(排序中没用到索引),
using index(用到了覆盖索引, 如果同时出现using where表明用到了索引查找,如果没有表明用到了索引查询)
慢查询日志
show variables like '%slow%';慢查询日志分析工具: mysqldumpslow(mysql自带的)常用参数:
mysqldumpslow -s t -t 10 -g "left join"
-s: 按什么排序
-t: 返回几条数据
-g: 正则Query Profiler分析语句
能定位出一条SQL语句执行的各种资源消耗情况, 比如CPU, IO等, 以及该SQL执行所耗费的时间等.
select @@profiling;常见的SQL优化例子
- 如果预先知道select的查询结果是一条,最好使用LIMIT 1,可停止全表扫描
- 分页offset过大时:
select * from user where id >= (select id from user order by id limit 100000,1) limit 20;- 尽量不使用count(*), 使用count(主键), count(*), 遍历所有行, count(列), 查询指定列不为null的行数