参数 | 描述 |
---|---|
slow_query_log | 是否启用慢sql查询 |
long_query_time | 设置sql时间阈值,超过阈值即为慢sql |
slow_query_log_file | 指定查询日志的路径以及名字 |
log_output | 慢日志输出到文件还是表中 |
log_output=‘FILE’表示将日志存入文件,默认值是’FILE’。
log_output='TABLE’表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。
MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,建议优先记录到文件
慢sql状态查询
show variables like "%slow_query_log%"
开启慢sql
1.set global slow_query_log = on;数据库重启失效
2.修改配置文件my.ini
[mysqld] log_output='FILE,TABLE' slow_query_log='ON'
使用mysqldumpslow来分析慢sql日志
找出慢sql后使用explain分析sql语句,
在sql语句之前加上explain关键字
执行计划各个字段含义:
id
select_type
表示查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
SIMPLE 简单的select查询,查询中不包含子查询或者UNION
table
当前执行的表
partition
表分区、表创建的时候可以指定通过那个列进行表分区
type
访问类型
ppossible_keys
可能使用的索引,不一定会使用,为null考虑优化
key
查询中实际使用的索引
key_len
索引长度
ref
表的连接匹配条件
rows
估算的结果集数目
filtered
存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,是百分比
Extra
额外信息
join优化
left join、right ioin
在优化关联查询时,在被驱动表上建立索引才有效
left join 时,左侧的为驱动表,右侧为被驱动表
right ioin是,左侧的为被驱动表,右侧为驱动表
inner join
把小结果集的表选为驱动表(小表驱动大表)
union优化
union all不去重但是性能高于union,能用union all就用union all
in、exists
in 是把外表和内表作hash 连接,先查询子表,子表再与外表做笛卡尔积(子表为驱动表),in不出来null
exists是对外表作loop循环,每次loop循环再对内表进行查询(外表为驱动表)
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时使用in, 如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists
LIMIT
可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
WHERE
select id from t where num is null -- 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0
select id from t where num=10 or num=20 -- 可以这样查询: select id from t where num=10 union all select id from t where num=20
select id from t where num in(1,2,3) -- 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3
select id from t where num=@num -- 可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num
select id from t where num/2=100 -- 应改为: select id from t where num=100*2
select id from t where substring(name,1,3)=’abc’ -- name以abc开头的id应改为: select id from t where name like ‘abc%’
除此之外的优化就要考虑
表拆分、分库分表、主从、集群了