表的字段越多会影响查询速度吗？

Question

mysql 5.1.41建表语句
CREATE TABLE IF NOT EXISTS `company` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET gbk NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `work` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET gbk NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `pact` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `cid` int(4) NOT NULL,
  `wid` int(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  KEY `wid` (`wid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `spec` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `pid` int(6) NOT NULL,
  `color` varchar(100) CHARACTER SET gbk NOT NULL,
  `size` varchar(10) CHARACTER SET gbk NOT NULL,
  `qty` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `goods_in` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `goods_out` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `barcode` (
  `barcode` int(11) NOT NULL AUTO_INCREMENT,
  `in_id` int(6) NOT NULL DEFAULT '0',
  `out_id` int(6) NOT NULL DEFAULT '0',
  `sid` int(6) NOT NULL,
  `qty` int(3) NOT NULL,
  `in_wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
  `out_wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`barcode`),
  KEY `in_id` (`in_id`),
  KEY `out_id` (`out_id`),
  KEY `spec_id` (`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;ALTER TABLE `pact`
  ADD CONSTRAINT `pact_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pact_ibfk_2` FOREIGN KEY (`wid`) REFERENCES `work` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE `spec`
  ADD CONSTRAINT `spec_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pact` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE `barcode`
  ADD CONSTRAINT `barcode_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `barcode_ibfk_2` FOREIGN KEY (`out_id`) REFERENCES `goods_out` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `barcode_ibfk_3` FOREIGN KEY (`sid`) REFERENCES `spec` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;同样的查询语句SELECT `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM `goods_in` AS `g`,`barcode` AS `b`,`spec` AS `s`,`pact` AS `p`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`pid` = `p`.`id` AND `p`.`cid` = `c`.`id` AND `b`.`in_id` <> 0
GROUP BY `b`.`in_id`
ORDER BY `b`.`in_id` DESC
LIMIT 0, 100;数据库A中的表是原始数据，上面查询语句中涉及的表都有其他字段
数据库B是由数据库A导入，并把不相关的表和相关表但不相关的字段都删除掉(上面的建表语句可以看做是数据库B的建表语句，只不过我建立数据库B时，是把数据库A导出，然后删除掉不相关表和约束后，建立数据库B再导入，导入后再把数据库B中的表的不相关字段删掉，然后再建立约束)
同样的这条查询语句，数据库A需要1.5秒以上，而数据库B则只需要0.5秒左右
使用EXPLAIN进行跟踪
数据库A
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|  1 | SIMPLE      | c     | index  | PRIMARY       | name    | 102     | NULL          |   89 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref    | PRIMARY,cid   | cid     | 4       | testA.c.id    |    3 | Using index                                  |
|  1 | SIMPLE      | s     | ref    | PRIMARY,pid   | pid     | 4       | testA.p.id    |    6 | Using index                                  |
|  1 | SIMPLE      | b     | ref    | sid,in_id     | sid     | 4       | testA.s.id    |    6 | Using where                                  |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY       | PRIMARY | 4       | testA.b.in_id |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
数据库B
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|  1 | SIMPLE      | c     | index  | PRIMARY       | name    | 102     | NULL          |   89 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref    | PRIMARY,cid   | cid     | 4       | testB.c.id    |    2 | Using index                                  |
|  1 | SIMPLE      | s     | ref    | PRIMARY,pid   | pid     | 4       | testB.p.id    |    6 | Using index                                  |
|  1 | SIMPLE      | b     | ref    | sid,in_id     | sid     | 4       | testB.s.id    |    8 | Using where                                  |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY       | PRIMARY | 4       | testB.b.in_id |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+P.S.在不使用GROUP_CONCAT函数的情况下，速度差不多

Answer 1

嗯...再次测试发现和约束也没关系
在spec表中添加了cid和wid，作为company.id和work.id的外键
之后查询语句修改为
SELECT `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM `goods_in` AS `g`,`barcode` AS `b`,`spec` AS `s`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`cid` = `c`.`id` AND `g`.`id` <> 0
GROUP BY `g`.`id`
ORDER BY `g`.`id` DESC
LIMIT 0,100;
再explain
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | g     | range  | PRIMARY       | PRIMARY | 4       | NULL        |  531 | Using where |
|  1 | SIMPLE      | b     | ref    | in_id,sid     | in_id   | 4       | testA.g.id  |   26 |             |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,cid   | PRIMARY | 4       | testA.b.sid |    1 |             |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | testA.s.cid |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
现在查询速度只要0.1~0.3秒...

Answer 2

.........问个问题...
把命令行关了，而且重启服务器了，之后再进入命令行的话，要怎么查看到之前输入的查询语句呢？？
我刚刚明明就是用上面那句语句查询的，怎么现在发现EXPLAIN后，结果不一样了...而且速度明显变慢了...
但查询语句貌似一样啊(不确定是不是我上面帖的那句...)
有什么办法可以查看以前输入的查询语句吗？？

Answer 3

同一个命令第二次后的查询，就直接从缓存中取了，都不需要解析啥的一套。直接取结果显示。SELECT SQL_NO_CACHE id, name FROM customer;要查看执行速度，用SQL_NO_CACHE 来修饰。

Answer 4

命令行没有保存这个功能，关闭就没有了。你可以用toad for mysql来，或者其他的客户端工具来操作。

Answer 5

但现在不过查询几次，用EXPLAIN查看都和之前不一样了...
我也不确定是不是同一条查询语句(刚才测试很多条查询语句，虽然都有记录在TXT里，但现在不过EXPLAIN哪条查询语句结果都和之前不一样了...)

Answer 6

实在想不出我之前到底是怎么写的，为什么之前执行顺序可以是g、b、s、c(表)
但后来再用我认为是同样的语句EXPLAIN后却是c、s、b、g...
按照g、b、s、c的思路，弄了个内查询SELECT SQL_NO_CACHE `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM (SELECT `g`.`id`,`g`.`date` FROM `goods_in` AS `g`,`goods_barcode` AS `b` WHERE `g`.`id` = `b`.`in_id` AND `g`.`id` <> 0 GROUP BY `g`.`id` ORDER BY `id` DESC LIMIT 0, 100) AS `g`,`goods_barcode` AS `b`, `specification` AS `s`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`cid` = `c`.`id`
GROUP BY `g`.`id`
ORDER BY `g`.`id` DESC;+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref         | rows | Extra          |
+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL        |  100 | Using filesort |
|  1 | PRIMARY     | b          | ref    | in_id,sid     | in_id   | 4       | g.id        |  140 |                |
|  1 | PRIMARY     | s          | eq_ref | PRIMARY,cid   | PRIMARY | 4       | testA.b.sid |    1 |                |
|  1 | PRIMARY     | c          | eq_ref | PRIMARY       | PRIMARY | 4       | testA.s.cid |    1 |                |
|  2 | DERIVED     | g          | range  | PRIMARY       | PRIMARY | 4       | NULL        |  831 | Using where    |
|  2 | DERIVED     | b          | ref    | in_id         | in_id   | 4       | testA.g.id  |  140 | Using index    |
+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+查询速度为0.11~0.13秒...比之前快...
总算圆满了...
不过profile的时候，发现大部分时间都耗费在sending data上+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000123 |
| Opening tables     | 0.000020 |
| System lock        | 0.000003 |
| Table lock         | 0.000087 |
| optimizing         | 0.000009 |
| statistics         | 0.000104 |
| preparing          | 0.000034 |
| executing          | 0.000004 |
| Sorting result     | 0.000001 |
| Sending data       | 0.014002 |
| init               | 0.000033 |
| optimizing         | 0.000009 |
| statistics         | 0.000030 |
| preparing          | 0.000010 |
| executing          | 0.000070 |
| Sorting result     | 0.000024 |
| Sending data       | 0.108234 |
| end                | 0.000004 |
| removing tmp table | 0.000008 |
| end                | 0.000016 |
| query end          | 0.000003 |
| freeing items      | 0.000074 |
| removing tmp table | 0.000004 |
| closing tables     | 0.000004 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000013 |
+--------------------+----------+

Answer 7

 字段少，显然一行记录实际的尺寸就小。读一次磁盘就会多读一些记录。 这样显然会提高访问速度。你可以假设create table xx1 (id int);create table xx2 (id int,col varchar(1000),col2 varchar(1000),col3 varchar(1000));同样的 select id form xx1; select id from xx2; 所需要的磁盘读取次数是不同的。