汗青很长远的一篇文章了，不记得是从那里纪录过来的，不过照样有用
当我们去设想数据库表组织，对操纵数据库时（尤其是查表时的SQL语句），我们都须要注重数据操纵的机能。这里，我们不会讲过量的SQL语句的优化，而只是针对MySQL这一Web运用最多的数据库。愿望下面的这些优化技能对你有用。（引荐课程：MySQL教程）
1. 为查询缓存优化你的查询
大多数的MySQL效劳器都开启了查询缓存。这是进步性最有用的要领之一，而且这是被MySQL的数据库引擎处置惩罚的。当有许多雷同的查询被实行了屡次的时刻，这些查询效果会被放到一个缓存中，如许，后续的雷同的查询就不必操纵表而直接接见缓存效果了。
这里最主要的题目是，关于顺序员来讲，这个事变是很轻易被疏忽的。由于，我们某些查询语句会让MySQL不运用缓存。请看下面的示例：
代码以下:
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
上面两条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. 当只需一行数据时运用 LIMIT 1
当你查询表的有些时刻，你已晓得效果只会有一条效果，但由于你可以须要去fetch游标，或是你或许会去搜检返回的纪录数。
在这类状况下，加上 LIMIT 1 可以增添机能。如许一样，MySQL数据库引擎会在找到一条数据后住手搜刮，而不是继承今后查少下一条相符纪录的数据。
下面的示例，只是为了找一下是不是有“中国”的用户，很明显，背面的会比前面的更有用率。（请注重，第一条中是Select *，第二条是Select 1）
代码以下:
// 没有用率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有用率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
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范例，还须要有雷同的字符集才行。（两个表的字符集有可以不一样）
代码以下:
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 两个 state 字段应当是被建过索引的，而且应当是相称的范例，雷同的字符集。
6. 万万不要 ORDER BY RAND()
想打乱返回的数据行？随机挑一个数据？真不晓得谁发清楚明了这类用法，但许多新手很喜欢如许用。但你确不相识如许做有何等恐惧的机能题目。
假如你真的想把返回的数据行打乱了，你有N种要领可以到达这个目标。如许运用只让你的数据库的机能呈指数级的下落。这里的题目是：MySQL会不得不去实行RAND()函数（很耗CPU时候），而且这是为了每一行纪录去记行，然后再对其排序。就算是你用了Limit 1也杯水车薪（由于要排序）
下面的示例是随机挑一条纪录
代码以下:
// 万万不要如许做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$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");
7. 防止 SELECT *
从数据库里读出越多的数据，那末查询就会变得越慢。而且，假如你的数据库效劳器和WEB效劳器是两台自力的效劳器的话，这还会增添收集传输的负载。
所以，你应当养成一个须要什么就取什么的好的习气。
代码以下:
// 不引荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 引荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
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本身的文档：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
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.
代码以下:
// 建立 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// 绑定参数
$stmt->bind_param("s", $state);
// 实行
$stmt->execute();
// 绑定效果
$stmt->bind_result($username);
// 挪动游标
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
13. 无缓冲的查询
一般的状况下，当你在当你在你的剧本中实行一个SQL语句的时刻，你的顺序会停在那里直到没这个SQL语句返回，然后你的顺序再往下继承实行。你可以运用无缓冲查询来转变这个行动。
关于这个事变，在PHP的文档中有一个异常不错的申明： mysql_unbuffered_query() 函数：
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed.”
上面那句话翻译过来是说，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()。
1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
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 前提是一个好的要领。下面是一个示例：
代码以下:
while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
break;
}
// 每次都要歇息一会儿
usleep(50000);
}
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机能优化的最全面的履历分享的细致内容，更多请关注ki4网别的相干文章！
