我知道,如果可以保证表中的所有行的长度都相同(通过避免可以为空的列,而不使用任何varchar、text或blob列),那么依赖于正在使用的数据库存储引擎,可以找到性能优势。我不清楚这对InnoDB有多大的影响,它的表安排很有趣。让我们举个例子:我有下表
CREATE TABLE `PlayerGameRcd` (
`User` SMALLINT UNSIGNED NOT NULL,
`Game` MEDIUMINT UNSIGNED NOT NULL,
`GameResult` ENUM('Quit',
'Kicked by Vote',
'Kicked by Admin',
'Kicked by System',
'Finished 5th',
'Finished 4th',
'Finished 3rd',
'Finished 2nd',
'Finished 1st',
'Game Aborted',
'Playing',
'Hide'
) NOT NULL DEFAULT 'Playing',
`Inherited` TINYINT NOT NULL,
`GameCounts` TINYINT NOT NULL,
`Colour` TINYINT UNSIGNED NOT NULL,
`Score` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
`NumLongTurns` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`Notes` MEDIUMTEXT,
`CurrentOccupant` TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`Game`, `User`),
UNIQUE KEY `PGR_multi_uk` (`Game`, `CurrentOccupant`, `Colour`),
INDEX `Stats_ind_PGR` (`GameCounts`, `GameResult`, `Score`, `User`),
INDEX `GameList_ind_PGR` (`User`, `CurrentOccupant`, `Game`, `Colour`),
CONSTRAINT `Constr_PlayerGameRcd_User_fk`
FOREIGN KEY `User_fk` (`User`) REFERENCES `User` (`UserID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `Constr_PlayerGameRcd_Game_fk`
FOREIGN KEY `Game_fk` (`Game`) REFERENCES `Game` (`GameID`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci
唯一可以为空的列是
Notes
中文本。这张桌子现在有33097行(我认为这张桌子还小)。在这些行中,只有61个在
笔记
. 例如,如果添加一个新表来存储Notes列并在必要时执行左联接,我可以看到多少改进?