从 http://mysqldba.blogspot.com/2008/04/mysql-uc-2007-presentation-file.html 的PPT：
Record every Referral for Flickr Realtime
可以看到大师强烈推荐使用整型ID作为主键和索引。
从实际应用的情况中，之前曾有过一次使用28byte的字符串做主键的例子，只有200w数据的时候，竟然比一个700w数据的有更多列但使用整形主键的表慢很多，特别是进行批量操作时，很多时候更是有SQL执行超过1分钟，导致系统抖动非常厉害。
Start over
• Converted the URL into a 64-bit ID
• CONV(SUBSTR(MD5(Url),0,16),16,10)
• ==
• 64-bit number
• For added protection its unique for the
owner
• Reduced Primary key size to 8 bytes +
owner, object, object-type
INNODB & Strings
• Indexing a string takes a lot of space
• Indexing a large string takes even more
space
• Each index has its own 16KB page.
• Fragmentation across pages was hurting
the app - chewing up I/O
• That's a lot of disk space chewed up per
day
INNODB & High Concurrency of
String Writes
• Requirement: 300 ms for total db access
FOR ALL Apps
• Writes when the datafile(s) are greater
then the buffer_size slow down at high
concurrency
• 10 ms to 20 seconds sometimes for the
full transaction
• Noticed replication keeping up
Record every Referral for Flickr Realtime
可以看到大师强烈推荐使用整型ID作为主键和索引。
从实际应用的情况中，之前曾有过一次使用28byte的字符串做主键的例子，只有200w数据的时候，竟然比一个700w数据的有更多列但使用整形主键的表慢很多，特别是进行批量操作时，很多时候更是有SQL执行超过1分钟，导致系统抖动非常厉害。
Start over
• Converted the URL into a 64-bit ID
• CONV(SUBSTR(MD5(Url),0,16),16,10)
• ==
• 64-bit number
• For added protection its unique for the
owner
• Reduced Primary key size to 8 bytes +
owner, object, object-type
INNODB & Strings
• Indexing a string takes a lot of space
• Indexing a large string takes even more
space
• Each index has its own 16KB page.
• Fragmentation across pages was hurting
the app - chewing up I/O
• That's a lot of disk space chewed up per
day
INNODB & High Concurrency of
String Writes
• Requirement: 300 ms for total db access
FOR ALL Apps
• Writes when the datafile(s) are greater
then the buffer_size slow down at high
concurrency
• 10 ms to 20 seconds sometimes for the
full transaction
• Noticed replication keeping up
--EOF--
Leave a comment