526互联

slow_log/generic_log/audit/存储引擎

发布时间 2023-04-02 10:28:22作者: bingo-HF

一. 慢查询日志进阶

1. 相关参数
  • slow_query_log
    • 是否开启慢查询
  • slow_query_log_file
    • 慢查询日志文件名，在my.cnf我们已经定义为solw_log，默认是机器名-slow.log
  • long_query_time
    • 制定慢查询阈值，单位是秒，且当版本>=5.5.X，支持毫秒。例如0.5即为500ms
    • 大于该值，不包括值本身。例如该值为2，则执行时间刚好为2的SQL语句不会记录
  • long_queries_not_using_indexes
    • 将没有使用索引的SQL记录到慢查询日志
      • 如果一开始因为数据少，查表快，耗时的SQL没有被记录，当数据量大时，该SQL可能会执行很长时间
      • 需要测试阶段就要发现问题，较少上线后出现问题的概率
  • log_throttle_queries_not_using_indexes
    • 限制每分钟内，在慢查询日志中，去记录没有使用索引的SQL语句次数，需要版本>=5.6.X
      • 因为没有使用索引的SQL可能会在短时间内重复执行，为了避免日志快速增大，限制每分钟执行的次数
  • min_examined_row_limit
    • 扫描记录少于该值得SQL不记录到慢查询日志
      • 结合没有使用索引的SQL语句的例子，有可能存在某一张表，数据量维持在百行左右，且没有建立索引。这种表即使不建立索引，查询也很快，扫描记录很小，如果确定有这种表，则可以通过此参数，将这个SQL不记录到慢查询日志
  • log_slow_admin_statements
    • 记录超时的管理操作SQL到慢查询日志，比如ALTER/ANALYZE TABLE
  • log_output
    • 慢查询日志的格式，[ FILE | TABLE | NONE ]，默认是FILE，版本>=5.5
    • 如果设置为TABLE，则记录到mysql.slow_log
  • log_slow_slave_statements
    • 在从服务上开启慢查询日志
  • log_timestamps
    • 写入时区信息，可根据需求记录UTC时间或服务器本地系统时间

 

2. 慢查询日志实践

  • 设置慢查询记录的相关参数
--
-- 终端A
--
-- 注意做实验以前，先设置my.cnf中的slow_query_log = 0，同时将min_examined_row_limit = 100注释掉
--
[root@mysql.sock][(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> show variables like 'slow_query_log'; -- 为了测试，提前关闭
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> set global slow_query_log = on; -- slow_query_log可以在线打开
Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][(none)]> show variables like 'slow_query_log'; -- 已打开
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |  -- my.cnf中设置为2s
+-----------------+----------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> show variables like "min_ex%";  -- my.cnf中已注释，所以这里为0
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
  • 查看慢查询日志

# 终端B

[root@vm-1 mysql_data]# tail -f slow.log
/usr/local/mysql/bin/mysqld, Version: 8.0.32 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument  -- 测试没有任何慢查询日志信息
  • 进行模拟耗时操作

# 终端A

[root@mysql.sock][(none)]> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)
  •  最终产生慢查询日志

#终端B

[root@vm-1 mysql_data]# tail -f slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.9 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2023-04-01T03:31:49.991263Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 4.001235  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
                                                          -- 这个就是min_examined_row_limit设置的意义。如果my.cnf设置为100，则这条语句Rows_examined<100不会被记录
SET timestamp=1680319909;
select sleep(4);

 注意：如果在终端A中设置set global min_examined_row_limit = 100;，然后执行select sleep(4)，会发现该记录仍然会被记录到慢日志。原因是因为set global min_examined_row_limit设置的是全局变量，此次会话不生效。但是我们上面设置的set global slow_query_log = 1;却是在线生效的，这点有所不同。

 

  • mysqldumpslow
[root@vm-1 mysql_data]# mysqldumpslow slow.log

Reading mysql slow query log from slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Time: N-N-01T03:N:N.272270Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-01T03:N:N.991263Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)

[root@vm-1 mysql_data]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     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       just show the top n queries
  -a           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  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

如果在线上操作，不需要mysqldumpslow去扫描整个slow.log，可以去 tail -n 10000 slow.log > last_10000_slow.log (10000这个数字根据实际情况进行调整),然后进行 mysqldumpslow last_10000_slow.log。

 

  • 查询慢日志存入表
--
-- 在my.cnf中增加log_output = TABLE，打开slow_query_log，然后重启数据库实例
--
[root@mysql.sock][(none)]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]> select * from mysql.slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time                 | user_host                 | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2023-04-01 21:27:07.023489 | root[root] @ localhost [] | 00:00:04.001634 | 00:00:00.000000 |         1 |             0 |    |              0 |         0 |         0 | select sleep(4) |         3 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.05 sec)

[root@mysql.sock][(none)]> show create table mysql.slow_log;
--
-- 表结构省略，关键一句如下
--
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'   -- ENGINE=CSV，这里使用CSV引擎性能较差

-- 建议改为MyIASM
[root@mysql.sock][(none)]> set global slow_query_log=off; -- 关闭慢日志
Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock][(none)]> alter table mysql.slow_log ENGINE=MyISAM;  -- 修改存储引擎
Query OK, 1 row affected (0.29 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@mysql.sock][(none)]> set global slow_query_log=on;  -- 打开慢日志
Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock][(none)]> show create table mysql.slow_log;  -- 查看表结构
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
--
-- 表结构省略，关键一句如下
--
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' -- ENGINE变成MyISAM
  1. 使用TABLE的优势在于方便查询，但是记住当在备份的时候，不要备份慢查询日志的表，避免备份过大
  2. 使用FILE也可以，需要定时清理该文件，避免文件过大

 

二. 通用日志（generic_log）与审计

1. 通用日志作用
  • 当需要查找某条特定SQL语句时，且该SQL语句执行较快，无法记录到slow_log中，可以开启通用日志generic_log进行全面记录，可用于审计Audit
  • 通用日志会记录所有操作，性能明显下降。所以如果需要审计，需要Audit Plugin

 

2. 审计插件
  • MariaDB Audit插件
    • MySQL社区办目前没有提供Audit功能，企业版本提供了该功能。Mariadb提供了开源Audit插件，且MySQL也能使用。官网：https://mariadb.com/kb/en/mariadb-audit-plugin/

 

三. 存储引擎

1. MySQL上支持的存储引擎

[root@mysql.sock][(none)]> show ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

 2. 存储引擎的概念
  • 用来处理数据库的相关CRUD操作（什么事CRUD）
  • 每个数据库都有存储引擎，知识MySQL比较强调存储引擎的概念

 

3. MySQL存储引擎
  • 官方存储引擎
    • MyISAM
    • INNODB
    • Memory
    • Federated
    • CSV
    • Archive
  • 第三方存储引擎
    • TokuDB – 开源，适合插入密集型
    • InfoBright – 商业，开源版本有数据量限制。属于列存储，面向OLAP场景
    • Spider 

 

4. 存储引擎之MyISAM
  • MySQL5.1版本之前的默认存储引擎
  • 堆表数据结构
  • 表锁设计
  • 支持数据静态压缩
  • 不支持事物
  • 数据容易丢失
  • 索引容易损坏
  • 唯一优点
    • 数据文件可以直接拷贝到另一台服务器使用

现在MySQL中还有用MyISAM的表，主要是历史原因。数据库文件以 MY 开头的基本都是MyISAM的表