MySQL性能优化二十大实战经验[MySQL防范]

本日,数据库的操作越来越成为整个利用的性能瓶颈了,这点关于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索引,并且有表联接:

我们可以看到,前一个后果显示搜索了 7883 行,此后一个只是搜索了两个表的 9 和 16 行.查看rows列可以让我们找到潜在的性能问题.

3. 当只要一行数据时利用 LIMIT 1

当你查询表的有些时刻,你已经知道后果只会有一条后果,但因为你大概需求去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和课程ID叫“外键”其共同构成主键.

9. 利用 ENUM 而不是 VARCHAR

ENUM 范例是非常快和紧凑的.在实际上,其保存的是 TINYINT,但其外表上显示为字符串.这样一来,用这个字段来做一些选项列表变得相当的完善.

假如你有一个字段,比方“性别”,“国家”,“民族”,“状况”或“部门”,你知道这些字段的取值是有限并且固定的,那么,你应当利用 ENUM 而不是 VARCHAR.

MySQL也有一个“倡议”（见第十条）奉告你怎么去重新组织你的表构造.当你有一个 VARCHAR 字段时,这个倡议会奉告你把其改成 ENUM 范例.利用 PROCEDURE ANALYSE() 你可以得到相关的倡议.

10. 从 PROCEDURE ANALYSE() 获得倡议

PROCEDURE ANALYSE() 会让 MySQL 帮你去解析你的字段和其实际的数据,并会给你一些有效的倡议.只有表中有实际的数据,这些倡议才会变得有效,因为要做一些大的决意是需求有数据作为底子的.

比方,假如你成立了一个 INT 字段作为你的主键,但是并没有太多的数据,那么,PROCEDURE ANALYSE()会倡议你把这个字段的范例改成 MEDIUMINT .或是你利用了一个 VARCHAR 字段,因为数据不多,你大概会得到一个让你把它改成 ENUM 的倡议.这些倡议,都是大概因为数据不够多,所以抉择做得就不够准.

在phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些倡议

一定要注意,这些只是倡议,只有当你的表里的数据越来越多时,这些倡议才会变得精确.一定要记着,你才是终究做决意的人.

11. 尽大概的利用 NOT NULL

除非你有一个很分外的缘由去利用 NULL 值,你应当老是让你的字段保持 NOT NULL.这看起来仿佛有点争议,请往下看.

首先,问问你自己“Empty”和“NULL”有多大的辨别（假如是INT,那就是0和NULL）?假如你认为它们之间没有什么辨别,那么你就不要利用NULL.（你知道吗?在 Oracle 里,NULL 和 Empty 的字符串是一样的!)

不要认为 NULL 不需求空间,其需求额外的空间,并且,在你举行对比的时刻,你的程序会更复杂. 当然,这里并非说你就不能利用NULL了,实际情形是很复杂的,仍然会有些情形下,你需求利用NULL值.

下面摘自MySQL自己的文档:

12. Prepared Statements

Prepared Statements很像存储历程,是一种运行在后台的SQL语句调集,我们可以从利用 prepared statements 得到很多好处,无论是性能问题还是安全问题.

Prepared Statements 可以查抄一些你绑定好的变量,这样可以保护你的程序不会遭到“SQL注入式”攻击.当然,你也可以手动地查抄你的这些变量,但是,手动的查抄简单出问题,并且很常常会被程序员忘了.当我们利用一些framework或是ORM的时刻,这样的问题会好一些.

在性能方面,当一个相同的查询被利用多次的时刻,这会为你带来可观的性能上风.你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次.

固然最新版本的MySQL在传输Prepared Statements是利用二进制形势,所以这会使得网络传输非常有效率.

当然,也有一些情形下,我们需求避免利用Prepared Statements,因为其不支持查询缓存.但据说版本5.1后支持了.

在PHP中要利用prepared statements,你可以查看其利用手册:mysqli 扩大 或是利用数据库抽象层,如: PDO.

13. 无缓冲的查询

正常的情形下,当你在当你在你的脚本中履行一个SQL语句的时刻,你的程序会停在那边直到没这个SQL语句返回,然后你的程序再往下持续履行.你可以利用无缓冲查询来改变这个行为.

关于这个事情,在PHP的文档中有一个非常不错的阐明: mysql_unbuffered_query() 函数:

上面那句话翻译过来是说,mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存后果.这会相当节俭很多可观的内存,特别是那些会产生大量后果的查询语句,并且,你不需求等到全部的后果都返回,只需求第一行数据返回的时刻,你便可以开始即刻开始工作于查询后果了.

但是,这会有一些限制.因为你要末把全部行都读走,或是你要在举行下一次的查询前调用 mysql_free_result() 排除后果.并且, mysql_num_rows() 或 mysql_data_seek() 将无法利用.所以,能否利用无缓冲的查询你需求细心考虑.

14. 把IP地址存成 UNSIGNED INT

很多程序员城市成立一个 VARCHAR(15) 字段来存放字符串情势的IP而不是整形的IP.假如你用整形来存放,只需求4个字节,并且你可以有定长的字段.并且,这会为你带来查询上的上风,特别是当你需求利用这样的WHERE条件:IP between ip1 and ip2.

