Mysql必读MySQL 的 20+ 条最佳实践

概述介绍《Mysql必读MySQL 的 20+ 条最佳实践》开发教程，希望对您有用。

《MysqL必读MysqL 的 20+ 条最佳实践》要点：
本文介绍了MysqL必读MysqL 的 20+ 条最佳实践，希望对您有用。如果有疑问，可以联系我们。

MysqL教程数据库 *** 作是当今 Web 应用程序中的主要瓶颈. 不仅是 DBA(数据库管理员)必要为各种性能问题 *** 心,程序员为做出准确的结构化表,优化查询性能和编写更优代码,也要费尽心思. 在本文中,我列出了一些针对程序员的 MysqL 优化技术.
在我们开始学习之前,我补充一点：你可以在 Envato Market 上找到大量的 MysqL 脚本和实用程序.

MysqL教程

MysqL教程1.优化查询的查询缓存

MysqL教程大部分MysqL服务器都有查询缓存功能.这是提高性能的最有效的办法之一,这是由数据库引擎私下处理的.当同一个查询被多次执行,结果会直接从缓存里提取,这样速度就很快.

MysqL教程主要的问题是,这对程序员来说太简单了,不容易看到,我们很多人都容易忽略.我们实际上是可以组织查询缓存执行任务的.

MysqL教程// 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'");

MysqL教程查询缓存在第一行不执行的原因在于CURDTE()功能的使用.这适用于所有的非确定性功能,就像Now()和RAND()等等...因为功能返回的结果是可变的.MysqL决定禁用查询器的查询缓存.我们所必要做的是通过添加一额外一行PHP,在查询前阻止它发生.

MysqL教程2. EXPLAIN你的选择查询

MysqL教程使用EXPLAIN关键词可以赞助了解MysqL是怎样运行你的查询的.这有助于发现瓶颈和查询或表结构的其它问题.

MysqL教程EXPLAIN的查询结果会展示哪一个索引被使用过,表现怎样扫描和储存的,等等...

MysqL教程选择一个SELECT查询(一个有连接的复杂查询会更好),在它的前面添加关键词EXPLAIN,这样就可以直接使用数据库了.结果会以一个漂亮的表来展示.例如,就比如我执行连接时忘了添加一栏的索引：

MysqL教程

MysqL教程现在它只会从表2里面扫描9和16行,而非扫描7883行.经验法则是乘以所有“行”那一栏的数字,你的查询性能会跟结果数字成比例的.

MysqL教程3. 获取唯一行时使用liMIT 1

MysqL教程有时当你查表时,你已经知道你正在查找的结果只有一行.你可能正在获取唯一记录,或者你可能只是查询是否存在满足你的WHERE子句条件的记录.

MysqL教程在这种情况下,将liMIT 1添加到查询条件中可以提高性能.这样,数据库引擎将在找到刚刚第一个记录之后停止扫描记录,而不是遍历整个表或索引.

MysqL教程// 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) {  // ...}

MysqL教程4. 索引搜索字段

MysqL教程索引不仅仅是为了主键或唯一键.如果你会在你的表中依照任何列搜索,你就都应该索引它们.

MysqL教程

MysqL教程正如你所看到的,这个规则也适用于如 "last_name liKE 'a%'"的部分字符串搜索.当从字符串的开头搜索时,MysqL就可以使用那一列的索引.

MysqL教程你也应该明白什么样搜索可以不使用有规律的索引.例如,当搜索一个单词时(例如,"WHERE post_content liKE '%apple%'"),你将不会看到普通索引的好处.你最好使用 MysqL 全文搜索或者构建你自己的索引办理方案.

MysqL教程5. 索引并对连接使用同样的字段类型

MysqL教程如果你的应用程序包括许多连接查询,你需要确保连接的字段在两张表上都建立了索引. 这会影响MysqL如何内部优化连接 *** 作.

MysqL教程此外,被连接的字段,必要使用同样类型.例如,如果你使用一个DECIMAL字段,连接另一张表的INT字段,MysqL将无法使用至少一个索引. 即使字符编码也必要使用相同的字符类型.

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

MysqL教程6. 不要ORDER BY RAND()

MysqL教程起初这是一个听起来挺酷的技巧,让许多菜鸟程序员陷入了这个陷阱.但你可能不知道,一旦你开始在查询中使用它,你创建了非常可怕的查询瓶颈.

