Top 20+ MySQL Best Practices(20条MySQL最佳实践)

由于作者翻译会加入 自己的理解 以便自己学习和使用, 如果英文好的同学可看下面   如文章中有翻译错误还请留言. 交流并改正. (:  原文地址   <== 

 

======================Enein翻译=========================

 

        今天大多数web应用的数据库操作往往是最主要的battleneck(瓶颈)， 这不仅仅是DBA所要担心的问题, 我们作为程序员也要做好我们的那部分(结构设计， 性能最好的查询/最优化的代码), 下面会为程序员介绍一些MySQL中的优化技术.

    Optimize Your Queries For the Query Cache  

        大多数MySQL服务的query caching（查询缓存）是开启的,  它是提高性能的最好办法之一, 由database engine(数据库引擎内部处理). 当相同的查询执行多次,  这个结果将会从 cache 中获取，这样是高效的.

        但主要的问题是, 对于程序员来说它是太容易了和是隐式的. 大多数时候我们都会忽略它.  只有我们在执行下面的语句的时候才会真正的关闭 query cache.

    

// query cache does NOT work  
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");  
// query cache works!  
$today = date("Y-m-d");  
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

 query cache 不工作的原因是 这个 第1行使用了 CURDATE(） 函数. (同样适用于 不确定函数  NOW() RAND() etc.)  因为返回的结果都是不同的. 所以MySQL 决定关闭 query cache 功能,  我们要做的就在(在PHP中) 在query 关闭之前做一些事情.

EXPLAIN Your SELECT Queries   

        使用EXPLAIN①关键字. 能告诉你MySQL在你语句执行的时候都做了什么. 这能帮助你找到查询和表结构的瓶颈以及一些问题.

EXPLAIN的结果可以看出这个query使用没使用indexs（索引）表是如何被搜索和排序 etc.

        写一个SELECT语句(最好是复杂点, 有join的) 在之前加入EXPLAIN, 你可以在phpmyadmin下看它, 它将会为你展示个友好的表格, 例如:  我们忘记加入一个字段索引, 执行如下:

加入索引之后（之样是比较好的）

 

现在不是扫描 7883行, 而是只扫描 9 行和 第16行 从两个表中. 经验告诉我们 看"rows" 下所有的数字, 就可以得出 查询性能与查询的结果数是成正比的结论.

LIMIT 1 When Getting a Unique Row 

        当你查询你的表时, 你知道你找的是一行. 你可能会得到一个结果, 或者你只是通过WHERE条件查询记录的数量. 在这种情况下加入 "LIMIT 1" . 这样数据库引擎就会找到1条记录后就停止,  而不是查找整个表或索引.

// do I have any users from Alabama?  
// what NOT to do:  
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");  
if (mysql_num_rows($r) > 0) {  
    // ...  
}  
// much better:  
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");  
if (mysql_num_rows($r) > 0) {  
    // ...  
}  

Index the Search Fields 

索引不仅是 "主键索引"/"唯一索引", 如果在你的表中有个字段你是要搜索的, 那么你也可以设置它.

上图中.  设置索引也可以应用到 搜索语句 "last_name LIKE "a%""中.  当搜索这个字段的时候, MySQL就会利用这个字段的索引.

但你也要知道哪些搜索是不有用到索引的:

        "当搜索一个单词(e.g "WHERE post_content LIKE '%apple%'")"

你可以使用 mysql fulltext search ②或者构建你自己的索引解决方案.

Index and Use Same Column Types for Joins 

        如果你的查询中存在 “Join”查询, 那你要确定你join的两个字段在两个表中都存在索引. 这直接影响 MySQL内部"Join"优化. 另外这个字段是被"Join"了, 还需要都为相同类型, 例如: 如果你加入了一个 DECIMAL 字段, 和另一个表的 INT 字段进行关联, MySQL将不会使用最后一个字段的索引。

如果为字符串列, 甚至字符编码都要一致.

// looking for companies in my state  
$r = mysql_query("SELECT company_name FROM users 
    LEFT JOIN companies ON (users.state = companies.state) 
    WHERE users.id = $user_id");  
// both state columns should be indexed  
// and they both should be the same type and character encoding  
// or MySQL might do full table scans  

Do Not ORDER BY RAND()

PS: 这个我相信一般都没有这么干地.

        这个技巧看上去挺有意思, 大多数 新手(菜鸟)都会犯这种错误,  你可能还不知道在你的查询中使用它的后果.

如果你真的需要随即行, 有很多方法可以做它, 只需要多几行代码；这种你可以防止数据线性增长所带来的瓶颈.  在你对它进行排序MySQL会对表中的每一行都进行 RAND() 但你仅仅只操作一行.

// what NOT to do:  
$r = mysql_query("SELECT 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");  

所以不要在你的语句中直接使用MySQL的随机方法.

Avoid SELECT * 

在表中查询所有数据的速度是很慢的.  对磁盘操作所耗费的时间很长. 而且当数据服务和WEB服务是分开的. 你将会有一个很长的等待时间.

在搜索的时候只查询指定字段是很好的习惯.

// 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  

Almost Always Have an id Field  

        在每个表中都有一个id字段(是主键的, 自增长的, int 类型的最好是UNSIGNED③的因为这个不能为负数), 如果你的一个用户表只有一个唯一段 "username" 你也不要把它设置成主键, VARCHAR类型的主键是很慢的. 最好的方法就在加入一个id字段.

        还可以通过MySQL引擎内部进行处理, 使用内部的主键(这点很重要) 在很多复杂的数据库中都可以设置(e.g 集群, 分片etc...)

        但 "association tables"(关联表)除外, 在两个表中使用多对多关系 . 例如 "posts_tags" 表中有两个字段 "post_id" "tag_id"  分别为"posts""tags"的主键ID 那么这个表就可以有一个主键ID.

Use ENUM over VARCHAR 

        ENUM④类型字段是快速和简单的. 它们在内部被存储像"TINYINT" 他们可以显示为字段串类型.  这使得它们可以代替某些字段.

如果有一个字段仅仅有一些不用的值, 那么请使用 ENUM 代替 VARCHAR 例如 你有个字段 status 只存在  “active” “inactive” "pending" "expired" etc ...

     甚至还有一些优化建议for MySQL来建议你合理化你的表 当你有一个 VARCHAR字段, 它会建议你使用ENUM代替. 你可以使用 PROCEDURE ANALYSE() 详细看下一节.

Get Suggestions with PROCEDURE ANALYSE() ⑤

        PROCEDURE ANALYSE() 返回一个可优化字段信息 来减轻表大小. 通常在实际中是很有用的.

例如 如果你创建了一个INT类型的主键, 但你没有几行, 那么可能会建议你使用 MEDIUMINT 来代替. 或 你创建一个VARCHAR字段, 那么它会建议使用ENUM代替, 因为它仅仅是一些唯一因定的值.

        在phpmyamin 中你可以敲击 "Propose table structure" 

你要有你自己的想法 因为这只是些建议 。 如果你的表增长的很快, 数据很大. 那么这些建议可能就不适合你了. 这些仅仅是建议而已.

Use NOT NULL If You Can  

        除非你有一个特殊需求要将它设置为空, 要么就能不设置为NULL就不设置NULL.

首先, 问一下你自己 知道不知道  空字符串和null有什么区别  (或者是INT 0 VS. NULL) . 如果没有合理理由, 那你不需要设置为NULL(在Oracle中它会认为是相同的)

        NULL是要一定的空间的, 并且会为你比较语句带来复杂性, 如果可以尽量避免它们, 无论怎么样, 一些人会有一些特殊的理由来把它设置为空. 那样是不好的, MySQL doc文档上说 

        “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.”

Prepared Statements

    预声名公有很多好处, 比如可以提高性能/提高安全性 etc...

预声名变量 可以过滤一些 变量 为它们设置default值,  也可以很好的防止SQL注入攻击 你也可以手动过滤这些变量, 但一些方法会出现人为错误 , 这就是为什么使用 一些框架来避免一些问题.

        因为我们的焦点是在性能上, 所以我也会说一下这部分的好处, 例如我们要查询相同的语句很多次, 我们可以赋值给同一个声明中, 这样MySQL只会解析一次。

        而且现在最后版本的MySQL在传输 prepared statements 改为二进制形式, 这样即可以提高效率又可以减少网络延迟.

当有特殊原因时候要使用一个空的 prepared statemtns时候 将不会MySQL query cache cache到,  5.1后 也支持了.

可以看一下PHP中 你可以使用  mysqli extension 也可以 抽像出一个 PDO.

// create a prepared statement  
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {  
    // bind parameters  
    $stmt->bind_param("s", $state);  
    // execute  
    $stmt->execute();  
    // bind result variables  
    $stmt->bind_result($username);  
    // fetch value  
    $stmt->fetch();  
    printf("%s is from %s\n", $username, $state);  
    $stmt->close();  
}  

Unbuffered Queries 

        通常在脚本中执行一个查询, 它将会等待查询完成后你才可以继续. 你也可以改变这种情况 , 使 "Unbffered Queries" 下面是 PHP doc 的mysql_unbuffered_query() 介绍  

        “mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”

但它有一些限制, 你还可以在你执行下一个query的时候使用 mysql_free_result()  但不允许你在结果集上使用  mysql_num_rows() or mysql_data_seek()

Store IP Addresses as UNSIGNED INT

        一些程序员对IP 地址会设置为 VARCHAR(15) 它们没有意示到IP地址都是整数. 而这个int只占用4个bytes ，这样可以修复一下大小.

在你的查询中可以使用  INET_ATON() 把IP变为整形 , 也可以使用  INET_NTOA() 变量回来 .

        这儿可以使用PHP中的  ip2long() and long2ip().

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";  

Fixed-length(Static) Tables are Faster 

        当表中的每一列都是 固定长度, 那么这个表就称做  “static” or “fixed-length”. "VARCHAR, TEXT, BLOB" 这些不是固定长度, 如果你的列中包含其中之一, 这个表就不是 固定长度的表, 那MySQL引擎的处理方式也会不同.

        Fixed-length 的表可以提高性能, 因为MySQL引擎查找记录是很快的, 当它想找一个特殊的行, 它可以很快定位到它, 如果行大小不固定, 每次都需要做一次寻址 还要去查找主键索引.

        他也是很容易被 cache / 拆开后重建. 但他们会带来更多的空间. 例如: 你有 VARCHAR(20)/CHAR(20)， 这个空间会一直存在, 不管里有没有值.

Vertical Partitioning 

        垂直分区是以垂直方式来拆分你的表结构, 是一种优化的方式. 

    Example 1:  你可能会有一个用户表, 有一个家庭住址字段, 不被经常读的, 你可能选择split你的表 home address 放到一个特殊的表里. 这样你的主表将会缩减大小(表越小执行效率越快).

    Example 2:  在你的表中的一个"last_login"字段, 每次用户登录都会更新这个字段该表在 query cache上的数据将会被 清空 ， 你可以使这个字段分到另一个表中保持更新次数降到最低.

    注意分区后的表 你要确定保不会经常插入数据, 那样的话性能也会降低.

Split the Big DELETE or INSERT Queries 

        如果你要执行一个大的 删除 和 插入 查询在一个在线网站上,  你需要注意这个连接速度, 当这个query被执行, 页面会锁定, 这样会带来不好的影响.

Apache 会提供一些并行的线程、 因此它工作是高效的 很快会执行完成脚本, 所以服务没有太多的开/闭在一次中. 那样会消耗资源 特别是内存.

        你也可以使用LIMIT来做它:

Smaller Columns Are Faster 

        在数据库引擎中, 磁盘是最大的瓶颈. 所以在保持最小,最简单可能帮助我们提高性能. 减少磁盘开销.

MySQL 文档中有一个列表   Storage Requirements  有所有类型.

如果你的表很少的行并且主键还是 INT 你可以替换 MEDIUMINT SAMILINT 甚至你也可以使用TININT. 如果你不需要时间组件, 你也可以使用DATA替换 DATETIME.

        重点在于你如何合理化你的表结构. 你可能会喜欢Slashdot.

Choose the Right Storage Engine

        MySQL中有两个存储引擎"MyISAM"/"InnoDB" 它们都有各自的优点和缺点.

        MyISAM 对读取操作是很好的.  它的伸缩性不是很好当有很多写候, 甚至你更新一行的一个字段, 这个表是被锁定的, 其它线程也不能读直到查询完毕. MyISAM 在计算 SELECT COUNT(*) 是很快的.

        InnoDB 趋向于一些复杂的存储引擎在大部分小应用里是比MyISAM慢的.  但它支持基于行的锁定， 它还支持些更高级的功能 比如事务.

1. MyISAM Storage Engine
2. InnoDB Storage Engine

Use an Object Relational Mapper 

/*忽略*/

附录

①“When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order”(当你在select之前加上EXPLAIN, MySQ 从优化器中显示一些执行信息, 即, 它将如何处理语句, 表加入顺序等信息.) --- MySQL

② "Full-text indexes can be used only with MyISAM tables" (但 MySQL 5.1不支持ISAM) --- MySQL

③ unsigned   既为非负数，用此类型可以增加数据长度!

④ “An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.(是一个string 类型的 在表创建的时候一个可选值的集合)”-- MySQL

⑤ "ANALYSE() examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes.(为查询语句返回一个可优化字段信息 来减轻表大小)" --- MySQL

======================Enein翻译=========================

由于作者翻译会加入 自己的理解 以便自己学习和使用. 如有转载请注明出处谢谢.  如文章中有翻译错误或有更好的方案还请留言. 交流并改正. (: