查看 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物理文件體系結構
- binlog 二進制日志文件
通過以下命令查看當前 binlog 文件列表：
mysql> show master logs;
ERROR 1381 (HY000): You are not using binary logging
mysql>
binlog 分為 statement 和 raw 格式。
- redo log
binlog 與 redo log 的分工不同，binlog主要做數(shù)據(jù)歸檔，redo log是奔潰恢復。 - innodb 共享表空間(系統(tǒng)表空間)和獨立表空間，相關參數(shù)
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>
- undo log 回滾日志，如果事務回滾，需要依賴 undo 日志進行回滾操作。為避免
ibdata1共享表空間暴漲，建議將 undo log 單獨存放?？梢允褂眠x項: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)
- 臨時表空間，可以通過參數(shù)
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)
- errorlog 錯誤日志
mysql> show variables like 'log_error';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| log_error | .\KRAIT.err |
+---------------+-------------+
1 row in set, 1 warning (0.00 sec)
- slow.log, 如果配置了 MySQL 的慢查詢日志， MySQL 就會將運行過程中的慢查詢日志記錄到 show_log 文件中。慢查詢指的是執(zhí)行時長超過
long_query_time值的 SQL，默認為 10s，參數(shù)如下：
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)
- 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.數(shù)據(jù)庫路徑，即系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫。
