2020-10-25-1-41页 MySQL 事务和锁

查看 MySQL 支持的存储引擎可以使用命令：SHOW ENGINES

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

mysql>

重点关注 InnoDB、MyISAM、MEMORY这三种。

---

MySQL物理文件体系结构

1. binlog 二进制日志文件
   通过以下命令查看当前 binlog 文件列表：

mysql> show master logs;
ERROR 1381 (HY000): You are not using binary logging
mysql>

binlog 分为 statement 和 raw 格式。

2. redo log
   binlog 与 redo log 的分工不同，binlog主要做数据归档，redo log是奔溃恢复。
3. innodb 共享表空间(系统表空间)和独立表空间，相关参数 innodb_file_per_table
   查看是否开启了这个独立表空间选项：

mysql> show variables like 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

4. undo log 回滚日志，如果事务回滚，需要依赖 undo 日志进行回滚操作。为避免 ibdata1共享表空间暴涨，建议将 undo log 单独存放。可以使用选项: innodb_undo_directory，例如：

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | .\         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set, 1 warning (0.00 sec)

5. 临时表空间，可以通过参数innodb_temp_data_file_path查看，例如：

mysql> show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)

6. errorlog 错误日志

mysql> show variables like 'log_error';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| log_error     | .\KRAIT.err |
+---------------+-------------+
1 row in set, 1 warning (0.00 sec)

7. slow.log, 如果配置了 MySQL 的慢查询日志， MySQL 就会将运行过程中的慢查询日志记录到 show_log 文件中。慢查询指的是执行时长超过 long_query_time值的 SQL，默认为 10s，参数如下：

mysql> show variables like '%slow_query_log%';
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_query_log      | ON             |
| slow_query_log_file | KRAIT-slow.log |
+---------------------+----------------+
2 rows in set, 1 warning (0.00 sec)

还有

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

8. general_log 通用查询日志，记录client 连接和运行的语句。

mysql> show variables like '%general%';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| general_log      | OFF       |
| general_log_file | KRAIT.log |
+------------------+-----------+
2 rows in set, 1 warning (0.00 sec)

9.数据库路径，即系统数据库和用户数据库。