以下SQL查询在本地需要大概2秒时间,服务器上时间更长.
explain select tz.id,tz.uid,tz.czlx,tz.qihao as tzqh,tz.addtime,tz.wanfa,tz.beishu,tz.ms,tz.money,tz.zt,qh.czlx,qh.qihao,qh.endtime from tz as tz inner join qihaoinfo as qh on tz.czlx = qh.czlx and tz.qihao = qh.qihao where tz.uid='796' and tz.zt =0 and qh.endtime > FROM_UNIXTIME( UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s');结束如下:
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
| 1 | SIMPLE | tz | ref | index_1 | index_1 | 4 | const | 16 | Using where |
| 1 | SIMPLE | qh | index | NULL | qihao | 435 | NULL | 94296 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
2 rows in setqh表的字段:
CREATE TABLE `NewTable` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`qihao` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`ctype` smallint(4) NOT NULL ,
`czlx` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`kjhm` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`starttime` datetime NULL DEFAULT NULL ,
`endtime` datetime NOT NULL ,
`addtime` date NOT NULL ,
`stoptime` int(4) NULL DEFAULT NULL ,
`zt` tinyint(4) NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `qihao` (`id`, `qihao`, `czlx`, `endtime`, `zt`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=94251
ROW_FORMAT=COMPACT
;mysql优化sql
explain select tz.id,tz.uid,tz.czlx,tz.qihao as tzqh,tz.addtime,tz.wanfa,tz.beishu,tz.ms,tz.money,tz.zt,qh.czlx,qh.qihao,qh.endtime from tz as tz inner join qihaoinfo as qh on tz.czlx = qh.czlx and tz.qihao = qh.qihao where tz.uid='796' and tz.zt =0 and qh.endtime > FROM_UNIXTIME( UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s');结束如下:
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
| 1 | SIMPLE | tz | ref | index_1 | index_1 | 4 | const | 16 | Using where |
| 1 | SIMPLE | qh | index | NULL | qihao | 435 | NULL | 94296 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+
2 rows in setqh表的字段:
CREATE TABLE `NewTable` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`qihao` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`ctype` smallint(4) NOT NULL ,
`czlx` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`kjhm` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`starttime` datetime NULL DEFAULT NULL ,
`endtime` datetime NOT NULL ,
`addtime` date NOT NULL ,
`stoptime` int(4) NULL DEFAULT NULL ,
`zt` tinyint(4) NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `qihao` (`id`, `qihao`, `czlx`, `endtime`, `zt`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=94251
ROW_FORMAT=COMPACT
;mysql优化sql
别把那么多字段建立在一个index里,建议INDEX分开建立
`id` int(11) NOT NULL AUTO_INCREMENT ,
`qihao` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`ctype` smallint(4) NOT NULL ,
`czlx` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`kjhm` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`starttime` datetime NULL DEFAULT NULL ,
`endtime` datetime NOT NULL ,
`addtime` date NOT NULL ,
`stoptime` int(4) NULL DEFAULT NULL ,
`zt` tinyint(4) NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
KEY `qihao`(`qihao`),
KEY `czlx`(czlx),
KEY `endtime`(endtime),
KEY `zt`(zt),
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=94251另外my.cnf里,合理调整一下 innodb_buffer_pool_size 的大小,详细看手册。