Mysql Innodb 性能优化

1. 概述

innodb 作为最主流使用的 mysql 存储引擎，尤其在新版本的 mysql 中 MyISAM 存储引擎被废除，更加提升了 innodb 如日中天的地位。 那么，作为 mysql 的使用者，如何优化 innodb 使之发挥更强大的性能，就成为了必修课。

2. 参数优化

2.1. innodb_buffer_pool_size

innodb_buffer_pool_size 参数指的是内存中缓存 innodb 表的索引、数据的插入时缓冲，这是 innodb 参数优化的首要参数。 innodb_buffer_pool_size 默认值是 8M，如果 mysql 所用的是专用的 DB 服务器，那么可以把这个数字设置为实际物理内存的 70% 到 80%，这个参数不能动态改变，所以配置时需要考虑：

• 如果分配过大，那么会导致 mysql 占用内存过大，使机器 swap 增多，导致查询变慢
• 如果分配过小，又会由于缓存无法被充分利用而致使磁盘io增多，降低性能

通过 show engine innodb status 命令可以查看该参数。

2.2. innodb_additional_mem_pool

innodb 内部目录存放空间大小。 这个参数通常不需要配置太大，16M 即可，可以在运行时进行调整，如果数据库中表较多，可以适当增大该参数。 通过 show engine innodb status 命令查看运行中的 DB 状态，Total memory allocated 与 in additional pool allocated 两项数值进行比较，可以根据实际情况适当调整。 执行 innodb_additional_mem_pool=16M 可以设置该参数。

2.3. innodb_file_per_table

是否为每个表使用独立的文件存储，参数为 0 或 1，1 是开启，0 是关闭。 通常来说还是建议将这一项开启的。

2.4. innodb_flush_logs_at_trx_commix

事务提交方式，有三种取值：0、1、2，默认为 1，推荐为了保证数据的可靠性，主库设置为 1，如果设置为 0 或 2 可以提高效率，但是会丢失 1 秒的事务。 1. 0 — log buffer 数据每隔 1 秒写入到日志文件中，并将文件刷新到磁盘，而事务提交不会触发写磁盘操作 2. 1 — 事务提交时立刻写入日志文件并刷新磁盘 3. 2 — 每次事务提交时，立即将数据写入文件，但每隔 1 秒刷新一次磁盘

2.5. autocommit

是否自动提交，1 表示启用，0 表示禁用，可以在运行时通过 SET AUTOCOMMIT = 1 或 0 来改变这一参数。 当 AUTOCOMMIT = 0 时，所有查询都在一个事务中，直到显式 COMMIT 提交或 ROLLBACK 回滚，事务才结束，而该事务结束的同时又会开始一个新的事务。

2.6. long_query_time

mysql 的日志功能非常强大，通常我们都会打开 binlog，而另一个重要的 log 就是慢查询日志。 通过 set global log_low_queries=on 可以开启慢查询日志。 通过 set global long_query_time 可以设置慢查询日志捕获慢查询的阈值（秒数），可以通过把这个值设为 0 让 mysql 捕获所有查询。 但是，全部符合下列条件的查询才会被计入慢查询日志： 1. 查询必询不是管理语句，或者开启了log_slow_admin_statements 2. 查询的时间至少是long_query_time的秒数,或者查询没有使用索引并且开启了log_queries_not_using_indexes 3. 查询至少检索了min_examined_row_limit 的行数 4. 查询必要不会根据log_throttle_queries_not_using_indexes 的设置而被抑制 所以如果设置没有生效，需要检查上述其他配置项是否影响了慢查询日志的记录。

3. 主键设计原则

1. 采用一个没有业务用途的自增属性列作为主键 2. 主键字段值不能进行 update 操作 3. 主键数据类型应尽可能小，使用 smallint 或 int 而不是 bigint 4. 主键字段放在数据表的第一列

这样做的好处： 1. 随机 IO 转化为顺序 IO 2. 新增数据主键顺序增长，可以减少 innodb 页的分裂 3. 数据变更但主键值不变更，由于聚簇索引的物理结构，使物理存储位置发生变化的概率降低

4. 索引使用原则

1. 索引可以加快数据库的检索速度，但是会降低数据库插入、修改、删除等操作的速度，同时索引也需要内存空间进行存储，因此并非越多越好 2. 大多数情况下每次查询只能使用一个索引，所以联合索引通常优于单列索引 3. 较频繁的查询条件应用于创建索引 4. 唯一性差的字段不适合作为单独索引 5. 更新频繁的字段不适合创建索引

5. binlog 格式设置

binlog 有三种格式： 1. STATEMENT — 语句级别 2. ROW — 行级别 3. MIXED — 混合级别

推荐使用的是 ROW 格式，因为 STATEMENT 格式记录的是实际执行的 SQL，在某些情况下会造成主从不一致，而 ROW 格式记录的是数据库中行数据的变化，是最安全的。

6. 单条 SQL 语句的剖析

当定位到某条需要优化的单条查询后，针对这条查询进行优化的前提就是获取相关的更多信息。

6.1. SHOW PROFILE

SHOW PROFILE 命令是 MySql5.1 以后版本引入的，默认是禁用的，可以通过 set profiling = 1 命令开启。 该命令开启后，所有在服务器上执行的语句都会被测量其耗费的时间和其他一些查询执行状态变更相关的数据。 当执行完一条查询后，通过命令 show profiles 就可以看到精确地查询耗时，通过 show profile for query 1 可以看到上一条查询的完整剖析报告，他将给出查询执行的每个步骤及其花费的时间。

事实上，上述剖析报告存储在 INFORMATION_SCHEMA.PROFILING 表中，通过对这个表中的数据可以进行更加灵活方便的查询统计操作。

6.2. EXPLAIN 与 SHOW STATUS

在查询语句前加上 explain 关键字，mysql 将给出此次查询的各项估计值，而 show status 则会给出自 上次执行 flush status 命令以来的各项计数统计值。 show status 本身会创建一个临时表，并通过句柄访问这个临时表，因此在 show status 的统计结果中，临时表计数器通常会加 2。

7. 参考资料

《高性能 MySQL》。 数据库运维介绍 — http://blog.sina.com.cn/s/blog_a1e9c7910102wd3m.html。 mysql innodb 引擎优化 — http://linux.chinaunix.net/techdoc/database/2009/04/28/1109193.shtml。