MysqL教程如果你真的需要对结果随机排序,这有一个更好的办法.补充一些额外代码,你将可以防止当数据成指数级增长时造成的瓶颈.关键问题是,MysqL必须在排序之前对表中的每一行执行RAND() *** 作(这需要处理能力),并且仅仅给出一行.

MysqL教程// 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教程所以挑选一个小于结果数的随机数,并将其用作liMIT子句中的偏移量.

MysqL教程7. 避免使用SELECT *

MysqL教程从数据表中读取的数据越多,查询 *** 作速度就越慢.它增加了磁盘 *** 作所需的时间.此外,当数据库服务器与Web服务器分开时,由于必需在服务器之间传输数据,将会有更长的网络延迟.

MysqL教程这是一个好习惯：当你使用SELECT语句时总是指定你必要的列.

MysqL教程// 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

MysqL教程8. 几乎总是有一个ID字段

MysqL教程在每个以ID列为PRIMARY KEY的数据表中,优先选择auto_INCREMENT或者INT. 也可以优选使用UNSIGNED,因为该值不克不及为负的.

MysqL教程即使你拥有一个具有唯一用户名字段的用户表,也不要将其作为主键. VARCHAR字段作为主键(检索)速度较慢.通过内部ID引用所有的用户数据,你的代码中将更加结构化.

MysqL教程有些后台 *** 作是由MysqL引擎自己完成的,它在内部使用主键字段.当数据库设置越复杂(集群,分区等...),这就变得更加重要了.

MysqL教程这个规则的一个可能的例外是“关联表”,用于两个表之间的多对多类型的关联.例如,“posts_Tags”表中包括两列：post_ID,tag_ID,用于保存表名为“post”和“Tags”的两个表之间的关系.这些表可以具有包括两个ID字段的PRIMARY键.

MysqL教程9. 相比VARCHAR优先使用ENUM

MysqL教程ENUM枚举类型是非常快速和紧凑的.在内部它们像tinyint一样存储,但它们可以包括和显示字符串值.这使他们成为某些领域的完美候选.

MysqL教程如果有一个字段只包括几种不同的值,请使用ENUM而不是VARCHAR.例如,它可以是名为“status”的列,并且只包括诸如“active”,“inactive”,“pending”,“expired”等的值...

MysqL教程关于如何重构你的数据表,甚至有一种办法是可以从MysqL本身得到“建议”. 当你有一个VARCHAR字段,它实际上建议你将该列类型更改为ENUM.这通过调用PROCEDURE ANALYZE()来完成.

MysqL教程10. 使用PROCEDURE ANALYSE()获取建议

MysqL教程PROCEDURE ANALYSE() 将使用MysqL分析列结构和表中的实际数据,为你提供一些建议.它只有在数据表中有实际数据时才有用,因为这在分析决策时很重要.

MysqL教程例如,如果你创建了一个INT类型的主键,但没有太多行,MysqL则可能建议您改用MEDIUMINT.或者如果你使用VARCHAR字段,如果内外只有很少的取值,你可能会得到一个建议是将其转换为ENUM.

MysqL教程你也可以在其中一个表视图中单击phpmyadmin中的“建议表结构”链接来执行此 *** 作.

MysqL教程

MysqL教程请记住,这些只是建议. 如果你的数据表变得越来越大,他们甚至可能不是正确的建议.至于如何修改最终是你来决定.

MysqL教程11. 如果可以的话使用NOT NulL

MysqL教程除非你有非常重要的理由使用NulL值,否则你应该设置你的列为NOT NulL.

MysqL教程首先,问一下你本身在空字符串值和NulL值之间(对应INT字段：0 vs. NulL)是否有任何的不同.如果没有理由一起使用这两个,那么你就不需要一个NulL字段(你知道在Oracle中NulL和空字符串是一样的吗?).

MysqL教程NulL列需要额外的空间,他们增加了你的比拟语句的复杂度.如果可以的话尽量避免它们.当然,我理解一些人,他们也许有非常重要的理由使用NulL值,这不总是一件坏事.

MysqL教程摘自MysqL 文档：

MysqL教程"NulL列在行记录它们的值是否为NulL时必要额外的空间.例如MyISAM 表,每一个NulL列拥有额外的一个比特,聚集在最近的字节."

MysqL教程12. 预处理语句

MysqL教程使用预处理语句有诸多好处,包括更高的性能和更好的平安性.

MysqL教程预处理语句默认情况下会过滤绑定到它的变量,这对于避免sql注入攻击极为有效.当然你也可以指定要过滤的变量.但这些办法更容易出现人为错误,也更容易被程序员遗忘.这在使用框架或 ORM 的时候会出现一些问题.

