今天，数据库操作越来越成为整个应用程序的性能瓶颈，Web应用程序尤其如此。关于数据库的性能，这不仅是DBA需要担心的问题，也是我们的程序员需要注意的问题。在设计数据库表结构时，在操作数据库时需要注意数据操作的性能(特别是在查找表时需要注意SQL语句)。在这里，我们不讨论太多SQL语句的优化，而只讨论MySQL中web应用程序最多的数据库。希望下面这些优化技巧对您有用。

1.为查询缓存优化你的查询

大多数MySQL服务器都启用了查询缓存。这是最有效的改进方法之一，由MySQL数据库引擎处理。当许多相同的查询被多次执行时，这些查询的结果被放置在缓存中，以便后续相同的查询可以访问缓存的结果，而不需要操作表。这里的主要问题是，对于程序员来说，这一点很容易被忽略。因为，我们的一些查询将不允许MySQL使用缓存。请看下面的例子:

上面两条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.当只要一行数据时使用LIMIT1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上LIMIT 1可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。(请注意，第一条中是Select *，第二条是Select 1)：

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类型，还需要有相同的字符集才行。(两个表的字符集有可能不一样)

6.千万不要ORDER BY RAND()

想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数(很耗CPU时间)，而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序)。

下面的示例是随机挑一条记录：

7.避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。所以，你应该养成一个需要什么就取什么的好的习惯。

8.永远为每张表设置一个ID

我们应该为数据库中的每个表设置一个ID作为它的主键，最好是INT类型(建议使用UNSIGNED)，并设置自动添加的AUTO_INCREMENT标志。即使您的users表有一个名为“email”的主键字段，您也不应该使它成为主键。使用VARCHAR类型可以在使用主键时降低性能。此外，在程序中，应该使用表的ID来构造数据结构。此外，在MySQL数据引擎下，仍然有一些操作需要使用主键。在这些情况下，主键的性能和设置变得非常重要，例如集群、分区……

这里只有一个例外，那就是“关联表”的“外键”，即表的主键由几个单独表的主键组成。我们称这种情况为“外键”。例如,如果有一个“学生表”与学生ID和一个“表”课程ID,那么“成绩表”是“关联表”,这是与学生表和相关课程,在记分表,学号和课程ID被称为“外键”一起构成的主键。

9.使用ENUM而不是VARCHAR

ENUM型非常快速和紧凑。实际上，它保存了TINYINT，但是它的外观显示为一个字符串。这样，使用这个字段来创建一些选项列表就变得非常完美了。如果您有一个字段，如“性别”、“国家”、“国家”、“州”或“部门”，您知道这些字段的值是有限的和固定的，那么应该使用ENUM而不是VARCHAR。

10.从PROCEDURE ANALYSE()取得建议

PROCEDURE ANALYSE() 将让MySQL帮助您分析字段及其实际数据，并将给您一些有用的建议。这些建议只有在表中有实际数据时才有用，因为一些重大决策需要基于数据。

例如，如果您创建一个INT字段作为主键，但是没有太多数据，那么PROCEDURE analysis()将建议您将该字段的类型更改为MEDIUMINT。或者如果您使用VARCHAR字段，因为没有太多数据，您可能会得到将其更改为ENUM的建议。这些建议都是因为数据不够，所以决策不够准确。在phpmyadmin中，您可以在查看表时单击“建议表结构”来查看这些建议。

一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。

11.尽可能的使用NOT NULL

除非你有一个很特别的原因去使用NULL值，你应该总是让你的字段保持NOT NULL。这看起来好像有点争议，请往下看。

首先，问问您自己“Empty”和“NULL”之间有多大的区别(如果INT，那就是0和NULL)?如果您认为它们之间没有区别，那么不应该使用NULL。(你知道吗?在Oracle中，NULL和空字符串是相同的!)不要假设NULL不需要空间，它需要额外的空间，当您比较时，您的程序更复杂。当然，这并不是说不能使用NULL。现实是非常复杂的。在某些情况下，需要使用NULL值。以下摘录自MySQL自己的文档:

