mysql表的列数量与效率之间的关系

文章的大致结论：

MyISAM表，

fixed_row_size时，列数量对效率会产生微小的影响。

dynamic_row_size时，列数量过多，会对效率产生巨大的负面作用

innodb表，

由于不存在row-size的区别，char与varchar之间对效率影响小

但是，过多的列会长生更为巨大的负面作用

It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row processing speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables - First containing single tinyint column which is almost shortest type possible (CHAR(0) could be taking less space), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the same row length but have number of column different 50 times. Finally I have created 4th table which is also 100 columns but one of them is VARCHAR causes raw format to be dynamic.

More specially:

PLAIN TEXT

SQL:

1. CREATE TABLE `t1` (
2.   `t1` tinyint(3) UNSIGNED NOT NULL
3. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

PLAIN TEXT

SQL:

1. CREATE TABLE `t1c99` (
2.   `t1` tinyint(3) UNSIGNED NOT NULL,
3.   `c99` char(99) NOT NULL
4. 管理员在2009年8月13日编辑了该文章文章。

-->

阅读(3697) | 评论(0) | 转发(0) |

0

上一篇：crontab执行时间偏差

下一篇：shell管道可能引发的逻辑错误