MySQL优化

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

大多数的MySQL服务器都开启了查询缓存，这是提高性能最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续相同的查询就不用操作表而直接访问缓存的结果了。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存，请看下面的示例：

  // 查询缓存不开启

  $r = mysql_query(“SELECT username FROM user WHERE reg_date >= CURDATE()”);

 //开启查询缓存

  $today = date(“Y-m-d”);

 $r = mysql_query(“SELECT username FROM user WHERE reg_date >= ‘$today’”);

 上面两条SQL语句的差别就是CURDATE()，MySQL的查询缓存对这个函数不起作用，所以，像NOW()和RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启查询缓存。

 

2.       使用EXPLAIN查看你的SELECT查询

使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的，这可以帮助你分析你的查询语句或是表结构的性能瓶颈。

挑一个你的SELECT语句（推荐挑选一个最复杂的，有多表联接的），把关键字EXPLAIN加到SELECT的前面，然后执行查询操作，你会看到一个表格数据，这个表格数据里的每一列分别代表的意思，请点击下面的连接：http://blog.csdn.net/spring_yyy/article/details/12971183

 

3．当只要一行数据时使用LIMIT 1

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

 

4.       为搜索字段建立索引

索引并不一定就是给主键和是唯一的字段，如果在你的表中，有某个字段你总要用来做搜索条件，那么，请为其建立索引吧。

另外，你应该也需要知道什么样的搜索是不能使用索引的，例如：

SELECT * FROM news WHERE content LIKE ‘%apple%’

这里前后都有一个通配符(%)，此时索引是没有意义的，如果是’apple%’，这样mysql是会使用索引的。

 

5．在Join表的时候使用相同类型的列，并将其做索引

   如果在你的应用程序有很多JOIN查询，你应该确认两个表中的join的字段是被建立过索引的，这样，mysql内部会启动为你优化join的SQL语句的机制。

   而且，这些被用来join的字段，应该是相同的类型的。例如，如果你要把一个decimal字段的一个int字段join在一起，mysql就无法使用它们的索引。对于那些varchar类型，还需要有相同的字符集才行（两个表的字符集有可能是不一样的）。

 

6. 千万不要ORDER BY RAND()

想打乱返回的数据行？随机挑选一些数据？真不明白谁发明了这种用法，但很多新手都很喜欢这样用（哈哈，其实我曾经用这样用过），但你却不了解这样做将有多么可怕的性能问题。

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

 

7. 避免SELECT *

select *这种想法，想必很多人都写过，特别是新手，但是这样会导致从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两如独立的服务器的话，这还会增加网络传输的负载。所以，你应该要养成一个需要什么就取什么字段的好习惯。

 

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

我们应该为数据库里的每张表设置一个ID做为其主键，而且最好的是一个int型的（推荐使用unsigned，表示无符号的整型）并且设置上自动增长的AUTO_INCREMENT标志。  就算是你的user表里有一个主键叫”email”的字段，那么你也别让它成为主键，因为使用varchar类型的当主键会使得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在mysql数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区，等等……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成，我们把这种情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID的课程ID都叫做“外键”，其共同组成主键

 

9. 使用ENUM而不是VARCHAR

Enum类型是非常快和紧凑的，在实际上保存的是tinyiny，但其外表上显示为字符串，这样一来，用这个字段来做一些选列变得相当的完美。

如果你有一个这段，比如“性别”，“国家”，“民族”，“状态”，“部门”，你知道这些字段的取值是有限而且是固定的，那么你应该使用enum而不是varchar！如果你觉得emum使用的麻烦，那么你也可以直接用TINYINT来保存

 

 

10. 从PROCEDURE ANALYSE()取得建议

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

   例如，如果你建了一个int字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成UNSIGNED INT。或是你使用了一个varchar字段，因为数据不多，你可能会得到一个让你把它改成enum的建议，这些建议，都是可能因为数据不够多，所以决策做得就不够准确。

其语法如下: select * from table  PROCEDURE analyse();

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

 

11．尽可能的使用NOT NULL

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

   首先，问问你自己，empty和null有多大的区别（如果是int那就是0和null）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（在oracle里，null和empty的字符串是一样的）

   这不是在程序里null不需要空间，在数据库里千万不要以为null不需要空间，而且它还其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值！

 

