- 查看慢SQL是否开启
- show variables like ‘%slow_query_log%’;
- 如value值为OFF,开启慢SQL日志
- set global ‘slow_query_log’ = on;
- 查看慢SQL判定时长,默认10秒
- show variables like ‘%long_query_time%’;
- 修改慢SQL判定时长,单位秒
- set long_query_time = 0.1;
- 查看慢SQL日志
- Time:日志记录的时间
- User@Host:执行sql的用户及主机
- Query_time:执行时间
- Lock_time:锁表时间
- Rows_sent:发送给请求方的记录数->结果数量
- Rows_examined:语句扫描的记录条数
- SET timestamp:语句执行的时间点
- select…:执行的sql语句
慢sql优化
- 使用EXPLAIN查看是否使用了索引,并查看扫描条数。
- explain select * from user where name = ‘关羽’;
- 使用索引时,只有查询的条数减少了,效率才会得到提升。对于一个大表,不止要创建索引,还要考虑过滤性,过滤性好,执行效率才会快。
- 提高索引过滤性
- 索引过滤性与索引字段、表的数据量、表设计结构都有关系。
- 添加虚拟列,如:
- alter table student add 虚拟列名 varchar(3) generated always as (left(列名,3)),add index(虚拟列名,字段名)
- left(列名,3)取该列的前3个字符做为虚拟列的值
- alter table student add 虚拟列名 varchar(3) generated always as (left(列名,3)),add index(虚拟列名,字段名)
- 慢查询原因:
- 全表扫描:explain的type属性为all
- 全索引扫描:explain的type属性为index
- 索引过滤性差:比如总量10W,过滤完后还剩9.5W条->注意索引字段选型、数据量和状态、虚拟列
- 频繁的回表查询开销:不要使用select *,使用覆盖索引
分页查询优化
-
首先开启show profiling,用于查看每条sql的执行时长。show profiling 默认是关闭的,需要手动开启。查看命令 show variables like ‘profiling’;
开启命令 set profiling = 1;
然后执行sql
通过命令查看sql执行情况 show profiles;
如图:
- Query_ID 2~6: 当偏移量变化,返回值固定时,从1000开始,sql执行时长明显增加;
- Query_ID 7~11:当偏移量规定,返回值变化是,从10000开始,sql执行时长明显增加。
- 因为limit查询机制,每次都会从数据库的第一条记录开始扫描,越往后越慢,随着查询数据的增多,查询速度越来越慢
- 优化方案一:
- 利用覆盖索引优化
- 优化方案二:
- 利用子查询优化(先利用覆盖索引锁定偏移量的起点,然后再用limit返回所需要的条数)
如上图:返回值均是 * 偏移量越大速度越慢,效率提升也就越显著。
- 利用子查询优化(先利用覆盖索引锁定偏移量的起点,然后再用limit返回所需要的条数)
其他章节 -> 跳转
end...