环境:
MySQL数据库,InnoDB引擎,数据库之前有K-V内存cache。问题:
有必要分表吗?如下(已分表,有必要吗?),就是把访问频率不同的属性,分在了两个不同的表里。另外,question表中,只有一个VARCHAR类型的字段,其他都是整形,这也是我考虑分表的一个原因,但我也确定有没有好处。有必要如下这样分表吗?CREATE TABLE IF NOT EXISTS question (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sub_id1 INT UNSIGNED NOT NULL DEFAULT 0',
sub_id2 TINYINT UNSIGNED NOT NULL DEFAULT 0,
time1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
time2 TIMESTAMP NOT NULL,
`status` TINYINT NOT NULL DEFAULT 0',
scope TINYINT NOT NULL DEFAULT 0,
title VARCHAR(64) NOT NULL DEFAULT '',
source TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX (time2, sub_id2, sub_id1),
INDEX (time1, sub_id1)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ciCREATE TABLE IF NOT EXISTS question_ex (
question_id INT UNSIGNED NOT NULL,
`desc` TEXT,
`image` VARCHAR(128) NOT NULL DEFAULT ''',
aid INT UNSIGNED NOT NULL',
em VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY (question_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci;question表,存储问题的基本信息,这些基本信息需要经常被列出来,因为多个界面是问题列表,列表中的一个条目的形式是“[问题类型]问题标题 问题时间”。只有一到两个界面是问题详情,也就是question_ex表中的数据。通过点击上述问题条目,可以进入问题详情界面。
MySQL数据库,InnoDB引擎,数据库之前有K-V内存cache。问题:
有必要分表吗?如下(已分表,有必要吗?),就是把访问频率不同的属性,分在了两个不同的表里。另外,question表中,只有一个VARCHAR类型的字段,其他都是整形,这也是我考虑分表的一个原因,但我也确定有没有好处。有必要如下这样分表吗?CREATE TABLE IF NOT EXISTS question (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sub_id1 INT UNSIGNED NOT NULL DEFAULT 0',
sub_id2 TINYINT UNSIGNED NOT NULL DEFAULT 0,
time1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
time2 TIMESTAMP NOT NULL,
`status` TINYINT NOT NULL DEFAULT 0',
scope TINYINT NOT NULL DEFAULT 0,
title VARCHAR(64) NOT NULL DEFAULT '',
source TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX (time2, sub_id2, sub_id1),
INDEX (time1, sub_id1)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ciCREATE TABLE IF NOT EXISTS question_ex (
question_id INT UNSIGNED NOT NULL,
`desc` TEXT,
`image` VARCHAR(128) NOT NULL DEFAULT ''',
aid INT UNSIGNED NOT NULL',
em VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY (question_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci;question表,存储问题的基本信息,这些基本信息需要经常被列出来,因为多个界面是问题列表,列表中的一个条目的形式是“[问题类型]问题标题 问题时间”。只有一到两个界面是问题详情,也就是question_ex表中的数据。通过点击上述问题条目,可以进入问题详情界面。
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sub_id1 INT UNSIGNED NOT NULL DEFAULT 0',
sub_id2 TINYINT UNSIGNED NOT NULL DEFAULT 0,
time1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
time2 TIMESTAMP NOT NULL,
`status` TINYINT NOT NULL DEFAULT 0',
scope TINYINT NOT NULL DEFAULT 0,
title VARCHAR(64) NOT NULL DEFAULT '',
source TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX (sub_id2, time2),
INDEX (sub_id1, time1),
INDEX (sub_id1, sub_id2, time1),
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci;常用查询:
SELECT id, sub_id1, time2, title FROM question WHERE `status`=1 ORDER BY time2 DESC;
SELECT id, sub_id2, time2, title FROM question WHERE sub_id2=X AND `status`=1 ORDER BY answer_time DESC;
SELECT id, sub_id2, time2, title FROM question WHERE `status`=1 AND title LIKE '%xxxx%' ORDER BY time2 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND `status`=1 ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND sub_id2=X ORDER BY time1 DESC;
SELECT `desc`, `image`, `aid`, `em` FROM question WHERE id=X LIMIT 1;如果分表,语句是:
SELECT `desc`, `image`, `aid`, `em` FROM question_ex WHERE question_id=X LIMIT 1;而如果分表的话,两张表(question、question_ex)都会更小,因为字段更少。
常用读语句(前6句是问题列表,最后1句是问题详情):
SELECT id, sub_id1, time2, title FROM question WHERE `status`=1 ORDER BY time2 DESC;
SELECT id, sub_id2, time2, title FROM question WHERE sub_id2=X AND `status`=1 ORDER BY answer_time DESC;
SELECT id, sub_id2, time2, title FROM question WHERE `status`=1 AND title LIKE '%xxxx%' ORDER BY time2 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND `status`=1 ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND sub_id2=X ORDER BY time1 DESC;SELECT `desc`, `image`, `aid`, `em` FROM question_ex WHERE question_id=X LIMIT 1;【不分表的情况下】,question表的定义如下:
CREATE TABLE IF NOT EXISTS question (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sub_id1 INT UNSIGNED NOT NULL DEFAULT 0',
sub_id2 TINYINT UNSIGNED NOT NULL DEFAULT 0,
time1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
time2 TIMESTAMP NOT NULL,
`status` TINYINT NOT NULL DEFAULT 0',
scope TINYINT NOT NULL DEFAULT 0,
title VARCHAR(64) NOT NULL DEFAULT '',
source TINYINT NOT NULL DEFAULT 0,
`desc` TEXT, -- 来自question_ex表
`image` VARCHAR(128) NOT NULL DEFAULT ''', -- 来自question_ex表
aid INT UNSIGNED NOT NULL', -- 来自question_ex表
em VARCHAR(32) NOT NULL DEFAULT '', -- 来自question_ex表
PRIMARY KEY (id),
INDEX (time2, sub_id2, sub_id1),
INDEX (time1, sub_id1)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci;常用读查询(前6句是问题列表,和上面的6句相同,最后1句是问题详情,和上面比有所改动):
SELECT id, sub_id1, time2, title FROM question WHERE `status`=1 ORDER BY time2 DESC;
SELECT id, sub_id2, time2, title FROM question WHERE sub_id2=X AND `status`=1 ORDER BY answer_time DESC;
SELECT id, sub_id2, time2, title FROM question WHERE `status`=1 AND title LIKE '%xxxx%' ORDER BY time2 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND `status`=1 ORDER BY time1 DESC;
SELECT id, sub_id2, time1, title FROM question WHERE sub_id1=X AND sub_id2=X ORDER BY time1 DESC;SELECT `desc`, `image`, `aid`, `em` FROM question WHERE id=X LIMIT 1;