Windows Firefox Python Android 开源 mysql linux php centos shell linux命令 HTML5 apache google nginx 程序员 微软 Ubuntu java wordpress

MySQL Innodb IO优化 之 数据库表设计

数据库表设计这块学问比较多,我这里单从互联网角度出发同时结合Innodb的特性给出一些设计方法供大家参考。本文构建大概分两分部分:Innodb的特性及设计中如何利用这种特性。

Innodb特性:

  • Innodb是索引聚集表, 存储结构是BTREE
  • Innodb的表的数据存储是有顺序的,默认是以主建排序,主建即是数据本身,不单独存放。
  • 如果没有主建,Innodb以第一个唯一索引排序,如果连唯一索引也没,Innodb内部会产生一个6字节的字段排序(这个也是性能杀手,所以对这块如果不想花太多时间去想这个事,就添加一个自增的列无业务意义做为主建即可)
  • Innodb表普通索引存储需要包含主建(或是Innodb表聚集的字段)

如何利用这些特性:

  • 对于Insert操作要求比高的表: 建议增加一个自增的列做主建,这样减少数据的写入造成Innodb表在存储上不断的拆叶排序的操作。 通过添加一个自增的列做主建从而达到Innodb表的写入都是顺序IO的形态。所以这种情况,保证在1W/S左右的Insert也是比较容易的。添加一个主建还有另外一个好处,真正的条件将会成为唯一索引或是普通索引, 这样索引单独存放起来后,整体上比原来的表文件会小很多,这样基于条件的查询,可以从一个较少的文件快速定位到需要的行。这样也有机会利用到索引覆盖。
  • 另一种场景,写入少,同时每次读多(读取不是一条记录),这种场景可以考虑不要使用自增的列做为主建,就使用查询条件或是查询条和其它通达到唯一,定义为主建。这样查询就可以一次读到数据。还一种场景如存好友关系,或是股票信息,特别好友关系表类的数据,可以考虑使用两个用户的Id做联合主建,查询时条件中只用自已的Id读所有用户的数据,这样就是一个顺序IO的请求。同样这种情景下对一些数据就可以考虑冗余,减少请求反向的数据操作。
  • 更新最好基于主建或是唯一索引来做,这样才能有机会利用Innodb的行级锁。
  • 互联网中还有一种观点是:页面展现什么,就存成什么样的表。这种在CMS中还能适用,在WEB2.0及相关的应用这种观念就行不通。但可以考虑适当的多处写,实现数据的快速读取及索引表的引入。

从业务形态上来看:

建议数据在设计阶段就要考虑那些是大表,可以分为多少个业务,怎么能核心功能拆分。这块举个例子:大家经常听到的淘宝的用户库,商品库,收藏夹库,交易库,评论库等。及其它我们经常也能听到的:认证库,好友关系库等等。到业务形态上后,可以根据不同的形态选择不同的软件来做不要只看到mysql了,这块的设计可以把nosql类的东西也要考虑进来,最终设计数据库的模型。

延伸阅读

评论