mysql and 数量_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:

SQL:

CREATE TABLE `t1` (

`t1` tinyint(3) UNSIGNED NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

SQL:

CREATE TABLE `t1c99` (

`t1` tinyint(3) UNSIGNED NOT NULL,

`c99` char(99) NOT NULL

管理员在2009年8月13日编辑了该文章文章。 -->

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