MysqL教程既然我们关注性能,那就应该说说这个方面的好处.当在应用中多次使用同一个查询的时候,它的好处特别明显.既然向同一个预备好的语句中传入不同的参数值,MysqL 对这个语句也只会进行一次解析.

MysqL教程同时,最新版本的 MysqL 在传输预备好的语句时会采用二进制形式,这样做的作用非常明显,而且对减少网络延迟很有赞助.

MysqL教程曾经有一段时间,许多程序员为了一个重要的原因则避免使用预处理语句.这个原因便是,它们不会被MysqL 缓存.不过在 5.1 版本的某个时候,查询缓存也得到的支持.

MysqL教程想在 PHP 中使用预处理语句,你可以看看 MysqLi 扩展 或使用数据抽象层,如 PDO.

MysqL教程// create a prepared statementif ($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();}

MysqL教程13. 无缓冲查询

MysqL教程通常当你从脚本执行一个查询,在它可以继续后面的任务之前将必要等待查询执行完成.你可以使用无缓冲的查询来改变这一情况.

MysqL教程在PHP 文档中对  MysqL_unbuffered_query() f函数有一个很好的解释： 

MysqL教程"MysqL_unbuffered_query() 发送SQL查询语句到MysqL不会像 MysqL_query()那样自动地取并缓冲结果行.这让产生大量结果集的查询节省了大量的内存,在第一行已经被取回时你就可以立即在结果集上继续工作,而不用比及SQL查询被执行完成."

MysqL教程然而,它有必定的局限性.你必须在执行另一个查询之前读取所有的行或调用MysqL_free_result() .另外你不能在结果集上使用MysqL_num_rows() 或 MysqL_data_seek() .

MysqL教程14. 使用 UNSIGNED INT 存储IP地址

MysqL教程很多程序员没有意识到可以使用整数类型的字段来存储 IP 地址,所以一直使用 VARCHAR(15) 类型的字段.使用 INT 只必要 4 个字节的空间,而且字段长度固定.

MysqL教程必需确保列是 UNSINGED INT 类型,因为 IP 地址可能会用到 32 位无符号整型数据的每一个位.

MysqL教程在查询中可以使用 INET_ATON() 来把一个IP转换为整数,用 INET_NTOA() 来进行相反的 *** 作.在 PHP 也有类似的函数,ip2long() 和 long2ip().

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

MysqL教程15. 固定长度(静态)的表会更快

MysqL教程(译者注：这里提到的表的长度,实际是指表头的长度,即表中每条数据占用的空间大小,而不是指表的数据量)

MysqL教程如果表中所有列都是“固定长度”,那么这个表被认为是“静态”或“固定长度”的.不固定的列类型包括 VARCHAR、TEXT、BLOB等.即使表中只包括一个这些类型的列,这个表就不再是固定长度的,MysqL 引擎会以不同的方式来处理它.

MysqL教程固定长度的表会提高性能,因为 MysqL 引擎在记录中检索的时候速度会更快.如果想读取表中的某一地,它可以直接计算出这一行的位置.如果行的大小不固定,那就必要在主键中进行检索.

MysqL教程它们也易于缓存,瓦解后容易重建.不过它们也会占用更多空间.例如,如果你把一个 VARCHAR(20) 的字符改为 CHAR(20) 类型,它会总是占用 20 个字节,不管里面存的是什么内容.

MysqL教程你可以使用“垂直分区”技术,将长度变化的列拆分到另一张表中.来看看：

MysqL教程16. 垂直分区

MysqL教程垂直分区是为了优化表结构而对其进行纵向拆分的行为.

MysqL教程示例 1: 你可能会有一张用户表,包括家庭住址,而这个不是一个常用数据.这时候你可以选择把表拆分开,将住址信息保存到另一个表中.这样你的主用户表就会更小.如你所知,表越小越快.

MysqL教程示例 2: 表中有一个 "last_login" 字段,用户每次登录网站都会更新这个字段,而每次更新都会导致这个表缓存的查询数据被清空.这种情况下你可以将那个字段放到另一张表里,坚持用户表更新量最小.

MysqL教程不过你也必要确保不会经常联合查询分开后的两张表,要不然你就得忍受由这带来的性能下降.

MysqL教程17. 拆分大型DELETE或INSERT语句

MysqL教程如果你必要在网站上执行大型DELETE或INSERT查询,则必要注意不要影响网络流量.当执行大型语句时,它会锁表并使你的Web应用程序停止.