12. PreparedStatements

Prepared Statements很像存储过程。它们是在后台运行的SQL语句的集合。无论是性能问题还是安全问题，使用Prepared Statements都可以获得很多好处。Prepared Statements可以检查绑定的一些变量，这样就可以保护程序免受“SQL注入”攻击。当然，您也可以手动检查变量，但是，手动检查很容易出现问题，而且常常被程序员遗忘。当我们使用一些框架或ORM时，这个问题会更好。在性能方面，当一个相同的查询被多次使用时，这将给您带来相当大的性能优势。您可以为这些Prepared Statements定义一些参数，MySQL只解析它们一次。虽然MySQL的最新版本在传输Prepared Statements时使用二进制格式，但是这使得网络传输非常高效。

在PHP中要使用prepared statements，你可以查看其使用手册：mysqli扩展或是使用数据库抽象层，如：PDO.

13.无缓冲的查询

在正常情况下，当您在脚本中执行SQL语句时，程序将在此停止，直到没有返回SQL语句，然后程序将继续执行。您可以使用未缓冲的查询来更改此行为。在PHP文档中有一个很好的描述:mysql_unbuffered_query()函数:

上面这句话的意思是，mysql_unbuffered_query()向MySQL发送一条SQL语句，并且不会自动地执行fethch并缓存mysql_query()这样的结果。这可以节省大量的内存，尤其是那些产生大量结果的内存，而且您不必等到所有结果都返回时，就在第一行数据返回时，您可以立即开始工作。

14.把IP地址存成UNSIGNED INT

许多程序员创建一个VARCHAR(15)字段来保存字符串形式的IP，而不是形状的IP。如果使用整形来存储它，只需要4个字节，并且可以有固定长度的字段。此外，这将为您提供查询的优势，特别是当您需要使用诸如WHERE条件:ip1和ip2之间的IP时。我们必须使用无符号整数，因为IP地址将使用整个32位无符号整数。对于查询，可以使用INET_ATON()将字符串IP转换为整数，并使用INET_NTOA()将整数转换为字符串IP。在PHP中，也有这样的函数ip2long()和long2ip()。

15.固定长度的表会更快

如果表中的所有字段都是“固定长度”，那么整个表将被认为是“静态”或“固定长度”。例如，表中没有以下类型的字段:VARCHAR、TEXT、BLOB。只要包含其中一个字段，该表就不是“固定长度的静态表”，因此MySQL引擎将以另一种方式处理它。

固定长度的表将提高性能，因为MySQL的搜索速度更快，因为这些固定长度的表很容易计算下一个数据的偏移量，所以读取速度自然会很快。如果字段的长度不是固定的，那么每次你想找到下一个，你需要程序找到主键。此外，固定长度的表更容易缓存和重建。然而，惟一的副作用是固定长度字段会浪费一些空间，因为固定长度字段分配的空间与您使用它们的空间一样多。

16.垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。(以前，在银行做过项目，见过一张表有100多个字段，很恐怖)

“垂直分割”是将数据库中的表转换为多个表的方法，可以降低表的复杂度和字段的数量，从而达到优化的目的。(之前我在银行做过项目，在表格中看到100多个字段，很糟糕)

例1:Users表中有一个字段是家庭地址。该字段是一个可选字段。与个人信息相比，您不需要频繁地读取或重写该字段。那为什么不把他放到另一张桌子上呢?这将使你的手表有更好的性能。想想。当我有很多时间时，我只有user表的用户ID、用户名和密码。、用户角色等将被频繁使用。较小的表总是具有良好的性能。

示例2:您有一个名为“last_login”的字段，该字段将在每次用户登录时更新。但是，每次更新都会清空表的查询缓存。因此，您可以将此字段放在另一个表中，这样就不会影响您读取用户ID、用户名和用户角色的能力，因为查询缓存将帮助您提高很多性能。

