MySQL中主键的选择与磁盘性能

netwenk

偶然看到了“<a href="http://www.slideshare.net/frankmashraqi/fotolog-scaling-the-worlds-largest-photo-blogging-community" target="_blank" target="_blank">Fotolog: Scaling the World's Largest Photo Blogging Community</a>”，才发现很多数据库的优化其实道理都很简单，至高境界是当你面对问题时，是否真正做出了自己的思考，而不仅仅只是经验主义的惯性使然：<br><br>本文案例背景介绍：一个图片网站，每张图片都有很多评论。浏览时会执行：SELECT ... FROM ... WHERE photo_identifier = ... ORDER BY posted ...<br><br>在“Old Schema”的解决方案中，一切都显得中规中矩：使用了最常见的自增字段identifier作为主键，同时使用photo_identifier, posted作为索引。<br><br><a href="http://blog.chinaunix.net/attachment/201107/1/377971_13095023033m3Z.jpg" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/1/377971_13095023033m3Z.jpg" .load="imgResize(this, 650);" ;="" border="0"></a><br><br>数据按照主键进行排序，当执行查询时，根据索引进行数据对位。不过这里的问题在于，同一个图片的评论数据，在磁盘上会分散到多个数据页之上。这也就意味着在查询这些数据的时候，磁盘要不断的调整数据定位。这是一个不小的IO开销。<br><br><a href="http://blog.chinaunix.net/attachment/201107/1/377971_1309502341AHqq.jpg" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/1/377971_1309502341AHqq.jpg" .load="imgResize(this, 650);" ;="" border="0"></a><br><br>在“New Schema”的解决方案中，虽然也使用了自增字段，但是采用的是联合主键photo_identifier,
posted,identifier，并把identifier作为索引。同时需要注意的是，表类型使用的是Innodb，并缩减了自增字段的长度，这
样，主键的长度会短一些，有助于提升Innodb的性能。<br><br><a href="http://blog.chinaunix.net/attachment/201107/1/377971_13095023737w3J.jpg" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/1/377971_13095023737w3J.jpg" .load="imgResize(this, 650);" ;="" border="0"></a><br><br>数据按照联合主键进行排序，由于photo_identifier字段是联合主键中的第一个字段，所以对于一张图片而言，它所有的评论都保存在磁盘中相邻
的位置上。在这种情况下，当对数据进行定位时，Innodb会进行优化：“Pending
read”，所谓Pendingread，指的是当发生一次read的时候，并不一定是直接从文件系统里“物理read”，而只是从缓冲池中“逻辑
read”，Innodb内部的优化机制可以合并多次“逻辑read”为一次“物理read”，从而降低IO消耗，提高磁盘性能。<br><br><a href="http://blog.chinaunix.net/attachment/201107/1/377971_1309502393WU6i.jpg" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/1/377971_1309502393WU6i.jpg" .load="imgResize(this, 650);" ;="" border="0"></a><br><br>还有一个问题要考虑，使用photo_identifier,
posted,identifier联合主键时，如果对一个“旧图片”（photo_identifier较小的图片）发表评论的时候，数据会记录在比较
靠前的数据页上（因为数据在硬盘上保存的物理顺序是按主键排序的），和直接使用identifier自增主键相比，这样会引起一个不小的IO负担，因为自
增主键在添加新数据时，新数据始终位于数据文件的结尾。所以，实际应用中，文中所示的方法是否可用，还要从客观情况分析而定，比如说评论主要集中在“新图
片”上，则IO问题不大，因为“新图片”的记录位于数据文件靠后的位置上，但是如果评论分布的图片比较随机的话，那么此方法是否适用则需要斟酌，不过也可
以变通着来，比如说在主从服务器的结构里，我们可以在主服务器上使用identifier自增主键，在从服务器上使用
photo_identifier,posted, identifier联合主键，这样既保证了写操作的效率，也保证了读操作的效率<br><br>转自：<a href="http://hi.baidu.com/thinkinginlamp/blog/item/8905564e02d21b01b3de0580.html" target="_blank" target="_blank">http://hi.baidu.com/thinkinginla ... d21b01b3de0580.html</a><br><br>