mysql数据库优化总结(心得)
在mysql服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。
但主要的问题是，它是那么容易被隐藏起来以至于我们大多数程序员会忽略它。在有些处理任务中，我们实际上是可以阻止查询缓存工作的。
// query cache does not work
$r = mysql_query("select username from user where signup_date >= curdate()");
// query cache works!
$today = date("y-m-d");
$r = mysql_query("select username from user where signup_date >= '$today'");
// query cache does not work
$r = mysql_query("select username from user where signup_date >= curdate()");
// query cache works!
$today = date("y-m-d");
$r = mysql_query("select username from user where signup_date >= '$today'");
2. 用explain使你的select查询更加清晰
使用explain关键字是另一个mysql优化技巧，可以让你了解mysql正在进行什么样的查询操作，这可以帮助你发现瓶颈的所在，并显示出查询或表结构在哪里出了问题。
explain查询的结果，可以告诉你那些索引正在被引用，表是如何被扫描和排序的等等。
实现一个select查询（最好是比较复杂的一个，带joins方式的），在里面添加上你的关键词解释，在这里我们可以使用phpmyadmin，他会告诉你表中的结果。举例来说，假如当我在执行joins时，正忘记往一个索引中添加列，explain能帮助我找到问题的所在。
添加索引到group_id field后
有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的where子句。
在这种情况下，增加一个limit 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。
// do i have any users from alabama?
// what not to do:
$r = mysql_query("select * from user where state = 'alabama'");
if (mysql_num_rows($r) > 0) {
// ...
}
// much better:
$r = mysql_query("select 1 from user where state = 'alabama' limit 1");
if (mysql_num_rows($r) > 0) {
// ...
}
4.索引中的检索字段
索引不仅是主键或唯一键。如果你想搜索表中的任何列，你应该一直指向索引。
5. 保证连接的索引是相同的类型
如果应用程序中包含多个连接查询，你需要确保你链接的列在两边的表上都被索引。这会影响mysql如何优化内部联接操作。
此外，加入的列，必须是同一类型。例如，你加入一个decimal列，而同时加入另一个表中的int列，mysql将无法使用其中至少一个指标。即使字符编码必须同为字符串类型。
// looking for companies in my state
$r = mysql_query("select company_name from users
left join companies on (users.state = companies.state)
where users.id = $user_id");
// both state columns should be indexed
// and they both should be the same type and character encoding
// or mysql might do full table scans
6. 不要使用by rand()命令
这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用by rand（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，mysql可能会为表中每一个独立的行执行by rand()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。
// what not to do:
$r = mysql_query("select username from user order by rand() limit 1");
// much better:
$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服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。
// not preferred
$r = mysql_query("select * from user where user_id = 1");
$d = mysql_fetch_assoc($r);
echo "welcome {$d['username']}";
// better:
$r = mysql_query("select username from user where user_id = 1");
$d = mysql_fetch_assoc($r);
echo "welcome {$d['username']}";
// the differences are more significant with bigger result sets
8. 从procedure analyse()中获得建议
procedure analyse()可让mysql的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。
9. 准备好的语句
准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止sql注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。
// create a prepared statement
if ($stmt = $mysqli->prepare("select username from user where state=?")) {
// bind parameters
$stmt->bind_param("s", $state);
// execute
$stmt->execute();
// bind result variables
$stmt->bind_result($username);
// fetch value
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
10. 将ip地址存储为无符号整型
许多程序员在创建一个varchar（15）时并没有意识到他们可以将ip地址以整数形式来存储。当你有一个int类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个unsigned int类型的,因为ip地址将使用32位unsigned integer。
$r = "update users set ip = inet_aton('{$_server['remote_addr']}') where user_id = $user_id";
11.永远为每张表设置一个id
我们应该为数据库里的每张表都设置一个id做为其主键，而且最好的是一个int型的(推荐使用unsigned)，并设置上自动增加的auto_increment标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用varchar类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的id来构造你的数据结构。
而且，在mysql数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的id，有一个“课程表”有课程id，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生id和课程id叫“外键”其共同组成主键。
12.使用enum而不是varchar
enum类型是非常快和紧凑的。在实际上，其保存的是tinyint，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用enum而不是varchar。
mysql也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个varchar字段时，这个建议会告诉你把其改成enum类型。使用procedure analyse() 你可以得到相关的建议。
13.从procedure analyse()取得建议p程序员站
procedure analyse() 会让mysql帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。
例如，如果你创建了一个int字段作为你的主键，然而并没有太多的数据，那么，procedure analyse()会建议你把这个字段的类型改成mediumint。或是你使用了一个varchar字段，因为数据不多，你可能会得到一个让你把它改成enum的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。
在phpmyadmin里，你可以在查看表时，点击“propose table structure”来查看这些建议
一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人
14.尽可能的使用not null php程序员站
除非你有一个很特别的原因去使用null值，你应该总是让你的字段保持not null。这看起来好像有点争议，请往下看。
首先，问问你自己“empty”和“null”有多大的区别(如果是int，那就是0和null)?如果你觉得它们之间没有什么区别，那么你就不要使用null。(你知道吗?在oracle里，null 和 empty的字符串是一样的!)
不要以为 null 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。当然，这里并不是说你就不能使用null了，现实情况是很复杂的，依然会有些情况下，你需要使用null值。
下面摘自mysql自己的文档：
15. 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.
16.无缓冲的查询
正常的情况下，当你在当你在你的脚本中执行一个sql语句的时候，你的程序会停在那里直到没这个sql语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
关于这个事情，在php的文档中有一个非常不错的说明：mysql_unbuffered_query()函数：
上面那句话翻译过来是说，mysql_unbuffered_query()发送一个sql语句到mysql而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。
然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且， mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。
17.把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()。
18.固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： varchar，text，blob。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，mysql 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为mysql搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。 php程序员站
使用“垂直分割”技术(见下一条)，你可以分割你的表成为两个一个是定长的，一个则是不定长的。
19.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。(以前，在银行做过项目，见过一张表有100多个字段，很恐怖)
示例一：在users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢?这样会让你的表有更好