MySQL数据库十大优化本领[MySQL防范]

　　WEB开辟者不光要办理程序的效率问题,对数据库的快速拜候和呼应也是一个大问题.但愿本文能对大家掌握MySQL优化本拥有所帮忙.

　　工具/原料

　　MySQL 6.0下载:http://www.pc6.com/softview/SoftView_41452.html

　　步骤/办法

　　1. 优化你的MySQL查询缓存

　　在MySQL服务器上举行查询,可以启用高速查询缓存.让数据库引擎在后台暗暗的处理是提高性能的最有效办法之一.当同一个查询被履行多次时,假如后果是从缓存中提取,那是相当快的.

　　但主要的问题是,它是那么简单被躲藏起来以至于我们大大都程序员会忽视它.在有些处理任务中,我们实际上是可以禁止查询缓存工作的.

　　1. // query cache does NOT work

　　2. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

　　3.

　　4. // query cache works!

　　5. $today = date("Y-m-d");

　　6. $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

　　7.

　　8. // query cache does NOT work

　　9. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

　　10.

　　11. // query cache works!

　　12. $today = date("Y-m-d");

　　13. $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

　　2. 用EXPLAIN使你的SELECT查询越发清楚

　　利用EXPLAIN关键字是另一个MySQL优化本领,可以让你理解MySQL正在举行什么样的查询操作,这可以帮忙你发现瓶颈的所在,并显示出查询或表构造在那边出了问题.

　　EXPLAIN查询的后果,可以奉告你那些索引正在被引用,表是若何被扫描和排序的等等.

　　实现一个SELECT查询(最好是对比复杂的一个,带joins方法的),在里面增添上你的关键词注释,在这里我们可以利用phpMyAdmin,他会奉告你表中的后果.举例来说,假定当我在履行joins时,正忘掉往一个索引中增添列,EXPLAIN能帮忙我找到问题的所在.

　　3. 操纵LIMIT 1获得唯一行

　　有时,当你要查询一张表是,你知道自己只需求看一行.你大概会去的一条非常独特的记录,大概只是刚好查抄了任何存在的记录数,他们都满意了你的WHERE子句.

　　在这种情形下,增添一个LIMIT 1会令你的查询越发有效.这样数据库引擎发现只有1后将终止扫描,而不是去扫描整个表或索引.

　　1. // do I have any users from Alabama?

　　2. // what NOT to do:

　　3. $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");

　　4. if (mysql_num_rows($r) > 0) {

　　5. // ...

　　6. }

　　7. // much better:

　　8. $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");

　　9. if (mysql_num_rows($r) > 0) {

　　10. // ...

　　11. }

　　4. 索引中的检索字段

　　索引不但是主键或唯一键.假如你想搜索表中的任何列,你应当一向指向索引.

　　5. 保证衔接的索引是相同的范例

　　假如利用程序中包含多个衔接查询,你需求确保你链接的列在两边的表上都被索引.这会影响MySQL若何优化内部联接操作.

　　此外,加入的列,必须是同一范例.比方,你加入一个DECIMAL列,而同时加入另一个表中的int列,MySQL将无法利用此中至少一个指标.即便字符编码必须同为字符串范例.

　　1. // looking for companies in my state

　　2. $r = mysql_query("SELECT company_name FROM users

　　3. LEFT JOIN companies ON (users.state = companies.state)

　　4. WHERE users.id = $user_id");

　　5.

　　6. // both state columns should be indexed

　　7. // and they both should be the same type and character encoding

　　8. // or MySQL might do full table scans

　　6. 不要利用BY RAND()号令

　　这是一个令很多新手程序员会掉进去的陷阱.你大概不知不觉中制造了一个可怕的安静.这个陷阱在你是用BY RAND()号令时就开始成立了.

　　假如您真的需求随机显示你的后果,有很多更好的途径去实现.当然这需求写更多的代码,但是能避免性能瓶颈的呈现.问题在于,MySQL大概会为表中每一个独立的行履行BY RAND()号令(这会损耗处理器的处理本领),然后给你仅仅返回一行.

　　1. // what NOT to do:

　　2. $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

　　3. // much better:

　　4. $r = mysql_query("SELECT count(*) FROM user");

　　5. $d = mysql_fetch_row($r);

　　6. $rand = mt_rand(0,$d[0] - 1);

　　7.

　　8. $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

　　7. 尽大概避免SELECT *号令

　　从表中读取越多的数据,查询会变得更慢.他增添了磁盘需求操作的时间,还是在数据库服务器与WEB服务器是独立脱离的情形下.你将会阅历非常冗长的网络耽误,仅仅是因为数据不必要的在服务器之间传输.

　　始终指定你需求的列,这是一个非常杰出的习惯.

　　1. // not preferred

　　2. $r = mysql_query("SELECT * FROM user WHERE user_id = 1");

　　3. $d = mysql_fetch_assoc($r);

　　4. echo "Welcome {$d['username']}";

　　5. // better:

　　6. $r = mysql_query("SELECT username FROM user WHERE user_id = 1");

　　7. $d = mysql_fetch_assoc($r);

　　8. echo "Welcome {$d['username']}";

　　9. // the differences are more significant with bigger result sets

　　8. 从PROCEDURE ANALYSE()中得到倡议

　　PROCEDURE ANALYSE()可以让MySQL的柱构造解析和表中的实际数据来给你一些倡议.假如你的表中已经存在实际数据了,能为你的庞大抉择服务.

　　9. 预备好的语句

　　预备好的语句,可以从性能优化和安全两方面对大家有所帮忙.

　　预备好的语句在过滤已经绑定的变量默许情形下,能给利用程序以有效的保护,避免SQL注入攻击.当然你也可以手动过滤,不过由于大大都程序员忘掉的性格,很难到达效果.

　　1. // create a prepared statement

　　2. if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {

　　3. // bind parameters

　　4. $stmt->bind_param("s", $state);

　　5. // execute

　　6. $stmt->execute();

　　7. // bind result variables

　　8. $stmt->bind_result($username);

　　9. // fetch value

　　10. $stmt->fetch();

　　11. printf("%s is from %s\n", $username, $state);

　　12. $stmt->close();

　　13. }

　　10. 将IP地址存储为无标记整型

　　很多程序员在成立一个VARCHAR(15)时并没有意识到他们可以将IP地址以整数情势来存储.当你有一个INT范例时,你只占用4个字节的空间,这是一个固定大小的范畴.

　　你必须肯定你所操作的列是一个UNSIGNED INT范例的,因为IP地址将利用32位unsigned integer.

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

　　十大MySQL优化本领就介绍到这里.