MySQL面试问题整理三(非个人面试题，持续更新)

作业三

将内存结构对应的部分大小查询出来，能够描述各个内存结构的作用，描述用户线程工作空间对应的状态监控变量

           首先内存结构主要是由三大部分组成：innodb buffer pool、innodb log buffer、用户线程工作空间

          对于innodb buffer pool来说，又叫做数据缓冲区，大小可以占物理内存的50%~80%之间。

          对于innodb log buffer来说，是重做日志缓冲区，事务开始时，将redolog存到log buffer中 ，log thread将redolog刷新到log buffer中，保证只要事务提交，log thread就将redolog全部写入log buffer中，即使宕机，也能保证该事务所做的操作不会丢失。

        对于用户工作线程空间来说。是每一个用户都会被分配一个线程，一个线程对应一个用户空间。在用户工作空间中有很多缓冲区。例如是sort buffer，当需要对大量数据进行排序操作时，会使用到sort buffer,如果设置过小的话，可能会发生磁盘排序。第二个是join buffer，当进行多表连接时，会使用到join buffer。对于read buffer和read rnd buffer来说，read buffer主要是针对myisam表的，可以设置小一些或者不变，对于read rnd buffer来说，是针对所有的表的，可以设置大一些，16M即可。

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

mysql> show variables like '%innodb_sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
+-------------------------+---------+

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

mysql> show variables like '%read_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+

mysql> show variables like '%read_rnd_buffer%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+

mysql> show variables like '%binlog_cache%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+

 

描述MySQL数据以及目录结构、包括对应的参数控制文件

          mysql的数据文件结构，我们主要是关注binlog、redolog、errorlog、slowlog等

          对于binlog来说，最常用于主从架构之间保持数据的一致性问题。binlog属于逻辑日志，以SQL的方式来记录变更日志。当事务提交时，将redolog写入到logfile中去，因此需要binlog cache来缓存，缓存过小的话会造成binlog cache disk use，关于binlog需要注意的点在下一题中详细说明

        对于redolog来说，属于物理日志，记录的是哪个数据文件的哪个数据页的数据行操作、操作类型、操作值。redolog主要用于保护脏页、用于崩溃恢复的前滚、保护数据的一致性和持久性。并且redolog只能保护innodb引擎对应的表。

       errorlog的作用是帮助我们去排错，如果说再启动数据库时没有启动成功，可以去errorlog中查找错误，再根据错误的指示上网查询或者查看官方文档来进行排错。另外在errorlog中还可以记录死锁的信息，需要将参数打开。

mysql> show variables like '%print_all_deadlock%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+

       slowlog的作用是记录数据库中已经慢完的SQL和没有走索引的SQL都会被记录下来。

mysql> show variables like '%slow_query%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | ON                                    |
| slow_query_log_file | /usr/local/mysql/data/Client-slow.log |
+---------------------+---------------------------------------+

mysql> show variables like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.01 sec)


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

 

 

描述redolog和binlog的作用，设置binlog,包括开启、保留策略、模式、cache、大小、双1策略

          redolog的作用保护脏页、保持事务的持久性和一致性、崩溃恢复前滚

          binlog作用是用来备份恢复、主从之间传输保持数据的一致性、保证事务快速提交

//开启binlog
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
+---------------------------------+-----------------------------------------+


//设置记录SQL语句的形式，建议采用mixed模式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

//在会话级别可以关闭binlog参数
mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+

//binlog删除策略，一般不建议设置，可以采用脚本方式手工删除
mysql> show variables like '%expire_logs%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+

//可以在binlog文件中看到原始的SQL语句
mysql> show variables like '%binlog_rows_query%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON    |
+------------------------------+-------+

         双1策略的意思就是：事务提交直接将redolog跟binlog写到磁盘文件中。这样做的目的是为了避免在主从架构中，主库的binlog已经传输到从库，从库应用完之后，而主库的redolog还没有刷新到磁盘。这就造成了主从之间数据的不一致性。

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+

mysql> show variables like '%flush_log_at_trx%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

mysql> show variables like '%support_xa%'; //要么成功，要么失败
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_support_xa | ON    |
+-------------------+-------+

 

描述写抖动现象原因分析对应的监控指标要求

描述事务对应的undo结构图，包括系统事务表、段头块中的事务槽及undo指针、数据行中rollpointer、事务ID等

描述rollback过程

         当执行start transaction时，代表着一个事务开始，首先会选择一个空闲的undo段，在段头页的事务槽中分配一个事务ID跟undo页。事务去修改数据行，会在数据行上加上事务ID号和rollpointer指向改行数据被修改的最后一个undo页。各个undo页之间是通过指针串联的，形成一个回滚日志段，段头页中的指针指向事务修改的最后一个undo页。

        当事务回滚时，首先根据段头页中的指针找到该事务修改的最后一个undo页，根据这个undo页的rollpointer指针的指向，将数据行反向修改。多个undo页之间是串联起来的。rollback是从事务的最后一个undo页开始回滚，到最后一个undo页回滚结束。

描述崩溃恢复中的前滚过程

数据库重新启动之后，利用redo进行前滚，将崩溃前的脏页都构造出来。对于未提交事务。因为这些事务所对应的会话已经关闭，那么这是事务就变成了死事务了。需要主动回滚

监控大事务、长事务的实现方式

//执行
show engine innodb status \G
trx_started: 2019-05-11 13:02:24 //事务开始的时间与当前的时间做对比，看是否是长事务
trx_rows_modified: 0 //事务修改的行，可以作为判断大事务的依据

描述MySQL的长事务、大事务、长查询造成undo暴增的

         大事务：一个事务中有大量的DML，产生了大量的undo数据

         长事务：事务开始后，长时间不提交。导致purge线程不能够回收undo页

         长查询：其实跟长事务的含义是一样的

MySQL对delete操作的具体实现机制以及为什么产生少量undo原因分析

      delete操作的实际是在数据行上面标记该行已删除，减少delete对undo的使用。等待purge thread线程去进行回收。所以危害不是很大。