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聲明不