Mysql 开发最佳实践
2014-11-15 14:28:51 来源：我爱运维网 评论：0 点击：
1.Optimize Your Queries For the Query Cache 使用query cache来优化查询Most MySQL servers have query caching enabled...
- ELECT username FROM user ORDER BY RAND() LIMIT 1");
- // much better:
- $r = mysql_query("SELECT count(*) FROM user");
- $d = mysql_fetch_row($r);
- $rand = mt_rand(0,$d[0] - 1);
- $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
So you pick a random number less than the number of results and use that as the offset in your LIMIT clause.
这样，你首先获取一个小于行数的随机数，然后根据这个便宜量使用limit得到结果。
7. Avoid SELECT *
避免SELECT *
The more data is read from the tables, the slower the query will become. It increases the time it takes for the disk operations. Also when the database server is separate from the web server, you will have longer network delays due to the data having to be transferred between the servers.
表中读出的数据越多，查询将越慢。磁盘操作的时间增加。而且当数据库和web服务器分开始，你的网络延迟将更严重，因为你需要传输的数据更多了。
It is a good habit to always specify which columns you need when you are doing your SELECT’s.
在做查询时指定需要查询的行事一个很好的习惯（其实这个更重要的原因是减少了程序的修改，试想你如果表中增加了一个字段，那么程序中可能有很多地方都受到影响。）
- // not preferred
- $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
- $d = mysql_fetch_assoc($r);
- echo "Welcome {$d['username']}";
- // better:
- $r = mysql_query("SELECT username FROM user WHERE user_id = 1");
- $d = mysql_fetch_assoc($r);
- echo "Welcome {$d['username']}";
- // the differences are more significant with bigger result sets
8. Almost Always Have an id Field
最好有id列
In every table have an id column that is the PRIMARY KEY, AUTO_INCREMENT and one of the flavors of INT. Also preferably UNSIGNED, since the value can not be negative.
Even if you have a users table that has a unique username field, do not make that your primary key. VARCHAR fields as primary keys are slower. And you will have a better structure in your code by referring to all users with their id’s internally.
在每一个表中拥有一个自增长的INT型主键。UNSIGNED也可以。这个数值不可为负值。即使你有一个用户表包含有唯一的用户名，也不要用这个做你的主键。VARCHAR字段作为主键会很慢。而且你的程序结构将更好，你可以使用这个id作为所有用户的参考。
There are also behind the scenes operations done by the MySQL engine itself, that uses the primary key field internally. Which become even more important, the more complicated the database setup is. (clusters, partitioning etc…).
很多日后的操作，MYSQL引擎也会用到主键。数据库越复杂，这个将越重要（集群，分区等。。）
One possible exception to the rule are the “association tables”, used for the many-to-many type of associations between 2 tables. For example a “posts_tags” table that contains 2 columns: post_id, tag_id, that is used for the relations between two tables named “post” and “tags”. These tables can have a PRIMARY key that contains both id fields.
一个可能的例外就是“关联表”，用来表示两个表之间的多对多关系。比如"posts_tags"表拥有两个字段: post_id, tag_id,被用来表示两个表 “post” and “tags”中间的关联。这些表可以拥有一个主键，它是由两个表的id段组合而成。
9. Use ENUM over VARCHAR
使用ENUM类型而不是VARCHAR
ENUM type columns are very fast and compact. Internally they are stored like TINYINT, yet they can contain and display string values. This makes them a perfect candidate for certain fields.
ENUM类型的字段非常的快捷和紧凑。在内部他是以类似于TINYINT的方式被存储，虽然他们可以包含，展示string。这个属性是的他们很适合于特定的字段。
If you have a field, which will contain only a few different kinds of values, use ENUM instead of VARCHAR. For example, it could be a column named “status”, and only contain values such as “active”, “inactive”, “pending”, “expired” etc…
如果你有一个字段，只包含有很有限的不同值，考虑用ENUM而不是VARCHAR.比如，一个叫做"status"的字段，只具有值比如"active","inactive","pending","expired"等。
There is even a way to get a “suggestion” from MySQL itself on how to restructure your table. When you do have a VARCHAR field, it can actually suggest you to change that column type to ENUM instead. This done using the PROCEDURE ANALYSE() call. Which brings us to:
MYSQL本身也能够表的结构上给你“建议”。当你有一个VARCHAR字段，他会建议你改成ENUM. 这个通过存储过程ANALYSE() 来实现。
10. Get Suggestions with PROCEDURE ANALYSE()
从存储过程ANALYZE()获取建议
PROCEDURE ANALYSE() will let MySQL analyze the columns structures and the actual data in your table to come up with certain suggestions for you. It is only useful if there is actual data in your tables because that plays a big role in the decision making.
过程ANALYZE()会让MYSQL分析字段的结构和真实数据，从而
For example, if you created an INT field for your primary key, however do not have too many rows, it might suggest you to use a MEDIUMINT instead. Or if you are using a VARCHAR field, you might get a suggestion to convert it to ENUM, if there are only few unique values.
比如，如果你建了一个INT型来作为主键，然而你并没有太多的行，他将会建议你使用MEDIUMINT。或者你使用了一个VARCHAR字段，你可能得到建议让你转化为ENUM，如果它只拥有很少的值。
You can also run this by clicking the “Propose table structure” link in phpmyadmin, in one of your table views.
你也可以通过phpmyadmin的任何一个table视图的"Propose table structure"来获取这个。
Keep in mind these are only suggestions. And if your table is going to grow bigger, they may not even be the right suggestions to follow. The decision is ultimately yours.
记住，这些只是建议。而且当表变得更大时，这个可能并不是很好的建议。这个由你来决定。
11. Use NOT NULL If You Can
尽可能使用NOT NULL
Unless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL.
除非你有特殊的理由要使用NULL, 你应当将你的字段设置成NOT NULL.
First of all, ask yourself if there is any difference between having an empty string value vs. a NULL value (for INT fields: 0 vs. NULL). If there is no reason to have both, you do not need a NULL field. (Did you know that Oracle considers NULL and empty string as being the same?)
首先，问问自己用空字符串或者NULL值是否有区别（比如对于INT字段：0和NULL的区别). 如果没有必要两者都用，就不要用NULL。
NULL columns require additional space and they can add complexity to your comparison statements. Just avoid them when you can. However, I understand some people might have very specific reasons to have NULL values, which is not always a bad thing.
NULL字段拥有额外的空间，而且他会增加你比较的复杂性。如果可能不要使用它。然而，我能够理解有些人获取认为他们一定要使用NULL类型，这也不赖。
From MySQL docs:
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
12. Prepared Statements
Prepared声明
There are multiple benefits to using prepared statements, both for performance and security reasons.
使用prepared声明具有很多好处，不管是性能还是安全原因。
Prepared Statements will filter the variables you bind to them by default, which is great for protecting your application against SQL injection attacks. You can of course filter your variables manually too, but those methods are more prone to human error and forgetfulness by the programmer. This is less of an issue when using some kind of framework or ORM.
Since our focus is on performance, I should also mention the benefits in that area. These benefits are more significant when the same query is being used multiple times in your application. You can assign different values to the same prepared statement, yet MySQL will only have to parse it once.
Prepared声明将会默认过滤掉你绑定给他的变量，这对于保护你的程序遭受SQL注入攻击非常有效。当然你也可以自己过滤变量，但是这些方法被证明会带来更多的人为错误。对于使用了一些框架或者ORM的情况，这个并不是很严重。从性能角度来看，prepared也能够带来益处。这个优点在相同的查询被执行很多次的时候尤为明显。你可以赋给同一个prepare声明不
相关热词搜索：Mysql
上一篇：阿里双十一核心数据库技术实践
下一篇：最后一页
分享到：
收藏
收藏
评论排行
- ·Hive查询OOM分析(14)
- ·复杂网络架构导致的诡异...(8)
- ·使用 OpenStack 实现云...(7)
- ·影响Java EE性能的十大问题(6)
- ·Mysql数据库复制延时分析(5)
- ·云计算平台管理的三大利...(5)
- ·OpenStack Nova开发与测...(4)
- ·LTPP一键安装包1.2 发布(4)
- ·Linux下系统或服务排障的...(4)
- ·PHP发布5.4.4 和 5.3.1...(4)
- ·RSYSLOG搭建集中日志管理服务(4)
- ·转换程序源码的编码格式[...(3)
- ·Linux 的木马程式 Wirenet 出现(3)
- ·Nginx 发布1.2.1稳定版...(3)
- ·zend framework文件读取漏洞分析(3)
- ·Percona Playback 0.3 development release(3)
- ·运维业务与CMDB集成关系一例(3)
- ·应该知道的Linux技巧(3)
- ·同时出现首选IP与复制IP...(2)
- ·服务器遭黑客入侵导致网...(2)
沪ICP备12031567号