数据库优化建议

　  数据库优化建议（21条）~.~

• 为缓存优化你的查询

  大多数的MySQL服务器都开启了看查询缓存。这是提高性能最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表，而直接访问缓存结果了。像NOW()和RAND()或者是其他的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是易变的，所以，需要用一个变量来代替MySQL的函数，从而开启缓存

• EXPLAIN你的SELECT查询

  使用SELECT查询时，前面加上EXPLAIN关键字可以让你知道MySQL是如何处理SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键是如何利用的，你的数据表是如何被搜索和排序的等等...

• 当只有一行数据时使用LIMTT1

  当查询表的时候，已经知道结果只会有一条结果，但因为可能需要去fetch游标，或是会去检查返回的记录数。在这种情况下，加上LIMTT1可以增加性能。这样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

• 为搜索字段建立索引

  索引并不一定就是给主键或是唯一字段。在一张表中，如果某个字段经常会用来做搜索，那么，就可以为其建立索引

• 在Join表得我时候使用相当类型的列，并将其索引

  如果一个应用程序有很多联合（join）查询，应该确认两个表中关联的字段是被建过索引的，而且应该是相同类型的字段。这样，MySQL内部会启动为你优化join的SQL语句的机制

• 不要使用ORDER BY RAND()

  这样会打乱数据行，让数据的性能呈指数级下降

• 避免SELECT *

  从数据库里读取的数据越多，那么查询就会变得越慢。如果你的数据库服务器和WEB服务器是两*立的服务器的话，还会增加网络传输的负载。

• 为每张表都设置一个主键ID

  应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），用VARCHAR等类型来当主键会使用得性能下降，并设置上自动增加的AUTO_INCREMENT标志。在程序中，应该根据表的ID来构造你的数据结构。

• 字段的取值是有限而且固定的，使用ENUM

  ENUM类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表 上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。如果已经知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR

• 从PROCEDURE ANALYSE()取得建议

  PROCEDURE ANALYSE() 会让MySQL帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

• 尽可能的使用NOT NULL

  除非特殊原因使用NULL值，否则应该总是让字段保持NOT NULL。NULL其实也需要额外的空间，所有尽可能的让字段保持非空约束

• Prepared Statements

  Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用prepared statements获得很多好处，无论是性能问题还是安全问题。Prepared Statements可以检查一些绑定好的变量，这样可以保护程序不会受到“SQL注入式”攻击。在性能方面，当一个相同的查询被使用多次的时候，这会带来可观的性能优势。可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。 虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。

• 无缓冲的查询

  正常情况下，当在脚本中执行一个SQL语句的时候，程序会一直停滞，直到SQL执行完才继续往下执行。可以使用无缓冲查询来改变这个行为。mysql_unbuffered_query()发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且， mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询需要仔细考虑。

• 把IP地址存为UNSIGNED INT

  存放IP字段时，需要使用UNSIGNED INT，因为IP地址会使用整个32位的无符号整形。如果你用整形来存放，只需要4个字节，并且可以有定长的字段。而且，这会带来查询上的优势。在查询中，可以使用INET_ATON()来把一个字符串IP转成一个整形，并使用INET_NTOA()把一个整形转成一个字符串IP。

• 给字段设置固定长度

  如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，它都是要分配那么多的空间。

• 垂直分割

  “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。小一点的表总是会有好的性能。

• 长度越小的列效率越快

  对于大多数数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把数据变得紧凑会非常使用有帮助，因为这减少了对硬盘的访问。如果一个表只有几列，那么使用MEDIUMINT,SMALLINT或是更小的TINYINT会比INT更好。如果不需要记录时间，使用DATE要比DATETIME好的多。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成

• 选择正确的存储引擎

  在MySQL中有两个存储引擎MyISAM和InnoDB，每个引擎都有弊有利。MyISAM适合于大量查询的应用，而对于大量写操作的应用则支持的不太好。

  InnoDB是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM还慢，但是它支持行锁，所以在写操作比较多的时候，会更优秀。而且它还支持如事务等更多的高级应用。

• 使用对象关系映射器

  使用一个对象关系映射器（Object Relational Mapper），能够获得可靠的性能增涨。使用对象关系映射器，只有在需要去取值的时候才会真正去做，但这种机制的副作用是很可能会因为要去创建很多很小的查询反而降低性能。对象关系映射器还可以把你的SQL语句打包成一个事物，这会比单独执行SQL快得多。PHP中可以使用Doctrine对象关系映射器

• 小心永久链接

  “永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算数据库操作已经结束了。而且，自从httpd开始重用它的子进程后，也就是说，下一次的HTTP请求会重用httpd的子进程，并重用相同的MySQL链接。在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数，等等。 而且，httpd运行在极端并行的环境中，会创建很多很多的子进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在决定要使用“永久链接”之前，需要好好地考虑一下整个系统的架构。