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

2010-10-27 12:56:34 970 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

命中索引一定能提高查询速度吗？答案是否定的，在实际项目中我曾踩过这个坑。在进行性能优化时，我发现一个接口的 SQL 语句没有加索引，EXPLAIN执行后发现是全表扫描。我对查询的字段添加了索引后，性能却没有明显提升。这是为什么呢？本文将探讨结合项目优化实例、索引的工作原理、影响查询性能的因素，以及在什么情况下索引可能不会带来预期的性能提升。阅读详情

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函数的情况下，速度差不多

...全文

MySQL 的索引数量是否越多越好虽然索引可以提高查询效率，但过多的索引也会带来额外的开销，影响数据库的性能。阅读详情

ma2jiajia

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

ACMAIN_CHM 2010-10-27

打赏
举报

字段少，显然一行记录实际的尺寸就小。读一次磁盘就会多读一些记录。这样显然会提高访问速度。

你可以假设

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; 所需要的磁盘读取次数是不同的。

ma2jiajia 2010-10-27

打赏
举报

实在想不出我之前到底是怎么写的，为什么之前执行顺序可以是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 |

+--------------------+----------+

ma2jiajia 2010-10-27

打赏
举报

[Quote=引用 9 楼 zuoxingyu 的回复:]
同一个命令第二次后的查询，就直接从缓存中取了，都不需要解析啥的一套。直接取结果显示。

SELECT SQL_NO_CACHE id, name FROM customer;

要查看执行速度，用SQL_NO_CACHE 来修饰。
[/Quote]
但现在不过查询几次，用EXPLAIN查看都和之前不一样了...
我也不确定是不是同一条查询语句(刚才测试很多条查询语句，虽然都有记录在TXT里，但现在不过EXPLAIN哪条查询语句结果都和之前不一样了...)

zuoxingyu 2010-10-27

打赏
举报

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

zuoxingyu 2010-10-27

打赏
举报

同一个命令第二次后的查询，就直接从缓存中取了，都不需要解析啥的一套。直接取结果显示。

SELECT SQL_NO_CACHE id, name FROM customer;

要查看执行速度，用SQL_NO_CACHE 来修饰。

ma2jiajia 2010-10-27

打赏
举报

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

ma2jiajia 2010-10-27

打赏
举报

[Quote=引用 6 楼 sql_lover 的回复:]
字段个数影响不会太大呵呵
[/Quote]
嗯...再次测试发现和约束也没关系
在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秒...

sql_lover 2010-10-27

打赏
举报

字段个数影响不会太大呵呵

ma2jiajia 2010-10-27

打赏
举报

[Quote=引用 1 楼 zuoxingyu 的回复:]
字段多少没有关系，应该是约束影响了速度。
[/Quote]
貌似真的是无关的约束影响了速度哦...
我把数据库A中，这几张表未涉及到这条查询的约束都删除掉
再查询，发现速度基本上一致了

ma2jiajia 2010-10-27

打赏
举报

[Quote=引用 1 楼 zuoxingyu 的回复:]
字段多少没有关系，应该是约束影响了速度。
[/Quote]
是说数据库A中，和这条查询无关的字段的约束？？

rucypli 2010-10-27

打赏
举报

会部分影响

zuoxingyu 2010-10-27

打赏
举报

字段多少没有关系，应该是约束影响了速度。

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

相关推荐