CREATE TABLE
t_mobilesms_11
(
id
bigint(20) NOT NULL AUTO_INCREMENT,
userId
varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘用户id,创建任务时的userid’,
mobile
varchar(24) NOT NULL DEFAULT ‘’ COMMENT ‘手机号码’,
billMonth
varchar(32) DEFAULT NULL COMMENT ‘账单月’,
time
varchar(32) DEFAULT NULL COMMENT ‘收/发短信时间’,
peerNumber
varchar(64) NOT NULL COMMENT ‘对方号码’,
location
varchar(64) DEFAULT NULL COMMENT ‘通信地(自己的)’,
sendType
varchar(16) DEFAULT NULL COMMENT ‘SEND-发送; RECEIVE-收取’,
msgType
varchar(8) DEFAULT NULL COMMENT ‘SMS-短信; MSS-彩信’,
serviceName
varchar(256) DEFAULT NULL COMMENT ‘业务名称. e.g. 点对点(网内)’,
fee
int(11) DEFAULT NULL COMMENT ‘通信费(单位分)’,
createTime
datetime DEFAULT NULL COMMENT ‘创建时间’,
lastModifyTime
datetime DEFAULT NULL COMMENT ‘最后修改时间’,
PRIMARY KEY (id
),
KEY联合索引
(userId
,mobile
,billMonth
)
) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT=‘手机短信详情’
一、联合索引测试
1. 查询条件为 userid 结果: 可以通过key看到,联合索引有效
EXPLAIN SELECT * FROM t_mobilesms_11
WHERE userid=‘2222’