MySQL 手册

发表于 更新于 分类于

参数配置

查看所有参数

1
mysql> SHOW VARIABLES;

慢查询、索引相关的参数

查看慢查询日志是否打开

Usage:

1
mysql> SHOW VARIABLES LIKE '%slow_query_log%';

Result:

Variable_name Value
slow_query_log OFF
slow_query_log_file /var/lib/mysql/17998065ff2d-slow.log

打开慢查询日志

Usage:

1
2
3
4
-- 打开MySQL慢查询日志
mysql> SET GLOBAL slow_query_log = ON;
-- 或者用这种方式
mysql> SET GLOBAL slow_query_log = 1;

慢查询阈值

查看慢查询阈值

Usage:

1
mysql> SHOW VARIABLES LIKE 'long_query_time';

Result:

Variable_name Value
long_query_time 10.000000
设置慢查询阈值

Usage:

1
2
-- 设置完之后，在当前窗口查询，可能存在值不变的情况，重新打开窗口查询即可。
mysql> SET GLOBAL long_query_time = 0.1;

未使用索引的情况

查看未使用索引的查询是否计入慢查询日志

Usage:

1
mysql> SHOW VARIABLES LIKE '%log_queries_not_using_indexes%';

Result:

Variable_name Value
log_queries_not_using_indexes OFF
打开未使用索引记入慢查询日志

Usage:

1
2
3
mysql> SET GLOBAL log_queries_not_using_indexes = ON;
-- 或者
mysql> SET GLOBAL log_queries_not_using_indexes = 1;

慢查询日志分析

MySQL 自带了 mysqldumpslow 可以帮助我们快速地分析慢查询日志。pt-query-digest 是一个非常优秀的第三方工具来帮助我们分析 MySQL 的 binlog、generallog、slowlog。

mysqldumpslow

Usage：

mysqldumpslow [ OPTS... ] [ LOGS... ]

具体的参数，可自行查看其对应的含义

示例：

1
2
3
4
5
6
7
8
9
10
11
# 得到返回记录集最多的 10 个 SQL。
mysqldumpslow -s r -t 10 /var/lib/mysql/izwz9ezwhlhq4hy1wzw9oqz-slow.log

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

# 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/izwz9ezwhlhq4hy1wzw9oqz-slow.log

# 另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /var/lib/mysql/izwz9ezwhlhq4hy1wzw9oqz-slow.log | more

剖析 MySQL 查询

在实际项目中，我们通过慢查询日志，只是找到对应的慢查询，那么如何对慢查询进行剖析呢？

MySQL 在 5.1 版本之后引入了 SHOW PROFILE 命令，默认是禁用的，但可以在会话级别动态地修改。

开启
1
2
-- 设置为 ON 或 1 均可
mysql> SET PROFILING = ON;
剖析查询耗时

开启之后执行任意一个查询，然后执行 SHOW PROFILING，观察对应的结果。

1
mysql> SHOW PROFILING;

其结果如下

Query_ID Duration Query
1 0.00060525 SELECT * FROM actor

如果需要看更为详细的信息

1
mysql> SHOW PROFILE FOR QUERY 1;

其结果如下，可以看到详细地记录了该次查询各个阶段的耗时情况

Status Duration
starting 0.000069
checking permissions 0.000009
Opening tables 0.000021
init 0.000022
System lock 0.000010
optimizing 0.000006
statistics 0.000017
preparing 0.000015
executing 0.000004
Sending data 0.000298
end 0.000006
query end 0.000008
closing tables 0.000009
freeing items 0.000020
logging slow query 0.000074
cleaning up 0.000019

事务相关的参数

事务隔离级别

查看事务的隔离级别
1
2
3
4
5
6
7
8
-- 查看当前数据库事务隔离级别
mysql> SHOW VARIABLES LIKE '%transaction_isolation%';
-- 或者
mysql> SELECT @@transaction_isolation;
-- 低版本的MySQL属性是不一样的
mysql> SHOW VARIABLES LIKE '%tx_isolation%';
-- 或者
mysql> SELECT @@tx_isolation;
设置事务的隔离级别
1
2
3
4
5
6
7
8
-- 设置当前会话级别为`read-uncommitted`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 设置当前会话级别为`read-committed`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置当前会话级别为`repeatable-read`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 设置当前会话级别为`serializable`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务自动提交

MySQL 默认采用自动提交模式，也就是说如果不是显式地开始一个事务，则每个查询都被当做一个事务执行提交操作。另外还有一些命令，在执行之前会强制 COMMIT 提交当前活动的事务，典型的例子如 DDL 语言中会导致大量数据改变的操作，又比如 ALTER TABLE 等，此外还有 LOCK TABLES 等语句。

查看事务自动提交状态

Usage:

1
2
-- 查看事务自动提交的参数
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';

Result:

Variable_name Value
autocommit ON
关闭事务自动提交

Usage:

1
2
-- 关闭事务自动提交
mysql> SET AUTOCOMMIT = OFF;