12. Prepared Statements

   Prepared Statements很像存储过程，是一种运行在后台的SQL语句，我们可以从使用Prepared Statements获得很多好处，无论是性能问题还是安全问题！

   Prepared Statements可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入攻击”。当然，你也可以手动检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了，当我们使用一些framework或是 ORM的时候，这样的问题会好很多。

在性能度方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势，你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。

 

13.把IP地址改成unsigned int

   很多程序员都会创建一个varchar(15)字段来存放字符串形式的IP，而不是整形的IP。如果你用整形来存放只需要4个字节，并且你可以有定长的字段。而且，这会为你带上查询上的优势，尤其是当你需要使用这样的WHERE条件：IP BETWEEN ip1 AND ip2。我们必需要使用unsigned int，因为IP地址会使用整个32位的无符号整形。而你的查询，你可以使用inet_aton()来把一个字符串IP转成一个整形，并使用inet_ntoa()把一个整形转成一个字符串IP。

 

14. 固定长度的表会更快

   如果表中的所有字段都是“固定长度”的，整个表会被认为是“static”或“fixed-length”

。例如，表中没有如下类型的字段：varchar , text , blob。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL引擎会用另一种方法来处理。

    固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快，而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，它都是要分配那么多的空间在那里。

 

15.垂直分割

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

例一：在user表中有一个字段是字庭地址，这个字段是可选的，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把它放到另外一张表中呢？这样会让你的表有更好的性能，大家想想是不是，我对于用户表来说，只有用户ID，用户名，密码，用户角色是我所关心的会经常用到。小一点的表总是会有好的性能。

例二：另外可能表里还有一个叫“last_login”的字段，它会在每次用户登录时被更新，但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID、用户名等的字段不停的读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性的去join它们，不然的话，这样的性能会比不分割时还要差，而且，会有极数级的性能下降。

 

16 拆分大的DELETE和INSERT语句

   如果你需要在一个在线网站上去执行一个大的DELETE和INSERT查询，你需要非常小心，要避免你的操作让你的整个网站停止相应响，因为这两个操作会是锁表的，表一锁住了，别的操作就进不来了。

 

17．越小的列会越快 

   对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

   如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用int来做主键，使用mediumint,smallint或是更小的tinyint会更经济一些。如果你不需要记录时间，使用Date要比Datetime好得多。

 

18 选择正砍确的存储引擎

   在MySQL中有两个最常用的存储引擎（MyISAM和InnoDB），每个引擎都有利弊。

   MyISAM适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作这张表，直到update进程结束工作。另外，MyISAM对于select count(*)会非常好，因为它有保证表的总记录数。

InnoDB的趋势会是一个非常复杂的存储引擎。对于一些小的应用，它会比MyISAM还慢。但是它支持行锁，于是在写操作的时候，它会更优秀。并且它还支持外键、事务等MyISAM不具备的功能。

 

19.无缓冲的查询

   正常情况下，当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句的返回，你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。

   在php的文档里有一个非常不错的说明：myql_unbuffered_query()函数：

   myql_unbuffered_query()发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果返回，只需要第一行数据的时候，你就可以马上开始工作于查询了。

  然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询调用mysql_free_result()清除结果。而且，mysql_num_rows()或mysql_data_seek()将无法使用。所以，是否使用缓冲的查询你需要仔细考虑。

 

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

使用ORM(Object Relational Mapping)，你能获得可靠的性能增涨，一个ORM可以做的所有事情，也能被手动的编写出来。

ORM的最重要的是“lazy loading”，也就是说，只有在真正需要去取值的时候才会去做数据库的操作，但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很小的查询反而降低性能。

ORM还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多。

 

21．小心“永久链接”

   “永久链接”的目的是用来减少重新创建MySQL链接的次数，当一个链接被创建了，它会永远处在链接状态，就算是数据库操作已经结束了。而且，自从Apache开始重用它的子进程后，也就是说下一次的HTTP请求会重用Apache的子进程。并重用相同的MySQL链接。