译-Mysql-innodb存储引擎的性能优化
Mysql innodb performance optimization
Mysql innodb存储引擎的性能优化
本文翻译自http://www.mysqlperformanceblog.com/files/presentations/UC2007-Innodb-Performance-Optimization.pdf
这里只是我对这个PDF文件进行了翻译，由于本人刚从事mysql DBA一职，所以很多东西自己并不能很好的翻译出来，其中对于硬件部分翻译的应该还行，但是关于数据库方面的翻译的不好，大家就看看吧，翻译本文只是想更 清楚的了解mysql 优化上的一些基本原则，而国内对于这个没有完整的资料。本文的作者Heikki Tuuri是InnoDB的创始人，在翻译过程中同样获得了Peter Zaitsev的帮助，他是《High Performance MySQL》一书的作者。
作者： Heikki Tuuri
Email: pz@mysqlperformanceblog.com
译者： Timo
大家有什么意见可以给我邮件。
一切都从应用程序设计开始
- 通用应用程序的设计是至关重要的
1.1. 设计你的schema，索引和查询，以及选择正确的存储引擎是常用的优化手段。
1.2. 在有些情况下存储引擎的选择会影响到schema和索引
1.3. 我们这里不会覆盖到一般的schema设计方法，但是会主要聚焦到Innodb存储引擎。
- 每个存储引擎都是不同的
2.1. MySQl提供多种存储引擎可供选择
2.2. 它们每个都有不同的设计和操作属性。
2.3. 一个给某个存储引擎写的应用程序可能在其它存储引擎下表现良好。
2.4. 每个存储引擎都有特定的优化方式，所以它们只对特定的设计模式有用。
2.5. 我们覆盖所有对于InnoDB存储引擎的做何不做。
- 使用事务
3.1. InnoDB默认就是使用事务，甚至你不知道如何使用。每句语句都将在自己的事务内（假设你运行“autocommit”模式 set autocommit=1），在每句语句后面都会自动增加commit语句。
3.2. 把多条update语句包装在同一个事务是更有效率的方式。（set autcommit=0;..comit;…commit;）。不能让事务过长，这样会造成死锁和等待超时。
- 不要使用锁表(lock tables)
4.1. 锁表（LOCK TABLES）是设计用来给那些表级锁的存储引擎。在行级锁的存储引擎中事务是更好的选择。InnoDB的的锁表行为在不同的mysql 版本是不同的，如果你从MySQL4.0或者更新的版本升级，那你依赖于innodb_table_locks这个选项会导致很多问题。
- 主键簇
5.1. 主键是特殊的
5.1.1. 通过主键访问数据比通过其它key访问更快。无论是在内存还是磁盘通过主键查找都是最快的。
5.1.2. 数据都由主键聚集的。连续的主键值很容易让同一页主键的数据进行排序同时首字查询也会非常有效率。可用于把所有需要访问数据集中在一起。把用户信息存储在一起可以使用（user_id,message_id）作为主键来保存所有用户的信息在很多页中。主键是一个替代索引对于任何字段。
- 主键的开销
6.1. 主键在随机排序是开销比较大的，也会导致表产生碎片（主键的插入一般都是根据升序进行的）
6.1.1. 如果可以装载数据都通过主键来进行排序的。
6.1.2. 有时候把主键设置成自动增长（auto_increment）是一个好主意
6.2. 如果你不指定，主键默认就是一个内在的聚集key。所以最好就定义一个并且使用它。
6.3. UPDATE PK开销是非常大的
6.3.1. 行数据将会在索引中物理的从一个地方移动位置。
6.3.2. 通常这种需要在设计上进行避免。
- 让主键尽量短
7.1. 因为其它索引都是通过主键来构建索引的。
7.1.1. 使主键成为其它索引的一部分。
7.2. 长主键会让你的索引变大和变慢
7.2.1. 你可以把主键变成唯一KEY，同时给主键添加自动增长。你不能简单的让InnoDB自己去创建它的内部主键，通过把主键变为唯一key因为MySQL会自动的转换一个非空的的唯一key作为主键。
7.2.2. 如果一个表中只有主键，同时所有查询都是通过主键进行，即使主键是比较长的，那通过主键进行查询也是更快的。
- InnoDB的索引
8.1. 让唯一索引变的简单
8.1.1. 不使用”insert buffer”会加快索引的更新。
8.2. 索引是不能做前缀压缩的
8.2.1. 所以它会比MyISAM引擎占用更多的空间
8.2.2. 要尽量避免过多的索引
8.3. 对所有需要更新的列进行索引
8.3.1. 不然你将看到不愿看到的lock问题。 DELETE FROM users WHERE name=’peter’, 如果没有对name列进行索引的话就会锁住表中所有的行。
- 自动增加将限制可扩展性
9.1. 自动增长的插入可能会用到表级锁（但只会在insert语句的最后部分，没有事务的情况下）。即使你指定了自动增长列的值。
9.2. 对于并发插入将限制可可扩展性。
9.3. 会导致在运行中出现困难。
9.4. 导致超出MySQL所能分配的值。要特别小心那种非常长和随机的主键。
- 多版本
10.1. 只对需要的行进行lock将会获得更好的并发性能。
10.2. 普通的SELECT不会进行lock操作，只会去读适当的行。
10.2.1. Lock在共享模式下，UPDATE做更新操作时会对读进行Lock。
10.3. 甚至长时间的select查询不会阻止对于表的update或者select操作。
10.4. 过度的慢查询（通常在事务内）对于性能是不好的，如导致版本的不一致性。READ COMMITTED能够减轻这种问题。
10.4.1. InnoDB只能隔离一个行版本当读取这行的时候没有事务在运行。
- 在共享模式下的…FOR UPDATE and LOCK
11.1. 在read commited模式下会进行select lock。因为不能不能lock一个不存在的行。所以这个跟普通的select是不同的。
11.2. SELECT…FOR UPDATE总是不得不访问行数据页进行lock，所以不能进行对这些查询进行索引，就会减慢查询的速度。
- 减少死锁
12.1. 在事务的数据库中死锁是普遍存在的。
12.1.1. 在你InnoDB中没有锁的select语句是不会导致死锁的。
12.1.2. 在你的应用需要控制好你的死锁时间。
12.2. 如果可能的话确认在事务中锁住的数据就是你请求的那些。
12.3. 让update数据变小（分离你的事务）
12.4. 使用SELECT…FOR UPDATE如果你想更新大部分你所选择的行。
12.5. 使用外部锁可以避免死锁这个问题—应用程序级别的锁，SELECT GET_LOCK(‘mylock’)等等。
- 隔离级别是如何影响性能的
13.1. InnoDB支持很多种的隔离级别。这些隔离级别可以设置为全局有效也可以针对每个连接和每个事务。
13.1.1. READ UMCOMMITED（不提交读）—这个是很少使用。如果你不想有脏数据产生那就可以很好的使用这个，但是会影响性能。
13.1.2. READ COMMITED（提角度）—所有提交事务的结果对于下一条语句都是显而易见的。可能比其他更高的隔离级别性能更高。允许老的内容更快的更新。在mysql5.1，InnoDB会有一些间歇锁在这个级别上：使用行级复制和binlog可以避免这个问题。
13.1.2.1. REPEATABLE READ（可重复读）—默认的隔离级别。事务内的读都是完全可重复的，没有幽灵行的产生。
13.1.2.2. SERIALIZABLE（串行化）— 让所有select都锁住select，尽可能避免使用这个隔离级别。
- 外键性能
14.1. 当更新行时候InnoDB都会检查外键，而且不会进行批处理或者当事务提交时候检查延迟。外键通常都有很多性能上的开销，但是这也保证了数据库的连续性。
14.2. 外键增加了很多行级锁，这将会影响到很多其它表不光是自己直接更新的那张表。
14.3. 外键会锁住子表，当父表在更新的时候。（select … for update在父表上这样执行就不会锁住子表）
- 运行中的事务中的约束数量
15.1. 在一定数量内的运行中的事务和执行查询，InnoDB性能表现良好
15.1.1. 多个运行中查询可能导致互相之间干扰。Innodb_thread_concurrency能够被用作在InnoDB内核中限制线程数量。
15.1.2. 许多运行中事务会导致更多的锁，同时造成机器负载增加。
15.1.3. 如果有可能，在同一时间内限制一定数量的查询，在应用程序端做好队列。
- 注意不要有太多的表
16.1. InnoDB自己的表定义（字典）缓存依赖于MySQL的table_cache变量值。
16.2. 只要打开一次，InnoDB就不会从缓存中移除这个表。
16.3. 每张表大概要消耗4KB以上的空间。MySQL 5.1 的 InnoDB已经将这个空间减少了50% 到 75%
16.4. 当重启时，每个表的统计将会被重新计算。所以第一次操作会是非常耗资源的。MySQL的table_cache将会串行执行这些操作。
- Insert…Select
17.1. Insert…Select语句执行时会对select进行锁
17.2. 语句级别复制要求更新都是串行化的。在MysQL5.1 行级别更新在 READ COMMITED已经没有问题了。
17.3. 无论什么时候你启用或者不启用log-bin，都需要保持一致性。
17.4. Innodb_locks_unsafe_for_binlog在MySQL5.0是有帮助的，但是你的复制有可能会被中断，同时会禁止next-key的锁。
17.5. SELECT…INTO OUTFILE + LOAD DATA INFILE经常被用作non-blocking的安全替代。
- Next key lock（间隙锁）
18.1. Innodb不光会锁使用到行，也会锁这些行之间的行（称为间隙行）。
18.2. 这个是为了防止幽灵行的出现。 设置 “REPEATABLE READ”确实会让InnoDB可重复的。
18.3. 对于MySQL语句级别的复制是很有必要的。
18.4. 会让一些写负载大的机器上增加锁的情况。
18.5. 如果你没有设置和使用二进制log（用作复制和恢复的），那可以禁止这个间隙锁。
18.6. 在MySQL5.1中，如果你使用行级复制就可以安全的进行修改。
- Count（*）的事实和传说
19.1. InnoDB不能很好的控制count()的查询—这个只是传闻。在所有引擎中大部分count()查询都用相同的方式进行查询。 select count(*) from articles Where user_id=5
19.2. 在缺少where字句的情况下，InnoDB不对count()查询进行优化—这个是事实。如select count() from users; InnoDB不能简单存储行的计数，每个事务都有自己的表的视图。因为有重要的工作还要去实现。你可以使用触发器和计数器。SHOW TABLE STATUS LIKE “USERS” 可以显示表近似的行数。
- InnoDB和集体提交
20.1. 集体提交—提交多个事务通过单个日志写。这个可以提高非常多的性能，特别是没有做RAID的情况下。
20.2. 在MySQL5.0下，集体提交不能在有二进制log的情况下工作。由于XA（分布式事务）方法被实现，特别要小心从MysqL4.1进行的升级。
回到基本的服务器性能调优
- 一切都从内存开始
1.1. InnoDB_buffer_pool_size
1.1.1. 详细指定了主要InnoDB缓存—数据和索引页，插入缓存，锁都会存在这里。
1.1.2. 在大数据集的情况下对于性能非常重要
1.1.3. 比OS级别的缓存更有效的多，特别对于写操作。InnoDB不得不去绕过OS的buffer去写。
1.1.4. 最好使用70%–80%的系统内存作为InnoDB的buffer使用。
1.1.5. 默认值是8M，可用的独立内存，要好好确认如何去配置。
1.2. InnoDB_additional_mem_pool
1.2.1. 仅仅存储字典，它会自动增长，不用设置的太大。
- InnoDB日志
2.1. Innodb_log_file_size
2.1.1. 对于写性能有非常重要的影响。要保持非常大。
2.1.2. 高的数值会增加你回复的时间。检查一下你能设置的最大的大小。
2.1.3. 最大的限制是4G。
2.2. Innodb_log_files_in_group
2.2.1. 这些文件指定了对于Log所能使用的大小。
2.2.2. 通常不需要改变其默认值。
- InnoDB日志
3.1. Innodb_log_buffer_size
3.1.1. 不要设置超过2-9M，除非你使用大量的超大文件，日志文件都会被刷新在每秒执行完毕后。
3.1.2. 检查innodb_os_log_written的增长来看你的日志文件的写入。
3.1.3. Innodb日志是物理逻辑的，不是基于页的，所以他们是非常紧凑的。
3.2. Innodb_flush_logs_at_trx_commit
3.2.1. 默认日志被刷新到磁盘上在每次事务提交后。这个是为了保证ACID（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）），所以开销非常大。
3.2.2. 可以设置2和0，如果你能接受丢失最后一次的事务。
- InnoDB日志重新设置大小
4.1. 这个不是简单修改选项和重启下能够完成的。
4.2. 首先要关闭MysQL服务器
4.3. 确认它是正常关闭的（检查有没有错误日志）
4.4. 移动所有InnoDB日志文件到其它地方
4.5. 修改配置文件然后重新启动MySQL服务器
4.6. 检查错误日志并查看是否产生新的日志文件。
- InnoDB_flush_method
5.1. 指定一个方法让innodb跟OS文件系统一起工作
5.2. Windows: 总是使用没有buffer的IO方法
5.3. Unix:可以使用fsync()或者O_SYNC/O_DSYNC进行刷新文件。
5.3.1. Fsync()通常是更快的，允许累计多个写操作然后并发执行。
5.3.2. 一些操作系统允许关闭OS级别的缓冲针对InnoDB的数据文件。这样非常好，你总不希望数据被缓冲2次吧。
5.4. Linux：O_DIRECT 使用直接的非缓冲IO。避免双重缓冲，可能会让写更慢。
- Innodb_file_per_table
6.1. InnoDB可以存储每个表在单独的文件中。
6.2. 对于系统需要的主表空间还是需要的。
6.3. 能够帮助拆封不同的表到多个磁盘上。
6.4. 如果表被删除允许回收空间。
6.5. 有时候使用连续的fsync()写会更慢。
6.6. 当有大量的表的时候会增加启动和关闭的时间。
- 其它文件IO设定
7.1. Innodb_autoextend_increment–这个参数指定了对于共享表空间的增量（不是为了单独表空间的）。大的值可以有效的减少碎片。
7.2. Innodb_file_io_thread–改变IO线程的数量，只对windows有效，所有4个线程可以做不同的事情。
7.3. Innodb_open_file–这个值是用作指定每个表空间所允许打开文件的数量。如果你有很多表那就要增加它。
7.4. Innodb_support_x–把这个值设置定为0的时候能够减少innodb的工作在事务提交时。Binlog 能够通过异步的方式同步。
- 最小化重启时间
8.1. Innodb缓存池可能有一些未写入到磁盘的数据。所以关闭的时候需要非常的时间。
8.2. 如果你想最小化关闭时间，那需要如下设定：
8.2.1. SET GLOBAL innodb_max_dirty_pages_pct=0
8.2.2. 执行show status后观察 innodb_buffer_pool_pages_dirty
8.2.3. 当这个值变为0的时候就可以关闭服务器了
8.3. 在执行这个操作时候会让性能降低，因为InnoDB将立刻要将脏数据页写入到磁盘上。
- 排错逃离清除
9.1. InnoDB不会通过delete来移除一行（和在更新时候旧的行），因为这些行可能会被其他事务使用。
9.2. 清除线程被用在清除这些没有用到的行。
9.3. 在一些工作负载，清除线程可能不能保持这些表空间无限的增长。通过show innodb status观察transactions部分。
9.4. Innodb_max_purge_lag–这个是用来限制事务每次所能更新或者删除最大的行数。将会延迟insert/update操作，所以清除线程会被保持。
9.5. 为什么我们不用多个清除线程呢？
- 并发控制设置
10.1. 设置可以帮助InnoDB适应于控制大量的并发事务。
10.2. Innodb_thread_concurrency–InnoDB内核中同时可以允许最大的在线程数量（0表示没有限制），2*（CPU核数+磁盘数量）在理论上是一个合理的值，在实际应用中设置的更小一点可能会让性能更好一点。
10.3. Innodb_commit_concurrency–允许同一时间内事务提交的最大线程数。
10.4. Innodb_concurrency_tickets–在不得不退出内核空间和等待之前能运行线程的数量。
10.5. Innodb_thread_sleep_delay
10.6. Innodb_sync_spin_loops
- 获得高性能的不安全方式
11.1. Innodb有一些检查和方法是数据不被最小化丢失和错误。
11.2. Innodb_doublewrite–这个值是用来保护部分页的修改，只是禁止假如OS保证它不会发生。
11.3. Innodb_checksums–在页中的数据校验码，帮助发现文件系统错误，内存损坏和其它问题。
11.3.1. 导致一部分大工作负载的机器会过载。
11.3.2. 当性能是第一的情况下可以禁止这个参数。
- Innodb SHOW STATUS部分
12.1. Mysql5.0有一些性能计数信息通过show status能够显示出来。
12.1.1. 它们都是全局的，虽然大部分其它技术信息都是针对每个线程的。
12.1.2. 它们大部分都是从show innodb status中获取的。
12.2. 下面将显示其中的一些指标。
12.3. Innodb_buffer_pool_pages_misc–其他缓存页需要的在innodb buffer中所使用到的页。
12.4. Innodb_buffer_pool_read_ahead_rnd–innodb处理的随机预读的数量。
12.5. Innodb_buffer_pool_read_request, innodb_buffer_pool_reads 这2个值是用来计算缓存读的命中率的。
- Show innodb status
13.1. 这个是innodb故障处理的工具。当发生问题时就输入“show innodb status”
13.2. 这时候就会显示一些比show status更详细的信息。
13.3. 一些关于现在正在运行中的事务的信息（列入它们的锁等等）
13.4. 最新的一个死锁和外键的信息等等
13.5. 一些关于latches,spinlocks(自旋锁)，操作系统等待信息
13.6. 更详细的参考http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
- Show mutex(互斥量) status
14.1. 一个用来显示在你的工作负载下哪些热门的互斥量的工具
14.2. 显示了哪些是真正发生的互斥量，自旋锁的细节，以及操作系统等待。
14.3. Timed_mutexes — 跟踪操作系统等待了多久
硬件和操作系统的选择
15. 硬件和操作系统选择的检查列表
15.1. 使用什么CPU，以及多少个？
15.2. 使用多大的内存？
15.3. 如何建立IO子系统（如RAID）？
15.4. 使用哪个文件系统是最好的选择？
- 选择CPU
16.1. 不同的CPU/架构对于innodb的扩展性能是不同的。
16.2. 老的“netburst”基于intel的xeon的扩展能力是比较弱的。
16.3. 新的酷睿基于xeon和 AMD的opterons具有更好的可扩张性
16.4. X86_64是非常必须的。
16.5. 使用多核处理器会让Innodb工作的更好
16.6. 每个系统拥有8核是一个比较合理的限制。
16.6.1. 必须根据实际的工作负载来进行调配
16.6.2. Innodb必须要根据未来的预期负载进行
16.7. 外部扩展，使用多台性能 较低的终端服务器。
16.8. 32位的CPU马上就要被淘汰了，所以请不要再使用32位的操作系统。
- 使用多大的内存
17.1. 内存经常是对于很好的应用程序调优的性能瓶颈。
17.2. Innodb可以很有效的使用大量的内存。
17.3. 工作设定必须合理设置内存
17.3.1. 因为数据页是最常被访问
17.3.2. 不要通过行进行计数：100byte的行大概是1亿行，随机100万行的工作设定能够产生大部分的数据页。
17.4. 是总数据库大小的5%能够导致50%空间占用。
17.5. 确定使用64位的平台，操作系统和mysql版本。
- 如何建立IO子系统
18.1. Innodb可以很好的加载大部分的磁盘驱动。每个节点有6-8个是一个看上去最优的配置。
18.2. 直接能够访问到的存储通常工作的最好
18.3. SAN–会增加恢复时间，而且价格昂贵
18.4. NAS–可以避免很多数据错误的风险
18.5. ISCSI–在大部分业务中都是非常适合的，也会增加恢复时间
18.6. RAID–电池备份cache是非常重要的。一定要确认启动了在写入cache的时候有电池备份。
18.7. 硬盘自身的缓存一般都是需要关闭。或者确认使用fsync()写入数据到磁盘或者当操作系统崩溃的时候能够产生数据腐坏。
- 本地存储的配置
19.1. 日志最好放到单独的raid1中。这个是非常有帮助的，在很多情况比放在跟数据一起更好。
19.2. 二进制日志最好放到单独的卷中—能够很好的帮助备份和恢复。
19.3. RAID10对于表空间非常好。比预期下降的性能更多。
19.4. RAID5对于特定的工作负载时好的。仅仅需要确认是否需要降低性能。
19.5. 大的RAID条带大小（128K+）在理论是最好的，但是很多RAID控制器不能非常好的去控制。
19.6. 软RAID也是不错的，特别是RAID1。
- 操作的选择也有会有问题？
20.1. 需要考虑性能，工具的有效性，社区等等。
20.2. Windows–试用于开发环境，很低的扩展性的WEB/企业项目上。
20.3. Solaris–提供了一些非常好的工具，也能对Mysql提供很好的支持，但是缺乏社区支援。
20.4. FreeBSD–历史上Mysql有很多问题在这个平台上，现在是好多了，但是只有很少的工具可用，很少在生产环境中使用。
20.5. Linux–在生产环境中和开发环境中最常使用的平台。有一些像LVM,JFS这样的工具可以使用。
- 文件系统的选择
21.1. 主要是linux环境中有很多文件系统可以选择
21.2. EXT3–很多Linux发行版的默认文件系统，工作的非常好对于终端安装。
21.3. ReiserFS–很多Linux支持这个迁移。通常没有打的问题在标准的Mysql工作负载下。
21.4. XFS–被用在有RAID驱动的情况下，能够提供不错的性能提升。
21.5. JFS–很少被使用
21.6. Raw分区（原始分区）针对innodb表空间—很少使用
21.7. 通常非常高的性能提升预期都是通过更改文件文件系统来获得。
最近InnoDB的性能开发
- InnoDB可伸缩性的补丁
22.1. 减少了buffer pool也中竞争。在5.0中直接可用，在4.1中需要自己移植。
22.2. 在MySQL5.1提升了sync_array的实现。
22.3. 基于工作负载，硬件环境，并发上的性能提升跟原来有所不同。
22.4. 从很少的百分比到很多次的进行排序。
22.5. 性能还是会在很大数量的并发线程的情况下有所下降。
22.6. 未来可扩展实现补丁的原型都是来源于社区的。
- 其它改进
23.1. 在Mysql5.1中行级别复制减轻了间隙锁的问题。
23.2. 在没有auto_increment”talbe locks”能够继续工作。
23.3. 数据库页中支持ZIP压缩
23.4. 更快的索引建立
23.4.1. 不再需要全表重建
23.4.2. 可以根据物理分片的索引进行排序。