InnoDB的主索引是聚簇索引，索引与数据公用表空间。对于InnoDB来说，数据就是索引，索引就是数据。InnoDB缓存机制与MyISAM的最大区别在于，InnoDB不仅缓存索引，同时还会缓存数据。

一、数据库常用参数

MYSQL数据库的参数配置一般在my.ini配置(部分参数也可以用set  global 参数名=值 做临时调整，重启后失效)，配置完后需要重启数据库才生效。

 参数1：slow_query_log = 0|1

说明:开关慢查询日志。slow_query_log_file=为存放路径；long_query_time =记录超过的时间，默认为10s。

参数2：join_buffer_size = MB

说明：join buffer存放基于每thread的连接表信息，连接时，只需访问join buffer,不需要再去有并发机制保护的cache.

参数3：Sort_Buffer_Size = MB

说明:Sort_Buffer_Size 是一个connection级参数，每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官网文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation” 

参数4: binlog_format = STATEMENT|ROW|MIXED

说明：日志格式

1)STATEMENT模式(SBR)

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)。

2)ROW模式(RBR)

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3)MIXED模式(MBR)

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

参数5：binlog_cache_size = MB

说明：默认大小是37268即32K.根据事务需要调整大小。该参数表示在事务中容纳二进制日志sql语句的缓存大小。二进制日志缓存，是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间。

参数6:Max_binlog_cache_size = MB

说明：默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。

参数7:Max_binlog_size = GB/MB

说明：Max_binlog_size: 1073741824=1G ，binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。

参数8:expire_logs_days = N

说明:设置binlog老化日期；有大致三种情况引发日志切换：binlog大小超过max_binlog_size；手动执行flush logs；重新启动时(MySQL将会new一个新文件用于记录binlog)

参数9:innodb_file_per_table = 0|1

说明：参数值为1，表示对每张表使用单独的 innoDB 文件

参数10:innodb_log_file_size = GB/MB

说明:对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要;值太大了会让恢复过程变慢.

参数11：innodb_log_files_in_group = N

说明:该变量控制日志文件数。默认值为2。日志是以顺序的方式写入。

参数12:innodb_flush_method =  

说明:设置InnoDB同步IO的方式：Default (fsync);O_SYNC (以sync模式打开文件，通常比较慢);O_DIRECT(在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering(mysql buffering 和OS buffering))

参数13:transaction_isolation = READ-UNCOMMITTED | READ-COMMITTED |REPEATABLE-READ | SERIALIZABLE

说明:设定事务隔离级别

1)未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

2)提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

3)可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

4)串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

参数14:character-set-server = utf8|utf8mb4

说明:设定字符集，utf8存3个字节，utf8mb4存4个字节。

参数15：innodb_buffer_pool_size = Gb/MB

说明：此参数类似于oracle的SGA配置，当主机做为mysql数据库服务器时，一般配置为整机内存的60%~80%。

参数16：innodb_buffer_pool_instances=N

说明：内存缓冲池实例数，将innodb_buffer_pool_size配置的内存分割成N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值《=服务器CPU的个数。

参数17：max_connections = xxxx

说明：最大连接数，当数据库面对高并发时，这个值需要调节为一个合理的值，才满足业务的并发要求，避免数据库拒绝连接。

参数18：max_user_connections=xxxx

说明：设置单个用户的连接数。

参数19：innodb_log_buffer_size =xxxxx

说明：日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据日志就行了，mysql默认1秒写一轮询写一次日志到磁盘。

参数20：innodb_flush_log_at_trx_commit =

说明：(这个配置很关键)一般的实时业务交易配置为2，取值0,1,2

0:数据操作时，直接写内存，并不同时写入磁盘；

2:数据操作时，直接写内存，并不同时写入磁盘；

1:就每个事务提交就会要刷新到磁盘后才算提交完成,这种情况是保证了事务的一致性,但性能会有很大的影响。

0与2的区别：

0：当mysql挂了之后，可能会损失前一秒的事务信息

2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失。

参数21：innodb_read_io_threads = xxxx

说明：数据库读操作时的线程数，用于并发。

参数22：innodb_write_io_threads = xxx

说明：数据库写操作时的线程数，用于并发。

参数23：innodb file per table= 1

说明：每一个表是否使用独立的数据表空间，默认为OFF(使用共享表空间)，一般建议配置为1，InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：(ibdata1)，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，

单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。

共享表空间在Insert操作上少有优势。其它都没独立表空间表现好，如果数据库基本上都插入操作则配置为0。

参数24：innodb_stats_on_metadata={ OFF|on}

说明：是否动态收集统计信息，开启时会影响数据库的性能(一般关闭，找个时间手动刷新，或定时刷新)如果为关闭时，需要配置数据库调度任务，定时刷新数据库的统计信息。

参数25：innodb_spin_wait_delay=xxxxx

说明：控制CPU的轮询时间间隔，默认是6,配置过低时，任务调度比较频繁，会消耗CPU资源。

参数26：innodb_lock_wait_timeout=xxxx

说明：控制锁的超时时间，默认为50，这个值要注意，如果有特殊业务确实要耗时较长时，不能配置太短。

二、InnoDB缓存池

InnoDB缓存池(InnoDB buffer pool)是提升InnoDB提升性能的关键，它既可以缓存数据，又可以缓存索引，甚至其他的管理数据(元数据、行级锁)等。可以使用show variables like 'innodb%pool%'; 来查看相关的参数选项。

mysql> show variables like 'innodb%pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size     | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+
9 rows in set (0.00 sec)

innodb_buffer_pool_size

