笔记：MySQL技术内幕-03
慢查询日志
1.慢查询日志(先打开开关，再修改时间)
mysql> show variables like '%slow_query%'\g;
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
mysql> show variables like '%long_query%'\g;
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
2.慢查询日志，也可记录没有使用索引
mysql> show variables like 'log_queries%'\g;
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
分析慢查询日志工具
mysqldumpslow
3.也可以表格形式查看慢查询日志（表 mysql.slow_log）
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
4.也可根据逻辑IO读取次数记录慢查询(没有找到变量)
slow_query_type 启用slow log的方式
long_query_io 限定的逻辑IO次数，用于记录为slow log
binlog 二进制日志
默认不启用
待补充
mysql> show variables like '%binlog%'\g;
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
表空间文件
1.共享表空间的文件
=> 默认12M，可指定到不同的空间
mysql> show variables like '%innodb_data%'\g;
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+
2.为每个表创建独立的表空间文件（默认打开），文件后缀为.ibd
mysql> show variables like '%innodb_file_per%'\g;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
redo 重做日志文件
1.至少有1个重做日志文件组，每个组下至少有2个日志文件
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2
2.为提供高可用性，可设置多个镜像日志组，采用循环写方式
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
3.对比binlog和redolog
1.归属不同：binlog记录mysql所有日志，redolog只记录innodb的事务日志；
2.目标不同：binlog记录逻辑日志，而redolog记录关于页page的更改物理情况；
3.写入时间不同：binlog在提交时写，redolog不断写；