ACID模型：
ACID模型是关系型数据库普遍支持的事务模型，用来保证数据的一致性，其中的ACID分别代表：
A：atomicity原子性：事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生
C：consistency一致性：事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性
I：isolation独立性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果
D：durability持续性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
为保证并发操作和回滚操作，InnoDB会将修改前的数据存放在回滚段中。InnoDB会在数据库的每一行上额外增加三个字段以实现多版本控制，第一个字段是DB_TRX_ID用来存放针对该行最后一次执行insert、update操作的事务ID，而delete操作也会被认为是update，只是会有额外的一位来代表事务为删除操作；第二个字段是DB_ROLL_PTR指针指向回滚段里对应的undo日志记录；第三个字段是DB_ROW_ID代表每一行的行ID。回滚段中的undo日志记录只有在事务commit提交之后才会被丢弃，为避免回滚段越来越大，要注意及时执行commit命令
InnoDB线程：
InnoDB线程并发度配置
InnoDB利用操作系统的线程技术达到多线程实现。
Innodb_thread_concurrency 参数限制同时执行的线程数。默认值是0代表没有限制。
Innodb_thread_sleep_delay 参数确定
InnoDB后台IO线程配置
通过配置innodb_read_io_threads和innodb_write_io_threads参数来指定后台读和写数据页的线程的个数，默认值是4，容许的取值范围是1-64。
mysql> show engine innodb statusG
FILE I/O
——–
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
使用Linux异步IO
InnoDB在Linux平台使用异步IO子系统完成数据文件页的读写请求，可以通过
innodb_user_native_aio 参数控制，默认是开启状态，并且需要libaio系统库支持。
InnoDB主线程配置
InnoDB的主线程在后台承担了诸多的任务，绝大多数是和IO操作相关的，比如将bufferpool中的修改后的数据刷新的磁盘文件中。
Innodb_io_capacity参数设置了InnoDB的整体IO能力。该参数应该被设置为等同于操作系统每秒的IO操作数量。该参数可以设置为100及以上的任意数值，默认值是200。其中设置为100相当于7200RPM的磁盘性能。
查看Innodb线程：
# 查看当前MySQL实例所有线程
# 线程ID 当前进程ID与show processlist查看的为对应关系
mysql> select thread_id,name,type,processlist_id from performance_schema.threads order by name;
+———–+———————————————+————+—————-+
| thread_id | name | type | processlist_id |
+———–+———————————————+————+—————-+
| 24 | thread/innodb/buf_dump_thread | BACKGROUND | NULL |
| 23 | thread/innodb/buf_resize_thread | BACKGROUND | NULL |
| 26 | thread/innodb/dict_stats_thread | BACKGROUND | NULL |
| 27 | thread/innodb/fts_optimize_thread | BACKGROUND | NULL |
| 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL |
| 5 | thread/innodb/io_log_thread | BACKGROUND | NULL |
| 8 | thread/innodb/io_read_thread | BACKGROUND | NULL |
| 7 | thread/innodb/io_read_thread | BACKGROUND | NULL |
| 6 | thread/innodb/io_read_thread | BACKGROUND | NULL |
| 4 | thread/innodb/io_read_thread | BACKGROUND | NULL |
| 12 | thread/innodb/io_write_thread | BACKGROUND | NULL |
| 11 | thread/innodb/io_write_thread | BACKGROUND | NULL |
| 10 | thread/innodb/io_write_thread | BACKGROUND | NULL |
| 9 | thread/innodb/io_write_thread | BACKGROUND | NULL |
| 14 | thread/innodb/log_checkpointer_thread | BACKGROUND | NULL |
| 15 | thread/innodb/log_closer_thread | BACKGROUND | NULL |
| 19 | thread/innodb/log_flush_notifier_thread | BACKGROUND | NULL |
| 17 | thread/innodb/log_flusher_thread | BACKGROUND | NULL |
| 18 | thread/innodb/log_write_notifier_thread | BACKGROUND | NULL |
| 16 | thread/innodb/log_writer_thread | BACKGROUND | NULL |
| 13 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL |
| 22 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL |
| 20 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL |
| 25 | thread/innodb/srv_master_thread | BACKGROUND | NULL |
| 21 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL |
| 34 | thread/innodb/srv_purge_thread | BACKGROUND | NULL |
| 35 | thread/innodb/srv_purge_thread | BACKGROUND | NULL |
| 36 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 37 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 38 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 39 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 40 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 41 | thread/innodb/srv_worker_thread | BACKGROUND | NULL |
| 30 | thread/mysqlx/acceptor_network | BACKGROUND | NULL |
| 31 | thread/mysqlx/acceptor_network | BACKGROUND | NULL |
| 28 | thread/mysqlx/worker | BACKGROUND | NULL |
| 29 | thread/mysqlx/worker | BACKGROUND | NULL |
| 44 | thread/sql/compress_gtid_table | FOREGROUND | 6 |
| 42 | thread/sql/event_scheduler | FOREGROUND | 4 |
| 1 | thread/sql/main | BACKGROUND | NULL |
| 65 | thread/sql/one_connection | FOREGROUND | 27 |
| 43 | thread/sql/signal_handler | BACKGROUND | NULL |
+———–+———————————————+————+—————-+
42 rows in set (0.00 sec)
# Id与processlist_id为对应关系
mysql> show processlist;
+—-+—————–+———–+——————–+———+——–+————————+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+—————–+———–+——————–+———+——–+————————+——————+
| 4 | event_scheduler | localhost | NULL | Daemon | 536852 | Waiting on empty queue | NULL |
| 27 | root | localhost | performance_schema | Query | 0 | starting | show processlist |
+—-+—————–+———–+——————–+———+——–+————————+——————+
2 rows in set (0.00 sec)
用户连接线程：
mysql> select thread_id,name,type,processlist_id from performance_schema.threads where name=’thread/sql/one_connection’;
+———–+—————————+————+—————-+
| thread_id | name | type | processlist_id |
+———–+—————————+————+—————-+
| 65 | thread/sql/one_connection | FOREGROUND | 27 |
+———–+—————————+————+—————-+
1 row in set (0.00 sec)
InnoDB优化器：
优化器永久统计资料数据在系统表mysql.innodb_table_stats和mysql.innodb_index_stats表中存储，这两个表中有个字段last_update可以用来判断统计信息最后更改时间。这两个表的数据也可以被手工更改。当手工更改完数据之后，要执行flush table 表名命令来重新load此表的统计资料。innodb_table_stats表中每个目标表一行记录，而innodb_index_stats表中每个索引会有多条记录.
Innodb表永久统计资料是存放在哪两张表内？
mysql> select * from mysql.innodb_table_stats;
+—————+—————–+———————+——–+———————-+————————–+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+—————+—————–+———————+——–+———————-+————————–+
| course | course | 2019-10-17 13:29:46 | 5 | 1 | 1 |
| course | dept | 2019-10-18 11:00:16 | 2 | 1 | 0 |
| course | score | 2019-10-17 13:29:46 | 27 | 1 | 0 |
| course | students | 2019-10-17 13:29:46 | 8 | 1 | 1 |
| course | students_2 | 2019-10-17 13:29:57 | 10016 | 23 | 11 |
| course | t1 | 2019-10-18 13:46:21 | 0 | 1 | 0 |
| course | teacher | 2019-10-18 10:57:38 | 5 | 1 | 1 |
| course | teacher_history | 2019-10-17 13:30:07 | 2 | 1 | 0 |
| course | temp | 2019-10-18 15:29:12 | 3 | 1 | 0 |
| mysql | component | 2019-10-09 14:45:13 | 0 | 1 | 0 |
| mysql | gtid_executed | 2019-10-09 14:45:13 | 0 | 1 | 0 |
| sys | sys_config | 2019-10-09 14:45:14 | 6 | 1 | 0 |
+—————+—————–+———————+——–+———————-+————————–+
12 rows in set (0.00 sec)
# 通过stat_value字段查看数据区别数，也就是不重复的数据（越大越好）
mysql> select * from mysql.innodb_index_stats where table_name=’students’;
+—————+————+————+———————+————–+————+————-+———————————–+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+—————+————+————+———————+————–+————+————-+———————————–+
| course | students | PRIMARY | 2019-10-17 13:29:46 | n_diff_pfx01 | 8 | 1 | sid |
| course | students | PRIMARY | 2019-10-17 13:29:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| course | students | PRIMARY | 2019-10-17 13:29:46 | size | 1 | NULL | Number of pages in the index |
| course | students | for_1 | 2019-10-17 13:29:46 | n_diff_pfx01 | 3 | 1 | dept_id |
| course | students | for_1 | 2019-10-17 13:29:46 | n_diff_pfx02 | 8 | 1 | dept_id,sid |
| course | students | for_1 | 2019-10-17 13:29:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| course | students | for_1 | 2019-10-17 13:29:46 | size | 1 | NULL | Number of pages in the index |
+—————+————+————+———————+————–+————+————-+———————————–+
7 rows in set (0.00 sec)
优化器统计信息会被存储在磁盘上，通过设置innodb_stats_persistent=ON参数（默认）。
MySQL的查询优化器会基于评估好的统计资料选择合适的索引参与到执行计划中，而类似analyze table的语句会从索引中随机选取数据页参与到每个索引的基数评估中。而参数innodb_stats_persistent_sample_pages决定了参与评估的数据页的数量，默认值是20。当语句执行的执行计划不是最优选择时，则考虑增加此参数，以便获得正确的统计资料。
当设置innodb_stats_persistent=OFF参数或者对单个表设置stats_persistent=0时，对应的统计资料就仅存在于内存中而非磁盘上，当服务器重启之后统计资料丢失。当然此类统计资料也可以周期性的更新。
比如执行analyze table语句手动刷新统计资料，或者在innodb_stats_on_metadata选项打开之后执行show table status/show index或查询information_schema.tables/statistics表时非永久统计资料会自动更新，当InnoDB检测到1/16的表数据被修改时也会更新。
查找数据库有哪些大表：
# 通过n_rows字段找出数据库实例中的大表
mysql> select database_name,table_name,last_update,n_rows from mysql.innodb_table_stats;
+—————+—————–+———————+——–+
| database_name | table_name | last_update | n_rows |
+—————+—————–+———————+——–+
| course | course | 2019-10-17 13:29:46 | 5 |
| course | dept | 2019-10-18 11:00:16 | 2 |
| course | score | 2019-10-17 13:29:46 | 27 |
| course | students | 2019-10-17 13:29:46 | 8 |
| course | students_2 | 2019-10-17 13:29:57 | 10016 |
| course | t1 | 2019-10-18 13:46:21 | 0 |
| course | teacher | 2019-10-18 10:57:38 | 5 |
| course | teacher_history | 2019-10-17 13:30:07 | 2 |
| course | temp | 2019-10-18 15:29:12 | 3 |
| mysql | component | 2019-10-09 14:45:13 | 0 |
| mysql | gtid_executed | 2019-10-09 14:45:13 | 0 |
| sys | sys_config | 2019-10-09 14:45:14 | 6 |
+—————+—————–+———————+——–+
12 rows in set (0.00 sec)
# 手动刷新表数据信息
mysql> analyze table course.temp;
+————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————-+———+———-+———-+
| course.temp | analyze | status | OK |
+————-+———+———-+———-+
1 row in set (0.02 sec)
表空间转换：
通过alter table命令可以将InnoDB表在系统表空间、独立表空间和普通表空间之间转化：
ALTER TABLE tbl_name TABLESPACE [=] tablespace_name ##从系统表空间或者独立表空间上转移到普通表空间
ALTER TABLE tbl_name … TABLESPACE [=] innodb_system ##从普通表空间或者独立表空间上转移到系统表空间
ALTER TABLE tbl_name … TABLESPACE [=] innodb_file_per_table ##从系统表空间或者普通表空间转移到独立表空间
Alter table … tablespace语句的执行都会导致此表会重建，即使表空间的属性和之前是一样的。
创建表空间：
# 因为有t1表，然后t1.ibd文件时存在的才可以创建表空间
mysql> create tablespace temp_tablespace add datafile ‘t1.ibd’;
Query OK, 0 rows affected (0.02 sec)
查看所有表空间：
mysql> select * from information_schema.innodb_tablespaces;
+————+————————+——-+———————-+———–+—————+————+—————+———–+—————-+—————-+—————+————+——–+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+————+————————+——-+———————-+———–+—————+————+—————+———–+—————-+—————-+—————+————+——–+
| 4294967294 | mysql | 18432 | Any | 16384 | 0 | General | 4096 | 24117248 | 24117248 | 8.0.16 | 1 | N | normal |
| 4294967293 | innodb_temporary | 4096 | Compact or Redundant | 16384 | 0 | System | 0 | 0 | 0 | 8.0.16 | 1 | N | normal |
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 0 | 0 | 0 | 8.0.16 | 1 | N | active |
| 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 0 | 0 | 0 | 8.0.16 | 1 | N | active |
| 1 | sys/sys_config | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.16 | 1 | N | normal |
| 50 | course/course | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 131072 | 114688 | 8.0.16 | 1 | N | normal |
| 51 | course/dept | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.16 | 1 | N | normal |
| 52 | course/score | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.16 | 1 | N | normal |
| 53 | course/students | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 131072 | 114688 | 8.0.16 | 1 | N | normal |
| 54 | course/students_2 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 638976 | 638976 | 8.0.16 | 1 | N | normal |
| 56 | course/teacher_history | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.16 | 1 | N | normal |
| 58 | course/teacher | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 131072 | 114688 | 8.0.16 | 1 | N | normal |
| 59 | course/t1 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.16 | 1 | N | normal |
| 63 | temp_tablespace | 18432 | Any | 16384 | 0 | General | 4096 | 114688 | 65536 | 8.0.16 | 1 | N | normal |
+————+————————+——-+———————-+———–+—————+————+—————+———–+—————-+—————-+—————+————+——–+
14 rows in set (0.00 sec)
删除表空间：
# 如果有数据表在使用temp_tablespace这个表空间，要提前把该表使用的表空间转换到其他表空间或者独立表空间才可以删除。
mysql> drop tablespace temp_tablespace;
Query OK, 0 rows affected (0.01 sec)
InnoDB表主要限制：
InnoDB表目前只支持最多1017个列
InnoDB表目前支持最大64个二级索引
多列索引目前支持最大16个列
如果表中不存在text或者blob类型字段时，行数据整体的最大长度是65535个字节
# 默认是utf8mb4字符集，一个字符占用4个字节（ 一行数据最多存储65535字节 ）
mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000), c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),f VARCHAR(10000), g VARCHAR(10000))ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> select 65535/4;
+————+
| 65535/4 |
+————+
| 16383.7500 |
+————+
1 row in set (0.01 sec)
mysql> CREATE TABLE t (a VARCHAR(5000), b VARCHAR(10000))ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)