先贴表结构:CREATE TABLE `mobile_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`phone` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`comefrom` varchar(10) DEFAULT NULL,
`createtime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `mobile_user_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`openid` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`bindonce` tinyint(1) NOT NULL DEFAULT '0',
`unfollowed` tinyint(1) NOT NULL DEFAULT '0',
`admin` tinyint(1) NOT NULL DEFAULT '0',
`comefrom` varchar(10) NOT NULL DEFAULT '0',
`nickname` varchar(45) DEFAULT NULL,
`followtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `index_openid` (`openid`),
KEY `index_phone` (`phone`),
KEY `user_nickname` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `mobile_area` (
`id` int(11) NOT NULL,
`num` varchar(45) DEFAULT NULL,
`area` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ma_num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mobile_user实际上是从user表精简出来的,这两张表都有10W左右数据,mobile_area表有30W数据,然后执行SQL:
select u.*,a.area from mobile_user u
left join mobile_area a
ON a.num = SUBSTRING(u.phone, 1, 7)
limit 100;
#15秒以上select u.*,a.area from user u
left join mobile_area a
ON a.num = SUBSTRING(u.phone, 1, 7)
limit 100;
#0.015秒左右
上面两个语句是一样的作用,而且对两张user表的phone字段都做了索引,为什么第一个sql效率会差这么多,
另外问下,查询手机归属地有没有什么好的途径,觉得这样直接连表查不是很合理~
`id` int(11) NOT NULL AUTO_INCREMENT,
`phone` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`comefrom` varchar(10) DEFAULT NULL,
`createtime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `mobile_user_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`openid` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`bindonce` tinyint(1) NOT NULL DEFAULT '0',
`unfollowed` tinyint(1) NOT NULL DEFAULT '0',
`admin` tinyint(1) NOT NULL DEFAULT '0',
`comefrom` varchar(10) NOT NULL DEFAULT '0',
`nickname` varchar(45) DEFAULT NULL,
`followtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `index_openid` (`openid`),
KEY `index_phone` (`phone`),
KEY `user_nickname` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `mobile_area` (
`id` int(11) NOT NULL,
`num` varchar(45) DEFAULT NULL,
`area` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ma_num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mobile_user实际上是从user表精简出来的,这两张表都有10W左右数据,mobile_area表有30W数据,然后执行SQL:
select u.*,a.area from mobile_user u
left join mobile_area a
ON a.num = SUBSTRING(u.phone, 1, 7)
limit 100;
#15秒以上select u.*,a.area from user u
left join mobile_area a
ON a.num = SUBSTRING(u.phone, 1, 7)
limit 100;
#0.015秒左右
上面两个语句是一样的作用,而且对两张user表的phone字段都做了索引,为什么第一个sql效率会差这么多,
另外问下,查询手机归属地有没有什么好的途径,觉得这样直接连表查不是很合理~
以文本方式贴出
explain select ...
show index from ..
以供分析。