innodb_buffer_pool_size是用于设置InnoDB缓存池(InnoDBBufferPool)的大小，会缓冲索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数字字典信息等。InnoDB缓存池的大小对InnoDB的整体性能影响较大，默认值是128M。

 通过查询show status like 'Innodb_buffer_pool_%'，保证Innodb Buffer Pool的Read命中率越高越好：(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) /Innodb_buffer_pool_read_requests * 100%

innodb_buffer_pool_instance

innodb_buffer_pool_instance允许多个缓冲池实例，每页根据哈希平均分配到不同缓冲池实例中，减少数据库内部资源竞争，可以提升InnoDB的并发性能。默认值是1，表示InnoDB缓存池被划分为一个区域。一般配置数值<=服务器CPU的个数。

innodb_additional_mem_pool_size

指定InnoDB用于来存储数据字典和其他内部数据的缓存大小，默认值是2M.InnoDB的表个数越多，就应该适当的增加该参数的大小，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小。对于大数据设置16M足够用。

二、InnoDB缓存池内部结构

InnoDB在内存中维护一个缓存池用于缓存数据和索引。缓存池可以认为是一条很长的链表(list).该链表分为两个子链表，一个子链表存放old page数据，old page 数据是长时间未被访问的数据页，另一个子链表存放new page，new page 是最近被访问的数据页。old page 默认占整个链表大小的37%，可以通过innodb_old_blocks_pct参数查看.

mysql> show variables like 'innodb_old_blocks%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_pct  | 37    |
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
2 rows in set (0.00 sec)

old page 和 new page 的交汇点称为midpoint，modpoint指新读取到的页放入LRU(最近最少使用算法)列表中的位置。在innodb存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单理解为new列表中的页都是最为活跃的热点数据。

当用户访问数据时，InnoDB首先会在InnoDB缓存中查找数据，如果缓存池中没有数据，InnoDB会将硬盘中的数据插入到InnoDB缓存池中，如果缓存池已满，则利用LRU算法清除过期的旧数据

innodb_old_blocks_pct

innodb_old_blocks_pct 确定modpoint位置，默认37，表示新读取的页插入到LRU列表尾端的37%的位置(差不多3/8的位置)。innodb的37%的空间是可以让人来刷的。(意思就是内存的37%拿出来让人刷，就是冷数据区的大小),建议innodb_old_blocks_pct 调成20%

mysql> set @@global.innodb_old_blocks_pct=20;
Query OK, 0 rows affected (0.00 sec)

(内存的80%给DB，DB的80%给热数据(在一个时间段内被频繁的访问！！)) 即，一个64G的物理内存，80%给数据库，数据库的80%给热数据区，即40.96G给了热数据区。

三、InnoDB缓存池预热。

MySQL服务器启动一段时间后，InnoDB会将经常访问的数据(业务数据，管理数据)放入InnoDB缓存中，即InnoDB缓存池中保存的是频繁需要访问的数据(简称热数据)。当InnoDB缓存池的大小是几十G或者上百G的时候，如果重启MySQL，如果将之前InnoDB缓存池中的热数据加载到InnoDB缓存池中呢？

如果单靠InnoDB自身预热的InnoDB缓存池，将会是一个不短的时间周期，这对于业务繁忙的系统来说，长时间的挂机，是严重的生产事故，不能够容忍。幸好在MySQL5.6版本支持关闭服务时，可以将热数据保存至硬盘，MySQL重启是首先将硬盘中的热数据加载到InnoDB的缓存中去，这样可以缩短预热的时间，提高业务繁忙高并发时的效率。

mysql> show variables like '%innodb%pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size     | 4194304        |
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 2              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 536870912      |
+-------------------------------------+----------------+
9 rows in set (0.00 sec)

innodb_buffer_pool_dump_at_shutdown

默认是关的，如果开启参数，停止MySQL服务时，InnoDB缓存中的热数据将会保存到硬盘中。

需在my.cnf里，加入：innodb_buffer_pool_dump_at_shutdown = 1
#或者
mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
Query OK, 0 rows affected (0.00 sec)

innodb_buffer_pool_load_at_startup

默认是关闭的，如果开启该参数，启动MySQL服务时，MySQL将本地硬盘的热数据加载到InnoDB缓存池中。

innodb_buffer_pool_dump_now

默认关闭，如果开启该参数，停止MySQL服务时，以手动方式将InnoDB缓存池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_now

默认关闭，如果开启该参数，启动MySQL服务时，以手动方式将本地硬盘的数据加载到InnoDB缓存池中。

innodb_buffer_pool_filename

如果开启InnoDB预热功能，停止MySQL服务时，MySQL将InnoDB缓存池中的热数据保存在磁盘里ib_buffer_pool文件中，位于数据库根目录下，默认文件名是这个参数的值。

注：只有在正常关闭MySQL服务，或者pkill mysql时，会把热数据dump到内存。机器宕机或者pkill -9 mysql，是不会dump。

开启InnoDB缓存后，可以使用如下命令查看当前InnoDB缓存池预热的状态信息：

mysql> show status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | not started                                      |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 181009 18:38:21 |
| Innodb_buffer_pool_pages_data         | 886                                              |
| Innodb_buffer_pool_bytes_data         | 14516224                                         |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 160                                              |
| Innodb_buffer_pool_pages_free         | 31872                                            |
| Innodb_buffer_pool_pages_misc         | 9                                                |
| Innodb_buffer_pool_pages_total        | 32767                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 32644                                            |
| Innodb_buffer_pool_reads              | 886                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 341                                              |
+---------------------------------------+--------------------------------------------------+
17 rows in set (0.00 sec)

这里面的英语都比较简单，就不解释了。

四、InnoDB实时监控

mysql> show engine innodb status\G