Mysql应用MySQL性能优化的最佳20+条经验
|
《Mysql应用MySQL性能优化的最佳20+条经验》要点： 今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用.MYSQL应用 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的函数,从而 开启缓存.MYSQL应用 2. EXPLAIN 你的 SELECT 查询MYSQL应用 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的.这可以帮你分析你的查询语句或是表结构的性能瓶颈. 挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面..然后,你会看到一张表格.下面的这个示例中,我们忘记加上了group_id索引,并且有表联接：MYSQL应用
当我们为 group_id 字段加上索引后：MYSQL应用
我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16 行.查看rows列可以让我们找到潜在的性能问题.MYSQL应用 3. 当只要一行数据时使用 LIMIT 1MYSQL应用 当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数.
// 没有效率的：
$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. 为搜索字段建索引MYSQL应用 索引并不一定就是给主键或是唯一的字段.如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧.MYSQL应用
从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”,一个是建了索引,一个是没有索引,性能差了4倍左右. 5. 在Join表的时候使用相当类型的例,并将其索引MYSQL应用 如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的.这样,MySQL内部会启动为你优化Join的SQL语句的机制.
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集.
6. 千万不要 ORDER BY RAND() 想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用.但你确不了解这样做有多么可怕的性能问题.
// 千万不要这样做：
$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 *MYSQL应用 从数据库里读出越多的数据,那么查询就会变得越慢.并且,如果你的数据库服务器和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. 永远为每张表设置一个IDMYSQL应用 我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志.MYSQL应用 就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键.使用 VARCHAR 类型来当主键会使用得性能下降.另外,在你的程序中,你应该使用表的ID来构造你的数据结构.MYSQL应用 而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区…… 9. 使用 ENUM 而不是 VARCHARMYSQL应用 ENUM 类型是非常快和紧凑的.在实际上,其保存的是 TINYINT,但其外表上显示为字符串.这样一来,用这个字段来做一些选项列表变得相当的完美. 10. 从 PROCEDURE ANALYSE() 取得建议MYSQL应用 PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议.只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的.
一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确.一定要记住,你才是最终做决定的人.MYSQL应用 11. 尽可能的使用 NOT NULLMYSQL应用 除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL.这看起来好像有点争议,请往下看. 12. Prepared StatementsMYSQL应用 Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是平安问题.
// 创建 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 %sn",$username,$state);
$stmt->close();
}
13. 无缓冲的查询 上面那句话翻译过来是说,mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果.这会相当节约很多可观的内存,尤其是那些会产生大 量结果的查询语句,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了.MYSQL应用 然而,这会有一些限制.因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果.而且,mysql_num_rows() 或 mysql_data_seek() 将无法使用.所以,是否使用无缓冲的查询你需要仔细考虑.MYSQL应用 14. 把IP地址存成 UNSIGNED INT 15. 固定长度的表会更快MYSQL应用 如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”. 例如,表中没有如下类型的字段： VARCHAR,TEXT,BLOB.只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,MySQL 引擎会用另一种办法来处理.MYSQL应用 固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快.而如果字段不是定长的,每一次要找下一条的话,需要程序找到主键.MYSQL应用 并且,固定长度的表也更容易被缓存和重建.不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间.MYSQL应用 使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的.MYSQL应用 16. 垂直分割MYSQL应用 “垂直分割”是一种把数据库中的表按列变成几张表的办法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的.(以前,在银行做过项目,见过一张表有100多个字段,很恐怖) 另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降.MYSQL应用 17. 拆分大的 DELETE 或 INSERT 语句MYSQL应用 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应.因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了.
while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了,退出!
break;
}
// 每次都要休息一会儿
usleep(50000);
}
18. 越小的列会越快MYSQL应用 对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈.所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问. 当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据.MYSQL应用 19. 选择正确的存储引擎MYSQL应用 在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊.酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情. target=”_blank”MyISAM Storage Engine 20. 使用一个对象关系映射器(Object Relational Mapper)MYSQL应用 使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨.一个ORM可以做的所有事情,也能被手动的编写出来.但是,这需要一个高级专家. 21. 小心“永久链接”MYSQL应用 “永久链接”的目的是用来减少重新创建MySQL链接的次数.当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了.而且,自 从我们的Apache开始重用它的子进程后――也就是说,下一次的HTTP哀求会重用Apache的子进程,并重用相同的 MySQL 链接. 以上就是本文的全部内容,希望对大家的学习有所赞助,也希望大家多多支持编程之家.MYSQL应用 《Mysql应用MySQL性能优化的最佳20+条经验》是否对您有启发，欢迎查看更多与《Mysql应用MySQL性能优化的最佳20+条经验》相关教程，学精学透。编程之家 52php.cn为您提供精彩教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
