MySql InnoDB 存储引擎表优化

一、InnoDB 表存储优化

1、OPTIMIZE TABLE

适时的使用 OPTIMIZE TABLE 语句来重组表，压缩浪费的表空间。这是在其它优化技术不可用的情况下最直接的方法。OPTIMIZE TABLE 语句通过拷贝表数据并重建表索引，使得索引数据更加紧凑，减少空间碎片。语句的执行效果会因表的不同而不同。过大的表或者过大的索引及初次添加大量数据的情况下都会使得这一操作变慢。

2、自增主键

InnoDB表，如果主键过长（长数据列做主键，或者多个列组合做主键）会浪费很多空间。同时，二级索引也包含主键。这种情况，可以考虑创建自增列作为主键，或者使用前缀索引。

3、VARCHAR

对于需要存储长度不定或者包含很多NULL值的字符串列，使用 VARCHAR 代替 CHAR 。在小表应用上，缓存使用及磁盘 I/O 消耗会更小。

4、压缩的行格式存储

对于包含大量重复文本或者数字的大表，可以考虑采用压缩的行格式存储。这样数据加载会减少对缓存及 I/O 的需求。在使用压缩行格式前，需要考虑压缩行格式 COMPRESSED 和的不同性能影响。

二、InnoDB 事务管理优化

优化 InnoDB 事务处理，主要需要找到事务特性和服务器负载间的某个平衡点。例如，一秒需要提交几千事务的，或者每隔2-3个小时提交一次事务的不同应用表现。

1、AUTOCOMMIT 设置

MySQL 的默认设置 AUTOCOMMIT=1 会限制繁忙数据库的性能。如果可以的话，可以在应用中使用 SET AUTOCOMMIT=0 或者 START TRANSACTION ，然后将多个相关的数据变更操作添加到同一事务中，然后执行 COMMIT 语句来提交事务，提交数据变更。

InnoDB 对于引发数据库变更的操作，必须将其进行日志刷盘。

2、只读事务

对于只包含 SELECT 语句的事务，启用 AUTOCOMMIT ，使得 InnoDB 能够识别只读事务，然后进行相应的优化。

3、回滚操作

避免对大数据量操作插入，更新和删除之后的回滚操作。如果一个大的事务拖慢了服务器，那么回滚将是服务器性能变得更糟。可以分批处理大数据量操作。通过杀进程方式终止的回滚操作会在服务器启动时重新启动。
可以通过如下策略减少此类问题发生：

• 增大缓存，避免频繁磁盘I/O。

• 设置 innodb_change_buffering=all，这样 update 和 delete 操作也会和 insert 一样进行缓存，回滚也更快。

• 手动commit，分割大数据操作。

为了避免时空的回滚。增大缓存，使得回滚进程可以应用到最大的资源以便快速执行。或者杀掉回滚进程，然后使用innodb_force_recovery=3选项重启。

对于较多执行耗时inserts, updates, 及 deletes 操作的服务器，确保innodb_change_buffering=all开启。

4、日志刷盘

InnoDB 如会每秒刷盘一次日志，如果可以承受最新事务崩溃的数据损失，可以设置innodb_flush_log_at_trx_commit = 0。虽然日志的刷盘操作也不是保证的，同时也可以设置innodb_support_xa = 0，减少磁盘和二进制日志的同步操作。

Note

innodb_support_xa 已被弃用，将来版本会被移除。MySQL 5.7.10版本，InnoDB XA事务的两阶段提交是默认支持的，不能设置禁用innodb_support_xa。

5、耗时事务数据

行修改或删除后，行数据及 undo logs 在物理上并没有立刻被变更。即使在事务立刻提交后。旧数据会保持直到之前启动的事务或者并发