mysql merge性能_MySQL 性能分析备忘录

[TOC]

最后修改时间: 2019年10月21日15:08:59

救急

show full processlist

查看当前线程处理情况, 确认当前有哪些语句在执行, 执行情况如何.

特别注意执行时间长的, 如果确定有问题, 那么可以使用 kill {id} 干掉该连接

show full processlist 等价于以下语句

select id, db, user, host, command, time, state, info

from information_schema.processlist

order by time desc;

注意:

show processlist时, root账号可以看到所有账号的连接, 如果是普通账号则只能看到自己的连接.

如果显示长度太长看的很乱, 可以使用 \G, 即 show full processlist\G , 来将显示结果纵向输出, 方便查看.

扩展.

\g 等价于分号

\G 是将显示的表格以纵向输出, 方便查看.

Slow Query Log 慢查询日志

慢查询日志用于记录执行时间超过指定阀值的SQL命令.

确认开启情况

mysql> show variables like 'slow_query_log%';

+---------------------+------------------------------------------------+

| Variable_name | Value |

+---------------------+------------------------------------------------+

| slow_query_log | ON |

| slow_query_log_file | C:\laragon\data\mysql\DESKTOP-C1GGBS1-slow.log |

+---------------------+------------------------------------------------+

mysql> show variables like 'long_query_time';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 1.000000 |

+-----------------+----------+

开启方式

配置文件

############### 慢查询日志 ################

# 打开慢查询日志

slow_query_log=1

# 日志记录位置

log_output=file

# 慢查询日志记录文件

slow_query_log_file=/var/run/mysqld/mysqld-slow.log

# 慢查询时间阀值

long_query_time=10

命令方式(mysqld实例重启后失效)

-- 必须全局开启慢查询日志记录

set global slow_query_log=1;

-- 设置慢查询时间阀值

set global long_query_time=1;

如果想要分析一些语句的执行, 则可以考虑将当前Session的慢查询时间阀值设为0

set long_query_time=0;

注意

当数据库被拖垮时(高负载), 任何简单的语句都可能执行超时, 此时的慢查询日志能提供的帮助就有限了.

慢查询日志分析工具

mysqldumpslow

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

# 分析慢日志

mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log

# 参数说明

--verbose 版本

--debug 调试

--help 帮助

-v 版本

-d 调试模式

-s ORDER 排序方式, 默认是 'at'

what to sort by (al, at, ar, c, l, r, t), 'at' is default

al: average lock time 平均锁定时间

ar: average rows sent 平均返回记录数

at: average query time 平均查询时间

c: count 访问计数

l: lock time 锁定时间

r: rows sent 返回记录

t: query time 查询时间

-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)

-t NUM 显示前N条

-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'

-n NUM abstract numbers with at least n digits within names

-g PATTERN 正则匹配；grep: only consider stmts that include this string

-h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),

default is '*', i.e. match all

-i NAME name of server instance (if using mysql.server startup script)

-l 总时间中不减去锁定时间；don't subtract lock time from total time

示例

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

SQL Explain

对于慢查询日志中执行慢的语句分析其 SQL语句的执行计划

EXPLAIN 可以帮助了解:

数据表的读取顺序

SELECT子句的类型

数据表的访问类型

可使用的索引 possible_keys

实际使用的索引 key

使用的索引长度 ken_len

上一个表的连接匹配条件

被优化器查询的行的数量

额外的信息(如使用使用外部排序, 是否使用临时表)

举例

EXPLAIN 结果列分析

字段 id

SQL 执行顺序是根据

id从大到小执行

id相同时按照顺序从上往下执行.

字段 select_type(查询类型)

查询类型

说明

SIMPLE

简单查询

不包含UNION查询或子查询

PRIMARY

最外层查询

查询中若 包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY

子查询

在 SELECT 或 WHERE 中包含了子查询

DEPENDENT SUBQUERY

!!! 子查询, 但依赖于外层查询的结果

注意确认, 避免大表驱动小表

DERIVED

子查询

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)

UNION

联合

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION

UNION RESULT

使用联合的结果

从UNION表获取结果的SELECT

关于UNION, 网上有写以下这段, 但我个人不理解

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

字段 table(数据表)

访问的数据表

字段 partitions(分区)

匹配的分区

字段 type(访问方式)

查询时的访问方式, 性能：all < index < range < index_merge < ref < eq_ref < system/const

一般来说至少需要保证访问方式是 range, 最好是 ref 级别.

访问方式

说明

ALL

全表扫描，对于数据表从头到尾找一遍

