介绍

InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。

目前来说:InnoDB是为Mysql处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大的网站或是应用中InnoDB是倍受青睐的。

另一方面,在数据库的复制操作中InnoDB也是能保证master和slave数据一致有一定的作用。

InnoDB的创始人:Heikki Tuuri

参数调优内容

内存利用方面

  • innodb_buffer_pool_size

    这个参数和MyISAM的key_buffer_size有相似之处,但也是有差别的。这个参数主要缓存InnoDB表的索引,数据,插入数据时的缓冲。为Innodb加速优化首要参数。

    该参数分配内存的原则:这个参数默认分配只有8M,可以说是非常小的一个值。如果是一个专用DB服务器,那么他可以占到内存的70%-80%。这个参数不能动态更改,所以分配需多考虑。分配过大,会使Swap占用过多,致使Mysql的查询特慢。如果你的数据比较小,那么可分配是你的数据大小+10%左右做为这个参数的值。

    例如:数据大小为50M,那么给这个值分配innodb_buffer_pool_size=64M

    这个参数分配值的使用情况可以根据 show innodb status\G; 中的

    1
    2
    3
    4
    5
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------

    Total memory allocated 4668764894;

    去确认使用情况。

    设置方法:

    1
    innodb_buffer_pool_size=4G

日志控制方面

  • innodb_log_file_size

    作用:指定日志的大小

    分配原则:几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日志上限大小为4G.一般控制在几个LOG文件相加大小在2G以内为佳。具体情况还需要看你的事务大小,数据大小为依据。

    说明:这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。

    设置方法:

    1
    innodb_log_file_size=256M
  • innodb_log_files_in_group

    作用:指定你有几个日志组。

    分配原则: 一般我们可以用2-3个日志组。默认为两个。

    设置方法:

    1
    innodb_log_files_in_group=3
  • innodb_log_buffer_size

    作用:事务在内存中的缓冲。

    分配原则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Oracle等数据库了解这个,一般最大指定为3M比较合适。

    参考:Innodb_os_log_written(show global status 可以拿到)

    如果这个值增长过快,可以适当的增加innodb_log_buffer_size

    另外如果你需要处理大量的TEXT,或是BLOB字段,可以考虑增加这个参数的值。

    设置方法:

    1
    innodb_log_buffer_size=3M
  • innodb_flush_logs_at_trx_commit

    作用:控制事务的提交方式

    分配原则:这个参数只有3个值,0, 1, 2 请确认一下自已能接受的级别。默认为1。

    性能更高的可以设置为0或是2,但会丢失一秒钟的事务。

    说明:

    这个参数的设置对InnoDB的性能有很大的影响,所以在这里给多说明一下。

    当这个值为1时:InnoDB的事务LOG在每次提交后写入日志文件,并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。

    当这个值为2时:在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是,由于进程调用方面的问题,并不能保证每秒100%的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

    当这个值为0时:日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

    从以上分析,当这个值不为1时,可以取得较好的性能,但遇到异常会有损失,所以需要根据自已的情况去衡量。

    设置方法:

    1
    innodb_flush_logs_at_trx_commit=2
  • sync_binlog

    作用:控制binlog刷到磁盘的时机

    sync_binlog的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

    当sync_binlog=N (N>0) ,MySQL在每写N次二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

    设置方法:

    1
    sync_binlog=100

文件IO分配,空间占用方面

  • innodb_file_per_table

    作用:使每个Innodb的表,有自已独立的表空间。如删除文件后可以回收那部分空间。

    分配原则:只有使用不使用。但DB还需要有一个公共的表空间。

    设置方法:

    1
    innodb_file_per_table=1
  • innodb_open_files

    作用:限制Innodb能打开的表的数据。

    分配原则:如果库里的表特别多的情况,请增加这个。这个值默认是300。

    设置方法:

    1
    innodb_open_files=800

    请适当的增加table_cache

其它相关参数

  • innodb_flush_method

    作用:InnoDB和系统打交道的一个IO模型

    分配原则:Windows不用设置。Unix可以设置:fsync() or O_SYNC/O_DSYNC

    如果系统可以禁止系统的Cache那就把他禁了。

    Linux可以选择:O_DIRECT

    直接写入磁盘,禁止系统Cache了

    设置方法:

    1
    innodb_flush_method=O_DIRECT
  • innodb_max_dirty_pages_pct

    作用:控制InnoDB的脏页在缓冲中在哪个百分比之下,值在范围1-100,默认为90.

    这个参数的另一个用处:当InnoDB的内存分配过大,致使Swap占用严重时,可以适当的减小调整这个值,使达到Swap空间释放出来。建义:这个值最大在90%,最小在15%。太大,缓存中每次更新需要致换数据页太多,太小,放的数据页太小,更新操作太慢。

    设置方法:

    1
    innodb_max_dirty_pages_pct=90

    动态更改需要有Super权限:

    1
    set global innodb_max_dirty_pages_pct=50;

关于Schema设计规范及SQL使用建议

  1. 所有的InnoDB表都设计一个无业务用途的自增列做主键,对于绝大多数场景都是如此,真正纯只读用InnoDB表的并不多,真如此的话还不如用TokuDB来得划算;
  2. 字段长度满足需求前提下,尽可能选择长度小的。此外,字段属性尽量都加上NOT NULL约束,可一定程度提高性能;
  3. 尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT * 的时候读性能太差。
  4. 读取数据时,只选取所需要的列,不要每次都SELECT *,避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB列;
  5. 对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引;
  6. 通常情况下,子查询的性能比较差,建议改造成JOIN写法;
  7. 多表联接查询时,关联字段类型尽量一致,并且都要有索引;
  8. 多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表;
  9. 多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;
  10. 多用复合索引,少用多个独立索引,尤其是一些基数(Cardinality)太小(比如说,该列的唯一值总数少于255)的列就不要创建独立索引了;
  11. 类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多;

其他建议

  1. 通常地,单表物理大小不超过10GB,单表行数不超过1亿条,行平均长度不超过8KB,如果机器性能足够,这些数据量MySQL是完全能处理的过来的,不用担心性能问题,这么建议主要是考虑ONLINE DDL的代价较高;
  2. 不用太担心mysqld进程占用太多内存,只要不发生OOM kill和用到大量的SWAP都还好;
  3. 在以往,单机上跑多实例的目的是能最大化利用计算资源,如果单实例已经能耗尽大部分计算资源的话,就没必要再跑多实例了;
  4. 定期使用pt-duplicate-key-checker检查并删除重复的索引。定期使用pt-index-usage工具检查并删除使用频率很低的索引;
  5. 定期采集slow query log,用pt-query-digest工具进行分析,可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作;
  6. 可使用pt-kill杀掉超长时间的SQL请求,Percona版本中有个选项 innodb_kill_idle_transaction 也可实现该功能;
  7. 使用pt-online-schema-change来完成大表的ONLINE DDL需求;
  8. 定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异;