mysql的从头到脚优化之数据库引擎的选择

浏览数：34 / 时间：2015年06月12日

一. Mysql常用的存储引擎包括Innodb和Myisam以及memory引擎，但是最常用的莫过于Innodb引擎和MyISAM引擎，下边分别做下记录和比较：

　　下面思考下这几个问题：

你的数据库需要外键支持吗？
你的数据库需要事务支持吗？
你的数据库需要全文索引吗？
你的数据库的数据量有多大？
你经常使用什么样的查询模式？

　　思考上面的这些问题，可以让你找到更合适的方向，但这个并不是绝对的。如果你需要外键处理，那你就要选择Innodb，如果需要全文索引，那么MyIsam可能是一个比较好的选择，因为系统内建了这个全文的索引，然而，其实我们并不会实际的测试两百万数据，所以就算是我们使用Innodb引擎，也可以使用sphinx来完成索引。

　　数据的大小，也是影响选择的一个重要因素，Innodb更适合处理大量的高并发的数据，因为其良好的事务日志和故障恢复处理。数据库的大小决定了故障的恢复时间的长短，这会比较快，但是Myisam会需要几个小时甚至几天来恢复，这是一个灾难！

　　您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些，但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。

　　MyISAM引擎：

特性：
- 不支持事务：MyISAM的引擎不支持事务，所以对事物要求的场景不适合
- 表级锁定：锁定机制是表级索引，这虽然让锁定的实现成本很小，但是也大大的降低了并发的性能
- 读写相互阻塞：在读取数据的时候，阻塞的写入数据，并且在写入数据的时候，也会阻塞读取数据
- 只会缓存索引：可以通过Key_buffer_size来设定缓存数据索引的大小，但是不会缓存数据块，这就增加了和IO的交换读取
使用场景：
- 不需要事务支持（不支持事务）
- 数据修改相对比较少（读写相互阻塞）
- 以读为主的
- 并发相对比较低（锁定机制）
- 数据一直性要求不是很高
最佳实践：
- 尽量索引（缓存机制）
- 对于相对静态的数据，使用query_cache可以极大的提高访问效率
- MyIsam的count只有在全表扫描的时候，才会显得特别的高效，其他条件的count都是需要进行实际数据的访问的
- 分解一些比较大的SQL的执行，降低sql的执行时间，减少阻塞
- 降低并发数，某些高并发的场景通过应用来进行排队机制

　　Innodb引擎：

特性：
- 具有较好的事务支持，具备ACID的特性.
- 支持行级锁定，支持外键
- 能够缓存索引和数据，具有非常高效的索引缓存特性。
- 整个表和主键以cluster的方式进行存储，组成一棵平衡树。
- 所有的secondry index都会保存主键信息
适用场景：
- 适用于高并发的大量数据，数据性一致要求特别高的
- 需要事物支持（较好的事物支持）
- 行级锁定对高并发有很好的适应能力，但是需要确保查询是通过索引完成的
- 硬件设备的内存比较大，能较好的将数据的索引和数据块放到内存中，从而提高内存的缓存利用率，减少磁盘的IO
最佳实践：
- 尽可能缓存所有的数据和索引，从而提高响应速度。
- 避免主键更新，因为这会带来大量的数据移动。
- 在大批量小插入的时候，尽量自己控制事物而不要使用autocommit自动提交
- 主键尽可能的小，避免secondary index带来过大的空间负担