Rails SQL 通配符真的很糟糕么

hooopo · 2012年09月22日 · 最后由 woaigithub 回复于 2012年10月04日 · 6309 次阅读
本帖已被管理员设置为精华贴

@bhuztez 同学在这个帖子里提到:

而那个ActiveRecord Query Interface,默认生成的SQL可是select *啊,开发的时候的确不碍事,但总不能就这样发布了,还得把所有用到的column名字都补回去,下次改到这里再注释掉。要不就不用ActiveRecord Query Interface。
现在,还有谁敢拿着里面还有select *的代码去发布啊,现在都找不到多少PHP代码里面到处都是select *了吧。

先说说优点:

一、打字少!看看下面对比,哪个简单就不用说了,更少的输入意味着更少的错误:

select * from bugs;

select bug_id, date_reported, summary, description, resolution, reported_by,assigned_to, verified_by, status, priority, hours,......, from bugs;

二、在添加和修改或删除的时候不需要更改查询语句。

上面这两点都满足了程序员“懒”的习惯。所以大家都非常喜欢使用。

下面谈谈缺点:

说到这里一定会有同学跳出说:这有性能问题,不能这么用! 的确,在在查询中使用通配符会影响性能,一次查询过多(有时候不需要的列也会被查到)会增加客户端和数据库之间的网络传输开销。

why AR's default SELECT * is not a big deal

一、性能问题永远不是应该最先考虑的问题。 很少应用的数据会达到百万、千万级别。在开发阶段代码简单灵活可扩展永远是第一位的。

二、考虑到两种特殊情况,一个表字段非常多或是有大字段(text/blob 类型)。这种情况最彻底的解决方案是垂直拆分表和剥离大字段。

比如 users 这样的表很大,将 users 表分成 accounts + user_blog_parts + user_ask_parts + user_topic_parts 等。

带有大字段的表 topics 分成 topics 和 topic_texts。

三、不使用通配符会带来更多的 Query Cache Miss 和额外的缓存存储开销。无论是数据库级别的 Query Cache 还是 ActiveRecord 级别的 Query Cache。原因很简单 Query Cache 使用 sql 语句作为缓存 key,会把 select *select colum1,column2 当作不同的查询。

mysql> select * from users order by id limit 1;

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16690672 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 6        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 70       |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 16       |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select id,nick_name,email from users order by id limit 1;
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16689648 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 7        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 70       |
| Qcache_queries_in_cache | 7        |
| Qcache_total_blocks     | 18       |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> 

四、和 Query Cache 类似,如果项目用了类似 CacheMoney 这样的行缓存插件,通配符方式会带来额外的性能提升,有些查询甚至不需要去 hit db,比指定列的方式省的那一点儿流量强的多。

五、有些查询使用select *不能进行覆盖索引优化。但是别忘了 AR 是可以使用 select 选项的... 默认 select * ,在特殊的地方手动指定查询列,这有什么问题呢?这就是 CoC 啊..

结论:

所以,单从一条查询来看某些做法可能是最好的,但是从整体的角度看未必是最优的。 有些同学干了很多脏活累活儿,看起来很勤劳勇敢的样子,但是也未必起到什么效果:-)

这个倒没啥,感觉 count 的默认应该改成 1 或 0 什么的

#1 楼 @jjym 是现有 migration 后有 model 的,如果是从 model 生成 migration 就可以根据 model 里设置的 counter_cache 选项来自动设置默认值了。

也可以一个字段以 count 结尾就给添加默认值?不过这样有点管的太多了吧。

#2 楼 @hooopo 我的意思是 count 方法应该默认生成select count(1) from xxx而不是 count(*)。毕竟不是所有的 model 都需要 counter_cache

#3 楼 @jjym count(1) 真的比 count(*) 快么?

#4 楼 @hooopo 据老码农说是这样的..因为不必检测所有字段,我自己是没试验过..周一去公司试下

#5 楼 @jjym

