InnoDB其他配置
数据库运行期间保存和重新加载buffer pool的方法是：
SET GLOBAL innodb_buffer_pool_dump_now=ON;
SET GLOBAL innodb_buffer_pool_load_now=ON;
查看buffer pool保存和重新加载的进度的方法是：
mysql> show variables like '%dump%'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | innodb_buffer_pool_dump_at_shutdown | ON |#数据库关闭时，可把文件存到文件中。 | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 |#多少个比例的数据，默认是25% +-------------------------------------+-------+ 3 rows in set (0.00 sec) mysql> [root@localhost ~]# ll -h /mysql8/mysql_data/ib_buffer* -rw-r----- 1 mysql mysql 3.9K May 20 19:34 /mysql8/mysql_data/ib_buffer_pool [root@localhost ~]#
当数据库重启时，原本加载在内存里的数据消失了，会出现数据库刚启动的一段时间里，性能降低，可通过
innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_dump_pct参数
• 监控buffer pool的状态情况
通过show engine innodb status命令可以查看buffer pool的运行情况
show engine innodb status• InnoDB change buffer设置
change buffering是MySQL5.5加入的新特性，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效。当修改一个索引块(secondary index)时的数据时，索引块在buffter pool中不存在，修改信息就会被cache在change buffer中，当通过索引扫描把需要的索引块读取到buffer pool时，会和change buffer中修改信息合并，再择机写回disk。
目的还是为了减少随机IO带来性能损耗
Change buffer是作为buffer pool中的一部分存在。
Innodb_change_buffering参数缓存所对应的操作：(update会被认为是delete+insert)
all: 默认值，缓存insert, delete, purges操作
none: 不缓存
inserts: 缓存insert操作
deletes: 缓存delete操作
changes: 缓存insert和delete操作
purges: 缓存后台执行的物理删除操作
innodb_change_buffer_max_size参数配置change buffer在buffer pool中所占的最大百分比，默认是25%，最大可以设置为50%。当MySQL实例中有大量的修改操作时，要考虑增大innodb_change_buffer_max_size。
• 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 *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2021-05-21 14:57:07 0x7f346410a700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 10 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 19763 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 22024 Purge done for trx's n:o < 22022 undo n:o < 0 state: running but idle History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421337999202144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- 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) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 991 OS file reads, 203 OS file writes, 31 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 3 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 23329855 Log buffer assigned up to 23329855 Log buffer completed up to 23329855 Log written up to 23329855 Log flushed up to 23329855 Added dirty pages up to 23329855 Pages flushed up to 23329855 Last checkpoint at 23329855 15 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 390004 Buffer pool size 8192 Free buffers 7078 Database pages 1110 Old database pages 429 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 968, created 142, written 155 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1110, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=9579, Main thread ID=139862511322880 , state=sleeping Number of rows inserted 0, updated 313, deleted 0, read 4406 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) mysql>
查看后台线程运行情况
mysql> use performance_schema; Database changed mysql> select thread_id,name,type,processlist_id from threads order by name; +-----------+---------------------------------------------+------------+----------------+ | thread_id | name | type | processlist_id | +-----------+---------------------------------------------+------------+----------------+ | 26 | thread/innodb/buf_dump_thread | BACKGROUND | NULL | | 23 | thread/innodb/buf_resize_thread | BACKGROUND | NULL | | 25 | thread/innodb/dict_stats_thread | BACKGROUND | NULL | | 27 | thread/innodb/fts_optimize_thread | BACKGROUND | NULL | | 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL | | 4 | thread/innodb/io_log_thread | BACKGROUND | NULL | | 7 | thread/innodb/io_read_thread | BACKGROUND | NULL | | 5 | thread/innodb/io_read_thread | BACKGROUND | NULL | | 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | | 13 | thread/innodb/io_read_thread | BACKGROUND | NULL | | 8 | thread/innodb/io_write_thread | BACKGROUND | NULL | | 11 | thread/innodb/io_write_thread | BACKGROUND | NULL | | 9 | thread/innodb/io_write_thread | BACKGROUND | NULL | | 10 | 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 | | 12 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL | | 21 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | | 20 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | | 24 | thread/innodb/srv_master_thread | BACKGROUND | NULL | | 22 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | | 34 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | | 36 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | | 41 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 40 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 39 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 38 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 35 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 37 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | | 29 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | | 30 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | | 31 | thread/mysqlx/worker | BACKGROUND | NULL | | 28 | 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 |#主线程 | 46 | thread/sql/one_connection | FOREGROUND | 8 | | 48 | thread/sql/one_connection | FOREGROUND | 10 | | 43 | thread/sql/signal_handler | BACKGROUND | NULL | +-----------+---------------------------------------------+------------+----------------+ 43 rows in set (0.00 sec) mysql>
mysql> select * from threads; +-----------+---------------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP | +-----------+---------------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+ | 1 | thread/sql/main | BACKGROUND | NULL | NULL | NULL | mysql | NULL | 23829 | NULL | NULL | NULL | NULL | YES | YES | NULL | 9579 | SYS_default | | 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12342 | SYS_default | | 4 | thread/innodb/io_log_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12343 | SYS_default | | 5 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12344 | SYS_default | | 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12345 | SYS_default | | 7 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12347 | SYS_default | | 8 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12348 | SYS_default | | 9 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12349 | SYS_default | | 10 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12350 | SYS_default | | 11 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12351 | SYS_default | | 12 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12352 | SYS_default | | 13 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12346 | SYS_default | | 14 | thread/innodb/log_checkpointer_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12417 | SYS_default | | 15 | thread/innodb/log_closer_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12418 | SYS_default | | 16 | thread/innodb/log_writer_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12419 | SYS_default | | 17 | thread/innodb/log_flusher_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12420 | SYS_default | | 18 | thread/innodb/log_write_notifier_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12421 | SYS_default | | 19 | thread/innodb/log_flush_notifier_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12422 | SYS_default | | 20 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12510 | SYS_default | | 21 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12511 | SYS_default | | 22 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12512 | SYS_default | | 23 | thread/innodb/buf_resize_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12864 | SYS_default | | 24 | thread/innodb/srv_master_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23831 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12865 | SYS_default | | 25 | thread/innodb/dict_stats_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23831 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12867 | SYS_default | | 26 | thread/innodb/buf_dump_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12866 | SYS_default | | 27 | thread/innodb/fts_optimize_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23831 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12868 | SYS_default | | 28 | thread/mysqlx/worker | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 12889 | SYS_default | | 29 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23829 | NULL | NULL | 1 | NULL | YES | YES | Plugin | 12892 | SYS_default | | 30 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 12891 | SYS_default | | 31 | thread/mysqlx/worker | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 12890 | SYS_default | | 34 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23830 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12903 | SYS_default | | 35 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23830 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12904 | SYS_default | | 36 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12903 | SYS_default | | 37 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23830 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12905 | SYS_default | | 38 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12904 | SYS_default | | 39 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12905 | SYS_default | | 40 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 23830 | NULL | NULL | NULL | NULL | YES | YES | NULL | 12906 | SYS_default | | 41 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 12906 | SYS_default | | 42 | thread/sql/event_scheduler | FOREGROUND | 4 | NULL | NULL | NULL | Sleep | NULL | Waiting on empty queue | NULL | 1 | NULL | YES | YES | NULL | 12979 | SYS_default | | 43 | thread/sql/signal_handler | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | YES | YES | NULL | 12980 | SYS_default | | 44 | thread/sql/compress_gtid_table | FOREGROUND | 6 | NULL | NULL | NULL | Daemon | 23829 | Suspending | NULL | 1 | NULL | YES | YES | NULL | 12981 | SYS_default | | 46 | thread/sql/one_connection | FOREGROUND | 8 | root | localhost | performance_schema | Query | 0 | Sending data | select * from threads | 1 | NULL | YES | YES | Socket | 19623 | USR_default | | 48 | thread/sql/one_connection | FOREGROUND | 10 | root | localhost | NULL | Sleep | 398 | NULL | NULL | NULL | NULL | YES | YES | Socket | 23555 | USR_default | +-----------+---------------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+------------------------+-----------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+ 43 rows in set (0.00 sec)
mysql> show full processlist; +----+-----------------+-----------+--------------------+---------+-------+------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+--------------------+---------+-------+------------------------+-----------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 23800 | Waiting on empty queue | NULL | | 8 | root | localhost | performance_schema | Query | 0 | starting | show full processlist | | 11 | root | localhost | NULL | Sleep | 3 | | NULL | +----+-----------------+-----------+--------------------+---------+-------+------------------------+-----------------------+ 3 rows in set (0.00 sec) mysql>
mysql> kill 11;#根据ID可以kill掉线程 Query OK, 0 rows affected (0.00 sec) mysql> 在另一个session上，会提示No connection mysql> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 12 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | company | | course | | information_schema | | mysql | | performance_schema | | school | | sys | | test_20200702 | | test_20200702_01 | +--------------------+ 9 rows in set (0.01 sec)
• 使用Linux异步IO
InnoDB在Linux平台使用异步IO子系统完成数据文件页的读写请求，可以通过
innodb_user_native_aio参数控制，默认是开启状态，并且需要libaio系统库支持。
InnoDB主线程配置
InnoDB的主线程在后台承担了诸多的任务，绝大多数是和IO操作相关的，比如将buffer pool中的修改后的数据刷新的磁盘文件中。
Innodb_io_capacity参数设置了InnoDB的整体IO能力。该参数应该被设置为等同于操作系统每秒的IO操作数量。该参数可以设置为100及以上的任意数值，默认值是200。其中设置为100相当于7200RPM的磁盘性能。
• InnoDB purge配置
InnoDB的purge操作是一类垃圾回收操作，是由一个或多个独立线程自动执行。通过innodb_purge_threads参数设置purge线程的数量，如果DML操作比较复杂且涉及到多个表时，则可以考虑增加此值，最大可以设置为32。
事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页.
MySQL执行SQL会经过SQL解析和查询优化的过程，解析器将SQL分解成数据结构并传递到后续步骤，查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如何执行，依赖于数据库的统计信息
InnoDB 优化器统计信息配置
Innodb表的优化器统计信息分为永久和非永久两种。永久的优化器统计信息即使是服务器重启的情况下也会存在，其用来选出更优的执行计划以便提供更好的查询性能。
通过配置innodb_stats_auto_recalc参数来控制统计信息是否在表发生巨大变化（超过10%的行）之后是否自动更新，但由于自动更新统计信息本身是异步的，所以有时未必能马上更新，这时可以执行analyze table语句来同步更新统计信息。
Create table和alter table语句中的Stats_persistent, stats_auto_recalc, stats_sample_pages子句可用来配置单个表的优化器统计信息规则
Stats_persistent用来指定是否对此表开启永久统计资料，1代表开启，0代表不开启。当开启之后，可以执行analyze table命令来收集统计资料。
Stats_auto_recalc表示是否自动对表的永久统计资料进行重新计算，默认值和全局参数innodb_stats_auto_recalc一致。1代表当表中数据10%以上更新时重新计算，0代表不自动更新，而是通过analyze table命令重新计算。
Stats_sample_pages表示当计算索引列的统计资料是需要的索引页的样本数量
mysql> CREATE TABLE `t2` ( -> `id` int(8) NOT NULL auto_increment, -> `data` varchar(255), -> `date` datetime, -> PRIMARY KEY (`id`), -> INDEX `DATE_IX` (`date`) -> ) ENGINE=InnoDB, -> STATS_PERSISTENT=1, -> STATS_AUTO_RECALC=1, -> STATS_SAMPLE_PAGES=25;
mysql> show create table t2; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `id` int(8) NOT NULL AUTO_INCREMENT, `data` varchar(255) COLLATE utf8_bin DEFAULT NULL, `date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `DATE_IX` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=25 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
优化器永久统计资料数据在系统表mysql.innodb_table_stats和mysql.innodb_index_stats表中存储，这两个表中有个字段last_update可以用来判断统计信息最后更改时间。这两个表的数据也可以被手工更改。当手工更改完数据之后，要执行flush table 表名命令来重新load此表的统计资料。innodb_table_stats表中每个目标表一行记录，而innodb_index_stats表中每个索引会有多条记录Innodb_table_stats表结构：
表统计信息:
innodb_table_stats表存储的是表维度的统计信息，innodb_table_stats表有6个字段，他们的各字段相关定义以及含义如下表所示：
字段名 字段类型 字段含义
database_name verchar(64) 统计信息所属表的数据库名
table_name verchar(64) 统计信息所属的表名
last_update timestamp 统计信息最后一次更新的时间
n_rows bigint(20) unsigned 表所包含的行数
clustered_index_size bigint(20) unsigned 聚集索引的页的数量
sum_of_other_index_size bigint(20) unsigned 其他索引所占的页的数量
mysql> select * from mysql.innodb_table_stats where database_name='school' and table_name='artile_list'; +---------------+-------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+-------------+---------------------+--------+----------------------+--------------------------+ | school | artile_list | 2021-05-22 10:18:19 | 30391 | 1254 | 97 | +---------------+-------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where table_name='students'G *************************** 1. row *************************** database_name: course table_name: students last_update: 2020-07-04 18:34:08 n_rows: 0 clustered_index_size: 1 sum_of_other_index_sizes: 1 1 row in set (0.00 sec) mysql>
索引统计信息
innidb_index_stats表存储的是索引维度的统计信息，innodb_index_stats表有8个字段，他们的各字段相关定义以及含义如下表所示：
innidb_index_stats表存储的是索引维度的统计信息，innodb_index_stats表有8个字段，他们的各字段相关定义以及含义如下表所示：
Innodb_index_stats表结构：
字段名 字段类型 字段含义
database_name varchar(64) 统计信息所属表的数据库名
table_name varchar(64) 统计信息所属表名
index_name varchar(64) 统计信息所属索引名
last_update timestamp 统计信息更新的时间
stat_name varchar(64) 统计信息名称
stat_value bigint(20) unsigned 统计值
sample_size bigint(20) unsigned 采样大小
stat_description varchar(64) 统计描述信息
database_name varchar(64) 统计信息所属表的数据库名
table_name varchar(64) 统计信息所属表名
index_name varchar(64) 统计信息所属索引名
last_update timestamp 统计信息更新的时间
stat_name varchar(64) 统计信息名称
stat_value bigint(20) unsigned 统计值
sample_size bigint(20) unsigned 采样大小
stat_description varchar(64) 统计描述信息
Stat_name=n_diff_pfxNN参数：当是n_diff_pfx01时stat_value列表示索引第一列上的区别值有几个，当是n_diff_pfx02
时stat_value列表示索引第一、二列上的区别值有几个，以此类推。而stat_description列显示了对应的逗号可开的索引
列值。
默认情况下永久优化器统计信息的属性是开启的，innodb_stats_persistent=ON
非永久优化器统计信息会在每次服务器重启或者其他一些操作时被清理。
mysql> select * from mysql.innodb_index_stats where database_name='school' and table_name='artile_list'; +---------------+-------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+-------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+ | school | artile_list | PRIMARY | 2021-05-22 10:18:19 | n_diff_pfx01 | 0 | 2 | webaddr | | school | artile_list | PRIMARY | 2021-05-22 10:18:19 | n_diff_pfx02 | 30391 | 20 | webaddr,article_name | | school | artile_list | PRIMARY | 2021-05-22 10:18:19 | n_leaf_pages | 1206 | NULL | Number of leaf pages in the index | | school | artile_list | PRIMARY | 2021-05-22 10:18:19 | size | 1254 | NULL | Number of pages in the index | | school | artile_list | article_name | 2021-05-22 10:18:19 | n_diff_pfx01 | 30297 | 20 | article_name | | school | artile_list | article_name | 2021-05-22 10:18:19 | n_diff_pfx02 | 30408 | 20 | article_name,webaddr | | school | artile_list | article_name | 2021-05-22 10:18:19 | n_leaf_pages | 96 | NULL | Number of leaf pages in the index | | school | artile_list | article_name | 2021-05-22 10:18:19 | size | 97 | NULL | Number of pages in the index | +---------------+-------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0.00 sec) mysql
优化器统计信息会被存储在磁盘上，通过设置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的表数据被修改时也会更新。
• 索引页之间合并阈值
通过配置merge_threshold来确保当索引页的数据由于删除操作或者修改操作低于阈值，InnoDB会将此索引页和邻近的索引页合并。默认值是50，取值范围是1到50。Merge_threshold参数可以定义在表上，也可以定义在一个独立的索引上。
Merge_threshold参数可以定义在表上，也可以定义在一个独立的索引上。
CREATE TABLE t1 (
id INT,
KEY id_index (id)
) COMMENT='MERGE_THRESHOLD=45';
CREATE TABLE t1 (
id INT,
KEY id_index (id)
);
ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';
CREATE TABLE t1 (
id INT,
KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
);
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
评估merge_threshold参数合理的方法是查看innodb_metrics表里的相关参数，确保发生了
较少的索引页合并且合并请求和成功合并的数量相当
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME like '%index_page_merge%';
+-----------------------------+----------------------------------------+
| NAME | COMMENT |
+-----------------------------+----------------------------------------+
| index_page_merge_attempts | Number of index page merge attempts |
| index_page_merge_successful | Number of successful index page merges |
+-----------------------------+----------------------------------------+
• 重置InnoDB系统表空间
最简单的增加系统表空间的办法就是在初始化阶段配置数据文件的自增长，通过配置最后一
个文件的autoextend属性，当数据文件空间不足时默认自动增长64M大小。也可以通过修改
innodb_autoextend_increment参数修改自动增长的大小。
也可以通过增加另一个数据文件方法扩展表空间，步骤如下：
关闭MySQL
检查配置的最后一个数据文件是否是autoextend，如果是则根据当前数据文件的大小去掉自
动扩展属性，改成当前大小
在配置文件的innodb_data_file_path参数里增加一个新的数据文件，选择是否自动扩展
启动MySQL
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
####改成
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
减小系统表空间大小的方法如下：
Mysqldump出所有的InnoDB表，包括mysql系统数据库下的五个表
mysql> select table_name from information_schema.tables where table_schema='mysql' and
engine='InnoDB';
+----------------------+
| table_name |
+----------------------+
| innodb_index_stats |
| innodb_table_stats |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
+----------------------+
关闭MySQL
删除所有InnoDB的数据文件和日志文件，包括*.ibd和ib_log文件，还有在MySQL库文件夹下的*.ibd文件
删除所有.frm的InnoDB表文件
在配置文件里配置新的表空间文件
启动MySQL
导入备份出的dump文件
重置InnoDB redo log文件大小
关闭MySQL
通过innodb_log_file_size更改文件大小，通过innodb_log_files_in_group更改
文件数量
启动MySQL
innodb_log_file_size=30M
innodb_log_files_in_group=3
• 配置单表数据文件表空间
InnoDB的单表数据文件表空间代表每个InnoDB表的数据和索引数据都存放在单独的.ibd数据文件中，每个.ibd数据文件代表独立的表空间。此属性通过innodb_file_per_table配置。
此配置的主要优势：
当删除表或者truncate表的时候，意味着对磁盘空间可以回收。而共享表空间时删除一个表时空间不会释放而只是文件里有空闲空间Truncate table命令要比共享表空间快
通过定义create table …data directory=绝对路径，可以将特定的表放在特定的磁盘或者存储空间可以将单独的表物理拷贝到另外的MySQL实例中
此配置的劣势：
每个表都有未使用的空间，意味着磁盘空间有些浪费
启动单独表空间的方式如下：
[mysqld]
innodb_file_per_table=1
当设置innodb_file_per_table=0时，所有创建的新表都会放置到共享表空间里，除非在create table命令里显示的使用tablespace选项。将已经存在于共享表空间的表修改为独立表空间的方法：
SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;
通过命令create table … data directory=绝对路径可以将单表数据文件创建在另外的目录里。在指定的绝对路径下，会创建数据库名相同的文件夹，里面含有此表的.ibd文件，同时在MySQL的默认数据文件下的数据库名文件夹下会创建table_name.isl文件包含了此表的路径，相当于link文件。
mysql> USE test;
Database changed
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table students2(id int,name varchar(100));
mysql> set global innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table students2 engine=innodb;
root@ip-172-31-18-152:/usr/local/mysql/data/course# ls students2.*
students2.frm students2.ibd
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';
Query OK, 0 rows affected (0.03 sec)
# MySQL creates a .ibd file for the new table in a subdirectory that corresponding
# to the database name
db_user@ubuntu:~/alternative/directory/test$ ls
t1.ibd
# MySQL creates a .isl file containing the path name for the table in a directory
# beneath the MySQL data directory
db_user@ubuntu:~/mysql/data/test$ ls
db.opt t1.frm t1.isl
当没有开启innodb_file_per_table时，可以将tablespace和data directory两个参数配合使用
CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
DATA DIRECTORY = '/alternative/directory';
不管是出于备份复制还是什么原因要将单表复制到另外的数据库实例下，可以使用传输表空
间的方法
在原实例下创建表
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
在目标实例下创建表
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
在目标实例下将表的表空间属性去除
mysql> ALTER TABLE t DISCARD TABLESPACE;
此命令对有外键的表不支持，必须首先执行foreign_key_checks=0
在原实例下表加锁仅允许读操作，并生成.cfg元文件
mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;
将.ibd和.cfg文件拷贝到目标实例的指定目录下
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination
server:/path/to/datadir/test
• 原实例下释放锁
mysql> use test;
mysql> UNLOCK TABLES;
目标实例下执行导入表空间操作
mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;
• 设置Undo log独立表空间
默认情况下undo log是存储在系统表空间里，我们也可以将其存放在一个或多个独立表空
间下。
Innodb_undo_tablespaces参数定义了有多少个undo表空间，此参数只能在建立MySQL实例
时被配置
innodb_undo_directory参数定义了undo表空间的存放路径
innodb_undo_logs参数定义了回滚段的数量
mysql> show variables like '%innodb_undo%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
Innodb_undo_log_truncate参数决定是否开启undo表空间清空
mysql> SET GLOBAL innodb_undo_log_truncate=ON;
当设置了此参数为ON后，则代表undo文件大小超过innodb_max_undo_log_size（默认值是
128M）的都标记为清空
mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
| 1073741824 |
mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;
Query OK, 0 rows affected (0.00 sec)
当标记为清空后，回滚段标记为非激活状态表示不接收新的事务，而已存在的事务会等到完
成；然后通过purge操作将回滚段空间释放；当undo表空间的所有回滚段都释放后，表空间就
会清空成初始10M大小；然后回滚段重新变成激活状态以接收新的事务
• InnoDB普通表空间
通过create tablespace命令可以创建一个共享的InnoDB表空间，和系统表空间一样，多个
表可以在此表空间上存储数据，此表空间的数据文件可以放置在任意的文件夹下。
CREATE TABLESPACE tablespace_name
ADD DATAFILE 'file_name'
[FILE_BLOCK_SIZE = value]
[ENGINE [=] engine_name]
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; ##创建在
MySQL数据目录下
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd'
Engine=InnoDB;
当创建完表空间之后，就可以通过create table …tablespace或者alter table …
tablespace命令将表增加到此表空间上
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
mysql> ALTER TABLE t2 TABLESPACE ts1;
通过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语句的执行都会导致此表会重建，即使表空间的属性和之前是
一样的。
mysql> create tablespace ts1 add datafile
'/usr/local/mysql/data/ts1.ibd';
Query OK, 0 rows affected (0.02 sec)
mysql> use course;
mysql> create table students4(id int,name varchar(10)) tablespace ts1;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table students4 tablespace=innodb_file_per_table;
root@ip-172-31-18-152:/usr/local/mysql/data/course# ls students4.*
students4.frm students4.ibd
当删除一个普通表空间时，首先需要保证此表空间上的所有表都被删除，否则会报
错。删除表空间是用drop tablespace语句来执行。Drop database的动作会删除所
有的表，但创建的tablespace不会被自动删除，必须通过drop tablespace显示执
行。
普通表空间不支持临时表，而且也不支持alter table … discard tablespace和
alter table …import tablespace命令。
mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1
mysql> drop table temp123;
Query OK, 0 rows affected (0.00 sec)
mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.01 sec)
• 创建InnoDB表
通过create table语句创建InnoDB表，因为默认存储引擎就是InnoDB，所以不需要在创建表
的语句最后指定engine=innodb。
CREATE TABLE `students` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(10) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL
)
InnoDB的表数据和索引数据默认是存储在系统表空间中，但可以通过开启
innodb_file_per_table选项将表数据和索引数据存放在独立表空间中。当表创建完之后，会
在表所在的数据库文件夹里创建.frm文件用来存储表的结构，系统表空间对应的.ibdata文件
存储数据文件，而当开启独立表空间时，则会在表所在的数据库文件夹里创建.ibd用来存储
表数据和索引数据
通过show table status语句可以查看InnoDB的表属性
mysql> show table status like 'students'G
*************************** 1. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-05-05 23:10:11
Update_time: 2017-05-05 23:25:29
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
• 修改表的存储引擎
通过alter table语句修改已有表的存储引擎
ALTER TABLE table_name ENGINE=InnoDB;
自增长字段设置
当对InnoDB表设置了自增长字段之后，表会在内存中保存一个自增长计数器。
默认情况下自增长字段的初始值是1，但也可以通过配置auto_increment_offset参数将所有
的自增长字段初始值设置为另外的值，而当表中插入数值时，InnoDB会求出当前表中的该列
的最大值，然后在此基础上加1作为插入的数据。默认是以+1为增长的进度，但也可以通过
auto_increment_increment配置所有自增长字段的自定义增长进度。
• InnoDB表主要的限制
InnoDB表目前只支持最多1017个列
InnoDB表目前支持最大64个二级索引
多列索引目前支持最大16个列
如果表中不存在text或者blob类型字段时，行数据整体的最大长度是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. You have to change some
columns to TEXT or BLOBs