mysql中如何设置作业_MySQL 作业六

1.　InnoDB 的 buffer pool 的主要作用是什么？

InnoDB 在内存中开辟的用来缓存表数据和索引数据的区域，加快语句执行效率

2.　请简要说明 InnoDB 通过什么机制来实现事务的回滚和多版本控制？

为保证并发操作和回滚操作，InnoDB会将修改前的数据存放在回滚段中

为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称为Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了rollback语句，系统可以利用 Undo Log中的备份将数据恢复到事务开始之前的状态

3.　请简要说明 InnoDB_redo_log_buffer 通过哪两个参数控制刷新到 redo log 文件的频率？参数的不同配置值有什么区别？

mysql> show variables like '%innodb_flush_log_at_trx_commit%';+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1 |

0：每秒写入并持久化一次(不安全，性能高，无论mysql或服务器宕机，都会丢数据最多1秒的数据)

1：每次commit都持久化(安全，性能低，IO负担重)

2：每次commit都写入内存的内存缓存，每秒再刷新到磁盘(安全，性能折中，mysql宕机数据不会丢失，服务器宕机数据会丢失最多1秒的数据)

4.　InnoDB 的每个表一个独立表空间的方式如何实现？

mysql> show variables like '%per_table%';+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

5.　数据库服务器上 my.cnf配置文件的存放文件夹都有哪些？如果多个文件夹下都存在这个文件，则里面的配置项如何生效？

6.　系统表空间对应的数据文件如何设置自动扩展？如何增加新的数据文件？

可以通过innodb_data_file_path和innodb_data_home_dir来配置系统表空间数据文件

Innodb_data_file_path可以包含一个或多个数据文件，中间用；号分开

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

datafile_spec1 = file_name:file_size[:autoextend[:max:max_file_size]]

其中autoextend和max选项只能用作最后的这个数据文件。Autoextend默认情况下是一次增加64MB，如果要修改此值可通过innodb_autoextend_increment参数。Max用来指定可扩展数据文件的最大容量用来避免数据文件大小超过可用磁盘空间大小。

7.　InnoDB 的read only 模式如何设置？

[mysqld]

innodb-read-only=1

8.　默认情况下如果在配置文件中设置 innodb_buffer_pool_size 为500M, 实际生效的大小是多少？为什么？

128*4=512

innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的整数倍，如果不是，则buffer pool会被调整成大于设定值且最接近的一个值

9.　InnoDB 表的永久统计资料是存放在哪两张表里的？

优化器永久统计资料数据在系统表mysql.innodb_table_stats和mysql.innodb_index_stats表中存储，这两个表中有个字段last_update可以用来判断统计信息最后更改时间。

10.　InnoDB 的普通表空间的创建语句是什么？如何将一个要创建的表指向到普通表空间上？

CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd'Engine=InnoDB;

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1；

CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2；

11.　InnoDB 的系统表空间上的表和独立表空间上的表如何实现相互转换？

ALTER TABLE tbl ... TABLESPACE [=] innodb_file_per_table ##从系统表空间或者普通表空间转移到独立表空间

12.　如果当前有锁等待情况，如何发现源头是哪个事务？怎么处理？

session 1：

[root@mysql-master ~]# mysql -u root -p

mysql> show variables like '%wait%';+---------------------------------------------------+----------+

| Variable_name | Value |

+---------------------------------------------------+----------+

| innodb_lock_wait_timeout | 50 |

| innodb_log_wait_for_flush_spin_hwm | 400 |

| innodb_spin_wait_delay | 6 |

| lock_wait_timeout | 31536000 |

| mysqlx_wait_timeout | 28800 |

| performance_schema_events_waits_history_long_size | 10000 |

| performance_schema_events_waits_history_size | 10 |

| wait_timeout | 28800 |

+---------------------------------------------------+----------+

8 rows in set (0.03sec)

mysql> set global innodb_lock_wait_timeout=600;

Query OK,0 rows affected (0.00sec)

mysql> show variables like '%wait%';+---------------------------------------------------+----------+

| Variable_name | Value |

+---------------------------------------------------+----------+

| innodb_lock_wait_timeout | 50 |

| innodb_log_wait_for_flush_spin_hwm | 400 |

| innodb_spin_wait_delay | 6 |

| lock_wait_timeout | 31536000 |

| mysqlx_wait_timeout | 28800 |

| performance_schema_events_waits_history_long_size | 10000 |

| performance_schema_events_waits_history_size | 10 |

| wait_timeout | 28800 |

+---------------------------------------------------+----------+

8 rows in set (0.00sec)

mysql> set innodb_lock_wait_timeout=600;

Query OK,0 rows affected (0.00sec)

mysql> show variables like '%wait%';+---------------------------------------------------+----------+

| Variable_name | Value |

+---------------------------------------------------+----------+

| innodb_lock_wait_timeout | 600 |

| innodb_log_wait_for_flush_spin_hwm | 400 |

| innodb_spin_wait_delay | 6 |

| lock_wait_timeout | 31536000 |

| mysqlx_wait_timeout | 28800 |

| performance_schema_events_waits_history_long_size | 10000 |

| performance_schema_events_waits_history_size | 10 |

| wait_timeout | 28800 |

+---------------------------------------------------+----------+

8 rows in set (0.00sec)

mysql>use course;

Database changed

mysql>drop table temp;

Query OK,0 rows affected (0.03sec)

mysql> create table temp(id int primary key,name varchar(10));

Query OK,0 rows affected (0.17sec)

mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql>commit;

Query OK,0 rows affected (0.00sec)

mysql> set autocommit=0;

Query OK,0 rows affected (0.00sec)

