Mysql innodb存储引擎的性能优化一

http://zauc.wordpress.com/category/mysql/

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: 

译者： Timo Seven

Email: greycd@gmail.com

大家有什么意见可以给我邮件。

一切都从应用程序设计开始

1. 通用应用程序的设计是至关重要的

1.1. 设计你的schema，索引和查询，以及选择正确的存储引擎是常用的优化手段。

1.2. 在有些情况下存储引擎的选择会影响到schema和索引

1.3. 我们这里不会覆盖到一般的schema设计方法，但是会主要聚焦到Innodb存储引擎。

2. 每个存储引擎都是不同的

2.1. MySQl提供多种存储引擎可供选择

2.2. 它们每个都有不同的设计和操作属性。

2.3. 一个给某个存储引擎写的应用程序可能在其它存储引擎下表现良好。

2.4. 每个存储引擎都有特定的优化方式，所以它们只对特定的设计模式有用。

2.5. 我们覆盖所有对于InnoDB存储引擎的做何不做。

3. 使用事务

3.1. InnoDB默认就是使用事务，甚至你不知道如何使用。每句语句都将在自己的事务内（假设你运行“autocommit”模式set autocommit=1），在每句语句后面都会自动增加commit语句。

3.2. 把多条update语句包装在同一个事务是更有效率的方式。（set autcommit=0;..comit;…commit;）。不能让事务过长，这样会造成死锁和等待超时。

4. 不要使用锁表(lock tables)

4.1. 锁表（LOCK TABLES）是设计用来给那些表级锁的存储引擎。在行级锁的存储引擎中事务是更好的选择。InnoDB的的锁表行为在不同的mysql 版本是不同的，如果你从MySQL4.0或者更新的版本升级，那你依赖于innodb_table_locks这个选项会导致很多问题。

5. 主键簇

5.1. 主键是特殊的

5.1.1. 通过主键访问数据比通过其它key访问更快。无论是在内存还是磁盘通过主键查找都是最快的。

5.1.2. 数据都由主键聚集的。连续的主键值很容易让同一页主键的数据进行排序同时首字查询也会非常有效率。可用于把所有需要访问数据集中在一起。把用户信息存储在一起可以使用（user_id,message_id）作为主键来保存所有用户的信息在很多页中。主键是一个替代索引对于任何字段。

6. 主键的开销

6.1. 主键在随机排序是开销比较大的，也会导致表产生碎片（主键的插入一般都是根据升序进行的）

6.1.1. 如果可以装载数据都通过主键来进行排序的。

6.1.2. 有时候把主键设置成自动增长（auto_increment）是一个好主意

6.2. 如果你不指定，主键默认就是一个内在的聚集key。所以最好就定义一个并且使用它。

6.3. UPDATE PK开销是非常大的

6.3.1. 行数据将会在索引中物理的从一个地方移动位置。

6.3.2. 通常这种需要在设计上进行避免。

7. 让主键尽量短

7.1. 因为其它索引都是通过主键来构建索引的。

7.1.1. 使主键成为其它索引的一部分。

7.2. 长主键会让你的索引变大和变慢

7.2.1. 你可以把主键变成唯一KEY，同时给主键添加自动增长。你不能简单的让InnoDB自己去创建它的内部主键，通过把主键变为唯一key因为MySQL会自动的转换一个非空的的唯一key作为主键。

7.2.2. 如果一个表中只有主键，同时所有查询都是通过主键进行，即使主键是比较长的，那通过主键进行查询也是更快的。

8. 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. 自动增加将限制可扩展性

9.1. 自动增长的插入可能会用到表级锁（但只会在insert语句的最后部分，没有事务的情况下）。即使你指定了自动增长列的值。

9.2. 对于并发插入将限制可可扩展性。

9.3. 会导致在运行中出现困难。

9.4. 导致超出MySQL所能分配的值。要特别小心那种非常长和随机的主键。

10. 多版本

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只能隔离一个行版本当读取这行的时候没有事务在运行。

11. 在共享模式下的…FOR UPDATE and LOCK

11.1. 在read commited模式下会进行select lock。因为不能不能lock一个不存在的行。所以这个跟普通的select是不同的。

11.2. SELECT…FOR UPDATE总是不得不访问行数据页进行lock，所以不能进行对这些查询进行索引，就会减慢查询的速度。

12. 减少死锁

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. 隔离级别是如何影响性能的

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. 外键性能

14.1. 当更新行时候InnoDB都会检查外键，而且不会进行批处理或者当事务提交时候检查延迟。外键通常都有很多性能上的开销，但是这也保证了数据库的连续性。

14.2. 外键增加了很多行级锁，这将会影响到很多其它表不光是自己直接更新的那张表。

14.3. 外键会锁住子表，当父表在更新的时候。（select … for update在父表上这样执行就不会锁住子表）

15. 运行中的事务中的约束数量

15.1. 在一定数量内的运行中的事务和执行查询，InnoDB性能表现良好

15.1.1. 多个运行中查询可能导致互相之间干扰。Innodb_thread_concurrency能够被用作在InnoDB内核中限制线程数量。

15.1.2. 许多运行中事务会导致更多的锁，同时造成机器负载增加。

15.1.3. 如果有可能，在同一时间内限制一定数量的查询，在应用程序端做好队列。

16. 注意不要有太多的表

16.1. InnoDB自己的表定义（字典）缓存依赖于MySQL的table_cache变量值。

16.2. 只要打开一次，InnoDB就不会从缓存中移除这个表。

16.3. 每张表大概要消耗4KB以上的空间。MySQL 5.1 的 InnoDB已经将这个空间减少了50% 到 75%

16.4. 当重启时，每个表的统计将会被重新计算。所以第一次操作会是非常耗资源的。MySQL的table_cache将会串行执行这些操作。

17. 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的安全替代。

18. Next key lock（间隙锁）

18.1. Innodb不光会锁使用到行，也会锁这些行之间的行（称为间隙行）。

18.2. 这个是为了防止幽灵行的出现。 设置 “REPEATABLE READ”确实会让InnoDB可重复的。

18.3. 对于MySQL语句级别的复制是很有必要的。

18.4. 会让一些写负载大的机器上增加锁的情况。

18.5. 如果你没有设置和使用二进制log（用作复制和恢复的），那可以禁止这个间隙锁。

18.6. 在MySQL5.1中，如果你使用行级复制就可以安全的进行修改。

19. 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” 可以显示表近似的行数。

20. InnoDB和集体提交

20.1. 集体提交–提交多个事务通过单个日志写。这个可以提高非常多的性能，特别是没有做RAID的情况下。

20.2. 在MySQL5.0下，集体提交不能在有二进制log的情况下工作。由于XA（分布式事务）方法被实现，特别要小心从MysqL4.1进行的升级。

回到基本的服务器性能调优

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. 仅仅存储字典，它会自动增长，不用设置的太大。

2. 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. 通常不需要改变其默认值。