【征询意见】两条SQL语句查询效率比较,那条更优

/*==============================================================*/
/* Table: classify   [分类表]                                   */
/*==============================================================*/
create table classify
(
   id                         varchar(25)   not null, --  编号
   name                       varchar(80), --  名称
   type                       char(1),     --  类型
   primary key (id)
)
type = InnoDB;/*==============================================================*/
/* Table: news  [新闻表]                                        */
/*==============================================================*/
create table  news
(
   id                           varchar(25)      not null, --  编号
    type                         varchar(25), --  分类，对应classify表ID
   title                        varchar(100), --  标题
   primary key (id)
)
type = InnoDB;数据库表没有设置外键，是用程序控制的！

explain出来orset profiling=1
show profiles
show profile for query 1;

效率无非指的是cpu消耗，内存消耗，io消耗，给我们最直观的感觉当然是执行时间看这两个语句哪个执行快，就代表在当前服务器状态下哪个效率高

索引能否修改
在classify上建立id、type联合索引
news上建立type索引

贴建表及插入记录的SQL
实际上你自己测试一下，看看执行时间就判断出来了

这个问题无法直接下结论，要看具体的情况。1. 索引的情况如何？如果没有任何type上的索引，则理论上两个差不多。
2. 每个表中的数据情况，每个表中的记录总数是多少？如果两个表中都只有10条记录，则没什么差别，所有记录都可以在内存中搞定。

没看懂第二个，n.id?? 一看还以为是C#还是Java的点操作

两条语句是一样的。你们公司没懂sql执行计划？SELECT n.* FROM news n,classify c WHERE c.id=n.type AND c.type='2'SELECT * FROM news WHERE TYPE IN (SELECT id FROM classify WHERE type='2')SELECT * FROM news n join classify c on n.TYPE=c.ID where type='2'SELECT * FROM news n inner join classify c on n.TYPE=c.ID where type='2'大部分不同sql 都会被sqlserver 自动找到最优执行计划。所以这几个都是一样的。

in 用 EXISTS 改写，也是一样。

好的数据库引擎会优化成一样的另外，type最好加上索引

我觉得两个语句都是一样的效率！
在SQL 2005中，第二条语句
SELECT n.* FROM news n,classify c WHERE c.id=n.type AND c.type='2'
表news有聚集索引但是查询中没有用到news.id因此对于表news做了一个聚集所以扫描运算,相反对classify表做了一个聚集索引查找运算，然后再进行嵌套循环
第一条查询也是一样的分析，所以效率都是一样的。

应该效率是一样的。
因为都从WHERE 开始遍历表。
两张表都遍历了一次

差不多吧第一个先执行子查询第二个先执行c.type='2'，再执行连接这样2个理论上就差不多了

关于这个问题其实以前就有一个类似的讨论。
http://topic.csdn.net/u/20090626/16/65f043cf-b9d9-4707-b660-9857461177f4.html

如果数据量很大的话，比如两个表都有超多行数据DBMS会选择最优方式去执行，不会有什么差别；如果数据量少的话内存中就解决了

如果是我会把第二条查询语句写成inner join的连接方式，不过DBMS优化过以后也应该是这样，所有大数据量时也许第二条语句更好

我的意见：第一种好，从底层来说，只要是select就是执行了笛卡尔积和除法操作，第一句层次分明易于理解，第二句sql执行了笛卡尔积和投影操作。

数据库会退sql进行一定的优化。想这种情况，两个的执行计划应该一样。

我是凭感觉说的，平时这方面看的不多。
SELECT * FROM news WHERE TYPE IN (SELECT id FROM classify WHERE type='2')
等同于
where id=a1 or id=a2 or id=a3......
先找出集合然后再一个一个比较SELECT n.* FROM news n,classify c WHERE c.id=n.type AND c.type='2'
这个貌似n和c进行个运算就出来了（线代忘干净了）

整理一下大家大观点：1，第一种方法优
   代表理由：（1）select执行笛卡尔积和投影操作会影响效率，第一句层次分明易于理解
             （2）《数据库系统概论第四版》提到过（未考证）
   代表者：64楼 luoweihua7
          65楼 gangqinjiawang2，第二种方法优
   代表理由：（1）数据量大时IN的效率不高
             （2）第二种用到了索引id
   代表者：47楼 zhan7505201
          58楼 afandasan
          15楼 ssp2009 等等3，两种方法一样
   代表理由：（1）SQL计划执行，会自动选择最优的SQL语句执行
             （2）表news有聚集索引但是查询中没有用到news.id因此对于表news做了一个聚集所以扫描运算,相反对classify表做了一个聚集索引查找运算，然后再进行嵌套循环
