MySQL

MySQL

记MySQL MERGE引擎分表存储实战

孤魂 发表了文章 • 0 个评论 • 1425 次浏览 • 2016-03-12 18:50 • 来自相关话题

最近收集的数据量比较大,都是在千万级别,使用MySQL数据表,为了解决性能问题,需要使用到MySQL分表存储来解决服务器IO的问题。数据库的建立使用index_id做索引,以一百万为单位,超过一百万,自动建立新表。CREATE TABLE `ms_index_data_0` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_index` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_index_data_0`);

CREATE TABLE `ms_post_data_0` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_post` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_post_data_0`);
如果到时候新增加表,只需要ALETR一下即可:ALTER TABLE `ms_index` ENGINE=MRG_MyISAM UNION=(`ms_index_data_0`,`ms_index_data_1`) INSERT_METHOD=LAST; 查看全部
最近收集的数据量比较大,都是在千万级别,使用MySQL数据表,为了解决性能问题,需要使用到MySQL分表存储来解决服务器IO的问题。数据库的建立使用index_id做索引,以一百万为单位,超过一百万,自动建立新表。
CREATE TABLE `ms_index_data_0` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_index` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_index_data_0`);

CREATE TABLE `ms_post_data_0` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_post` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_post_data_0`);
如果到时候新增加表,只需要ALETR一下即可:
ALTER TABLE `ms_index` ENGINE=MRG_MyISAM UNION=(`ms_index_data_0`,`ms_index_data_1`) INSERT_METHOD=LAST;  

记MySQL MERGE引擎分表存储实战

孤魂 发表了文章 • 0 个评论 • 1425 次浏览 • 2016-03-12 18:50 • 来自相关话题

最近收集的数据量比较大,都是在千万级别,使用MySQL数据表,为了解决性能问题,需要使用到MySQL分表存储来解决服务器IO的问题。数据库的建立使用index_id做索引,以一百万为单位,超过一百万,自动建立新表。CREATE TABLE `ms_index_data_0` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_index` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_index_data_0`);

CREATE TABLE `ms_post_data_0` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_post` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_post_data_0`);
如果到时候新增加表,只需要ALETR一下即可:ALTER TABLE `ms_index` ENGINE=MRG_MyISAM UNION=(`ms_index_data_0`,`ms_index_data_1`) INSERT_METHOD=LAST; 查看全部
最近收集的数据量比较大,都是在千万级别,使用MySQL数据表,为了解决性能问题,需要使用到MySQL分表存储来解决服务器IO的问题。数据库的建立使用index_id做索引,以一百万为单位,超过一百万,自动建立新表。
CREATE TABLE `ms_index_data_0` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_index` (
    `index_id` int(10) NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `category_id` int(5) NOT NULL,
    `add_time` int(10) NOT NULL,
    `update_time` int(10) NOT NULL,
    `user_name` varchar(100) NOT NULL,
    `question_subject` varchar(255) NOT NULL,
    `question_detail` text,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`index_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `status` (`status`),
    KEY `category_id` (`category_id`),
    KEY `update_time` (`update_time`),
    KEY `user_name` (`user_name`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_index_data_0`);

CREATE TABLE `ms_post_data_0` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ms_post` (
    `post_id` int(10) NOT NULL AUTO_INCREMENT,
    `index_id` int(10) NOT NULL,
    `add_time` int(10) NOT NULL,
    `is_best` tinyint(1) NOT NULL DEFAULT '0',
    `user_name` varchar(100) NOT NULL,
    `question_detail` text NOT NULL,
    `repeat` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`),
    UNIQUE KEY `repeat` (`repeat`),
    KEY `add_time` (`add_time`),
    KEY `index_id` (`index_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`ms_post_data_0`);
如果到时候新增加表,只需要ALETR一下即可:
ALTER TABLE `ms_index` ENGINE=MRG_MyISAM UNION=(`ms_index_data_0`,`ms_index_data_1`) INSERT_METHOD=LAST;