select * from tb1;

特别的：如果有limit限制，则找到之后就不在继续向下扫描

select * from tb1 where email = 'seven@live.com'

select * from tb1 where email = 'seven@live.com' limit 1;

虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

INDEX

全索引扫描，对索引从头到尾找一遍

因为非主键索引树比较小, 所以会比 ALL 更快

RANGE

对索引列进行范围查找

通常是在索引树上快速定位到某一索引项, 再向左/右遍历.

INDEX_MERGE

合并索引，使用多个单列索引搜索, 最后结果取交集或并集

比如使用了UNION 且单独用到了两个索引.

REF

使用索引快速定位(根据索引查找一个或多个值), 该索引是 普通索引 或 唯一索引的部分前缀

EQ_REF

使用主键索引或唯一索引快速定位

通常出现在多表的join查询, 连接时使用primary key 或 unique 索引(都只能匹配到一行记录)

CONST

通过主键或唯一索引精确查找到一行

常量

表最多有一个匹配行(主键或唯一索引),因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次

SYSTEM

系统

表仅有一行, 这是const联接类型的一个特例, 可以忽略这种情况。

Q. ALL 和 INDEX 的区别

A. 两个都是全索引扫描, 不同的是 ALL 是对主键索引扫描, INDEX 是对非主键索引扫描.

这里要理解, 所谓的全表扫描指的是对主键索引扫描.

Q. EQ_REF 和 CONST 的区别

A. 相同点都是使用主键/唯一索引精确查找到行记录. 不同点在于:

CONST 查询条件通常是 索引列 = 具体常量值

EQ_REF 通常是在多表关联查询时作为连接条件使用.

字段 possible_keys(候选索引)

字段 key(实际使用的索引)

如果是合并索引(INDEX_MERGE), 则此处可能存在超过1个的key

字段 key_len(使用索引的实际长度)

该字段可以评估组合索引是否完全被使用或仅仅是最左前缀被用到.

该字段显示的值为索引字段的最大可能长度, 并非实际使用长度.(即 key_len 是根据表定义计算, 而非表内检索)

计算规则

字段类型

计算方式

字符串

char(n)

n字节长度

varchar(n)

若是utf8编码, 则是 n3 + 2 字节

如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值

tinyint

1字节

smallint

2字节

mediumint

3字节

int

4字节

bigint

8字节

时间

date

3字节

timestamp

4字节

datetime

8字节

如果对应索引字段允许为 null, 则还要额外消耗1个字节来存储 NULL.

字段 ref

表示索引的查找条件, 可能是常量(const) 或 联合查询中另一张表的某个字段.

字段 row(扫描行数)

估算的需要扫描的行数, 注意是估算的.

在某些情况下若索引统计信息偏差较大, 则此处的预估扫描行数也会过大, 导致影响查询计划的选择.

可以使用 SHOW INDEX FROM 表名 来查看索引统计信息

可以使用 ANYLYSIS TABLE 表名来重新统计索引信息.

字段 filtered

字段 Extra(额外信息)

该列包含MySQL解决查询的详细信息

值

说明

Using filesort

mysql无法依靠索引直接获取有序记录, 而是对结果进行额外排序.

mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成

explain不会告诉你mysql将使用哪一种文件排序

也不会告诉你排序会在内存里还是磁盘上完成。

Using index

使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。

Using index condition

索引下推优化, 5.6新增特性

Using temporary

意味着mysql在对查询结果排序时会使用一个临时表

Using where

使用了 where 过滤

这意味着mysql服务器将在存储引擎检索行后再进行过滤

许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验

因此不是所有带where子句的查询都会显示“Using where”。

有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。

Range checked for each record(index map: N)

这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

using join buffer

在表联结时, 使用了连接缓存

Profiling

写在最前: SHOW PROFILE 命令将被弃用, 注意, 仅仅是 SHOW PROFILE命令弃用.

替代方案是从 information_schema 中的profiling数据表进行查看, 可参数链接:

SHOW PROFILE 命令方式(旧)

查看语句执行的时间在各个步骤的开销

show profile 分析SQL性能工具(检测数据存在于临时表中)

在会话级别开启profile SET profiling=1;

发送sql

查看profile的资源开销结果

show profiles 查看所有的分析结果(会有一个数量上限)

show profile 查看最后一条执行语句的分析结果

show profile for query 查看指定执行语句的详细分析结果

show profile cpu, block io for query 查看详细信息, 且包含 cpu, block.io 执行时间

关闭profile

information_schema .profiling

待用到时补充.