此外，您需要注意的是，表中由这些分隔字段组成，您不会经常将它们连接起来，否则，这样的性能会比不分割差，并且会出现两极数下降的情况。

17.拆分大的DELETE或INSERT语句

如果您需要在一个在线网站上执行一个大型的删除或插入查询，您需要非常小心，并避免停止您的整个网站的操作。因为这两个操作将锁定表，所以表被锁定，而其他操作无法进入。

Apache有许多子进程或线程。因此，它的工作效率很高，而且我们的服务器不想有太多的子进程、线程和数据库链接，这对于服务器资源来说是一个巨大的资源，尤其是内存。如果你锁表一段时间,如30秒,然后一个网站有很高的流量,累计访问进程/线程的30秒,数据库链接,打开的文件,可能不仅只会让你公园web服务崩溃,而且它也可能让你的整个服务器挂电话了。

所以，如果你有一个大问题，你必须把它分解，使用极限条件是一个好方法。这里有一个例子:

18.越小的列会越快

对于大多数数据库引擎来说，硬盘操作可能是最重要的瓶颈。因此，在这种情况下，获得数据压缩非常有用，因为它减少了对硬盘的访问。

如果一个表只有几列(比如字典表，配置表)，那么没有理由使用INT作为主键。使用MEDIUMINT、SMALLINT或更小的TINYINT更经济。如果不需要记录时间，使用DATE要比DATETIME好得多。当然，你也需要有足够的扩展空间。否则，你以后会这样做，你会死得很惨。参见Slashdot的示例(2009年11月6日)，一个简单的ALTER TABLE语句花费了3个多小时，因为其中包含了1600万条数据。

19.选择正确的存储引擎

MySQL中有两种存储引擎MyISAM和InnoDB，各有优缺点。前一篇文章“MySQL: InnoDB还是MyISAM?”讨论了这个问题。MyISAM适用于一些需要大量查询的应用程序，但不适用于大量的写操作。即使您只需要更新一个字段，整个表也会被锁定，而其他进程，甚至读进程也不能操作，直到读操作完成。此外，MyISAM对于SELECT COUNT(*)之类的计算非常快。

InnoDB的趋势将是一个非常复杂的存储引擎，对于一些小型应用程序，它将比MyISAM慢。他支持“行锁”，所以当有更多的写操作时，情况会更好。而且，他还支持更高级的应用程序，比如:事务。

下面是MySQL的手册：

target=”_blank”MyISAM Storage Engine
InnoDB Storage Engine

20.使用一个对象关系映射器(Object Relational Mapper)

使用ORM(Object Relational Mapper)，您可以获得可靠的性能收益。ORM可以做的所有事情都可以手工编写。然而，这需要一位资深专家。ORM最重要的一点是“延迟加载”，这意味着只有在需要获取值时才需要进行加载。但是您还需要注意这种机制的副作用，因为它可能通过创建许多许多小查询来降低性能。ORM还可以将SQL语句打包到单个事务中，这比单独执行SQL语句快得多。目前，个人最喜欢的PHP ORM是:Doctrine。

21.小心“永久链接”

“永久链接”的目的是减少重新创建MySQL链接的次数。创建链接时，即使数据库操作已经结束，它也始终处于连接状态。此外，由于我们的Apache已经开始重用它的子进程——也就是说，下一个HTTP请求将重用Apache的子进程并重用相同的MySQL链接。理论上，这听起来很不错。但是从个人经验(以及大多数人)来看，这个特性会带来更多的麻烦。因为，您只有有限数量的链接、内存问题、文件句柄计数等等。而且，Apache运行在一个非常并行的环境中，并且创建了很多很多进程。这就是为什么这种“永久联系”机制效果不佳的原因。在决定使用永久链接之前，您需要考虑整个系统的体系结构。