常见误区
count(1)和count(primary_key) 优于 count(*)
很多人为了统计记录条数,就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他们认为这样性能更好,其实这是一个误区。对于有些场景,这样做可能性能会更差,应为数据库对 count(*) 计数操作做了一些特别的优化。
count(column) 和 count(*) 是一样的
这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在,很多人都会认为这是理所当然的。实际上,count(column) 和 count(*) 是一个完全不一样的操作,所代表的意义也完全不一样。
count(column) 是表示结果集中有多少个column字段不为空的记录
count(*) 是表示整个结果集有多少条记录

ref:http://isky000.com/database/mysql-performance-tuning-sql 我记得《高性能 Mysql》上面也是这么说的。

#6 楼 @hooopo 靠,上课就记住这几条..还是错的。。 好文章,收藏

select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量 靠,这个误解真大了。。我相信 9.5 成人都会这样认为吧

不过加载到 Ruby 里的数据还是越少越好吧

这些 SQL 的优化技巧是否还跟数据库还有关系呢,比如 oracle 与 mysql 会不同之类的

只有算法复杂度是持久靠谱的,很多优化技巧升个版本就不成立了

#10 楼 @luikore 是啊 比如这篇里的例子在不同 Ruby 版本或实现上就会有差别:http://ruby-china.org/topics/5311

@hooopo 不是号称数据库学的很烂么。。。。 话说我的看法:优化 sql 应该从生产日志中入手,加上 cache 之后 很多看似效率低下的查询实际上并不低效,很多看似高效的查询因为环境因素反而很慢。。。 在 CSTO 实习最大的收获就是这个 PS:不过早优化算是老生常谈吧,用 orm 的时候避免类似 n+1 这种自身的局限导致的低效查询就好

#12 楼 @jasl n+1 也不见得就一定有问题,对象缓存比查询缓存好的场景还是很多的

@fsword 嗯 这就回到我那看法了 从上线环境的日志里找查询的瓶颈

#14 楼 @jasl 恩,我只是加强一下你的观点,事实上我认为唯一需要在早期考虑性能的地方是架构,不过架构上能支持到下次重写的版本也就够了

你把问题搞混了吧,你说的两条优点对于 ORM 来说是不成立的。

只有你自己写 SQL 语句的时候,才能体现出SELECT *打字少且在添加和修改或删除的时候不需要更改查询语句的优势。

为什么不要用SELECT *?就是为了能在执行 SQL 语句的时候就出错,而不是在模板或者其他什么地方出错。

#16 楼 @bhuztez ORM 就不需要指定需要哪些列?

#16 楼 @bhuztez SELECT * 会出什么错?自动生成了 SELECT name 可以防止你在模板打错 .name 么?需不需要给 Ruby 添加编译期检查?

#18 楼 @ReiSELECT *可能会导致两种不同的问题,抛了个相同的错。这当然是能避免就避免的了。

一种是你的代码假设数据库有某个字段,但数据库里没有 另外一种是,你没有假定有这个字段,但你模板里写了

SELECT *就导致两个问题,都在模板那里出错。

#17 楼 @hooopo ORM 的存在就是要帮你偷懒,而不是帮他自己偷懒。

jQuery 还要搞定不同浏览器之间的差别呢,照你这么说就留给你自己搞定好了。

@bhuztez 如果像你说的,SELECT * 存在两种问题的话, SELECT name 就存在六种问题:

  • 你的代码假设数据库有某个字段,但数据库里没有,你在 SELECT name 没写错
  • 你的代码假设数据库有某个字段,但数据库里没有,你在 SELECT name 写错了
  • 你的代码假设数据库有某个字段,数据库里也有,你在 SELECT name 写错了
  • 你没有假定有这个字段,但你模板里写了,你在 SELECT name 没写错
  • 你没有假定有这个字段,但你模板里写了,你在 SELECT name 写错了
  • 你没有假定有这个字段,你模板里也没写,你在 SELECT name 写错了

#22 楼 @luikore 我是说两个不同的问题抛相同的错...

#23 楼 @bhuztez 但 6 种不同的问题抛 3 种错呢...

经典之至

我这现在就这种情况 一个标 67 个字段 varchar2(1000) 的有三个,用的是 hibernate 的 hql“from A...”

#6 楼 @hooopo 对啊,这两个含义不同,两者实现的功能不一样,带上 columnname 的是返回该列不为空的总数,*返回的是表行的总数。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号