最好使用*当调用mysql中的很多字段？ [英] Best to use * when calling a lot of fields in mysql?

问题描述

我知道一般来说，总是更好的性能，建立mysql查询命名您需要的每一个项目，例如在配置文件页面上，我可能需要除了几个项目之外的每个项目。

  SELECT USER_NAME，f_name，l_name，国家，usa_state，other_state，ZIP_CODE，城市，性别，birth_date，DATE_CREATED，date_last_visit，
 USER_ROLE，PHOTO_URL，user_status，FRIEND_COUNT，COMMENT_COUNT，forum_post_count，referral_count，referral_count_total，
 setting_public_profile，setting_online，profile_purpose，profile_height，profile_body_type，profile_ethnicity，profile_occupation，profile_marital_status，profile_sex_orientation，profile_home_town，profile_religion，
 profile_smoker ，profile_drinker，profile_kids，profile_education，profile_income，profile_headline，profile_about_me，
 profile_like_to_meet，profile_interest，profile_music，profile_television，profile_books，profile_heroes，profile_here_for，profile_counter用户的WHERE user_ID的= 1 AND USER_ROLE> 
  

所以没有做一堆测试，也许有更多经验的人可以用一些建议？ / p>

这会加重病情。

  SELECT * FROM用户WHERE user_ID的= 1 AND user_role> 
  

我更喜欢列出所有项目，因为在那个页面上，只是容易看到我有什么对我来说，如果我需要从数据库的东西，但如果它会更快然后我就不再一一列举了。

解决方案

注意：命名所有字段当然是最佳做法，但在本文中，我将仅讨论性能优势，而不是设计或维护。

---

由于以下原因， * 语法可能会更慢：

• 并非所有字段都已建立索引，并且查询使用全表扫描。也许不是你的情况：这几乎是不可能的，你返回所有领域都与一个索引收录




• 从包含可变长度列的表返回尾随字段可以。导致一个小的搜索开销：返回第20个字段，以前的 19 应该被检查和偏移计算。

$

只有更多资料需要传回b $ b

由于你需要几乎所有的字段，最后的原因可能是最重要的。说， description TEXT 字段只能是 1 的 50 字段未在页面上使用，但可以占据与所有其他字段在一起的 10 倍。

在这种情况下，当然最好是命名所有字段，省略不需要的长字段。

I know generally it is always better performance to build mysql queries to name every item you need but for example on a profile page I might need every item except a few.

    SELECT user_name,f_name,l_name,country,usa_state,other_state,zip_code,city,gender,birth_date,date_created,date_last_visit, 
user_role,photo_url,user_status,friend_count,comment_count,forum_post_count,referral_count,referral_count_total, 
setting_public_profile,setting_online,profile_purpose,profile_height,profile_body_type,profile_ethnicity, profile_occupation,profile_marital_status,profile_sex_orientation,profile_home_town,profile_religion, 
profile_smoker,profile_drinker,profile_kids,profile_education,profile_income,profile_headline,profile_about_me, 
profile_like_to_meet,profile_interest,profile_music,profile_television,profile_books,profile_heroes,profile_here_for,profile_counter FROM users WHERE user_id=1 AND user_role >

So without doing a bunch of test, maybe someone with more experience can chime in with some advice?

Would this be worse

SELECT * FROM users WHERE user_id=1 AND user_role >

I prefer to list all items because then on that page it is just easiar to see what I have available to me if I need something from the DB but if it would be faster then I would not list them

解决方案

Note: naming all fields is of course a best practice, but in this post I will discuss only performance benefits, not design or maintenance ones.

---

The * syntax can be slower for the following reasons:

• Not all fields are indexed and the query uses full table scan. Probably not your case: it's hardly possible that all fields you return are indexed with a single index.

• Returning trailing fields from a table that contains variable length columns can result in a slight searching overhead: to return 20th field, previous 19 should be examined and offsets calculated.

• Just more data need to be returned (passed over the connection).

Since you need almost all fields, the last reason is probably the most important one. Say, the description TEXT field can be only 1 of 50 fields not used on the page, but can occupy 10 times as much space as all other fields together.

In this case it will be of course better to name all fields and omit the long fields you don't need.

这篇关于最好使用*当调用mysql中的很多字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！