MySQL 优化

wanjichun 2020-06-10

慢日子查询

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。

当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

慢日志开启

查看是否开启: show variables like ‘%slow_query_log%‘;
开启慢查询日志:set global slow_query_log=1; (重启会失效)
 

开启了慢查询日志后,什么样的SQL才会记录到查询日志里面?

这个是由参数long_query_time控制,默认情况下long_query_time的值为10秒

查看命令: show variables like ‘long_query_time%‘;

 


注: 永久设置慢查询日志开启,以及设置慢查询日志时间临界点(不建议)

linux中,mysql配置文件一般默认在 /etc/my.cnf 更改对应参数即可

慢查询日志设置与查看

设置阀值命令: set global long_query_time=3 (修改为阀值到3秒钟的就是慢sql)
 

为什么设置后看不出变化:

需要重新连接或新开一个会话才能看到修改值。 show variables like ‘long_query_time%‘;
直接 show global variables like ‘long_query_time‘;
 

查看慢查询日志:

cat -n /data/mysql/mysql-slow.log



从慢查询日志中,我们可以看到每一条查询时间高于3s 的sql语句,并可以看到执行的时间是多少。

日志分析工具mysqldumpslow

在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具 mysqldumpslow

s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感的
 

工作常用参考:

得到返回记录集最多的10个SQL: mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

得到访问次数最多的10个SQL: mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

得到按照时间排序的前10条里面含有左连接的SQL: mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log

 

建议: 为方便 可以结合 | 和 more 使用,否则可能出现爆屏

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SQL 优化

详见SQL优化篇

建立索引

详见索引篇

性能分析Explain

Explain简称执行计划,使用Explain关键字可以模拟优化器执行SQL查询语句

用法:explain + SQL

1. id:

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

2. select_type:

查询的类型,主要是用于区分 普通查询、联合查询、子查询等的复杂查询

simple:简单的select 查询,查询中不包含子查询或者 union
primary:查询中若包含任何复杂的子部分,最外层查询则被标记为
subquery:在select或where列表中包含了子查询
derived:在from 列表中包含的子查询被标记为 derived(衍生)
union:若第二个select出现在之后,则被标记为 union(若union 包含from 子句的子查询中,外层select将被标记为:derived)
union result:从union 表获取结果的 select

3. table:

显示这一行的数据是关于哪张表的

4. type:

访问类型排列,显示查询使用了何种类型

从好到坏,system > const > eq_ref > ref > range > index > all

system:表只有一行记录(等于系统表),这是const 类型的特列,平时不会出现,这个也可以忽略不计
const:表示通过索引一次就找到了,const 用于比较 primary key或者unique索引
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问
range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了 between、<、>、in等的查询
index:Full Index Scan,index与 all 区别为 index 类型只遍历索引树
all:Full Table Scan,将遍历全表以找到匹配的行

5. possible_key:

显示可能应用在这张表的索引,一个或多个。(但不一定被实际应用)

6. key:

实际使用的索引,如果为null,则没有使用索引。

查询中若使用了覆盖索引,则该索引与查询的select字段重叠

7. key_len:

表示索引中使用的字节数

8. ref:

显示索引的哪一列被使用,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值

9. rows:

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

10. extra:

包含不适合在其他列中显示但十分重要的额外信息

Using filesort (劣): mysql 会对数据使用一个外部的索引排序(文件排序),而不是照表内的索引顺序进行读取

Using temporary (劣):使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by

Using index (优):表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!

Using where:表明使用了where 过滤

Using join buffer:表明使用了连接缓存

impossible where:where子句的值总是false,不能用来获取任何数据

select tables optimized away:
select操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了

distinct:在select部分使用了distinc关键字

explain总结

针对explain命令生成的执行计划,这里有一个查看心法。我们可以先从查询类型type列开始查看,如果出现all关键字,后面的内容就都可以不用看了,代表全表扫描。
    再看key列,看是否使用了索引,null代表没有使用索引。
    然后看rows列,该列用来表示在SQL执行过程中被扫描的行数,该数值越大,意味着需要扫描的行数越多,相应的耗时越长,
    最后看Extra列,在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现,这些是很影响数据库性能的。

相关推荐