MysqL教程Apache运行许多并行进程/线程. 因此它执行脚本效率很高.所以服务器不期望打开过多的连接和进程,这很消耗资源,特别是内存.

MysqL教程如果你锁表很长时间(如30秒或更长),在一个高流量的网站,会导致进程和查询堆积,处理这些进程和查询可能需要很长时间,最终甚至使你的网站瓦解.

MysqL教程如果你的维护脚本必要删除大量的行,只需使用liMIT子句,以避免阻塞.

MysqL教程while (1) {  MysqL_query("DELETE FROM logs WHERE log_date <= '2009-10-01' liMIT 10000");  if (MysqL_affected_rows() == 0) {    // done deleting    break;  }  // you can even pause a bit  usleep(50000);}

MysqL教程18. 越小的列越快

MysqL教程对于数据库引擎来说,磁盘空间可能是最必要注意的瓶颈.对性能而言,“小”和“紧缩”有助于减少磁盘传输量.

MysqL教程MysqL 文档中有一个列表,列举了各种数据类型所必要的存储空间.

MysqL教程如果数据表预计只会有少量的行,那就没需要把主键定义为 INT 类型,可以用 MEDIUMINT、SMALliNT 甚至 tinyint 来代替.(译者注：对于日期数据,)如果不需要时间部分,就应该使用 DATE 而不是 DATETIME.

MysqL教程请确保留出合理的数据成长空间,不然就可能造成像Slashdot那样的结果(译者注：Slashdot 因为数据增长将评论表的主键改为了 INT 型,但没有修改其父表中的相应的数据类型,虽然一个 ALTER 语句就可以办理问题,但是需要至少停止某些业务三个小时).

MysqL教程19. 选择正确的存储引擎

MysqL教程MysqL 有两个主要的存储引擎：MyISAM 和 InnoDB,它们各有利弊.

MysqL教程MyISAM 适用于读哀求特别多的应用,但不适用于有大量写哀求的情况.甚至你只是要更新一行中的某个字段,都会造成整张表被锁,然后直到这个查询完成,其它进程都不能从这张表读取数据.MyISAM 在计算 SELECT COUNT(*) 这种类型的查询时速度非常快.

MysqL教程InnoDB 是一个复杂的存储引擎,在多数小型应用中它比 MyISAM 慢.但是它支持行级锁,有更好的尺度.它还支持一些高级特性,好比事务.

MysqL教程MyISAM 存储引擎

MysqL教程InnoDB 存储引擎

MysqL教程20. 使用对象关系映射器(ORM,Object Relational Mapper)

MysqL教程通过使用ORM(对象关系映射器),你可以获得必定的性能提升.ORM可以完成的一切事情,手动编码也可完成.但这可能意味着需要太多额外的工作,并且需要高水平的专业知识.

ORM以“延迟加载”著称.这意味着它们仅在需要时获取实际值.但是你需要小心处理他们,否则你可能最终创建了许多微型查询,这会降低数据库性能.

ORM还可以将多个查询批处理到事务中,其 *** 作速度比向数据库发送单个查询快得多.

目前我最喜欢的PHP-ORM是Doctrine.我写了一篇关于如何安装Doctrine与CodeIgniter的文章(install Doctrine with CodeIgniter).

MysqL教程21. 小心使用持久连接

MysqL教程持久连接意味着减少重建连接到MysqL的成本. 当持久连接被创建时,它将坚持打开状态直到脚本完成运行. 因为Apache重用它的子进程,下一次进程运行一个新的脚本时,它将重用相同的MysqL连接.

MysqL教程PHP：MysqL_pconnect()

MysqL教程理论上看起来不错. 但从我个人(和许多其他人)的经验看来,这个功能可能会导致更多麻烦. 你可能会出现连接数限制问题、内存问题等等.

Apache总是并行运行的,它创建许多子进程. 这是持久连接在这种环境中不克不及很好工作的主要原因. 在你考虑使用MysqL_pconnect()之前,请咨询你的系统管理员.

《MysqL必读MysqL 的 20+ 条最佳实践》是否对您有启发，欢迎查看更多与《MysqL必读MysqL 的 20+ 条最佳实践》相关教程，学精学透。内存溢出 jb51.cc为您提供精彩教程。

总结

以上是内存溢出为你收集整理的Mysql必读MySQL 的 20+ 条最佳实践全部内容，希望文章能够帮你解决Mysql必读MySQL 的 20+ 条最佳实践所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。