Skip to content

Latest commit

 

History

History
35 lines (31 loc) · 1.94 KB

数据库问题.md

File metadata and controls

35 lines (31 loc) · 1.94 KB
Raw

数据库问题

CREATE TABLE `dic_device_info` (
  `device_id` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '设备ID',
  `domain` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '域名',
  `platform` tinyint(4) NOT NULL DEFAULT '0' COMMENT '主平台',
  `sub_platform` tinyint(4) NOT NULL DEFAULT '0' COMMENT '小平台',
  `product_version` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '产品版本',
  `sdk_version` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'sdk版本',
  `third_party_id` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
  `alias` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '别名',
  `signature` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '签名',
  `user` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '绑定的用户',
  `topic_mask` bigint(32) NOT NULL DEFAULT '0' COMMENT '掩码',
  `message_count` tinyint(4) NOT NULL DEFAULT '0' COMMENT '消息计数',
  `register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  `last_active_time` timestamp NULL DEFAULT NULL COMMENT '最后一次消息时间',
  `bind_expire` timestamp NULL DEFAULT NULL COMMENT '绑定过期时间',
  `password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`device_id`,`domain`),
  KEY `idx_domain_product_version` (`domain`,`product_version`),
  KEY `idx_domain_user_platform` (`domain`,`user`,`platform`),
  KEY `idx_domain_alias` (`domain`,`alias`),
  KEY `idx_third_party_id` (`third_party_id`),
  KEY `idx_domain_user_device_id` (`domain`,`user`,`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

没有创建 domain device_id 的索引，导致 update 更新很慢：

update dic_device_info set platform = ?,product_version = ?,alias = ?,register_time = ?,last_active_time = ? where device_id = ? and domain = ?; with args: [2,"32.1","866625026221066","2018-02-05 13:00:47","2018-02-05 13:00:47","866625026221066_G4","news.163.com"]