mysql数据库管理的项⽬经验_MySQL数据库21条最佳性能优
化经验
今天，数据库的操作越来越成为整个应⽤的性能瓶颈了，这点对于Web应⽤尤其明显。关于数据库的性能，这并不只是DBA才需要担⼼的事，⽽这更是我们程序员需要去关注的事情。
当我们去设计数据库表结构，对操作数据库时(尤其是查表时的SQL语句)，我们都需要注意数据操作的性能。这⾥，我们不会讲过多的SQL 语句的优化，⽽只是针对MySQL这⼀Web应⽤最多的数据库。希望下⾯的这些优化技巧对你有⽤。
1. 为查询缓存优化你的查询
⼤多数的MySQL服务器都开启了查询缓存。这是提⾼性最有效的⽅法之⼀，⽽且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执⾏了多次的时候，这些查询结果会被放到⼀个缓存中，这样，后续的相同的查询就不⽤操作表⽽直接访问缓存结果了。
这⾥最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使⽤缓存。请看下⾯的⽰例：
代码如下:
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
上⾯两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作⽤。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是⽤⼀个变量来代替MySQL的函数，从⽽开启缓存。
2. EXPLAIN 你的 SELECT 查询
使⽤ EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利⽤的，你的数据表是如何被搜索和排序的……等等，等等。
挑⼀个你的SELECT语句(推荐挑选那个最复杂的，有多表联接的)，把关键字EXPLAIN加到前⾯。你可以使⽤phpmyadmin来做这个事。然后，你会看到⼀张表格。下⾯的这个⽰例中，我们忘记加上了group_id索引，并且有表联接：
当我们为 group_id 字段加上索引后：
我们可以看到，前⼀个结果显⽰搜索了 7883 ⾏，⽽后⼀个只是搜索了两个表的 9 和 16 ⾏。查看rows列可以让我们到潜在的性能问题。
3. 当只要⼀⾏数据时使⽤ LIMIT 1
当你查询表的有些时候，你已经知道结果只会有⼀条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样⼀样，MySQL数据库引擎会在到⼀条数据后停⽌搜索，⽽不是继续往后查少下⼀条符合记录的数据。
下⾯的⽰例，只是为了⼀下是否有“中国”的⽤户，很明显，后⾯的会⽐前⾯的更有效率。(请注意，第⼀条中是Select *，第⼆条是Select 1)
代码如下:
// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
4. 为搜索字段建索引
索引并不⼀定就是给主键或是唯⼀的字段。如果在你的表中，有某个字段你总要会经常⽤来做搜索，那么，请为其建⽴索引吧。
从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”，⼀个是建了索引，⼀个是没有索引，性能差了4倍左右。
另外，你应该也需要知道什么样的搜索是不能使⽤正常的索引的。例如，当你需要在⼀篇⼤的⽂章中搜索⼀个词时，如： “WHERE
post_content LIKE ‘%apple%'”，索引可能是没有意义的。你可能需要使⽤MySQL全⽂索引 或是⾃⼰做⼀个索引(⽐如说：搜索关键词或是Tag什么的)
5. 在Join表的时候使⽤相当类型的例，并将其索引
如果你的应⽤程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
⽽且，这些被⽤来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和⼀个 INT 字段Join在⼀起，MySQL就⽆法使⽤它们的索引。对于那些STRING类型，还需要有相同的字符集才⾏。(两个表的字符集有可能不⼀样)
代码如下:
// 在state中查company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
mysql下载add produce// 两个 state 字段应该是被建过索引的，⽽且应该是相当的类型，相同的字符集。
6. 千万不要 ORDER BY RAND()
想打乱返回的数据⾏？随机挑⼀个数据？真不知道谁发明了这种⽤法，但很多新⼿很喜欢这样⽤。但你确不了解这样做有多么可怕的性能问题。
如果你真的想把返回的数据⾏打乱了，你有N种⽅法可以达到这个⽬的。这样使⽤只让你的数据库的性能呈指数级的下降。这⾥的问题是：MySQL会不得不去执⾏RAND()函数(很耗CPU时间)，⽽且这是为了每⼀⾏记录去记⾏，然后再对其排序。就算是你⽤了Limit 1也⽆济于事(因为要排序)
下⾯的⽰例是随机挑⼀条记录
代码如下:
// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$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");
7. 避免 SELECT *
从数据库⾥读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独⽴的服务器的话，这还会增加⽹络传输的负载。
所以，你应该养成⼀个需要什么就取什么的好的习惯。
代码如下:
// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
8. 永远为每张表设置⼀个ID
我们应该为数据库⾥的每张表都设置⼀个ID做为其主键，⽽且最好的是⼀个INT型的(推荐使⽤UNSIGNED)，并设置上⾃动增加的
AUTO_INCREMENT标志。
就算是你 users 表有⼀个主键叫 “email”的字段，你也别让它成为主键。使⽤ VARCHAR 类型来当主键会使⽤得性能下降。另外，在你的程序中，你应该使⽤表的ID来构造你的数据结构。
⽽且，在MySQL数据引擎下，还有⼀些操作需要使⽤主键，在这些情况下，主键的性能和设置变得⾮常重要，⽐如，集，分区……
在这⾥，只有⼀个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若⼲个别的表的主键构成。我们把这个情况叫做“外键”。⽐如：有⼀个“学⽣表”有学⽣的ID，有⼀个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学⽣表和课程表，在成绩表中，学⽣ID和课程ID叫“外键”其共同组成主键。
9. 使⽤ ENUM ⽽不是 VARCHAR
ENUM 类型是⾮常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显⽰为字符串。这样⼀来，⽤这个字段来做⼀些选项列表变得相当的完美。
如果你有⼀个字段，⽐如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限⽽且固定的，那么，你应该使⽤ ENUM ⽽不是 VARCHAR。
MySQL也有⼀个“建议”(见第⼗条)告诉你怎么去重新组织你的表结构。当你有⼀个 VARCHAR 字段时，这个建议会告诉你把其改成ENUM 类型。使⽤ PROCEDURE ANALYSE() 你可以得到相关的建议。
10. 从 PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你⼀些有⽤的建议。只有表中有实际的数据，这些建议才会变得有⽤，因为要做⼀些⼤的决定是需要有数据作为基础的。
例如，如果你创建了⼀个 INT 字段作为你的主键，然⽽并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使⽤了⼀个 VARCHAR 字段，因为数据不多，你可能会得到⼀个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。
在phpmyadmin⾥，你可以在查看表时，点击 “Propose table structure” 来查看这些建议
⼀定要注意，这些只是建议，只有当你的表⾥的数据越来越多时，这些建议才会变得准确。⼀定要记住，你才是最终做决定的⼈。
11. 尽可能的使⽤ NOT NULL
除⾮你有⼀个很特别的原因去使⽤ NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。
⾸先，问问你⾃⼰“Empty”和“NULL”有多⼤的区别(如果是INT，那就是0和NULL)？如果你觉得它们之间没有什么区别，那么你就不要使⽤NULL。(你知道吗？在 Oracle ⾥，NULL 和 Empty 的字符串是⼀样的！)
不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进⾏⽐较的时候，你的程序会更复杂。 当然，这⾥并不是说你就不能使⽤NULL了，现实情况是很复杂的，依然会有些情况下，你需要使⽤NULL值。
下⾯摘⾃MySQL⾃⼰的⽂档：
“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 Statements很像存储过程，是⼀种运⾏在后台的SQL语句集合，我们可以从使⽤ prepared statements 获得很多好处，⽆论是性能问题还是安全问题。
Prepared Statements 可以检查⼀些你绑定好的变量，这样可以保护你的程序不会受到“SQL注⼊式”攻击。当然，你也可以⼿动地检查你的这些变量，然⽽，⼿动的检查容易出问题，⽽且很经常会被程序员忘了。当我们使⽤⼀些framework或是ORM的时候，这样的问题会好⼀些。
在性能⽅⾯，当⼀个相同的查询被使⽤多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义⼀些参数，⽽MySQL只会解析⼀次。
虽然最新版本的MySQL在传输Prepared Statements是使⽤⼆进制形势，所以这会使得⽹络传输⾮常有效率。
当然，也有⼀些情况下，我们需要避免使⽤Prepared Statements，因为其不⽀持查询缓存。但据说版本5.1后⽀持了。
在PHP中要使⽤prepared statements，你可以查看其使⽤⼿册：mysqli 扩展 或是使⽤数据库抽象层，如： PDO.
代码如下:
// 创建 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// 绑定参数
$stmt->bind_param("s", $state);
// 执⾏
$stmt->execute();
// 绑定结果
$stmt->bind_result($username);
// 移动游标
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
13. ⽆缓冲的查询
正常的情况下，当你在当你在你的脚本中执⾏⼀个SQL语句的时候，你的程序会停在那⾥直到没这个SQL语句返回，然后你的程序再往下继续执⾏。你可以使⽤⽆缓冲查询来改变这个⾏为。
关于这个事情，在PHP的⽂档中有⼀个⾮常不错的说明： 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.”
上⾯那句话翻译过来是说，mysql_unbuffered_query() 发送⼀个SQL语句到MySQL⽽并不像mysql_qu
ery()⼀样去⾃动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产⽣⼤量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第⼀⾏数据返回的时候，你就可以开始马上开始⼯作于查询结果了。
然⽽，这会有⼀些限制。因为你要么把所有⾏都读⾛，或是你要在进⾏下⼀次的查询前调⽤ mysql_free_result() 清除结果。⽽且，
mysql_num_rows() 或 mysql_data_seek() 将⽆法使⽤。所以，是否使⽤⽆缓冲的查询你需要仔细考虑。
14. 把IP地址存成 UNSIGNED INT
很多程序员都会创建⼀个 VARCHAR(15) 字段来存放字符串形式的IP⽽不是整形的IP。如果你⽤整形来存放，只需要4个字节，并且你可以有定长的字段。⽽且，这会为你带来查询上的优势，尤其是当你需要使⽤这样的WHERE条件：IP between ip1 and ip2。
我们必需要使⽤UNSIGNED INT，因为 IP地址会使⽤整个32位的⽆符号整形。
⽽你的查询，你可以使⽤ INET_ATON() 来把⼀个字符串IP转成⼀个整形，并使⽤ INET_NTOA() 把⼀个整形转成⼀个字符串IP。在PHP 中，也有这样的函数 ip2long() 和 long2ip()。
1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
15. 固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中⼀个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会⽤另⼀种⽅法来处理。
固定长度的表会提⾼性能，因为MySQL搜寻得会更快⼀些，因为这些固定的长度是很容易计算下⼀个数据的偏移量的，所以读取的⾃然也会很快。⽽如果字段不是定长的，那么，每⼀次要下⼀条的话，需要程序到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯⼀的副作⽤是，固定长度的字段会浪费⼀些空间，因为定长的字段⽆论你⽤不⽤，他都是要分配那么多的空间。
使⽤“垂直分割”技术(见下⼀条)，你可以分割你的表成为两个⼀个是定长的，⼀个则是不定长的。
16. 垂直分割
“垂直分割”是⼀种把数据库中的表按列变成⼏张表的⽅法，这样可以降低表的复杂度和字段的数⽬，从⽽达到优化的⽬的。(以前，在银⾏做过项⽬，见过⼀张表有100多个字段，很恐怖)
⽰例⼀：在Users表中有⼀个字段是家庭地址，这个字段是可选字段，相⽐起，⽽且你在数据库操作的时候除了个⼈信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外⼀张表中呢？ 这样会让你的表有更好的性能，⼤家想想是不是，⼤量的时候，我对于⽤户表来说，只有⽤户ID，⽤户名，⼝令，⽤户⾓⾊等会被经常使⽤。⼩⼀点的表总是会有好的性能。
⽰例⼆： 你有⼀个叫 “last_login” 的字段，它会在每次⽤户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另⼀个表中，这样就不会影响你对⽤户 ID，⽤户名，⽤户⾓⾊的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会⽐不分割时还要差，⽽且，会是极数级的下降。
17. 拆分⼤的 DELETE 或 INSERT 语句
如果你需要在⼀个在线的⽹站上去执⾏⼀个⼤的 DELETE 或 INSERT 查询，你需要⾮常⼩⼼，要避免你的操作让你的整个⽹站停⽌相应。因为这两个操作是会锁表的，表⼀锁住了，别的操作都进不来了。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论