Erlo

MySQL 之 慢查询优化及慢日志管理

时间:2020-11-12   阅读:30次   来源:博客园
页面报错
点赞

1、查询优化神器 —— explain

​ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈。

(1)、通过EXPLAIN,可以分析出以下结果:

(2)、使用方式:

EXPLAIN + SQL语句

(3)、执行计划包含的信息

+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

(4)、执行计划各字段的含义

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

<2>、select_type:分别用来表示查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。

<3>、table:指的就是当前执行的表

<4>、type:显示的是查询使用了哪种类型

从最好到最差依次是:system > const > eq_ref > ref > range > index > all。一般来说,得保证查询至少达到range级别,最好能达到ref。

<5>、possible_keys:显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

<6>、key:实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)。查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中。

<7>、key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

<8>、ref:显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。

<9>、rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好。

<10>、包含不适合在其他列中显式但十分重要的额外信息。

​ 关于 explain 命令的具体用法和字段含义可以参考官网explain-output

需要强调 rows 是核心指标,绝大部分 rows 小的语句执行一定很快(也有例外)。所以优化语句基本上都是在优化rows。

执行计划:让mysql预估执行操作(一般正确)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email
    
    慢:
        select * from test where name='cai'
        
        explain select * from test where name='cai'
        type: ALL(全表扫描)
            select * from test limit 1;
    快:
        select * from test where email='cai@'
        type: const(走索引)

2、慢查询优化的基本步骤

0.先运行看看是否真的很慢,注意设置 SQL_NO_CACHE
1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
2.explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果,不符合预期继续从0分析

3、慢日志管理

​ MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

# 慢日志
    - 执行时间 > 10
    - 未命中索引
    - 日志文件路径

# 配置:
    - 内存
        show variables like '%query%';
        show variables like '%queries%';
        set global 变量名 = 值
    - 配置文件
    	mysqld --defaults-file='E:caimysql-5.7.16-winx64mysql-5.7.16-winx64my-default.ini'

# my.conf内容:
    slow_query_log = ON
    slow_query_log_file = D:/....

# 注意:修改配置文件之后,需要重启服务

4、MySQL日志管理

(1)、MySQL日志

#========================================================
错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称binlog日志,以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志: 备库将主库的二进制日志复制到自己的中继日志中,从而在本地进行重放
通用日志: 审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志: 记录Innodb事务相关的如事务执行时间、检查点等
#========================================================

(2)、MySQL日志配置

## 一、bin-log
1. 启用
# vim /etc/my.cnf
[mysqld]
log-bin[=dir[filename]]
# service mysqld restart
2. 暂停
//仅当前会话
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;
3. 查看
查看全部:
# mysqlbinlog mysql.000002
按时间:
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 

按字节数:
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4. 截断bin-log(产生新的bin-log文件)
a. 重启mysql服务器
b. # mysql -uroot -p123 -e 'flush logs'
5. 删除bin-log文件
# mysql -uroot -p123 -e 'reset master' 


## 二、查询日志
启用通用查询日志
# vim /etc/my.cnf
[mysqld]
log[=dir[filename]]
# service mysqld restart

## 三、慢查询日志
启用慢查询日志
# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3  单位为秒
查看慢查询日志
测试:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);

相关推荐

提交留言

评论留言

还没有评论留言,赶紧来抢楼吧~~

吐槽小黑屋()

* 这里是“吐槽小黑屋”,所有人可看,只保留当天信息。

  • Erlo吐槽

    Erlo.vip2021-01-16 07:13:29Hello、欢迎使用吐槽小黑屋,这就是个吐槽的地方。
  • 返回顶部

    给这篇文章打个标签吧~

    棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认