当我们去设计数据库表结构对操作数据库时(尤其是查表时SQL语句)我们都需要注意数据操作性能这里我们不会讲过多SQL语句优化而只是针对MySQL这Web应用最多数据库希望下面这些优化窍门技巧对你有用
1. 为查询缓存Cache优化你查询

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

这里最主要问题是对于员来说这个事情是很容易被忽略我们某些查询语句会让MySQL不使用缓存Cache请看下面举例:
复制代码 代码如下:

// 查询缓存Cache不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE");

// 开启查询缓存Cache
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");


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

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 *第 2条是Select 1)
复制代码 代码如下:

// 没有效率:
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
(mysql_num_rows($r) > 0) {
// ...
}

// 有效率:
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
(mysql_num_rows($r) > 0) {
// ...
}



4. 为搜索字段建索引

索引并不定就是给主键或是唯字段如果在你表中有某个字段你总要会经常用来做搜索那么请为其建立索引吧

从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”个是建了索引个是没有索引性能差了4倍左右

另外你应该也需要知道什么样搜索是不能使用正常索引例如当你需要在篇大文章中搜索个词时如: “WHERE post_content LIKE ‘%apple%'”索引可能是没有意义你可能需要使用MySQL全文索引 或是自己做个索引(比如说:搜索关键词或是Tag什么)

5. 在Join表时候使用相当类型例并将其索引
如果你应用有很多 JOIN 查询你应该确认两个表中Join字段是被建过索引这样MySQL内部会启动为你优化JoinSQL语句机制

而且这些被用来Join字段应该是相同类型例如:如果你要把 DECIMAL 字段和个 INT 字段Join在起MySQL就无法使用它们索引对于那些STRING类型还需要有相同集才行(两个表集有可能不样)
复制代码 代码如下:

// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies _disibledevent=>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 _disibledevent=>复制代码 代码如下:

// 创建 prepared statement
($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {

// 绑定参数
$stmt->bind_param("s", $state);

// 执行
$stmt->execute;

// 绑定结果
$stmt->bind_result($username);

// 移动游标
$stmt->fetch;

prf("%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 s, and you can start working _disibledevent=>15. 固定长度表会更快

如果表中所有字段都是“固定长度”整个表会被认为是 “” 或 “fixed-length” 例如表中没有如下类型字段: VARCHARTEXTBLOB只要你包括了其中个这些字段那么这个表就不是“固定长度静态表”了这样MySQL 引擎会用另种思路方法来处理

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

并且固定长度表也更容易被缓存Cache和重建不过唯副作用是固定长度字段会浪费些空间定长字段无论你用不用他都是要分配那么多空间

使用“垂直分割”技术(见下条)你可以分割你表成为两个个是定长个则是不定长
16. 垂直分割

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

举例:在Users表中有个字段是家庭地址这个字段是可选字段相比起而且你在数据库操作时候除了个人信息外你并不需要经常读取或是改写这个字段那么为什么不把他放到另外张表中呢？ 这样会让你表有更好性能大家想想是不是大量时候我对于用户表来说只有用户ID用户名口令用户角色等会被经常使用小点表总是会有好性能

举例 2: 你有个叫 “last_login” 字段它会在每次用户登录时被更新但是每次更新时会导致该表查询缓存Cache被清空所以你可以把这个字段放到另个表中这样就不会影响你对用户 ID用户名用户角色不停地读取了查询缓存Cache会帮你增加很多性能

另外你需要注意是这些被分出去字段所形成表你不会经常性地去Join他们不然话这样性能会比不分割时还要差而且会是极数级下降
17. 拆分大 DELETE 或 INSERT 语句
如果你需要在个在线网站WebSite上去执行个大 DELETE 或 INSERT 查询你需要非常小心要避免你操作让你整个网站WebSite停止相应这两个操作是会锁表表锁住了别操作都进不来了

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

如果你把你表锁上段时间比如30秒钟那么对于个有很高访问量站点来说这30秒所积累访问进程/线程数据库链接打开文件数可能不仅仅会让你泊WEB服务Crash还可能会让你整台服务器马上掛了

所以如果你有个大处理你定你定把其拆分使用 LIMIT 条件是个好思路方法下面是个举例:
复制代码 代码如下:

while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
(mysql_affected_rows 0) {
// 没得可删了退出！
;
}
// 每次都要休息会儿
usleep(50000);
}


18. 越小列会越快

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

参看 MySQL 文档 Storage Requirements 查看所有数据类型

如果个表只会有几列罢了(比如说字典表配置表)那么我们就没有理由使用 INT 来做主键使用 MEDIUMINT, SMALLINT 或是更小 TINYINT 会更经济些如果你不需要记录时间使用 DATE 要比 DATETIME 好得多

当然你也需要留够足够扩展空间不然你日后来干这个事你会死很难看参看Slashdot例子(2009年11月06 日)个简单ALTER TABLE语句花了3个多小时里面有千 6百万条数据
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语句打包成个事务这会比单独执行他们快得多得多

目前个人最喜欢PHPORM是:Doctrine
21. 小心“永久链接”

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

* PHP手册:mysql_pconnect

在理论上来说这听起来非常不错但是从个人经验(也是大多数人)上来说这个功能制造出来麻烦事更多你只有有限链接数内存问题文件句柄数等等

而且Apache 运行在极端并行环境中会创建很多很多了进程这就是为什么这种“永久链接”机制工作地不好原因在你决定要使用“永久链接”的前你需要好好地考虑下你整个系统架构
文章:来源