mysql> update temp set name='aa' where id=1;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> update temp set name='aa' where id=2;

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0

session 2：

[root@mysql-master ~]# mysql -u root -p

mysql>use course;

Database changed

mysql> set autocommit=0;

Query OK,0 rows affected (0.00sec)

mysql> update temp set name='aa' where id=1;

session 3：

[root@mysql-master ~]# mysql -u root -p

# processlist 显示有哪些线程在运行

mysql>show full processlist;+----+-----------------+-----------+--------+---------+------+------------------------+--------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-----------------+-----------+--------+---------+------+------------------------+--------------------------------------+

| 4 | event_scheduler | localhost | NULL | Daemon | 535 | Waiting on empty queue | NULL |

| 8 | root | localhost | course | Sleep | 91 | | NULL |

| 10 | root | localhost | NULL | Query | 0 | starting | show full processlist |

| 11 | root | localhost | course | Query | 36 | updating | update temp set name='aa' where id=1 |

+----+-----------------+-----------+--------+---------+------+------------------------+--------------------------------------+

4 rows in set (0.00sec)

# Information_schema.innodb_trx 记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息

mysql> select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked,trx_query frominformation_schema.innodb_trx;+--------+-----------+---------------------+-------------------+-----------------+--------------------------------------+

| trx_id | trx_state | trx_started | trx_tables_locked | trx_rows_locked | trx_query |

+--------+-----------+---------------------+-------------------+-----------------+--------------------------------------+

| 320562 | LOCK WAIT | 2020-05-04 20:16:42 | 1 | 1 | update temp set name='aa' where id=1 |

| 320561 | RUNNING | 2020-05-04 20:15:26 | 1 | 2 | NULL |

+--------+-----------+---------------------+-------------------+-----------------+--------------------------------------+

2 rows in set (0.00sec)

# performance_schema.data_locks 记录了InnoDB中事务的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁

mysql> select engine_lock_id,engine_transaction_id,lock_mode,lock_type,index_name,object_name,lock_data,lock_status,thread_id fromperformance_schema.data_locks;+----------------+-----------------------+---------------+-----------+------------+-------------+-----------+-------------+-----------+

| engine_lock_id | engine_transaction_id | lock_mode | lock_type | index_name | object_name | lock_data | lock_status | thread_id |

+----------------+-----------------------+---------------+-----------+------------+-------------+-----------+-------------+-----------+

| 320562:1172 | 320562 | IX | TABLE | NULL | temp | NULL | GRANTED | 49 |

| 320562:110:4:5 | 320562 | X,REC_NOT_GAP | RECORD | PRIMARY | temp | 1 | WAITING | 49 |

| 320561:1172 | 320561 | IX | TABLE | NULL | temp | NULL | GRANTED | 46 |

| 320561:110:4:5 | 320561 | X,REC_NOT_GAP | RECORD | PRIMARY | temp | 1 | GRANTED | 46 |

| 320561:110:4:6 | 320561 | X,REC_NOT_GAP | RECORD | PRIMARY | temp | 2 | GRANTED | 46 |

+----------------+-----------------------+---------------+-----------+------------+-------------+-----------+-------------+-----------+

5 rows in set (0.01sec)

# sys.innodb_lock_waits 记录了InnoDB中事务之间相互等待锁的信息

mysql> select locked_table_name,locked_index,waiting_pid,waiting_lock_id,blocking_lock_id,blocking_pid fromsys.innodb_lock_waits;+-------------------+--------------+-------------+-----------------+------------------+--------------+

| locked_table_name | locked_index | waiting_pid | waiting_lock_id | blocking_lock_id | blocking_pid |

+-------------------+--------------+-------------+-----------------+------------------+--------------+

| temp | PRIMARY | 11 | 320562:110:4:5 | 320561:110:4:5 | 8 |

+-------------------+--------------+-------------+-----------------+------------------+--------------+

1 row in set (0.08sec)

mysql> select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_history where thread_id in (49,46) order by thread_id,event_id;+-----------+----------+----------------------------+--------------------------------------------------------+

| thread_id | event_id | event_name | sql_text |

+-----------+----------+----------------------------+--------------------------------------------------------+

| 46 | 31 | statement/com/Field List | NULL |

| 46 | 32 | statement/com/Field List | NULL |

| 46 | 33 | statement/com/Field List | NULL |

| 46 | 34 | statement/sql/drop_table | drop table temp |

| 46 | 36 | statement/sql/create_table | create table temp(id int primary key,name varchar(10)) |

| 46 | 38 | statement/sql/insert | insert into temp values(1,'a'),(2,'b'),(3,'c') |

| 46 | 40 | statement/sql/commit | commit |

| 46 | 41 | statement/sql/set_option | set autocommit=0 |

| 46 | 42 | statement/sql/update | update temp set name='aa' where id=1 |

| 46 | 44 | statement/sql/update | update temp set name='bb' where id=2 |

| 49 | 22 | statement/com/Field List | NULL |

| 49 | 23 | statement/com/Field List | NULL |

| 49 | 24 | statement/com/Field List | NULL |

| 49 | 25 | statement/com/Field List | NULL |

| 49 | 26 | statement/com/Field List | NULL |

| 49 | 27 | statement/com/Field List | NULL |

| 49 | 28 | statement/com/Field List | NULL |

| 49 | 29 | statement/com/Field List | NULL |

| 49 | 30 | statement/sql/set_option | set autocommit=0 |

| 49 | 31 | statement/sql/update | update temp set name='aa' where id=1 |

+-----------+----------+----------------------------+--------------------------------------------------------+

20 rows in set (0.01 sec)