mysql支持多种存储引擎,在处理不同类型的应用时,可以通过选择使用不同的存储引擎提高应用的效率,或者提供灵活的存储。
最常使用的2种存储引擎:
1). Myisam是Mysql的默认存储引擎,当create创建新表时,未指定新表的存储引擎时,默认使用Myisam。
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是.frm(存储表定义)、.MYD (MYData,存储数据)、.MYI (MYIndex,存储索引)。数据文件
和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。
2). InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间
以保留数据和索引。(frm(存储表定义),索引和数据是存放在一起的,而且可一选择每个表单独存放或是使用共享空间)。
两种常用的存储引擎对
特点 Myisam InnoDB
存储限制 没有 64TB
事务安全 支持
锁机制 表锁 行锁
B树索引 支持 支持
哈希索引 支持
全文索引 支持
集群索引 支持
数据缓存 支持
索引缓存 支持 支持
数据可压缩 支持
空间使用 低 高
内存使用 低 高
批量插入的速度 高 低
支持外键 支持
数据库优化的架构设计:
1.适度冗余,让查询尽量减少join。(反范式)
2.大字段的垂直拆分,数据库是以单条记录为单位存放的,当没有索引的时候,必须读取所有字段,将大字段拆分后,在访问其他字段时可大大降低磁盘IO。某些字段的访问频率高,而另外一些低,也可将其拆分。
3.大表的水平分拆。
4.统计的准实时化,将实时统计改为定时统计,比如回帖总数,在线人数。
5.尽量使用小的数据类型一减少磁盘空间,例如浮点型可以通过乘以一个固定的系数转换为整形。
查询的优化:
1.小结果集驱动大结果集。
2.尽量避免复杂的join和子查询。查询涉及的表越多需要锁定的资源越多,会阻塞其他线程。可一将其拆分为多个较为简单的查询语句。
3.合理的利用索引,较为频繁的作为查询条件的字段,唯一性太差的不适合作为单独的索引,更新频繁的字段不适合,不会出现在where中的字段不应该建索引。
4.尽可能介绍join语句中的循环次数,优先优化内层循环。被驱动表join的条件字段添加索引,当join的条件字段没有索引时,join Buffer参数join_buffer_size可以设置大些。
5.对于排序,尽量通过索引,避免需要mysql通过排序算法进行排序操作返回结果,当无法避免排序操作时,加大max_length_for_sort_data参数,去掉不必要的返回字段(这个任何时候都应该这样吧),增大sort_buffer_size的设置。
6.group by和distinct操作也尽量利用索引。
最常使用的2种存储引擎:
1). Myisam是Mysql的默认存储引擎,当create创建新表时,未指定新表的存储引擎时,默认使用Myisam。
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是.frm(存储表定义)、.MYD (MYData,存储数据)、.MYI (MYIndex,存储索引)。数据文件
和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。
2). InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间
以保留数据和索引。(frm(存储表定义),索引和数据是存放在一起的,而且可一选择每个表单独存放或是使用共享空间)。
两种常用的存储引擎对
特点 Myisam InnoDB
存储限制 没有 64TB
事务安全 支持
锁机制 表锁 行锁
B树索引 支持 支持
哈希索引 支持
全文索引 支持
集群索引 支持
数据缓存 支持
索引缓存 支持 支持
数据可压缩 支持
空间使用 低 高
内存使用 低 高
批量插入的速度 高 低
支持外键 支持
数据库优化的架构设计:
1.适度冗余,让查询尽量减少join。(反范式)
2.大字段的垂直拆分,数据库是以单条记录为单位存放的,当没有索引的时候,必须读取所有字段,将大字段拆分后,在访问其他字段时可大大降低磁盘IO。某些字段的访问频率高,而另外一些低,也可将其拆分。
3.大表的水平分拆。
4.统计的准实时化,将实时统计改为定时统计,比如回帖总数,在线人数。
5.尽量使用小的数据类型一减少磁盘空间,例如浮点型可以通过乘以一个固定的系数转换为整形。
查询的优化:
1.小结果集驱动大结果集。
2.尽量避免复杂的join和子查询。查询涉及的表越多需要锁定的资源越多,会阻塞其他线程。可一将其拆分为多个较为简单的查询语句。
3.合理的利用索引,较为频繁的作为查询条件的字段,唯一性太差的不适合作为单独的索引,更新频繁的字段不适合,不会出现在where中的字段不应该建索引。
4.尽可能介绍join语句中的循环次数,优先优化内层循环。被驱动表join的条件字段添加索引,当join的条件字段没有索引时,join Buffer参数join_buffer_size可以设置大些。
5.对于排序,尽量通过索引,避免需要mysql通过排序算法进行排序操作返回结果,当无法避免排序操作时,加大max_length_for_sort_data参数,去掉不必要的返回字段(这个任何时候都应该这样吧),增大sort_buffer_size的设置。
6.group by和distinct操作也尽量利用索引。