第一条查询也是一样的分析，所以效率都是一样的。   代表者：26楼 winner2050
          30楼 bala7229291
支持者数量统计：  第一种优：2人
  第二种优：9人
  两种一样：8人  对比：第二种 > 一样 > 第一种
          再次申明，两个表除了主键索引外再没有任何索引欢迎大家发表自己独到的观点，继续讨论

我觉得应该是第2条效率比较高。第一条：
SELECT * FROM news WHERE TYPE IN (SELECT id FROM classify WHERE type='2')
如果classify表随着时间增大，子查询遍历出来的结果会很多。再用主查询一一遍历子查询的结果。效率很低。第二条：
SELECT n.* FROM news n,classify c WHERE c.id=n.type AND c.type='2'
可以看到，where中将 c.id=n.type 做了联接。可以看成将2个表合并为一个大表做查询。
每条记录判定1次。效率明显要比前一个不停的用得到的条件遍历同一个表效率高。浅见。可能很多地方欠考虑

昨天看到的：
IN 的作用相当与OR
　　语句：
　　Select * from table1 where tid in (2,3)
　　和
　　Select * from table1 where tid=2 or tid=3
是一样的，都会引起全表扫描，如果tid上有索引，其索引也会失效。
是不是in的速度慢呢。
还有就是测试，每次测试时运行的时间不是固定的。我对于同一个查询进行反复运行，得出的时间都不固定，且与运行先后无关。差距可达到20%。

仔细分析一下：SELECT * FROM  news WHERE  TYPE IN (SELECT id FROM  classify WHERE type='2')查询计划1：
步骤1：首先SELECT id FROM  classify WHERE type='2'
      表有主键，即聚集索引，采用聚集索引扫描，然后把扫描出的type=2的所有  行，取出id
步骤2：然后用查询的id作为type谓词的值进行SELECT * FROM  news WHERE  TYPE =id
      表有主键，即聚集索引，采用聚集索引扫描，逐一进行type与id的匹配（连接）查询计划2：
步骤1：首先SELECT id FROM  classify WHERE type='2'
      放弃使用聚集索引扫描，而使用行扫描，匹配type=2的所有行，取出id
步骤2：然后用查询的id作为type谓词的值进行SELECT * FROM  news WHERE  TYPE =id
      放弃使用聚集索引扫描，而使用行扫描，逐一进行type与id的匹配（连接）查询计划3：
步骤1：首先SELECT * FROM  news 的查询
      表有主键，即聚集索引，采用聚集索引扫描，取出type
步骤2：SELECT id FROM  classify WHERE id=（n.type） and type='2'
      表有主键，谓词使用主键，采用聚集索引查找，然后连接
.................其实还有几种组合，至少我知道的因为扫描一般是比查找慢，聚集索引>索引>堆（行）：所以从效率上来说
聚集索引查找>索引查找>聚集索引扫描>索引扫描>行扫描（表扫描）查询计划3是最优的，有兴趣的可以分析一下第二个查询，得到最优的结果也是这里的查询计划3
所以两条语句在执行的时候是一样的效率。另外在优化查询计划的时候，语句一可能比语句而消耗略微多一点，但是跟查询时间比不是一个数量级的（因为基于优化的时间是相当昂贵的，如果时间过多，不如不做优化）所以总的来说两条的效率是一样的，非要比个高低，也许第二条略微有点优势。进一步的话，可以通过设置type为索引，查询的计划就变为：聚集索引查找+索引查找+连接这样就优化了

个人认为这两条语句是第二条执行的速度在数据量大的时候效率高
我还想到一种写法不知道效果如何
select n.* from (SELECT id FROM classify WHERE type='2') c left join news n on c.id=n.type

对于关系数据来说，写数据库本来就是瓶颈，我觉得，关系带来了数据的一致性，但是同时降低效率和并发性，我个人认为，如果针对数据量大，应该考虑分区（SQL Server中），系统会在每个分区都使用不同的索引，这样插入等操作的时候不会影响其他分区的索引，这样也能得到很大效率的提高

效率无非指的是cpu消耗，内存消耗，io消耗，给我们最直观的感觉当然是执行时间

支持，顶一下，B/S的话 MVC是不错的选择其实只要你抽象好了你的接口，你就不愁这些代码分不开了
比如用 .Net 命名空间来存放跟网络有关的的业务处理大体的有Send(),Receive(),Status()
用.UI 命名空间来存放界面显示的业务
访问网络的时候调用.Net 下面的那三个方法就可以搞定的

调试易

【征询意见】两条SQL语句查询效率比较,那条更优

解决方案 »