数据库表涉及-逻辑删除

对于只进行逻辑删除的表，如

CREATE TABLE `pics` (
  `pid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `aid` bigint(20) unsigned NOT NULL,
  `pic_url` varchar(255) NOT NULL,
  `is_cover` tinyint(1) default 0,
  `is_local` tinyint(1) default 1,
  `type` int(10) unsigned default 0,
  `description` text,
  `ext` text,
  `uid` bigint(20) unsigned NOT NULL,
  `uip` int(10) unsigned default 0,
  `create_time` int(10) unsigned default 0,
  `update_time` int(10) unsigned default 0,
  `is_audited` tinyint(1) default 0,
  `is_deleted` tinyint(1) default 0,
  PRIMARY KEY (`pid`),
  KEY `aid` (`aid`,`create_time`),
  KEY `idx_user_time_del` (`uid`,`create_time`,`is_deleted`),
  KEY `idx_user_uptime_del` (`uid`,`update_time`,`is_deleted`),
  KEY `idx_time_del` (`create_time`,`is_deleted`),
  KEY `idx_uptime_del` (`update_time`,`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建议

pics表中只存储未删除的数据，不需要is_delete字段
另建一张表存储删除的数据，如pic_d
用户删除的话就把记录从pics中删除，插入到pic_d中，myisam表引擎即可，好处不言而喻

CREATE TABLE `pics_d` (
  `pid` bigint(20) ,
  `aid` bigint(20) unsigned NOT NULL,
  `pic_url` varchar(255) NOT NULL,
  `is_cover` tinyint(1) default 0,
  `is_local` tinyint(1) default 1,
  `type` int(10) unsigned default 0,
  `description` text,
  `ext` text,
  `uid` bigint(20) unsigned NOT NULL,
  `uip` int(10) unsigned default 0,
  `create_time` int(10) unsigned default 0,
  `update_time` int(10) unsigned default 0,
  `is_audited` tinyint(1) default 0,
  `is_deleted` tinyint(1) default 0
) ENGINE=Myisam DEFAULT CHARSET=utf8;