我们必必要利用UNSIGNED INT,因为 IP地址会利用整个32位的无标记整形.

而你的查询,你可以利用 INET_ATON() 来把一个字符串IP转成一个整形,并利用 INET_NTOA() 把一个整形转成一个字符串IP.在PHP中,也有这样的函数 ip2long() 和 long2ip().

15. 固定长度的表会更快

假如表中的全部字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”. 比方,表中没有以下范例的字段: VARCHAR,TEXT,BLOB.只要你包含了此中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种办法来处理.

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

并且,固定长度的表也更简单被缓存和重建.不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间.

利用“垂直分割”技术（见下一条）,你可以分割你的表成为两个一个是定长的,一个则是不定长的.

16. 垂直分割

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

示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,并且你在数据库操作的时刻除了个人信息外,你并不需求常常读取或是改写这个字段.那么,为什么不把他放到别的一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时刻,我关于用户表来说,只有效户ID,用户名,口令,用户角色等会被常常利用.小一点的表老是会有好的性能.

示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新.但是,每次更新时会招致该表的查询缓存被清空.所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不断地读取了,因为查询缓存会帮你增添很多性能.

别的,你需求注意的是,这些被分出去的字段所形成的表,你不会常常性地去Join他们,不然的话,这样的性能会比不分割时还要差,并且,会是极数级的下降.

17. 拆分大的 DELETE 或 INSERT 语句

假如你需求在一个在线的网站上去履行一个大的 DELETE 或 INSERT 查询,你需求非常当心,要避免你的操作让你的整个网站终止呼应.因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了.

Apache 会有很多的子进程或线程.所以,其工作起来相当有效率,而我们的服务器也不但愿有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,特别是内存.

假如你把你的表锁上一段时间,比方30秒钟,那么关于一个有很高拜候量的站点来说,这30秒所堆集的拜候进程/线程,数据库链接,翻开的文件数,大概不但仅会让你泊WEB服务Crash,还大概会让你的整台服务器即刻掛了.

所以,假如你有一个大的处理,你定你一定把其拆分,利用 LIMIT 条件是一个好的办法.下面是一个示例:

18. 越小的列会越快

关于大大都的数据库引擎来说,硬盘操作大概是最庞大的瓶颈.所以,把你的数据变得紧凑会对这种情形非常有帮忙,因为这削减了对硬盘的拜候.

参看 MySQL 的文档 Storage Requirements 查看全部的数据范例.

假如一个表只会有几列罢了（比方说字典表,配置表）,那么,我们就没有来由利用 INT 来做主键,利用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些.假如你不需求记录时间,利用 DATE 要比 DATETIME 好得多.

当然,你也需求留够充足的扩大空间,不然,你日后来干这个事,你会死的很丢脸,参看Slashdot的例子（2009年11月06日）,一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据.

19. 挑选精确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊.酷壳从前文章《MySQL: InnoDB 还是 MyISAM?》谈论和这个事情.

MyISAM 合适于一些需求大量查询的利用,但其关于有大量写操作并非很好.乃至你只是需求update一个字段,整个表城市被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成.别的,MyISAM 关于 SELECT COUNT(*) 这类的计算是超快非常的.

InnoDB 的趋向会是一个非常复杂的存储引擎,关于一些小的利用,它会比 MyISAM 还慢.他是它支持“行锁” ,于是在写操作对比多的时刻,会更优异.并且,他还支持更多的高级利用,比方:事件.

下面是MySQL的手册

target=”_blank”MyISAM Storage Engine

InnoDB Storage Engine

20. 利用一个对象关系映射器（Object Relational Mapper）

利用 ORM (Object Relational Mapper),你可以得到坚固的性能增涨.一个ORM可以做的全部事情,也能被手动的编写出来.但是,这需求一个高级专家.

ORM 的最重要的是“Lazy Loading”,也就是说,只有在需求的去取值的时刻才会去真正的去做.但你也需求当心这种机制的副作用,因为这很有大概会因为要去成立很多很多小的查询反而会降低性能.

ORM 还可以把你的SQL语句打包成一个事件,这会比单独履行他们快得多得多.

目前,个人最喜好的PHP的ORM是:Doctrine.

21. 当心“永久链接”

“永久链接”的目的是用来削减重新成立MySQL链接的次数.当一个链接被成立了,它会永久处在衔接的状况,就算是数据库操作已经完毕了.并且,自从我们的Apache开始重用它的子进程后——也就是说,下一次的HTTP恳求会重用Apache的子进程,并重用相同的 MySQL 链接.

PHP手册:mysql_pconnect()

在理论上来说,这听起来非常的不错.但是从个人经验（也是大大都人的）上来说,这个功效制造出来的麻烦事更多.因为,你只有有限的链接数,内存问题,文件句柄数,等等.

并且,Apache 运行在极度并行的环境中,会成立很多很多的了进程.这就是为什么这种“永久链接”的机制工作地不好的缘由.在你决意要利用“永久链接”之前,你需求好好地考虑一下你的整个系统的架构. 　　以上是“MySQL性能优化二十大实战经验[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：